1. 前言

• 本文介绍 PostgreSQL 实现 PITR 的条件及恢复流程，属于操作说明文档。

2. WAL 日志机制

2.1 WAL 日志简介

• 定义：Write Ahead Log（预写日志），用于保障事务的持久性和一致性。

• 对比其他数据库：

• MySQL：使用 Redo Log 实现事务持久性，PITR 需结合 Binlog（因多引擎架构限制）。

• PostgreSQL：纯事务引擎，WAL 可同时用于事务、主从同步和 PITR。

2.2 WAL 配置要求

需启用 WAL 归档以实现增量备份：

wal_level = replica       # 可选值：minimal, replica, logical
archive_mode = on         # 启用归档（需重启）
archive_command = 'cp %p /data/pgsql15/archive/%f && echo %f >> /data/pgsql15/archive/archive.list'

• 操作步骤：

1. 修改    postgresql.conf 。

2. 重启数据库：

pg_ctl -D /data/pgsql15/data/ -l /data/pgsql15/logs/start.log restart

3. pg_basebackup 工具

3.1 功能说明

• PostgreSQL 自带的物理热备工具，支持全实例备份（不支持单库/单表）。

• 用途：备库搭建、全量备份。

3.2 常用参数

3.3 示例命令

pg_basebackup -D /data/pgsql15/backup -v -P -Upostgres -h 127.0.0.1 -p 5432 -R

用 pg_basebackup 出来的备份，不会完整复制主库的所有配置文件。

cp /var/lib/pgsql/data/postgresql.conf /data/pgsql15/backup 
cp /var/lib/pgsql/data/pg_hba.conf /data/pgsql15/backup 
cp /var/lib/pgsql/data/pg_ident.conf /data/pgsql15/backup 

因为加了 -R 参数，所以在备份目录下自动生成了 standby.signal 。

standby.signal 的作用

• 从 PostgreSQL 12 开始，热备库（standby）的启动机制简化了。

• 以前需要 recovery.conf 文件，现在改为：

  • 数据目录下存在 standby.signal → 数据库启动时就进入 standby（物理复制模式），会去连接主库。

  • 如果没有这个文件，则正常作为 主库 启动。

换句话说：

• standby.signal 就是一个“开关”，告诉 PostgreSQL：“这是个从库，要去同步主库的数据”。

4. 全备恢复实验

4.1 执行全量备份

pg_basebackup -D /data/pgsql15/backup -v -P -Upostgres -h 127.0.0.1 -p 5432 -R
cp /var/lib/pgsql/data/postgresql.conf /data/pgsql15/backup 
cp /var/lib/pgsql/data/pg_hba.conf /data/pgsql15/backup 
cp /var/lib/pgsql/data/pg_ident.conf /data/pgsql15/backup 

4.2 模拟增量数据写入

-- 更新数据
UPDATE pgbench_accounts SET abalance = 100 WHERE bid = 41;


-- 记录当前 WAL LSN 和时间戳
SELECT pg_current_wal_lsn();  -- 示例输出：2/70D63FD0
SELECT now();                 -- 示例输出：2023-08-08 13:51:34.475891+08

4.3 模拟灾难恢复流程

步骤 1：停止数据库

pg_ctl -D /data/pgsql15/data/ stop

步骤 2：重命名数据目录

mv data data_bak          # 备份原数据
mv backup data            # 恢复备份
chmod 700 /data/pgsql15/data/  # 设置权限

步骤 3

步骤 3：删除standby.signal 文件

如果你只是想用备份来恢复一个新的主库：

1. 删除 standby.signal 文件（否则会一直当成从库）。

2. 根据需要修改 postgresql.conf（例如 port、listen_addresses 等）。

3. 启动 PostgreSQL：

rm -rf /data/pgsql15/data/standby.signal

步骤 4：修改恢复配置

编辑    postgresql.auto.conf ：

restore_command = 'cp /data/pgsql15/archive/%f %p > /data/pgsql15/archive/recovery.log 2>&1 '
# 通过 LSN 或时间点方式恢复 2 选 1
# recovery_target_lsn='2/70D63FD0'
recovery_target_time = '2023-08-08 13:51:34'
# 达到恢复目标后，数据的动作 promote 表示可以直接接受连接，测试使用，生产恢复建议使用 pause（默认值）确认数据恢复好后，手动删除 recovery.signal 文件即可。
recovery_target_action = 'promote'

步骤 5：启动恢复

pg_ctl -D /data/pgsql15/data start -l /data/pgsql15/logs/start.log

步骤 5：验证恢复结果

5. 部署备份任务计划（仅供参考）

5.1. 创建    backup.sh  备份脚本

[postgres@postgresql backupsets]$ cd /data/scripts/
[postgres@postgresql scripts]$ cat backup.sh
#!/bin/bash


# 定义变量
DATE=$(date +%Y%m%d)
BACKDIR=/data/backupsets/
REV_DATE=7


# 打印彩色日志函数
function green_echo(){
    echo -e "\e[40;32;1m$1\e[0m"
}


function red_echo(){
    echo -e "\e[40;31;1m$1\e[0m"
}


# 全量备份所有数据到以备份日期命名的文件夹
function backup(){
    pg_basebackup -D /data/backupsets/bkdata_$DATE/ -R -Ft -Pv -U postgres
}


# 开始备份流程
green_echo "Begin backup data At Date:`date`"
backup
green_echo "End backup data At Date:`date`"


# 删除7天前的备份文件
cd $BACKDIR
if [ $? -eq 0 ];then
    find ${BACKDIR:=/tmp} -type d -mtime +$REV_DATE |xargs rm -rvf
    green_echo "delete Success"
else
    red_echo "delete fail please check log!!!"
    exit 1
fi

5.2. 配置    crontab  任务计划

[postgres@postgresql scripts]$ crontab -l
30 03 * * 6 /bin/bash /data/scripts/backup.sh >/data/scripts/backup.log 2>&1

说明：

1. 脚本功能：

• 每日执行全量备份（使用    pg_basebackup ），备份目录按日期命名（如    bkdata_YYYYMMDD ）。

• 自动清理7天前的备份目录。

• 日志输出到    /data/scripts/backup.log （包含标准输出和错误输出）。

2. 定时任务：

• 每天凌晨3:30执行备份脚本（   30 03 * * * ）。

5. 总结

• PITR 核心条件：

1.    wal_level  设置为    replica  或更高。

1. 启用 WAL 归档机制。

2. 配置合理的归档清理策略。

参考引用

 https://blog.csdn.net/qq_42768234/article/details/132082554 

 https://www.modb.pro/db/436043