合 PG备份恢复工具之pg_probackup
Tags： PGPostgreSQL备份恢复pg_probackup
👉 本文共约1151个字，系统预计阅读时间或需5分钟。
简介
GitHub：https://github.com/postgrespro/pg_probackup
文档：https://postgrespro.github.io/pg_probackup/
https://postgrespro.com/docs/postgrespro/16/app-pgprobackup
pg_probackup 是一个用于 PostgreSQL 数据库集群备份和恢复的强大工具，由俄罗斯公司 Postgres Professional 开发。它支持增量备份，允许在数据库持续运行的情况下进行高效的数据保护，同时节省磁盘空间和缩短备份窗口。
pg_probackup 是一款免费的postgres数据库集群备份工具，与pg_basebackup和pg_rman相比，具有以下优势：
1、提供增量备份
2、可以通过全量备份+增量备份进行增量恢复
3、无需通过实际的数据恢复操作即可验证备份文件是否有效
4、提供备份压缩以节省磁盘空间
5、可以对远程实例进行备份
6、可以从STANDBY实例进行备份
7、可以查看已备份数据备份以及归档的列表以及相关详细信息
8、支持部分还原（还原部分数据库）
9、支持对备份集的管理
10、并行恢复
安装
二进制安装
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 | #DEB Ubuntu|Debian Packages sudo sh -c 'echo "deb [arch=amd64] https://repo.postgrespro.ru/pg_probackup/deb/ $(lsb_release -cs) main-$(lsb_release -cs)" > /etc/apt/sources.list.d/pg_probackup.list' sudo wget -O - https://repo.postgrespro.ru/pg_probackup/keys/GPG-KEY-PG_PROBACKUP | sudo apt-key add - && sudo apt-get update sudo apt-get install pg-probackup-{16,15,14,13,12,11} sudo apt-get install pg-probackup-{16,15,14,13,12,11}-dbg #DEB-SRC Packages sudo sh -c 'echo "deb-src [arch=amd64] https://repo.postgrespro.ru/pg_probackup/deb/ $(lsb_release -cs) main-$(lsb_release -cs)" >>\ /etc/apt/sources.list.d/pg_probackup.list' && sudo apt-get update sudo apt-get source pg-probackup-{16,15,14,13,12,11} #DEB Astra Linix Orel sudo sh -c 'echo "deb [arch=amd64] https://repo.postgrespro.ru/pg_probackup/deb/ stretch main-stretch" > /etc/apt/sources.list.d/pg_probackup.list' sudo wget -O - https://repo.postgrespro.ru/pg_probackup/keys/GPG-KEY-PG_PROBACKUP | sudo apt-key add - && sudo apt-get update sudo apt-get install pg-probackup-{16,15,14,13,12,11}{-dbg,} #RPM Centos Packages rpm -ivh https://repo.postgrespro.ru/pg_probackup/keys/pg_probackup-repo-centos.noarch.rpm yum install pg_probackup-{16,15,14,13,12,11} yum install pg_probackup-{16,15,14,13,12,11}-debuginfo #RPM RHEL Packages rpm -ivh https://repo.postgrespro.ru/pg_probackup/keys/pg_probackup-repo-rhel.noarch.rpm yum install pg_probackup-{16,15,14,13,12,11} yum install pg_probackup-{16,15,14,13,12,11}-debuginfo #RPM Oracle Linux Packages rpm -ivh https://repo.postgrespro.ru/pg_probackup/keys/pg_probackup-repo-oraclelinux.noarch.rpm yum install pg_probackup-{16,15,14,13,12,11} yum install pg_probackup-{16,15,14,13,12,11}-debuginfo #SRPM Centos|RHEL|OracleLinux Packages yumdownloader --source pg_probackup-{16,15,14,13,12,11} #RPM SUSE|SLES Packages zypper install --allow-unsigned-rpm -y https://repo.postgrespro.ru/pg_probackup/keys/pg_probackup-repo-suse.noarch.rpm zypper --gpg-auto-import-keys install -y pg_probackup-{16,15,14,13,12,11} zypper install pg_probackup-{16,15,14,13,12,11}-debuginfo #SRPM SUSE|SLES Packages zypper si pg_probackup-{16,15,14,13,12,11} #RPM ALT Linux 8 sudo sh -c 'echo "rpm https://repo.postgrespro.ru/pg_probackup/rpm/latest/altlinux-p8 x86_64 vanilla" > /etc/apt/sources.list.d/pg_probackup.list' sudo apt-get update sudo apt-get install pg_probackup-{16,15,14,13,12,11} sudo apt-get install pg_probackup-{16,15,14,13,12,11}-debuginfo #RPM ALT Linux 9 sudo sh -c 'echo "rpm https://repo.postgrespro.ru/pg_probackup/rpm/latest/altlinux-p9 x86_64 vanilla" > /etc/apt/sources.list.d/pg_probackup.list' sudo apt-get update sudo apt-get install pg_probackup-{16,15,14,13,12,11} sudo apt-get install pg_probackup-{16,15,14,13,12,11}-debuginfo #RPM ALT Linux 10 sudo sh -c 'echo "rpm https://repo.postgrespro.ru/pg_probackup/rpm/latest/altlinux-p10 x86_64 vanilla" > /etc/apt/sources.list.d/pg_probackup.list' sudo apt-get update sudo apt-get install pg_probackup-{16,15,14,13,12,11} sudo apt-get install pg_probackup-{16,15,14,13,12,11}-debuginfo |
源码编译
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | wget https://github.com/postgrespro/pg_probackup/archive/refs/tags/2.5.15.tar.gz -- https://mirror.ghproxy.com/https://github.com/postgrespro/pg_probackup/archive/refs/tags/2.5.15.tar.gz tar -xzvf pg_probackup.tar.gz cd pg_probackup 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 -- 注意： pg_config 是pg_config程序所在路径 top_srcdir 是postgresql源码所在路径 EG: make USE_PGXS=1 pg_config=/opt/pg151/bin top_srcdir=/home/pg15/postgresql-15.1 make USE_PGXS=1 pg_config=/opt/pg151/bin top_srcdir=/home/pg15/postgresql-15.1 install |
实验
lhrpg01(192.92.0.23)为数据库服务器，lhrpg02(192.92.0.42)为备份服务器。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 | docker pull registry.cn-hangzhou.aliyuncs.com/lhrbest/lhrpgall:4.0 docker rm -f lhrpg01 docker run -itd --name lhrpg01 -h lhrpg01 \ -p 25432-25445:5432-5445 -p 122:22 -p 189:3389 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --restart=always \ --privileged=true lhrbest/lhrpgall:4.0 \ /usr/sbin/init docker exec -it lhrpg01 bash ps -ef|grep postgres | grep bin docker rm -f lhrpg02 docker run -itd --name lhrpg02 -h lhrpg02 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --restart=always \ --privileged=true lhrbest/lhrpgall:4.0 \ /usr/sbin/init docker exec -it lhrpg02 bash systemctl stop pg94 pg96 pg10 pg11 pg12 pg13 pg14 pg15 systemctl stop postgresql-15.service rpm -ivh https://repo.postgrespro.ru/pg_probackup/keys/pg_probackup-repo-centos.noarch.rpm -y yum install pg_probackup-16 -y ln -s /usr/bin/pg_probackup-16 /usr/bin/pg_probackup -- 互信 ./sshUserSetup.sh -user pg16 -hosts "192.92.0.23 192.92.0.42" -advanced -noPromptPassphrase -confirm ./sshUserSetup.sh -user pg16 -hosts "lhrpg01 lhrpg02" -advanced -noPromptPassphrase -confirm chmod 600 /home/pg16/.ssh/config mkdir /bk chmod 777 /bk # 分别对pg01和pg02 设置数据库参数，注意：--instance 的参数 alter system set archive_command = '/usr/bin/pg_probackup archive-push -B /bk --instance pg01 --wal-file-name=%f --remote-proto=ssh --remote-host=192.92.0.23 --remote-port=22 --remote-user=pg16'; # alter system set archive_command = '/usr/bin/pg_probackup archive-push -B /bk --instance pg02 --wal-file-name=%f --remote-proto=ssh --remote-host=192.92.0.23 --remote-port=22 --remote-user=pg16'; alter system set archive_mode = on; alter system set listen_addresses = '*'; -- alter system set log_line_prefix = ''; alter system set max_wal_senders = 10; alter system set wal_level = replica; # 重启数据库 pg_ctl restart |
本机备份还原示例
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | pg_probackup-16 init -B /bk/ pg_probackup-16 add-instance -B /bk/ -D /pg16/pgdata --instance=pg01 pg_probackup-16 show-config -B /bk/ --instance=pg01 pg_probackup-16 show -B /bk/ -- 全量 pg_probackup-16 backup -B /bk/ --instance pg01 -b FULL -U postgres -h /pg16/pgdata/ -p 5441 -d postgres --compress --stream -- 增量 pg_probackup-16 backup -B /bk/ --instance pg01 -b DELTA -U postgres -h /pg16/pgdata/ -p 5441 -d postgres --compress --stream create table t(id int, col2 text); insert into t select n, 'test123' || n || md5(random()::varchar) from generate_series(1, 300000) as n; select pg_relation_filepath('t'); show data_directory; -- 全量还原 pg_probackup-16 restore -B /bk/ --instance pg01 -D /pg16/pgdata/ -i SG20AY --threads=4 -- 增量还原(注意i参数备份集的选择) pg_probackup-16 restore -B /bk/ --instance pg01 -D /pg16/pgdata/ -i SG20FJ --threads=4 -- 时间点还原 pg_probackup-16 restore -B /bk/ --instance pg01 -D /pg16/pgdata/ -i SG0888 --threads=4 \ —recovery-target-time='2024-07-17 21:13:58' —recovery-target-inclusive=true |
日志：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | [root@lhrpg01 bk]# pg_probackup-16 backup -B /bk/ --instance pg16 -b FULL -U postgres -h /pg16/pgdata/ -p 5441 -d postgres --compress --stream INFO: Backup start, pg_probackup version: 2.5.15, instance: pg16, backup ID: SG1ET0, backup mode: FULL, wal mode: STREAM, remote: false, compress-algorithm: zlib, compress-level: 1 INFO: This PostgreSQL instance was initialized with data block checksums. Data block corruption will be detected WARNING: Current PostgreSQL role is superuser. It is not recommended to run pg_probackup under superuser. INFO: Database backup start INFO: wait for pg_backup_start() INFO: Wait for WAL segment /bk/backups/pg16/SG1ET0/database/pg_wal/00000005000000000000000D to be streamed WARNING: Skip hidden file: '/pg16/pgdata/.s.PGSQL.5441.lock' WARNING: Skip hidden file: '/pg16/pgdata/.s.PGSQL.5441' INFO: PGDATA size: 43MB INFO: Current Start LSN: 0/D000028, TLI: 5 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: stop_lsn: 0/D0001A0 INFO: Getting the Recovery Time from WAL INFO: Syncing backup files to disk INFO: Backup files are synced, time elapsed: 0 INFO: Validating backup SG1ET0 INFO: Backup SG1ET0 data files are valid INFO: Backup SG1ET0 resident size: 31MB INFO: Backup SG1ET0 completed |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
