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，pg_basebackup在做增量备份的时间，要求打开summarize_wal
3，其他参数不逐一罗列，wal日记级别，开启wal日记归档，开启日记等等
 
1，创建测试表

1. drop table if exists public.test_incremental_backup ;
3. create table public.test_incremental_backup
4. (
5.         c1 int generated always as identity primary key,
6.         c2 timestamp
7. );
9. select * from public.test_incremental_backup;
11. c1|c2|
12. --+--+

复制代码

2，pg_basebackup全量和增量备份

以下模仿数据写入过程中实行全量备份和增量备份，实行一个完备备份和3个增量备份，3个增量备份分别基于前一个备份

1. --增量备份测试：
2. --step 1 , 写入一条数据
3. insert into public.test_incremental_backup(c2) values(now());
4. select * from test_incremental_backup;
5. c1|c2                     |
6. --+-----------------------+
7. 1|2025-09-08 19:46:00.386|
10. --step 2, 执行完整备份，该完整备份包含c1 = 1的数据
11. 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
14. --step 3 , 写入一条数据
15. insert into public.test_incremental_backup(c2) values(now());
16. select * from test_incremental_backup;
17. c1|c2                     |
18. --+-----------------------+
19. 1|2025-09-08 19:46:00.386|
20. 2|2025-09-08 19:49:00.490|
23. --step 4, 执行增量备份1，该增量备份1包含c1 = 2的数据
24. 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
27. --step 5 , 写入一条数据
28. insert into public.test_incremental_backup(c2) values(now());
29. select * from test_incremental_backup;
30. c1|c2                     |
31. --+-----------------------+
32. 1|2025-09-08 19:46:00.386|
33. 2|2025-09-08 19:49:00.490|
34. 3|2025-09-08 19:49:12.119|
37. --step 6, 执行增量备份2，该增量备份1包含c1 = 3的数据
38. 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
41. --step 7 , 写入一条数据
42. insert into public.test_incremental_backup(c2) values(now());
43. select * from test_incremental_backup;
44. c1|c2                     |
45. --+-----------------------+
46. 1|2025-09-08 19:46:00.386|
47. 2|2025-09-08 19:49:00.490|
48. 3|2025-09-08 19:49:12.119|
49. 4|2025-09-08 19:50:09.845|
52. --step 8, 执行增量备份3，该增量备份3包含c1 = 4的数据
53. 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
56. --step 9 , 写入一条数据:19:50:32.767，位于wal中，利用wal日志恢复，恢复至2025-09-08 19:52:00
57. insert into public.test_incremental_backup(c2) values(now());
58. select * from test_incremental_backup;
59. c1|c2                     |
60. --+-----------------------+
61. 1|2025-09-08 19:46:00.386|
62. 2|2025-09-08 19:49:00.490|
63. 3|2025-09-08 19:49:12.119|
64. 4|2025-09-08 19:50:09.845|
65. 5|2025-09-08 19:50:32.767|
68. --step 10 ,2025-09-08 19:52:00 之后再次写一条数据，该数据尚未备份，位于wal日志中，不恢复该条数据
69. insert into public.test_incremental_backup(c2) values(now());
70. select * from test_incremental_backup;
71. c1|c2                     |
72. --+-----------------------+
73. 1|2025-09-08 19:46:00.386|
74. 2|2025-09-08 19:49:00.490|
75. 3|2025-09-08 19:49:12.119|
76. 4|2025-09-08 19:50:09.845|
77. 5|2025-09-08 19:50:32.767|
78. 6|2025-09-08 19:52:23.904|
81. --step 11 ,强制wal切换
82. SELECT pg_walfile_name(pg_current_wal_lsn());
83. pg_walfile_name         |
84. ------------------------+
85. 000000010000000000000035|
87. select pg_switch_wal();
88. pg_switch_wal|
89. -------------+
90. 0/35000420   |
92. SELECT pg_walfile_name(pg_current_wal_lsn());
93. pg_walfile_name         |
94. ------------------------+
95. 000000010000000000000036|

复制代码

 备份下令实行过程中的日记

