百木园作者：颜博 青云科技数据库研发工程师
目前从事 PostgreSQL 产品开发工作，热衷于 PostgreSQL 数据库的学习和研究
在数据库系统中，数据是一切的基础，数据的安全更是重中之重。但可能因为各种原因导致数据丢失或损坏，因此数据的备份和恢复便显得尤为重要。
PostgreSQL 是一个强大的开源对象关系数据库系统，经过 30 多年的积极开发，在可靠性、功能稳健性和性能方面赢得了良好的声誉。Point-In-Time Recovery（简称 PITR） 是 PostgreSQL 的基于时间点的数据恢复技术，在开启 WAL 日志归档及基础备份后，可以即时恢复用户误操作丢失的数据，为您的数据库加上一重\"保险\"。
本文将演示 PostgreSQL 如何配置 PITR 数据备份，以及当数据被误删除后如何进行数据即时恢复。
｜环境准备
准备数据库
准备 PostgreSQL 11 版数据库环境。
# echo \"$PGHOME\" 查看
PGHOME=/usr/lib/postgresql/11
# echo \"$PGDATA\" 查看，PostgreSQL 的数据目录
PGDATA=/data/pgsql/main
# pg 命令行执行 show config_file; 命令查看，PostgreSQL 的配置文件位置
PGCONFIG_FILE=/etc/postgresql/11/main/postgresql.conf
# PostgreSQL 的启动日志位置，当启动失败时，可以通过日志快速查看报错内容
PG_LOG=/data/pglog/start.log
｜PITR 数据备份
当用户误删除数据之后，可以通过 PITR 恢复指定的时间戳的数据。下面介绍开启备份及恢复数据的详细步骤。
开启 WAL 日志归档
通过修改配置文件，开启 WAL 日志归档功能。
# 创建保存wal日志归档的目录，并修改目录拥有者
$ mkdir -p /data/wallog_back
$ chown -R postgres:postgres /data/wallog_back
# 修改 PostgreSQL 数据库配置文件 postgresql.conf
$ vim /etc/postgresql/11/main/postgresql.conf
# 修改如下配置项：
wal_level = archive
archive_mode = on
archive_command = \'test ! -f /data/wallog_back/%f && cp %p /data/wallog_back/%f\' # 配置归档命令，拷贝 wal 文件到指定目录，%p 为实际的 wal 文件目录，%f 为 wal 文件名称
# 保存后重启数据库
root:~# su postgres
# stop postgresql server
postgres:~$ /usr/lib/postgresql/11/bin/pg_ctl stop
# start postgresql server
postgres:~$ /usr/lib/postgresql/11/bin/pg_ctl -D /data/pgsql/main/ start
-o \'-c config_file=/etc/postgresql/11/main/postgresql.conf\' -l /data/pglog/start.log
成功保存配置修改，即打开了 WAL 日志归档功能。
准备数据
# 创建测试表
CREATE TABLE test_table(
ID INT NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL
);
# 插入测试数据。通过以下命令，快速插入 200001 条测试数据
insert into test_table(id, name, age)
values(
generate_series(0,200000),
substr(md5(random()::text), 0, 25),
generate_series(0,200000)
);
执行基础备份
- PostgreSQL 查看当前时间，指定备份时的 lable。
postgres=# select now();
now
-------------------------------
2021-11-04 14:50:42.482253+08
(1 row)
- 开始备份
# PostgreSQL 命令行执行
postgres=# select pg_start_backup(\'backup_2021-11-04 14:50:42\');
pg_start_backup
-----------------
0/9000060
(1 row)
# 切换到 linux 命令行执行
cd /data
$ sudo tar -cvzf pgsql.tar pgsql
postgres=# select pg_stop_backup();
NOTICE: pg_stop_backup complete, all required WAL segments have been archived
pg_stop_backup
----------------
0/9000168
(1 row)
- 执行一次 WAL 切换，使得 WAL 日志归档
postgres=# select pg_switch_wal();
pg_switch_wal
---------------
0/A000078
(1 row)
- 查看归档状态，如果 archived_count 为 0 代表异常，需要查看配置文件是否修改成功或尝试重启 PostgreSQL 服务
postgres=# select * from pg_stat_archiver;
注意：
- 备份完成后，/data/wallog_back/ 目录下应该有相应的归档文件。
- select * from pg_stat_archiver;命令执行完成后，如果 archived_count 为 0，代表归档设置开启失败，请重新检查配置文件或重启服务。
｜PITR 数据恢复
模拟误删数据
# 删除 test_table 表中 id 大于 10000 的数据，模拟误删除数据的操作
postgres=# delete from test_table where id>10000;
DELETE 190000
# 查询出当前时间，根据此时间确定大概的恢复时间点
postgres=# select now();
now
-------------------------------
2021-11-04 14:56:17.452967+08
(1 row)
即时恢复数据
# 停止数据库运行
$ /usr/lib/postgresql/11/bin/pg_ctl stop
# 重命名 pgsql 目录
cd /data
mv pgsql pgsql_back
# 恢复备份包到 data 目录
sudo tar -zxvf pgsql.tar
# 配置 data 目录下的recovery.done文件，如果没有则创建。
restore_command=\'cp /data/wallog_back/%f %p\' # wal归档的目录
recovery_target_time=\'2021-11-04 14:56:17.452967\' # 恢复到此时刻，根据之前的 select now()判断恢复时刻。
recovery_target_timeline=\'latest\'
重新启动数据库
$ /usr/lib/postgresql/11/bin/pg_ctl -D /data/pgsql/main/ start -o \'-c config_file=/etc/postgresql/11/main/postgresql.conf\' -l /data/pglog/start.log
｜数据恢复验证
postgres=# select * from test_table;
postgres=# select count(*) from test_table;
count
--------
200001
(1 row)
如图所示，数据恢复到了 200001 条。
｜总结
PITR 在配置一定条件后，可以进行数据的恢复，在误删除数据后，可以帮助我们找回数据。
｜参考
- https://www.postgresql.org/
- https://blog.csdn.net/arcticJian/article/details/102746287
- https://my.oschina.net/Kenyon/blog/58112
来源：https://www.cnblogs.com/radondb/p/15513108.html
图文来源于网络，如有侵权请联系删除。