登录
当前位置： 首页 > 工具软件 > pg_probackup > 使用案例 >

pg_probackup 之四 restore

慕云
2023-12-01

os: centos 7.4
db: postgresql 11.5
pg_probackup: 2.2.7

pg_probackup 是 postgres pro 一款优秀的备份还原工具，目前兼容 PostgreSQL 9.5, 9.6, 10, 11;

有时候在生产环境误删除了表，或者误删除了数据，再或者想恢复个生产库做些功能测试，就需要做异机恢复。

版本

# cat /etc/centos-release
CentOS Linux release 7.4.1708 (Core) 
#
# pg_probackup --version
pg_probackup 2.2.7 (PostgreSQL 11.5)
#
# su - postgres
Last login: Wed Mar  4 17:02:54 CST 2020 on pts/2

$ psql -c "select version();"
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 11.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
(1 row)

$ psql -c "\dx"
                            List of installed extensions
      Name       | Version |   Schema   |                Description                 
-----------------+---------+------------+--------------------------------------------
 amcheck         | 1.1     | public     | functions for verifying relation integrity
 plpgsql         | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

模拟原机误删除表

当前备份情况

# su - postgres
$ pg_probackup validate -B /var/lib/pgsql/pg_probackup_dir
$ pg_probackup show -B /var/lib/pgsql/pg_probackup_dir

BACKUP INSTANCE 'pg11'
====================================================================================================================================
 Instance  Version  ID      Recovery Time           Mode   WAL Mode  TLI  Time   Data   WAL  Zratio  Start LSN   Stop LSN    Status 
====================================================================================================================================
 pg11      11       Q6O08Q  2020-03-04 18:38:51+08  PAGE   ARCHIVE   1/1    2s  124kB  16MB    1.00  0/1E000028  0/1F0000B8  OK     
 pg11      11       Q6NYFO  2020-03-04 17:59:52+08  DELTA  ARCHIVE   1/1    5s   68MB  16MB    1.00  0/1C000028  0/1C000160  OK     
 pg11      11       Q6NYEY  2020-03-04 17:59:26+08  PAGE   ARCHIVE   1/1    5s   68MB  16MB    1.00  0/15000028  0/160000B8  OK     
 pg11      11       Q6NYDK  2020-03-04 17:58:38+08  PAGE   ARCHIVE   1/1    7s   67MB  16MB    1.00  0/E000028   0/F0000F0   OK     
 pg11      11       Q6NYCA  2020-03-04 17:57:48+08  PAGE   ARCHIVE   1/1    3s  212kB  16MB    1.00  0/6000028   0/70000B8   OK     
 pg11      11       Q6NXZ6  2020-03-04 17:50:00+08  FULL   ARCHIVE   1/0    7s   39MB  16MB    1.00  0/4000028   0/4000160   OK

模拟误删除表

$ psql
psql (11.5)
Type "help" for help.

postgres=# \d
              List of relations
 Schema |     Name     |   Type   |  Owner   
--------+--------------+----------+----------
 public | tmp_t0       | table    | postgres
(1 rows)

postgres=# \! date
Wed Mar  4 21:17:56 CST 2020
postgres=# drop table tmp_t0;
DROP TABLE
postgres=# \! date
Wed Mar  4 21:18:10 CST 2020

现在需要恢复到表删除之前的这个时间点 Wed Mar 4 21:17:56 CST 2020

$ pg_probackup validate -B /var/lib/pgsql/pg_probackup_dir --instance pg11 --recovery-target-time='2020-03-04 21:17:56+08' 

INFO: Validating parents for backup Q6O08Q
INFO: Validating backup Q6NXZ6
INFO: Backup Q6NXZ6 data files are valid
INFO: Validating backup Q6NYCA
INFO: Backup Q6NYCA data files are valid
INFO: Validating backup Q6NYDK
INFO: Backup Q6NYDK data files are valid
INFO: Validating backup Q6NYEY
INFO: Backup Q6NYEY data files are valid
INFO: Validating backup Q6NYFO
INFO: Backup Q6NYFO data files are valid
INFO: Validating backup Q6O08Q
INFO: Backup Q6O08Q data files are valid
WARNING: Thread [1]: Could not read WAL record at 0/20000000
ERROR: Thread [1]: WAL segment "/var/lib/pgsql/pg_probackup_dir/wal/pg11/000000010000000000000020" is absent
WARNING: Recovery can be done up to time 2020-03-04 18:38:51+08, xid 716 and LSN 0/1F0000B8
ERROR: Not enough WAL records to time 2020-03-04 21:17:56+08

