【pgBackRest备份工具使用】

一、工具介绍

pgBackRest是PostgreSQL数据库的一个开源备份工具，支持全量、增量和差异备份。这一点优于PG原生的备份工具pg_basebackup，因为pg_basebackup不支持增量备份，并且支持备份完整性检查等等。pgBackRest v2.49是当前的稳定版本。

项目主页：https://pgbackrest.org/
Github链接：https://github.com/pgbackrest/pgbackrest

优点：

支持本地或远程操作， 但是远程操作需要配置SSH
支持全量、增量和差异备份
支持从standby实例备份，减小主库的IO压力
支持备份完整性检查（checksum 算法）
支持压缩：支持gzip, bzip, lz4, zstd等压缩算法，并提供不同压缩等级

二、部署pgBackRest备份工具

1.安装依赖

yum -y install libxml2 libxml2-devel libyaml libyaml-devel bzip2 bzip2-devel 

2.创建所需目录并赋予权限

su - root
mkdir -p -m 770 /var/log/pgbackrest
chown postgres.postgres /var/log/pgbackrest/
mkdir -p /etc/pgbackrest
mkdir -p /etc/pgbackrest/conf.d
touch /etc/pgbackrest/pgbackrest.conf
chmod 640 /etc/pgbackrest/pgbackrest.conf
chown postgres.postgres -R /etc/pgbackrest/

3.编译安装pgbackrest备份工具

su - postgres
unzip pgbackrest-release-2.49.zip
cd pgbackrest-release-2.49/
ls
CODING.md  CONTRIBUTING.md  LICENSE  README.md  doc  meson.build  meson_options.txt  src  test

cd src/
./configure
make -j 24
make install -j 24

4.验证版本和命令

-bash-4.2$ pgbackrest version
pgBackRest 2.49
-bash-4.2$ pgbackrest help
pgBackRest 2.49 - General help

Usage:
    pgbackrest [options] [command]

Commands:
    annotate        Add or modify backup annotation.
    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.
    repo-get        Get a file from a repository.
    repo-ls         List files in a repository.
    restore         Restore a database cluster.
    server          pgBackRest server.
    server-ping     Ping pgBackRest server.
    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.
    verify          Verify contents of the repository.
    version         Get version.

Use 'pgbackrest help [command]' for more information.

三、使用方式

1.配置数据库的 archive_command 命令

开启归档，修改archive_command
stanza是指备份集的名称，可以在pgBackRest的配置文件中进行设置。

vi postgresql.conf 

archive_mode = on
archive_command = 'pgbackrest --stanza=pgxmaster archive-push %p'

需要修改archive_command并开启归档，否则可能有相关报错和提示

2.初始化实例(创建 Stanza)

pgbackrest --stanza=pgxmaster --log-level-console=info stanza-create

执行结果如下：

-bash-4.2$ pgbackrest --stanza=pgxmaster --log-level-console=info stanza-create
2023-12-29 20:53:25.054 P00   INFO: stanza-create command begin 2.49: --exec-id=59466-2c8ab1f2 --log-level-console=info --log-path=/var/log/pgbackrest --pg1-path=/data/pg16/data --pg1-port=5432 --pg1-socket-path=/tmp --repo1-path=/var/lib/pgsql/pgbackreset_bakdir --stanza=pgxmaster
2023-12-29 20:53:25.681 P00   INFO: stanza-create for stanza 'pgxmaster' on repo1
2023-12-29 20:53:25.703 P00   INFO: stanza-create command end: completed successfully (652ms)

3.修改配置文件

vi /etc/pgbackrest/pgbackrest.conf

-bash-4.2$ cat /etc/pgbackrest/pgbackrest.conf
[pgxmaster]
pg01-path=/data/pg16/data
pg01-port=5432
pg01-socket-path=/tmp
#pg1-user=postgres
#pg1-host-config-path=/etc/pgbackrest 
#pg1-host-port=22
#pg1-host-user=postgres

[global]
#repo1-path指定 备份和归档仓库路径 
repo1-path=/var/lib/pgsql/pgbackreset_bakdir
backup-user=pgsql
retention-full=2
#repo1-cipher-pass=
#repo1-cipher-type=aes-256-cbc
start-fast=y
process-max=3
log-path=/var/log/pgbackrest
[global:archive-push]
#compress-level指定压缩级别
compress-level=3

