概述

PostgreSQL 提供了不同的方法来备份和恢复数据库，可以是某一时刻数据库快照的完整备份或增量备份，可以使用 SQL 转储或文件系统级别的备份，在增量备份的基础上还可以实现基于时间点恢复。 这里主要介绍增量备份和基于时间点恢复（PITR)

实现增量备份的思路是

1) 记录数据库系统的操作记录（WAL归档）

2) 在某一时刻进行一次完整的数据库备份

3) 需要恢复时,还原上一次完整的数据库备份,根据操作记录恢复数据库至指定的某个时刻(即可以实现PITR 时间点恢复)

一、增量备份

PostgreSQL 在做写入操作时，对数据文件做的任何修改信息，首先会写入WAL 日 志（预写日志），然后才会对数据文件做物理修改。 当数据库服务器掉电或意外宕机， PostgreSQL 在启动时会首先读取 WAL 日志，对数据文件进行恢复。 因此，从理论上讲， 如果我们有一个数据库的基础备份（也称为全备），再配合 WAL 日志，是可以将数据库恢复到任意时间点的。

1、修改wal_level参数

wal_level 参数可选的值有 minimal、 replica 和 logical ，从 minimal到 replica 再到 logical 级别， WAL 的级别依次增高，在 WAL 中包含的信息也越多。由于 minimal 这一级别的 WAL 不包含从基本的备份和 WAL 日志中重建数据的足够信息，在 minimal 模式下无法开启 archive_mode，所以开启 WAL 归档 wal_level 至少设置为 replica，如下所示 ：

--命令行
ALTER SYSTEM SET wal_level = 'replica';

--配置文件postgresql.conf
wal_level = replica

2、修改archive_mode参数

archive_mode 参数可选的值有 on、off 和 always，默认值为 off，开启归档需要修改为 on，重启生效。

archive_command 参数的默认值是个空字符串，它的值可以是一条 shell 命令或者一个复杂的 shell 脚本。 在 archive_command 的 shell 命令或脚本中可以用“%p”表示将要归档 的 WAL 文件的包含完整路径信息的文件名，用

“%f”代表不包含路径信息的 WAL 文件的文件名，修改后不需要重启，reload即可。

--linux环境
archive_mode = on             
archive_command = 'test ! -f  /data/pgarch/%f &&  cp %p  /data/pgarch/%f'

--windows环境
archive_mode = on             
archive_command = 'copy "%p" "D:\\ProgramFiles\\pgdata\\archive\\%f"'

3、基于pg_basebackup创建基础备份

--创建REPLICATION角色
CREATE ROLE replica login replication encrypted password 'replica@1234';

--配置pg_hba.conf，允许远程流式备份
echo "host  replication replica  0.0.0.0/0   md5" >> pg_hba.conf

--远程使用pg_basebackup备份
systemctl stop postgresql
rm -rf /data/pgdata/* && rm -rf /data/pgtablespace/*
#-Fp表示以plain格式数据，-Xs表示以stream方式包含所需的WAL文件，-P表示显示进度，-R表示为replication写配置信息。
#备份完成，使用-R选项，在data目录下自动生成standby.signal“信号”文件（可手工使用touch命令生成）以及更新了postgresql.auto.conf文件
#postgresql.auto.conf中写入了主库的连接信息（可手工添加primary_conninfo信息）。
pg_basebackup --progress -D /data/pgdata -h xx.142 -p 55432 -U replica --password  -Fp -Xs -P -R

二、PG12 PITR

恢复过程完成后，服务器将删除recovery.signal（以防止以后意外重新进入恢复模式），然后开始正常的数据库操作。

1、环境准备

create table hwb(a int);
insert into hwb values (generate_series(1,1000000));
select now();
-- 2020-12-15 15:16:03.645+08
select pg_switch_wal();
delete from hwb;
select now();
-- 2020-12-15 15:17:55.078991+08
select pg_switch_wal();

--传输归档日志到异地服务器恢复
scp -r /data/pgarch/*  postgres@172.16.1.143:/data/pgarch

2、恢复到指定时间点

--配置postgresql.conf(注释archive相关参数)
touch recovery.signal
cat >> postgresql.conf <<EOF
restore_command = 'cp /data/pgarch/%f %p'
recovery_target_time = '2020-12-15 15:16:03.645+08'
EOF

--启动数据库（这时候就恢复了）
systemctl start postgresql

--恢复正常使用
rm -rf recovery.signal
在 postgresql.conf注释restore相关参数后重启

3、恢复到最近时间点

cat > recovery.signal <<EOF
restore_command = 'cp /data/pgarch/%f %p'
recovery_target_timeline='latest'
EOF

配置文件 recovery.conf 添加上面两项。12 通过 recovery.signal 文件触发

4、恢复到指定还原点

postgres=# select pg_create_restore_point('my-restore-point1');
pg_create_restore_point
-------------------------
0/8000290
(1 row)
restore_command = 'cp /data/pgarch/%f %p'
recovery_target_name='my-restore-point1'

配置文件 recovery.conf 添加上面两项。12 通过 recovery.signal 文件触发

5、恢复到指定事务

select txid_current(); --查询当前事务 xid

restore_command = 'cp /data/pgarch/%f %p'
recovery_target_xid=723

配置文件 recovery.conf 添加上面两项。12 通过 recovery.signal 文件触发

6、恢复到指定时间线

restore_command = 'cp /data/pgarch/%f %p'
recovery_target_timeline=2
recovery_target_time='2020-12-15 15:15:00'

配置文件 recovery.conf 添加上面两项。12 通过 recovery.signal 文件触发

后面会分享更多devops和DBA方面内容，感兴趣的朋友可以关注下！