PostgreSQL备份恢复管理器pg_probackup

参考：
https://github.com/postgrespro/pg_probackup
https://postgrespro.github.io/pg_probackup/

pg_probackup简介

pg_probackup是由俄罗斯PG社区组织Postgres Professional发布的一个用于管理PostgreSQL数据库备份和恢复的工具。
它用于执行PostgreSQL实例的定期备份，使你能够在失败时恢复服务器。
最新版2.2.7支持PostgreSQL 9.5, 9.6, 10, 11, 12

优点：

1.增量备份：支持三种不同模式
2.数据有效性自动检测
3.验证：基于checkdb命令
4.历史过期数据处理
5.并行化：支持backup、restore、merge、delete、validate、checkdb
6.压缩
7.Deduplication：重复数据不备份(比如_vm或者_fsm)
8.支持远程操作：例如SSH
9.支持从standby备份
10.数据存储在外部目录：与PGDATA保持独立
11.备份元数据管理：命令配置持久化到文本文件
12.归档元数据管理：命令配置持久化到文本文件
13.并行恢复

使用限制

1.支持9.5+
2.远程备份不支持windows
3.Unix系统，PG10+，备份用户必须是安装PG的操作系统用户
4.PG9.5，非超级用户角色备份可能比超级用户备份慢
5.参数block_size 和wal_block_size备份和恢复服务器要一致
6.增量备份要在同一个时间线(timeline)下
7.从standby备份
支持PG版本 9.6+
备库配置 hot_standby = on
主库配置 full_page_writes = on

增量备份三种模式

第一种：DELTA backup
此模式读取PGDATA所有数据文件，拷贝上次备份以来的所有PAGE，IO压力与全备类似。

第二种：PAGE backup
此模式扫描归档目录里上次扫过之后的WAL文件，只拷贝WAL的PAGE，
需要配置持续归档才能使用。

第三种：PTRACK backup
仅支持Postgres Pro Standard and Postgres Pro Enterprise

演示环境

remote_host 192.168.99.200
backup_host 192.168.99.223
pg_probackup 2.2.7
pg 12.1

安装pg_probackup

源码编译安装

下载文件
$ wget https://github.com/postgrespro/pg_probackup/archive/2.2.7.tar.gz
解压源码
$ tar zxvf 2.2.7.tar.gz
进入源码目录
$ cd pg_probackup-2.2.7/
编译
$ make USE_PGXS=1 PG_CONFIG=/opt/pgsql/bin/pg_config top_srcdir=/opt/postgresql-12.1
安装
$ make USE_PGXS=1 PG_CONFIG=/opt/pgsql/bin/pg_config top_srcdir=/opt/postgresql-12.1 install
查看安装版本
$ /opt/pgsql/bin/pg_probackup version
pg_probackup 2.2.7 (PostgreSQL 12.1)

RPM安装

#RPM Centos Packages
rpm -ivh http://repo.postgrespro.ru/pg_probackup/keys/pg_probackup-repo-centos.noarch.rpm
yum install pg_probackup-{12,11,10,9.6,9.5}
yum install pg_probackup-{12,11,10,9.6,9.5}-debuginfo
 
#RPM RHEL Packages
rpm -ivh http://repo.postgrespro.ru/pg_probackup/keys/pg_probackup-repo-rhel.noarch.rpm
yum install pg_probackup-{12,11,10,9.6,9.5}
yum install pg_probackup-{12,11,10,9.6,9.5}-debuginfo

配置

/opt/pgsql/bin/initdb -D /home/postgres/data5432 \
--allow-group-access \
-Upostgres -W
 
1.Initialize the backup catalog
使用postgres用户执行pg_probackup创建backup catalog
$ /opt/pgsql/bin/pg_probackup init \
-B /home/postgres/pgdata_probackup
 
2.Add a new backup instance to the backup catalog.
pg_probackup可以在一个backup catalog存储多个实例
添加backup_host本地备份实例
$ /opt/pgsql/bin/pg_probackup add-instance \
-B /home/postgres/pgdata_probackup \
-D /home/postgres/data5432 \
 --instance local_5432
 
添加远程备份实例
 $ /opt/pgsql/bin/pg_probackup add-instance \
 -B /home/postgres/pgdata_probackup \
 -D /home/postgres/data5432 \
 --instance pg200_5432 \
 --remote-proto=ssh \
 --remote-host=192.168.99.200 \
 --remote-port=22 \
 --remote-user=postgres \
 --remote-path=/opt/pgsql/bin \
 --ssh-options='-o ServerAliveCountMax=5 -o ServerAliveInterval=60'
 
 删除备份实例
 /opt/pgsql/bin/pg_probackup del-instance \
 -B /home/postgres/pgdata_probackup \
 --instance pg200_5432 
 
上面操作需要配置SSH信任
$ ssh-copy-id postgres@192.168.99.200
 
3.Configure the database cluster to enable pg_probackup 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;
 
