pg_probackup备份与恢复

最新推荐文章于 2025-02-17 17:05:35 发布

jerry-89

最新推荐文章于 2025-02-17 17:05:35 发布

阅读量2.9k

点赞数 3

分类专栏： pg 文章标签： postgresql 数据库

本文链接：https://blog.csdn.net/eagle89/article/details/111663939

版权

pg 专栏收录该内容

70 篇文章

订阅专栏

一、pg_probackup概述

pg_probackup 是一款免费的postgres数据库集群备份工具，与其他备份工具相比，它主要有如下一些优势：

提供增量备份，增量备份一定程度上可以节省磁盘空间的使用并且减少备份时间消耗
可通过全量备份+增量备份进行增量恢复
无需通过实际的数据恢复操作验证备份文件是否有效
Verification: on-demand verification of Postgres Pro instance with the checkdb command.
可以通过设置恢复时间以及备份最大文件数来进行备份文件以及WAL归档日志的保留策略
对于backup、restore、merge、delete、validate、checkdb操作都可开启并发线程执行
提供压缩备份以节省磁盘空间
可对远程实例进行备份恢复
可从standby实例进行备份
对于PGDATA外的目录数据（如：脚本、日志转储、sql dump 文件等），可使用参数额外指定进行备份
可查看已备份数据备份以及归档的列表以及相关详细信息
支持部分还原（还原部分数据库）

pg_probackup 下的几种备份方式：

全量备份：全量备份会将数据库集下所有的数据文件进行备份
增量备份：增量备份仅会备份上一次全量备份之后发生变更的数据，相对于全量备份其空间占用有了极大的缩减

DELTA模式：在该模式下， pg_probackup 会扫描所有的数据目录文件，然后将上一次备份后发生改变的数据页进行拷贝备份。这种模式下增量备份的IO消耗基本等同于全量备份。

PAGE模式：在该模式下， pg_probackup仅会扫描备份上一次备份结束时刻之后的所有WAL归档日志。这种模式下的增量备份必须保证wal日志有设置合理的归档（wal_level > minimal 、archive_mode = on/always、archive_command 使用 pg_probackup进行archive-push 归档）。

PTRACK模式：在该模式下，pg_probackup 会实时跟踪源备份实例端数据页的变化，对于距上一次备份后发生更新的数据页，将其记录在 bitmap 中，以此来加快增量备份的时间。该模式下不需要关注WAL日志归档的设置，增量备份时间相对于DELTA更快，但是由于需要实时跟踪发生变化的数据页，所以对源端数据库服务器是有一定的资源消耗的。

pg_probackup 工具的一些局限性：

仅支持Postgres Pro 9.5以上的版本
Windows系统不支持远程备份恢复
在Unix系统在，如果数据库版本小于等于 Postgres Pro 10 ，只能通过与OS同账号的超级用户postgres进行备份。
对于PostgreSQL 9.5版本数据库，进行备份的数据库账号必须具体superuser的角色，否则无法备份 pg_create_restore_point(text) 、 pg_switch_xlog()
备份工具与数据库 block_size 、 wal_block_size 必须一致，否则无法备份（block_size、wal_block_size在安装包源码编译时设置）

二、安装部署

2.1 源码安装

1、下载安装包

# wget -c https://github.com/postgrespro/pg_probackup/archive/2.4.2.tar.gz
# tar xf 2.4.2.tar.gz

# cd pg_probackup-2.4.2/
# ll
总用量 176
drwxrwxr-x 2 root root   4096 7月   1 08:07 doc
-rw-rw-r-- 1 root root 128060 7月   1 08:07 Documentation.md
-rw-rw-r-- 1 root root   4976 7月   1 08:07 gen_probackup_project.pl
-rw-rw-r-- 1 root root   1200 7月   1 08:07 LICENSE
-rw-rw-r-- 1 root root   3962 7月   1 08:07 Makefile
-rw-rw-r-- 1 root root  13345 7月   1 08:07 README.md
drwxrwxr-x 3 root root   4096 7月   1 08:07 src
drwxrwxr-x 4 root root   4096 7月   1 08:07 tests
drwxrwxr-x 2 root root   4096 7月   1 08:07 travis

2、编译安装

# PG_CONFIG是我们pg_config程序所在路径，top_srcdir为postgres源码所在路径
# make USE_PGXS=1 PG_CONFIG=/usr/local/pgsql/bin/pg_config  top_srcdir=/usr/local/postgresql-12.2

# ll
总用量 612
drwxrwxr-x 2 root root   4096 7月   1 08:07 doc
-rw-rw-r-- 1 root root 128060 7月   1 08:07 Documentation.md
-rw-rw-r-- 1 root root   4976 7月   1 08:07 gen_probackup_project.pl
-rw-rw-r-- 1 root root   1200 7月   1 08:07 LICENSE
-rw-rw-r-- 1 root root   3962 7月   1 08:07 Makefile
-rwxr-xr-x 1 root root 445832 9月  22 21:55 pg_probackup            //编译后
-rw-rw-r-- 1 root root  13345 7月   1 08:07 README.md
drwxrwxr-x 3 root root   4096 9月  22 21:55 src
drwxrwxr-x 4 root root   4096 7月   1 08:07 tests
drwxrwxr-x 2 root root   4096 7月   1 08:07 travis

