postgresql row is too big

liu-shaobo

晚上11點(diǎn)左右，朋友電話我，說(shuō)他們公司PG有點(diǎn)問(wèn)題，登錄上去看了PG(9.2.4)日志報(bào)錯(cuò)如下（配置文件中有開(kāi)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.",,,,,,,""

如是進(jìn)入到單用戶執(zhí)行如下操作:
pg_ctl -m f stop -D $PGDATA
postgres --single lockdb -D $PGDATA
backend> vacuum full;

操作到vacuum full報(bào)錯(cuò)如下：
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報(bào)錯(cuò)如下：
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表，后刪除索引，報(bào)錯(cuò)如下：
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;

當(dāng)時(shí)PG的數(shù)據(jù)庫(kù)大概470G左右，其中索引user_checkin表索引就占了130G。如是和朋友商量后，刪除索引和重建索引，時(shí)間太長(zhǎng)，打算清空user_checkin表，清空user_checkin表后，PG數(shù)據(jù)大小100G左右，然后在執(zhí)行上面的vacuum操作，PG數(shù)據(jù)大小為86G，重啟PG后，一切正常。