一、概述

pg_probackup是一个管理 PostgreSQL 数据库集群备份和恢复的实用程序。 它旨在执行 PostgreSQL 实例的定期备份，能够在数据库发生故障时恢复服务器。

二、备份优势

• 全量备份 ： 全量备份会将数据库集下所有的数据文件进行备份

• 增量备份：增量备份备份的是上一次备份之后发生改变的数据，极大的节省了磁盘空间，加快了备份和恢复的速度。增量备份有三种模式（delta/page/ptrack）

• 增量恢复：page-level增量恢复允许通过重用目标目录中有效的未更改的页面来显著加快恢复速度

• 合并：可实施增量更新备份策略，无需定期进行完全备份

• 验证：自动数据一致性检查和按需备份验证，无需实际数据恢复

• 验证：使用 checkdb 命令按需验证 PostgreSQL 实例。

• 保留：根据保留策略管理 WAL 归档和备份，可以根据恢复时间或要保留的备份数量配置保留策略，以及指定特定备份的生存时间 (TTL)。 过期的备份可以合并或删除。

• 并行化：支持backup、restore、merge、delete、validate、checkdb

• 压缩：以压缩状态存储备份数据以节省磁盘空间

• 重复数据删除（Deduplication）：通过不复制未更改的非数据文件（例如 _vm 或 _fsm）来节省磁盘空间

• 远程操作：备份位于远程操作系统上的postgresql实例或远程恢复

• 备用服务器备份：通过从备用服务器进行备份来避免主服务器上的额外负载

• 外部目录：备份于postgresql数据库数据目录（PGDATA）之外的目录和文件

• 备份目录：以纯文本或json格式获取备份列表和相应的元信息

• 归档目录：以纯文本或json格式获取所有WAL时间线和相应元信息的列表

• 部分还原：仅还原指定的数据库或将指定的数据库排除在还原之外

三、增量备份的三种模式

full备份包含从头开始恢复数据库集群所需的所有数据文件

Incremental备份仅存储自上次备份以来已更改的数据。Incremental备份仅存储自上次备份以来已更改的数据。pg_probackup支持以下三种增量备份模式：

1. PAGE备份。在这种模式下，pg_probackup从上次完整或增量备份开始扫描归档中的所有 WAL 文件。新创建的备份仅包含 WAL 记录中提到的页面。这要求自上次备份以来的所有 WAL 文件都存在于 WAL归档中。

2. DELTA备份。在此模式下，pg_probackup读取 PGDATA 目录中的所有数据文件，并且仅复制自上次备份以来更改的那些页面。它的运行不需要连续存档。此模式可能会施加与备份相等的只读 I/O 压力。

3. PTRACK备份。在这种模式下，PostgreSQL 动态跟踪页面更改。它的运行不需要连续存档。每次更新关系页面时，该页面都会在PTRACK此关系的特殊位图中标记。由于一页只需要PTRACK分叉中的一位，因此此类位图非常小。跟踪意味着数据库服务器操作的一些小开销，加快了增量备份。仅支持Postgres Pro Standard and Postgres Pro Enterprise

pg_probackup只能进行物理在线备份，在线备份需要 WAL 进行一致恢复。因此，无论选择何种备份模式（FULL、PAGE 或 DELTA），使用 pg_probackup进行的任何备份都必须使用以下 WAL 交付模式之一：

• archive ：此类备份依赖于连续归档来确保一致的恢复

• stream：此类备份包括在进行备份时将集群恢复到一致状态所需的所有文件。无论是否 设置了连续归档，一致恢复所需的 WAL 段在备份期间通过复制协议流式传输并包含到备份文件中。这就是为什么这种备份被称为自治（ autonomous）或独立（ standalone）。

四、pg_probackup 工具的一些局限性：

1.仅支持Postgres Pro 9.5以上的版本

2.Windows系统不支持远程备份恢复

3.在Unix系统在，如果数据库版本小于等于 Postgresql 10, 备份用户必须是安装PG的操作系统用户。例如，如果PostgreSQL服务器由 user 启动postgres，则该backup命令也必须由 user 运行postgres。要在使用 SSH 以远程模式进行备份时满足此要求,您必须将 --remote-user选项设置为postgres.

4.对于PostgreSQL 9.5版本数据库，进行备份的数据库账号必须具体superuser的角色，否则无法备份pg_create_restore_point(text) 、 pg_switch_xlog()

