PostgreSQL備份工具 pgBackRest使用詳解

更新時間：2021年02月05日 08:55:01 作者：晟數(shù)

這篇文章主要介紹了PostgreSQL備份工具 pgBackRest使用詳解，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧

前言

pgBackRest是一款開源的備份還原工具，目標(biāo)旨在為備份和還原提供可靠易用的備份。

特性

并行備份和還原

備份操作期間壓縮通常是其瓶頸所在。pgBackRest通過并行處理解決了備份期間壓縮出現(xiàn)的瓶頸問題。

本地遠(yuǎn)程操作

自定義協(xié)議允許 pgBackRest以最小化配置通過SSH在本地或者遠(yuǎn)程執(zhí)行備份、還原和歸檔。并且該程序也通過協(xié)議層提供了PostgreSQL查詢接口，以便于必須要再遠(yuǎn)程訪問PostgreSQL，從而保證了其安全性能。

全量，增量和差異備份

支持全量，增量和差異備份。pgBackRest不受異步時間影響，因此差異和增量備份完全安全。

備份保留策略和和歸檔過期

支持保留策略設(shè)置可以在任意時間創(chuàng)建全備和差異備份的覆蓋。

備份完整性

可以計算備份中每個文件的校驗和，并在還原期間重新檢查。備份完成文件復(fù)制后，將等待直到每個WAL段所需的備份保持一致然后存儲到備份倉庫中。

塊校驗和斷點備份流壓縮和校驗和增量還原并行異步WAL Push和Get表空間重新映射和Link支持S3存儲支持和Azure兼容對象存儲支持加密

使用

安裝解壓

[postgres@pgserver12 tools]$ tar -zxf pgbackrest-release-2.31.tar.gz
[postgres@pgserver12 tools]$ ls

創(chuàng)建必要目錄

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/

編譯安裝

[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

命令測試

[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.

配置數(shù)據(jù)庫監(jiān)聽和訪問及日志（可選）

[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數(shù)據(jù)庫數(shù)據(jù)存儲目錄

[postgres@sungsasong src]$ cat >>/etc/pgbackrest/pgbackrest.conf <<EOF
> [demo]
> pgl-path=/data/pg10/pgdata
> EOF

配置環(huán)境變量

[postgres@sungsasong ~]$ echo "export PATH=/usr/bin/pgbackrest/bin:\$PATH" >> .bashrc

創(chuàng)建備份和歸檔倉庫

[postgres@sungsasong ~]$ sudo mkdir -p /pgbackrest/repos
[postgres@sungsasong ~]$ sudo chmod 750 /pgbackrest/ -R
[postgres@sungsasong ~]$ sudo chown postgres.postgres /pgbackrest/ -R

將倉庫路徑加載在pgBackRest配置文件中

[postgres@sungsasong ~]$ cat /etc/pgbackrest/pgbackrest.conf
[demo]
pgl-path=/data/pg10/pgdata
 
[global]
repol-path=/pgbackrest/repos

配置數(shù)據(jù)庫歸檔

[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'

重新啟動數(shù)據(jù)庫

[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

可選配置歸檔壓縮

[postgres@sungsasong ~]$ cat /etc/pgbackrest/pgbackrest.conf
[demo]
pgl-path=/data/pg10/pgdata
 
[global]
repol-path=/pgbackrest/repos
 
[global:archive_push]
compress-level=3

配置基于保留策略的歸檔

[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

創(chuàng)建存儲空間并檢查配置

[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)

檢查配置

[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)

執(zhí)行備份

默認(rèn)為增量備份，增量備份將會請求一個基礎(chǔ)全備，如果沒有基礎(chǔ)全備，增量備份將會變更到全備。

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參數(shù)指定

[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)

備份信息查看

[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

還原一個備份

模擬數(shù)據(jù)庫損壞

[postgres@sungsasong ~]$ cd $PGDATA/
[postgres@sungsasong pgdata]$ rm -rf *
#100分警告：千萬不要拿生產(chǎn)庫執(zhí)行

執(zhí)行還原

[postgres@sungsasong pgdata]$ pgbackrest --stanza=demo restore

重新啟動數(shù)據(jù)庫

[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介紹及使用。當(dāng)前使用的最新版本已經(jīng)可以支持PostgreSQL13版本，僅僅是文檔上標(biāo)注支持最新到PostgreSQL11版本。感興趣的同學(xué)可以下去試一下。

希望能給大家一個參考，也希望大家多多支持腳本之家。如有錯誤或未考慮完全的地方，望不吝賜教。

您可能感興趣的文章: