postgresql 9.1 基于wal的 pitr 恢复

基于wal恢复前的准备

master 上确保这两个参数是启用状态

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 segment

pgsql 安装好的相关目录，记录一下

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_status

postgres=# 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的 pitr 恢复

# 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/main/recovery.conf

restore_command = 'cp /mnt/nas_dbbackup/archivelog/%f %p'
#archive_cleanup_command = ''
#recovery_end_command = ''
#recovery_target_name = ''
recovery_target_time = '2017-06-27 11:15:00+08'
#recovery_target_xid = ''
recovery_target_inclusive = 'true'
recovery_target_timeline = 'latest'
pause_at_recovery_target = 'true'
standby_mode = on
trigger_file = '/tmp/postgresql.trigger.5432'

留意 recovery_target_time 、recovery_target_timeline 这两个参数

启动pgsql

# service postgresql start
查看日志
2017-06-27 17:58:55.000 CST,,,4579,,59522c5e.11e3,1,,2017-06-27 17:58:54 CST,,0,LOG,00000,"database system was interrupted; last known up at 2017-06-27 11:07:32 CST",,,,,,,,,""
2017-06-27 17:58:55.229 CST,,,4580,"[local]",59522c5f.11e4,1,"",2017-06-27 17:58:55 CST,,0,LOG,08P01,"incomplete startup packet",,,,,,,,,""
2017-06-27 17:58:55.392 CST,,,4579,,59522c5e.11e3,2,,2017-06-27 17:58:54 CST,,0,LOG,00000,"starting point-in-time recovery to 2017-06-27 11:14:00+08",,,,,,,,,""
2017-06-27 17:58:55.401 CST,,,4579,,59522c5e.11e3,3,,2017-06-27 17:58:54 CST,,0,LOG,00000,"restored log file ""000000020000000000000015"" from archive",,,,,,,,,""
2017-06-27 17:58:55.426 CST,,,4579,,59522c5e.11e3,4,,2017-06-27 17:58:54 CST,1/1,0,LOG,00000,"redo starts at 0/15000020",,,,,,,,,""
2017-06-27 17:58:55.429 CST,,,4579,,59522c5e.11e3,5,,2017-06-27 17:58:54 CST,1/1,0,LOG,00000,"consistent recovery state reached at 0/150005E8",,,,,,,,,""
2017-06-27 17:58:55.431 CST,,,4577,,59522c5e.11e1,1,,2017-06-27 17:58:54 CST,,0,LOG,00000,"database system is ready to accept read only connections",,,,,,,,,""
2017-06-27 17:58:55.440 CST,,,4579,,59522c5e.11e3,6,,2017-06-27 17:58:54 CST,1/1,0,LOG,00000,"restored log file ""000000020000000000000016"" from archive",,,,,,,,,""
2017-06-27 17:58:55.448 CST,,,4579,,59522c5e.11e3,7,,2017-06-27 17:58:54 CST,1/1,0,LOG,00000,"**recovery stopping** before commit of transaction 708, **time 2017-06-27 11:15:27.887345+08**",,,,,,,,,""
2017-06-27 17:58:55.448 CST,,,4579,,59522c5e.11e3,8,,2017-06-27 17:58:54 CST,1/1,0,LOG,00000,"recovery has paused",,"**Execute pg_xlog_replay_resume() to continue**.",,,,,,,""

$ psql
psql (9.1.24)
输入 "help" 来获取帮助信息.

postgres=# select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 t
(1 行记录)


postgres=# select count(1) from tmp_test1;
 count 
-------
     0
(1 行记录)