5.参数block_size 和wal_block_size备份和恢复服务器要一致，否则无法备份

6.通过 ssh 运行远程操作时，远程和本地 pg_probackup 版本必须相同。

五、安装

Index of /pg_probackup/rpm/2.5.5/centos-7-x86_64/

rpm方式：

rpm -ivh http://repo.postgrespro.ru/pg_probackup/keys/pg_probackup-repo-centos.noarch.rpm 
yum install -y  pg_probackup-12.x86_64
yum install -y  pg_probackup-12-debuginfo.x86_64
复制

源码包：

$ wget https://github.com/postgrespro/pg_probackup/archive/2.5.5.tar.gz
解压源码
$ tar zxvf 2.5.5.tar.gz
进入源码目录
$ cd pg_probackup-2.5.5/

编译   ---PG_CONFIG是我们pg_config程序所在路径，top_srcdir为postgres源码所在路径
$ make USE_PGXS=1 PG_CONFIG=/usr/pgsql-12/bin/pg_config top_srcdir=/opt/postgresql-12.6

安装
$ make USE_PGXS=1 PG_CONFIG=/usr/pgsql-12/bin/pg_config top_srcdir=/opt/postgresql-12.6 install

查看安装版本
$ /usr/pgsql-12/bin/pg_probackup version
pg_probackup 2.5.5 (PostgreSQL 12.6)

复制

六、配置

1、初始化catalog

用于存放备份文件和归档日志文件

创建目录：

mkdir   /backup
chown  -R postgres:postgres   /backup
chmod  755   /backup
复制

初始化backup catalog

/usr/bin/pg_probackup-12 init -B /backup/
INFO: Backup catalog '/backup' successfully inited
复制

pg_probackup创建备份目录，其中包含以下子目录：

• wal/ -wal文件目录。

• backups/-备份数据文件的目录。

如果使用非postgres用户需要配置以下权限，本文使用postgres用户操作

$ psql -p5432 -Upostgres 

BEGIN; 
CREATE ROLE backup WITH LOGIN REPLICATION; 
GRANT USAGE ON SCHEMA pg_catalog TO backup; 
GRANT EXECUTE ON FUNCTION pg_catalog.current_setting(text) TO backup; 
GRANT EXECUTE ON FUNCTION pg_catalog.pg_is_in_recovery() TO backup; 
GRANT EXECUTE ON FUNCTION pg_catalog.pg_start_backup(text, boolean, boolean) TO backup; 
GRANT EXECUTE ON FUNCTION pg_catalog.pg_stop_backup(boolean, boolean) TO backup; 
GRANT EXECUTE ON FUNCTION pg_catalog.pg_create_restore_point(text) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_switch_wal() TO backup; 
GRANT EXECUTE ON FUNCTION pg_catalog.pg_last_wal_replay_lsn() TO backup; 
GRANT EXECUTE ON FUNCTION pg_catalog.txid_current() TO backup; 
GRANT EXECUTE ON FUNCTION pg_catalog.txid_current_snapshot() TO backup; 
GRANT EXECUTE ON FUNCTION pg_catalog.txid_snapshot_xmax(txid_snapshot) TO backup; 
GRANT EXECUTE ON FUNCTION pg_catalog.pg_control_checkpoint() TO backup; 
COMMIT;
复制

2、添加实例

#本地实例
/usr/bin/pg_probackup-12   add-instance -B backup_dir -D data_dir --instance instance_name [remote_options]


本地添加备份instance 示例
[postgres@pg03 ~]$ pg_probackup-12 add-instance -B /backup/ -D /postgresql/data/ --instance pg01
INFO: Instance 'pg01' successfully inited
复制

##添加远程实例
/usr/bin/pg_probackup-12  add-instance -B ${backup_dir} -D ${PGDATA} --instance ${instance_name} --remote-prot=ssh --remote-host=${remote_ip} --remote-port=${remote_ssh_port} --remote-user=${remote_ssh_user} --remote-path=${pg_probackup_dir}

示例：
/usr/bin/pg_probackup-12  add-instance -B /backup/  -D /postgresql/data/  --instance pg01 --remote-proto=ssh --remote-host=120.27.250.75 --remote-port=22 --remote-user=postgres --remote-path=/usr/local/pg_probackup-2.5.5/
INFO: Instance 'pg01' successfully inited
复制

• data_dir是您要备份的集群的数据目录。要设置和使用 pg_probackup，需要对该目录的写入权限。

• instance_name是将存储此集群的 WAL 和备份文件的子目录的名称。

• remote_options是可选参数，仅当位于远程系统上 时才需要指定 。

--remote-proto=proto

指定用于远程操作的协议。目前仅支持 SSH 协议。可能的值为：

* ssh通过 SSH 启用远程模式。这是默认值。

* none明确禁用远程模式。

如果 --remote-host指定了该选项，则可以省略该选项。

--remote-host=destination,

指定要连接的远程主机 IP 地址或主机名。

--remote-port=port

指定要连接的远程主机端口。

默认：22

--remote-user=username

指定 SSH 连接的远程主机用户。如果省略此选项，则使用启动 SSH 连接的当前用户。

--remote-path=path

指定远程系统上的pg_probackup安装目录。

3、数据库配置

vi  postgresql.conf
archive_mode = on
wal_level = 'replica'
max_wal_senders=10
archive_command = '/usr/bin/pg_probackup-12 archive-push -B /backup/ --instance pg01 --wal-file-path=%p --wal-file-name=%f'
复制

pg_hba.conf配置

1）本地备份

数据库 pg_hba.conf 配置文件中对 connection 和 replication 的放通

local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust
复制

2）远程备份

互信配置

## 在远程备份实例主机上
# su - postgres
$ ssh-keygen            
$ ssh-copy-id postgres@${备份机_ip}
## 在备份机上
# su - postgres
$ ssh-keygen            
$ ssh-copy-id postgres@${备份实例主机_ip}
## 测试互信
$ ssh postgres@${对方IP}
复制

数据库 pg_hba.conf 配置文件中对 connection 和 replication 的放通

local   all             all                                     md5
host    all             all             xxx(备份机)             trust
复制

七、备份

全量备份

命令：pg_probackup backup -B backup_dir --instance instance_name -b backup_mode



示例：
[postgres@pg01 ~]$ /usr/bin/pg_probackup-12  backup -B /backup/ --instance pg01 -b full
INFO: Backup start, pg_probackup version: 2.5.5, instance: pg01, backup ID: R5J921, backup mode: FULL, wal mode: ARCHIVE, remote: false, compress-algorithm: none, compress-level: 1
WARNING: This PostgreSQL instance was initialized without data block checksums. pg_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option '--data-checksums'.
WARNING: Current PostgreSQL role is superuser. It is not recommended to run pg_probackup under superuser.
INFO: wait for pg_start_backup()
INFO: pg_probackup archive-push WAL file: 000000010000000000000001, threads: 1/1, batch: 1/1, compression: none
INFO: pg_probackup archive-push completed successfully, pushed: 1, skipped: 0, time elapsed: 99ms
INFO: pg_probackup archive-push WAL file: 000000010000000000000002, threads: 1/1, batch: 1/1, compression: none
INFO: pg_probackup archive-push completed successfully, pushed: 1, skipped: 0, time elapsed: 47ms
INFO: Wait for WAL segment /backup/wal/pg03/000000010000000000000002 to be archived
WARNING: Skip hidden file: '/postgresql/data/.s.PGSQL.5432.lock'
WARNING: Skip hidden file: '/postgresql/data/.s.PGSQL.5432'
INFO: PGDATA size: 31MB
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 1s
2022-01-11 14:37:15.173 CST [36411] LOG:  restore point "pg_probackup, backup_id R5J921" created at 0/30000C8
2022-01-11 14:37:15.173 CST [36411] STATEMENT:  SELECT pg_catalog.pg_create_restore_point($1)
INFO: pg_probackup archive-push WAL file: 000000010000000000000002.00000028.backup, threads: 1/1, batch: 1/1, compression: none
INFO: pg_probackup archive-push completed successfully, pushed: 1, skipped: 0, time elapsed: 2ms
INFO: pg_probackup archive-push WAL file: 000000010000000000000003, threads: 1/1, batch: 1/1, compression: none
INFO: pg_probackup archive-push completed successfully, pushed: 1, skipped: 0, time elapsed: 48ms
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executed
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 0
INFO: Validating backup R5J921
INFO: Backup R5J921 data files are valid
INFO: Backup R5J921 resident size: 31MB
INFO: Backup R5J921 completed
复制

• FULL — 创建一个完整备份，其中包含要恢复的集群的所有数据文件。

• DELTA — 读取数据目录中的所有数据文件，并为自上次备份以来已更改的页面创建增量备份。

• PAGE — 根据自上次完整或增量备份以来生成的 WAL 文件创建增量备份。仅从数据文件中读取更改的块。

• PTRACK — 动态创建增量备份跟踪页面更改。

要备份位于数据目录之外的目录，请使用--external-dirs指定此目录路径的可选参数。如果您想添加多个外部目录，您可以提供多个路径，在 Linux 系统上用冒号分隔 --external-dirs=/etc/dir1:/etc/dir2

并行执行由 -j/--threads命令行选项控制 例如：

/usr/bin/pg_probackup-12  backup -B /backup/ --instance pg01 -b full  -j  4   #用四个并行创建备份
复制

增量备份

-- 增量备份DELTA
[postgres@pg01 ~]$ /usr/pgsql-12/bin/pg_probackup-12 backup -B /backup/   --instance pg01 -b delta
INFO: Backup start, pg_probackup version: 2.5.5, instance: pg01, backup ID: R5JAMZ, backup mode: DELTA, wal mode: ARCHIVE, remote: false, compress-algorithm: none, compress-level: 1
WARNING: This PostgreSQL instance was initialized without data block checksums. pg_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option '--data-checksums'.
WARNING: Current PostgreSQL role is superuser. It is not recommended to run pg_probackup under superuser.
INFO: wait for pg_start_backup()
INFO: pg_probackup archive-push WAL file: 000000010000000000000004, threads: 1/1, batch: 1/1, compression: none
INFO: Parent backup: R5J921
INFO: pg_probackup archive-push completed successfully, pushed: 1, skipped: 0, time elapsed: 80ms
INFO: pg_probackup archive-push WAL file: 000000010000000000000005, threads: 1/1, batch: 1/1, compression: none
INFO: pg_probackup archive-push completed successfully, pushed: 1, skipped: 0, time elapsed: 110ms
INFO: Wait for WAL segment /backup/wal/pg01/000000010000000000000005 to be archived
WARNING: Skip hidden file: '/postgresql/data/.s.PGSQL.5432.lock'
WARNING: Skip hidden file: '/postgresql/data/.s.PGSQL.5432'
INFO: PGDATA size: 31MB
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 0
2022-01-11 15:11:24.561 CST [38603] LOG:  restore point "pg_probackup, backup_id R5JAMZ" created at 0/6000090
2022-01-11 15:11:24.561 CST [38603] STATEMENT:  SELECT pg_catalog.pg_create_restore_point($1)
INFO: pg_probackup archive-push WAL file: 000000010000000000000005.00000028.backup, threads: 1/1, batch: 1/1, compression: none
INFO: pg_probackup archive-push completed successfully, pushed: 1, skipped: 0, time elapsed: 0ms
INFO: pg_probackup archive-push WAL file: 000000010000000000000006, threads: 1/1, batch: 1/1, compression: none
INFO: pg_probackup archive-push completed successfully, pushed: 1, skipped: 0, time elapsed: 38ms
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executed
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 0
INFO: Validating backup R5JAMZ
INFO: Backup R5JAMZ data files are valid
INFO: Backup R5JAMZ resident size: 111kB
INFO: Backup R5JAMZ completed
复制