提示 ERROR ，因为备份集的 WAL 只能恢复到 2020-03-04 21:17:56+08，需要使用到当前的 WAL，要做个 switch wal

$ psql -c "select pg_switch_wal();"
$ pg_probackup validate -B /var/lib/pgsql/pg_probackup_dir --instance pg11 --recovery-target-time='2020-03-04 21:17:56+08' 

INFO: Validating parents for backup Q6O08Q
INFO: Validating backup Q6NXZ6
INFO: Backup Q6NXZ6 data files are valid
INFO: Validating backup Q6NYCA
INFO: Backup Q6NYCA data files are valid
INFO: Validating backup Q6NYDK
INFO: Backup Q6NYDK data files are valid
INFO: Validating backup Q6NYEY
INFO: Backup Q6NYEY data files are valid
INFO: Validating backup Q6NYFO
INFO: Backup Q6NYFO data files are valid
INFO: Validating backup Q6O08Q
INFO: Backup Q6O08Q data files are valid
INFO: Backup validation completed successfully on time 2020-03-04 21:18:03+08, xid 716 and LSN 0/200097D0
INFO: Backup Q6O08Q is valid.
INFO: Validate of backup Q6O08Q completed.

没有问题，可以利用这些备份集进行异机恢复了。

异机恢复

另外一台机器需要安装 postgresql 11 软件，及 pg_probackup 2.2.7

# su - postgres
$ 
$ which pg_ctl
/usr/pgsql-11/bin/pg_ctl
$ pg_ctl --version
pg_ctl (PostgreSQL) 11.5
$ 
$ which pg_probackup
/bin/pg_probackup
$ pg_probackup --version
pg_probackup 2.2.7 (PostgreSQL 11.5)

所有的操作都异机在 /temp 下完成

异机创建 PGDATA 目录

$ cd /tmp
$ mkdir pgsql11data;
chmod 700 /tmp/pgsql11data ;

创建 pg_probackup 初始化目录

$ cd /tmp
$ mkdir pgprobackupdata
$ pg_probackup init -D /tmp/pgsql11data -B /tmp/pgprobackupdata
$ mkdir -p ./pgprobackupdata/backups/pg11;
mkdir -p ./pgprobackupdata/wal/pg11;

backups 及 wal 目录下的那个 pg11 文件代表的是 pg_probackup 时指定的 instance_name，需要和原库保持一致。

原机上的备份及WAL

需要将原机上的备份及WAL拷贝到异机的指定目录。
以下操作在原机上操作

# su - postgres
$ pwd
/var/lib/pgsql

