Postgres pgbackrest备份以及指定时间点恢复

最近狠折腾了一通postgres的备份，跟MySQL的阿里RDB恢复一样，如何不停止数据库服务，恢复某个表的数据到某个时间点，然后放到另一个表中呢？

其实用之前的wal恢复一样的，就是要把当天实例的全备份和wal都复制到另外一台上，然后恢复，再把数据给dump下来，再建一个新表，把数据导回去。

用pgbackrest感觉会好一些，其实都差不多，步骤如下：

一、安装并配置pgbackrest

apt install pgbackrest

# 建立备份的大本营
mkdir -p /var/lib/pgbackrest
chmod 750 /var/lib/pgbackrest
chown postgres:postgres /var/lib/pgbackrest

# 编辑 /etc/pgbackrest.conf
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=26
repo1-retention-archive=180
log-level-file=info
log-level-console=info

[global:archive-push]
compress-level=3

[main]
pg1-path=/var/lib/postgresql/13/main
pg1-port=5432

讲解一下参数

global里面是全局配置

repo1-path=/var/lib/pgbackrest   # pgBackRest 存储库路径
repo1-retention-full=26          # 保留最近的 26 个全量备份，大概6个月，180天
repo1-retention-archive=180      # 保留最近 180 天的 WAL 日志
log-level-file=info              # 文件日志级别
log-level-console=info           # 控制台日志级别

global:archive-push 设置的是wal归档的保存方式是压缩保存
main部分设置的是要备份的postgres源的路径和端口，【main】是stanza的名字

上面的备份其实有三种备份，full和incr和wal备份，那如果full和增量都没了，只要wal还在，那么依然能恢复到180天之内的某个时间点。wal很占空间，full也很占空间，一定要算准了。

那其实pgbackrest是用postgres身份去执行命令的，所以不用认证身份。

二、配置postgres

vi /etc/postgresql/13/main/postgresql.conf

# wal归档方法：
#archive_mode = on
#archive_command = 'cp %p /data/backup/%f'

# pgbackrest归档方法：
archive_mode = on                  
archive_command = 'pgbackrest --stanza=main archive-push %p'  
wal_level = replica   #备份级别是副本
max_wal_senders = 3   #最多wal日志发到3个客户端去

# 需要重启postgres
systemctl restart postgresql

三、测试pgbackrest的备份功能

  # 建立一个stanza
  sudo -u postgres pgbackrest --stanza=main --log-level-console=info stanza-create
  
  # 检查
  sudo -u postgres pgbackrest --stanza=main --log-level-console=info check
  
  # 查看信息
  sudo -u postgres pgbackrest info

看到如上信息就ok了。

四、正式的full和incr备份

全量备份的脚本full.sh：

#!/bin/bash

# 设置 pgBackRest 的相关参数和变量
PG_BACKREST_COMMAND="/usr/bin/pgbackrest"
STANZA="main"
LOG_FILE="/var/log/pgbackrest_full_backup.log"

# 执行全量备份
sudo -u postgres ${PG_BACKREST_COMMAND} --stanza=${STANZA} --log-level-console=info backup --type=full >> ${LOG_FILE} 2>&1

# 检测备份是否成功
if [ $? -eq 0 ]; then
    echo "$(date '+%Y-%m-%d %H:%M:%S') - Full Backup Completed Successfully" >> ${LOG_FILE}
else
    echo "$(date '+%Y-%m-%d %H:%M:%S') - Full Backup Failed" >> ${LOG_FILE}
fi

增量备份的脚本incr.sh

#!/bin/bash

# 设置 pgBackRest 相关参数
PG_BACKREST_COMMAND="/usr/bin/pgbackrest"
STANZA="main"
LOG_FILE="/var/log/pgbackrest_incr_backup.log"

# 执行增量备份
sudo -u postgres ${PG_BACKREST_COMMAND} --stanza=${STANZA} --log-level-console=info backup --type=incr >> ${LOG_FILE} 2>&1

# 检测备份是否成功
if [ $? -eq 0 ]; then
    echo "$(date '+%Y-%m-%d %H:%M:%S') - Incremental Backup Completed Successfully" >> ${LOG_FILE}
else
    echo "$(date '+%Y-%m-%d %H:%M:%S') - Incremental Backup Failed" >> ${LOG_FILE}
fi

编辑crontab

# 每周日凌晨2点执行完整备份 (full.sh)
0 2 * * 0 /path/to/full.sh

# 从周一到周六凌晨2点执行增量备份 (incr.sh)
0 2 * * 1-6 /path/to/incr.sh

五、新实例postgres安装

# 这是台新的debian 12系统, 要装 postgresql 13
apt update
apt upgrade
apt install -y curl gpg

curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc |  gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg
echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | tee /etc/apt/sources.list.d/pgdg.list

apt update
apt-get install postgresql-13

如上就新装了一个posgres 13

六、新实例安装pgbackrest

systemctl stop postgresql

apt install pgbackrest

vi /etc/pgbackrest.conf
[main]
pg1-path=/var/lib/postgresql/13/main
pg1-port=5432

[global]
repo1-path=/var/lib/pgbackrest
log-level-console=info
log-level-file=info

sudo systemctl start postgresq

六、准备恢复环境的数据

要把数pgbackrest的数据同步到新实例上：

# 使用 rsync（推荐）
sudo rsync -avz /var/lib/pgbackrest/ <new_instance_user>@<new_instance_ip>:/var/lib/pgbackrest/
# 或者，使用 scp
sudo scp -r /var/lib/pgbackrest/ <new_instance_user>@<new_instance_ip>:/var/lib/pgbackrest/

# 设置正确权限
chown -R postgres:postgres /var/lib/pgbackrest
chmod 750 /var/lib/pgbackrest

七、新实例上执行恢复到时间点

sudo -u postgres pgbackrest --stanza=main \
  --target="2025-09-15 14:00:00" \
  --target-action=promote \
  --delta restore --log-level-console=info

参数说明：
    --stanza=main：指定备份配置的名称。
    --target：设置时间点。
    --target-action=promote：让恢复完成后实例成为主实例。
    --delta：只恢复有变更的数据（提高恢复速度）。

systemctl start postgresql

八、把新实例上的数据dump出来

# 使用 psql 从恢复的数据库导出目标表数据。假设你要恢复的表名是 public.my_table，你可以导出它的数据为 SQL 脚本：
pg_dump -U postgres -t public.my_table my_database > my_table_data.sql

# 或者选择仅导出某些行数据（比如按时间条件筛选）到 SQL 脚本中：
psql -U postgres -d my_database -c "\copy (SELECT * FROM public.my_table WHERE created_at BETWEEN '2023-09-30' AND '2023-10-01') TO '/tmp/my_table_data.csv' WITH CSV HEADER"

然后去旧实例上，灌入数据

# 建同样的表
CREATE TABLE public.my_table_recovery (LIKE public.my_table INCLUDING ALL);

# 修改语句
sed -i 's/old_table/new_table/g' my_table_data.sql

# 导sql
psql -U postgres -d my_database -f my_table_data.sql

# 导数据
# 同样要修改CSV的！！！
\copy public.my_table_recovery FROM '/tmp/my_table_data.csv' WITH CSV HEADER

这样就ok了。

一、安装并配置pgbackrest#

二、配置postgres#

三、测试pgbackrest的备份功能#

四、正式的full和incr备份#

五、新实例postgres安装#

六、新实例安装pgbackrest#

六、准备恢复环境的数据#

七、新实例上执行恢复到时间点#

八、把新实例上的数据dump出来#