-- 增量备份page
[postgres@pg01 ~]$ /usr/pgsql-12/bin/pg_probackup-12 backup -B /backup/ --instance pg01 -b page
INFO: Backup start, pg_probackup version: 2.5.5, instance: pg01, backup ID: R5JAO0, backup mode: PAGE, wal mode: ARCHIVE, remote: false, compress-algorithm: none, compress-level: 1
WARNING: This PostgreSQL instance was initialized without data block checksums. pg_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option '--data-checksums'.
WARNING: Current PostgreSQL role is superuser. It is not recommended to run pg_probackup under superuser.
INFO: wait for pg_start_backup()
INFO: pg_probackup archive-push WAL file: 000000010000000000000007, threads: 1/1, batch: 1/1, compression: none
INFO: pg_probackup archive-push completed successfully, pushed: 1, skipped: 0, time elapsed: 78ms
INFO: Parent backup: R5JAMZ
INFO: pg_probackup archive-push WAL file: 000000010000000000000008, threads: 1/1, batch: 1/1, compression: none
INFO: pg_probackup archive-push completed successfully, pushed: 1, skipped: 0, time elapsed: 48ms
INFO: Wait for WAL segment /backup/wal/pg01/000000010000000000000008 to be archived
WARNING: Skip hidden file: '/postgresql/data/.s.PGSQL.5432.lock'
WARNING: Skip hidden file: '/postgresql/data/.s.PGSQL.5432'
INFO: PGDATA size: 31MB
INFO: Extracting pagemap of changed blocks
INFO: Pagemap successfully extracted, time elapsed: 0 sec
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 0
2022-01-11 15:12:01.288 CST [38650] LOG: restore point "pg_probackup, backup_id R5JAO0" created at 0/90000C8
2022-01-11 15:12:01.288 CST [38650] STATEMENT: SELECT pg_catalog.pg_create_restore_point($1)
INFO: pg_probackup archive-push WAL file: 000000010000000000000008.00000028.backup, threads: 1/1, batch: 1/1, compression: none
INFO: pg_probackup archive-push completed successfully, pushed: 1, skipped: 0, time elapsed: 0ms
INFO: pg_probackup archive-push WAL file: 000000010000000000000009, threads: 1/1, batch: 1/1, compression: none
INFO: pg_probackup archive-push completed successfully, pushed: 1, skipped: 0, time elapsed: 44ms
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executed
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 0
INFO: Validating backup R5JAO0
INFO: Backup R5JAO0 data files are valid
INFO: Backup R5JAO0 resident size: 111kB
INFO: Backup R5JAO0 completed
复制

查看备份

[postgres@pg03 ~]$ /usr/bin/pg_probackup-12 show -B /backup/
BACKUP INSTANCE 'pg01'
================================================================================================================================
Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status
================================================================================================================================
pg01 12 R5J921 2022-01-11 14:37:15+08 FULL ARCHIVE 1/0 3s 31MB 16MB 1.00 0/2000028 0/30000F0 OK

复制

[postgres@pg01 ~]$ /usr/in/pg_probackup-12 show -B /backup/ --instance pg01 -i R5J921
#Configuration
backup-mode = FULL
stream = false
compress-alg = none
compress-level = 1
from-replica = false

#Compatibility
block-size = 8192
xlog-block-size = 8192
checksum-version = 0
program-version = 2.5.4
server-version = 12


#Result backup info
timelineid = 1
start-lsn = 0/2000028
stop-lsn = 0/30000F0
start-time = '2022-01-11 14:37:13+08'
end-time = '2022-01-11 14:37:16+08'
recovery-xid = 489
recovery-time = '2022-01-11 14:37:15+08'
data-bytes = 32785806
wal-bytes = 16777216
uncompressed-bytes = 32758991
pgdata-bytes = 32758752
status = OK
primary_conninfo = 'user=postgres host=/postgresql/data port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'
content-crc = 1005076216
复制

[postgres@pg01 ~]$ /usr/bin/pg_probackup-12 show -B /backup/ --instance pg01 --archive
ARCHIVE INSTANCE 'pg01'
===============================================================================================================================
TLI Parent TLI Switchpoint Min Segno Max Segno N segments Size Zratio N backups Status
===============================================================================================================================
1 0 0/0 000000010000000000000001 000000010000000000000003 3 48MB 1.00 1 OK

复制

远程备份

配置互信
[postgres@remote ~]$ ssh-keygen -t rsa
[postgres@pg03 ~]$ ssh-keygen -t rsa
[postgres@remote ~]$ ssh-copy-id -i .ssh/id_rsa.pub pg01
[postgres@pg01 ~]$ ssh-copy-id -i .ssh/id_rsa.pub remote
复制

测试一下：

[postgres@remote ~]$ ssh pg01 date
Fri Jan 14 10:08:53 CST 2022

[postgres@pg01~]$ ssh remote date
Fri Jan 14 14:03:59 CST 2022
复制

--备份库 添加备份实例

remote端：
初始化备份目录：
[postgres@remote ~]$ /usr/bin/pg_probackup-12 init -B /backup/
INFO: Backup catalog '/backup' successfully inited
复制