4.postgresql.conf配置
$ vi data5432/postgresql.conf
max_wal_senders设置合理值
wal_level = 'replica'
archive_mode = 'on'
本地实例
archive_command = '/opt/pgsql/bin/pg_probackup archive-push 
-B /home/postgres/pgdata_probackup 
--instance local_5432 
--wal-file-path=%p 
--wal-file-name=%f'
 
200远程实例
archive_command = '/opt/pgsql/bin/pg_probackup archive-push 
-B /home/postgres/pgdata_probackup 
--instance pg200_5432 --wal-file-path=%p --wal-file-name=%f [remote_options]'
remote_options参考如下
 --remote-proto=ssh \
 --remote-host=192.168.99.223 \
 --remote-port=22 \
 --remote-user=postgres \
 --remote-path=/opt/pgsql/bin \
 --ssh-options='-o ServerAliveCountMax=5 -o ServerAliveInterval=60'
 
$ ssh-copy-id postgres@192.168.99.223

全备(Full backups)

backup_host备份本地实例
$  /opt/pgsql/bin/pg_probackup backup \
-B /home/postgres/pgdata_probackup \
--instance local_5432 \
-b full
 
backup_host备份远程实例
$ /opt/pgsql/bin/pg_probackup backup \
-B /home/postgres/pgdata_probackup \
--instance pg200_5432 \
-b full
 
如果需包含外部目录
--external-dirs=/etc/dir1:/etc/dir2

增量备份(DELTA)

$ /opt/pgsql/bin/pg_probackup backup \
-B /home/postgres/pgdata_probackup \
--instance local_5432 \
-b delta
 
$ /opt/pgsql/bin/pg_probackup backup \
-B /home/postgres/pgdata_probackup \
--instance pg200_5432 \
-b delta

增量备份(PAGE)

$ /opt/pgsql/bin/pg_probackup backup \
-B /home/postgres/pgdata_probackup \
--instance local_5432 \
-b page
 
$ /opt/pgsql/bin/pg_probackup backup \
-B /home/postgres/pgdata_probackup \
--instance pg200_5432 \
-b page

查看可用备份

$ pg_probackup show -B /home/postgres/pgdata_probackup/

查看备份详细

$ pg_probackup show \
-B /home/postgres/pgdata_probackup/  \
--instance pg200_5432 \
-i Q5Q3O0

查看归档详细

$ pg_probackup show \
-B /home/postgres/pgdata_probackup/  \
--instance pg200_5432 \
--archive

配置 Retention Policy

pg_probackup set-config \
-B /home/postgres/pgdata_probackup/ \
--instance pg200_5432 \
--retention-redundancy=20
 
pg_probackup set-config \
-B /home/postgres/pgdata_probackup/ \
--instance pg200_5432 \
--retention-window=7

删除过期数据

pg_probackup delete \
-B /home/postgres/pgdata_probackup/ \
--instance pg200_5432 \
--delete-expired
 
--同时删除过期WAL
pg_probackup delete \
-B /home/postgres/pgdata_probackup/ \
--instance pg200_5432 \
--delete-expired \
--delete-wal
 
--使用新策略覆盖当前策略删除
pg_probackup delete \
-B /home/postgres/pgdata_probackup/ \
--instance pg200_5432 \
--delete-expired --delete-wal \
--retention-window=1 --retention-redundancy=1

检测PG实例checksum

pg_probackup checkdb \
--backup-path=/home/postgres/pgdata_probackup \
--instance local_5432 \
--pgdata=/opt/data5432 \
--pguser=postgres \
--pgdatabase=postgres \
--pgport=5432 

恢复

备份有效性检测

检测所有备份

pg_probackup validate \
--backup-path=/home/postgres/pgdata_probackup \
--instance local_5432

检测单个备份

pg_probackup validate \
--backup-path=/home/postgres/pgdata_probackup \
--instance local_5432 \
--backup-id=QMZB1Q

223 back host操作
pg_probackup restore  \
-B /home/postgres/pgdata_probackup/ \
-D /home/postgres/data5432 \
--instance pg200_5432 \
--remote-user=postgres \
--remote-host=192.168.99.200 \
--remote-port=22 \
--archive-host=192.168.99.223 \
--archive-port=22 \
--archive-user=postgres
 
恢复之后需要重做基础备份，后续才能继续做增量备份
/opt/pgsql/bin/pg_probackup backup \
-B /home/postgres/pgdata_probackup \
--instance pg200_5432 \
-b full
 
再次恢复
pg_probackup restore  \
-B /home/postgres/pgdata_probackup/ \
-D /home/postgres/data5432 \
--instance pg200_5432 \
--remote-user=postgres \
--remote-host=192.168.99.200 \
--remote-port=22 \
--archive-host=192.168.99.223 \
--archive-port=22 \
--archive-user=postgres

保持联系

本人组建了一个技术交流群：PG乐知乐享交流群。欢迎关注文章的小伙伴随缘加入，进群请加本人微信skypkmoon并备注PG乐知乐享。