最近狠折腾了一通postgres的备份，跟MySQL的阿里RDB恢复一样，如何不停止数据库服务，恢复某个表的数据到某个时间点，然后放到另一个表中呢？
其实用之前的wal恢复一样的，就是要把当天实例的全备份和wal都复制到另外一台上，然后恢复，再把数据给dump下来，再建一个新表，把数据导回去。
用pgbackrest感觉会好一些，其实都差不多，步骤如下：
一、安装并配置pgbackrest
1apt install pgbackrest
2
3# 建立备份的大本营
4mkdir -p /var/lib/pgbackrest
5chmod 750 /var/lib/pgbackrest
6chown postgres:postgres /var/lib/pgbackrest
7
8# 编辑 /etc/pgbackrest.conf
9[global]
10repo1-path=/var/lib/pgbackrest
11repo1-retention-full=26
12repo1-retention-archive=180
13log-level-file=info
14log-level-console=info
15
16[global:archive-push]
17compress-level=3
18
19[main]
20pg1-path=/var/lib/postgresql/13/main
21pg1-port=5432
讲解一下参数
-
global里面是全局配置
-
1repo1-path=/var/lib/pgbackrest # pgBackRest 存储库路径 2repo1-retention-full=26 # 保留最近的 26 个全量备份，大概6个月，180天 3repo1-retention-archive=180 # 保留最近 180 天的 WAL 日志 4log-level-file=info # 文件日志级别 5log-level-console=info # 控制台日志级别
-
-
global:archive-push 设置的是wal归档的保存方式是压缩保存
-
main部分设置的是要备份的postgres源的路径和端口，【main】是stanza的名字
上面的备份其实有三种备份，full和incr和wal备份，那如果full和增量都没了，只要wal还在，那么依然能恢复到180天之内的某个时间点。wal很占空间，full也很占空间，一定要算准了。
那其实pgbackrest是用postgres身份去执行命令的，所以不用认证身份。
二、配置postgres
1vi /etc/postgresql/13/main/postgresql.conf
2
3# wal归档方法：
4#archive_mode = on
5#archive_command = 'cp %p /data/backup/%f'
6
7# pgbackrest归档方法：
8archive_mode = on
9archive_command = 'pgbackrest --stanza=main archive-push %p'
10wal_level = replica #备份级别是副本
11max_wal_senders = 3 #最多wal日志发到3个客户端去
12
13# 需要重启postgres
14systemctl restart postgresql
三、测试pgbackrest的备份功能
1 # 建立一个stanza
2 sudo -u postgres pgbackrest --stanza=main --log-level-console=info stanza-create
3
4 # 检查
5 sudo -u postgres pgbackrest --stanza=main --log-level-console=info check
6
7 # 查看信息
8 sudo -u postgres pgbackrest info
看到如上信息就ok了。
四、正式的full和incr备份
全量备份的脚本full.sh：
1#!/bin/bash
2
3# 设置 pgBackRest 的相关参数和变量
4PG_BACKREST_COMMAND="/usr/bin/pgbackrest"
5STANZA="main"
6LOG_FILE="/var/log/pgbackrest_full_backup.log"
7
8# 执行全量备份
9sudo -u postgres ${PG_BACKREST_COMMAND} --stanza=${STANZA} --log-level-console=info backup --type=full >> ${LOG_FILE} 2>&1
10
11# 检测备份是否成功
12if [ $? -eq 0 ]; then
13 echo "$(date '+%Y-%m-%d %H:%M:%S') - Full Backup Completed Successfully" >> ${LOG_FILE}
14else
15 echo "$(date '+%Y-%m-%d %H:%M:%S') - Full Backup Failed" >> ${LOG_FILE}
16fi
增量备份的脚本incr.sh
1#!/bin/bash
2
3# 设置 pgBackRest 相关参数
4PG_BACKREST_COMMAND="/usr/bin/pgbackrest"
5STANZA="main"
6LOG_FILE="/var/log/pgbackrest_incr_backup.log"
7
8# 执行增量备份
9sudo -u postgres ${PG_BACKREST_COMMAND} --stanza=${STANZA} --log-level-console=info backup --type=incr >> ${LOG_FILE} 2>&1
10
11# 检测备份是否成功
12if [ $? -eq 0 ]; then
13 echo "$(date '+%Y-%m-%d %H:%M:%S') - Incremental Backup Completed Successfully" >> ${LOG_FILE}
14else
15 echo "$(date '+%Y-%m-%d %H:%M:%S') - Incremental Backup Failed" >> ${LOG_FILE}
16fi
编辑crontab
1# 每周日凌晨2点执行完整备份 (full.sh)
20 2 * * 0 /path/to/full.sh >> /path/to/log/full.log 2>&1
3
4# 从周一到周六凌晨2点执行增量备份 (incr.sh)
50 2 * * 1-6 /path/to/incr.sh >> /path/to/log/incr.log 2>&1
五、新实例postgres安装
1# 这是台新的debian 12系统, 要装 postgresql 13
2apt update
3apt upgrade
4apt install -y curl gpg
5
6curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg
7echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | tee /etc/apt/sources.list.d/pgdg.list
8
9apt update
10apt-get install postgresql-13
如上就新装了一个posgres 13
六、新实例安装pgbackrest
1systemctl stop postgresql
2
3apt install pgbackrest
4
5vi /etc/pgbackrest.conf
6[main]
7pg1-path=/var/lib/postgresql/13/main
8pg1-port=5432
9
10[global]
11repo1-path=/var/lib/pgbackrest
12log-level-console=info
13log-level-file=info
14
15sudo systemctl start postgresq
六、准备恢复环境的数据
1要把数pgbackrest的数据同步到新实例上：
2
3# 使用 rsync（推荐）
4sudo rsync -avz /var/lib/pgbackrest/ <new_instance_user>@<new_instance_ip>:/var/lib/pgbackrest/
5# 或者，使用 scp
6sudo scp -r /var/lib/pgbackrest/ <new_instance_user>@<new_instance_ip>:/var/lib/pgbackrest/
7
8# 设置正确权限
9chown -R postgres:postgres /var/lib/pgbackrest
10chmod 750 /var/lib/pgbackrest
七、新实例上执行恢复到时间点
1sudo -u postgres pgbackrest --stanza=main \
2 --target="2025-09-15 14:00:00" \
3 --target-action=promote \
4 --delta restore --log-level-console=info
5
6参数说明：
7 --stanza=main：指定备份配置的名称。
8 --target：设置时间点。
9 --target-action=promote：让恢复完成后实例成为主实例。
10 --delta：只恢复有变更的数据（提高恢复速度）。
11
12systemctl start postgresql
八、把新实例上的数据dump出来
1# 使用 psql 从恢复的数据库导出目标表数据。假设你要恢复的表名是 public.my_table，你可以导出它的数据为 SQL 脚本：
2pg_dump -U postgres -t public.my_table my_database > my_table_data.sql
3
4# 或者选择仅导出某些行数据（比如按时间条件筛选）到 SQL 脚本中：
5psql -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"
然后去旧实例上，灌入数据
1# 建同样的表
2CREATE TABLE public.my_table_recovery (LIKE public.my_table INCLUDING ALL);
3
4# 修改语句
5sed -i 's/old_table/new_table/g' my_table_data.sql
6
7# 导sql
8psql -U postgres -d my_database -f my_table_data.sql
9
10# 导数据
11# 同样要修改CSV的！！！
12\copy public.my_table_recovery FROM '/tmp/my_table_data.csv' WITH CSV HEADER
这样就ok了。
