pgbackrest 用于 postgresql 的备份还原

一、pgbackRest介绍

pgBackRest旨在成为一种简单，可靠的备份和还原解决方案，通过利用针对特定数据库需求进行了优化的算法，可以无缝地扩展到最大的数据库和工作负载。
pgBackRest v2.23是当前的稳定版本。发行说明在“ 发行”页面上。v1的文档可以在这里找到。v1没有计划进一步的发行版，因为v2与v1选项和存储库向后兼容。

二、pgbackRest特征

1.并行备份和还原
2.本地或远程操作
3.完整，增量和差异备份
4.备份轮换和存档到期
5.备份完整性
6.页面校验和
7.备份简历
8.流压缩和校验和
9.增量还原
10.并行，异步WAL Push＆Get
11.表空间和链接支持
12.加密
13.与PostgreSQL > = 8.3的兼容性

三、pgbackRest下载地址

https://github.com/pgbackrest/pgbackrest/releases/tag/release/2.23

四、pgbackRest安装

# cat /etc/centos-release
CentOS Linux release 7.4.1708 (Core) 
 
# yum list installed |grep -i postgresql


# su - postgres
Last login: Wed Jan 15 18:34:12 CST 2020 on pts/0
$
$ psql -c "select version();"
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)

下载安装

# yum install make gcc openssl openssl-devel libxml2 libxml2-devel lz4 lz4-devel
 
# cd /tmp
# wget https://github.com/pgbackrest/pgbackrest/archive/release/2.26.tar.gz
# tar -zxvf 2.26.tar.gz
# cd pgbackrest-release-2.26/src
# export PATH=/usr/pgsql-11/bin:$PATH
# ./configure
# make
# make install
install -d /usr/local/bin
install -m 755 pgbackrest /usr/local/bin

# pgbackrest
pgBackRest 2.26 - 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.

# 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 /etc/pgbackrest/pgbackrest.conf;

五、pgbackrest备份 支持 full、diff、incr

有全备份、增量备份、差异备份(Full, differential, and incremental backups are supported. )。

backup 准备

backup 目录

# mkdir -p /var/lib/pgsql/pgbackrest;
chmod 750 /var/lib/pgsql/pgbackrest;
chown postgres:postgres /var/lib/pgsql/pgbackrest;

backup 配置文件

# vi /etc/pgbackrest/pgbackrest.conf

[pg1-data]
pg1-path=/var/lib/pgsql/11/data

[global]
archive-async=y
process-max=2
start-fast=y
repo1-path=/var/lib/pgsql/pgbackrest
repo1-retention-full=5

[global:archive-push]
process-max=2
compress-level=3

[global:archive-get]
process-max=2

backup 归档参数

# vi /var/lib/pgsql/11/data/postgresql.conf

archive_command = 'pgbackrest --stanza=pg1-data archive-push %p'
archive_mode = on
listen_addresses = '*'
max_wal_senders = 20
wal_level = replica

# systemctl restart postgresql-11.service

backup 创建 Stanza

# su - postgres
$ pgbackrest --stanza=pg1-data --log-level-console=info stanza-create

2020-05-03 23:38:27.315 P00   INFO: stanza-create command begin 2.26: --log-level-console=info --pg1-path=/var/lib/pgsql/11/data --repo1-path=/var/lib/pgsql/pgbackrest --stanza=pg1-data
2020-05-03 23:38:28.054 P00   INFO: stanza-create command end: completed successfully (740ms)

$ tree
.
├── archive
│   └── pg1-data
│       ├── archive.info
│       └── archive.info.copy
└── backup
    └── pg1-data
        ├── backup.info
        └── backup.info.copy

4 directories, 4 files

backup 检查配置文件

$ pgbackrest --stanza=pg1-data --log-level-console=info check

2020-05-03 23:41:21.263 P00   INFO: check command begin 2.26: --log-level-console=info --pg1-path=/var/lib/pgsql/11/data --repo1-path=/var/lib/pgsql/pgbackrest --stanza=pg1-data
2020-05-03 23:41:23.272 P00   INFO: WAL segment 00000001000000010000003F successfully archived to '/var/lib/pgsql/pgbackrest/archive/pg1-data/11-1/0000000100000001/00000001000000010000003F-56c9f61d16dbdeaa21218f9d23445eb00fb10eda.gz'
2020-05-03 23:41:23.272 P00   INFO: check command end: completed successfully (2009ms)

backup full

$ pgbackrest --stanza=pg1-data --log-level-console=info --start-fast --type=full backup

