PostgreSQL备份工具 pgBackRest使用详解

素颜马尾好姑娘i 2022-10-28 10:07 460阅读 0赞

前言  
pgBackRest是一款开源的备份还原工具，目标旨在为备份和还原提供可靠易用的备份。

特性  
并行备份和还原

备份操作期间压缩通常是其瓶颈所在。pgBackRest通过并行处理解决了备份期间压缩出现的瓶颈问题。

本地远程操作

自定义协议允许 pgBackRest以最小化配置通过SSH在本地或者远程执行备份、还原和归档。并且该程序也通过协议层提供了PostgreSQL查询接口，以便于必须要再远程访问PostgreSQL，从而保证了其安全性能。

全量，增量和差异备份

支持全量，增量和差异备份。pgBackRest不受异步时间影响，因此差异和增量备份完全安全。

备份保留策略和和归档过期

支持保留策略设置可以在任意时间创建全备和差异备份的覆盖。

备份完整性

可以计算备份中每个文件的校验和，并在还原期间重新检查。备份完成文件复制后，将等待直到每个WAL段所需的备份保持一致然后存储到备份仓库中。

块校验和断点备份流压缩和校验和增量还原并行异步WAL Push和Get表空间重新映射和Link支持S3存储支持和Azure兼容对象存储支持加密

使用  
安装解压

1  
2  
\[postgres@pgserver12 tools\]$ tar -zxf pgbackrest-release-2.31.tar.gz  
\[postgres@pgserver12 tools\]$ ls  
创建必要目录

1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
ostgres@sungsasong ~\]$ sudo mkdir -p -m 770 /var/log/pgbackrest  
\[postgres@sungsasong ~\]$ sudo chown postgres.postgres /var/log/pgbackrest/  
\[postgres@sungsasong ~\]$ sudo mkdir -p /etc/pgbackrest  
\[postgres@sungsasong ~\]$ sudo mkdir -p /etc/pgbackrest/conf.d  
\[postgres@sungsasong ~\]$ sudo touch /etc/pgbackrest/pgbackrest.conf  
\[postgres@sungsasong ~\]$ sudo chmod 640 /etc/pgbackrest/pgbackrest.conf  
\[postgres@sungsasong ~\]$ sudo chown postgres.postgres -R /etc/pgbackrest/  
\[postgres@sungsasong ~\]$ sudo mkdir /usr/bin/pgbackrest  
\[postgres@sungsasong ~\]$ sudo chown postgres.postgres /usr/bin/pgbackrest/  
\[postgres@sungsasong ~\]$ sudo chmod 755 /usr/bin/pgbackrest/  
编译安装

1  
2  
3  
4  
5  
\[postgres@sungsasong src\]$ ./configure --prefix=/usr/bin/pgbackrest/  
\[postgres@sungsasong src\]$ make -j24  
\[postgres@sungsasong src\]$ make install -j24  
install -d /usr/bin/pgbackrest/bin  
install -m 755 pgbackrest /usr/bin/pgbackrest/bin  
命令测试

1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
17  
18  
19  
20  
21  
22  
23  
\[postgres@sungsasong src\]$ /usr/bin/pgbackrest/bin/pgbackrest  
pgBackRest 2.31 - General help  
  
Usage:  
pgbackrest \[options\] \[command\]  
  
Commands:  
archive-get Get a WAL segment from the archive.  
archive-push Push a WAL segment to the archive.  
backup Backup a database cluster.  
check Check the configuration.  
expire Expire backups that exceed retention.  
help Get help.  
info Retrieve information about backups.  
restore Restore a database cluster.  
stanza-create Create the required stanza data.  
stanza-delete Delete a stanza.  
stanza-upgrade Upgrade a stanza.  
start Allow pgBackRest processes to run.  
stop Stop pgBackRest processes from running.  
version Get version.  
  
Use 'pgbackrest help \[command\]' for more information.  
配置数据库监听和访问及日志（可选）

1  
2  
3  
4  
5  
\[postgres@sungsasong pgbackrest-release-2.31\]$ egrep "10.10" $PGDATA/pg\_hba.conf  
host all all 10.10.20.0/24 trust  
\[postgres@sungsasong pgbackrest-release-2.31\]$ egrep -v "^\#" $PGDATA/postgresql.auto.conf  
logging\_collector = 'on'  
listen\_addresses = '\*'  
配置PostgreSQL数据库数据存储目录

1  
2  
3  
4  
\[postgres@sungsasong src\]$ cat >>/etc/pgbackrest/pgbackrest.conf <<EOF  
> \[demo\]  
> pgl-path=/data/pg10/pgdata  
> EOF  
配置环境变量

1  
\[postgres@sungsasong ~\]$ echo "export PATH=/usr/bin/pgbackrest/bin:\\$PATH" >> .bashrc  
创建备份和归档仓库

