digoal
2023-12-22
PostgreSQL , PolarDB , DuckDB , 增量备份 , pg_combinebackup , INCREMENTAL backup
大实例备份最头痛的几个问题: 备份耗费空间大, 备份耗时, 中间任何原因导致备份失败重来的代价高昂.
PostgreSQL 17 内置块级别物理增量备份(INCREMENTAL backup/pg_combinebackup)功能, 之前可以通过pg probackup, pg_rman, zfs来实现类似功能.
通过pg_rman实现块级增量备份文章参考:
- 《PostgreSQL ptrack , pg_rman , 块级增量备份》
- 《PostgreSQL 最佳实践 - pg_rman 数据库恢复示例 与 软件限制解说》
- 《PostgreSQL 最佳实践 - pg_rman 以standby为源的备份浅析》
- 《PostgreSQL 最佳实践 - 块级别增量备份(pg_rman baseon LSN)源码浅析与使用》
通过zfs实现块级增量备份/快照备份文章参考:
- 《如何创建RDS PG 的秒级 flashback闪回实例, 实时容灾实例 - zfs - snapshot - clone - standby - compress》
- 《PostgreSQL 最佳实践 - 块级增量备份(ZFS篇)验证 - recovery test script for zfs snapshot clone + postgresql stream replication + archive》
- 《PostgreSQL 最佳实践 - 块级增量备份(ZFS篇)双机HA与块级备份部署》
- 《PostgreSQL 最佳实践 - 块级增量备份(ZFS篇)单个数据库采用多个zfs卷(如表空间)时如何一致性备份》
- 《PostgreSQL 最佳实践 - 块级增量备份(ZFS篇)备份集有效性自动校验》
- 《PostgreSQL 最佳实践 - 块级增量备份(ZFS篇)方案与实战》
1、Add a new WAL summarizer process. 启动后, 自动生成wal summarize信息. 记录在一段WAL日志的内容中, 文件大小的变化(新建? 大小?). 哪些block发生了变化, 需要被更新或需要删除?
lsn范围, 以及对应每个文件(relation)的统计信息:
- 被创建或销毁的文件(relation)
- 文件(relation)缩小至某个值
- 无效块号
- 文件(relation)中被修改过的blocks id (不记录被truncate但是后面没有被修改过的blocks id).
WAL summarizer文件存储位置:
$PGDATA/pg_wal/summaries
每个 summary file 包含的信息:
- 1、certain range of LSNs on a certain TLI.
- 2、For each relation, it stores :
- 2.1、a "limit block" which is 0(文件被创建或销毁) if a relation is created or destroyed withina certain range of WAL records,
- 2.2、or otherwise the shortest length(文件缩小至某个值) to which the relation was truncated during that range of WAL records,
- 2.3、or otherwise InvalidBlockNumber(无效块号).
- 2.4、In addition, it stores a list of blocks which have been modified during that range of WAL records, (被修改过的blocks id). but excluding blocks which were removed by truncation after they were modified and never subsequently modified again. (不记录被truncate并且后面没有被修改过的blocks id)
Add a new WAL summarizer process.
author Robert Haas <rhaas@postgresql.org>
Wed, 20 Dec 2023 13:41:09 +0000 (08:41 -0500)
committer Robert Haas <rhaas@postgresql.org>
Wed, 20 Dec 2023 13:42:28 +0000 (08:42 -0500)
commit 174c480508ac25568561443e6d4a82d5c1103487
tree f42caba7a5f9a468e927107a58406a28a9f28ef2 tree
parent 00498b718564cee3530b76d860b328718aed672b commit | diff
Add a new WAL summarizer process.
When active, this process writes WAL summary files to
$PGDATA/pg_wal/summaries. Each summary file contains information for a
certain range of LSNs on a certain TLI. For each relation, it stores a
"limit block" which is 0 if a relation is created or destroyed within
a certain range of WAL records, or otherwise the shortest length to
which the relation was truncated during that range of WAL records, or
otherwise InvalidBlockNumber. In addition, it stores a list of blocks
which have been modified during that range of WAL records, but
excluding blocks which were removed by truncation after they were
modified and never subsequently modified again.
In other words, it tells us which blocks need to copied in case of an
incremental backup covering that range of WAL records. But this
doesn't yet add the capability to actually perform an incremental
backup; the next patch will do that.
A new parameter summarize_wal enables or disables this new background
process. The background process also automatically deletes summary
files that are older than wal_summarize_keep_time, if that parameter
has a non-zero value and the summarizer is configured to run.
Patch by me, with some design help from Dilip Kumar and Andres Freund.
Reviewed by Matthias van de Meent, Dilip Kumar, Jakub Wartak, Peter
Eisentraut, and Álvaro Herrera.
Discussion: http://postgr.es/m/CA+TgmoYOYZfMCyOXFyC-P+-mdrZqm5pP2N7S-r0z3_402h9rsA@mail.gmail.com
新增2个参数
- summarize_wal: 是否启动wal summarize. 主从库都可以启动wal summarize.
- wal_summary_keep_time: 保留的wal summary文件的时长. 默认10天.
https://www.postgresql.org/docs/devel/runtime-config-wal.html#RUNTIME-CONFIG-WAL-SUMMARIZATION
2、pg_basebackup 支持增量备份, pg_combinebackup支持增量备份级的合并.
pg_basebackup --incremental=PATH_TO_MANIFEST
增量备份的文件名: INCREMENTAL.${ORIGINAL_NAME}
pg_combinebackup 将一个全量备份+一个或多个增量备份合并为一个全新的全量备份.
Add support for incremental backup.
author Robert Haas <rhaas@postgresql.org>
Wed, 20 Dec 2023 14:49:12 +0000 (09:49 -0500)
committer Robert Haas <rhaas@postgresql.org>
Wed, 20 Dec 2023 14:49:12 +0000 (09:49 -0500)
commit dc212340058b4e7ecfc5a7a81ec50e7a207bf288
tree 79ffec15f6a8d9fce1333b99dd0b587e2459d38f tree
parent 174c480508ac25568561443e6d4a82d5c1103487 commit | diff
Add support for incremental backup.
To take an incremental backup, you use the new replication command
UPLOAD_MANIFEST to upload the manifest for the prior backup. This
prior backup could either be a full backup or another incremental
backup. You then use BASE_BACKUP with the INCREMENTAL option to take
the backup. pg_basebackup now has an --incremental=PATH_TO_MANIFEST
option to trigger this behavior.
An incremental backup is like a regular full backup except that
some relation files are replaced with files with names like
INCREMENTAL.${ORIGINAL_NAME}, and the backup_label file contains
additional lines identifying it as an incremental backup. The new
pg_combinebackup tool can be used to reconstruct a data directory
from a full backup and a series of incremental backups.
Patch by me. Reviewed by Matthias van de Meent, Dilip Kumar, Jakub
Wartak, Peter Eisentraut, and Álvaro Herrera. Thanks especially to
Jakub for incredibly helpful and extensive testing.
Discussion: http://postgr.es/m/CA+TgmoYOYZfMCyOXFyC-P+-mdrZqm5pP2N7S-r0z3_402h9rsA@mail.gmail.com
更多pg_basebackup增量备份用法参考:
pg_combinebackup 参考:
3、为了支持增量备份, 流复制协议的修改如下：
https://www.postgresql.org/docs/devel/protocol-replication.html
UPLOAD_MANIFEST
- Uploads a backup manifest in preparation for taking an incremental backup.
BASE_BACKUP [ ( option [, ...] ) ]
- Instructs the server to start streaming a base backup. The system will automatically be put in backup mode before the backup is started, and taken out of it when the backup is complete. The following options are accepted:
- ...
INCREMENTAL- Requests an incremental backup. The UPLOAD_MANIFEST command must be executed before running a base backup with this option.
就算你的数据块数据没有变化, 由于PG使用32位的xid, 所以需要vacuum freeze, freeze就会修改tuple head, 导致数据块发生变化.
记得我之前写过vacuum freeze/vacuum风暴?
- 《PostgreSQL Freeze 风暴预测续 - 珍藏级SQL》
- 《PostgreSQL freeze 风暴导致的IOPS飙升 - 事后追溯》
- 《PostgreSQL的"天气预报" - 如何预测Freeze IO风暴》
当增量备份遇到freeze, 你可能会遇到增量备份风暴.