2020-05-03 23:48:02.398 P00   INFO: full backup size = 141.6MB
2020-05-03 23:48:02.398 P00   INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
2020-05-03 23:48:02.603 P00   INFO: backup stop archive = 000000010000000100000041, lsn = 1/41000168
2020-05-03 23:48:02.619 P00   INFO: check archive for segment(s) 000000010000000100000041:000000010000000100000041
2020-05-03 23:48:02.676 P00   INFO: new backup label = 20200503-234738F
2020-05-03 23:48:02.760 P00   INFO: backup command end: completed successfully (25687ms)
2020-05-03 23:48:02.760 P00   INFO: expire command begin 2.26: --log-level-console=info --repo1-path=/var/lib/pgsql/pgbackrest --stanza=pg1-data
2020-05-03 23:48:02.769 P00   INFO: option 'repo1-retention-archive' is not set - archive logs will not be expired
2020-05-03 23:48:02.889 P00   INFO: expire command end: completed successfully (129ms)

backup diff

$ pgbackrest --stanza=pg1-data --log-level-console=info --start-fast --type=diff backup

2020-05-03 23:54:25.581 P00   INFO: diff backup size = 2.5MB
2020-05-03 23:54:25.581 P00   INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
2020-05-03 23:54:25.899 P00   INFO: backup stop archive = 000000010000000100000043, lsn = 1/430000F8
2020-05-03 23:54:25.904 P00   INFO: check archive for segment(s) 000000010000000100000043:000000010000000100000043
2020-05-03 23:54:25.960 P00   INFO: new backup label = 20200503-234738F_20200503-235423D
2020-05-03 23:54:26.070 P00   INFO: backup command end: completed successfully (3018ms)
2020-05-03 23:54:26.070 P00   INFO: expire command begin 2.26: --log-level-console=info --repo1-path=/var/lib/pgsql/pgbackrest --stanza=pg1-data
2020-05-03 23:54:26.078 P00   INFO: option 'repo1-retention-archive' is not set - archive logs will not be expired
2020-05-03 23:54:26.206 P00   INFO: expire command end: completed successfully (136ms)

backup incr

$ pgbackrest --stanza=pg1-data --log-level-console=info --start-fast --type=incr backup

2020-05-03 23:59:08.564 P00   INFO: incr backup size = 2.4MB
2020-05-03 23:59:08.564 P00   INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
2020-05-03 23:59:08.770 P00   INFO: backup stop archive = 000000010000000100000045, lsn = 1/450000F8
2020-05-03 23:59:08.775 P00   INFO: check archive for segment(s) 000000010000000100000045:000000010000000100000045
2020-05-03 23:59:08.840 P00   INFO: new backup label = 20200503-234738F_20200503-235906I
2020-05-03 23:59:08.932 P00   INFO: backup command end: completed successfully (3345ms)
2020-05-03 23:59:08.933 P00   INFO: expire command begin 2.26: --log-level-console=info --repo1-path=/var/lib/pgsql/pgbackrest --stanza=pg1-data
2020-05-03 23:59:08.942 P00   INFO: option 'repo1-retention-archive' is not set - archive logs will not be expired
2020-05-03 23:59:09.069 P00   INFO: expire command end: completed successfully (137ms)

$ pgbackrest info
stanza: pg1-data
    status: ok
    cipher: none

    db (current)
        wal archive min/max (11-1): 00000001000000010000003F/000000010000000100000045

        full backup: 20200503-234738F
            timestamp start/stop: 2020-05-03 23:47:38 / 2020-05-03 23:48:02
            wal start/stop: 000000010000000100000041 / 000000010000000100000041
            database size: 141.6MB, backup size: 141.6MB
            repository size: 15.1MB, repository backup size: 15.1MB

        diff backup: 20200503-234738F_20200503-235423D
            timestamp start/stop: 2020-05-03 23:54:23 / 2020-05-03 23:54:25
            wal start/stop: 000000010000000100000043 / 000000010000000100000043
            database size: 141.6MB, backup size: 2.5MB
            repository size: 15.1MB, repository backup size: 269.5KB
            backup reference list: 20200503-234738F

        incr backup: 20200503-234738F_20200503-235906I
            timestamp start/stop: 2020-05-03 23:59:06 / 2020-05-03 23:59:08
            wal start/stop: 000000010000000100000045 / 000000010000000100000045
            database size: 141.6MB, backup size: 2.4MB
            repository size: 15.1MB, repository backup size: 230.7KB
            backup reference list: 20200503-234738F, 20200503-234738F_20200503-235423D

 

 pgbackRest备份案例如下：

1. 执行全量备份

[cddb@cddb-node1 ~]$pgbackrest --stanza=demo --log-level-console=info backup
P00 WARN: no prior backup exists, incr backup has been changed to full

疑问：表空间所在目录怎么备份？

2. 执行差异备份

[cddb@cddb-node1 ~]$ pgbackrest --stanza=demo --log-level-console=info --type=diff backup

3. 执行增量备份

[cddb@cddb-node1 ~]$ pgbackrest --stanza=demo --log-level-console=info --type=incr backup