1  
2  
3  
\[postgres@sungsasong ~\]$ sudo mkdir -p /pgbackrest/repos  
\[postgres@sungsasong ~\]$ sudo chmod 750 /pgbackrest/ -R  
\[postgres@sungsasong ~\]$ sudo chown postgres.postgres /pgbackrest/ -R  
将仓库路径加载在pgBackRest配置文件中

1  
2  
3  
4  
5  
6  
\[postgres@sungsasong ~\]$ cat /etc/pgbackrest/pgbackrest.conf  
\[demo\]  
pgl-path=/data/pg10/pgdata  
  
\[global\]  
repol-path=/pgbackrest/repos  
配置数据库归档

1  
2  
3  
4  
5  
6  
7  
8  
9  
\[postgres@sungsasong ~\]$ egrep -v "^\#" $PGDATA/postgresql.auto.conf  
logging\_collector = 'on'  
archive\_mode = 'on'  
archive\_command = 'pgbackrest --stanza=demo archive-push %p'  
listen\_addresses = '\*'  
log\_filename = 'postgresql.log'  
log\_line\_prefix = ''  
max\_wal\_senders = '3'  
wal\_level = 'replica'  
重新启动数据库

1  
2  
3  
4  
5  
\[postgres@sungsasong ~\]$ pg\_ctl restart -D $PGDATA -l /tmp/logfile  
waiting for server to shut down.... done  
server stopped  
waiting for server to start.... done  
server started  
可选配置归档压缩

1  
2  
3  
4  
5  
6  
7  
8  
9  
\[postgres@sungsasong ~\]$ cat /etc/pgbackrest/pgbackrest.conf  
\[demo\]  
pgl-path=/data/pg10/pgdata  
  
\[global\]  
repol-path=/pgbackrest/repos  
  
\[global:archive\_push\]  
compress-level=3  
配置基于保留策略的归档

1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
\[postgres@sungsasong ~\]$ cat /etc/pgbackrest/pgbackrest.conf  
\[demo\]  
pg1-path=/data/pg10/pgdata  
pg1-host-config-path=/data/pg10/pgbackrest  
pg1-host-port=22  
pg1-host-user=postgres  
pg1-host=sungsasong  
pg1-port=10001  
pg1-user=postgres  
  
\[global\]  
repo1-path=/pgbackrest/repos  
repo1-retention-full=2  
  
\[global:archive\_push\]  
compress-level=3  
创建存储空间并检查配置

1  
2  
3  
\[postgres@sungsasong ~\]$ pgbackrest --stanza=demo --log-level-console=info stanza-create  
2021-01-08 20:42:58.887 P00 INFO: stanza-create command begin 2.31: --exec-id=9283-e9744c3e --log-level-console=info --pg1-host=sungsasong --pg1-host-config-path=/data/pg10/pgbackrest --pg1-host-port=22 --pg1-host-user=postgres --pg1-path=/data/pg10/pgdata --pg1-port=10001 --pg1-user=postgres --repo1-path=/pgbackrest/repos --stanza=demo  
2021-01-08 20:42:59.835 P00 INFO: stanza-create command end: completed successfully (948ms)  
检查配置

1  
2  
3  
4  
\[postgres@sungsasong ~\]$ pgbackrest --stanza=demo --log-level-console=info check  
2021-01-08 21:41:29.851 P00 INFO: check command begin 2.31: --exec-id=21648-1862ac0d --log-level-console=info --pg1-path=/data/pg10/pgdata --pg1-port=10001 --pg1-user=postgres --repo1-path=/pgbackrest/repos --stanza=demo  
2021-01-08 21:41:32.826 P00 INFO: WAL segment 000000010000000000000006 successfully archived to '/pgbackrest/repos/archive/demo/10-1/0000000100000000/000000010000000000000006-2f027934f4f35cd3983ca4b1b7b43c32ab089448.gz'  
2021-01-08 21:41:32.826 P00 INFO: check command end: completed successfully (2975ms)  
执行备份

默认为增量备份，增量备份将会请求一个基础全备，如果没有基础全备，增量备份将会变更到全备。

1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
pgbackrest --stanza=demo --log-level-console=info backup  
2021-01-08 21:43:55.014 P00 INFO: backup command begin 2.31: --exec-id=21661-13c54272 --log-level-console=info --pg1-path=/data/pg10/pgdata --pg1-port=10001 --pg1-user=postgres --repo1-path=/pgbackrest/repos --repo1-retention-full=2 --stanza=demo  
WARN: no prior backup exists, incr backup has been changed to full  
...  
此处省略很多输出  
...  
2021-01-08 21:44:00.951 P00 INFO: full backup size = 23.3MB  
2021-01-08 21:44:00.951 P00 INFO: execute non-exclusive pg\_stop\_backup() and wait for all WAL segments to archive  
2021-01-08 21:44:01.153 P00 INFO: backup stop archive = 000000010000000000000008, lsn = 0/8000130  
2021-01-08 21:44:01.154 P00 INFO: check archive for segment(s) 000000010000000000000008:000000010000000000000008  
2021-01-08 21:44:01.273 P00 INFO: new backup label = 20210108-214355F  
2021-01-08 21:44:01.301 P00 INFO: backup command end: completed successfully (6288ms)  
2021-01-08 21:44:01.301 P00 INFO: expire command begin 2.31: --exec-id=21661-13c54272 --log-level-console=info --repo1-path=/pgbackrest/repos --repo1-retention-full=2 --stanza=demo  
2021-01-08 21:44:01.304 P00 INFO: expire command end: completed successfully (3ms)  
可以定义备份的类型为增量还是差异备份

