利用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=> 


至此数据库恢复故障前情况。。（另外，可以编写热备份定期脚本和删除归档日志）