postgreSQL 11.4物理备份时间点恢复

创建相关管理目录
# mkdir –p /data/pgsql/

chown –R postgres: postgres /data/pgsql/

su - postgres

mkdir -p /data/pgsql/backup

mkdir -p /data/pgsql/archive

backup目录用来存放基础备份
archive目录用来存放归档
接下来我们修改我们的postgresql.conf文件的相关设置

wal_level = replica
 
archive_mode = on
 
archive_command = ' test ! -f /data/pgsql/archive/%f && cp %p /data/pgsql/archive/%f '

archive_command 参数的默认值是个空字符串，它的值可以是一条shell命令或者一个复杂的shell脚本。
在archive_command的shell命令或脚本中可以用 %p 表示将要归档的WAL文件的包含完整路径信息的文件名，用 %f 代表不包含路径信息的WAL文件的文件名。
修改wal_level和archive_mode参数都需要重新启动数据库才可以生效，修改archive_command不需要重启，只需要reload即可，例如：
postgres=# SELECT pg_reload_conf();
 
postgres=# show wal_level;
 wal_level
-----------
 replica
(1 row)

postgres=# show archive_mode;
 archive_mode
--------------
 on
(1 row)

postgres=# show archive_command;
                         archive_command
------------------------------------------------------------------
 test ! -f /data/pgsql/archive/%f && cp %p /data/pgsql/archive/%f
(1 row)

postgres=#
创建基础备份
$ -bash-4.2$ pg_basebackup -D /data/pgsql/backup/20190714151000 -v
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/D000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_62982"
pg_basebackup: write-ahead log end point: 0/D000130
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed
-bash-4.2$
创建测试表：
CREATE TABLE test(
 id SERIAL PRIMARY KEY,
 time TIMESTAMPTZ NOT NULL DEFAULT now()
);
初始化一些测试数据
INSERT INTO test values(default,default);
INSERT INTO test values(default,default);
INSERT INTO test values(default,default);
INSERT INTO test values(default,default);
INSERT INTO test values(default,default);
INSERT INTO test values(default,default);
INSERT INTO test values(default,default);
INSERT INTO test values(default,default);
INSERT INTO test values(default,default);
INSERT INTO test values(default,default);
select * from test
id|time               |
--|-------------------|
 1|2019-07-15 00:39:45|
 2|2019-07-15 00:39:48|
 3|2019-07-15 00:39:49|
 4|2019-07-15 00:39:49|
 5|2019-07-15 00:39:50|
 6|2019-07-15 00:39:51|
 7|2019-07-15 00:39:51|
 8|2019-07-15 00:39:52|
 9|2019-07-15 00:39:53|
10|2019-07-15 00:39:54|
并且按照上文的方法创建一个基础备份。如果是测试，有一点需要注意，由于WAL文件是写满16MB才会进行归档，测试阶段可能写入会非常少，可以在执行完 基础备份之后，手动进行一次WAL切换。例如：
postgres=# select pg_switch_wal();
 pg_switch_wal
---------------
 0/1D01B858
(1 row)
或者通过设置archive_timeout参数，在达到timeout阈值时强行切换到新的WAL段。
show archive_timeout
archive_timeout|
---------------|
0              |
alter system set archive_timeout=600;
SELECT pg_reload_conf();
show archive_timeout
archive_timeout|
---------------|
10min          |
接下来我们对数据做一些变更, 我们删除test的所有数据：
select now()
now                |
-------------------|
2019-07-15 00:42:10|
delete from test;
select * from test;
id|time|
--|----|
下面进行恢复到时间还原点测试
停止数据库
#service postgresql-11 stop
Redirecting to /bin/systemctl stop postgresql-11.service
netstat -an | grep 5432
移除旧的数据目录
ls
backups  data  initdb.log
mv data data_old
ls
backups  data_old  initdb.log

mv 20190714151000 /var/lib/pgsql/11/data

cp /usr/pgsql-11/share/recovery.conf.sample /var/lib/pgsql/11/data/recovery.conf
修改 recovery.conf, 修改以下配置信息：
restore_command = ' cp /data/pgsql/archive/%f %p '
recovery_target_time = '2019-07-14 23:19:34+08'  
然后启动数据库进入恢复状态，观察日志，如下所示：
bash-4.2$ pg_ctl start -D /var/lib/pgsql/11/data
waiting for server to start....2019-07-15 00:07:52.168 CST [65634] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2019-07-15 00:07:52.168 CST [65634] LOG:  listening on IPv6 address "::", port 5432
2019-07-15 00:07:52.170 CST [65634] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2019-07-15 00:07:52.173 CST [65634] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-07-15 00:07:52.188 CST [65634] LOG:  redirecting log output to logging collector process
2019-07-15 00:07:52.188 CST [65634] HINT:  Future log output will appear in directory "log".
 done