使用—type参数指定

1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
\[postgres@sungsasong ~\]$ pgbackrest --stanza=demo --log-level-console=info --type=diff backup  
2021-01-08 21:47:00.964 P00 INFO: backup command begin 2.31: --exec-id=21671-d3f8f8bf --log-level-console=info --pg1-path=/data/pg10/pgdata --pg1-port=10001 --pg1-user=postgres --repo1-path=/pgbackrest/repos --repo1-retention-full=2 --stanza=demo --type=diff  
2021-01-08 21:47:01.770 P00 INFO: last backup label = 20210108-214355F, version = 2.31  
2021-01-08 21:47:01.770 P00 INFO: execute non-exclusive pg\_start\_backup(): backup begins after the next regular checkpoint completes  
2021-01-08 21:47:02.309 P00 INFO: backup start archive = 00000001000000000000000A, lsn = 0/A000028  
2021-01-08 21:47:03.617 P01 INFO: backup file /data/pg10/pgdata/global/pg\_control (8KB, 99%) checksum 5f020e7df484269ea245041be3228673560184ef  
2021-01-08 21:47:03.721 P01 INFO: backup file /data/pg10/pgdata/pg\_logical/replorigin\_checkpoint (8B, 100%) checksum 347fc8f2df71bd4436e38bd1516ccd7ea0d46532  
2021-01-08 21:47:03.722 P00 INFO: diff backup size = 8KB  
2021-01-08 21:47:03.722 P00 INFO: execute non-exclusive pg\_stop\_backup() and wait for all WAL segments to archive  
2021-01-08 21:47:03.942 P00 INFO: backup stop archive = 00000001000000000000000A, lsn = 0/A0000F8  
2021-01-08 21:47:03.943 P00 INFO: check archive for segment(s) 00000001000000000000000A:00000001000000000000000A  
2021-01-08 21:47:04.062 P00 INFO: new backup label = 20210108-214355F\_20210108-214701D  
2021-01-08 21:47:04.092 P00 INFO: backup command end: completed successfully (3129ms)  
2021-01-08 21:47:04.092 P00 INFO: expire command begin 2.31: --exec-id=21671-d3f8f8bf --log-level-console=info --repo1-path=/pgbackrest/repos --repo1-retention-full=2 --stanza=demo  
2021-01-08 21:47:04.095 P00 INFO: expire command end: completed successfully (3ms)  
备份信息查看

1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
17  
18  
19  
20  
\[postgres@sungsasong ~\]$ pgbackrest info  
stanza: demo  
status: ok  
cipher: none  
  
db (current)  
wal archive min/max (10-1): 000000010000000000000001/00000001000000000000000A  
  
full backup: 20210108-214355F  
timestamp start/stop: 2021-01-08 21:43:55 / 2021-01-08 21:44:01  
wal start/stop: 000000010000000000000008 / 000000010000000000000008  
database size: 23.3MB, backup size: 23.3MB  
repository size: 2.7MB, repository backup size: 2.7MB  
  
diff backup: 20210108-214355F\_20210108-214701D  
timestamp start/stop: 2021-01-08 21:47:01 / 2021-01-08 21:47:03  
wal start/stop: 00000001000000000000000A / 00000001000000000000000A  
database size: 23.3MB, backup size: 8.2KB  
repository size: 2.7MB, repository backup size: 425B  
backup reference list: 20210108-214355F  
还原一个备份

模拟数据库损坏

1  
2  
3  
\[postgres@sungsasong ~\]$ cd $PGDATA/  
\[postgres@sungsasong pgdata\]$ rm -rf \*  
\#100分警告：千万不要拿生产库执行  
执行还原

1  
\[postgres@sungsasong pgdata\]$ pgbackrest --stanza=demo restore  
重新启动数据库  
1  
2  
3  
4  
5  
6  
\[postgres@sungsasong pgdata\]$ pg\_ctl start -D $PGDATA -l /tmp/logfile  
waiting for server to start.... done  
server started  
\[postgres@sungsasong pgdata\]$ psql  
psql (10.13)  
Type "help" for help.  
以上就是pgbackrest介绍及使用。当前使用的最新版本已经可以支持PostgreSQL13版本，仅仅是文档上标注支持最新到PostgreSQL11版本。感兴趣的同学可以下去试一下。