大家好，今天和大家分享一下 pgBackRest 这个备份和恢复的工具。
由于所在目前生产系统的数据量日益巨增加，之前的备份是每日凌晨做全备，没有增量备份的策略。 这样导致每天备份时间越来越长以及磁盘占有率越来越高的问题越来越明显。
之前全备采用的工具是 PG 原生自带的命令 pgbackup, 这个命令是不支持增量的， 而业界PG的备份工具又是鱼龙混杂的，种类繁多。
我们对比了pgbackrest,barman, pg_rman,bart… 等等, 测试了我们必要的功能点像是： 加密， 压缩，parallel, standby 节点支持备份，远程备份。。。
汇总如下，仅供各位参考：
最后我们决定选择了 pgBackRest： 在国外也是最受欢迎的，外面的世界上有很多印度小哥的视频。
项目官方主页： https://pgbackrest.org/
Github 链接： https://github.com/pgbackrest/pgbackrest
目前 v2.41 是稳定版本
INFRA [postgres@wqdcsrv3352 postgreSQL]# wget https://github.com/pgbackrest/pgbackrest/archive/refs/tags/release/2.41.tar.gz
安装篇
下载完成之后，解压压缩包
INFRA [postgres@wqdcsrv3352 postgreSQL]# tar -xvf 2.41.tar.gz
编译源码安装之前：我们需要安装依赖
否则会出现 ./configure 会出现错误：
configure: error: library ‘xml2’ is required
configure: error: library ‘yaml’ is required
configure: error: library ‘bz2’ is required
INFRA [postgres@wqdcsrv3352 src]# sudo yum install libxml2-devel
INFRA [postgres@wqdcsrv3352 src]# sudo yum install libyaml-devel
INFRA [postgres@wqdcsrv3352 src]# sudo yum install bzip2-devel
以上依赖的rpm包 完成之后， 再次执行 ./configure 就不会出现报错了：
... configure: creating ./config.status config.status: creating Makefile config.status: creating build.auto.h config.status: build.auto.h is unchanged
如果你是需要把pgBackRest 部署到独立的机器，那么还需要安装 PG的依赖库： postgresql-libs
sudo yum install postgresql-libs
最后我们编译一下源代码：
INFRA [postgres@wqdcsrv3352 src]# make
INFRA [postgres@wqdcsrv3352 src]# ls -lhtr | grep pgbackrest
-rwx------ 1 postgres postgres 940K Dec 17 17:20 pgbackrest
查看我们安装的pgbackrest的版本：
INFRA [postgres@wqdcsrv3352 src]# ./pgbackrest version
pgBackRest 2.41
配置 Stanza 篇
接下来，我们配置一下 pgbackrest 相关的配置文件和路径：
sudo mkdir -p -m 770 /var/log/pgbackrest sudo chown postgres:postgres /var/log/pgbackrest sudo mkdir -p /etc/pgbackrest sudo mkdir -p /etc/pgbackrest/conf.d sudo touch /etc/pgbackrest/pgbackrest.conf sudo chmod 640 /etc/pgbackrest/pgbackrest.conf sudo chown postgres:postgres /etc/pgbackrest/pgbackrest.conf
为了方便，我们把编译好的程序 pgbackrest 从 src的文件夹 拷贝到 /usr/bin 下面：
INFRA [postgres@wqdcsrv3352 src]# sudo cp pgbackrest /usr/bin/
INFRA [postgres@wqdcsrv3352 src]# sudo chmod 755 /usr/bin/pgbackrest
接下来，我们配置一下 pgbackrest 的备份配置文件 Stanza：
（这个Stanza,类似于记录了备份相关的元数据信息，类似备份的路径，备份的方式，备份集保留的时间retention 等等…）
一般需要配置到文件 /etc/pgbackrest/pgbackrest.conf 中：
1)配置备份集的基础路径：
pg1-path： PG 实例的data路径
repo1-path： 备份文件的路径
[pgdatabase]
pg1-path=/data/postgreSQL/2023
[global]
repo1-path=/data/postgreSQL/backup
2）配置数据库的archive 命令： archive_command
archive_command = 'pgbackrest --stanza=pgdatabase archive-push %p'
archive_mode = on
max_wal_senders = 3
wal_level = replica
重启数据库：
/opt/postgreSQL/pg13/bin/pg_ctl restart -D /data/postgreSQL/2023
3）配置数据库的备份集保留周期 retention：
repo1-retention-full=2 -- 保持最近2个full backup 文件
4）配置加密
repo1-cipher-pass=zWaf6XtpjIVZC5444yXB+cgFDFl7MxGlgkZSaoPvTGirhPygu4jOKOXf9LO4vjfO repo1-cipher-type=aes-256-cbc
5)初始化Stanza ,完整配置文件如下：/etc/pgbackrest/pgbackrest.conf
[pgdatabase]
pg1-path=/data/postgreSQL/2023
pg1-port=2023
pg1-socket-path=/tmp
[global]
repo1-cipher-pass=zWaf6XtpjIVZC5444yXB+cgFDFl7MxGlgkZSaoPvTGirhPygu4jOKOXf9LO4vjfO
repo1-cipher-type=aes-256-cbc
repo1-path=/data/postgreSQL/backup
repo1-retention-full=2
[global:archive-push]
compress-level=3
运行初始化命令： pgbackrest --stanza=pgdatabase --log-level-console=info stanza-create
INFRA [postgres@wqdcsrv3352 tmp]#
2022-12-17 19:22:55.187 P00 INFO: stanza-create command begin 2.41: --exec-id=128737-6064d646 --log-level-console=info --pg1-host=localhost --pg1-path=/data/postgreSQL/2023 --pg1-port=2023 --pg1-socket-path=/tmp --pg1-user=backupuser --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/data/postgreSQL/backup --stanza=pgdatabase
2022-12-17 19:22:55.905 P00 INFO: stanza-create for stanza 'pgdatabase' on repo1
2022-12-17 19:22:56.012 P00 INFO: stanza-create command end: completed successfully (826ms)
测试一下 archive 命令：
INFRA [postgres@wqdcsrv3352 pgbackrest]# pgbackrest --stanza=pgdatabase --log-level-console=info check
2022-12-17 19:50:41.097 P00 INFO: check command begin 2.41: --exec-id=17279-4a608c26 --log-level-console=info --pg1-path=/data/postgreSQL/2023 --pg1-port=2023 --pg1-socket-path=/tmp --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/data/postgreSQL/backup --stanza=pgdatabase
2022-12-17 19:50:41.704 P00 INFO: check repo1 configuration (primary)
2022-12-17 19:50:41.707 CST [17280] LOG: restore point "pgBackRest Archive Check" created at 0/20001B0
2022-12-17 19:50:41.707 CST [17280] STATEMENT: select pg_catalog.pg_create_restore_point('pgBackRest Archive Check')::text
2022-12-17 19:50:41.907 P00 INFO: check repo1 archive for WAL (primary)
2022-12-17 19:50:42.008 P00 INFO: WAL segment 000000010000000000000002 successfully archived to '/data/postgreSQL/backup/archive/pgdatabase/13-1/0000000100000000/000000010000000000000002-e3b1ac769250a4a36459c71feaad80e46679b298.gz' on repo1
2022-12-17 19:50:42.009 P00 INFO: check command end: completed successfully (913ms)
我们这个时候检查一下， WAL 日志已经归档到了我们的之前配置的 Stanza 的repo1-path的archive的文件夹下面：
INFRA [postgres@wqdcsrv3352 0000000100000000]# ls -lhtr
total 1.8M
-rw-r----- 1 postgres postgres 1.7M Dec 17 19:49 000000010000000000000001-ff76b43ff8867e8f4b1dc24d91ed720a7721bace.gz
-rw-r----- 1 postgres postgres 72K Dec 17 19:50 000000010000000000000002-e3b1ac769250a4a36459c71feaad80e46679b298.gz
-rw-r----- 1 postgres postgres 72K Dec 18 08:58 000000010000000000000003-1af26657a047d0800d21ac01e98cf8f52d895830.gz
备份和恢复测试篇
okay。 我们完成了 pgBackRest的简单配置，接下来我们要进行如下测试：
1)全备,增量，差异化增量备份（ full/incr/diff）
2)Restore 备份集操作
3)基于时间点的恢复（ PITR）
我们在进行全备测试的时候，需要配置设置如下2个参数：
start-fast=y -- 这个是强烈要求配置的，备份前主动执行 checkpoint 命令
process-max=2 --这个可以根据实际的数据库大小的情况以及机器的CPU情况进行配置，如果是主库备份，一定要保证不对业务系统造成影响
执行全备的命令很简单： pgbackrest --stanza=pgdatabase --log-level-console=info backup --type=full
INFRA [postgres@wqdcsrv3352 0000000100000000]# pgbackrest --stanza=pgdatabase --log-level-console=info backup --type=full
2022-12-18 09:54:37.692 P00 INFO: backup command begin 2.41: --exec-id=74087-80045e1f --log-level-console=info --pg1-path=/data/postgreSQL/2023 --pg1-port=2023 --pg1-socket-path=/tmp --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/data/postgreSQL/backup --repo1-retention-full=2 --stanza=pgdatabase --type=full
2022-12-18 09:54:38.398 P00 INFO: execute non-exclusive pg_start_backup(): backup begins after the next regular checkpoint completes
2022-12-18 09:54:38.899 P00 INFO: backup start archive = 000000010000000000000005, lsn = 0/5000028
2022-12-18 09:54:38.899 P00 INFO: check archive for prior segment 000000010000000000000004
2022-12-18 09:54:41.765 P00 INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
2022-12-18 09:54:41.966 P00 INFO: backup stop archive = 000000010000000000000005, lsn = 0/5000138
2022-12-18 09:54:41.968 P00 INFO: check archive for segment(s) 000000010000000000000005:000000010000000000000005
2022-12-18 09:54:41.984 P00 INFO: new backup label = 20221218-095438F
2022-12-18 09:54:42.010 P00 INFO: full backup size = 30.8MB, file total = 1226
2022-12-18 09:54:42.010 P00 INFO: backup command end: completed successfully (4319ms)
2022-12-18 09:54:42.010 P00 INFO: expire command begin 2.41: --exec-id=74087-80045e1f --log-level-console=info --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/data/postgreSQL/backup --repo1-retention-full=2 --stanza=pgdatabase
2022-12-18 09:54:42.014 P00 INFO: expire command end: completed successfully (4ms)
查看备份信息： pgbackrest info
INFRA [postgres@wqdcsrv3352 0000000100000000]# pgbackrest info
stanza: pgdatabase
status: ok
cipher: aes-256-cbc
db (current)
wal archive min/max (13): 000000010000000000000001/000000010000000000000005
full backup: 20221218-095438F
timestamp start/stop: 2022-12-18 09:54:38 / 2022-12-18 09:54:41
wal start/stop: 000000010000000000000005 / 000000010000000000000005
database size: 30.8MB, database backup size: 30.8MB
repo1: backup set size: 3.8MB, backup size: 3.8MB
增量备份
我们接下来进入数据库 创建一张表： 插入1千万条数据 ， 500MB左右的数据
postgres@[local:/tmp]:2023=#79660 create database testdb;
CREATE DATABASE
postgres@[local:/tmp]:2023=#79660 \c testdb
psql (14.4, server 13.8)
You are now connected to database "testdb" as user "postgres".
testdb@[local:/tmp]:2023=#79835 create table tab1(id int, name varchar(500));
CREATE TABLE ^
testdb@[local:/tmp]:2023=#79835 insert into tab1 select generate_series(1,10000000),'your super big bor!';
INSERT 0 10000000
testdb@[local:/tmp]:2023=#79835 select pg_size_pretty(pg_table_size('tab1'));
pg_size_pretty
----------------
498 MB
(1 row)
我们进行一个增量的备份： pgbackrest --stanza=pgdatabase --log-level-console=info backup --type=incr
我们可以看到增量备份集的大小也是 500MB 左右。 – incr backup size = 505.4MB, file total = 1518
NFRA [postgres@wqdcsrv3352 0000000100000000]# pgbackrest --stanza=pgdatabase --log-level-console=info backup --type=incr
2022-12-18 10:09:28.778 P00 INFO: backup command begin 2.41: --exec-id=84494-d097d83c --log-level-console=info --pg1-path=/data/postgreSQL/2023 --pg1-port=2023 --pg1-socket-path=/tmp --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/data/postgreSQL/backup --repo1-retention-full=2 --stanza=pgdatabase --type=incr
2022-12-18 10:09:29.494 P00 INFO: last backup label = 20221218-095438F, version = 2.41
2022-12-18 10:09:29.494 P00 INFO: execute non-exclusive pg_start_backup(): backup begins after the next regular checkpoint completes
2022-12-18 10:11:08.533 P00 INFO: backup start archive = 000000010000000000000037, lsn = 0/37000028
2022-12-18 10:11:08.533 P00 INFO: check archive for prior segment 000000010000000000000036
2022-12-18 10:11:20.532 P00 INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
2022-12-18 10:11:20.732 P00 INFO: backup stop archive = 000000010000000000000037, lsn = 0/37000308
2022-12-18 10:11:20.735 P00 INFO: check archive for segment(s) 000000010000000000000037:000000010000000000000037
2022-12-18 10:11:20.750 P00 INFO: new backup label = 20221218-095438F_20221218-100929I
2022-12-18 10:11:20.782 P00 INFO: incr backup size = 505.4MB, file total = 1518
2022-12-18 10:11:20.782 P00 INFO: backup command end: completed successfully (112005ms)
2022-12-18 10:11:20.782 P00 INFO: expire command begin 2.41: --exec-id=84494-d097d83c --log-level-console=info --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/data/postgreSQL/backup --repo1-retention-full=2 --stanza=pgdatabase
2022-12-18 10:11:20.786 P00 INFO: expire command end: completed successfully (4ms)
我们下面做一个diff 增量备份， 这个diff 的增量是基于 full backup 的备份集作为基准的， 有点类似于 cumulative incremental 的备份。
我们再次登录数据库，创建一张tab2的表，同样数据是1000万，大小在500MB左右。
testdb@[local:/tmp]:2023=#89239 create table tab2 (id int, name varchar(500));
CREATE TABLE
testdb@[local:/tmp]:2023=#89239 insert into tab2 select generate_series(1,10000000),'your super big bro!';
INSERT 0 10000000
testdb@[local:/tmp]:2023=#89239 select pg_size_pretty(pg_table_size('tab2'));
pg_size_pretty
----------------
498 MB
(1 row)
我们来进行一个 diff 类型的增量备份： pgbackrest --stanza=pgdatabase --log-level-console=info backup --type=diff
我们可以看到 cumulative incremental 的备份机大小在 1000MB左右， 正好上次full 全备之后 是 手动创建的表 tab1（498 MB） + tab2（498 MB） 的大小。
INFRA [postgres@wqdcsrv3352 0000000100000000]# pgbackrest --stanza=pgdatabase --log-level-console=info backup --type=diff
2022-12-18 10:18:54.588 P00 INFO: backup command begin 2.41: --exec-id=91119-a5d0b2a2 --log-level-console=info --pg1-path=/data/postgreSQL/2023 --pg1-port=2023 --pg1-socket-path=/tmp --process-max=2 --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/data/postgreSQL/backup --repo1-retention-full=2 --stanza=pgdatabase --start-fast --type=diff
2022-12-18 10:18:55.302 P00 INFO: last backup label = 20221218-095438F, version = 2.41
2022-12-18 10:18:55.302 P00 INFO: execute non-exclusive pg_start_backup(): backup begins after the requested immediate checkpoint completes
2022-12-18 10:18:55.903 P00 INFO: backup start archive = 000000010000000000000069, lsn = 0/690000D8
2022-12-18 10:18:55.903 P00 INFO: check archive for prior segment 000000010000000000000068
2022-12-18 10:19:07.096 P00 INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
2022-12-18 10:19:07.296 P00 INFO: backup stop archive = 000000010000000000000069, lsn = 0/690001E8
2022-12-18 10:19:07.298 P00 INFO: check archive for segment(s) 000000010000000000000069:000000010000000000000069
2022-12-18 10:19:07.317 P00 INFO: new backup label = 20221218-095438F_20221218-101855D
2022-12-18 10:19:07.352 P00 INFO: diff backup size = 1003.2MB, file total = 1521
2022-12-18 10:19:07.352 P00 INFO: backup command end: completed successfully (12765ms)
2022-12-18 10:19:07.352 P00 INFO: expire command begin 2.41: --exec-id=91119-a5d0b2a2 --log-level-console=info --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/data/postgreSQL/backup --repo1-retention-full=2 --stanza=pgdatabase
2022-12-18 10:19:07.356 P00 INFO: expire command end: completed successfully (4ms)
监控备份信息
如何查看和监控备份信息？
除了命令行的查询 pgbackrest info ， pgbackrest 还提供了数据库里面的SQL 查询
在官方下载的源代码包中，有相关的SQL文件： 路径在pgbackrest-release-2.41/doc/example
INFRA [postgres@wqdcsrv3352 example]# ls -lhtr
total 2.0K
-rw------- 1 postgres postgres 608 Sep 20 01:08 pgsql-pgbackrest-query.sql
-rw------- 1 postgres postgres 843 Sep 20 01:08 pgsql-pgbackrest-info.sql
大致的原理是copy pgbackrest 的信息到表中，从而方便用SQL语句来查询备份的信息：
-- An example of monitoring pgBackRest from within PostgreSQL
--
-- Use copy to export data from the pgBackRest info command into the jsonb
-- type so it can be queried directly by PostgreSQL.
-- Create monitor schema
create schema monitor;
-- Get pgBackRest info in JSON format
create function monitor.pgbackrest_info()
returns jsonb AS $$
declare
data jsonb;
begin
-- Create a temp table to hold the JSON data
create temp table temp_pgbackrest_data (data jsonb);
-- Copy data into the table directly from the pgBackRest info command
copy temp_pgbackrest_data (data)
from program
'pgbackrest --output=json info' (format text);
select temp_pgbackrest_data.data
into data
from temp_pgbackrest_data;
drop table temp_pgbackrest_data;
return data;
end $$ language plpgsql;
我们来执行一下这个文件 pgsql-pgbackrest-info.sql
INFRA [postgres@wqdcsrv3352 example]# psql -h /tmp -p 2023 -f pgsql-pgbackrest-info.sql
CREATE SCHEMA
CREATE FUNCTION
我们用pgsql-pgbackrest-query.sql 里面的SQL查询一下：
postgres@[local:/tmp]:2023=#71997 with stanza as
postgres-# (
postgres(# select data->'name' as name,
postgres(# data->'backup'->(
postgres(# jsonb_array_length(data->'backup') - 1) as last_backup,
postgres(# data->'archive'->(
postgres(# jsonb_array_length(data->'archive') - 1) as current_archive
postgres(# from jsonb_array_elements(monitor.pgbackrest_info()) as data
postgres(# )
postgres-# select name,
postgres-# to_timestamp(
postgres(# (last_backup->'timestamp'->>'stop')::numeric) as last_successful_backup,
postgres-# current_archive->>'max' as last_archived_wal
postgres-# from stanza;
name | last_successful_backup | last_archived_wal
--------------+------------------------+--------------------------
"pgdatabase" | 2022-12-18 10:19:07+08 | 000000010000000000000069
当然你也可以自己改造一下这个SQL，被最近一周的备份情况的信息，都列出来。
恢复篇
上面我们完成了数据库的备份操作，下面我们来测试一下如何从备份集恢复数据：
1）如何恢复database?
2）如何对实例进行基于时间点的恢复（PITR）？
1）如何恢复数据库？
我们查看一下数据库 testdb: (这个数据库是在我们之前备份的时候创建的)
You are now connected to database "testdb" as user "postgres".
testdb@[local:/tmp]:2023=#93407 \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | tab1 | table | postgres
public | tab2 | table | postgres
(2 rows)
testdb@[local:/tmp]:2023=#93407 select 'tab1',count(1) from tab1 union select 'tab2',count(1) from tab2;
?column? | count
----------+----------
tab1 | 10000000
tab2 | 10000000
(2 rows)
我们现在删除数据库 testdb:
postgres@[local:/tmp]:2023=#95090 drop database testdb;
DROP DATABASE
我们现在恢复database的操作：
首先这个操作是需要关闭数据库实例的：
INFRA [postgres@wqdcsrv3352 ~]# /opt/postgreSQL/pg13/bin/pg_ctl stop -D /data/postgreSQL/2023 -m fast
waiting for server to shut down.... done
server stopped
我们执行恢复的命令：
INFRA [postgres@wqdcsrv3352 ~]# pgbackrest --stanza=pgdatabase --delta --db-include=testdb --type=immediate --target-action=promote restore
我们再起启动数据库，并检查发现之前drop 的database testdb 已经恢复了
/opt/postgreSQL/pg13/bin/pg_ctl start -D /data/postgreSQL/2023
postgres@[local:/tmp]:2023=#105927 \c testdb
psql (14.4, server 13.8)
You are now connected to database "testdb" as user "postgres".
testdb@[local:/tmp]:2023=#106195 \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | tab1 | table | postgres
public | tab2 | table | postgres
(2 rows)
testdb@[local:/tmp]:2023=#106195 select 'tab1',count(1) from tab1 union select 'tab2',count(1) from tab2;
?column? | count
----------+----------
tab1 | 10000000
tab2 | 10000000
(2 rows)
基于时间点的恢复篇
关于基于时间点的恢复，模拟过程如下：
1）先进行一个全备
2）创建一张表 插入一条记录
3）查询一个时间点作为 PITR的时间点
4）PTIR之后插入一条记录
5）关闭数据库
6）执行 PTIR 的恢复命令
7）启动数据库
1）先进行一个全备
pgbackrest --stanza=pgdatabase --log-level-console=info backup --type=full
2）创建一张表 插入一条记录
testdb@[local:/tmp]:2023=#116841 create table pitr (id int, context varchar(100), inserttime timestamp);
CREATE TABLE
testdb@[local:/tmp]:2023=#116841 insert into pitr select 1,'Before point in time recover',current_timestamp;
INSERT 0 1
testdb@[local:/tmp]:2023=#116841 select * from pitr;
id | context | inserttime
----+------------------------------+----------------------------
1 | Before point in time recover | 2022-12-18 17:19:18.503963
(1 row)
3）查询一个时间点作为 PITR的时间点
testdb@[local:/tmp]:2023=#116841 select current_timestamp;
current_timestamp
-------------------------------
2022-12-18 17:20:04.338851+08
(1 row)
4）PTIR之后插入一条记录： ‘After point in time recover’ 这条记录会在数据库进行PITR之后 消失掉。
testdb@[local:/tmp]:2023=#116841 insert into pitr select 1,'After point in time recover',current_timestamp;
INSERT 0 1
testdb@[local:/tmp]:2023=#116841 select * from pitr;
id | context | inserttime
----+------------------------------+----------------------------
1 | Before point in time recover | 2022-12-18 17:19:18.503963
1 | After point in time recover | 2022-12-18 17:22:34.596779
(2 rows)
5）关闭数据库
/opt/postgreSQL/pg13/bin/pg_ctl stop -D /data/postgreSQL/2023 -m fast
6）执行 PTIR 的恢复命令： 恢复到时间点 --target=2022-12-18 17:20:04.338851+08
pgbackrest --stanza=pgdatabase --delta --type=time “–target=2022-12-18 17:20:04.338851+08” --log-level-console=info --target-action=promote restore
INFRA [postgres@wqdcsrv3352 ~]# pgbackrest --stanza=pgdatabase --delta --type=time "--target=2022-12-18 17:20:04.338851+08" --log-level-console=info --target-action=promote restore
2022-12-18 17:27:59.658 P00 INFO: restore command begin 2.41: --delta --exec-id=124528-8295025d --log-level-console=info --pg1-path=/data/postgreSQL/2023 --process-max=2 --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/data/postgreSQL/backup --stanza=pgdatabase --target="2022-12-18 17:20:04.338851+08" --target-action=promote --type=time
2022-12-18 17:27:59.670 P00 INFO: repo1: restore backup set 20221218-171300F, recovery will start at 2022-12-18 17:13:00
2022-12-18 17:27:59.671 P00 INFO: remove invalid files/links/paths from '/data/postgreSQL/2023'
2022-12-18 17:28:00.825 P00 INFO: write updated /data/postgreSQL/2023/postgresql.auto.conf
2022-12-18 17:28:00.828 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2022-12-18 17:28:00.828 P00 INFO: restore size = 1GB, file total = 1521
2022-12-18 17:28:00.828 P00 INFO: restore command end: completed successfully (1171ms)
7）启动数据库之前，我们检查一下 pg_data 下面：
检查文件 文件postgresql.auto.conf ， 发现我们之前设置的 PITR 的时间点 已经被自动设置好:
restore_command = 'pgbackrest --stanza=pgdatabase archive-get %f "%p"'
recovery_target_time = '2022-12-18 17:20:04.338851+08'
recovery_target_action = 'promote'
并且数据库进入了恢复模式： pgbackrest 创建了 recovery.signal
INFRA [postgres@wqdcsrv3352 2023]# ls -l | grep recovery.signal
-rw------- 1 postgres postgres 0 Dec 18 17:28 recovery.signal
我们启动一下数据库：
/opt/postgreSQL/pg13/bin/pg_ctl start -D /data/postgreSQL/2023
登录数据库检查表 pitr 里面的数据：
testdb@[local:/tmp]:2023=#129680 select * from pitr;
id | context | inserttime
----+------------------------------+----------------------------
1 | Before point in time recover | 2022-12-18 17:19:18.503963
(1 row)
我们可以看到这次基于时间点的恢复整体测试成功！
保留策略篇
备份的retention 保留时长策略设置：
目前支持备份次数和时间2种维度的保存策略：
repo1-retention-full-type=count
repo1-retention-full-type=time
我们可以设置full, diff, archive 3 种备份retention 保留时间的级别：
repo1-retention-full = 30
repo1-retention-diff = 30
repo1-retention-archive = 30
当然了如果你同时设置 archive, full 的保留时间， 并且archive 的保留时间比较短，不足以保证full 全备份恢复的话，过期的WAL也不会被移除。
相反如果你只设置了 repo1-retention-full = 30天的话， 那么30天之前备份的WAL日志同样也会标记被过期，会被移除掉。
我们测试一下，把full backup 的retention 设置为3天：
repo1-retention-full=3
我们看一下full备份集的备份时间是： 2022-12-18 （大于3天之前）
full backup: 20221218-095438F
timestamp start/stop: 2022-12-18 09:54:38 / 2022-12-18 09:54:41
wal start/stop: 000000010000000000000005 / 000000010000000000000005
database size: 30.8MB, database backup size: 30.8MB
repo1: backup set size: 3.8MB, backup size: 3.8MB
我们再次执行全备命令：pgbackrest --stanza=pgdatabase --log-level-console=info backup --type=full
我们可以看到备份的日志分为2个部分：
上半部分是备份的日志
INFRA [postgres@wqdcsrv3352 pgbackrest]# pgbackrest --stanza=pgdatabase --log-level-console=info backup --type=full
2022-12-22 18:06:47.167 P00 INFO: backup command begin 2.41: --exec-id=53964-1cadaee3 --log-level-console=info --pg1-path=/data/postgreSQL/2023 --pg1-port=2023 --pg1-socket-path=/tmp --process-max=2 --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/data/postgreSQL/backup --repo1-retention-full=3 --stanza=pgdatabase --start-fast --type=full
2022-12-22 18:06:47.876 P00 INFO: execute non-exclusive pg_start_backup(): backup begins after the requested immediate checkpoint completes
2022-12-22 18:06:48.378 P00 INFO: backup start archive = 00000003000000000000006E, lsn = 0/6E000028
2022-12-22 18:06:48.378 P00 INFO: check archive for prior segment 00000003000000000000006D
2022-12-22 18:07:01.128 P00 INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
2022-12-22 18:07:01.329 P00 INFO: backup stop archive = 00000003000000000000006E, lsn = 0/6E000138
2022-12-22 18:07:01.330 P00 INFO: check archive for segment(s) 00000003000000000000006E:00000003000000000000006E
2022-12-22 18:07:01.343 P00 INFO: new backup label = 20221222-180647F
2022-12-22 18:07:01.371 P00 INFO: full backup size = 1GB, file total = 1522
2022-12-22 18:07:01.372 P00 INFO: backup command end: completed successfully (14206ms)
下半部分是实行expire 命令，移除过期备份集和WAL日志：
2022-12-22 18:07:01.372 P00 INFO: expire command begin 2.41: --exec-id=53964-1cadaee3 --log-level-console=info --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/data/postgreSQL/backup --repo1-retention-full=3 --stanza=pgdatabase
2022-12-22 18:07:01.373 P00 INFO: repo1: expire full backup set 20221218-095438F, 20221218-095438F_20221218-100929I, 20221218-095438F_20221218-101855D
2022-12-22 18:07:01.376 P00 INFO: repo1: remove expired backup 20221218-095438F_20221218-101855D
2022-12-22 18:07:01.398 P00 INFO: repo1: remove expired backup 20221218-095438F_20221218-100929I
2022-12-22 18:07:01.409 P00 INFO: repo1: remove expired backup 20221218-095438F
2022-12-22 18:07:01.461 P00 INFO: repo1: 13-1 remove archive, start = 0000000100000000, stop = 000000020000000000000069
2022-12-22 18:07:01.462 P00 INFO: expire command end: completed successfully (90ms)
日常运维篇
备份的停止和启动：
如果我们在日常的生产运维中，要手动干预（停止|恢复）正在执行的备份日志可以：
pgbackrest stop/start
删除Stanza：
注意 stanza-delete 这个命令会删除之前所有的备份目录和文件
##############################################################################
写到最后，总结一下pg_backrest 的一些其他的亮点功能：
1）对于AWS, Azure 对象存储的支持， 这个也是pgbackrest 在国外流行的原因。
2）支持从standby实例备份，减小主库的IO压力，生产备份的必选项
3）支持standby 搭建功能， 这个功能相对于鸡肋一些，一般使用复制工具repmgr,或者原生pgbackup 构建standby 会多一些
Have a fun 😃 , Merry Xmas!
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
