PostgreSQL 17 pg_basebackup增量备份新特性测试，以及基于完整备份+增量备份+WAL日志备份的恢复
PostgreSQL 17版本的pg_baseback开始支持增量备份，终于可以像大多数的数据库物理备份工具一样支持增量备份了，下班后抽空尝试了一下，跟其他数据库的物理备份类似，还是比较简单的。
以下基于一个月前发布的PostgreSQL 17.6为测试环境，利用pg_basebackup，基于full+incremental+wal日志的备份，做一个基于时间点的恢复（Point-In-Time Recovery (PITR) ）测试。
0，环境准备
1，sudo apt-get install moreutils ，该依赖包用于记录备份日志时增加一个时间戳
2，设置summarize_wal= on，PostgreSQL 17中，增加了一个WAL Summarizer进程，来跟踪对所有数据库块，并将这些修改写入位于 pg_wal/summaries/ 目录中的 WAL 摘要文件中，pg_basebackup在做增量备份的时候，要求打开summarize_wal，以满足WAL Summarizer进程写入数据块的更改信息，如果不打开该参数，则无法实现增量备份。
3，其他参数不一一列举，wal日志级别，开启wal日志归档，开启日志等等
root@iZ7xv55xixens4mlf4jusqZ:/usr/local/postgresql_install_package# systemctl status postgresql9700
● postgresql9700.service - PostgreSQL database server
Loaded: loaded (/etc/systemd/system/postgresql9700.service; enabled; vendor preset: enabled)
Active: active (running) since Mon 2025-09-08 13:55:25 CST; 50min ago
Process: 1778 ExecStart=/usr/local/pgsql17/server/bin/pg_ctl start -D ${PGDATA} -s -w -t ${PGSTARTTIMEOUT} (code=exited, status=0/SUCCESS)
Main PID: 1795 (postgres)
Tasks: 12 (limit: 2194)
Memory: 429.9M
CGroup: /system.slice/postgresql9700.service
├─1795 /usr/local/pgsql17/server/bin/postgres -D /usr/local/pgsql17/pg9700/data
├─1796 postgres: logger
├─1798 postgres: checkpointer
├─1799 postgres: background writer
├─1802 postgres: walwriter
├─1803 postgres: walsummarizer #PostgreSQL17 新增的walsummarizer进程
├─1804 postgres: autovacuum launcher
├─1805 postgres: archiver last was 0000000100000000000000A3.00000028.backup
├─1806 postgres: logical replication launcher
Sep 08 13:55:25 iZ7xv55xixens4mlf4jusqZ systemd[1]: Starting PostgreSQL database server...
Sep 08 13:55:25 iZ7xv55xixens4mlf4jusqZ pg_ctl[1795]: 2025-09-08 13:55:25.854 CST [1795] LOG: redirecting log output to logging collector process
Sep 08 13:55:25 iZ7xv55xixens4mlf4jusqZ pg_ctl[1795]: 2025-09-08 13:55:25.854 CST [1795] HINT: Future log output will appear in directory "log".
Sep 08 13:55:25 iZ7xv55xixens4mlf4jusqZ systemd[1]: Started PostgreSQL database server.
root@iZ7xv55xixens4mlf4jusqZ:/usr/local/postgresql_install_package#walsummarizer目录示例
1，创建测试表
drop table if exists public.test_incremental_backup ;
create table public.test_incremental_backup
(
c1 int generated always as identity primary key,
c2 timestamp
);
select * from public.test_incremental_backup;
c1|c2|
--+--+2，pg_basebackup完整备份和增量备份
以下模拟数据写入过程中执行完整备份和增量备份，执行一个完整备份和3个增量备份，3个增量备份分别基于前一个备份
--增量备份测试：
--step 1 , 写入一条数据
insert into public.test_incremental_backup(c2) values(now());
select * from test_incremental_backup;
c1|c2 |
--+-----------------------+
1|2025-09-08 19:46:00.386|
--step 2, 执行完整备份，该完整备份包含c1 = 1的数据
pg_basebackup -U postgres -h 127.0.0.1 -p 9700 -P -v -Fp -Xs -D /usr/local/pgbackup/full 2>&1 | ts '[%Y-%m-%d %H:%M:%S]' | tee >> /usr/local/pgbackup/backup_log.log
--step 3 , 写入一条数据
insert into public.test_incremental_backup(c2) values(now());
select * from test_incremental_backup;
c1|c2 |
--+-----------------------+
1|2025-09-08 19:46:00.386|
2|2025-09-08 19:49:00.490|
--step 4, 执行增量备份1，该增量备份1包含c1 = 2的数据
pg_basebackup -U postgres -h 127.0.0.1 -p 9700 -P -v -Fp -Xs -D /usr/local/pgbackup/incremental_1 -i /usr/local/pgbackup/full/backup_manifest 2>&1 | ts '[%Y-%m-%d %H:%M:%S]' | tee >> /usr/local/pgbackup/backup_log.log
--step 5 , 写入一条数据
insert into public.test_incremental_backup(c2) values(now());
select * from test_incremental_backup;
c1|c2 |
--+-----------------------+
1|2025-09-08 19:46:00.386|
2|2025-09-08 19:49:00.490|
3|2025-09-08 19:49:12.119|
--step 6, 执行增量备份2，该增量备份1包含c1 = 3的数据
pg_basebackup -U postgres -h 127.0.0.1 -p 9700 -P -v -Fp -Xs -D /usr/local/pgbackup/incremental_2 -i /usr/local/pgbackup/incremental_1/backup_manifest 2>&1 | ts '[%Y-%m-%d %H:%M:%S]' | tee >> /usr/local/pgbackup/backup_log.log
--step 7 , 写入一条数据
insert into public.test_incremental_backup(c2) values(now());
select * from test_incremental_backup;
c1|c2 |
--+-----------------------+
1|2025-09-08 19:46:00.386|
2|2025-09-08 19:49:00.490|
3|2025-09-08 19:49:12.119|
4|2025-09-08 19:50:09.845|
--step 8, 执行增量备份3，该增量备份3包含c1 = 4的数据
pg_basebackup -U postgres -h 127.0.0.1 -p 9700 -P -v -Fp -Xs -D /usr/local/pgbackup/incremental_3 -i /usr/local/pgbackup/incremental_2/backup_manifest 2>&1 | ts '[%Y-%m-%d %H:%M:%S]' | tee >> /usr/local/pgbackup/backup_log.log
--step 9 , 写入一条数据:19:50:32.767，该数据尚未备份，位于wal日志中，利用wal日志恢复，恢复至2025-09-08 19:52:00
insert into public.test_incremental_backup(c2) values(now());
select * from test_incremental_backup;
c1|c2 |
--+-----------------------+
1|2025-09-08 19:46:00.386|
2|2025-09-08 19:49:00.490|
3|2025-09-08 19:49:12.119|
4|2025-09-08 19:50:09.845|
5|2025-09-08 19:50:32.767|
--step 10 ,2025-09-08 19:52:00 之后再次写一条数据，该数据尚未备份，位于wal日志中，基于时间点2025-09-08 19:52:00 恢复，不恢复该条数据
insert into public.test_incremental_backup(c2) values(now());
select * from test_incremental_backup;
c1|c2 |
--+-----------------------+
1|2025-09-08 19:46:00.386|
2|2025-09-08 19:49:00.490|
3|2025-09-08 19:49:12.119|
4|2025-09-08 19:50:09.845|
5|2025-09-08 19:50:32.767|
6|2025-09-08 19:52:23.904|
--step 11 ,强制wal切换
SELECT pg_walfile_name(pg_current_wal_lsn());
pg_walfile_name |
------------------------+
000000010000000000000035|
select pg_switch_wal();
pg_switch_wal|
-------------+
0/35000420 |
SELECT pg_walfile_name(pg_current_wal_lsn());
pg_walfile_name |
------------------------+
000000010000000000000036|
--查看WAL归档日志，确保最后一个日志被成功归档
select * from pg_stat_archiver;备份命令执行过程中的日志
[2025-09-08 19:48:28] pg_basebackup: initiating base backup, waiting for checkpoint to complete
[2025-09-08 19:48:31] pg_basebackup: checkpoint completed
[2025-09-08 19:48:31] pg_basebackup: write-ahead log start point: 0/2E000028 on timeline 1
[2025-09-08 19:48:31] pg_basebackup: starting background WAL receiver
[2025-09-08 19:48:31] pg_basebackup: created temporary replication slot "pg_basebackup_814241"
[2025-09-08 19:48:31] 69/23342 kB (0%), 0/1 tablespace (.../local/pgbackup/full/base/1/2669)
[2025-09-08 19:48:31] 23352/23352 kB (100%), 0/1 tablespace (.../pgbackup/full/global/pg_control)
[2025-09-08 19:48:32] 23352/23352 kB (100%), 1/1 tablespace
[2025-09-08 19:48:32] pg_basebackup: write-ahead log end point: 0/2E000120
[2025-09-08 19:48:32] pg_basebackup: waiting for background process to finish streaming ...
[2025-09-08 19:48:32] pg_basebackup: syncing data to disk ...
[2025-09-08 19:48:32] pg_basebackup: renaming backup_manifest.tmp to backup_manifest
[2025-09-08 19:48:32] pg_basebackup: base backup completed
*************************************************完整备份完整（该行为手动备注）*************************************************
[2025-09-08 19:49:04] pg_basebackup: initiating base backup, waiting for checkpoint to complete
[2025-09-08 19:49:04] pg_basebackup: checkpoint completed
[2025-09-08 19:49:04] pg_basebackup: write-ahead log start point: 0/30000028 on timeline 1
[2025-09-08 19:49:04] pg_basebackup: starting background WAL receiver
[2025-09-08 19:49:04] pg_basebackup: created temporary replication slot "pg_basebackup_814249"
[2025-09-08 19:49:04] 3865/23346 kB (16%), 0/1 tablespace (.../incremental_1/global/pg_control)
[2025-09-08 19:49:04] 3865/23346 kB (100%), 1/1 tablespace
[2025-09-08 19:49:04] pg_basebackup: write-ahead log end point: 0/30000120
[2025-09-08 19:49:04] pg_basebackup: waiting for background process to finish streaming ...
[2025-09-08 19:49:04] pg_basebackup: syncing data to disk ...
[2025-09-08 19:49:05] pg_basebackup: renaming backup_manifest.tmp to backup_manifest
[2025-09-08 19:49:05] pg_basebackup: base backup completed
*************************************************增量备份1（该行为手动备注）*************************************************
[2025-09-08 19:49:59] pg_basebackup: initiating base backup, waiting for checkpoint to complete
[2025-09-08 19:50:00] pg_basebackup: checkpoint completed
[2025-09-08 19:50:00] pg_basebackup: write-ahead log start point: 0/32000028 on timeline 1
[2025-09-08 19:50:00] pg_basebackup: starting background WAL receiver
[2025-09-08 19:50:00] pg_basebackup: created temporary replication slot "pg_basebackup_814259"
[2025-09-08 19:50:00] 3868/23349 kB (16%), 0/1 tablespace (.../incremental_2/global/pg_control)
[2025-09-08 19:50:00] 3868/23349 kB (100%), 1/1 tablespace
[2025-09-08 19:50:00] pg_basebackup: write-ahead log end point: 0/32000120
[2025-09-08 19:50:00] pg_basebackup: waiting for background process to finish streaming ...
[2025-09-08 19:50:00] pg_basebackup: syncing data to disk ...
[2025-09-08 19:50:00] pg_basebackup: renaming backup_manifest.tmp to backup_manifest
[2025-09-08 19:50:00] pg_basebackup: base backup completed
*************************************************增量备份2（该行为手动备注）*************************************************
[2025-09-08 19:50:23] pg_basebackup: initiating base backup, waiting for checkpoint to complete
[2025-09-08 19:50:23] pg_basebackup: checkpoint completed
[2025-09-08 19:50:23] pg_basebackup: write-ahead log start point: 0/34000028 on timeline 1
[2025-09-08 19:50:23] pg_basebackup: starting background WAL receiver
[2025-09-08 19:50:23] pg_basebackup: created temporary replication slot "pg_basebackup_814270"
[2025-09-08 19:50:23] 3872/23353 kB (16%), 0/1 tablespace (.../incremental_3/global/pg_control)
[2025-09-08 19:50:24] 3872/23353 kB (100%), 1/1 tablespace
[2025-09-08 19:50:24] pg_basebackup: write-ahead log end point: 0/34000120
[2025-09-08 19:50:24] pg_basebackup: waiting for background process to finish streaming ...
[2025-09-08 19:50:24] pg_basebackup: syncing data to disk ...
[2025-09-08 19:50:24] pg_basebackup: renaming backup_manifest.tmp to backup_manifest
[2025-09-08 19:50:24] pg_basebackup: base backup completed
*************************************************增量备份3（该行为手动备注）*************************************************
3，pg_combinebackup合并完整备份和增量备份
利用pg_combinebackup合并完整备份和三个增量备份，会自动生成combined_full_backup路径
--step 13 合并备份
pg_combinebackup /usr/local/pgbackup/full /usr/local/pgbackup/incremental_1 /usr/local/pgbackup/incremental_2 /usr/local/pgbackup/incremental_3 -o /usr/local/pgbackup/combined_full_backup
-- 也可以先加上-n参数进行dry-run，测试是否可以正常合并完整备份和增量备份
pg_combinebackup /usr/local/pgbackup/full /usr/local/pgbackup/incremental_1 /usr/local/pgbackup/incremental_2 /usr/local/pgbackup/incremental_3 -o /usr/local/pgbackup/combined_full_backup -n
4，完整备份和增量备份以及wal日志的恢复
--step 14 恢复至新实例
1，停止新实例的服务systemctl stop postgresql9800
2，移除新实例的数据目录（仅测试，有必要的话需要备份）
3，将pg_combinebackup的文件cp到新实例的数据文件路径下
4，需改上述新实例的数据文件下配置文件的端口号（我在本地用多实例测试的，因此两个实例的端口号不能一样）
5，创建恢复标记文件，touch recovery.signal
6，修改文件属性
chown -R postgres:postgres /usr/local/pgsql17/pg9800
chmod 700 -R /usr/local/pgsql17/pg9800
7，修改recovery_target_time = '2025-09-08 19:52:00'
8，修改restore_command = 'cp /usr/local/pgbackup/wal/%f %p'
9，修改recovery_target_action = 'pause'
10，启动数据库服务systemctl start postgresql9800
11，查询数据，按预期的恢复至2025-09-08 19:52:00
select * from test_incremental_backup;
c1|c2 |
--+-----------------------+
1|2025-09-08 19:46:00.386|
2|2025-09-08 19:49:00.490|
3|2025-09-08 19:49:12.119|
4|2025-09-08 19:50:09.845|
5|2025-09-08 19:50:32.767|
如下是PostgreSQL目标数据库实例的恢复日志，可以清楚地看到：starting point-in-time recovery to 2025-09-08 19:52:00+08，恢复至目标时间点
2025-09-08 20:01:14.453 CST [814476] LOG: starting PostgreSQL 17.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.2) 9.4.0, 64-bit
2025-09-08 20:01:14.453 CST [814476] LOG: listening on IPv4 address "0.0.0.0", port 9800
2025-09-08 20:01:14.454 CST [814476] LOG: listening on IPv6 address "::", port 9800
2025-09-08 20:01:14.459 CST [814476] LOG: listening on Unix socket "/tmp/.s.PGSQL.9800"
2025-09-08 20:01:14.465 CST [814480] LOG: database system was interrupted; last known up at 2025-09-08 19:50:23 CST
cp: cannot stat '/usr/local/pgbackup/wal/00000002.history': No such file or directory
2025-09-08 20:01:14.573 CST [814480] LOG: starting backup recovery with redo LSN 0/34000028, checkpoint LSN 0/34000080, on timeline ID 1
2025-09-08 20:01:14.588 CST [814480] LOG: restored log file "000000010000000000000034" from archive
2025-09-08 20:01:14.735 CST [814480] LOG: starting point-in-time recovery to 2025-09-08 19:52:00+08
2025-09-08 20:01:14.740 CST [814480] LOG: redo starts at 0/34000028
2025-09-08 20:01:14.755 CST [814480] LOG: restored log file "000000010000000000000035" from archive
cp: cannot stat '/usr/local/pgbackup/wal/000000010000000000000036': No such file or directory
2025-09-08 20:01:14.921 CST [814480] LOG: completed backup recovery with redo LSN 0/34000028 and end LSN 0/34000120
2025-09-08 20:01:14.921 CST [814480] LOG: consistent recovery state reached at 0/34000120
2025-09-08 20:01:14.921 CST [814476] LOG: database system is ready to accept read-only connections
2025-09-08 20:01:14.921 CST [814480] LOG: recovery stopping before commit of transaction 784, time 2025-09-08 19:52:23.904561+08
2025-09-08 20:01:14.921 CST [814480] LOG: pausing at the end of recovery
2025-09-08 20:01:14.921 CST [814480] HINT: Execute pg_wal_replay_resume() to promote.部分测试截图
浙公网安备 33010602011771号