备份恢复
http://bbs.chinaunix.net/thread-1771360-1-1.html
http://blog.163.com/digoal@126/blog/static/16387704020110442050808/
master:ip:127.0.0.1,port:1921,数据目录:$PGDATA=/home/pg93/pg_root
slave:ip:127.0.0.1,port:1922,数据目录:/home/pg93/dx_bak
建立master:
mkdir /home/pg93/pg_root
chown -R pg93 /home/pg93/pg_root
initdb -D $PGDATA -E UTF8 --locale=C -U postgres -W
设置(master):
vi postgresql.conf
max_wal_sender=10 #master端会开启sender的进程,slave端会开启recevi进程
wal_level = hot_standby
listen_address='*'
port=1921
archive_mode = on
archive_command = 'cp %p /home/pg93/arch/%f'
log_destination = 'csvlog'
logging_collector = on
vi pg_hba.conf
host all all 0.0.0.0/0 trust
host replication all 0.0.0.0/0 trust
mkdir /home/pg93/arch
chown -R pg93 /home/pg93/arch
启动(master):
pg_ctl start -D $PGDATA
操作(slave):
mkdir /home/pg93/dx_bak
chown -R pg93 /home/pg93/dx_bak
cd /home/pg93/dx_bak
pg_basebackup -F t -x -D ./ -h 127.0.0.1 -p 1921
tar -xvf base.tar
vi pg_hba.conf
host all all 0.0.0.0/0 trust
vi postgresql.conf
listen_address='*'
port=1922
cp /opt/pgsql934/share/recovery.conf.sample recovery.conf #拷贝postgresql的安装目录中的sample文件到dx_bak目录
vi recovery.conf
restore_command = 'cp /home/pg93/arch%f %p' #拷贝归档日志
操作(master):
psql -h 127.0.0.1 -p 1921 #建立一个表然后查看是否备份成功
create table abc(a int, b int);
insert into abc values(1,1);
insert into abc values(2,2);
checkpoint;
select pg_switch_xlog();
pg_ctl stop -D $PGDATA
操作(slave):
chmod 0700 /home/pg93/dx_bak
pg_ctl start -D /home/pg93/dx_bak
psql -h 127.0.0.1 -p 1921
select * from abc; #查看备份是否成功
[pg93@localhost arch]$ ll -t total 704544 -rw-------. 1 pg93 pg93 16777216 Mar 31 20:05 000000010000000000000027 -rw-------. 1 pg93 pg93 16777216 Mar 31 19:41 000000010000000000000026 -rw-------. 1 pg93 pg93 16777216 Mar 31 19:31 000000010000000000000025 -rw-------. 1 pg93 pg93 16777216 Mar 31 19:30 000000010000000000000024 -rw-------. 1 pg93 pg93 305 Mar 29 22:50 000000010000000000000023.00000028.backup -rw-------. 1 pg93 pg93 16777216 Mar 29 22:50 000000010000000000000023 -rw-------. 1 pg93 pg93 16777216 Mar 29 22:50 000000010000000000000022 -rw-------. 1 pg93 pg93 16777216 Mar 29 21:31 000000010000000000000021 -rw-------. 1 pg93 pg93 16777216 Mar 29 21:30 000000010000000000000020 -rw-------. 1 pg93 pg93 305 Mar 29 20:59 00000001000000000000001F.00000028.backup -rw-------. 1 pg93 pg93 16777216 Mar 29 20:59 00000001000000000000001F -rw-------. 1 pg93 pg93 16777216 Mar 29 20:59 00000001000000000000001E -rw-------. 1 pg93 pg93 16777216 Mar 29 20:58 00000001000000000000001D -rw-------. 1 pg93 pg93 16777216 Mar 29 20:46 00000001000000000000001C -rw-------. 1 pg93 pg93 16777216 Mar 29 20:42 00000001000000000000001B -rw-------. 1 pg93 pg93 16777216 Mar 29 20:38 00000001000000000000001A -rw-------. 1 pg93 pg93 16777216 Mar 29 20:31 000000010000000000000019 -rw-------. 1 pg93 pg93 16777216 Mar 29 20:22 000000010000000000000018 -rw-------. 1 pg93 pg93 16777216 Mar 29 20:09 000000010000000000000017 -rw-------. 1 pg93 pg93 16777216 Mar 29 16:58 000000010000000000000016 -rw-------. 1 pg93 pg93 16777216 Mar 29 16:57 000000030000000000000015 -rw-------. 1 pg93 pg93 16777216 Mar 29 16:57 000000030000000000000016 -rw-------. 1 pg93 pg93 42 Mar 29 16:54 00000003.history -rw-------. 1 pg93 pg93 16777216 Mar 29 16:54 000000010000000000000015 -rw-------. 1 pg93 pg93 305 Mar 29 16:39 000000010000000000000014.00000028.backup -rw-------. 1 pg93 pg93 16777216 Mar 29 16:39 000000010000000000000014 -rw-------. 1 pg93 pg93 305 Mar 29 16:38 000000010000000000000013.00000028.backup -rw-------. 1 pg93 pg93 16777216 Mar 29 16:38 000000010000000000000013 -rw-------. 1 pg93 pg93 305 Mar 29 16:36 000000010000000000000012.00000028.backup -rw-------. 1 pg93 pg93 16777216 Mar 29 16:36 000000010000000000000012 -rw-------. 1 pg93 pg93 16777216 Mar 29 16:36 000000010000000000000011 -rw-------. 1 pg93 pg93 16777216 Mar 29 16:29 00000002000000000000000F -rw-------. 1 pg93 pg93 16777216 Mar 29 16:18 00000002000000000000000E -rw-------. 1 pg93 pg93 41 Mar 29 16:13 00000002.history -rw-------. 1 pg93 pg93 16777216 Mar 29 16:13 00000001000000000000000E -rw-------. 1 pg93 pg93 16777216 Mar 29 16:10 000000010000000000000010 -rw-------. 1 pg93 pg93 16777216 Mar 29 16:08 00000001000000000000000F -rw-------. 1 pg93 pg93 302 Mar 29 13:27 00000001000000000000000E.00000028.backup -rw-------. 1 pg93 pg93 16777216 Mar 29 13:27 00000001000000000000000D -rw-------. 1 pg93 pg93 16777216 Mar 29 13:26 00000001000000000000000C -rw-------. 1 pg93 pg93 16777216 Mar 29 13:23 00000001000000000000000B -rw-------. 1 pg93 pg93 16777216 Mar 29 13:23 00000001000000000000000A -rw-------. 1 pg93 pg93 16777216 Mar 27 23:20 000000010000000000000009 -rw-------. 1 pg93 pg93 16777216 Mar 27 23:16 000000010000000000000008 -rw-------. 1 pg93 pg93 16777216 Mar 27 23:10 000000010000000000000007 -rw-------. 1 pg93 pg93 16777216 Mar 27 22:53 000000010000000000000006 -rw-------. 1 pg93 pg93 16777216 Mar 27 22:44 000000010000000000000005 -rw-------. 1 pg93 pg93 16777216 Mar 27 22:35 000000010000000000000004 -rw-------. 1 pg93 pg93 16777216 Mar 27 22:35 000000010000000000000003 -rw-------. 1 pg93 pg93 16777216 Mar 27 22:31 000000010000000000000002 -rw-------. 1 pg93 pg93 16777216 Mar 27 20:59 000000010000000000000001