$ cd /var/lib/pgsql/pg_probackup_dir/backups/pg11
$ tar -zcvf ./backup.tar.gz ./*
$ scp ./backup.tar.gz postgres@192.168.56.111:/tmp/pgprobackupdata/backups/pg11

$ cd /var/lib/pgsql/pg_probackup_dir/wal/pg11
$ tar -zcvf ./wal.tar.gz ./*
$ scp ./wal.tar.gz postgres@192.168.56.111:/tmp/pgprobackupdata/wal/pg11

异机解压相关的文件

# su - postgres
$ cd /tmp
$ cd /tmp/pgprobackupdata/backups/pg11
$ tar -zxvf ./backup.tar.gz


$ cd /tmp/pgprobackupdata/wal/pg11
$ tar -zxvf ./wal.tar.gz

异机验证恢复

$ pg_probackup show -B /tmp/pgprobackupdata

BACKUP INSTANCE 'pg11'
====================================================================================================================================
 Instance  Version  ID      Recovery Time           Mode   WAL Mode  TLI  Time   Data   WAL  Zratio  Start LSN   Stop LSN    Status 
====================================================================================================================================
 pg11      11       Q6O08Q  2020-03-04 18:38:51+08  PAGE   ARCHIVE   1/1    2s  124kB  16MB    1.00  0/1E000028  0/1F0000B8  OK     
 pg11      11       Q6NYFO  2020-03-04 17:59:52+08  DELTA  ARCHIVE   1/1    5s   68MB  16MB    1.00  0/1C000028  0/1C000160  OK     
 pg11      11       Q6NYEY  2020-03-04 17:59:26+08  PAGE   ARCHIVE   1/1    5s   68MB  16MB    1.00  0/15000028  0/160000B8  OK     
 pg11      11       Q6NYDK  2020-03-04 17:58:38+08  PAGE   ARCHIVE   1/1    7s   67MB  16MB    1.00  0/E000028   0/F0000F0   OK     
 pg11      11       Q6NYCA  2020-03-04 17:57:48+08  PAGE   ARCHIVE   1/1    3s  212kB  16MB    1.00  0/6000028   0/70000B8   OK     
 pg11      11       Q6NXZ6  2020-03-04 17:50:00+08  FULL   ARCHIVE   1/0    7s   39MB  16MB    1.00  0/4000028   0/4000160   OK     


$ pg_probackup validate -D /tmp/pgsql11data/ -B /tmp/pgprobackupdata/ --instance pg11 --recovery-target-time='2020-03-04 21:17:56+08' 

INFO: Validating parents for backup Q6O08Q
INFO: Validating backup Q6NXZ6
INFO: Backup Q6NXZ6 data files are valid
INFO: Validating backup Q6NYCA
INFO: Backup Q6NYCA data files are valid
INFO: Validating backup Q6NYDK
INFO: Backup Q6NYDK data files are valid
INFO: Validating backup Q6NYEY
INFO: Backup Q6NYEY data files are valid
INFO: Validating backup Q6NYFO
INFO: Backup Q6NYFO data files are valid
INFO: Validating backup Q6O08Q
INFO: Backup Q6O08Q data files are valid
INFO: Backup validation completed successfully on time 2020-03-04 21:18:03+08, xid 716 and LSN 0/200097D0
INFO: Backup Q6O08Q is valid.
INFO: Validate of backup Q6O08Q completed.


$ pg_probackup restore -D /tmp/pgsql11data/ -B /tmp/pgprobackupdata/ --instance pg11 --recovery-target-time='2020-03-04 21:17:56+08' 

INFO: Validating parents for backup Q6O08Q
INFO: Validating backup Q6NXZ6
INFO: Backup Q6NXZ6 data files are valid
INFO: Validating backup Q6NYCA
INFO: Backup Q6NYCA data files are valid
INFO: Validating backup Q6NYDK
INFO: Backup Q6NYDK data files are valid
INFO: Validating backup Q6NYEY
INFO: Backup Q6NYEY data files are valid
INFO: Validating backup Q6NYFO
INFO: Backup Q6NYFO data files are valid
INFO: Validating backup Q6O08Q
INFO: Backup Q6O08Q data files are valid
INFO: Backup validation completed successfully on time 2020-03-04 21:18:03+08, xid 716 and LSN 0/200097D0
INFO: Backup Q6O08Q is valid.
INFO: Restore of backup Q6O08Q completed.

异机恢复数据库

自动生成了 recovery.conf 文件

$ cd /tmp/pgprobackupdata
$ cat recovery.conf 
# recovery.conf generated by pg_probackup 2.2.7
recovery_target_time = '2020-03-04 21:17:56+08'
recovery_target_action = 'pause'
restore_command = '/usr/bin/pg_probackup-11 archive-get -B /tmp/pgprobackupdata --instance pg11 --wal-file-path=%p --wal-file-name=%f'

修改参数

$ vi postgresql.conf

data_directory = '/tmp/pgsql11data'
hba_file = '/tmp/pgsql11data/pg_hba.conf'
ident_file = '/tmp/pgsql11data/pg_ident.conf'
port=5433
archive_mode = off
archive_command = ''
archive_timeout = 0

启动pgsql

$ /usr/pgsql-11/bin/pg_ctl start -D /tmp/pgsql11data/

waiting for server to start....2020-03-04 22:34:59.914 CST [2961] LOG:  listening on IPv4 address "0.0.0.0", port 5433
2020-03-04 22:34:59.914 CST [2961] LOG:  listening on IPv6 address "::", port 5433
2020-03-04 22:34:59.916 CST [2961] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433"
2020-03-04 22:34:59.921 CST [2961] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5433"
2020-03-04 22:34:59.961 CST [2961] LOG:  redirecting log output to logging collector process
2020-03-04 22:34:59.961 CST [2961] HINT:  Future log output will appear in directory "log".
. done
server started

查看日志，已经恢复到指定时间，并处于 pause 状态

$ tail -n 50 -f /tmp/pgsql11data/log/postgresql-Wed.log 
2020-03-04 22:34:59.965 CST [2963] LOG:  database system was interrupted; last known up at 2020-03-04 18:38:50 CST
2020-03-04 22:34:59.965 CST [2963] LOG:  creating missing WAL directory "pg_wal/archive_status"
2020-03-04 22:35:00.625 CST [2963] LOG:  starting point-in-time recovery to 2020-03-04 21:17:56+08
INFO: pg_probackup archive-get from /tmp/pgprobackupdata/wal/pg11/00000001000000000000001E to /tmp/pgsql11data/pg_wal/RECOVERYXLOG
INFO: pg_probackup archive-get completed successfully
2020-03-04 22:35:00.721 CST [2963] LOG:  restored log file "00000001000000000000001E" from archive
2020-03-04 22:35:00.729 CST [2963] LOG:  redo starts at 0/1E000028
INFO: pg_probackup archive-get from /tmp/pgprobackupdata/wal/pg11/00000001000000000000001F to /tmp/pgsql11data/pg_wal/RECOVERYXLOG
INFO: pg_probackup archive-get completed successfully
2020-03-04 22:35:00.850 CST [2963] LOG:  restored log file "00000001000000000000001F" from archive
2020-03-04 22:35:00.855 CST [2963] LOG:  consistent recovery state reached at 0/1F0000B8
2020-03-04 22:35:00.856 CST [2961] LOG:  database system is ready to accept read only connections
INFO: pg_probackup archive-get from /tmp/pgprobackupdata/wal/pg11/000000010000000000000020 to /tmp/pgsql11data/pg_wal/RECOVERYXLOG
INFO: pg_probackup archive-get completed successfully
2020-03-04 22:35:00.958 CST [2963] LOG:  restored log file "000000010000000000000020" from archive
2020-03-04 22:35:00.962 CST [2963] LOG:  recovery stopping before commit of transaction 716, time 2020-03-04 21:18:03.525552+08
2020-03-04 22:35:00.962 CST [2963] LOG:  recovery has paused
2020-03-04 22:35:00.962 CST [2963] HINT:  Execute pg_wal_replay_resume() to continue.

异机登录数据库

$ psql -p 5433
psql (11.5)
Type "help" for help.

postgres=# \d
              List of relations
 Schema |     Name     |   Type   |  Owner   
--------+--------------+----------+----------
 public | tmp_t0       | table    | postgres
(1 rows)

nice

参考：

https://github.com/postgrespro/pg_probackup
https://postgrespro.com/products/extensions/pg_probackup

https://github.com/postgrespro/pg_probackup/blob/master/Documentation.md
https://postgrespro.github.io/pg_probackup/
https://postgrespro.com/docs/postgrespro/11/app-pgprobackup

 类似资料：

相关阅读

Flutter进阶之实现动画效果（四）KnockoutJS 3.X API 第四章之click绑定SpringBoot四大神器之Actuator的使用小结我的Node.js学习之路（四）--单元测试如何在Java中将时间四舍五入到最近的四分之一小时？

相关文章

秋招面经分享之拼多多产品运营（四）DBMS第四范式（4NF）8.0 Zookeeper 四字命令第四范式二面第四范式前端

相关问答

LWJGL-未渲染四边形-四舍五入双向上Java怎么把数字四舍五入到小数点后四位？[重复]阿帕奇四郎与泽西如何使用四郎的SaltedAuthenticationInfo？

相关文档

第四范式智能客服Android 控件操作 二十四章经结构之法算法之道Flask 之旅Python 之旅
快捷导航： 新手教程 算法原理 架构设计 Java进阶 数据库进阶 大厂专栏 面试经验 编程笔记 编程问答 所有专题 文档资料 工具软件 电子书籍 小牛导航
在线工具： 房贷计算器 个税计算器 Linux命令查询 Json格式化 正则表达式 颜色转换 AES加解密 SHA1加密 MD5加密 毒鸡汤 字数统计 随机密码生成 进制转换 Base64编解码 励志句子
Copyright © 2019-2024 小牛知识库@xnip.cn. All Rights Reserved.