3、版本检查

# 查看版本
# ./pg_probackup --version
pg_probackup 2.4.2 (PostgreSQL 12.2)

2.2 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
或者

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

三、常用命令

$ pg_probackup init -B ${backup_dir}

添加实例

## 本地实例
pg_probackup  add-instance -B ${backup_dir} -D ${PGDATA} --instance ${instance_name}


## 添加远程实例
pg_probackup  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}

修改参数POSTGRESQL.CONF

配置文件中需要对 archive_command 进行改变

archive_mode = on

archive_command = 'pg_probackup-11 archive-push -B /pgdata/backup --instance pg_test --wal-file-path %p --wal-file-name %f'

max_wal_senders = 10

hot_standby = on

full_page_writes = on

备份

## 本地实例全量备份
pg_probackup backup -B ${backup_dir} --instance ${instance_name} -b full

## 远程实例全量备份
pg_probackup backup -B ${backup_dir} --instance ${instance_name} --remote-user=${remote_ssh_user} --remote-host=${remote_ip} --remote-port=${remote_ssh_port} -b full

## 增量备份

pg_probackup backup -B -B ${backup_dir} --instance ${instance_name} -b page|detla|ptrack

恢复

## 根据备份集恢复
pg_probackup restore -B ${backup_dir} --instance ${instance_name} -i ${backup_id}

## 不完整恢复，恢复部分database
pg_probackup restore -B ${backup_dir} --instance ${instance_name} --db-include=${database_name1} --db-include=${database_name2}

## 按时间点恢复
pg_probackup restore -B ${backup_dir} --instance ${instance_name} --recovery-target-time='2020-09-22 22:49:34'
pg_probackup restore -B ${backup_dir} --instance ${instance_name} --recovery-target-xid='687'
pg_probackup restore -B ${backup_dir} --instance ${instance_name} --recovery-target-lsn='16/B374D848'
pg_probackup restore -B ${backup_dir} --instance ${instance_name} --recovery-target-name='before_app_upgrade'
pg_probackup restore -B ${backup_dir} --instance ${instance_name} --recovery-target='latest'
pg_probackup restore -B ${backup_dir} --instance ${instance_name} -recovery-target='immediate'

查看备份文件可用性

pg_probackup show -B ${backup_dir} --instance ${instance_name} -i ${backup_id}

查看备份详情

pg_probackup show -B ${backup_dir} --instance ${instance_name}  -i ${backup_id}
#Configuration
backup-mode = FULL                  //备份模式
stream = false                      //是否启用stream
compress-alg = none
compress-level = 1                  //压缩等级
from-replica = false

#Compatibility
block-size = 8192                   //blocksize
xlog-block-size = 8192
checksum-version = 0
program-version = 2.4.2
server-version = 12                 //PG Version

#Result backup info
timelineid = 1
start-lsn = 0/A000028
stop-lsn = 0/B0000B8
start-time = '2020-09-22 22:49:33+08'
end-time = '2020-09-22 22:49:39+08'
recovery-xid = 658
recovery-time = '2020-09-22 22:49:34+08'
data-bytes = 41423956
wal-bytes = 16777216
uncompressed-bytes = 41389959
pgdata-bytes = 41389720
status = OK
primary_conninfo = 'user=postgres port=5432 sslmode=disable sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any'
content-crc = 1486842437

查看归档详情

pg_probackup show -B ${backup_dir} --instance ${instance_name} --archive

配置 Retention Policy

–retention-redundancy=redundancy : 保留备份多少天 FULL

–retention-window=window : 可恢复多少天之前备份

pg_probackup set-config -B ${backup_dir} --instance ${instance_name} --retention-redundancy 7 --retention-window 7


$ /usr/local/pg_probackup-2.4.2/pg_probackup show-config -B /data/pgdata_probackup/ --instance local_5432
# Backup instance information
pgdata = /data/pgsql12/data
system-identifier = 6870373621203487994
xlog-seg-size = 16777216
# Connection parameters
pgdatabase = postgres
# Replica parameters
replica-timeout = 5min
# Archive parameters
archive-timeout = 5min
# Logging parameters
log-level-console = INFO
log-level-file = OFF
log-filename = pg_probackup.log
log-rotation-size = 0TB
log-rotation-age = 0d
# Retention parameters
retention-redundancy = 7
retention-window = 7
wal-depth = 0
# Compression parameters
compress-algorithm = none
compress-level = 1
# Remote access parameters
remote-proto = ssh

删除过期数据

pg_probackup delete -B ${backup_dir} --instance ${instance_name} --delete-expired

--同时删除过期WAL
pg_probackup delete -B ${backup_dir} --instance ${instance_name} --delete-expired --delete-wal

--使用新策略覆盖当前策略删除
pg_probackup delete -B ${backup_dir} --instance ${instance_name} --delete-expired --delete-wal --retention-window=1 --retention-redundancy=1

四、工具使用

4.1 初始化配置

1、初始化备份目录