如果需要进行远程备份，需要配置pg1-host、pg1-host-port、pg1-host-user等。

4.检查配置

pgbackrest --stanza=pgxmaster --log-level-console=info check

执行结果如下：

-bash-4.2$ pgbackrest --stanza=pgxmaster --log-level-console=info check
2023-12-29 20:54:27.959 P00   INFO: check command begin 2.49: --exec-id=59474-9f746218 --log-level-console=info --log-path=/var/log/pgbackrest --pg1-path=/data/pg16/data --pg1-port=5432 --pg1-socket-path=/tmp --repo1-path=/var/lib/pgsql/pgbackreset_bakdir --stanza=pgxmaster
2023-12-29 20:54:28.575 P00   INFO: check repo1 configuration (primary)
2023-12-29 20:54:28.587 CST [59475] LOG:  restore point "pgBackRest Archive Check" created at 0/9000090
2023-12-29 20:54:28.587 CST [59475] STATEMENT:  select pg_catalog.pg_create_restore_point('pgBackRest Archive Check')::text
2023-12-29 20:54:29.293 P00   INFO: check repo1 archive for WAL (primary)
2023-12-29 20:54:29.396 P00   INFO: WAL segment 000000010000000000000009 successfully archived to '/var/lib/pgsql/pgbackreset_bakdir/archive/pgxmaster/16-1/0000000100000000/000000010000000000000009-1ff7c856c782639a8401763893d294e666946657.gz' on repo1
2023-12-29 20:54:29.397 P00   INFO: check command end: completed successfully (1439ms)

5.全量备份

使用 pgbackrest help backup命令可以查看备份可以带的一些命令参数

pgbackrest help backup

全量备份命令如下

pgbackrest --stanza=pgxmaster --log-level-console=info backup --type=full

执行结果如下：

