PostgreSQL pgbackrest 怎么设计带远程控制端的PG备份系统系统

AustinDatabases

发布于 2023-12-14 12:41:19

2990

发布于 2023-12-14 12:41:19

前两期介绍了关于pgbackrest 的基本搭建和一些设置以及日常的使用，在实际的工作中，pgbackrest本身是可以进行集中化管理的，通过集中化的管理方式来进行批量的大范围的数据库管理如何进行相关的操作和配置。实际上pgbackrest 在单机备份的简便性还是不如pgrman的的便利性，但是在下图的备份方案中，则pgbackrest 是好的备份的方案选择。

同时这篇文章，和上两篇之间有很大时间的差距的原因在，这篇在测试中失败N次，各种问题，各种参数在文档中未说明等问题。

问题的难点，在于中文，英文，没有一篇文档说明了其中还包含了操作的顺序，最后找到其中的奥秘还是通过某个繁体字的网站的一点信息发现其中一直没有成功的原因。

这里需要将pgbackrest 单独进行安装到一台服务器中，并部署pgbackrest的主机，同时打通主机与各个postgresql数据库的SSH 免密连接。

下面是整个搭建的过程

1 搭建设备

pgbackrest 主机 IP 地址 192.168.198.150

postgresql IP 地址 192.168.198.101

2 账号

pgbackrest 账号为 pgbackrest

postgresql 账号为 postgres

3 主机之间进行ssh 免密设置

ssh-keygen -t rsa

ssh-copy-id postgres@192.168.198.101

ssh-copy-id postgres@192.168.198.150

在不同的主机上进行ssh 的测试

pgbackrest@pgbackrest:~$ 
pgbackrest@pgbackrest:~$ ssh postgres@192.168.198.101
Last login: Thu Feb 16 20:54:36 2023
[postgres@postgre14 ~]$

[postgres@postgre14 ~]$ ssh pgbackrest@192.168.198.150
Welcome to Ubuntu 22.04 LTS (GNU/Linux 5.15.0-87-generic x86_64)

 * Documentation:  https://help.ubuntu.com
 * Management:     https://landscape.canonical.com
 * Support:        https://ubuntu.com/advantage

  System information as of Wed Dec  6 06:18:53 AM UTC 2023

4 pgbackrest 的安装

这里有几个注意事项

1 pgbackrest 的配置文件的默认位置在 /etc/pgbackrest/pgbackrest.conf

这个位置最好不要进行变化，同时日志目录在 /var/log/pgbackrest/目录中，这些目录都需要给 pgbackrest postgres 账号具有全部的目录和文件的写入权限

2 数据库，控制端全部需要安装pgbackrest

3 pgbackrest 的安装目录最好一致，并且可以在对应的账号下直接引用命令。

4 针对多实例的备份，应建立不同的文件夹进行数据存储，不能将各个实例的备份不进行目录分割

5 基于备份数据通过网络进行备份和恢复，需要网络，网络的带宽至少为千兆

——————————————————————————————

以下为数据库服务端的配置文件

[test]
pg1-path=/pgdata/data
pg1-socket-path=/tmp


[global]
repo1-host=192.168.198.150
log-level-file=detail
log-path=/var/log/pgbackrest

以下为pgbackrest 服务端的配置

[test]
pg1-path=/pgdata/data
pg1-port=5432
pg1-socket-path=/tmp
pg1-host=192.168.198.101
pg1-user=backup
pg1-pgpass=/home/postgres/.pgpass

[global]
repo1-path=/pgbackrest/backup
repo1-retention-full=2
log-level-console=info
log-level-file=debug
buffer-size=16MiB
compress-type=gz


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

配置错误，以及没有按照操作顺序，导致的备份初始化失败

pgbackrest@pgbackrest:~$ pgbackrest --stanza=test stanza-create
WARN: configuration file contains invalid option 'pg1-pgpass'
2023-12-06 07:38:07.986 P00   INFO: stanza-create command begin 2.48: --buffer-size=16MiB --exec-id=45679-3c58b42b --log-level-console=info --log-level-file=debug --pg1-host=192.168.198.101 --pg1-host-config-path=/etc/pgbackrest --pg1-host-port=22 --pg1-host-user=postgres --pg1-path=/pgdata/data --pg1-port=5432 --pg1-user=postgres --repo1-path=/pgbackrest/backup --stanza=test
WARN: unable to check pg1: [DbConnectError] raised from remote-0 ssh protocol on '192.168.198.101': unable to connect to 'dbname='postgres' port=5432 user='postgres'': could not connect to server: No such file or directory
       Is the server running locally and accepting
       connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
ERROR: [056]: unable to find primary cluster - cannot proceed
       HINT: are all available clusters in recovery?

[postgres@postgre14 pgbackrest]$ pgbackrest --stanza=test stanza-create
WARN: unable to check pg1: [DbConnectError] unable to connect to 'dbname='postgres' port=5432': could not connect to server: No such file or directory
       Is the server running locally and accepting
       connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
ERROR: [056]: unable to find primary cluster - cannot proceed

pgbackrest --stanza=test stanza-create
WARN: configuration file contains invalid option 'pg1-pgpass'
2023-12-06 07:52:26.993 P00   INFO: stanza-create command begin 2.48: --buffer-size=16MiB --exec-id=47078-8289a622 --log-level-console=info --log-level-file=debug --pg1-host=192.168.198.101 --pg1-path=/pgdata/data --pg1-port=5432 --pg1-socket-path=/tmp --pg1-user=backup --repo1-path=/pgbackrest/backup --stanza=test
2023-12-06 07:52:27.799 P00   INFO: stanza-create for stanza 'test' on repo1
ERROR: [055]: archive.info exists but backup.info is missing on repo1
       HINT: this may be a symptom of repository corruption!
2023-12-06 07:52:27.801 P00   INFO: stanza-create command end: aborted with exception [055]

[postgres@postgre14 pgbackrest]$ pgbackrest --stanza=test stanza-create
ERROR: [055]: archive.info exists but backup.info is missing on repo1
       HINT: this may be a symptom of repository corruption!

——————————————————————————————

pgbackrest 正确进行初始化的顺序

1 需要在远程控制端进行初始化，在远程控制端完成初始化后，在再数据库端进行初始化

pgbackrest --stanza=test stanza-create
WARN: configuration file contains invalid option 'pg1-pgpass'
2023-12-06 07:52:54.849 P00   INFO: stanza-create command begin 2.48: --buffer-size=16MiB --exec-id=47087-9c19c49c --log-level-console=info --log-level-file=debug --pg1-host=192.168.198.101 --pg1-path=/pgdata/data --pg1-port=5432 --pg1-socket-path=/tmp --pg1-user=backup --repo1-path=/pgbackrest/backup --stanza=test
2023-12-06 07:52:55.620 P00   INFO: stanza-create for stanza 'test' on repo1
2023-12-06 07:52:55.731 P00   INFO: stanza-create command end: completed successfully (885ms)

[postgres@postgre14 pgbackrest]$ pgbackrest --stanza=test stanza-create

下面是，远程启动，check, 全备，差异备份

