一、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] pgctlstop–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] pgctlstart–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-actinotallow=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=22. 差异备份冗余策略
repo1-retention-diff=13. 归档策略
repo1-retention-diff=2
repo1-retention-full=2
3、 pgbackResth常用脚本
- 查看备份信息
pgbackrest info
- 查看备份的详细信息
1. pgbackrest --stanza=demo --set=20200205-142255F info