--备份库 添加备份实例
[postgres@remote ~]$ /usr/bin/pg_probackup-12 add-instance -B /backup -D /postgresql/data --instance pg01 --remote-proto=ssh --remote-host=192.168.122.113 --remote-port=22 --remote-user=postgres --remote-path=/usr/bin/ --ssh-options='-o ServerAliveCountMax=5 -o ServerAliveInterval=60'
INFO: Instance 'pg01' successfully inited
复制

--remote-proto=proto

指定用于远程操作的协议。目前仅支持 SSH 协议。可能的值为：

ssh通过 SSH 启用远程模式。这是默认值。

none 明确禁用远程模式。

如果 --remote-host指定了该选项，则可以省略该选项。

--remote-host=destination,

指定要连接的远程主机 IP 地址或主机名。

--remote-port=port

指定要连接的远程主机端口。

默认： 22

--remote-user=username

指定 SSH 连接的远程主机用户。如果省略此选项，则使用启动 SSH 连接的当前用户。

--remote-path=path

指定远程系统上的pg_probackup安装目录。

--ssh-options=ssh_options

提供一串 SSH 命令行选项。例如，以下选项可用于设置pg_probackup打开的keep-aliveSSH 连接： --ssh-options="-o ServerAliveCountMax=5 -o ServerAliveInterval=60"

--备份需要用到postgres超级用户；所以在pg_hba.conf;

host all postgres 192.168.122.38/24 trust

-- postgresql.conf

wal_level = replica
archive_mode = on
archive_command = '/usr/bin/pg_probackup-12 archive-push -B /backup --instance pg01 --wal-file-path=%p --wal-file-name=%f --remote-proto=ssh --remote-host=192.168.122.38 --remote-port=22 --remote-user=postgres --remote-path=/usr/bin'
复制

- 备份库； 执行远程备份

[postgres@remote ~]$ /usr/bin/pg_probackup-12 backup -B /backup --instance pg03 -b full --remote-proto=ssh --remote-host=192.168.122.113 --remote-port=22 --remote-user=postgres --remote-path=/usr/bin --ssh-options='-o ServerAliveCountMax=5 -o ServerAliveInterval=60'
复制

---增量备份（ delta）模式

[postgres@remote ~]$ /usr/bin/pg_probackup-12 backup -B /backup --instance pg01 -b delta --remote-proto=ssh --remote-host=192.168.122.113 --remote-port=22 --remote-user=postgres --remote-path=/usr/bin --ssh-options='-o ServerAliveCountMax=5 -o ServerAliveInterval=60'
复制

---增量备份（ page）模式

[postgres@remote ~]$/usr/bin/pg_probackup-12 backup -B /backup --instance pg01 -b page --remote-proto=ssh --remote-host=192.168.122.113 --remote-port=22 --remote-user=postgres --remote-path=/usr/bin --ssh-options='-o ServerAliveCountMax=5 -o ServerAliveInterval=60'
复制

查看备份

[postgres@remote ~]$ /usr/bin/pg_probackup-12 show -B /backup/BACKUP INSTANCE 'pg01'
==================================================================================================================================
Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status
==================================================================================================================================
pg01 12 R5U54D 2022-01-17 11:45:52+08 FULL ARCHIVE 1/0 5s 31MB 16MB 1.00 0/1F000028 0/200000B8 OK
pg01 ---- R5U52P ---- FULL ARCHIVE 0/0 0 0 0 1.00 0/0 0/0 ERROR
pg01 ---- R5U508 ---- FULL ARCHIVE 0/0 0 0 0 1.00 0/0 0/0 ERROR
pg01 ---- R5U4YD ---- FULL ARCHIVE 0/0 0 0 0 1.00 0/0 0/0 ERROR
复制

查看详细备份

[postgres@remote ~]$ /usr/pgsql-12/bin/pg_probackup-12 show -B /backup/ --instance pg01 -i R5U54D
#Configuration
backup-mode = FULL
stream = false
compress-alg = none
compress-level = 1
from-replica = false

#Compatibility
block-size = 8192
xlog-block-size = 8192
checksum-version = 0
program-version = 2.5.4
server-version = 12

#Result backup info
timelineid = 1
start-lsn = 0/1F000028
stop-lsn = 0/200000B8
start-time = '2022-01-17 11:45:49+08'
end-time = '2022-01-17 11:45:54+08'
recovery-xid = 489
recovery-time = '2022-01-17 11:45:52+08'
data-bytes = 32785993
wal-bytes = 16777216
uncompressed-bytes = 32759178
pgdata-bytes = 32758937
status = OK
primary_conninfo = 'user=postgres host=192.168.122.113 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'
content-crc = 2026536059
复制