pgbackrest@pgbackrest:/pgbackrest$ pgbackrest --stanza=test check
WARN: configuration file contains invalid option 'pg1-pgpass'
2023-12-06 07:53:19.995 P00   INFO: check command begin 2.48: --buffer-size=16MiB --exec-id=47148-5c776784 --log-level-console=info --log-level-file=debug --pg1-host=192.168.198.101 --pg1-path=/pgdata/data --pg1-port=5432 --pg1-socket-path=/tmp --pg1-user=backup --repo1-path=/pgbackrest/backup --stanza=test
2023-12-06 07:53:20.836 P00   INFO: check repo1 configuration (primary)
2023-12-06 07:53:21.500 P00   INFO: check repo1 archive for WAL (primary)
2023-12-06 07:53:22.106 P00   INFO: WAL segment 00000001000000070000000D successfully archived to '/pgbackrest/backup/archive/test/14-1/0000000100000007/00000001000000070000000D-46144492923763da2d73d3b681abb903320a3f24.gz' on repo1
2023-12-06 07:53:22.210 P00   INFO: check command end: completed successfully (2218ms)
pgbackrest@pgbackrest:/pgbackrest$ 
pgbackrest@pgbackrest:/pgbackrest$ 
pgbackrest@pgbackrest:/pgbackrest$ pgbackrest --stanza=test --log-level-console=info backup
WARN: configuration file contains invalid option 'pg1-pgpass'
2023-12-06 08:10:38.578 P00   INFO: backup command begin 2.48: --buffer-size=16MiB --compress-type=gz --exec-id=47204-2ecf5294 --log-level-console=info --log-level-file=debug --pg1-host=192.168.198.101 --pg1-path=/pgdata/data --pg1-port=5432 --pg1-socket-path=/tmp --pg1-user=backup --repo1-path=/pgbackrest/backup --repo1-retention-full=2 --stanza=test
WARN: no prior backup exists, incr backup has been changed to full
2023-12-06 08:10:39.684 P00   INFO: execute non-exclusive backup start: backup begins after the next regular checkpoint completes
2023-12-06 08:10:41.103 P00   INFO: backup start archive = 00000001000000070000000F, lsn = 7/3C000060
2023-12-06 08:10:41.103 P00   INFO: check archive for prior segment 00000001000000070000000E
2023-12-06 08:14:40.153 P00   INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2023-12-06 08:14:40.811 P00   INFO: backup stop archive = 00000001000000070000000F, lsn = 7/3C000170
2023-12-06 08:14:40.815 P00   INFO: check archive for segment(s) 00000001000000070000000F:00000001000000070000000F
2023-12-06 08:14:42.051 P00   INFO: new backup label = 20230217-035638F
2023-12-06 08:14:42.108 P00   INFO: full backup size = 12.7GB, file total = 2860
2023-12-06 08:14:42.109 P00   INFO: backup command end: completed successfully (243533ms)
2023-12-06 08:14:42.109 P00   INFO: expire command begin 2.48: --buffer-size=16MiB --exec-id=47204-2ecf5294 --log-level-console=info --log-level-file=debug --repo1-path=/pgbackrest/backup --repo1-retention-full=2 --stanza=test
2023-12-06 08:14:42.112 P00   INFO: expire command end: completed successfully (3ms)
pgbackrest@pgbackrest:/pgbackrest$ 
pgbackrest@pgbackrest:/pgbackrest$ 
pgbackrest@pgbackrest:/pgbackrest$ pgbackrest --stanza=test --log-level-console=info --type=diff
ERROR: [030]: no command found
pgbackrest@pgbackrest:/pgbackrest$ pgbackrest --stanza=test --log-level-console=info --type-diff
ERROR: [031]: invalid option '--type-diff'
pgbackrest@pgbackrest:/pgbackrest$ pgbackrest --stanza=test --log-level-console=info --type-diff  backup
ERROR: [031]: invalid option '--type-diff'
pgbackrest@pgbackrest:/pgbackrest$ pgbackrest --stanza=test  --type-diff  backup
ERROR: [031]: invalid option '--type-diff'
pgbackrest@pgbackrest:/pgbackrest$ pgbackrest --stanza=test  --type=diff backup
WARN: configuration file contains invalid option 'pg1-pgpass'
2023-12-06 08:27:07.388 P00   INFO: backup command begin 2.48: --buffer-size=16MiB --compress-type=gz --exec-id=47390-89b37813 --log-level-console=info --log-level-file=debug --pg1-host=192.168.198.101 --pg1-path=/pgdata/data --pg1-port=5432 --pg1-socket-path=/tmp --pg1-user=backup --repo1-path=/pgbackrest/backup --repo1-retention-full=2 --stanza=test --type=diff
2023-12-06 08:27:08.322 P00   INFO: last backup label = 20230217-035638F, version = 2.48
2023-12-06 08:27:08.322 P00   INFO: execute non-exclusive backup start: backup begins after the next regular checkpoint completes
2023-12-06 08:27:09.279 P00   INFO: backup start archive = 000000010000000700000011, lsn = 7/44000028
2023-12-06 08:27:09.280 P00   INFO: check archive for prior segment 000000010000000700000010
2023-12-06 08:27:11.620 P00   INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2023-12-06 08:27:12.279 P00   INFO: backup stop archive = 000000010000000700000011, lsn = 7/44000100
2023-12-06 08:27:12.284 P00   INFO: check archive for segment(s) 000000010000000700000011:000000010000000700000011
2023-12-06 08:27:13.010 P00   INFO: new backup label = 20230217-035638F_20230217-041429D
2023-12-06 08:27:13.070 P00   INFO: diff backup size = 8.3KB, file total = 2860
2023-12-06 08:27:13.071 P00   INFO: backup command end: completed successfully (5686ms)
2023-12-06 08:27:13.071 P00   INFO: expire command begin 2.48: --buffer-size=16MiB --exec-id=47390-89b37813 --log-level-console=info --log-level-file=debug --repo1-path=/pgbackrest/backup --repo1-retention-full=2 --stanza=test
2023-12-06 08:27:13.074 P00   INFO: expire command end: completed successfully (3ms)

整体的备份都很顺利，并无问题。

总结：之前的失败的主要因素

1 对于配置文件的参数理解不深刻

2 没有获知初始化的顺序，这是导致远程备份失败的最大的原因

后续会对以上的参数，以及更多的一些设置方式以及数据恢复等进行测试和文字的撰写。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2023-12-12，如有侵权请联系 cloudcommunity@tencent.com 删除

postgresql