pgsql9.1 基于wal的 pitr 恢复

基于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 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’

# 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 行记录)