1. [2025-09-08 19:48:28] pg_basebackup: initiating base backup, waiting for checkpoint to complete
2. [2025-09-08 19:48:31] pg_basebackup: checkpoint completed
3. [2025-09-08 19:48:31] pg_basebackup: write-ahead log start point: 0/2E000028 on timeline 1
4. [2025-09-08 19:48:31] pg_basebackup: starting background WAL receiver
5. [2025-09-08 19:48:31] pg_basebackup: created temporary replication slot "pg_basebackup_814241"
6. [2025-09-08 19:48:31]    69/23342 kB (0%), 0/1 tablespace (.../local/pgbackup/full/base/1/2669)
7. [2025-09-08 19:48:31] 23352/23352 kB (100%), 0/1 tablespace (.../pgbackup/full/global/pg_control)
8. [2025-09-08 19:48:32] 23352/23352 kB (100%), 1/1 tablespace                                         
9. [2025-09-08 19:48:32] pg_basebackup: write-ahead log end point: 0/2E000120
10. [2025-09-08 19:48:32] pg_basebackup: waiting for background process to finish streaming ...
11. [2025-09-08 19:48:32] pg_basebackup: syncing data to disk ...
12. [2025-09-08 19:48:32] pg_basebackup: renaming backup_manifest.tmp to backup_manifest
13. [2025-09-08 19:48:32] pg_basebackup: base backup completed
14. [2025-09-08 19:49:04] pg_basebackup: initiating base backup, waiting for checkpoint to complete
15. [2025-09-08 19:49:04] pg_basebackup: checkpoint completed
16. *******************************************完整备份完整（该行为手动备注）*******************************************
17. [2025-09-08 19:49:04] pg_basebackup: write-ahead log start point: 0/30000028 on timeline 1
18. [2025-09-08 19:49:04] pg_basebackup: starting background WAL receiver
19. [2025-09-08 19:49:04] pg_basebackup: created temporary replication slot "pg_basebackup_814249"
20. [2025-09-08 19:49:04]  3865/23346 kB (16%), 0/1 tablespace (.../incremental_1/global/pg_control)
21. [2025-09-08 19:49:04]  3865/23346 kB (100%), 1/1 tablespace                                         
22. [2025-09-08 19:49:04] pg_basebackup: write-ahead log end point: 0/30000120
23. [2025-09-08 19:49:04] pg_basebackup: waiting for background process to finish streaming ...
24. [2025-09-08 19:49:04] pg_basebackup: syncing data to disk ...
25. [2025-09-08 19:49:05] pg_basebackup: renaming backup_manifest.tmp to backup_manifest
26. [2025-09-08 19:49:05] pg_basebackup: base backup completed
27. *******************************************增量备份1（该行为手动备注）*******************************************
28. [2025-09-08 19:49:59] pg_basebackup: initiating base backup, waiting for checkpoint to complete
29. [2025-09-08 19:50:00] pg_basebackup: checkpoint completed
30. [2025-09-08 19:50:00] pg_basebackup: write-ahead log start point: 0/32000028 on timeline 1
31. [2025-09-08 19:50:00] pg_basebackup: starting background WAL receiver
32. [2025-09-08 19:50:00] pg_basebackup: created temporary replication slot "pg_basebackup_814259"
33. [2025-09-08 19:50:00]  3868/23349 kB (16%), 0/1 tablespace (.../incremental_2/global/pg_control)
34. [2025-09-08 19:50:00]  3868/23349 kB (100%), 1/1 tablespace                                         
35. [2025-09-08 19:50:00] pg_basebackup: write-ahead log end point: 0/32000120
36. [2025-09-08 19:50:00] pg_basebackup: waiting for background process to finish streaming ...
37. [2025-09-08 19:50:00] pg_basebackup: syncing data to disk ...
38. [2025-09-08 19:50:00] pg_basebackup: renaming backup_manifest.tmp to backup_manifest
39. [2025-09-08 19:50:00] pg_basebackup: base backup completed
40. *******************************************增量备份2（该行为手动备注）*******************************************
41. [2025-09-08 19:50:23] pg_basebackup: initiating base backup, waiting for checkpoint to complete
42. [2025-09-08 19:50:23] pg_basebackup: checkpoint completed
43. [2025-09-08 19:50:23] pg_basebackup: write-ahead log start point: 0/34000028 on timeline 1
44. [2025-09-08 19:50:23] pg_basebackup: starting background WAL receiver
45. [2025-09-08 19:50:23] pg_basebackup: created temporary replication slot "pg_basebackup_814270"
46. [2025-09-08 19:50:23]  3872/23353 kB (16%), 0/1 tablespace (.../incremental_3/global/pg_control)
47. [2025-09-08 19:50:24]  3872/23353 kB (100%), 1/1 tablespace                                         
48. [2025-09-08 19:50:24] pg_basebackup: write-ahead log end point: 0/34000120
49. [2025-09-08 19:50:24] pg_basebackup: waiting for background process to finish streaming ...
50. [2025-09-08 19:50:24] pg_basebackup: syncing data to disk ...
51. [2025-09-08 19:50:24] pg_basebackup: renaming backup_manifest.tmp to backup_manifest
52. [2025-09-08 19:50:24] pg_basebackup: base backup completed
53. *******************************************增量备份3（该行为手动备注）*******************************************

