怒首领蜂大复活
1.80 GB · 2025-09-11
PostgreSQL 17版本的pg_baseback开始支持增量备份，终于可以像大多数的数据库物理备份工具一样支持增量备份了，下班后抽空尝试了一下，跟其他数据库的物理备份类似，还是比较简单的。
以下基于一个月前发布的PostgreSQL 17.6为测试环境，利用pg_basebackup，基于full+incremental+wal日志的备份，做一个基于时间点的恢复（Point-In-Time Recovery (PITR) ）测试。
1，sudo apt-get install moreutils ，该依赖包用于记录备份日志时增加一个时间戳
2，summarize_wal= on，pg_basebackup在做增量备份的时候，要求打开summarize_wal
3，其他参数不一一列举，wal日志级别，开启wal日志归档，开启日志等等
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:00insert 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（该行为手动备注）*******************************************
利用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
--step 14 恢复至新实例1，停止新实例的服务systemctl stop postgresql98002，移除新实例的数据目录（仅测试，有必要的话需要备份）3，将pg_combinebackup的文件cp到新实例的数据文件路径下4，需改上述新实例的数据文件下配置文件的端口号（我在本地用多实例测试的，因此两个实例的端口号不能一样）5，创建恢复标记文件，touch recovery.signal6，修改文件属性 chown -R postgres:postgres /usr/local/pgsql17/pg9800 chmod 700 -R /usr/local/pgsql17/pg98007，修改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 postgresql980011，查询数据，按预期的恢复至2025-09-08 19:52:00select * 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-bit2025-09-08 20:01:14.453 CST [814476] LOG: listening on IPv4 address "0.0.0.0", port 98002025-09-08 20:01:14.454 CST [814476] LOG: listening on IPv6 address "::", port 98002025-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 CSTcp: cannot stat '/usr/local/pgbackup/wal/00000002.history': No such file or directory2025-09-08 20:01:14.573 CST [814480] LOG: starting backup recovery with redo LSN 0/34000028, checkpoint LSN 0/34000080, on timeline ID 12025-09-08 20:01:14.588 CST [814480] LOG: restored log file "000000010000000000000034" from archive2025-09-08 20:01:14.735 CST [814480] LOG: starting point-in-time recovery to 2025-09-08 19:52:00+082025-09-08 20:01:14.740 CST [814480] LOG: redo starts at 0/340000282025-09-08 20:01:14.755 CST [814480] LOG: restored log file "000000010000000000000035" from archivecp: cannot stat '/usr/local/pgbackup/wal/000000010000000000000036': No such file or directory2025-09-08 20:01:14.921 CST [814480] LOG: completed backup recovery with redo LSN 0/34000028 and end LSN 0/340001202025-09-08 20:01:14.921 CST [814480] LOG: consistent recovery state reached at 0/340001202025-09-08 20:01:14.921 CST [814476] LOG: database system is ready to accept read-only connections2025-09-08 20:01:14.921 CST [814480] LOG: recovery stopping before commit of transaction 784, time 2025-09-08 19:52:23.904561+082025-09-08 20:01:14.921 CST [814480] LOG: pausing at the end of recovery2025-09-08 20:01:14.921 CST [814480] HINT: Execute pg_wal_replay_resume() to promote.部分测试截图
