参考： 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乐知乐享。