4. Fast Start 备份选项

start-fast=y
[cddb@cddb-node1 ~]$ vi /etc/pgbackrest/pgbackrest.conf

5. 并行备份

[demo]
pg1-path=/cddb5.0/data2/
pg1-port=7321
[global]
repo1-cipher-pass=zWaf6XtpjIVZC5444yXB+cgFDFl7MxGlgkZSaoPvTGirhPygu4jOKOXf9LO4vjfO
repo1-cipher-type=aes-256-cbc
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
start-fast=y
process-max = 3
[global:archive-push]
compress-level=3

说明： 开启三个并行，备份时间确实减少的5分钟

6. 查看备份信息

[cddb@cddb-node1 ~]$ pgbackrest info

7. 配置自动调度

#m h dom mon dow command
30 06 * * 0 pgbackrest --type=full --stanza=demo backup
30 06 * * 1-6 pgbackrest --type=diff --stanza=demo backup

 

六、restore

restore

# systemctl stop postgresql-11.service
# su - postgres
$ cd /var/lib/pgsql/11/data
$ rm -rf ./*

$ pgbackrest --stanza=pg1-data --log-level-console=info --delta \
       --type=time --target="2020-05-04 01:00:00.507654+08" \
       --target-action=pause restore

$ cat recovery.conf
# Recovery settings generated by pgBackRest restore on 2020-05-04 01:28:51
restore_command = 'pgbackrest --stanza=pg1-data archive-get %f "%p"'
recovery_target_time = '2020-05-04 01:00:00.507654+08'

给 recovery.conf 添加 recovery_target_action = ‘pause’

# systemctl start postgresql-11.service
# 
# ps -ef|grep -i post
postgres 11558     1  0 01:40 ?        00:00:00 /usr/pgsql-11/bin/postmaster -D /var/lib/pgsql/11/data/
postgres 11564 11558  0 01:40 ?        00:00:00 postgres: logger   
postgres 11569 11558  0 01:40 ?        00:00:00 postgres: checkpointer   
postgres 11570 11558  0 01:40 ?        00:00:00 postgres: background writer   
postgres 11572 11558  0 01:40 ?        00:00:00 postgres: stats collector   
postgres 11581 11558  0 01:40 ?        00:00:00 postgres: walwriter   
postgres 11582 11558  0 01:40 ?        00:00:00 postgres: autovacuum launcher   
postgres 11583 11558  0 01:40 ?        00:00:00 postgres: archiver   last was 00000003.history
postgres 11584 11558  0 01:40 ?        00:00:00 postgres: logical replication launcher   

pgbackRest恢复案例如下：

1. 查看现有备份

[cddb@cddb-node1 ~]$ pgbackrest info
stanza: demo
status: ok
cipher: aes-256-cbc
db (current)
wal archive min/max (11-1): 0000000A00000001000000F8/0000000A00000001000000FC
full backup: 20200204-163558F
timestamp start/stop: 2020-02-04 16:35:58 / 2020-02-04 16:46:14
wal start/stop: 0000000A00000001000000F8 / 0000000A00000001000000F8
database size: 3.9GB, backup size: 3.9GB
repository size: 810.3MB, repository backup size: 810.3MB
full backup: 20200205-142255F
timestamp start/stop: 2020-02-05 14:22:55 / 2020-02-05 14:30:50
wal start/stop: 0000000A00000001000000FB / 0000000A00000001000000FB
database size: 3.9GB, backup size: 3.9GB
repository size: 810.3MB, repository backup size: 810.3MB

2. 准备恢复环境

数据目录：/cddb5.0/data2
表空间目录：/cddb5.0/tablespace
关闭数据库
pg_ctl stop –D /cddb5.0/data2
删除data2/和tablespace目录
rm –f /cddb5.0/data2/*
rm –f /cddb5.0/tablespace

3. 执行完全恢复操作

[cddb@cddb-node1 cddb5.0]$ pgbackrest restore --stanza=demo --delta --log-level-console=detail
2020-02-05 14:58:45.779 P00 DETAIL: sync path ‘/cddb5.0/data2/global’
2020-02-05 14:58:46.124 P00 INFO: restore command end: completed successfully (226917ms)

4. 执行部分数据库恢复操作

创建测试数据库
[cddb@cddb-node1 cddb5.0]$ psql -d SWDS -p 7321 -U swds2014
SWDS=# create database test1;
SWDS=# create database test2;
执行增量备份
[cddb@cddb-node1 cddb5.0]$ pgbackrest --stanza=demo --type=incr backup
创建测试表
test1=# create table test1_table(id int);
test1=# insert into test1_table values(1);
test1=# \c test2
test2=# create table test2_table(id int);
test2=# insert into test2_table values(2);

恢复数据库test2
[cddb@cddb-node1 cddb5.0] p g c t l s t o p – D / c d d b 5.0 / d a t a 2 [ c d d b @ c d d b − n o d e 1 c d d b 5.0 ] pg_ctl stop –D /cddb5.0/data2 [cddb@cddb-node1 cddb5.0] pgc​tlstop–D/cddb5.0/data2[cddb@cddb−node1cddb5.0] pgbackrest --stanza=demo --delta --db-include=test2 restore
[cddb@cddb-node1 cddb5.0] p g c t l s t a r t – D / c d d b 5.0 / d a t a 2 [ c d d b @ c d d b − n o d e 1 c d d b 5.0 ] pg_ctl start –D /cddb5.0/data2 [cddb@cddb-node1 cddb5.0] pgc​tlstart–D/cddb5.0/data2[cddb@cddb−node1cddb5.0] psql -d SWDS -p 7321 -U swds2014
psql: FATAL: relation mapping file “base/16391/pg_filenode.map” contains invalid data
[cddb@cddb-node1 cddb5.0]$ psql -d SWDS -p 7321 -U swds2014
psql: FATAL: relation mapping file “base/16391/pg_filenode.map” contains invalid data
test2=# select * from test2_table;
id
2
说明：test1,swds,nyc_data三个数据库无法访问，pg自带的数据库可以访问

5. 基于时间点的恢复操作

准备测试数据
test2=# create table test(message text);
CREATE TABLE
test2=# insert into test values(‘aaaa’);
INSERT 0 1
test2=# select current_timestamp;
current_timestamp
2020-02-05 15:58:01.852901+08
执行差异备份
[cddb@cddb-node1 cddb5.0]$ pgbackrest --stanza=demo --type=diff --log-level-console=detail backup

删除test
test2=# drop table test2_table ;
执行基于时间点的恢复test表
[cddb@cddb-node1 cddb5.0]$ pg_ctl stop -D /cddb5.0/data2/
 
[cddb@cddb-node1 cddb5.0]$ mv /cddb5.0/data2/recovery.done /cddb5.0/data2/recovery.conf
[cddb@cddb-node1 cddb5.0]$ vi /cddb5.0/data2/recovery.conf
restore_command = ‘pgbackrest --stanza=demo archive-get %f “%p”’
recovery_target_time = ‘2020-02-05 15:58:01.852901+08’
recovery_target_action = ‘promote’
[cddb@cddb-node1 cddb5.0]$ pgbackrest --stanza=demo --delta --type=time --target=“2020-02-05 15:58:01.852901+08” --target-action=promote restore
2020-02-05 16:33:32.955 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2020-02-05 16:33:32.955 P00 DETAIL: sync path ‘/cddb5.0/data2/global’
2020-02-05 16:33:33.307 P00 INFO: restore command end: completed successfully (85946ms)
[cddb@cddb-node1 cddb5.0]$ pg_ctl start -D /cddb5.0/data2/
查看恢复情况
[cddb@cddb-node1 cddb5.0]$ psql –d test2 -U swds2014
test2=# \d
List of relations
Schema | Name | Type | Owner
--------±------------±------±---------
public | test | table | swds2014
public | test2_table | table | swds2014
test2=# select * from test2_table ;
id
2
结果：恢复成功

七、配置监控、冗余策略

1、 pgbackRest配置监控

[root@cddb-node0 example]# pwd
/opt/pgbackrest-release-2.23/doc/example
[root@cddb-node0 example]# ls
pgsql-pgbackrest-info.sql pgsql-pgbackrest-query.sql
[root@cddb-node0 example]# chown -R cddb:cddb *
[root@cddb-node0 example]# chmod -R 755 *
[cddb@cddb-node0 example]$ scp pgsql-pgbackrest-* cddb-node1:/home/cddb/
pgsql-pgbackrest-info.sql
pgsql-pgbackrest-query.sql
[cddb@cddb-node1 ~]$ psql -d SWDS -U swds2014 -p 7321 -f pgsql-pgbackrest-info.sql
CREATE SCHEMA
CREATE FUNCTION
[cddb@cddb-node1 ~]$ psql -d SWDS -U swds2014 -p 7321 -f pgsql-pgbackrest-query.sql
name | last_successful_backup | last_archived_wal
--------±-----------------------±-------------------------
“demo” | 2020-02-04 15:40:46+08 | 0000000A00000001000000F2
(1 row)

2、 pgbackRest冗余策略

1. 全量冗余策略
   repo1-retention-full=2
2. 差异备份冗余策略
   repo1-retention-diff=1
3. 归档策略
   repo1-retention-diff=2
   repo1-retention-full=2

3、 pgbackResth常用脚本

1. 查看备份信息
     pgbackrest info
    
2. 查看备份的详细信息
     pgbackrest --stanza=demo --set=20200205-142255F info