postgreSQL 11.4物理备份时间点恢复

深碍√TFBOYSˉ_ 2023-10-18 21:39 61阅读 0赞

创建相关管理目录  
\# 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$