## 初始化备份目录
$ /usr/local/pg_probackup-2.4.2/pg_probackup init -B /data/pgdata_probackup
INFO: Backup catalog '/data/pgdata_probackup' successfully inited

## 初始化其实就是在该目录下创建一个backups目录一个wal目录
$ pwd
/data/pgdata_probackup
$ tree
.
├── backups
└── wal

2 directories, 0 files

2、一些必要的检查

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

3）备份数据库账号准备

若使用的数据库账号不是postgres超级账号，如果使用数据库账号backup进行备份工作，那么需要授予以下权限：

## For PostgreSQL 9.5:
BEGIN;
CREATE ROLE backup WITH LOGIN;
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) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_stop_backup() TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_create_restore_point(text) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_switch_xlog() 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;
COMMIT;

## For Postgres Pro 9.6:
BEGIN;
CREATE ROLE backup WITH LOGIN;
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) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_create_restore_point(text) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_switch_xlog() TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_last_xlog_replay_location() 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;

## For Postgres Pro 10 or higher:
BEGIN;
CREATE ROLE backup WITH LOGIN;
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;

3、添加需要备份实例信息

一个备份目录下可添加多个实例，可以是本地实例也可以是远程实例

–instance : 指定instance的名称
–remote-host : 指定远程备份实例IP
–remote-port : 指定远程备份实例SSH端口
–remote-user : 指定远程SSH用户
–remote-path : 指定远程备份实例pg_probackup工具所在路径

1）添加本地备份实例

## 添加本地实例
$ /usr/local/pg_probackup-2.4.2/pg_probackup  add-instance -B /data/pgdata_probackup -D /data/pg5433/ --instance local_5433
INFO: Instance 'local_5433' successfully inited

$ /usr/local/pg_probackup-2.4.2/pg_probackup  add-instance -B /data/pgdata_probackup -D /data/pgsql12/data/ --instance local_5432
INFO: Instance 'local_5432' successfully inited

2）添加远程备份实例

## 添加远程实例
$ /usr/local/pg_probackup-2.4.2/pg_probackup  add-instance -B /data/pgdata_probackup/ -D /data/pgsql/data --instance zijie_5432 --remote-proto=ssh --remote-host=120.27.250.75 --remote-port=22 --remote-user=postgres --remote-path=/usr/local/pg_probackup-2.4.2/
INFO: Instance 'zijie_5432' successfully inited

4、修改需要备份实例的配置文件

1）本地备份实例

## 本地备份实例
$ vi postgres.conf
max_wal_senders     设置合理值
wal_level = 'replica'
archive_mode = 'on'
archive_command = '/usr/local/pg_probackup-2.4.2/pg_probackup archive-push -B /data/pgdata_probackup --instance local_5432 --wal-file-path=%p --wal-file-name=%f'

2）远程备份实例

## 远程备份实例
max_wal_senders     设置合理值
wal_level = 'replica'
archive_mode = 'on'
archive_command = '/usr/local/pg_probackup-2.4.2/pg_probackup archive-push -B /data/pgdata_probackup --instance zijie_5432 --wal-file-path=%p --wal-file-name=%f --remote-proto=ssh --remote-host=124.70.209.222 --remote-port=22 --remote-user=postgres --remote-path=/usr/local/pg_probackup-2.4.2/'

4.2 数据备份(全备+增备)

1）本机实例数据备份

## 记录t1表中原本的数据量
db1=# select count(*) from t1;
 count
-------
     8
(1 row)

## 进行第一次全备
$ /usr/local/pg_probackup-2.4.2/pg_probackup backup -B /data/pgdata_probackup/ --instance local_5432 -b full
INFO: Backup start, pg_probackup version: 2.4.2, instance: local_5432, backup ID: QH2EIL, 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 backup or checkdb as superuser.
INFO: Wait for WAL segment /data/pgdata_probackup/wal/local_5432/00000001000000000000000A to be archived
INFO: PGDATA size: 39MB
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 0
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: 4s
INFO: Validating backup QH2EIL
INFO: Backup QH2EIL data files are valid
INFO: Backup QH2EIL resident size: 40MB
INFO: Backup QH2EIL completed


## 查看备份信息
$ /usr/local/pg_probackup-2.4.2/pg_probackup show -B /data/pgdata_probackup/

BACKUP INSTANCE 'local_5432'
===================================================================================================================================
 Instance    Version  ID      Recovery Time           Mode  WAL Mode  TLI   Time  Data   WAL  Zratio  Start LSN  Stop LSN   Status
===================================================================================================================================
 local_5432  12       QH2EIL  2020-09-22 22:49:34+08  FULL  ARCHIVE   1/0     6s  40MB  16MB    1.00  0/A000028  0/B0000B8  OK
 local_5432  ----     QH2EI6  ----                    FULL  ARCHIVE   0/0      0     0     0    1.00  0/0        0/0        ERROR
 local_5432  ----     QH2EHB  ----                    FULL  ARCHIVE   0/0      0     0     0    1.00  0/0        0/0        ERROR
 local_5432  ----     QH2EGB  ----                    FULL  ARCHIVE   0/0      0     0     0    1.00  0/0        0/0        ERROR
 local_5432  ----     QH2EFM  ----                    FULL  ARCHIVE   0/0      0     0     0    1.00  0/0        0/0        ERROR
 local_5432  12       QH2E90  ----                    FULL  ARCHIVE   1/0  3m:5s     0     0    1.00  0/5000028  0/0        ERROR

