參考：
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樂知樂享。