PostgreSQL-物理备份工具PGbackrest使用教程

前言

PGSQL的备份工具还是挺多了，大致分为官方的和第三方的，以官方为例有逻辑备份工具，pg_dump和pg_dumpall，物理备份工具，pg_basebackup，第三方的有pg_rman、brman、以及我们这里要说的pgbackrest。而官方的物理备份pg_basebackup还不支持增量备份，以及在使用压缩时，不支持标准化输出，而pgrman又不支持压缩备份，很多时候生产环境下满足不了大多数需求，所以出现了pgbackrest，支持增量、压缩、从库备份远程备份、加密备份等。满足大多数生产环境需求，下面用一张表格，具体看下各种备份工具支持的备份方式。

pgbackrest官网地址：链接: https://pgbackrest.org/

环境介绍

角色	IP	服务
Leader	172.22.138.220	PostgreSQL v14;Pgbackrest v2.52.1 备份源
standby1	172.22.138.219	PostgreSQL v14;Pgbackrest v2.52.1 备份源
standby2	172.22.138.218	PostgreSQL v14;Pgbackrest v2.52.1 备份端

环境准备

1.安装相关依赖包。(所有节点执行)

root # yum install libxml2-devel libyaml-devel bzip2-devel postgresql-libs -y

2.下载安装包，并解压(所有节点执行)

root # cd /opt
root # wget https://github.com/pgbackrest/pgbackrest/releases/tag/release/2.53.1/pgbackrest-release-2.52.1.tar.gz
root # tar xf pgbackrest-release-2.52.1.tar.gz

3.三节点相互配置互信(所有节点执行)

#leader节点
postgres # ssh-keygen -t rsa
postgres # ssh-copy-id postgres@172.22.138.218
postgres # ssh-copy-id postgres@172.22.138.219
 
#standby1节点
postgres # ssh-keygen -t rsa
postgres # ssh-copy-id postgres@172.22.138.218
postgres # ssh-copy-id postgres@172.22.138.220

#standby2节点
postgres # ssh-keygen -t rsa
postgres # ssh-copy-id postgres@172.22.138.219
postgres # ssh-copy-id postgres@172.22.138.220

安装配置pgpgbackrest

1.使用postgres用户编译安装pgbackrest(所有节点执行)

root # cd /opt/pgbackrest-release-2.52.1
root # chown postgres. -R /opt/pgbackrest-release-2.52.1
root # su - postgres
postgres # cd /opt/pgbackrest-release-2.52.1/src
postgres # ./configure
postgres # make && make install

2.创建相关的目录并进行授权(所有节点执行)

postgres # sudo mkdir -p -m 770 /var/log/pgbackrest
postgres # sudo chown postgres.postgres /var/log/pgbackrest/
postgres # sudo mkdir /pgbackrest/backup -p
postgres # sudo mkdir -p /etc/pgbackrest
postgres # sudo touch /etc/pgbackrest/pgbackrest.conf
postgres # sudo chmod 640 /etc/pgbackrest/pgbackrest.conf
postgres # sudo chown postgres.postgres -R /etc/pgbackrest

3.编辑pgbackrest配置文件(备份端配置)

postgres # vim /etc/pgbackrest/pgbackrest.conf
[test]
pg1-path=/data/pgsql/data
pg1-port=5432
pg1-socket-path=/tmp
pg1-user=postgres
pg1-host-config-path=/etc/pgbackrest
pg1-host=172.22.138.220
pg1-host-port=22
pg1-host-user=postgres

pg2-path=/data/pgsql/data
pg2-port=5432
pg2-socket-path=/tmp
pg2-user=postgres
pg2-host-config-path=/etc/pgbackrest
pg2-host=172.22.138.219
pg2-host-port=22
pg2-host-user=postgres

[global]
backup-standby=y
process-max=3
start-fast=y
repo1-path=/pgbackrest/backup
repo1-retention-full=2
backup-user=postgres
log-level-console=info
log-level-file=debug
buffer-size=16MiB
compress-type=gz

[global:archive-push]
compress-level=3

4.配置备份源节点，Leader和standby1(备份源配置)

postgres # vim /etc/pgbackrest/pgbackrest.conf
#Leader
[test]
pg1-path=/data/pgsql/data
[global]
log-level-file=detail
repo1-host=172.22.138.218
repo1-host-user=postgres
log-path=/var/log/pgbackrest

#Standby1
[test]
pg2-path=/data/pgsql/data
[global]
log-level-file=detail
repo1-host=172.22.138.218
repo1-host-user=postgres
log-path=/var/log/pgbackrest

编辑PGSQL的配置文件

1.在pgsql的postgres.conf文件中，增加归档命令。(备份源配置)

postgres # vim /data/pgsql/data/postgresql.conf
...
archive_command = 'pgbackrest --stanza=test archive-push %p'
...

2.重启postgresql或者reload使配置文件生效。

#重启
root # systemctl restart postgresql
#重载配置文件
postgres # pg_ctl reload

3.检查归档命令是否生效。

postgres=# select name,setting from pg_settings where name='archive_command';
+-----------------+------------------------------------------+
|      name       |                 setting                  |
+-----------------+------------------------------------------+
| archive_command | pgbackrest --stanza=test archive-push %p |
+-----------------+------------------------------------------+

全备和增备

1.检测两个备份源节点。（备份端节点执行）

postgres # pgbackrest --stanza=test check

2.初始化备份源节点（所有节点执行）

postgres # pgbackrest --stanza=test --log-level-console=info stanza-create

3.执行全备命令

postgres # pgbackrest --stanza=test --log-level-console=info backup --type=full

4.执行增备命令

postgres # pgbackrest --stanza=test --log-level-console=info backup --type=incr