BACKUP INSTANCE 'local_5433'
=================================================================================================================
 Instance  Version  ID  Recovery Time  Mode  WAL Mode  TLI  Time  Data  WAL  Zratio  Start LSN  Stop LSN  Status
=================================================================================================================


## 手动插入部分数据，模拟增量数据
db1=# select count(*) from t1;
 count
-------
    16
(1 row)

## 在全备的基础上进行第一次增量备份
$ /usr/local/pg_probackup-2.4.2/pg_probackup backup -B /data/pgdata_probackup/ --instance local_5432 -b page
INFO: Backup start, pg_probackup version: 2.4.2, instance: local_5432, backup ID: QH2F68, 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 backup or checkdb as superuser.
INFO: Wait for WAL segment /data/pgdata_probackup/wal/local_5432/00000001000000000000000D to be archived
INFO: Parent backup: QH2EIL
INFO: PGDATA size: 39MB
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
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 QH2F68
INFO: Backup QH2F68 data files are valid
INFO: Backup QH2F68 resident size: 152kB
INFO: Backup QH2F68 completed



## 查看备份信息
$ /usr/local/pg_probackup-2.4.2/pg_probackup show -B /data/pgdata_probackup/

BACKUP INSTANCE 'local_5432'
====================================================================================================================================
 Instance    Version  ID      Recovery Time           Mode  WAL Mode  TLI   Time   Data   WAL  Zratio  Start LSN  Stop LSN   Status
====================================================================================================================================
 local_5432  12       QH2F68  2020-09-22 23:03:45+08  PAGE  ARCHIVE   1/1     2s  152kB  16MB    1.00  0/D000028  0/E0000F0  OK         //第一次增备
 local_5432  12       QH2EIL  2020-09-22 22:49:34+08  FULL  ARCHIVE   1/0     6s   40MB  16MB    1.00  0/A000028  0/B0000B8  OK         //全备
 local_5432  ----     QH2EI6  ----                    FULL  ARCHIVE   0/0      0      0     0    1.00  0/0        0/0        ERROR
 local_5432  ----     QH2EHB  ----                    FULL  ARCHIVE   0/0      0      0     0    1.00  0/0        0/0        ERROR
 local_5432  ----     QH2EGB  ----                    FULL  ARCHIVE   0/0      0      0     0    1.00  0/0        0/0        ERROR
 local_5432  ----     QH2EFM  ----                    FULL  ARCHIVE   0/0      0      0     0    1.00  0/0        0/0        ERROR
 local_5432  12       QH2E90  ----                    FULL  ARCHIVE   1/0  3m:5s      0     0    1.00  0/5000028  0/0        ERROR

BACKUP INSTANCE 'local_5433'
=================================================================================================================
 Instance  Version  ID  Recovery Time  Mode  WAL Mode  TLI  Time  Data  WAL  Zratio  Start LSN  Stop LSN  Status
=================================================================================================================

## 查看归档明细
pg_probackup-13 show -B /home/probackup_pg/ --instance pg_13db --archive --format=json

## 增量合成(merge)

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

pg_probackup merge -B backup_dir --instance instance_name -i backup_id

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

## 基于PITR备份与恢复
-- 备份 pg_probackup backup -B /data/postgres/probackup --instance local_6000 -b full 

