PostgreSQL PITR实现时间点误删除恢复功能

┌────────────────────────────────────────────────────┐
│  ① 基础备份（base backup）                           │
│     → 某个时刻的全库物理快照（如凌晨2:00）              │
│                                                    │
│  ② wal/归档日志持续记录（2:00 → 故障时刻）            │
│     → 每条数据修改都先写日志，再写数据页               │
│     → 日志实时归档到独立存储                           │
│                                                    │
│  ③ 重放日志到目标时间点 → 停止 → 恢复完成             │
│     → 比如停在 9:59:59（误删之前1秒）                 │
└────────────────────────────────────────────────────┘

2:00  ──── 全库物理备份（pg_basebackup）
2:00~10:00 ── wal日志持续归档（每5分钟切换一个文件）
10:00 ──── ❌ 运维误删 orders 表
10:01 ──── 决定恢复到 9:59:59

恢复过程：
  还原2:00的全备 → 从归档拉取2:00~9:59:59的wal → 逐条重放 → 停在9:59:59

结果：orders表数据完好，丢失几乎为0（rpo≈1秒）

#在pg服务器开启归档
[root@pgserver ~]#vim /apps/pgsql/data/postgresql.conf
archive_mode = on
archive_command = 'test ! -f /apps/pgsql/archive/%f && cp %p /apps/pgsql/archive/%f'
[root@pgserver ~]#su - postgres
[postgres@pgserver ~]$pg_ctl restart -d /apps/pgsql/data/
#在pg服务器上创建测试数据
postgres=# create database testdb;
postgres=# \c testdb
testdb=# create table t1(id int);
testdb=# insert into t1 values(1);
testdb=# select * from t1;
 id 
----
  1
testdb=# show listen_addresses;
 listen_addresses 
------------------
 127.0.0.1
#修改配置文件
[postgres@pgserver ~]$vim /apps/pgsql/data/postgresql.conf
listen_addresses = '*'
#重启主库
[postgres@pgserver ~]$pg_ctl restart -d /apps/pgsql/data/
testdb=# show listen_addresses;
 listen_addresses 
------------------
 *
#确认端口在监听
[postgres@pgserver ~]$ss -tlnp | grep 5432
listen 0      100          0.0.0.0:5432      0.0.0.0:*    users:(("postgres",pid=1861,fd=7))
listen 0      100             [::]:5432         [::]:*    users:(("postgres",pid=1861,fd=8))
[postgres@pgserver ~]$cat /apps/pgsql/data/pg_hba.conf | grep -v "^#" | grep -v "^$"
local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
local   replication     all                                     md5
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5
host    replication     postgres        10.0.0.28/32            md5
#reload 配置
[postgres@pgserver ~]$pg_ctl reload -d /apps/pgsql/data/
[postgres@pgserver ~]$firewall-cmd --list-ports
firewalld is not running
[postgres@pgserver ~]$ll /apps/pgsql/data/postgresql.conf.bak.20260916
-rw------- 1 root root 31029 sep 16 12:17 /apps/pgsql/data/postgresql.conf.bak.20260916
[postgres@pgserver ~]$rm -f /apps/pgsql/data/postgresql.conf.bak.20260916
#在备份服务器对pg数据库进行远程完全备份
[root@backup ~]#su - postgres
[postgres@backup ~]$pg_basebackup -d /apps/pgsql/backup/ -ft -pv -u postgres -h 10.0.0.18 -p 5432 -r
password: 
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/28000028 on timeline 1
pg_basebackup: starting background wal receiver
pg_basebackup: created temporary replication slot "pg_basebackup_1973"
warning:  aborting backup due to backend exiting before pg_backup_stop was called
    0/78992 kb (100%), 1/1 tablespace                                         
