postgres数据库时间点恢复

背景：当数据库有问题的时候，比如说被删除数据，update没有带where等，需要恢复到故障时间点之前
测试版本：pg14
前提条件：

1. 要启用WAL归档，设置archive_mode为on
2. 需设置wal_level配置参数为replica或更高
3. 并且使用archive_command配置参数指定一个shell命令

# 修改后需要重启pg
archive_mode = on
archive_command = 'test ! -f /data/archive/%f && cp %p /data/archive/%f'
wal_level = replica

制作基础备份

# 备份本地数据库，如果是远端要添加-h
sudo -u postgres pg_basebackup -D /data/pgbk -Pv

pg_basebackup被用于获得一个正在运行的PostgreSQL数据库集簇的基础备份。获得这些备份不会影响数据库的其他客户端，并且可以被用于时间点恢复

插入数据

postgres=# create database test;
CREATE DATABASE
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# create table t1(id int);
CREATE TABLE
test=# insert into t1 values(1);
INSERT 0 1
test=# 

归档恢复

目前已通过命令 drop database test; 删除之前创建的数据库，下面进行恢复
目标恢复模式在归档的WAL全部回放或到达recovery_target时结束，不设置target默认全部回放，我们要恢复到指定时间点，所以要设置target

1. 停止pg服务。
2. 配置Recovery，/data/archive/%f是wal文件归档目录，recovery_target_xid是要回滚的xid号 归档恢复配置参考

# 在postgresql.conf里Recovery配置段
restore_command = 'cp /data/archive/%f "%p"'
# recovery_target_xid 通过搜索wal日志确认，因为是97921删除的数据库，恢复到97920即可
recovery_target_xid = '97920'

下面是xid搜索结果

# 搜索到 drop 数据库操作，在最新的wal日志查找
/usr/lib/postgresql/14/bin/pg_waldump /data/archive/000000010000000100000005|grep -i DROP
rmgr: Database    len (rec/tot):     38/    38, tx:      97921, lsn: 1/0512C378, prev 1/0512C300, desc: DROP dir 1663/22867

2. 把现在的数据目录移动到其他地方

mv /var/lib/postgresql/14/main /var/lib/postgresql/14/main-backup

3. 把备份的数据拷贝到现在的数据目录里。设置正确的权限，需在数据目录中建立名为recovery.signal的文件

mv /data/pgbk /var/lib/postgresql/14/main
touch /var/lib/postgresql/14/main/recovery.signal
find /var/lib/postgresql/14/main -type d -exec chmod 700 {} \;
find /var/lib/postgresql/14/main -type f -exec chmod 600 {} \;
chown -R postgres:postgres /var/lib/postgresql/14/main

4. 启动pg，目前pg服务是只读状态
5. 查看数据是否恢复，如果恢复，在pg客户端执行SELECT pg_wal_replay_resume(); 服务器将删除recovery.signal，恢复正常

值得注意的是恢复成功后，默认的是pause，只运行对数据库进行查询操作，可以使用pg_wal_replay_resume()恢复服务，如果这个恢复目标不是想要的停止点，那么关闭pg服务，将恢复目标重新设置改为一个稍后的xid并且重启以继续恢复
官方恢复文档参考