postgresql 9.1 基于wal的完全恢复
基于wal恢复前的准备
确保这两个参数是启用状态
archive_mode = on # allows archiving to be done
archive_command = 'cp %p /mnt/nas_dbbackup/archivelog/%f && echo %f >>/mnt/nas_dbbackup/archivelog/archive.log' # command to use to archive a logfile segmentpgsql 安装好的相关目录
config /etc/postgresql/9.1/main
data /var/lib/postgresql/9.1/main
socket /var/run/postgresql
service /etc/init.d/postgresql
lib /usr/lib/postgresql/9.1/lib
log /var/log/postgresql启动postgresql数据库
# service postgresql start
# ps -ef|grep -i postgres
root 1657 1651 0 09:20 pts/2 00:00:00 su - postgres
postgres 1658 1657 0 09:20 pts/2 00:00:00 -su
postgres 2534 1 3 09:38 ? 00:00:00 /usr/lib/postgresql/9.1/bin/postgres -D /var/lib/postgresql/9.1/main -c config_file=/etc/postgresql/9.1/main/postgresql.conf
postgres 2535 2534 0 09:38 ? 00:00:00 postgres: logger process
postgres 2537 2534 0 09:38 ? 00:00:00 postgres: writer process
postgres 2538 2534 0 09:38 ? 00:00:00 postgres: wal writer process
postgres 2539 2534 0 09:38 ? 00:00:00 postgres: autovacuum launcher process
postgres 2540 2534 0 09:38 ? 00:00:00 postgres: archiver process
postgres 2541 2534 0 09:38 ? 00:00:00 postgres: stats collector process
root 2555 1569 0 09:38 pts/1 00:00:00 grep -i postgres基础备份文件
第一步 select pg_start_backup(‘20170627’);
第二步 tar -Pzcvf /mnt/nas_dbbackup/basebackup/base_20170627102400.tar.gz /var/lib/postgresql/9.1/main
第三步 select pg_stop_backup();
$ ls -l /var/lib/postgresql/9.1/main/pg_xlog
总用量 163852
-rw------- 1 postgres postgres 16777216 6月 27 09:48 00000002000000000000000C
-rw------- 1 postgres postgres 16777216 6月 27 09:52 00000002000000000000000D
-rw------- 1 postgres postgres 16777216 6月 27 09:52 00000002000000000000000E
-rw------- 1 postgres postgres 16777216 6月 27 09:52 00000002000000000000000F
-rw------- 1 postgres postgres 16777216 6月 27 09:54 000000020000000000000010
-rw------- 1 postgres postgres 16777216 6月 27 09:54 000000020000000000000011
-rw------- 1 postgres postgres 16777216 6月 27 09:54 000000020000000000000012
-rw------- 1 postgres postgres 16777216 6月 27 09:54 000000020000000000000013
-rw------- 1 postgres postgres 16777216 6月 27 10:27 000000020000000000000014
-rw------- 1 postgres postgres 16777216 6月 27 11:12 000000020000000000000015
-rw------- 1 postgres postgres 281 6月 27 11:12 000000020000000000000015.00000020.backup
-rw------- 1 postgres postgres 56 6月 22 05:48 00000002.history
drwx------ 2 postgres postgres 4096 6月 27 11:12 archive_statuspostgres=# create table tmp_test1(c1 bigint);
postgres=# select pg_switch_xlog();
postgres=# insert into tmp_test1 select generate_series(1,10000);
postgres=# select pg_switch_xlog();
postgres=# insert into tmp_test1 select generate_series(1,10000);
postgres=# select pg_switch_xlog();
postgres=# insert into tmp_test1 select generate_series(1,10000);
postgres=# select pg_switch_xlog();
postgres=# insert into tmp_test1 select generate_series(1,10000);
postgres=# select pg_switch_xlog();$ ls -l /var/lib/postgresql/9.1/main/pg_xlog
总用量 229388
-rw------- 1 postgres postgres 16777216 6月 27 09:48 00000002000000000000000C
-rw------- 1 postgres postgres 16777216 6月 27 09:52 00000002000000000000000D
-rw------- 1 postgres postgres 16777216 6月 27 09:52 00000002000000000000000E
-rw------- 1 postgres postgres 16777216 6月 27 09:52 00000002000000000000000F
-rw------- 1 postgres postgres 16777216 6月 27 09:54 000000020000000000000010
-rw------- 1 postgres postgres 16777216 6月 27 09:54 000000020000000000000011
-rw------- 1 postgres postgres 16777216 6月 27 09:54 000000020000000000000012
-rw------- 1 postgres postgres 16777216 6月 27 09:54 000000020000000000000013
-rw------- 1 postgres postgres 16777216 6月 27 10:27 000000020000000000000014
-rw------- 1 postgres postgres 16777216 6月 27 11:12 000000020000000000000015
-rw------- 1 postgres postgres 281 6月 27 11:12 000000020000000000000015.00000020.backup
-rw------- 1 postgres postgres 16777216 6月 27 11:15 000000020000000000000016
-rw------- 1 postgres postgres 16777216 6月 27 11:15 000000020000000000000017
-rw------- 1 postgres postgres 16777216 6月 27 11:15 000000020000000000000018
-rw------- 1 postgres postgres 16777216 6月 27 11:15 000000020000000000000019
-rw------- 1 postgres postgres 56 6月 22 05:48 00000002.history
drwx------ 2 postgres postgres 4096 6月 27 11:15 archive_status可以看到，多了4个WAL日志
基于wal的完全恢复
# service postgresql stop
$ cd /var/lib/postgresql/9.1/main
$ rm -rf ./* 解压之前备份的 basebackup 文件
$ cd /
$ tar -Pzxvf /mnt/nas_dbbackup/basebackup/base_20170627102400.tar.gz如果tar 时排除了 pg_xlog，则需要创建pg_xlog 文件夹
目的是要确保 pg_xlog 下只有一个空的 archive_status 文件夹
$ cd /var/lib/postgresql/9.1/main
$ mkdir -p ./pg_xlog
$ mkdir -p ./pg_xlog/archive_status创建或者修改 recovery.conf
可以参考这个文件 /usr/share/postgresql/9.1/recovery.conf.sample
$ vi /var/lib/postgresql/9.1/mainrecovery.conf
restore_command = 'cp /mnt/nas_dbbackup/archivelog/%f %p'
#archive_cleanup_command = ''
#recovery_end_command = ''
#recovery_target_name = ''
#recovery_target_time = ''
#recovery_target_xid = ''
recovery_target_inclusive = 'true'
recovery_target_timeline = 'latest'
pause_at_recovery_target = 'true'
trigger_file = '/tmp/postgresql.trigger.5432'# service postgresql start查看日志
2017-06-27 16:43:27.470 CST,,,3458,,59521aaf.d82,2,,2017-06-27 16:43:27 CST,,0,LOG,00000,"starting archive recovery",,,,,,,,,""
2017-06-27 16:43:27.671 CST,,,3458,,59521aaf.d82,3,,2017-06-27 16:43:27 CST,,0,LOG,00000,"restored log file ""000000020000000000000015"" from archive",,,,,,,,,""
2017-06-27 16:43:27.689 CST,,,3458,,59521aaf.d82,4,,2017-06-27 16:43:27 CST,1/1,0,LOG,00000,"redo starts at 0/15000020",,,,,,,,,""
2017-06-27 16:43:27.692 CST,,,3458,,59521aaf.d82,5,,2017-06-27 16:43:27 CST,1/1,0,LOG,00000,"consistent recovery state reached at 0/150005E8",,,,,,,,,""
2017-06-27 16:43:27.695 CST,,,3456,,59521aad.d80,1,,2017-06-27 16:43:25 CST,,0,LOG,00000,"database system is ready to accept read only connections",,,,,,,,,""
2017-06-27 16:43:27.810 CST,,,3458,,59521aaf.d82,6,,2017-06-27 16:43:27 CST,1/1,0,LOG,00000,"restored log file ""000000020000000000000016"" from archive",,,,,,,,,""
2017-06-27 16:43:27.851 CST,,,3458,,59521aaf.d82,7,,2017-06-27 16:43:27 CST,1/1,0,LOG,00000,"restored log file ""000000020000000000000017"" from archive",,,,,,,,,""
2017-06-27 16:43:27.870 CST,,,3458,,59521aaf.d82,8,,2017-06-27 16:43:27 CST,1/1,0,LOG,00000,"restored log file ""000000020000000000000018"" from archive",,,,,,,,,""
2017-06-27 16:43:27.889 CST,,,3458,,59521aaf.d82,9,,2017-06-27 16:43:27 CST,1/1,0,LOG,00000,"restored log file ""000000020000000000000019"" from archive",,,,,,,,,""
2017-06-27 16:43:27.909 CST,,,3458,,59521aaf.d82,10,,2017-06-27 16:43:27 CST,1/1,0,LOG,00000,"restored log file ""00000002000000000000001A"" from archive",,,,,,,,,""
2017-06-27 16:43:27.912 CST,,,3458,,59521aaf.d82,11,,2017-06-27 16:43:27 CST,1/1,0,LOG,58P01,"could not open file ""pg_xlog/00000002000000000000001B"" (log file 0, segment 27): No such file or directory",,,,,,,,,""
2017-06-27 16:43:27.912 CST,,,3458,,59521aaf.d82,12,,2017-06-27 16:43:27 CST,1/1,0,LOG,00000,"redo done at 0/1A0018F8",,,,,,,,,""
2017-06-27 16:43:27.912 CST,,,3458,,59521aaf.d82,13,,2017-06-27 16:43:27 CST,1/1,0,LOG,00000,"last completed transaction was at log time 2017-06-27 11:16:00.377089+08",,,,,,,,,""
2017-06-27 16:43:27.926 CST,,,3458,,59521aaf.d82,14,,2017-06-27 16:43:27 CST,1/1,0,LOG,00000,"restored log file ""00000002000000000000001A"" from archive",,,,,,,,,""
2017-06-27 16:43:27.928 CST,,,3458,,59521aaf.d82,15,,2017-06-27 16:43:27 CST,1/1,0,LOG,00000,"s**elected new timeline ID: 3**",,,,,,,,,""
2017-06-27 16:43:28.177 CST,,,3458,,59521aaf.d82,16,,2017-06-27 16:43:27 CST,1/1,0,LOG,00000,"**archive recovery complete**",,,,,,,,,""
2017-06-27 16:43:28.815 CST,,,3456,,59521aad.d80,2,,2017-06-27 16:43:25 CST,,0,LOG,00000,"**database system is ready to accept connections**",,,,,,,,,""
2017-06-27 16:43:28.816 CST,,,3490,,59521ab0.da2,1,,2017-06-27 16:43:28 CST,,0,LOG,00000,"autovacuum launcher started",,,,,,,,,""$ psql
psql (9.1.24)
输入 "help" 来获取帮助信息.
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1 行记录)
postgres=# select count(1) from tmp_test1;
count
-------
40000
(1 行记录)
