利用pg_basebackup命令进行热备份和恢复
利用pg_basebackup命令进行热备份和恢复
一、开启归档日志
[postgres@rhel5-01 ~]$ vi /usr/local/postgresql-9.2.1/data/postgresql.conf
# - Settings -
wal_level = archive # minimal, archive, or hot_standby
# (change requires restart)
#fsync = on # turns forced synchronization on or off
#synchronous_commit = on # synchronization level;
# - Archiving -
archive_mode = on # allows archiving to be done
# (change requires restart)
archive_command = ' cp %p /usr/local/postgresql-9.2.1/archive/%f' # command to use to archive a logfile segment
# placeholders: %p = path of file to archive
# %f = file name only
# e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
#archive_timeout = 0 # force a logfile segment switch after this
# number of seconds; 0 disables
#------------------------------------------------------------------------------
# REPLICATION
#------------------------------------------------------------------------------
# - Sending Server(s) -
# Set these on the master and on any standby that will send replication data.
max_wal_senders = 2 # max number of walsender processes
# (change requires restart)
二、开启流复制访问
编辑 pg_hba.conf
# replication privilege.
#local replication postgres trust
host replication postgres 127.0.0.1/32 trust
#host replication postgres ::1/128 trust
三、生成热备份
[postgres@rhel5-01 tmp]$ pg_basebackup -D backup -h localhost -U postgres -Ft -z -P
26711/26711 kB (100%), 2/2 tablespaces
NOTICE: pg_stop_backup complete, all required WAL segments have been archived
[postgres@rhel5-01 tmp]$
四、利用热备进行恢复
[root@rhel5-01 postgresql-9.2.1]# mv data data_bak
[root@rhel5-01 postgresql-9.2.1]#
[root@rhel5-01 backup]# mv /tmp/backup /usr/local/postgresql-9.2.1/data
[root@rhel5-01 data]# cd /usr/local/postgresql-9.2.1/data
[root@rhel5-01 data]# tar -zxvf 16394.tar.gz
[root@rhel5-01 data]# tar -zxvf base.tar.gz
[root@rhel5-01 data]# cp /usr/local/postgresql-9.2.1/share/recovery.conf.sample recovery.conf
[root@rhel5-01 data]#mkdir -p pg_xlog/archive_status/
五、编辑recovery.conf
# NOTE that the basename of %p will be different from %f; do not
# expect them to be interchangeable.
#
restore_command = 'cp /usr/local/postgresql-9.2.1/archive/%f %p'
六、启动postgreseql
[root@rhel5-01 pg_log]# /etc/init.d/postgresql-9.2 start
[root@rhel5-01 pg_log]# tail -f postgresql-2013-03-01_064501.csv
2013-03-01 06:45:02.068 CST,,,6207,,512fdded.183f,3,,2013-03-01 06:45:01 CST,,0,LOG,00000,"restored log file ""000000010000000000000017"" from archive",,,,,,,,,""
2013-03-01 06:45:02.433 CST,,,6207,,512fdded.183f,4,,2013-03-01 06:45:01 CST,,0,LOG,00000,"redo starts at 0/17000080",,,,,,,,,""
2013-03-01 06:45:02.434 CST,,,6207,,512fdded.183f,5,,2013-03-01 06:45:01 CST,,0,LOG,00000,"consistent recovery state reached at 0/18000000",,,,,,,,,""
2013-03-01 06:45:03.425 CST,,,6207,,512fdded.183f,6,,2013-03-01 06:45:01 CST,,0,LOG,00000,"restored log file ""000000010000000000000018"" from archive",,,,,,,,,""
2013-03-01 06:45:03.435 CST,,,6207,,512fdded.183f,7,,2013-03-01 06:45:01 CST,,0,LOG,58P01,"could not open file ""pg_xlog/000000010000000000000019"" (log file 0, segment 25): No such file or directory",,,,,,,,,""
2013-03-01 06:45:03.435 CST,,,6207,,512fdded.183f,8,,2013-03-01 06:45:01 CST,,0,LOG,00000,"redo done at 0/18000FF0",,,,,,,,,""
2013-03-01 06:45:03.435 CST,,,6207,,512fdded.183f,9,,2013-03-01 06:45:01 CST,,0,LOG,00000,"last completed transaction was at log time 2013-03-01 06:28:01.44982+08",,,,,,,,,""
2013-03-01 06:45:04.335 CST,,,6207,,512fdded.183f,10,,2013-03-01 06:45:01 CST,,0,LOG,00000,"restored log file ""000000010000000000000018"" from archive",,,,,,,,,""
2013-03-01 06:45:04.358 CST,,,6207,,512fdded.183f,11,,2013-03-01 06:45:01 CST,,0,LOG,00000,"selected new timeline ID: 2",,,,,,,,,""
2013-03-01 06:45:07.774 CST,,,6207,,512fdded.183f,12,,2013-03-01 06:45:01 CST,,0,LOG,00000,"archive recovery complete",,,,,,,,,""
2013-03-01 06:45:10.208 CST,,,6225,,512fddf6.1851,1,,2013-03-01 06:45:10 CST,,0,LOG,00000,"autovacuum launcher started",,,,,,,,,""
2013-03-01 06:45:10.249 CST,,,6205,,512fdded.183d,1,,2013-03-01 06:45:01 CST,,0,LOG,00000,"database system is ready to accept connections",,,,,,,,,""
七、查看数据库状态
[postgres@rhel5-01 ~]$ psql -U mqis_app mqis
psql (9.2.1)
Type "help" for help.
mqis=> select count (*) from test;
count
-------
41
(1 row)
mqis=>
至此数据库恢复故障前情况。。（另外，可以编写热备份定期脚本和删除归档日志）