pg_basebackup: error: backup failed: error:  could not open file "./postgresql.conf.bak.20260916": permission denied
pg_basebackup: removing contents of data directory "/apps/pgsql/backup/"
#彻底清理
# 1. 退出 postgres，切回 root
[postgres@backup ~]$exit
# 2. 彻底清空 backup 目录
[root@backup ~]#rm -rf /apps/pgsql/backup/*
# 3. 重建空目录，权限确保正确
[root@backup ~]#mkdir -p /apps/pgsql/backup
[root@backup ~]#chown postgres:postgres /apps/pgsql/backup
[root@backup ~]#chmod 700 /apps/pgsql/backup
# 4. 确认目录干净
[root@backup ~]#ls -la /apps/pgsql/backup/
[postgres@backup ~]$find /apps/pgsql/data/ -name "postgresql.conf.bak*"
/apps/pgsql/data/postgresql.conf.bak.20260916
#现在执行备份
[root@backup ~]#su - postgres
[postgres@backup ~]$pg_basebackup -d /apps/pgsql/backup/ -ft -pv -u postgres -h 10.0.0.18 -p 5432 -r
[postgres@backup ~]$pg_basebackup -d /apps/pgsql/backup/ -ft -pv -u postgres -h 10.0.0.18 -p 5432 -r
password: 
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2c000028 on timeline 1
pg_basebackup: starting background wal receiver
pg_basebackup: created temporary replication slot "pg_basebackup_2053"
78972/78972 kb (100%), 1/1 tablespace                                         
pg_basebackup: write-ahead log end point: 0/2c000120
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
#验证备份
# 看备份目录结构
[postgres@backup ~]$ls -la /apps/pgsql/backup/
total 95552
drwx------  2 postgres postgres       63 oct  2 22:37 .
drwxr-xr-x 10 postgres postgres      108 aug 18 17:25 ..
-rw-------  1 postgres postgres   189784 oct  2 22:37 backup_manifest
-rw-------  1 postgres postgres 80868864 oct  2 22:37 base.tar
-rw-------  1 postgres postgres 16778752 oct  2 22:37 pg_wal.tar
#验证 tar 里面有没有恢复配置
[postgres@backup ~]$tar -tf /apps/pgsql/backup/base.tar | grep -e "standby|postgresql"
postgresql.auto.conf
postgresql.conf
standby.signal
#在pg服务器上继续生成测试数据
testdb=# insert into t1 values(2);
#模拟数据库删除
testdb=# \c postgres
postgres=# drop database testdb;
#发现故障，停止用户访问
#查看当前日志文件
postgres=# select pg_walfile_name(pg_current_wal_lsn());
     pg_walfile_name      
--------------------------
 00000001000000000000002d
#查看当前事务id
postgres=# select txid_current();
 txid_current 
--------------
          839

#在pg服务器上切换归档日志
postgres=# select pg_switch_wal();
 pg_switch_wal 
---------------
 0/2d0006f0
#在要还原的服务器停止服务，准备还原
[postgres@pgserver ~]$pg_ctl stop -d /apps/pgsql/data/
[postgres@pgserver ~]$rm -rf /apps/pgsql/data/*
[postgres@pgserver ~]$chown -r postgres:postgres /apps/pgsql/data/
[postgres@pgserver ~]$chmod 700 /apps/pgsql/data/
#复制pg服务器的归档日志到还原的测试服务器
[postgres@pgserver ~]$rsync -a 10.0.0.28:/apps/pgsql/archive/ /apps/pgsql/archive/
postgres=# \c testdb
testdb=# select * from t1;
 id 
----
  1
  3
[postgres@pgserver ~]$pg_controldata
pg_control version number:            1700
catalog version number:               202406281
database system identifier:           7675300641084329406
database cluster state:               in production
#恢复正常写入
testdb=# insert into t1 values(4);
testdb=# select * from t1;
 id 
----
  1
  3
  4
testdb=# select count(*), sum(id) from t1;
 count | sum 
-------+-----
     3 |   8

#在 backup机器上检查恢复状态
postgres=# select pg_is_in_recovery();
 pg_is_in_recovery 
-------------------
 f					#已退出恢复模式，变成独立主库
(1 row)
postgres=# select pg_last_wal_receive_lsn();
 pg_last_wal_receive_lsn 
-------------------------
 						#主库停了，不再接收									
(1 row)
postgres=# select pg_last_wal_replay_lsn();
 pg_last_wal_replay_lsn 
------------------------
 0/24084fd0			#已回放完所有 wal
(1 row)
postgres=# select txid_current();
 txid_current 
--------------
          834			#稳定，不再增长
#standby 已追上主库，自动提升为主库
#在测试的还原服务器进行还原
[postgres@backup ~]$tar xf /apps/pgsql/backup/base.tar -c /apps/pgsql/data/
[postgres@backup ~]$tar xf /apps/pgsql/backup/pg_wal.tar -c /apps/pgsql/archive/
#查看故障点事务id
[postgres@backup ~]$pg_waldump /apps/pgsql/archive/00000001000000000000002c
rmgr: xlog        len (rec/tot):     30/    30, tx:          0, lsn: 0/2c000028, prev 0/2b000130, desc: checkpoint_redo wal_level replica
rmgr: standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/2c000048, prev 0/2c000028, desc: running_xacts nextxid 837 latestcompletedxid 836 oldestrunningxid 837
rmgr: xlog        len (rec/tot):    114/   114, tx:          0, lsn: 0/2c000080, prev 0/2c000048, desc: checkpoint_online redo 0/2c000028; tli 1; prev tli 1; fpw true; wal_level replica; xid 0:837; oid 40973; multi 1; offset 0; oldest xid 731 in db 1; oldest multi 1 in db 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 837; online
rmgr: xlog        len (rec/tot):     34/    34, tx:          0, lsn: 0/2c0000f8, prev 0/2c000080, desc: backup_end 0/2c000028
rmgr: xlog        len (rec/tot):     24/    24, tx:          0, lsn: 0/2c000120, prev 0/2c0000f8, desc: switch 
#修改配置文件postgresql.conf,或者postgresql.auto.conf文件也可以
[postgres@backup ~]$vim /apps/pgsql/data/postgresql.conf
#加下面三行
restore_command = 'cp /apps/pgsql/archive/%f %p'
recovery_target_xid = '838'
recovery_target_action = 'promote'
[postgres@backup ~]$pg_ctl stop -d /apps/pgsql/data/ -m fast
[postgres@backup ~]$rm -f /apps/pgsql/data/postmaster.pid
#启动服务
[postgres@backup ~]$pg_ctl start -d /apps/pgsql/data/
#检查恢复日志
2026-10-03 00:41:48.527 cst [2713] fatal:  could not connect to the primary server: connection to server at "10.0.0.18", port 5432 failed: connection refused
		is the server running on that host and accepting tcp/ip connections?
cp: cannot stat '/apps/pgsql/archive/00000002.history': no such file or directory
2026-10-03 00:41:48.534 cst [2566] log:  waiting for wal to become available at 0/2d000018
qcp: cannot stat '/apps/pgsql/archive/00000001000000000000002d': no such file or directory
2026-10-03 00:41:53.535 cst [2716] fatal:  could not connect to the primary server: connection to server at "10.0.0.18", port 5432 failed: connection refused
		is the server running on that host and accepting tcp/ip connections?
cp: cannot stat '/apps/pgsql/archive/00000002.history': no such file or directory
2026-10-03 00:41:53.541 cst [2566] log:  waiting for wal to become available at 0/2d000018
^c
[postgres@backup ~]$tail -f /apps/pgsql/log/postgresql-*.log
#停止当前恢复进程
[postgres@backup ~]$pg_ctl stop -d /apps/pgsql/data/ -m immediate
#先确认 wal 文件在哪
[postgres@backup ~]$ls -l /apps/pgsql/data/pg_wal/00000001000000000000002d 2>/dev/null
-rw------- 1 postgres postgres 16777216 sep 16 19:58 /apps/pgsql/data/pg_wal/00000001000000000000002d
# wal 文件在 pg_wal 目录，直接修复配置
#修改 postgresql.conf
[postgres@backup ~]$vim /apps/pgsql/data/postgresql.conf
restore_command = 'cp /apps/pgsql/data/pg_wal/%f %p'
recovery_target_xid = '838'
recovery_target_action = 'promote'
[postgres@backup ~]$pg_ctl start -d /apps/pgsql/data/
#验证数据
postgres=# \c testdb
testdb=# select * from t1;
 id 
----
  1
#清理恢复配置
#删除或注释以下行：
#restore_command = 'cp /apps/pgsql/data/pg_wal/%f %p'
#recovery_target_xid = '838'
#recovery_target_action = 'promote'
#重启
[root@backup ~]#su - postgres
[postgres@backup ~]$pg_ctl restart -d /apps/pgsql/data/
#当前无法写入
testdb=# insert into t1 values(3);
error:  cannot execute insert in a read-only transaction
[postgres@backup ~]$pg_controldata
pg_control version number:            1700
catalog version number:               202406281
database system identifier:           7675300641084329406
database cluster state:               in archive recovery
#恢复正常模式
testdb=# select pg_wal_replay_resume();
 pg_wal_replay_resume 
----------------------
[postgres@backup ~]$tar -tf /apps/pgsql/backup/base.tar | grep -e "standby|postgresql"
postgresql.auto.conf
postgresql.conf
standby.signal
# 删除 standby.signal
[postgres@backup ~]$rm -f /apps/pgsql/data/standby.signal
#重启
[postgres@backup ~]$pg_ctl restart -d /apps/pgsql/data/
#验证
testdb=# insert into t1 values(3);
insert 0 1
testdb=# select * from t1;
 id 
----
  1
  3
[postgres@backup ~]$pg_controldata
pg_control version number:            1700
catalog version number:               202406281
database system identifier:           7675300641084329406
database cluster state:               in production
testdb=# select * from t1;
 id 
----
  1
  3
  4
testdb=# select count(*), sum(id) from t1;
 count | sum 
-------+-----
     3 |   8
#数据完整，恢复成功