--恢复之前需要先停库；
在清理PGDATA目录以及外在的表空间目录 pg_ctl stop;rm -rf $PGDATA/* 
--恢复到新的PGDATA目录 pg_probackup restore -B /data/postgres/probackup --instance local_6000 -D /data/postgres/data6000 --recovery-target-time='2020-09-23 11:05:17'
 -- 恢复到原目录 pg_probackup restore -B /data/postgres/probackup --instance local_6000 --recovery-target-time='2020-09-23 11:05:17'


--　同时对于目录中的备份的数据可以设置保留期限
 pg_probackup set-config -B /pgdata/backup/ --instance pg_test --retention-redundancy 2 --retention-window 7

--retention-redundancy=redundancy

保留备份多少天 FULL 

--retention-window=window

2）远程实例数据备份

## 远程实例的全量备份
$ /usr/local/pg_probackup-2.4.2/pg_probackup backup -B /data/pgdata_probackup/ --instance zijie_5432 --remote-user=postgres --remote-host=120.27.250.75 --remote-port=22 -b full
INFO: Backup start, pg_probackup version: 2.4.2, instance: zijie_5432, backup ID: QH3V3Y, backup mode: FULL, wal mode: ARCHIVE, remote: true, compress-algorithm: none, compress-level: 1
Password:
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 backup or checkdb as superuser.
INFO: Wait for WAL segment /data/pgdata_probackup/wal/zijie_5432/0000000100000000000000E7 to be archived
WARNING: By default pg_probackup assume WAL delivery method to be ARCHIVE. If continuous archiving is not set up, use '--stream' option to make autonomous backup. Otherwise check that continuous archiving works correctly.
INFO: PGDATA size: 59MB
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 7m:41s
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executed
INFO: Wait for LSN 0/E80001D8 in archived WAL segment /data/pgdata_probackup/wal/zijie_5432/0000000100000000000000E8
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 0
INFO: Validating backup QH3V3Y
INFO: Backup QH3V3Y data files are valid
INFO: Backup QH3V3Y resident size: 43MB
INFO: Backup QH3V3Y completed

## 查看备份信息
$ /usr/local/pg_probackup-2.4.2/pg_probackup show -B /data/pgdata_probackup/ --instance zijie_5432
=======================================================================================================================================
 Instance    Version  ID      Recovery Time           Mode  WAL Mode  TLI     Time  Data   WAL  Zratio  Start LSN   Stop LSN    Status
=======================================================================================================================================
 zijie_5432  12       QH3V3Y  2020-09-23 17:57:28+08  FULL  ARCHIVE   1/0  13m:59s  43MB  16MB    1.00  0/E7000028  0/E80001D8  OK
 zijie_5432  12       QH3U2S  ----                    FULL  ARCHIVE   1/0  17m:12s  54MB     0    1.00  0/E4000028  0/0         ERROR
 zijie_5432  ----     QH3U2J  ----                    FULL  ARCHIVE   0/0       4s     0     0    1.00  0/0         0/0         ERROR
 zijie_5432  12       QH3U20  ----                    FULL  ARCHIVE   0/0       5s     0     0    1.00  0/0         0/0         ERROR

3）备份文件可用性校验

## 备份文件校验
$ /usr/local/pg_probackup-2.4.2/pg_probackup validate -B /data/pgdata_probackup/ --instance local_5432
INFO: Validate backups of the instance 'local_5432'
INFO: Validating backup QH2F68
INFO: Backup QH2F68 data files are valid
INFO: Backup QH2F68 WAL segments are valid
INFO: Validating backup QH2EIL
INFO: Backup QH2EIL data files are valid
INFO: Backup QH2EIL WAL segments are valid
ERROR: Backup QH2EI6 has server version , but current pg_probackup binary compiled with server version 12

$ /usr/local/pg_probackup-2.4.2/pg_probackup validate -B /data/pgdata_probackup/ --instance zijie_5432
INFO: Validate backups of the instance 'zijie_5432'
INFO: Validating backup QH3V3Y
INFO: Backup QH3V3Y data files are valid
INFO: Backup QH3V3Y WAL segments are valid
WARNING: Backup QH3U2S has status ERROR. Skip validation.
ERROR: Backup QH3U2J has server version , but current pg_probackup binary compiled with server version 12

4.3 数据恢复

1、全量备份恢复

# 先关闭源数据库并备份原先的数据目录
$ pg_ctl -D /data/pgsql12/data/ stop
waiting for server to shut down.... done
server stopped
$ mv /data/pgsql12/data/ /data/pgsql12/data_bak/

## 通过全备恢复
$ /usr/local/pg_probackup-2.4.2/pg_probackup restore -B /data/pgdata_probackup/ --instance local_5432 --recovery-target-time='2020-09-22 22:49:34'
INFO: Validating backup QH2EIL
INFO: Backup QH2EIL data files are valid
INFO: Backup validation completed successfully on time 2020-09-22 22:49:34+08, xid 658 and LSN 0/B0000B8
INFO: Backup QH2EIL is valid.
INFO: Restoring the database from backup at 2020-09-22 22:49:33+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 QH2EIL completed.

## 启动数据库并检查t1表数据恢复情况
$ pg_ctl -D /data/pgsql12/data/ start
waiting for server to start....2020-09-22 23:21:23.529 CST [32368] LOG:  starting PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
2020-09-22 23:21:23.530 CST [32368] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2020-09-22 23:21:23.530 CST [32368] LOG:  listening on IPv6 address "::", port 5432
2020-09-22 23:21:23.537 CST [32368] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-09-22 23:21:23.552 CST [32368] LOG:  redirecting log output to logging collector process
2020-09-22 23:21:23.552 CST [32368] HINT:  Future log output will appear in directory "/data/pgsql12/logs".
 done
server started

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

2、增量数据恢复

## 再次关闭数据库并删除已有的数据目录
$ pg_ctl -D /data/pgsql12/data/ stop
waiting for server to shut down.... done
server stopped
$ rm -rf /data/pgsql12/data

## 将数据恢复到第一次增备时间点，并检查t1表的数据恢复情况
$ /usr/local/pg_probackup-2.4.2/pg_probackup restore -B /data/pgdata_probackup/ --instance local_5432 --recovery-target-time='2020-09-22 23:03:45'
INFO: Validating parents for backup QH2F68
INFO: Validating backup QH2EIL
INFO: Backup QH2EIL data files are valid
INFO: Validating backup QH2F68
INFO: Backup QH2F68 data files are valid
INFO: Backup validation completed successfully on time 2020-09-22 23:03:45+08, xid 659 and LSN 0/E0000F0
INFO: Backup QH2F68 is valid.
INFO: Restoring the database from backup at 2020-09-22 23:03:44+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 QH2F68 completed.
[postgres@sansi_test pgsql12]$ pg_ctl -D /data/pgsql12/data/ start
waiting for server to start....2020-09-22 23:22:42.746 CST [32391] LOG:  starting PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
2020-09-22 23:22:42.746 CST [32391] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2020-09-22 23:22:42.746 CST [32391] LOG:  listening on IPv6 address "::", port 5432
2020-09-22 23:22:42.750 CST [32391] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-09-22 23:22:42.761 CST [32391] LOG:  redirecting log output to logging collector process
2020-09-22 23:22:42.761 CST [32391] HINT:  Future log output will appear in directory "/data/pgsql12/logs".
 done
server started

db1=# select count(*) from t1;
 count
-------
    16          //可以看到数据恢复成功
(1 row)

```

五、模拟原机误删除表

当前备份情况

# su - postgres
$ pg_probackup validate -B /var/lib/pgsql/pg_probackup_dir
$ pg_probackup show -B /var/lib/pgsql/pg_probackup_dir

BACKUP INSTANCE 'pg11'
====================================================================================================================================
 Instance  Version  ID      Recovery Time           Mode   WAL Mode  TLI  Time   Data   WAL  Zratio  Start LSN   Stop LSN    Status 
====================================================================================================================================
 pg11      11       Q6O08Q  2020-03-04 18:38:51+08  PAGE   ARCHIVE   1/1    2s  124kB  16MB    1.00  0/1E000028  0/1F0000B8  OK     
 pg11      11       Q6NYFO  2020-03-04 17:59:52+08  DELTA  ARCHIVE   1/1    5s   68MB  16MB    1.00  0/1C000028  0/1C000160  OK     
 pg11      11       Q6NYEY  2020-03-04 17:59:26+08  PAGE   ARCHIVE   1/1    5s   68MB  16MB    1.00  0/15000028  0/160000B8  OK     
 pg11      11       Q6NYDK  2020-03-04 17:58:38+08  PAGE   ARCHIVE   1/1    7s   67MB  16MB    1.00  0/E000028   0/F0000F0   OK     
 pg11      11       Q6NYCA  2020-03-04 17:57:48+08  PAGE   ARCHIVE   1/1    3s  212kB  16MB    1.00  0/6000028   0/70000B8   OK     
 pg11      11       Q6NXZ6  2020-03-04 17:50:00+08  FULL   ARCHIVE   1/0    7s   39MB  16MB    1.00  0/4000028   0/4000160   OK

模拟误删除表

$ psql
psql (11.5)
Type "help" for help.

postgres=# \d
              List of relations
 Schema |     Name     |   Type   |  Owner   
--------+--------------+----------+----------
 public | tmp_t0       | table    | postgres
(1 rows)

postgres=# \! date
Wed Mar  4 21:17:56 CST 2020
postgres=# drop table tmp_t0;
DROP TABLE
postgres=# \! date
Wed Mar  4 21:18:10 CST 2020

现在需要恢复到表删除之前的这个时间点 Wed Mar 4 21:17:56 CST 2020

$ pg_probackup validate -B /var/lib/pgsql/pg_probackup_dir --instance pg11 --recovery-target-time='2020-03-04 21:17:56+08' 

INFO: Validating parents for backup Q6O08Q
INFO: Validating backup Q6NXZ6
INFO: Backup Q6NXZ6 data files are valid
INFO: Validating backup Q6NYCA
INFO: Backup Q6NYCA data files are valid
INFO: Validating backup Q6NYDK
INFO: Backup Q6NYDK data files are valid
INFO: Validating backup Q6NYEY
INFO: Backup Q6NYEY data files are valid
INFO: Validating backup Q6NYFO
INFO: Backup Q6NYFO data files are valid
INFO: Validating backup Q6O08Q
INFO: Backup Q6O08Q data files are valid
WARNING: Thread [1]: Could not read WAL record at 0/20000000
ERROR: Thread [1]: WAL segment "/var/lib/pgsql/pg_probackup_dir/wal/pg11/000000010000000000000020" is absent
WARNING: Recovery can be done up to time 2020-03-04 18:38:51+08, xid 716 and LSN 0/1F0000B8
ERROR: Not enough WAL records to time 2020-03-04 21:17:56+08

提示 ERROR ，因为备份集的 WAL 只能恢复到 2020-03-04 21:17:56+08，需要使用到当前的 WAL，要做个 switch wal

$ psql -c "select pg_switch_wal();"
$ pg_probackup validate -B /var/lib/pgsql/pg_probackup_dir --instance pg11 --recovery-target-time='2020-03-04 21:17:56+08' 

INFO: Validating parents for backup Q6O08Q
INFO: Validating backup Q6NXZ6
INFO: Backup Q6NXZ6 data files are valid
INFO: Validating backup Q6NYCA
INFO: Backup Q6NYCA data files are valid
INFO: Validating backup Q6NYDK
INFO: Backup Q6NYDK data files are valid
INFO: Validating backup Q6NYEY
INFO: Backup Q6NYEY data files are valid
INFO: Validating backup Q6NYFO
INFO: Backup Q6NYFO data files are valid
INFO: Validating backup Q6O08Q
INFO: Backup Q6O08Q data files are valid
INFO: Backup validation completed successfully on time 2020-03-04 21:18:03+08, xid 716 and LSN 0/200097D0
INFO: Backup Q6O08Q is valid.
INFO: Validate of backup Q6O08Q completed.

没有问题，可以利用这些备份集进行异机恢复了。

六、异机恢复

另外一台机器需要安装 postgresql 11 软件，及 pg_probackup 2.2.7

# su - postgres
$ 
$ which pg_ctl
/usr/pgsql-11/bin/pg_ctl
$ pg_ctl --version
pg_ctl (PostgreSQL) 11.5
$ 
$ which pg_probackup
/bin/pg_probackup
$ pg_probackup --version
pg_probackup 2.2.7 (PostgreSQL 11.5)

所有的操作都异机在 /temp 下完成

异机创建 PGDATA 目录

$ cd /tmp
$ mkdir pgsql11data;
chmod 700 /tmp/pgsql11data ;

创建 pg_probackup 初始化目录

$ cd /tmp
$ mkdir pgprobackupdata
$ pg_probackup init -D /tmp/pgsql11data -B /tmp/pgprobackupdata
$ mkdir -p ./pgprobackupdata/backups/pg11;
mkdir -p ./pgprobackupdata/wal/pg11;

backups 及 wal 目录下的那个 pg11 文件代表的是 pg_probackup 时指定的 instance_name，需要和原库保持一致。

原机上的备份及WAL

需要将原机上的备份及WAL拷贝到异机的指定目录。
以下操作在原机上操作

# su - postgres
$ pwd
/var/lib/pgsql

$ cd /var/lib/pgsql/pg_probackup_dir/backups/pg11
$ tar -zcvf ./backup.tar.gz ./*
$ scp ./backup.tar.gz postgres@192.168.56.111:/tmp/pgprobackupdata/backups/pg11

$ cd /var/lib/pgsql/pg_probackup_dir/wal/pg11
$ tar -zcvf ./wal.tar.gz ./*
$ scp ./wal.tar.gz postgres@192.168.56.111:/tmp/pgprobackupdata/wal/pg11

异机解压相关的文件

# su - postgres
$ cd /tmp
$ cd /tmp/pgprobackupdata/backups/pg11
$ tar -zxvf ./backup.tar.gz

$ cd /tmp/pgprobackupdata/wal/pg11
$ tar -zxvf ./wal.tar.gz

异机验证恢复

$ pg_probackup show -B /tmp/pgprobackupdata

BACKUP INSTANCE 'pg11'
====================================================================================================================================
 Instance  Version  ID      Recovery Time           Mode   WAL Mode  TLI  Time   Data   WAL  Zratio  Start LSN   Stop LSN    Status 
====================================================================================================================================
 pg11      11       Q6O08Q  2020-03-04 18:38:51+08  PAGE   ARCHIVE   1/1    2s  124kB  16MB    1.00  0/1E000028  0/1F0000B8  OK     
 pg11      11       Q6NYFO  2020-03-04 17:59:52+08  DELTA  ARCHIVE   1/1    5s   68MB  16MB    1.00  0/1C000028  0/1C000160  OK     
 pg11      11       Q6NYEY  2020-03-04 17:59:26+08  PAGE   ARCHIVE   1/1    5s   68MB  16MB    1.00  0/15000028  0/160000B8  OK     
 pg11      11       Q6NYDK  2020-03-04 17:58:38+08  PAGE   ARCHIVE   1/1    7s   67MB  16MB    1.00  0/E000028   0/F0000F0   OK     
 pg11      11       Q6NYCA  2020-03-04 17:57:48+08  PAGE   ARCHIVE   1/1    3s  212kB  16MB    1.00  0/6000028   0/70000B8   OK     
 pg11      11       Q6NXZ6  2020-03-04 17:50:00+08  FULL   ARCHIVE   1/0    7s   39MB  16MB    1.00  0/4000028   0/4000160   OK

$ pg_probackup validate -D /tmp/pgsql11data/ -B /tmp/pgprobackupdata/ --instance pg11 --recovery-target-time='2020-03-04 21:17:56+08' 

INFO: Validating parents for backup Q6O08Q
INFO: Validating backup Q6NXZ6
INFO: Backup Q6NXZ6 data files are valid
INFO: Validating backup Q6NYCA
INFO: Backup Q6NYCA data files are valid
INFO: Validating backup Q6NYDK
INFO: Backup Q6NYDK data files are valid
INFO: Validating backup Q6NYEY
INFO: Backup Q6NYEY data files are valid
INFO: Validating backup Q6NYFO
INFO: Backup Q6NYFO data files are valid
INFO: Validating backup Q6O08Q
INFO: Backup Q6O08Q data files are valid
INFO: Backup validation completed successfully on time 2020-03-04 21:18:03+08, xid 716 and LSN 0/200097D0
INFO: Backup Q6O08Q is valid.
INFO: Validate of backup Q6O08Q completed.

$ pg_probackup restore -D /tmp/pgsql11data/ -B /tmp/pgprobackupdata/ --instance pg11 --recovery-target-time='2020-03-04 21:17:56+08' 

INFO: Validating parents for backup Q6O08Q
INFO: Validating backup Q6NXZ6
INFO: Backup Q6NXZ6 data files are valid
INFO: Validating backup Q6NYCA
INFO: Backup Q6NYCA data files are valid
INFO: Validating backup Q6NYDK
INFO: Backup Q6NYDK data files are valid
INFO: Validating backup Q6NYEY
INFO: Backup Q6NYEY data files are valid
INFO: Validating backup Q6NYFO
INFO: Backup Q6NYFO data files are valid
INFO: Validating backup Q6O08Q
INFO: Backup Q6O08Q data files are valid
INFO: Backup validation completed successfully on time 2020-03-04 21:18:03+08, xid 716 and LSN 0/200097D0
INFO: Backup Q6O08Q is valid.
INFO: Restore of backup Q6O08Q completed.

异机恢复数据库

自动生成了 recovery.conf 文件

$ cd /tmp/pgprobackupdata
$ cat recovery.conf 
# recovery.conf generated by pg_probackup 2.2.7
recovery_target_time = '2020-03-04 21:17:56+08'
recovery_target_action = 'pause'
restore_command = '/usr/bin/pg_probackup-11 archive-get -B /tmp/pgprobackupdata --instance pg11 --wal-file-path=%p --wal-file-name=%f'

修改参数

$ vi postgresql.conf

data_directory = '/tmp/pgsql11data'
hba_file = '/tmp/pgsql11data/pg_hba.conf'
ident_file = '/tmp/pgsql11data/pg_ident.conf'
port=5433
archive_mode = off
archive_command = ''
archive_timeout = 0

启动pgsql

$ /usr/pgsql-11/bin/pg_ctl start -D /tmp/pgsql11data/

waiting for server to start....2020-03-04 22:34:59.914 CST [2961] LOG:  listening on IPv4 address "0.0.0.0", port 5433
2020-03-04 22:34:59.914 CST [2961] LOG:  listening on IPv6 address "::", port 5433
2020-03-04 22:34:59.916 CST [2961] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433"
2020-03-04 22:34:59.921 CST [2961] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5433"
2020-03-04 22:34:59.961 CST [2961] LOG:  redirecting log output to logging collector process
2020-03-04 22:34:59.961 CST [2961] HINT:  Future log output will appear in directory "log".
. done
server started

查看日志，已经恢复到指定时间，并处于 pause 状态

$ tail -n 50 -f /tmp/pgsql11data/log/postgresql-Wed.log 
2020-03-04 22:34:59.965 CST [2963] LOG:  database system was interrupted; last known up at 2020-03-04 18:38:50 CST
2020-03-04 22:34:59.965 CST [2963] LOG:  creating missing WAL directory "pg_wal/archive_status"
2020-03-04 22:35:00.625 CST [2963] LOG:  starting point-in-time recovery to 2020-03-04 21:17:56+08
INFO: pg_probackup archive-get from /tmp/pgprobackupdata/wal/pg11/00000001000000000000001E to /tmp/pgsql11data/pg_wal/RECOVERYXLOG
INFO: pg_probackup archive-get completed successfully
2020-03-04 22:35:00.721 CST [2963] LOG:  restored log file "00000001000000000000001E" from archive
2020-03-04 22:35:00.729 CST [2963] LOG:  redo starts at 0/1E000028
INFO: pg_probackup archive-get from /tmp/pgprobackupdata/wal/pg11/00000001000000000000001F to /tmp/pgsql11data/pg_wal/RECOVERYXLOG
INFO: pg_probackup archive-get completed successfully
2020-03-04 22:35:00.850 CST [2963] LOG:  restored log file "00000001000000000000001F" from archive
2020-03-04 22:35:00.855 CST [2963] LOG:  consistent recovery state reached at 0/1F0000B8
2020-03-04 22:35:00.856 CST [2961] LOG:  database system is ready to accept read only connections
INFO: pg_probackup archive-get from /tmp/pgprobackupdata/wal/pg11/000000010000000000000020 to /tmp/pgsql11data/pg_wal/RECOVERYXLOG
INFO: pg_probackup archive-get completed successfully
2020-03-04 22:35:00.958 CST [2963] LOG:  restored log file "000000010000000000000020" from archive
2020-03-04 22:35:00.962 CST [2963] LOG:  recovery stopping before commit of transaction 716, time 2020-03-04 21:18:03.525552+08
2020-03-04 22:35:00.962 CST [2963] LOG:  recovery has paused
2020-03-04 22:35:00.962 CST [2963] HINT:  Execute pg_wal_replay_resume() to continue.

异机登录数据库

$ psql -p 5433
psql (11.5)
Type "help" for help.

postgres=# \d
              List of relations
 Schema |     Name     |   Type   |  Owner   
--------+--------------+----------+----------
 public | tmp_t0       | table    | postgres
(1 rows)

七、增量备份三种模式

第一种：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