复制代码

 
 
3，pg_combinebackup归并完备备份和增量备份

 使用pg_combinebackup归并全量备份和三个增量备份，会主动天生combined_full_backup路径

1. --step 13  合并备份
2. 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日记的规复

1. --step 14  恢复至新实例
2. 1，停止新实例的服务systemctl stop postgresql9800
3. 2，移除新实例的数据目录（仅测试，有必要的话需要备份）
4. 3，将pg_combinebackup的文件cp到新实例的数据文件路径下
5. 4，需改上述新实例的数据文件下配置文件的端口号（我在本地用多实例测试的，因此两个实例的端口号不能一样）
6. 5，创建恢复标记文件，touch recovery.signal
7. 6，修改文件属性
8.         chown -R postgres:postgres /usr/local/pgsql17/pg9800
9.         chmod 700 -R /usr/local/pgsql17/pg9800
10. 7，修改recovery_target_time = '2025-09-08 19:52:00'
11. 8，修改restore_command = 'cp /usr/local/pgbackup/wal/%f %p'
12. 9，修改recovery_target_action = 'pause'
13. 10，启动数据库服务systemctl start postgresql9800
14. 11，查询数据，按预期的恢复至2025-09-08 19:52:00
15. select * from test_incremental_backup;
16. c1|c2                     |
17. --+-----------------------+
18. 1|2025-09-08 19:46:00.386|
19. 2|2025-09-08 19:49:00.490|
20. 3|2025-09-08 19:49:12.119|
21. 4|2025-09-08 19:50:09.845|
22. 5|2025-09-08 19:50:32.767|

复制代码

如下是PostgreSQL目标数据库实例的规复日记，可以清晰地看到：starting point-in-time recovery to 2025-09-08 19:52:00+08，规复至目标时间点

1. 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
2. 2025-09-08 20:01:14.453 CST [814476] LOG:  listening on IPv4 address "0.0.0.0", port 9800
3. 2025-09-08 20:01:14.454 CST [814476] LOG:  listening on IPv6 address "::", port 9800
4. 2025-09-08 20:01:14.459 CST [814476] LOG:  listening on Unix socket "/tmp/.s.PGSQL.9800"
5. 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
6. cp: cannot stat '/usr/local/pgbackup/wal/00000002.history': No such file or directory
7. 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
8. 2025-09-08 20:01:14.588 CST [814480] LOG:  restored log file "000000010000000000000034" from archive
9. 2025-09-08 20:01:14.735 CST [814480] LOG:  starting point-in-time recovery to 2025-09-08 19:52:00+08
10. 2025-09-08 20:01:14.740 CST [814480] LOG:  redo starts at 0/34000028
11. 2025-09-08 20:01:14.755 CST [814480] LOG:  restored log file "000000010000000000000035" from archive
12. cp: cannot stat '/usr/local/pgbackup/wal/000000010000000000000036': No such file or directory
13. 2025-09-08 20:01:14.921 CST [814480] LOG:  completed backup recovery with redo LSN 0/34000028 and end LSN 0/34000120
14. 2025-09-08 20:01:14.921 CST [814480] LOG:  consistent recovery state reached at 0/34000120
15. 2025-09-08 20:01:14.921 CST [814476] LOG:  database system is ready to accept read-only connections
16. 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
17. 2025-09-08 20:01:14.921 CST [814480] LOG:  pausing at the end of recovery
18. 2025-09-08 20:01:14.921 CST [814480] HINT:  Execute pg_wal_replay_resume() to promote.

复制代码

部门测试截图

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。