postgresql row is too big
晚上11点左右，朋友电话我，说他们公司PG有点问题，登录上去看了PG(9.2.4)日志报错如下（配置文件中有开autovacuum）：
2016-08-05 23:47:32.839 CST,,,41181,,57a4b514.a0dd,2,,2016-08-05 23:47:32 CST,,0,WARNING,01000,"database with OID 16384 must be vacuumed within 999409 transactions",,"To avoid a database shutdown, execute a database-wide VACUUM in that database.
You might also need to commit or roll back old prepared transactions.",,,,,,,""
如是进入到单用户执行如下操作:
pg_ctl -m f stop -D $PGDATA
postgres --single lockdb -D $PGDATA
backend> vacuum full;
操作到vacuum full报错如下：
WARNING:database "lockdb" must be vacuumed within 999238 transactions
HINT:To avoid a database shutdown, execute a database-wide VACUUM in that database.
You might also need to commit or roll back old prepared transactions.
ERROR:row is too big: size 235728, maximum size 8160
STATEMENT:vacuum full;
改用vacuum freeze报错如下：
backend> vacuum freeze;
\ERROR:failed to re-find parent key in index "user_checkin_user_id_idx" for deletion target page 902154
STATEMENT:vacuum freeze;
如是打算dump user_checkin表，后删除索引，报错如下：
pg_dump: Dumping the contents of table "user_checkin" failed: PQgetResult() failed.
pg_dump: Error message from server: ERROR:invalid memory alloc request size 18446744073709551613
pg_dump: The command was: COPY public.user_checkin (id, user_id, active_date, last_login_date, update_date, update_flag, product_code, kernel_code, product_ver, product_ver_num, opt_update_num) TO stdout;
当时PG的数据库大概470G左右，其中索引user_checkin表索引就占了130G。如是和朋友商量后，删除索引和重建索引，时间太长，打算清空user_checkin表，清空user_checkin表后，PG数据大小100G左右，然后在执行上面的vacuum操作，PG数据大小为86G，重启PG后，一切正常。
页:
[1]