PostgreSQL流复制告警的处理

1、查看主库日志

less /cache1/data/pg_log/postgresql-2014-01-17_000000.csv
提示:2014-01-09 10:13:31.637 CST,"postgres","",6159,"218.107.217.79:33724",52ce05cb.180f,1,"idle",2014-01-09 10:13:31 CST,39/0,0,FATAL,58P01,"requested WAL segment 0000000600000082000000DD has already been removed",,,,,,,,,"walreceiver"

显示预读写日志0000000600000082000000DD已被归档,原因是数据库读写频繁短时间内有大量数据生成,SQL操作日志产生到预读写日志wal中,若pg_xlog下的wal数超过wal_keep_segments的值,则触发归档。导致walsender进程不能准时发送wal到备库还原,引起流复制中断。
2、备机停库

脚本命令参考数据库管理脚本

/etc/init.d/postgresql-9.1 stop  

3. 在主机上进行基础备份

psql -U mato -h 127.0.0.1 -c "select pg_start_backup('backups02');"

5. 同步wal文件,此处是同步整个data目录,可以测试下只覆盖wal的归档文件

rsync -apurz -e "ssh -p 端口" /cache1/data/* [email protected]远程IP:/opt/PostgreSQL/9.1/data/

6. 基础备份结束

psql -U mato -h 127.0.0.1 -c "select pg_stop_backup();"

7.启动备机

/etc/init.d/postgresql-9.1 start

8.查看日志,有recevier即pitr流同步成功

ps -ef|grep wal 、ps -ef |grep postgres

weinxin
聂扬帆博客
一个分享IT运维相关工作经验和实战技巧的个人博客

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: