PostgreSQL pgbackrest 怎麼設計帶遠端控制端的PG備份系統系統
來源：AustinDatabases
前兩期介紹了關於pgbackrest 的基本搭建和一些設定以及日常的使用，在實際的工作中，pgbackrest本身是可以進行集中化管理的，透過集中化的管理方式來進行批次的大範圍的資料庫管理如何進行相關的操作和配置。實際上pgbackrest 在單機備份的簡便性還是不如pgrman的的便利性，但是在下圖的備份方案中，則pgbackrest 是好的備份的方案選擇。
同時這篇文章，和上兩篇之間有很大時間的差距的原因在，這篇在測試中失敗N次，各種問題，各種引數在文件中未說明等問題。
問題的難點，在於中文，英文，沒有一篇文件說明了其中還包含了操作的順序，最後找到其中的奧秘還是透過某個繁體字的網站的一點資訊發現其中一直沒有成功的原因。
這裡需要將pgbackrest 單獨進行安裝到一臺伺服器中，並部署pgbackrest的主機，同時打通主機與各個postgresql資料庫的SSH 免密連線。
下面是整個搭建的過程
postgresql IP 地址 192.168.198.101
2 賬號
pgbackrest 賬號為 pgbackrest
postgresql 賬號為 postgres
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:
* Management:
* Support:
System information as of Wed Dec 6 06:18:53 AM UTC 2023
[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
[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 --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
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)
來自 “ ITPUB部落格 ” ，連結：https://blog.itpub.net/70027826/viewspace-3000235/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 怎麼防止電腦被遠端控制？Win10系統停用遠端控制方法Win10
- windows10系統qq怎麼發遠端_windows10qq遠端控制怎麼弄Windows
- 洞見RSA 2021｜如何設計安全的控制系統遠端訪問
- 簡要分析Hacking Team 遠端控制系統
- 免費遠端桌面伺服器 系統自帶遠端桌面伺服器
- Debian系統上怎麼配置SSH遠端訪問
- Win10系統怎麼建立備份系統映像Win10
- 分散式系統：遠端呼叫分散式
- 無法連線遠端，Windows系統電腦無法遠端連線怎麼辦Windows
- innobackupex遠端備份
- SQLServer遠端備份SQLServer
- 電腦遠端控制怎麼弄 遠端控制別人的電腦
- 如何備份win10_win10系統怎麼備份Win10
- Linux系統的遠端登陸管理Linux
- win10系統怎麼備份到u盤_win10u盤備份系統的方法Win10
- 電腦遠端控制怎麼弄 學會遠端控制電腦
- 如何遠端登入Linux系統？Linux
- 如何遠端登入Windows系統？Windows
- 怎麼遠端控制電腦 遠端控制電腦的兩種方法分享
- win10正式版系統怎麼使用TeamViewer遠端列印Win10View
- win10自帶系統遷移工具怎麼用 win10備份系統到新硬碟怎麼操作Win10硬碟
- 遠端辦公系統整合CRM+HR+ERP的管理系統
- 啟用遠端系統上的應用
- vnc遠端控制軟體怎麼用，vnc遠端控制軟體怎麼用，使用教程VNC
- 如何建設分散式微電網遠端管理系統？分散式
- win10系統遠端桌面連線在哪_win10怎麼開啟遠端桌面連線Win10
- win10備份系統成映象檔案怎麼操作 如何備份win10系統映象Win10
- win10怎麼備份系統還原 win10系統備份與還原步驟Win10
- vscode配置遠端linux系統除錯VSCodeLinux除錯
- 內網部署遠端會議系統內網
- windows系統vscode遠端除錯MySQLWindowsVSCode除錯MySql
- 遠端檔案管理系統（SpringBoot + Vue）Spring BootVue
- 遠端庭審系統解決方案
- 為什麼要遠端連線Linux系統？linux作業系統入門Linux作業系統
- Win7系統防火牆阻止遠端桌面連線怎麼辦？Win7系統防火牆阻止了遠端桌面連線的解決方法Win7防火牆
- 城市景觀智慧燈控系統如何遠端監控和控制
- Linux系統最常用的遠端管理協議！Linux協議
- 使用Xtrabackup遠端備份MysqlMySql