pg_probackup备份
  IE5LYMWlmdvL 2023年11月02日 40 0

一、概述
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 版本必须相同。

五、安装

http://repo.postgrespro.ru/pg_probackup/rpm/2.5.5/centos-7-x86_64/

pg_probackup备份_pg_probackup

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/pgsql-12/bin/pg_probackup-12 init -B /backup/
INFO: Backup catalog '/backup' successfully inited

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

pg_probackup备份_pg_probackup_02

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

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

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/pgsql-12/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/pgsql-12/bin/g_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/pgsql-12/bin/g_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是可选参数，仅当位于远程系统上 时才需要指定 。
指定用于远程操作的协议。目前仅支持 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.数据库配置

archive_mode = on
wal_level = 'replica'
max_wal_senders=10
archive_command = '/usr/pgsql-12/bin/g_probackup-12 archive-push -B /backup/ --instance pg01 --wal-file-path=%p --wal-file-name=%f'

pg_hba.conf配置

数据库 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/pgsql-12/bin/g_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/pgsql-12/bin/g_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@pg01 ~]$ /usr/pgsql-12/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/pgsql-12/bin/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/pgsql-12/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@pg01 ~]$ 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/pgsql-12/bin/pg_probackup-12 init -B /backup/
INFO: Backup catalog '/backup' successfully inited

--备份库 添加备份实例
[postgres@remote ~]$  /usr/pgsql-12/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/pgsql-12/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/pgsql-12/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/pgsql-12/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）模式

/usr/pgsql-12/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/pgsql-12/bin/pg_probackup-12 show -B /backup/


BACKUP INSTANCE 'pg03'
==================================================================================================================================
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 pg03 -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/pgsql-12/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          00000001000000000000000B  000000010000000000000023  18          288MB  1.00    2          DEGRADED

配置 Retention Policy使用pg_probackup，可以配置保留策略以删除冗余备份、清理不需要的 WAL 文件以及固定特定备份以确保它们保留指定的时间，
设置–retention-redundancy(保留几个完整的备份)

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

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

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

删除过期数据

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

--同时删除过期WAL
/usr/pgsql-12/bin/pg_probackup-12 delete  -B /backup/  --instance pg01  --delete-expired  --delete-wal

--使用新策略覆盖当前策略删除
/usr/pgsql-12/bin/pg_probackup-12 delete  -B /backup/  --instance pg01  --delete-expired  --delete-wal  --retention-window=1 --retention-redundancy=1

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

/usr/pgsql-12/bin/pg_probackup-12 merge -B backup_dir --instance instance_name -i backup_id
backup_id为需要合成的增量备份id

[postgres@remote ~]$ /usr/pgsql-12/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/pgsql-12/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/pgsql-12/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/pgsql-12/bin/pg_probackup-12  validate -B /backup/ --instance pg01 
INFO: Validate backups of the instance 'pg03'
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/pgsql-12/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/pgsql-12/bin/g_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/g_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/g_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/pgsql-12/bin/g_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/pgsql-12/bin/g_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/pgsql-12/bin/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/pgsql-12/bin/g_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/pgsql-12/bin/g_probackup-12 restore -B  /backup  --instance pg01  --recovery-target='immediate'

远程模式下使用pg_probackup恢复
配置restore_command

restore_command = '/usr/pgsql-12/bin/g_probackup-12 archive-get -B /backup --instance pg01--wal-file-path=%p --wal-file-name=%f --remote-host=192.168.0.3 --remote-port=22 --remote-user=postgres'

恢复命令：

/usr/pgsql-12/bin/g_probackup-12  restore -B/backup  --instance pg01 --remote-user=postgres --remote-host=192.168.0.2 --remote-port=22
【版权声明】本文内容来自摩杜云社区用户原创、第三方投稿、转载，内容版权归原作者所有。本网站的目的在于传递更多信息，不拥有版权，亦不承担相应法律责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@moduyun.com
  1. 分享：
最后一次编辑于 2023年11月08日 0

暂无评论

推荐阅读
  dix3DMteueFN   2023年11月02日   46   0   0 openGausssed增量备份Backup
  IE5LYMWlmdvL   2023年11月02日   41   0   0 物理备份pg_probackup增量备份
IE5LYMWlmdvL
IE5LYMWlmdvL
作者其他文章 更多
postgresql 数据库面试题及知识梳理

2023-12-06

slowlog汇总报告工具slow-log-summary

2023-12-05

MySQL 数据库面试题及知识梳理

2023-12-05

Linux CPU软中断问题诊断之基础概念

2023-12-02

Oracle Hint(提示）之INDEX_SS、INDEX_SS_ASC、INDEX_SS_DESC

2023-11-30

Oracle Hint(提示）之INDEX_COMBINE

2023-11-28

Oracle Hint(提示）之LEADING

2023-11-26

Oracle Hint(提示）之ORDERED

2023-11-26

Oracle Hint(提示）之 PUSH_SUBQ、NO_PUSH_SUBQ

2023-11-25

insert死锁与唯一索引-mysql

2023-11-24

最新推荐 更多
LED Driver数码屏应用解决方案

2023-12-12

FastAdmin插件开发记录（一）一键生成CRUD后 关联id在列表和添加、编辑页显示名称

2023-12-12

申请获取数字高程模型12米、30米数据：TanDEM-X

2023-12-12

推荐一款好用的windows工具，推荐！

2023-12-12

3.5K star，开源效率工具推荐

2023-12-12

分享巴西劈裂试验代码

2023-12-12

如何实现抽屉式导航（ArkUI）

2023-12-12

蚂蚁集团mPaaS平台与华为达成合作 加速上千家App“鸿蒙化”进程

2023-12-12

国产鲲鹏920处理器服务器部署Proxmox VE Arm 8.x

2023-12-12

『江鸟中原』harmony 鸿蒙简单时钟开发

2023-12-11

LED面板显示屏驱动芯片

2023-12-11

FunAdmin安装记录

2023-12-11

推荐一款超级强大的软件，电脑必备

2023-12-11

pfc 常用fish语言及定义方法

2023-12-11

39.4k star，强推这款下载器，可以放弃迅雷了

2023-12-11

如何实现波纹进度条（ArkUI）

2023-12-11

云贝教育 |【技术文章】PostgreSQL中误删除数据怎么办（一）

2023-12-11

云贝教育 | 【PostgreSQL PGCA题库解析-6】在PostgreSQL的数据目录结构中,默认表空间的目录是哪个？

2023-12-11

超卓航科引领冷喷涂增材制造革新，推动先进核反应堆发展

2023-12-11

queryCoord的balancer分析

2023-12-11

常用标签
数据数据数据数据javalinuxlinuxpythonCentOSredisJavapython区块链技术区块链信息系统字符串MySQL资讯项目管理mysql教程MySQL