-bash-4.2$ pgbackrest --stanza=pgxmaster --log-level-console=info backup --type=full
2023-12-29 21:02:03.548 P00   INFO: backup command begin 2.49: --exec-id=59518-ff63d8ae --log-level-console=info --log-path=/var/log/pgbackrest --pg1-path=/data/pg16/data --pg1-port=5432 --pg1-socket-path=/tmp --process-max=3 --repo1-path=/var/lib/pgsql/pgbackreset_bakdir --repo1-retention-full=2 --stanza=pgxmaster --start-fast --type=full
2023-12-29 21:02:04.283 P00   INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2023-12-29 21:02:04.705 CST [59454] LOG:  checkpoint starting: immediate force wait
2023-12-29 21:02:04.730 CST [59454] LOG:  checkpoint complete: wrote 19 buffers (0.1%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.010 s, sync=0.008 s, total=0.026 s; sync files=16, longest=0.006 s, average=0.001 s; distance=16384 kB, estimate=29633 kB; lsn=0/B000098, redo lsn=0/B000060
2023-12-29 21:02:04.991 P00   INFO: backup start archive = 00000001000000000000000B, lsn = 0/B000060
2023-12-29 21:02:04.992 P00   INFO: check archive for prior segment 00000001000000000000000A
2023-12-29 21:02:07.833 P00   INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2023-12-29 21:02:08.237 P00   INFO: backup stop archive = 00000001000000000000000B, lsn = 0/B000138
2023-12-29 21:02:08.244 P00   INFO: check archive for segment(s) 00000001000000000000000B:00000001000000000000000B
2023-12-29 21:02:08.279 P00   INFO: new backup label = 20231229-210204F
2023-12-29 21:02:08.380 P00   INFO: full backup size = 23MB, file total = 974
2023-12-29 21:02:08.380 P00   INFO: backup command end: completed successfully (4835ms)
2023-12-29 21:02:08.380 P00   INFO: expire command begin 2.49: --exec-id=59518-ff63d8ae --log-level-console=info --log-path=/var/log/pgbackrest --repo1-path=/var/lib/pgsql/pgbackreset_bakdir --repo1-retention-full=2 --stanza=pgxmaster
2023-12-29 21:02:08.390 P00   INFO: expire command end: completed successfully (10ms)

查看备份

-bash-4.2$ date
Fri Dec 29 21:03:05 CST 2023
-bash-4.2$ pgbackrest info
stanza: pgxmaster
    status: ok
    cipher: none

    db (current)
        wal archive min/max (16): 000000010000000000000008/00000001000000000000000B

        full backup: 20231229-210204F
            timestamp start/stop: 2023-12-29 21:02:04+08 / 2023-12-29 21:02:08+08
            wal start/stop: 00000001000000000000000B / 00000001000000000000000B
            database size: 23MB, database backup size: 23MB
            repo1: backup set size: 3MB, backup size: 3MB

备份路径分成两个，archive和backup两个路径

具体结构如下

-bash-4.2$ pwd
/var/lib/pgsql/pgbackreset_bakdir
-bash-4.2$ tree -d
.
├── archive
│?? └── pgxmaster
│??     └── 16-1
│??         └── 0000000100000000
└── backup
    └── pgxmaster
        ├── 20231229-210204F
        │?? └── pg_data
        │??     ├── base
        │??     │?? ├── 1
        │??     │?? ├── 14484
        │??     │?? └── 4
        │??     ├── global
        │??     ├── pg_commit_ts
        │??     ├── pg_dynshmem
        │??     ├── pg_logical
        │??     │?? ├── mappings
        │??     │?? └── snapshots
        │??     ├── pg_multixact
        │??     │?? ├── members
        │??     │?? └── offsets
        │??     ├── pg_notify
        │??     ├── pg_replslot
        │??     ├── pg_serial
        │??     ├── pg_snapshots
        │??     ├── pg_stat
        │??     ├── pg_stat_tmp
        │??     ├── pg_subtrans
        │??     ├── pg_tblspc
        │??     ├── pg_twophase
        │??     ├── pg_upgrade_output.d
        │??     │?? ├── 20231229T185426.620
        │??     │?? │?? ├── dump
        │??     │?? │?? └── log
        │??     │?? └── 20231229T185705.267
        │??     │??     ├── dump
        │??     │??     └── log
        │??     ├── pg_wal
        │??     │?? └── archive_status
        │??     └── pg_xact
        ├── backup.history
        │?? └── 2023
        └── latest -> 20231229-210204F

43 directories

---------------------------------------------------------------------------------------------------------------

//backup下每个备份实例对应一个目录，下边带有版本号的目录里存储wal归档相关备份，archive.info里存放归档备份相关信息。

-bash-4.2$ pwd
/var/lib/pgsql/pgbackreset_bakdir/backup/pgxmaster
-bash-4.2$ ls
20231229-210204F  backup.history  backup.info  backup.info.copy  latest
-bash-4.2$ cat backup.info
[backrest]
backrest-format=5
backrest-version="2.49"

[backup:current]
20231229-210204F={"backrest-format":5,"backrest-version":"2.49","backup-archive-start":"00000001000000000000000B","backup-archive-stop":"00000001000000000000000B","backup-error":false,"backup-info-repo-size":3225873,"backup-info-repo-size-delta":3225873,"backup-info-size":24131917,"backup-info-size-delta":24131917,"backup-lsn-start":"0/B000060","backup-lsn-stop":"0/B000138","backup-timestamp-start":1703854924,"backup-timestamp-stop":1703854928,"backup-type":"full","db-id":1,"option-archive-check":true,"option-archive-copy":false,"option-backup-standby":false,"option-checksum-page":false,"option-compress":true,"option-hardlink":false,"option-online":true}

[db]
db-catalog-version=202307071
db-control-version=1300
db-id=1
db-system-id=7317967753805234269
db-version="16"

[db:history]
1={"db-catalog-version":202307071,"db-control-version":1300,"db-system-id":7317967753805234269,"db-version":"16"}

[backrest]
backrest-checksum="1bc5921e775f76660ff4ab7e54f04125f2b93ced"

-------------------------------------------------

-bash-4.2$ pwd
/var/lib/pgsql/pgbackreset_bakdir/backup/pgxmaster
-bash-4.2$ ls
20231229-210204F  backup.history  backup.info  backup.info.copy  latest
-bash-4.2$ cd 20231229-210204F/
-bash-4.2$ ls
backup.manifest  backup.manifest.copy  pg_data
-bash-4.2$ cd pg_data/
-bash-4.2$ ls
backup_label.gz  pg_dynshmem       pg_multixact  pg_snapshots  pg_tblspc            pg_wal
base             pg_hba.conf.gz    pg_notify     pg_stat       pg_twophase          pg_xact
global           pg_ident.conf.gz  pg_replslot   pg_stat_tmp   pg_upgrade_output.d  postgresql.auto.conf.gz
pg_commit_ts     pg_logical        pg_serial     pg_subtrans   PG_VERSION.gz        postgresql.conf.gz

---------------------------------------------------------------------------------------------------------------

//archive下每个备份实例对应一个目录，下边带有版本号的目录里存储wal归档相关备份，backup.info里存放物理备份相关信息。

/var/lib/pgsql/pgbackreset_bakdir/archive/pgxmaster
-bash-4.2$ ls
16-1  archive.info  archive.info.copy

-------------------------------------------------

-bash-4.2$ cat archive.info
[backrest]
backrest-format=5
backrest-version="2.49"

[db]
db-id=1
db-system-id=7317967753805234269
db-version="16"

[db:history]
1={"db-id":7317967753805234269,"db-version":"16"}

[backrest]
backrest-checksum="9125f9a1f113ce1ccb7b5d9e13fb5bfacfcfdce4"
-------------------------------------------------
-bash-4.2$ pwd
/var/lib/pgsql/pgbackreset_bakdir/archive/pgxmaster/16-1/0000000100000000

-bash-4.2$ cat 00000001000000000000000B.00000060.backup
START WAL LOCATION: 0/B000060 (file 00000001000000000000000B)
STOP WAL LOCATION: 0/B000138 (file 00000001000000000000000B)
CHECKPOINT LOCATION: 0/B000098
BACKUP METHOD: streamed
BACKUP FROM: primary
START TIME: 2023-12-29 21:02:04 CST
LABEL: pgBackRest backup started at 2023-12-29 21:02:04.486765+08
START TIMELINE: 1
STOP TIME: 2023-12-29 21:02:08 CST
STOP TIMELINE: 1

归档备份如下所示

物理备份如下所示

6.增量备份

pgbackrest --stanza=pgxmaster --log-level-console=info backup --type=incr

执行结果如下所示

-bash-4.2$ pgbackrest --stanza=pgxmaster --log-level-console=info backup --type=incr
2023-12-29 21:15:19.913 P00   INFO: backup command begin 2.49: --exec-id=59681-61772c9f --log-level-console=info --log-path=/var/log/pgbackrest --pg1-path=/data/pg16/data --pg1-port=5432 --pg1-socket-path=/tmp --process-max=3 --repo1-path=/var/lib/pgsql/pgbackreset_bakdir --repo1-retention-full=2 --stanza=pgxmaster --start-fast --type=incr
2023-12-29 21:15:20.721 P00   INFO: last backup label = 20231229-210204F, version = 2.49
2023-12-29 21:15:20.721 P00   INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2023-12-29 21:15:21.532 CST [59454] LOG:  checkpoint starting: immediate force wait
2023-12-29 21:15:21.534 CST [59454] LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.001 s, total=0.003 s; sync files=0, longest=0.000 s, average=0.000 s; distance=16383 kB, estimate=27115 kB; lsn=0/D000060, redo lsn=0/D000028
2023-12-29 21:15:21.807 P00   INFO: backup start archive = 00000001000000000000000D, lsn = 0/D000028
2023-12-29 21:15:21.807 P00   INFO: check archive for prior segment 00000001000000000000000C
2023-12-29 21:15:23.793 P00   INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2023-12-29 21:15:24.196 P00   INFO: backup stop archive = 00000001000000000000000D, lsn = 0/D000100
2023-12-29 21:15:24.200 P00   INFO: check archive for segment(s) 00000001000000000000000D:00000001000000000000000D
2023-12-29 21:15:24.233 P00   INFO: new backup label = 20231229-210204F_20231229-211520I
2023-12-29 21:15:24.324 P00   INFO: incr backup size = 8.3KB, file total = 974
2023-12-29 21:15:24.324 P00   INFO: backup command end: completed successfully (4414ms)
2023-12-29 21:15:24.324 P00   INFO: expire command begin 2.49: --exec-id=59681-61772c9f --log-level-console=info --log-path=/var/log/pgbackrest --repo1-path=/var/lib/pgsql/pgbackreset_bakdir --repo1-retention-full=2 --stanza=pgxmaster
2023-12-29 21:15:24.331 P00   INFO: expire command end: completed successfully (7ms)

查看备份如下所示

-bash-4.2$ pgbackrest info
stanza: pgxmaster
    status: ok
    cipher: none

    db (current)
        wal archive min/max (16): 000000010000000000000008/00000001000000000000000D

        full backup: 20231229-210204F
            timestamp start/stop: 2023-12-29 21:02:04+08 / 2023-12-29 21:02:08+08
            wal start/stop: 00000001000000000000000B / 00000001000000000000000B
            database size: 23MB, database backup size: 23MB
            repo1: backup set size: 3MB, backup size: 3MB

        incr backup: 20231229-210204F_20231229-211520I
            timestamp start/stop: 2023-12-29 21:15:20+08 / 2023-12-29 21:15:24+08
            wal start/stop: 00000001000000000000000D / 00000001000000000000000D
            database size: 23MB, database backup size: 8.3KB
            repo1: backup set size: 3MB, backup size: 438B
            backup reference list: 20231229-210204F
-bash-4.2$
-bash-4.2$ date
Fri Dec 29 21:17:52 CST 2023

备份路径下backup数据备份路径下，latest是一个指向最新备份目录的软链。

-bash-4.2$ pwd
/var/lib/pgsql/pgbackreset_bakdir/backup/pgxmaster
-bash-4.2$ ll
total 8
drwxr-x--- 3 postgres postgres   72 Dec 29 21:02 20231229-210204F
drwxr-x--- 3 postgres postgres   72 Dec 29 21:15 20231229-210204F_20231229-211520I
drwxr-x--- 3 postgres postgres   18 Dec 29 21:02 backup.history
-rw-r----- 1 postgres postgres 1797 Dec 29 21:15 backup.info
-rw-r----- 1 postgres postgres 1797 Dec 29 21:15 backup.info.copy
lrwxrwxrwx 1 postgres postgres   33 Dec 29 21:15 latest -> 20231229-210204F_20231229-211520I

backup.info下包含两次备份的信息。

增量备份的backup路径下包含控制文件相关，具体的增量数据，主要还是以wal日志的备份为主。

备份路径下归档备份路径

7.差异备份

pgbackrest --stanza=pgxmaster --log-level-console=info backup --type=diff

先做个数据更改。

-bash-4.2$ psql
psql (16.1)
Type "help" for help.

postgres=# \d tab_ysl
              Table "public.tab_ysl"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           |          |

postgres=# insert into tab_ysl values(6);
INSERT 0 1

执行差异备份

-bash-4.2$ pgbackrest --stanza=pgxmaster --log-level-console=info backup --type=diff
2023-12-29 21:46:54.601 P00   INFO: backup command begin 2.49: --exec-id=59867-16372c14 --log-level-console=info --log-path=/var/log/pgbackrest --pg1-path=/data/pg16/data --pg1-port=5432 --pg1-socket-path=/tmp --process-max=3 --repo1-path=/var/lib/pgsql/pgbackreset_bakdir --repo1-retention-full=2 --stanza=pgxmaster --start-fast --type=diff
2023-12-29 21:46:55.382 P00   INFO: last backup label = 20231229-210204F, version = 2.49
2023-12-29 21:46:55.382 P00   INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2023-12-29 21:46:55.830 CST [59454] LOG:  checkpoint starting: immediate force wait
2023-12-29 21:46:55.840 CST [59454] LOG:  checkpoint complete: wrote 2 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.003 s, sync=0.002 s, total=0.010 s; sync files=2, longest=0.001 s, average=0.001 s; distance=16383 kB, estimate=25076 kB; lsn=0/F000060, redo lsn=0/F000028
2023-12-29 21:46:56.096 P00   INFO: backup start archive = 00000001000000000000000F, lsn = 0/F000028
2023-12-29 21:46:56.096 P00   INFO: check archive for prior segment 00000001000000000000000E
2023-12-29 21:46:57.771 P00   INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2023-12-29 21:46:58.176 P00   INFO: backup stop archive = 00000001000000000000000F, lsn = 0/F000100
2023-12-29 21:46:58.178 P00   INFO: check archive for segment(s) 00000001000000000000000F:00000001000000000000000F
2023-12-29 21:46:58.308 P00   INFO: new backup label = 20231229-210204F_20231229-214655D
2023-12-29 21:46:58.371 P00   INFO: diff backup size = 24.3KB, file total = 974
2023-12-29 21:46:58.371 P00   INFO: backup command end: completed successfully (3773ms)
2023-12-29 21:46:58.372 P00   INFO: expire command begin 2.49: --exec-id=59867-16372c14 --log-level-console=info --log-path=/var/log/pgbackrest --repo1-path=/var/lib/pgsql/pgbackreset_bakdir --repo1-retention-full=2 --stanza=pgxmaster
2023-12-29 21:46:58.382 P00   INFO: expire command end: completed successfully (11ms)

查看备份

差异备份方式在backup目录下主要包含发生变化的对象的数据文件。

-bash-4.2$ pwd
/var/lib/pgsql/pgbackreset_bakdir/backup/pgxmaster/20231229-210204F_20231229-214655D
-bash-4.2$ cd pg_data/
-bash-4.2$ ls
backup_label.gz  base  global  pg_logical  pg_xact
-bash-4.2$ tree
.
├── backup_label.gz
├── base
│?? └── 14484
│??     └── 24594.gz
├── global
│?? └── pg_control.gz
├── pg_logical
│?? └── replorigin_checkpoint.gz
└── pg_xact
    └── 0000.gz

5 directories, 5 files

postgres=# select pg_relation_filepath('tab_ysl');
 pg_relation_filepath
----------------------
 base/14484/24594
(1 row)

archive备份 目录下

8.查看备份情况

pgbackrest info --repo-path=path 

如果写了配置文件，可以省略–repo-path=path ，走配置文件。

-bash-4.2$ pgbackrest info
stanza: pgxmaster
    status: ok
    cipher: none

    db (current)
        wal archive min/max (16): 000000010000000000000008/00000001000000000000000D

        full backup: 20231229-210204F
            timestamp start/stop: 2023-12-29 21:02:04+08 / 2023-12-29 21:02:08+08
            wal start/stop: 00000001000000000000000B / 00000001000000000000000B
            database size: 23MB, database backup size: 23MB
            repo1: backup set size: 3MB, backup size: 3MB

        incr backup: 20231229-210204F_20231229-211520I
            timestamp start/stop: 2023-12-29 21:15:20+08 / 2023-12-29 21:15:24+08
            wal start/stop: 00000001000000000000000D / 00000001000000000000000D
            database size: 23MB, database backup size: 8.3KB
            repo1: backup set size: 3MB, backup size: 438B
            backup reference list: 20231229-210204F

9.监控备份信息

doc/example下有两个SQL文件，使用这两个文件可以帮助我们监控上一次成功备份的状态，

psql -f pgsql-pgbackrest-info.sql
psql -f pgsql-pgbackrest-query.sql

执行结果如下

-bash-4.2$ psql -f pgsql-pgbackrest-info.sql
CREATE SCHEMA
CREATE FUNCTION
-bash-4.2$ psql -f pgsql-pgbackrest-query.sql
    name     | last_successful_backup |    last_archived_wal
-------------+------------------------+--------------------------
 "pgxmaster" | 2023-12-29 21:46:58+08 | 000000010000000000000011
(1 row)

同样的，pgbackrest info可以查看更详细的备份信息

10.恢复数据

常用的一些例子如下：

–delta参数可以进行增量恢复

pgbackrest --stanza=pgxmaster --delta=2024-01-01 restore

-基于时间点恢复

pgbackrest --stanza=pgxmaster --delta --log-level-console=detail --type=time "--target=2023-12-29 21:46:58+08" restore

-设置恢复目录

pgbackrest --stanza=pgxmaster --set=restore-command="cp %f /var/lib/pgsql/16/data/pg_restore/%p" restore

也可以使用 pgbackrest help restore查看可以添加的一些参数选项。如下是一个实际恢复数据的案例

创建恢复路径

-bash-4.2$ mkdir /data/pg16/data2

查看可用备份

-bash-4.2$ pgbackrest info
stanza: pgxmaster
    status: ok
    cipher: none

    db (current)
        wal archive min/max (16): 000000010000000000000008/000000010000000000000012

        full backup: 20231229-210204F
            timestamp start/stop: 2023-12-29 21:02:04+08 / 2023-12-29 21:02:08+08
            wal start/stop: 00000001000000000000000B / 00000001000000000000000B
            database size: 23MB, database backup size: 23MB
            repo1: backup set size: 3MB, backup size: 3MB

        incr backup: 20231229-210204F_20231229-211520I
            timestamp start/stop: 2023-12-29 21:15:20+08 / 2023-12-29 21:15:24+08
            wal start/stop: 00000001000000000000000D / 00000001000000000000000D
            database size: 23MB, database backup size: 8.3KB
            repo1: backup set size: 3MB, backup size: 438B
            backup reference list: 20231229-210204F

        diff backup: 20231229-210204F_20231229-214655D
            timestamp start/stop: 2023-12-29 21:46:55+08 / 2023-12-29 21:46:58+08
            wal start/stop: 00000001000000000000000F / 00000001000000000000000F
            database size: 23MB, database backup size: 24.3KB
            repo1: backup set size: 3MB, backup size: 590B
            backup reference list: 20231229-210204F

恢复命令

-bash-4.2$ pgbackrest --stanza=pgxmaster --delta --log-level-console=detail --type=time "--target=2023-12-29 21:15:24+08" restore --repo-path=/var/lib/pgsql/pgbackreset_bakdir --pg1-path=/data/pg16/data2

?

?

可以配置recovery相关参数，然后拉起数据库后，再使用pg_wal_replay_resume()函数提升为主节点。
?

11.远程备份

数据库端配置文件里的repo1-host写远程备份节点的主机名或IP，远程备份节点的配置文件里的pg1-host写数据库锁在节点的主机名或者IP，并且需要开通两个节点的SSH。设置免密。

11.1数据库端配置

pgbackrest.conf配置

vi /etc/pgbackrest/pgbackrest.conf

[global]

log-level-file=detail
repo1-host=node_back
log-path=/var/log/pgbackrest

[pgxmaster]
pg1-path=/data/pg16/data

设置数据库参数

vi postgresql.auto.conf 

archive_command = 'pgbackrest --stanza=pgxmaster archive-push %p'
archive_mode = on
listen_addresses = '*'
log_line_prefix = ''
max_wal_senders = 3
wal_level = replica

重启数据库

pg_ctl restart

11.2 备份端配置

vi   /etc/pgbackrest/pgbackrest.conf
[global]
repo1-path=/data/pg16/data
repo1-retention-full=2
log-path=/var/log/pgbackrest
#compress-type=gz

[global:archive_push]
compress-level=3

[pgxmaster]
pg1-path=/u01/postgresql/data/
pg1-host-config-path=/etc/pgbackrest 
pg1-host-port=22
pg1-host-user=postgres
pg1-host=node_database
pg1-port=5432
pg1-user=postgres

备注：
repo1-path 指定 备份和归档仓库路径
compress-level 指定压缩级别 bz2 - 9 ; gz - 6 ; lz4 - 1 ; zst - 3

11.3 创建存储空间

需要在远程控制端进行初始化，在远程控制端完成初始化后，再在数据库端进行初始化
两个节点都需要执行如下命令。

pgbackrest --stanza=pgxmaster --log-level-console=info stanza-create

11.4 备份测试

远程节点执行备份相关命令
可以参照前文部分，此处略。