参考:
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
安装很简单repo
rpm -ivh http://repo.postgrespro.ru/pg_probackup/keys/pg_probackup-repo-centos.noarch.rpm
yum install pg_probackup-{11,10,9.6,9.5}
在安装完pg_probackup 后,第一个工作就是要初始化备份的目录
(rpm包安装完毕的需要在执行程序后面带需要备份数据库的版本号)
pg_probackup-11 init -B /pgdata/backup/
并且初始化的数据目录必须是空的,否则会报错,在初始化后,相关的目录会自动生成 wal 和 backup 目录
在初始化目录后需要,需要初始化需要备份的 instance ,也就是当前的服务器的postgresql 的 数据目录,并给这个当前的pg 备份的数据库一个 备份的目录名。
pg_probackup-11 add-instance -B /pgdata/backup/ -D /pgdata/data/ --instance pg_test
其中有一点需要注意
备份目录必须属于数据库服务器的文件系统。启动pg_probackup的用户必须完全访问备份目录的内容。如果在BACKUP_PATH环境变量中指定备份目录的路径,则可以在运行pg_probackup命令时忽略相应的选项。
配置文件中需要对 archive_command 进行改变
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-11 show-config -B /pgdata/backup/ --instance pg_test
增量备份三种模式
第一种: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
源码编译安装
下载文件
$ 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
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
$ /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
$ /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
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
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