查看归档详细

[postgres@remote ~]$ /usr/bin/pg_probackup-12 show -B /backup/ --instance pg01 --archiveARCHIVE INSTANCE 'pg01'
==================================================================================================================================
TLI Parent TLI Switchpoint Min Segno Max Segno N segments Size Zratio N backups Status
==================================================================================================================================
1 0 0/0 00000001000000000000000B 000000010000000000000023 18 288MB 1.00 2 DEGRADED
复制

配置 Retention Policy

使用pg_probackup，可以配置保留策略以删除冗余备份、清理不需要的 WAL 文件以及固定特定备份以确保它们保留指定的时间，

设置--retention-redundancy(保留几个完整的备份)

/usr/bin/pg_probackup-12 set-config -B /backup --instance pg01 --retention-redundancy=7
复制

设置--retention-window（保留备份的天数）

/usr/bin/pg_probackup-12 set-config -B /backup --instance pg01 --retention-window=7

删除过期数据

/usr/bin/pg_probackup-12 delete -B /backup/ --instance pg01 --delete-expired

--同时删除过期WAL

/usr/bin/pg_probackup-12 delete -B /backup/ --instance pg01 --delete-expired --delete-wal

--使用新策略覆盖当前策略删除

/usr/bin/pg_probackup-12 delete -B /backup/ --instance pg01 --delete-expired --delete-wal --retention-window=1 --retention-redundancy=1

增量合成(merge)

为了防止过多的增量备份集占用过多的备份空间，同时为了方便管理，可以将指定的增量备份和全量备份合并为一个新的全量备份

/usr/bin/pg_probackup-12 merge -B backup_dir --instance instance_name -i backup_id

backup_id为需要合成的增量备份id

[postgres@remote ~]$ /usr/bin/pg_probackup-12 show -B /backup/
BACKUP INSTANCE 'pg01'
===================================================================================================================================
Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status
===================================================================================================================================
pg01 12 R5UOFV 2022-01-17 18:43:09+08 PAGE ARCHIVE 1/1 3s 111kB 16MB 1.00 0/2A000028 0/2B0000F0 OK
pg01 12 R5UOFC 2022-01-17 18:42:51+08 FULL ARCHIVE 1/0 4s 31MB 16MB 1.00 0/28000028 0/290000F0 OK
复制

[postgres@remote ~]$ /usr/bin/pg_probackup-12 merge -B /backup/ --instance pg01 -i R5UOFV
INFO: Merge started
INFO: Merging backup R5UOFV with parent chain
INFO: Validate parent chain for backup R5UOFV
INFO: Validating backup R5UOFC
INFO: Backup R5UOFC data files are valid
INFO: Validating backup R5UOFV
INFO: Backup R5UOFV data files are valid
INFO: Start merging backup files
INFO: Backup files are successfully merged, time elapsed: 0
INFO: Delete: R5UOFV 2022-01-17 18:43:09+08
INFO: Rename merged full backup R5UOFC to R5UOFV
INFO: Validating backup R5UOFV
INFO: Backup R5UOFV data files are valid
INFO: Merge of backup R5UOFV completed
复制

执行集群验证

要验证PostgreSQL数据库集群是否未损坏，请运行以下命令：

/usr/bin/pg_probackup-12 checkdb --backup-path=/backup --instance pg01 --pgdata=/postgresql/data --pguser=postgres --pgdatabase=postgres --pgport=5432

此命令通过运行页头完整性检查来对位于指定数据目录中的所有数据文件执行物理验证，如果启用了校验和，还会执行块级校验和验证。如果检测到损坏的页面，checkdb 将继续进行集群验证，直到验证集群中的所有页面。默认情况下，类似的页面验证会在pg_probackup 进行备份时自动执行 。该checkdb 命令使能够按需执行此类页面验证，而无需获取任何备份副本，即使集群根本没有使用pg_probackup备份。

检测备份集可用性

pg_probackup validate -B backup_dir --instance instance_name -i backupid [recovery target option]

例如检查是否能将备份集应用WAL恢复到最新的状态

[postgres@remote ~]$ /usr/bin/pg_probackup-12 validate -B /backup/ --instance pg01
INFO: Validate backups of the instance 'pg01'
INFO: Validating backup R5UOFV
INFO: Backup R5UOFV data files are valid
INFO: Backup R5UOFV WAL segments are valid
INFO: All backups are valid
复制

