动态

详情 返回 返回

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目錄示例
image

 

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.

部分測試截圖image

postgresql , postgresql
点赞

Add a new 评论

Some HTML is okay.