Chinaunix首页 | 论坛 | 博客
  • 博客访问: 4843
  • 博文数量: 2
  • 博客积分: 30
  • 博客等级: 民兵
  • 技术积分: 20
  • 用 户 组: 普通用户
  • 注册时间: 2010-03-29 09:49
文章分类
文章存档

2016年(2)

我的朋友
最近访客

分类: Mysql/postgreSQL

2016-08-06 09:48:12


晚上11点左右,朋友电话我,说他们公司PG(9.2.4版本)有点问题,登录上去看了PG日志报错如下(配置文件中有开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后,一切正常。


阅读(1186) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~