脚本宝典收集整理的这篇文章主要介绍了PG-pg_probackup备份恢复工具,脚本宝典觉得挺不错的,现在分享给大家,也给大家做个参考。
pg_probackup是由Postgres Professional发布的一个用于管理PostgreSQL 9.5 及以上版本数据库备份和恢复的工具。pg_probackup 实用程序基于 pg_arman,它最初由 NTT 编写,然后由 Michael Paquier 开发和维护。
wget https://codeload.gIThub.COM/postgrespro/pg_probackup/tar.gz/refs/tags/2.5.1
make USE_PGXS=1 PG_config=<path_to_pg_config> top_srcdir=<path_to_PostgreSQL_source_tree>
make USE_PGXS=1 PG_CONFIG=<path_to_pg_config> top_srcdir=<path_to_PostgreSQL_source_tree> install
# 不使用 PGXS
cd <path_to_PostgreSQL_source_tree> && git clone https://github.com/postgrespro/pg_probackup contrib/pg_probackup && cd contrib/pg_probackup && make
# pg_probackup for PostgresPro Standard and Enterprise
#DEB Ubuntu|Debian Packages
sudo sh -c 'echo "deb [Arch=amd64] https://repo.postgrespro.ru/pg_probackup-forks/deb/ $(lsb_release -cs) main-$(lsb_release -cs)" > /etc/apt/sources.list.d/pg_probackup-forks.list'
sudo wget -O - https://repo.postgrespro.ru/pg_probackup-forks/keys/GPG-KEY-PG_PROBACKUP | sudo apt-key add - && sudo apt-get update
sudo apt-get install pg-probackup-{std,ent}-{12,11,10,9.6}
sudo apt-get install pg-probackup-{std,ent}-{12,11,10,9.6}-dbg
#DEB Astra Linix Orel
sudo sh -c 'echo "deb [arch=amd64] https://repo.postgrespro.ru/pg_probackup-forks/deb/ stretch main-stretch" > /etc/apt/sources.list.d/pg_probackup.list'
sudo wget -O - https://repo.postgrespro.ru/pg_probackup-forks/keys/GPG-KEY-PG_PROBACKUP | sudo apt-key add - && sudo apt-get update
sudo apt-get install pg-probackup-{std,ent}-{12,11,10,9.6}{-dbg,}
#RPM Centos Packages
rpm -ivh https://repo.postgrespro.ru/pg_probackup-forks/keys/pg_probackup-repo-forks-centos.noarch.rpm
yum install pg_probackup-{std,ent}-{12,11,10,9.6}
yum install pg_probackup-{std,ent}-{12,11,10,9.6}-debuginfo
#RPM RHEL Packages
rpm -ivh https://repo.postgrespro.ru/pg_probackup-forks/keys/pg_probackup-repo-forks-rhel.noarch.rpm
yum install pg_probackup-{std,ent}-{12,11,10,9.6}
yum install pg_probackup-{std,ent}-{12,11,10,9.6}-debuginfo
#RPM oracle Linux Packages
rpm -ivh https://repo.postgrespro.ru/pg_probackup-forks/keys/pg_probackup-repo-forks-oraclelinux.noarch.rpm
yum install pg_probackup-{std,ent}-{12,11,10,9.6}
yum install pg_probackup-{std,ent}-{12,11,10,9.6}-debuginfo
#RPM ALT Linux 7
sudo sh -c 'echo "rpm https://repo.postgrespro.ru/pg_probackup-forks/rpm/latest/altlinux-p7 x86_64 forks" > /etc/apt/sources.list.d/pg_probackup_forks.list'
sudo apt-get update
sudo apt-get install pg_probackup-{std,ent}-{12,11,10,9.6}
sudo apt-get install pg_probackup-{std,ent}-{12,11,10,9.6}-debuginfo
#RPM ALT Linux 8
sudo sh -c 'echo "rpm https://repo.postgrespro.ru/pg_probackup-forks/rpm/latest/altlinux-p8 x86_64 forks" > /etc/apt/sources.list.d/pg_probackup_forks.list'
sudo apt-get update
sudo apt-get install pg_probackup-{std,ent}-{12,11,10,9.6}
sudo apt-get install pg_probackup-{std,ent}-{12,11,10,9.6}-debuginfo
#RPM ALT Linux 9
sudo sh -c 'echo "rpm https://repo.postgrespro.ru/pg_probackup-forks/rpm/latest/altlinux-p9 x86_64 forks" > /etc/apt/sources.list.d/pg_probackup_forks.list' && sudo apt-get update
sudo apt-get install pg_probackup-{std,ent}-{12,11,10,9.6}
sudo apt-get install pg_probackup-{std,ent}-{12,11,10,9.6}-debuginfo
pg_probackup version
pg_probackup help [command]
pg_probackup init -B backup_dir
pg_probackup add-instance -B backup_dir -D data_dir --instance instance_name
pg_probackup del-instance -B backup_dir --instance instance_name
pg_probackup set-config -B backup_dir --instance instance_name [option...]
pg_probackup set-backup -B backup_dir --instance instance_name -i backup_id [option...]
pg_probackup show-config -B backup_dir --instance instance_name [--format=format]
pg_probackup show -B backup_dir [option...]
pg_probackup backup -B backup_dir --instance instance_name -b backup_mode [option...]
pg_probackup reStore -B backup_dir --instance instance_name [option...]
pg_probackup checkdb -B backup_dir --instance instance_name -D data_dir [option...]
pg_probackup validate -B backup_dir [option...]
pg_probackup merge -B backup_dir --instance instance_name -i backup_id [option...]
pg_probackup delete -B backup_dir --instance instance_name { -i backup_id | --delete-wal | --delete-expired | --merge-expired } [option...]
pg_probackup archive-push -B backup_dir --instance instance_name --wal-file-name wal_file_name [option...]
pg_probackup archive-get -B backup_dir --instance instance_name --wal-file-path wal_file_path --wal-file-name wal_file_name [option...]
pg_probackup catchup -b catchup_mode --source-pgdata=path_to_pgdata_on_remote_server --destination-pgdata=path_to_local_dir [option...]
用于存放备份文件和归档日志文件
pg_probackup init -B /data/postgres/probackup
完成上面命令后,它会创建下面的子目录
wal/
— directory for WAL files.backups/
— directory for backup files.pg_probackup add-instance -B backup_dir -D data_dir --instance instance_name [remote_options]
#-- 本地添加备份instance 示例
pg_probackup add-instance -B /data/postgres/probackup -D $PGDATA --instance local5432
建议创建一个具有所选备份策略所需的最低权限的单独角色运行pg_probackup使用程序
For Postgres Pro 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;
使用流复制配置
ALTER ROLE backup WITH REPLICATION;
确保有连续 WAL 归档配置
确保wal_level参数高于minimal
如果要在Primary上配置存档,则存档模式必须设置为“on”或“always”。要在standby下执行存档,请将此参数设置为“always"
archive_command 参数配置
archive_command = '"install_dir/pg_probackup" archive-push -B "backup_dir" --instance instance_name --wal-file-name=%f [remote_options]'
# -- 示例
/opt/pg12/bin/pg_probackup archive-push -B /data/postgres/probackup --instance local_6000 --wal-file-path=%p --wal-file-name=%f
CREATE EXTENSION amcheck;
为集群的每个数据库中的备份角色授予以下权限:
GRANT SELECT ON TABLE pg_catalog.pg_am TO backup;
GRANT SELECT ON TABLE pg_catalog.pg_class TO backup;
GRANT SELECT ON TABLE pg_catalog.pg_database TO backup;
GRANT SELECT ON TABLE pg_catalog.pg_namespace TO backup;
GRANT SELECT ON TABLE pg_catalog.pg_extension TO backup;
GRANT EXECUTE ON FUNCTION BT_index_check(regclass) TO backup;
GRANT EXECUTE ON FUNCTION bt_index_check(regclass, bool) TO backup;
将 pg_catalog.pg_database
的只读权限给backup用户角色
GRANT SELECT ON TABLE pg_catalog.pg_database TO backup;
如果要使用 PTRACK 备份,将执行 PTRACK 备份的角色必须有权访问集群的所有数据库。
创建插件
CREATE EXTENSION ptrack;
启用跟踪页面更新参数(tracking page updates)
# N 是 Postgres Pro 集群的大小,以 MB 为单位
ptrack.map_size = N / 1024
pg_probackup backup -B backup_dir --instance instance_name -b backup_mode
backup_mode 取值:
pg_probackup show -B /data/postgres/probackup
pg_probackup restore -B backup_dir --instance instance_name -i backup_id
pg_probackup restore -B backup_dir --instance instance_name --db-include=database_name
pg_probackup restore -B backup_dir --instance instance_name --db-include=db1 --db-include=db2
pg_probackup restore -B backup_dir --instance instance_name --db-exclude=database_name
pg_probackup restore -B backup_dir --instance instance_name --db-exclude=db1 --db-exclude=db2
pg_probackup restore -B backup_dir --instance instance_name --recovery-target-time="2021-05-18 14:18:11+03"
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'
# postgres 用户通过端口 2302 的 SSH 连接创建位于远程系统上的 Postgres Pro 集群的归档完整备份,主机地址为 192.168.0.2
pg_probackup backup -B backup_dir --instance instance_name -b FULL --remote-user=postgres --remote-host=192.168.0.2 --remote-port=2302
# postgres 用户通过端口 2302 的 SSH 连接在主机地址为 192.168.0.2 的远程系统上恢复最新的可用备份
pg_probackup restore -B backup_dir --instance instance_name --remote-user=postgres --remote-host=192.168.0.2 --remote-port=2302
以上是脚本宝典为你收集整理的PG-pg_probackup备份恢复工具全部内容,希望文章能够帮你解决PG-pg_probackup备份恢复工具所遇到的问题。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。