简介
PostgreSQL 17 引入了一个重要的新功能：支持使用 pg_basebackup 执行增量备份。这一改进极大地简化了备份和恢复的操作流程，并提高了备份效率，尤其适用于备份数据量较大的场景。
参数变化
PG 17提供了两个参数来支持增量备份功能而无需借助第三方备份工具(pg_probackup、pg_rman、pgbackrest等)
第一个参数是summarize_wal，控制是否开启walsummarizer进程来记录WAL摘要信息。
默认情况下，此进程不会启用，但需要启用后增量备份才能正常工作。设置为on之后，从操作系统能看到walsummarizer进程，使用如下SQL语句也能观测到该后台进程。
该进程会在pg_wal/summaries目录下创建 WAL 文件的摘要信息，内容是非文本形式。
1ALTER SYSTEM SET summarize_wal = 'on';
2SELECT pg_reload_conf();
3SELECT * FROM pg_stat_activity WHERE backend_type = 'walsummarizer';
4
5
6
7[pg17@lhrpgalloe bk]$ ps -ef|grep walsummarizer
8pg17 667311 3450 0 15:07 ? 00:00:00 postgres: walsummarizer
9pg17 670529 667844 0 15:55 pts/7 00:00:00 grep --color=auto walsummarizer
10[pg17@lhrpgalloe bk]$
11[pg17@lhrpgalloe pg_wal]$ cd summaries/
12[pg17@lhrpgalloe summaries]$ ll
13total 32
14-rw------- 1 pg17 pg17 3376 Dec 4 15:07 00000001000000088D0E4D80000000088D24FEC0.summary
15-rw------- 1 pg17 pg17 986 Dec 4 15:07 00000001000000088D24FEC0000000088D2A4B30.summary
16-rw------- 1 pg17 pg17 7300 Dec 4 15:07 00000001000000088D2A4B30000000088EDBC4F8.summary
17-rw------- 1 pg17 pg17 32 Dec 4 15:29 00000001000000088EDBC4F8000000088F000028.summary
18-rw------- 1 pg17 pg17 32 Dec 4 15:34 00000001000000088F0000280000000891000028.summary
19-rw------- 1 pg17 pg17 32 Dec 4 15:39 0000000100000008910000280000000892000060.summary
20-rw------- 1 pg17 pg17 32 Dec 4 15:52 0000000100000008920000600000000893000028.summary
21[pg17@lhrpgalloe summaries]$
22[pg17@lhrpgalloe summaries]$ pwd
23/pg17/pgdata/pg_wal/summaries
24[pg17@lhrpgalloe summaries]$ more 00000001000000088D2A4B30000000088EDBC4F8.summary
25{+eM@
26
27
28
29postgres=# \x
30Expanded display is on.
31postgres=# SELECT * FROM pg_stat_activity WHERE backend_type = 'walsummarizer';
32-[ RECORD 1 ]----+------------------------------
33datid |
34datname |
35pid | 667311
36leader_pid |
37usesysid |
38usename |
39application_name |
40client_addr |
41client_hostname |
42client_port |
43backend_start | 2024-12-04 15:07:04.998946+08
44xact_start |
45query_start |
46state_change |
47wait_event_type | Activity
48wait_event | WalSummarizerWal
49state |
50backend_xid |
51backend_xmin |
52query_id |
53query |
54backend_type | walsummarizer
55
56postgres=# select pg_available_wal_summaries();
57 pg_available_wal_summaries
58----------------------------
59 (1,8/8D0E4D80,8/8D24FEC0)
60 (1,8/8D24FEC0,8/8D2A4B30)
61 (1,8/8D2A4B30,8/8EDBC4F8)
62 (1,8/8EDBC4F8,8/8F000028)
63 (1,8/8F000028,8/91000028)
64 (1,8/91000028,8/92000060)
65 (1,8/92000060,8/93000028)
66(7 rows)
67
68postgres=#
walsummarizer进程会将WAL的摘要信息写入PGDATA/pg_wal/summaries目录下。
第二个参数wal_summary_keep_time设置为非0的值时，根据时间周期自动清理WAL摘要信息文件，默认策略是自动删除10天前的文件。
1postgres=# show wal_summary_keep_time;
2 wal_summary_keep_time
3-----------------------
4 10d
5(1 row)
工具pg_combinebackup 介绍
在 PostgreSQL 17 中，新增了工具 pg_combinebackup，这是一个用于合并全量备份和增量备份的实用工具。它的主要作用是将一系列增量备份整合到全量备份中，生成一个新的完整备份，简化管理并提高恢复的效率。
1pg_combinebackup full_20241204152955 inc_20241204153430 -o bk_all
若是备份为tar格式，则需要解压缩才可以，否则报错：
1[pg17@lhrpgalloe bk]$ pg_combinebackup full_20241204152955 inc_20241204153430 -o bk_all
2pg_combinebackup: error: could not open file "inc_20241204153430/PG_VERSION": No such file or directory
pg_basebackup示例
1[pg17@lhrpgalloe bk]$ pg_basebackup --help
2pg_basebackup takes a base backup of a running PostgreSQL server.
3
4Usage:
5 pg_basebackup [OPTION]...
6
7Options controlling the output:
8 -D, --pgdata=DIRECTORY receive base backup into directory
9 -F, --format=p|t output format (plain (default), tar)
10 -i, --incremental=OLDMANIFEST
11 take incremental backup
12 -r, --max-rate=RATE maximum transfer rate to transfer data directory
13 (in kB/s, or use suffix "k" or "M")
14 -R, --write-recovery-conf
15 write configuration for replication
16 -t, --target=TARGET[:DETAIL]
17 backup target (if other than client)
18 -T, --tablespace-mapping=OLDDIR=NEWDIR
19 relocate tablespace in OLDDIR to NEWDIR
20 --waldir=WALDIR location for the write-ahead log directory
21 -X, --wal-method=none|fetch|stream
22 include required WAL files with specified method
23 -z, --gzip compress tar output
24 -Z, --compress=[{client|server}-]METHOD[:DETAIL]
25 compress on client or server as specified
26 -Z, --compress=none do not compress tar output
27
28General options:
29 -c, --checkpoint=fast|spread
30 set fast or spread (default) checkpointing
31 -C, --create-slot create replication slot
32 -l, --label=LABEL set backup label
33 -n, --no-clean do not clean up after errors
34 -N, --no-sync do not wait for changes to be written safely to disk
35 -P, --progress show progress information
36 -S, --slot=SLOTNAME replication slot to use
37 -v, --verbose output verbose messages
38 -V, --version output version information, then exit
39 --manifest-checksums=SHA{224,256,384,512}|CRC32C|NONE
40 use algorithm for manifest checksums
41 --manifest-force-encode
42 hex encode all file names in manifest
43 --no-estimate-size do not estimate backup size in server side
44 --no-manifest suppress generation of backup manifest
45 --no-slot prevent creation of temporary replication slot
46 --no-verify-checksums
47 do not verify checksums
48 --sync-method=METHOD
49 set method for syncing files to disk
50 -?, --help show this help, then exit
51
52Connection options:
53 -d, --dbname=CONNSTR connection string
54 -h, --host=HOSTNAME database server host or socket directory
55 -p, --port=PORT database server port number
56 -s, --status-interval=INTERVAL
57 time between status packets sent to server (in seconds)
58 -U, --username=NAME connect as specified database user
59 -w, --no-password never prompt for password
60 -W, --password force password prompt (should happen automatically)
61
62Report bugs to <pgsql-bugs@lists.postgresql.org>.
63PostgreSQL home page: <https://www.postgresql.org/>
增量备份恢复示例
全量+增量备份
1-- 配置参数
2ALTER SYSTEM SET summarize_wal = 'on';
3SELECT pg_reload_conf();
4
5
6-- 造数据(每2秒持续做update操作)
7
8create database db1;
9\c db1
10
11CREATE TABLE t_hash AS
12SELECT id, md5(id::text)
13FROM generate_series(1, 2000000) AS id;
14
15
16CREATE TABLE t1 (last_updated timestamptz);
17INSERT INTO t1 (last_updated) VALUES (now());
18
19
20db1=# \dt+
21 List of relations
22 Schema | Name | Type | Owner | Persistence | Access method | Size | Description
23--------+--------+-------+----------+-------------+---------------+------------+-------------
24 public | t1 | table | postgres | permanent | heap | 8192 bytes |
25 public | t_hash | table | postgres | permanent | heap | 131 MB |
26(2 rows)
27
28db1=# UPDATE t1 SET last_updated = now(); \watch 2
29UPDATE 1
30UPDATE 1
31UPDATE 1
32。。。。。。。。 <=== (每2秒持续做update操作)
33
34
35
36-- 全量备份
37pg_basebackup -D /bk/full_$(date +%Y%m%d%H%M%S) -Ft -z -c fast -P -v
38
39
40
41-- 增量备份
42pg_basebackup -D /bk/inc_$(date +%Y%m%d%H%M%S)_full_20241204173216 -Ft -z -c fast --incremental=/bk/full_20241204173216/backup_manifest -P -v
日志：
1[pg17@lhrpgalloe bk]$ pg_basebackup -D /bk/full_$(date +%Y%m%d%H%M%S) -Ft -z -c fast -P -v
2pg_basebackup: initiating base backup, waiting for checkpoint to complete
3WARNING: skipping special file "./.s.PGSQL.5442"
4pg_basebackup: checkpoint completed
5pg_basebackup: write-ahead log start point: 0/E0000D8 on timeline 1
6pg_basebackup: starting background WAL receiver
7pg_basebackup: created temporary replication slot "pg_basebackup_74737"
8WARNING: skipping special file "./.s.PGSQL.5442"20241204164738/base.tar.gz)
9165094/165094 kB (100%), 1/1 tablespace
10pg_basebackup: write-ahead log end point: 0/E0009E8
11pg_basebackup: waiting for background process to finish streaming ...
12pg_basebackup: syncing data to disk ...
13pg_basebackup: renaming backup_manifest.tmp to backup_manifest
14pg_basebackup: base backup completed
15[pg17@lhrpgalloe bk]$ pg_basebackup -D /bk/full_$(date +%Y%m%d%H%M%S) -Ft -z -c fast -P -v
16pg_basebackup: initiating base backup, waiting for checkpoint to complete
17WARNING: skipping special file "./.s.PGSQL.5442"
18pg_basebackup: checkpoint completed
19pg_basebackup: write-ahead log start point: 0/10000028 on timeline 1
20pg_basebackup: starting background WAL receiver
21pg_basebackup: created temporary replication slot "pg_basebackup_74743"
22WARNING: skipping special file "./.s.PGSQL.5442"20241204164800/base.tar.gz)
23165094/165094 kB (100%), 1/1 tablespace
24pg_basebackup: write-ahead log end point: 0/1000AC90
25pg_basebackup: waiting for background process to finish streaming ...
26pg_basebackup: syncing data to disk ...
27pg_basebackup: renaming backup_manifest.tmp to backup_manifest
28pg_basebackup: base backup completed
29[pg17@lhrpgalloe bk]$ ll
30total 0
31drwx------ 2 pg17 pg17 85 Dec 4 16:47 full_20241204164738
32drwx------ 2 pg17 pg17 85 Dec 4 16:48 full_20241204164800
33[pg17@lhrpgalloe bk]$ du -sh ./full*
3457M ./full_20241204164738
3557M ./full_20241204164800
36[pg17@lhrpgalloe bk]$ pg_basebackup -D /bk/inc_$(date +%Y%m%d%H%M%S) -Ft -z -c fast --incremental=/bk/full_20241204164738/backup_manifest -P -v
37pg_basebackup: initiating base backup, waiting for checkpoint to complete
38WARNING: skipping special file "./.s.PGSQL.5442"
39pg_basebackup: checkpoint completed
40pg_basebackup: write-ahead log start point: 0/12000028 on timeline 1
41pg_basebackup: starting background WAL receiver
42pg_basebackup: created temporary replication slot "pg_basebackup_74755"
43WARNING: skipping special file "./.s.PGSQL.5442"
44 4996/165117 kB (100%), 1/1 tablespace
45pg_basebackup: write-ahead log end point: 0/12000120
46pg_basebackup: waiting for background process to finish streaming ...
47pg_basebackup: syncing data to disk ...
48pg_basebackup: renaming backup_manifest.tmp to backup_manifest
49pg_basebackup: base backup completed
50[pg17@lhrpgalloe bk]$ pg_basebackup -D /bk/inc_$(date +%Y%m%d%H%M%S) -Ft -z -c fast --incremental=/bk/full_20241204164800/backup_manifest -P -v
51pg_basebackup: initiating base backup, waiting for checkpoint to complete
52WARNING: skipping special file "./.s.PGSQL.5442"
53pg_basebackup: checkpoint completed
54pg_basebackup: write-ahead log start point: 0/14000028 on timeline 1
55pg_basebackup: starting background WAL receiver
56pg_basebackup: created temporary replication slot "pg_basebackup_74761"
57WARNING: skipping special file "./.s.PGSQL.5442"
58 4996/165117 kB (100%), 1/1 tablespace
59pg_basebackup: write-ahead log end point: 0/14000120
60pg_basebackup: waiting for background process to finish streaming ...
61pg_basebackup: syncing data to disk ...
62pg_basebackup: renaming backup_manifest.tmp to backup_manifest
63pg_basebackup: base backup completed
64[pg17@lhrpgalloe bk]$ ll
65total 0
66drwx------ 2 pg17 pg17 85 Dec 4 16:47 full_20241204164738
67drwx------ 2 pg17 pg17 85 Dec 4 16:48 full_20241204164800
68drwx------ 2 pg17 pg17 85 Dec 4 16:48 inc_20241204164838
69drwx------ 2 pg17 pg17 85 Dec 4 16:48 inc_20241204164851
70[pg17@lhrpgalloe bk]$ pg_basebackup -D /bk/inc_$(date +%Y%m%d%H%M%S) -Ft -z -c fast --incremental=/bk/inc_20241204164851/backup_manifest -P -v
71pg_basebackup: initiating base backup, waiting for checkpoint to complete
72WARNING: skipping special file "./.s.PGSQL.5442"
73pg_basebackup: checkpoint completed
74pg_basebackup: write-ahead log start point: 0/16000028 on timeline 1
75pg_basebackup: starting background WAL receiver
76pg_basebackup: created temporary replication slot "pg_basebackup_74768"
77WARNING: skipping special file "./.s.PGSQL.5442"
78 4943/165118 kB (100%), 1/1 tablespace
79pg_basebackup: write-ahead log end point: 0/16000120
80pg_basebackup: waiting for background process to finish streaming ...
81pg_basebackup: syncing data to disk ...
82pg_basebackup: renaming backup_manifest.tmp to backup_manifest
83pg_basebackup: base backup completed
84[pg17@lhrpgalloe bk]$ ll
85total 0
86drwx------ 2 pg17 pg17 85 Dec 4 16:47 full_20241204164738
87drwx------ 2 pg17 pg17 85 Dec 4 16:48 full_20241204164800
88drwx------ 2 pg17 pg17 85 Dec 4 16:48 inc_20241204164838
89drwx------ 2 pg17 pg17 85 Dec 4 16:48 inc_20241204164851
90drwx------ 2 pg17 pg17 85 Dec 4 16:49 inc_20241204164905
91[pg17@lhrpgalloe bk]$ du -sh ./*
9257M ./full_20241204164738
9357M ./full_20241204164800
94276K ./inc_20241204164838
95276K ./inc_20241204164851
96272K ./inc_20241204164905
97
恢复到全量
1pg_ctl stop
2rm -rf /pg17/pgdata/*
3rm -rf /pg17/pgdata/.s.PGSQL.5442.lock
4
5tar -zvxf /bk/full_20241204164800/base.tar.gz -C /pg17/pgdata/
6tar -zvxf /bk/full_20241204164800/pg_wal.tar.gz -C /pg17/pgdata/pg_wal
7pg_ctl start
8rm -rf /pg17/pgdata/standby.signal
9
恢复到增量
1pg_ctl stop
2rm -rf /pg17/pgdata/*
3rm -rf /pg17/pgdata/.s.PGSQL.5442.lock
4
5rm -rf /bk/full/pg_wal/*
6mkdir -p /bk/full/pg_wal/
7tar -zvxf /bk/full_20241204164738/base.tar.gz -C /bk/full/
8tar -zvxf /bk/full_20241204164738/pg_wal.tar.gz -C /bk/full/pg_wal/
9cp /bk/full_20241204164738/backup_manifest /bk/full/
10
11rm -rf /bk/inc/pg_wal/*
12mkdir -p /bk/inc/pg_wal/
13tar -zvxf /bk/inc_20241204164838/base.tar.gz -C /bk/inc/
14tar -zvxf /bk/inc_20241204164838/pg_wal.tar.gz -C /bk/inc/pg_wal/
15cp /bk/inc_20241204164838/backup_manifest /bk/inc/
16
17
18pg_combinebackup /bk/full /bk/inc -o /pg17/pgdata/
19
20
21pg_ctl start
22rm -rf /pg17/pgdata/standby.signal
23
全量+增量备份脚本分享
周六做全量，其它基于周六的全量做增量备份。
1cat > /home/postgres/bkpg17.sh <<"EOF"
2#!/bin/bash
3
4
5
6. /home/postgres/.bash_profile
7
8# 配置备份目录
9BACKUP_DIR="/bk"
10LOG_FILE="${BACKUP_DIR}/bk_pg.log"
11
12# PostgreSQL 配置
13PG_USER="postgres"
14PG_HOST="127.0.0.1"
15PG_PORT="5432"
16
17
18
19# 删除7天前的备份文件
20find $BACKUP_DIR -ctime +7 -exec rm -rf {} \;
21
22
23# 当前日期和时间
24DATE=$(date +%Y-%m-%d)
25WEEKDAY=$(date +%u) # 1-7, 1表示周一，7表示周日
26
27
28# 记录日志函数
29log_message() {
30 echo "$(date +'%Y-%m-%d %H:%M:%S') - $1" >> "${LOG_FILE}"
31}
32log_message "=============================================================="
33
34
35# 检查 pg_basebackup 是否可用
36if ! command -v pg_basebackup &> /dev/null; then
37 log_message "pg_basebackup 未找到，请安装 PostgreSQL 客户端工具。"
38 exit 1
39fi
40
41# 备份逻辑
42if [ "${WEEKDAY}" -eq 6 ]; then
43 # 周六执行全量备份
44 log_message "开始执行全量备份..."
45 FULL_BK_DIR=${BACKUP_DIR}/full_$(date +%Y%m%d%H%M%S)
46
47 BACKUP_COMMAND="pg_basebackup -D $FULL_BK_DIR -h ${PG_HOST} -p ${PG_PORT} -U ${PG_USER} -Ft -z -c fast -P -v"
48 log_message "执行命令: ${BACKUP_COMMAND}"
49 eval ${BACKUP_COMMAND}
50 if [ $? -eq 0 ]; then
51 log_message "全量备份成功，存储于 $BACKUP_DIR/$FULL_BK_DIR"
52 else
53 log_message "全量备份失败。"
54 exit 1
55 fi
56else
57 # 其他时间执行增量备份
58 LATEST_FULL_BACKUP=$(ls -d ${BACKUP_DIR}/full_* | sort | tail -n 1 | xargs basename)
59 if [ -z "${LATEST_FULL_BACKUP}" ]; then
60 log_message "未找到全量备份，无法执行增量备份。请先执行一次全量备份。"
61 exit 1
62 fi
63
64 log_message "开始执行增量备份..."
65 INC_BK_DIR=${BACKUP_DIR}/inc_$(date +%Y%m%d%H%M%S)_${LATEST_FULL_BACKUP}
66 BACKUP_COMMAND="pg_basebackup -D $INC_BK_DIR -h ${PG_HOST} -p ${PG_PORT} -U ${PG_USER} -Ft -z -c fast --incremental=$BACKUP_DIR/$LATEST_FULL_BACKUP/backup_manifest -P -v"
67 log_message "执行命令: ${BACKUP_COMMAND}"
68 eval ${BACKUP_COMMAND}
69
70
71 if [ $? -eq 0 ]; then
72 log_message "增量备份成功，存储于 $BACKUP_DIR/$INC_BK_DIR"
73 else
74 log_message "增量备份失败。"
75 exit 1
76 fi
77fi
78
79log_message "备份任务完成。"
80EOF
81
82
83chmod +x /home/postgres/bkpg17.sh
84
85sh /home/postgres/bkpg17.sh
86
87
880 2 * * * /home/postgres/bkpg17.sh
总结
1、PG 17的pg_basebackup支持了增量备份，但需要配置ALTER SYSTEM SET summarize_wal = 'on';
2、增量备份可以基于全量，也可以基于某个增量
3、若有增量备份进行还原，则需要使用 pg_combinebackup 合并备份，否则报错：
12024-12-04 17:03:51.908 CST [75837] FATAL: this is an incremental backup, not a data directory
22024-12-04 17:03:51.908 CST [75837] HINT: Use pg_combinebackup to reconstruct a valid data directory.
故，增量备份进行恢复时比较麻烦，且当数据库较大时，需要的空间也比较大。
4、同样，可以使用s3fs挂载S3为本地存储，然后进行备份。
参考
https://mp.weixin.qq.com/s/w23jGqmQ-vg09_TzZpAHiw
https://blog.csdn.net/jerome_peng/article/details/142133144
https://pg-x.github.io/posts/2024-01-20-postgres-incremental-backup/
https://blog.csdn.net/yanzongshuai/article/details/135738562
https://www.postgresql.org/docs/current/app-pgbasebackup.html
’
