分享程序员开发的那些事...
更新时间：2022-09-23 21:42:41
试验目的：
在基于PG的PITR基础上进行，首先获得数据库的基础备份，然后做基于时间点的恢复，恢复时正常打开数据库。每次打开一次数据库，都会重新生成一个timeline，对于timeline的数值，可以在pg_xlog下根据日志获得。结合时间和timeline，就可以进行基于时间线和时间点的恢复
试验环境：
数据库目录 /db/pgsql/testdb
归档目录 /db/pgsql/archive
基础备份 /db/pgsql/fullbackup/base.tar --pg_basebackup -p 5433 -U postgres -Ft -Dfullbackup
pg_basebackup命令将pg_start_backup()和pg_stop_backup()封装起来，在开始备份时，PG会强制执行一个checkpoint并将当前的WAL文件的位置写入数据目录下的backup_label中。结束备份，switch一个WAL文件，并在pg_xlog目录下生成一个以.backup结尾的文件，命名规则为TTTTTTTTSSSSSSSSSSSSSSS.OOOOOOOO.backup。T部分代表着timeline，S部分代表了WAL段的序列号。查看TTTTTTTTSSSSSSSSSSSSSSS.OOOOOOOO.backup文件的内容，如下：
$cat testdb/pg_xlog/00000001000000000000000B.00000028.backup
START WAL LOCATION: 0/B000028 (file 00000001000000000000000B)
STOP WAL LOCATION: 0/B0000F0 (file 00000001000000000000000B)
CHECKPOINT LOCATION: 0/B000060
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2015-10-30 14:35:29 CST
LABEL: pg_basebackup base backup
STOP TIME: 2015-10-30 14:35:29 CST
因为测试环境， 数据量较小，故备份时间短，且在备份期间，也没有切换过WAL文件。该文件也会被归档到归档目录下。
实验步骤：
接下来，产生一些数据修改：
psql -p 5433 -d test
psql.bin (9.3.4)
Type "help" for help.
test=# create table t(id int);
CREATE TABLE
test=# insert into t values (1),(2);
INSERT 0 2
test=# select now(); --我们接下来会将数据恢复至此处
now
-------------------------------
2015-10-30 14:56:30.128886+08
(1 row)
test=# select * from t ;
id
----
1
2
(2 rows)
test=# drop table t ;
DROP TABLE
test=# select pg_switch_xlog();
pg_switch_xlog
----------------
0/C0137A8
(1 row)
接下来我们进行恢复，关闭数据库，将备份还原。为了保证所有的WAL日志均已经归档，可以手工拷贝pg_xlog下的WAL到归档目录下。
pg_ctl stop -D /db/pgsql/testdb/
waiting for server to shut down.... done
server stopped
cp -i testdb/pg_xlog/* archive/
恢复后启动数据库，pg_log日志信息：
2015-10-30 15:41:16.874 CST,,,16634,,56331f1c.40fa,1,,2015-10-30 15:41:16 CST,,0,LOG,00000,"database system was interrupted; last known up at 2015-10-30 14:35:29 CST",,,,,,,,,""
2015-10-30 15:41:16.874 CST,,,16634,,56331f1c.40fa,2,,2015-10-30 15:41:16 CST,,0,LOG,00000,"creating missing WAL directory ""pg_xlog/archive_status""",,,,,,,,,""
2015-10-30 15:41:16.874 CST,,,16634,,56331f1c.40fa,3,,2015-10-30 15:41:16 CST,,0,LOG,00000,"starting point-in-time recovery to 2015-10-30 14:56:30+08",,,,,,,,,""
2015-10-30 15:41:17.030 CST,,,16634,,56331f1c.40fa,4,,2015-10-30 15:41:16 CST,,0,LOG,00000,"restored log file ""00000001000000000000000B"" from archive",,,,,,,,,""
2015-10-30 15:41:17.051 CST,,,16634,,56331f1c.40fa,5,,2015-10-30 15:41:16 CST,1/0,0,LOG,00000,"redo starts at 0/B000028",,,,,,,,,""
2015-10-30 15:41:17.051 CST,,,16634,,56331f1c.40fa,6,,2015-10-30 15:41:16 CST,1/0,0,LOG,00000,"consistent recovery state reached at 0/B0000F0",,,,,,,,,""
2015-10-30 15:41:17.053 CST,,,16632,,56331f1c.40f8,2,,2015-10-30 15:41:16 CST,,0,LOG,00000,"database system is ready to accept read only connections",,,,,,,,,""
2015-10-30 15:41:17.217 CST,,,16634,,56331f1c.40fa,7,,2015-10-30 15:41:16 CST,1/0,0,LOG,00000,"restored log file ""00000001000000000000000C"" from archive",,,,,,,,,""
2015-10-30 15:41:17.220 CST,,,16634,,56331f1c.40fa,8,,2015-10-30 15:41:16 CST,1/0,0,LOG,00000,"recovery stopping before commit of transaction 1812, time 2015-10-30 14:57:02.813578+08",,,,,,,,,""
2015-10-30 15:41:17.220 CST,,,16634,,56331f1c.40fa,9,,2015-10-30 15:41:16 CST,1/0,0,LOG,00000,"recovery has paused",,"Execute pg_xlog_replay_resume() to continue.",,,,,,,""
2015-10-30 15:43:05.340 CST,,,16634,,56331f1c.40fa,10,,2015-10-30 15:41:16 CST,1/0,0,LOG,00000,"redo done at 0/C0135E8",,,,,,,,,""
2015-10-30 15:43:05.340 CST,,,16634,,56331f1c.40fa,11,,2015-10-30 15:41:16 CST,1/0,0,LOG,00000,"last completed transaction was at log time 2015-10-30 14:56:14.266773+08",,,,,,,,,""
2015-10-30 15:43:05.347 CST,,,16634,,56331f1c.40fa,12,,2015-10-30 15:41:16 CST,1/0,0,LOG,00000,"selected new timeline ID: 2",,,,,,,,,""
2015-10-30 15:43:05.525 CST,,,16634,,56331f1c.40fa,13,,2015-10-30 15:41:16 CST,1/0,0,LOG,00000,"archive recovery complete",,,,,,,,,""
2015-10-30 15:43:05.632 CST,,,16668,,56331f89.411c,1,,2015-10-30 15:43:05 CST,,0,LOG,00000,"autovacuum launcher started",,,,,,,,,""
2015-10-30 15:43:05.632 CST,,,16632,,56331f1c.40f8,3,,2015-10-30 15:41:16 CST,,0,LOG,00000,"database system is ready to accept connections",,,,,,,,,""
由日志可以看出，从现在开始起，数据库的timeline变成了2，而之前备份时的timeline是1。 对于每一个恢复，PG都会创建一个TTTTTTTT.history文件，这个文件告诉PG当前数据库的parent timeline是什么，而当前的数据库正是从该时间线中分支出来的。
此时验证一下数据：
psql -p 5433 -d test
psql.bin (9.3.4)
Type "help" for help.
test=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | t | table | postgres
(1 row)
test=# select * from t;
id
----
1
2
(2 rows)
这样我们就恢复了被删除的表t。
接下来，在做一些数据修改：
test=# insert into t values (3),(4);
INSERT 0 2
test=# select now();
now
-------------------------------
2015-10-30 15:56:31.636684+08
(1 row)
test=# select * from t ;
id
----
1
2
3
4
(4 rows)
test=# insert into t values (5),(6);
INSERT 0 2
test=# select now();
now
-------------------------------
2015-10-30 15:57:05.774935+08
(1 row)
test=# select * from t;
id
----
1
2
3
4
5
6
(6 rows)
test=# select pg_switch_xlog();
pg_switch_xlog
----------------
0/C013868
(1 row)
test=# drop table t;
DROP TABLE
然后我们进行一次恢复，恢复到插入数据3和4的时候。此时recovery.conf文件里增加recovery_target_timeline选项
关闭数据
pg_ctl stop -D /db/pgsql/testdb
waiting for server to shut down.... done
server stopped
恢复数据目录
修改恢复配置文件：
standby_mode = on
restore_command='cp /db/pgsql/archive/%f %p'
recovery_target_time='2015-10-30 15:56:31'
recovery_target_timeline='2'
启动数据库，查看日志：
2015-10-30 16:05:38.126 CST,,,16943,,563324d1.422f,1,,2015-10-30 16:05:37 CST,,0,LOG,00000,"ending log output to stderr",,"Future log output will go to log destination ""csvlog"".",,,,,,,""
2015-10-30 16:05:38.130 CST,,,16945,,563324d2.4231,1,,2015-10-30 16:05:38 CST,,0,LOG,00000,"database system was interrupted; last known up at 2015-10-30 14:35:29 CST",,,,,,,,,""
2015-10-30 16:05:38.130 CST,,,16945,,563324d2.4231,2,,2015-10-30 16:05:38 CST,,0,LOG,00000,"creating missing WAL directory ""pg_xlog/archive_status""",,,,,,,,,""
2015-10-30 16:05:38.137 CST,,,16945,,563324d2.4231,3,,2015-10-30 16:05:38 CST,,0,LOG,00000,"restored log file ""00000002.history"" from archive",,,,,,,,,""
2015-10-30 16:05:38.137 CST,,,16945,,563324d2.4231,4,,2015-10-30 16:05:38 CST,,0,LOG,00000,"entering standby mode",,,,,,,,,""
2015-10-30 16:05:38.143 CST,,,16945,,563324d2.4231,5,,2015-10-30 16:05:38 CST,,0,LOG,00000,"restored log file ""00000002.history"" from archive",,,,,,,,,""
2015-10-30 16:05:38.630 CST,,,16945,,563324d2.4231,6,,2015-10-30 16:05:38 CST,,0,LOG,00000,"restored log file ""00000001000000000000000B"" from archive",,,,,,,,,""
2015-10-30 16:05:38.652 CST,,,16945,,563324d2.4231,7,,2015-10-30 16:05:38 CST,1/0,0,LOG,00000,"redo starts at 0/B000028",,,,,,,,,""
2015-10-30 16:05:38.652 CST,,,16945,,563324d2.4231,8,,2015-10-30 16:05:38 CST,1/0,0,LOG,00000,"consistent recovery state reached at 0/B0000F0",,,,,,,,,""
2015-10-30 16:05:38.673 CST,,,16943,,563324d1.422f,2,,2015-10-30 16:05:37 CST,,0,LOG,00000,"database system is ready to accept read only connections",,,,,,,,,""
2015-10-30 16:05:39.175 CST,,,16945,,563324d2.4231,9,,2015-10-30 16:05:38 CST,1/0,0,LOG,00000,"restored log file ""00000002000000000000000C"" from archive",,,,,,,,,""
2015-10-30 16:05:39.179 CST,,,16945,,563324d2.4231,10,,2015-10-30 16:05:38 CST,1/0,0,LOG,00000,"recovery stopping before commit of transaction 1814, time 2015-10-30 15:56:57.155436+08",,,,,,,,,""
2015-10-30 16:05:39.179 CST,,,16945,,563324d2.4231,11,,2015-10-30 16:05:38 CST,1/0,0,LOG,00000,"recovery has paused",,"Execute pg_xlog_replay_resume() to continue.",,,,,,,""
连接数据库，查看数据恢复情况：
psql -p 5433 -d test
psql.bin (9.3.4)
Type "help" for help.
test=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | t | table | postgres
(1 row)
test=# select * from t ;
id
----
1
2
3
4
(4 rows)
正常打开数据库：
test=# select pg_xlog_replay_resume();
pg_xlog_replay_resume
-----------------------
(1 row)
日志输出：
2015-10-30 16:13:41.715 CST,,,16945,,563324d2.4231,12,,2015-10-30 16:05:38 CST,1/0,0,LOG,00000,"redo done at 0/C013818",,,,,,,,,""
2015-10-30 16:13:41.715 CST,,,16945,,563324d2.4231,13,,2015-10-30 16:05:38 CST,1/0,0,LOG,00000,"last completed transaction was at log time 2015-10-30 15:56:25.505592+08",,,,,,,,,""
2015-10-30 16:13:41.722 CST,,,16945,,563324d2.4231,14,,2015-10-30 16:05:38 CST,1/0,0,LOG,00000,"selected new timeline ID: 3",,,,,,,,,""
2015-10-30 16:13:41.728 CST,,,16945,,563324d2.4231,15,,2015-10-30 16:05:38 CST,1/0,0,LOG,00000,"restored log file ""00000002.history"" from archive",,,,,,,,,""
2015-10-30 16:13:42.081 CST,,,16945,,563324d2.4231,16,,2015-10-30 16:05:38 CST,1/0,0,LOG,00000,"archive recovery complete",,,,,,,,,""
2015-10-30 16:13:42.188 CST,,,17032,,563326b6.4288,1,,2015-10-30 16:13:42 CST,,0,LOG,00000,"autovacuum launcher started",,,,,,,,,""
此时当前数据库的时间线已经变为3。
接下来，我们继续往里面添加数据：
psql -p 5433 -d test
psql.bin (9.3.4)
Type "help" for help.
test=# insert into t values (7),(8);
INSERT 0 2
test=# select now();
now
-------------------------------
2015-10-30 16:17:18.325926+08
(1 row)
test=# select * from t;
id
----
1
2
3
4
7
8
(6 rows)
test=# select pg_switch_xlog();
pg_switch_xlog
----------------
0/C013A78
(1 row)
接下来我们希望恢复数据库到timeline为2，且数据已经插入数据5和6的时间点：
关闭数据库
恢复数据
修改恢复配置文件：
standby_mode = on
restore_command='cp /db/pgsql/archive/%f %p'
#recovery_target_time='2015-10-30 15:56:31'
recovery_target_time='2015-10-30 15:57:05'
recovery_target_timeline='2'
启动数据库，查看日志：
2015-10-30 16:21:34.463 CST,,,17161,,5633288e.4309,1,,2015-10-30 16:21:34 CST,,0,LOG,00000,"ending log output to stderr",,"Future log output will go to log destination ""csvlog"".",,,,,,,""
2015-10-30 16:21:34.467 CST,,,17163,,5633288e.430b,1,,2015-10-30 16:21:34 CST,,0,LOG,00000,"database system was interrupted; last known up at 2015-10-30 14:35:29 CST",,,,,,,,,""
2015-10-30 16:21:34.467 CST,,,17163,,5633288e.430b,2,,2015-10-30 16:21:34 CST,,0,LOG,00000,"creating missing WAL directory ""pg_xlog/archive_status""",,,,,,,,,""
2015-10-30 16:21:34.474 CST,,,17163,,5633288e.430b,3,,2015-10-30 16:21:34 CST,,0,LOG,00000,"restored log file ""00000002.history"" from archive",,,,,,,,,""
2015-10-30 16:21:34.474 CST,,,17163,,5633288e.430b,4,,2015-10-30 16:21:34 CST,,0,LOG,00000,"entering standby mode",,,,,,,,,""
2015-10-30 16:21:34.480 CST,,,17163,,5633288e.430b,5,,2015-10-30 16:21:34 CST,,0,LOG,00000,"restored log file ""00000002.history"" from archive",,,,,,,,,""
2015-10-30 16:21:34.655 CST,,,17163,,5633288e.430b,6,,2015-10-30 16:21:34 CST,,0,LOG,00000,"restored log file ""00000001000000000000000B"" from archive",,,,,,,,,""
2015-10-30 16:21:34.672 CST,,,17163,,5633288e.430b,7,,2015-10-30 16:21:34 CST,1/0,0,LOG,00000,"redo starts at 0/B000028",,,,,,,,,""
2015-10-30 16:21:34.672 CST,,,17163,,5633288e.430b,8,,2015-10-30 16:21:34 CST,1/0,0,LOG,00000,"consistent recovery state reached at 0/B0000F0",,,,,,,,,""
2015-10-30 16:21:34.673 CST,,,17161,,5633288e.4309,2,,2015-10-30 16:21:34 CST,,0,LOG,00000,"database system is ready to accept read only connections",,,,,,,,,""
2015-10-30 16:21:34.953 CST,,,17163,,5633288e.430b,9,,2015-10-30 16:21:34 CST,1/0,0,LOG,00000,"restored log file ""00000002000000000000000C"" from archive",,,,,,,,,""
2015-10-30 16:21:35.280 CST,,,17163,,5633288e.430b,10,,2015-10-30 16:21:34 CST,1/0,0,LOG,00000,"restored log file ""00000002000000000000000D"" from archive",,,,,,,,,""
2015-10-30 16:21:35.281 CST,,,17163,,5633288e.430b,11,,2015-10-30 16:21:34 CST,1/0,0,LOG,00000,"recovery stopping before commit of transaction 1815, time 2015-10-30 15:57:44.669966+08",,,,,,,,,""
2015-10-30 16:21:35.281 CST,,,17163,,5633288e.430b,12,,2015-10-30 16:21:34 CST,1/0,0,LOG,00000,"recovery has paused",,"Execute pg_xlog_replay_resume() to continue.",,,,,,,""
连接数据库，查看数据恢复状态：
test=# select * from t ;
id
----
1
2
3
4
5
6
(6 rows)
此时数据库日志：
2015-10-30 16:23:43.429 CST,,,17163,,5633288e.430b,13,,2015-10-30 16:21:34 CST,1/0,0,LOG,00000,"redo done at 0/D0054A0",,,,,,,,,""
2015-10-30 16:23:43.429 CST,,,17163,,5633288e.430b,14,,2015-10-30 16:21:34 CST,1/0,0,LOG,00000,"last completed transaction was at log time 2015-10-30 15:56:57.155436+08",,,,,,,,,""
2015-10-30 16:23:43.435 CST,,,17163,,5633288e.430b,15,,2015-10-30 16:21:34 CST,1/0,0,LOG,00000,"restored log file ""00000003.history"" from archive",,,,,,,,,""
2015-10-30 16:23:43.442 CST,,,17163,,5633288e.430b,16,,2015-10-30 16:21:34 CST,1/0,0,LOG,00000,"selected new timeline ID: 4",,,,,,,,,""
2015-10-30 16:23:43.447 CST,,,17163,,5633288e.430b,17,,2015-10-30 16:21:34 CST,1/0,0,LOG,00000,"restored log file ""00000002.history"" from archive",,,,,,,,,""
2015-10-30 16:23:43.746 CST,,,17163,,5633288e.430b,18,,2015-10-30 16:21:34 CST,1/0,0,LOG,00000,"archive recovery complete",,,,,,,,,""
2015-10-30 16:23:43.853 CST,,,17194,,5633290f.432a,1,,2015-10-30 16:23:43 CST,,0,LOG,00000,"autovacuum launcher started",,,,,,,,,""
我们的恢复时从时间线2开始，但是恢复成功后，并没有覆盖之前恢复时出现的3，而是重新生成了4。
PG使用TTTTTTTT.history文件来记录历史的timeline，这对于在恢复中指定时间点事至关重要的。一旦PG恢复到某一个时间线上的某个时间后，会自动创建一个新的timeline，且不会重写之前的任何一个已经存在的timeline。
试验灵感来源于该链接：
http://jinxter555.blogspot.hk/2009/09/postgresql-point-in-time-recovery-pitr.html