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，pg_basebackup在做增量备份的时候，要求打开summarize_wal
3，其他参数不一一列举，wal日志级别，开启wal日志归档，开启日志等等
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| --+--+
以下模拟数据写入过程中执行全量备份和增量备份，执行一个完整备份和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日志中，不恢复该条数据 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|
- [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
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|
- 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.
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！ |
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
照妖镜