server started
-bash-4.2$
重启后，我们对test表进行查询，看数据是否正常恢复：
select * from test
id|time               |
--|-------------------|
 1|2019-07-15 00:39:45|
 2|2019-07-15 00:39:48|
 3|2019-07-15 00:39:49|
 4|2019-07-15 00:39:49|
 5|2019-07-15 00:39:50|
 6|2019-07-15 00:39:51|
 7|2019-07-15 00:39:51|
 8|2019-07-15 00:39:52|
 9|2019-07-15 00:39:53|
10|2019-07-15 00:39:54|
可以看到数据已经被恢复了
当前数据库状态为pause状态（recovery_target_action），我们可以判断当前的数据是否满足我们的预期值，如果不满足，那么可以继续向后推，直到达到我们的预期值

解除暂停状态
postgres=# select pg_wal_replay_resume();
 pg_wal_replay_resume
----------------------

(1 row)

postgres=#
这时，recovery.conf文件名字变成了recovery.done可以移除
 

查看postgreSQL日志：

more /var/lib/pgsql/11/data/log/postgresql-Sun.log

2019-07-14 18:09:34.161 CST [11188] LOG:  database system was interrupted; last known up at 2019-07-14 18:00:03 CST
2019-07-14 18:09:34.194 CST [11188] LOG:  starting point-in-time recovery to 2019-07-14 17:40:34+08
2019-07-14 18:09:34.201 CST [11188] LOG:  restored log file "00000002.history" from archive
2019-07-14 18:09:34.230 CST [11188] LOG:  restored log file "000000020000000000000013" from archive
2019-07-14 18:09:34.256 CST [11188] LOG:  redo starts at 0/13000028
2019-07-14 18:09:34.257 CST [11188] LOG:  consistent recovery state reached at 0/130000F8
2019-07-14 18:09:34.257 CST [11186] LOG:  database system is ready to accept read only connections
2019-07-14 18:09:34.281 CST [11188] LOG:  restored log file "000000020000000000000014" from archive
2019-07-14 18:09:34.295 CST [11188] LOG:  recovery stopping before commit of transaction 732, time 2019-07-14 18:00:28.482788+08
2019-07-14 18:09:34.295 CST [11188] LOG:  recovery has paused
2019-07-14 18:09:34.295 CST [11188] HINT:  Execute pg_wal_replay_resume() to continue.
2019-07-14 18:10:34.239 CST [11186] LOG:  received fast shutdown request
2019-07-14 18:10:34.240 CST [11186] LOG:  aborting any active transactions
2019-07-14 18:10:34.244 CST [11191] LOG:  shutting down
2019-07-14 18:10:34.257 CST [11186] LOG:  database system is shut down
2019-07-14 18:11:39.995 CST [11261] LOG:  database system was shut down in recovery at 2019-07-14 18:10:34 CST
2019-07-14 18:11:39.996 CST [11261] LOG:  starting point-in-time recovery to 2019-07-14 18:02:34+08
2019-07-14 18:11:40.002 CST [11261] LOG:  restored log file "00000002.history" from archive
2019-07-14 18:11:40.029 CST [11261] LOG:  restored log file "000000020000000000000013" from archive
2019-07-14 18:11:40.052 CST [11261] LOG:  redo starts at 0/13000028
2019-07-14 18:11:40.074 CST [11261] LOG:  restored log file "000000020000000000000014" from archive
2019-07-14 18:11:40.092 CST [11261] LOG:  consistent recovery state reached at 0/140009C0
2019-07-14 18:11:40.092 CST [11261] LOG:  recovery stopping before commit of transaction 735, time 2019-07-14 18:04:28.992452+08
2019-07-14 18:11:40.092 CST [11261] LOG:  recovery has paused
2019-07-14 18:11:40.092 CST [11261] HINT:  Execute pg_wal_replay_resume() to continue.
2019-07-14 18:11:40.093 CST [11259] LOG:  database system is ready to accept read only connections
2019-07-14 18:12:20.815 CST [11269] ERROR:  cannot execute INSERT in a read-only transaction
2019-07-14 18:12:20.815 CST [11269] STATEMENT:  INSERT INTO test values(default,default);
2019-07-14 18:13:31.877 CST [11259] LOG:  received fast shutdown request
2019-07-14 18:13:31.878 CST [11259] LOG:  aborting any active transactions
2019-07-14 18:13:31.881 CST [11265] LOG:  shutting down
2019-07-14 18:13:31.893 CST [11259] LOG:  database system is shut down
2019-07-14 18:13:38.425 CST [11294] LOG:  database system was shut down in recovery at 2019-07-14 18:13:31 CST
2019-07-14 18:13:38.426 CST [11294] LOG:  database system was not properly shut down; automatic recovery in progress
2019-07-14 18:13:38.428 CST [11294] LOG:  redo starts at 0/13000028
2019-07-14 18:13:38.428 CST [11294] LOG:  redo done at 0/14000D80
2019-07-14 18:13:38.428 CST [11294] LOG:  last completed transaction was at log time 2019-07-14 18:04:28.992452+08
2019-07-14 18:13:38.471 CST [11292] LOG:  database system is ready to accept connections
-bash-4.2$