利用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",,,,,,,,,""