如示：通过检测备份集R5UOFV可以应用WAL恢复到最新状态

八、恢复

全量恢复

# 先关闭源数据库并备份原先的数据目录

$ pg_ctl stop
waiting for server to shut down.... done
server stopped
$ mv /postgresql/data/ /postgresql/data_bak/
复制

## 通过全备恢复

$ /usr/bin/pg_probackup-12 restore -B /backup --instance pg01 --recovery-target=latest
INFO: Validating backup QH2EIL
INFO: Backup R5J921 data files are valid
INFO: Backup validation completed successfully on time 2021-09-22 22:49:34+08, xid 658 and LSN 0/B0000B8
INFO: Backup R5J921 is valid.
INFO: Restoring the database from backup at 2022-01-11 15:12:01+08
INFO: Start restoring backup files. PGDATA size: 39MB
INFO: Backup files are restored. Transfered bytes: 39MB, time elapsed: 0
INFO: Restore incremental ratio (less is better): 100% (39MB/39MB)
INFO: Syncing restored files to disk
INFO: Restored backup files are synced, time elapsed: 4s
INFO: Restore of backup R5J921 completed.
复制

## 启动数据库并检查t1表数据恢复情况

$ pg_ctl start
postgres=# select count(*) from t1;
count
-------
8 　　　　　　　　//可以看到数据恢复成功
(1 row)
复制

当数据库恢复完成启动后，必须进行一此full备份，后续才能继续做增量备份

/usr/in/pg_probackup-12 backup -B /backup/ --instance pg01 -b full

部分恢复

1、指定要恢复的数据库

pg_probackup restore -B backup_dir --instance instance_name --db-include=database_name 　　　　#--db-include选项可以指定多次
示例：
/usr/pgsql-12/bin/pg_probackup-12 restore -B /backup --instance pg01 --db-include=db1 --db-include=db2 　　　　#恢复db1和db2数据库
复制

2、要从恢复中排除一个或多个数据库，可以使用--db-exclude

pg_probackup restore -B backup_dir--instance instance_name--db-exclude=database_name #该--db-exclude选项可以指定多次
示例：
/usr/pgsql-12/bin/pg_probackup-12 restore -B /backup --instance pg01 --db-exclude=db1 --db-exclude=db2 #从恢复中排除db1和db2数据库
复制

时间点 (PITR) 恢复

如果您在进行备份之前启用 了连续 WAL 归档，则可以使用 restore命令 的恢复目标选项将集群恢复到任意时间点（恢复目标）的状态。

1.要在准确时间恢复集群状态，可以使用--recovery-target-time

pg_probackup restore -B backup_dir --instance instance_name --recovery-target-time="2017-05-18 14:18:11+03"

示例：
/usr/bin/pg_probackup-12 restore -B /backup --instance pg01 --recovery-target-time="2017-05-18 14:18:11+03"
复制

2.要将集群状态恢复到特定的事务ID，可以使用以下--recovery-target-xid

pg_probackup restore -B backup_dir--instance instance_name --recovery-target-xid=687

示例：
/usr/bin/pg_probackup-12 restore -B /backup --instance pg01 --recovery-target-xid=687
复制

3.要将集群状态恢复到特定 LSN，请使用 --recovery-target-lsn以下选项：

pg_probackup restore -B backup_dir--instance instance_name --recovery-target-lsn=16/B374D848

示例：
/usr/binp/g_probackup-12 restore -B /backup --instance pg01 --recovery-target-lsn=16/B374D848
复制

4.要将备份恢复到 WAL 存档中可用的最新状态，请使用--recovery-target带有latest值的选项：

pg_probackup restore -B backup_dir--instance instance_name --recovery-target="latest"

示例：
/usr/bin/pg_probackup-12 restore -B /backup --instance pg01 --recovery-target=latest
复制

4.要将集群恢复到最早的一致性点，请使用--recovery-target具有以下值的选项 immediate：

pg_probackup restore -B backup_dir--instance instance_name --recovery-target='immediate'

示例：
/usr/bin/pg_probackup-12 restore -B /backup --instance pg01 --recovery-target='immediate'
复制

远程模式下使用pg_probackup恢复

配置restore_command

恢复命令：