暂无图片
暂无图片
2
暂无图片
暂无图片
暂无图片
首页 / postgresql12备份和恢复工具实战篇（一）：pgBackRest操作演练

postgresql12备份和恢复工具实战篇（一）：pgBackRest操作演练

原创 杨凯同学 2023-08-16
2484

一、为什么要用pgBackRest这款工具

因为PG原生的备份工具pg_basebackup 不支持增量的，面对数据量日益增加，如果每天都做全备，没有增量备份， 这样会导致每天备份的数据量越来越大，备份时间越来越长。

pgBackRest是一个开源备份工具，且是一种可靠且易于配置的备份和还原解决方案，它为所有PostgreSQL数据库提供了强大的解决方案；无论是小型项目，还是企业级的项目。

二、pgBackRest有那些功能

支持并行备份和恢复 ，解决了压缩瓶颈。备份期间压缩通常是其瓶颈所在。pgBackRest通过并行处理解决了备份期间压缩出现的瓶颈问题。
支持本地或远程操作， 远程操作需要配置 SSH。
支持全量、增量和差异备份，支持时间点还原，还支持部分还原。
支持保留策略，可以指定全量、差异备份的具体数量。
支持从standby实例备份，减小主库的IO压力，生产备份的必选项。
支持备份完整性检查，采用checksum 算法，验证备份期间复制的每个文件的校验和。在全备期间，验证所有页面的校验和，	而在差异备份和增量式备份期间，验证已更改文件的校验和。
支持异步归档，利用pgbackrest并发特性，提升备份和还原的性能，同时在处理过程中对数据进行校验和处理，保证备份和还原数据正确性。
支持加密，通过定义加密类型及密码，来保证备份数据的安全性。
支持压缩：支持gzip, bzip, lz4, zstd等压缩算法，并提供不同压缩等级。
支持S3、Azure 和 GCS 兼容的对象存储，这个也是pgbackrest 在国外流行的原因。

三、pgBackRest官网

项目官方主页： https://pgbackrest.org/
文档地址：https://pgbackrest.org/user-guide-rhel.html
Github 链接： https://github.com/pgbackrest/pgbackrest

四、pgBackRest部署

4.1.下载安装包

--目前 v2.44 是稳定版本
wget -c https://github.com/pgbackrest/pgbackrest/archive/refs/tags/release/2.44.zip
unzip 2.44.zip

4.2.安装依赖包

--如果把 pgBackRest 部署到独立的机器，需要安装 PG的依赖库(postgresql-libs)
yum -y install libxml2-devel libyaml-devel bzip2-devel postgresql-libs

4.3.配置环境变量

[root@k8s-node2 ~]# vi /etc/profile
#pg_backreset
export PGHOME=/dbms/pg12
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH:.

4.4.编译源码安装

[root@k8s-node2 ~]# cd /dbms/pgbackrest-release-2.44/
[root@k8s-node2 pgbackrest-release-2.44]# ls
CODING.md  CONTRIBUTING.md  doc  LICENSE  meson.build  meson_options.txt  README.md  src  test
[root@k8s-node2 pgbackrest-release-2.44]# cd src/
[root@k8s-node2 src]# ls
build  build.auto.h.in  command  common  config  configure  db  info  main.c  Makefile.in  meson.build  postgres  protocol  storage  version.h
[root@k8s-node2 src]# ./configure 
checking for gcc... gcc
checking whether the C compiler works... yes
checking for C compiler default output file name... a.out
checking for suffix of executables... 
checking whether we are cross compiling... no
checking for suffix of object files... o
checking whether the compiler supports GNU C... yes
checking whether gcc accepts -g... yes
checking for gcc option to enable C11 features... -std=gnu11
checking build system type... x86_64-pc-linux-gnu
checking host system type... x86_64-pc-linux-gnu
checking for pg_config... yes
checking for PQconnectdb in -lpq... yes
checking for stdio.h... yes
checking for stdlib.h... yes
checking for string.h... yes
checking for inttypes.h... yes
checking for stdint.h... yes
checking for strings.h... yes
checking for sys/stat.h... yes
checking for sys/types.h... yes
checking for unistd.h... yes
checking for libpq-fe.h... yes
checking for EVP_get_digestbyname in -lcrypto... yes
checking for SSL_new in -lssl... yes
checking for pkg-config... yes
checking for xmlSaveToBuffer in -lxml2... yes
checking for libxml/parser.h... yes
checking for yaml_parser_initialize in -lyaml... yes
checking for zlib.h... yes
checking for deflate in -lz... yes
checking for zlib.h... (cached) yes
checking for BZ2_bzCompress in -lbz2... yes
checking for bzlib.h... yes
checking for LZ4F_isError in -llz4... no
checking for ZSTD_isError in -lzstd... no
checking whether C compiler accepts -Wno-clobbered... yes
configure: creating ./config.status
config.status: creating Makefile
config.status: creating build.auto.h

[root@k8s-node2 src]# make 

[root@k8s-node2 src]# make install
install -d /usr/local/bin
install -m 755 pgbackrest /usr/local/bin
[root@k8s-node2 src]# install -d /usr/local/bin
[root@k8s-node2 src]# install -m 755 pgbackrest /usr/local/bin
--验证安装是否成功
[root@k8s-node2 src]# pgbackrest version
pgBackRest 2.44
[root@k8s-node2 src]#

五、配置pgbackrest

5.1.创建 pgbackrest 相关的目录和配置文件

--创建日志目录
mkdir -p -m 770 /var/log/pgbackrest
chown pgsql:pgsql /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 pgsql:pgsql /etc/pgbackrest/pgbackrest.conf

--创建备份目录
mkdir -p /dbms/pgbackup
chmod 750 /dbms/pgbackup
chown pgsql:pgsql /dbms/pgbackup

5.2.修改 pgbackrest 的配置文件

[root@k8s-node2 pgbackup]# vi /etc/pgbackrest/pgbackrest.conf

[pg01]
pg01-path=/dbms/pg12/pgdata
pg01-port=5432
pg01-socket-path=/tmp

[global]
repo1-path=/dbms/pgbackup
backup-user=pgsql
retention-full=2
repo1-cipher-pass=3BpN1NVJOSRNhajKAk7Fs+PE4vZeQNK/+aENDuUz4xyOsPxXW6Ug7qolr7m+vBcb
repo1-cipher-type=aes-256-cbc
start-fast=y
process-max=3
log-path=/var/log/pgbackrest

[global:archive-push]
compress-level=3

5.3.配置数据库的 archive_command 命令

vi postgresql.conf
archive_mode = on
archive_command = 'pgbackrest --stanza=pg01 archive-push %p

5.4.初始化pgbackrest

pgsql@k8s-node2:/dbms/pg12/pgdata]$ pgbackrest --stanza=pg01 --log-level-console=info stanza-create
2023-08-16 11:41:53.861 P00   INFO: stanza-create command begin 2.44: --exec-id=3269-413072fa --log-level-console=info --log-path=/var/log/pgbackrest --pg1-path=/dbms/pg12/pgdata --pg1-port=5432 --pg1-socket-path=/tmp --repo1-cipher-pass=<redacted> --repo1-cipher-type=
aes-256-cbc --repo1-path=/dbms/pgbackup --stanza=pg012023-08-16 11:41:54.477 P00   INFO: stanza-create for stanza 'pg01' on repo1
2023-08-16 11:41:54.489 P00   INFO: stanza-create command end: completed successfully (629ms)
--测试备份
[pgsql@k8s-node2:/dbms/pg12/pgdata]$pgbackrest --stanza=pg01 --log-level-console=info check
2023-08-16 11:44:25.310 P00   INFO: check command begin 2.44: --exec-id=3282-0f86307f --log-level-console=info --log-path=/var/log/pgbackrest --pg1-path=/dbms/pg12/pgdata --pg1-port=5432 --pg1-socket-path=/tmp --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-
cbc --repo1-path=/dbms/pgbackup --stanza=pg012023-08-16 11:44:25.919 P00   INFO: check repo1 configuration (primary)
2023-08-16 11:44:27.815 P00   INFO: check repo1 archive for WAL (primary)
2023-08-16 11:44:31.386 P00   INFO: WAL segment 000000020000000100000000 successfully archived to '/dbms/pgbackup/archive/pg01/12-1/0000000200000001/000000020000000100000000-40fa2fe22a6d1272f9f649c4dfdf8abc18036950.gz' on repo1

六、pgBackRest备份

6.1.全量备份

[pgsql@k8s-node2:/dbms/pgbackup/archive/pg01/12-1]$pgbackrest --stanza=pg01 --log-level-console=info backup --type=full
2023-08-16 11:45:11.974 P00   INFO: backup command begin 2.44: --exec-id=3288-610e446f --log-level-console=info --log-path=/var/log/pgbackrest --pg1-path=/dbms/pg12/pgdata --pg1-port=5432 --pg1-socket-path=/tmp --process-max=3 --repo1-cipher-pass=<redacted> --repo1-cip
her-type=aes-256-cbc --repo1-path=/dbms/pgbackup --repo1-retention-full=2 --stanza=pg01 --start-fast --type=full2023-08-16 11:45:12.687 P00   INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2023-08-16 11:45:17.197 P00   INFO: backup start archive = 000000020000000100000002, lsn = 1/80000060
2023-08-16 11:45:17.197 P00   INFO: check archive for prior segment 000000020000000100000001
WARN: exclude special file '/dbms/pg12/pgdata/.s.PGSQL.5432' from backup
2023-08-16 11:45:21.979 P00   INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2023-08-16 11:45:24.170 P00   INFO: backup stop archive = 000000020000000100000002, lsn = 1/80000138
2023-08-16 11:45:24.176 P00   INFO: check archive for segment(s) 000000020000000100000002:000000020000000100000002
2023-08-16 11:45:27.129 P00   INFO: new backup label = 20230816-114512F
2023-08-16 11:45:27.166 P00   INFO: full backup size = 39.2MB, file total = 1616
2023-08-16 11:45:27.166 P00   INFO: backup command end: completed successfully (15192ms)
2023-08-16 11:45:27.166 P00   INFO: expire command begin 2.44: --exec-id=3288-610e446f --log-level-console=info --log-path=/var/log/pgbackrest --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/dbms/pgbackup --repo1-retention-full=2 --stanza=p
g012023-08-16 11:45:27.170 P00   INFO: expire command end: completed successfully (4ms)

--查看备份
[pgsql@k8s-node2:/dbms/pgbackup/archive/pg01/12-1]$pgbackrest info
stanza: pg01
status: ok
cipher: aes-256-cbc

db (current)
    wal archive min/max (12): 000000020000000100000000/000000020000000100000002

    full backup: 20230816-114512F
        timestamp start/stop: 2023-08-16 11:45:12 / 2023-08-16 11:45:24
        wal start/stop: 000000020000000100000002 / 000000020000000100000002
        database size: 39.2MB, database backup size: 39.2MB
        repo1: backup set size: 4.7MB, backup size: 4.7MB

6.2.增量备份

--制造数据
--接下来进入数据库创建一张表： 插入100万条数据 ， 65MB左右的数据
postgres=# \c osdbadb
You are now connected to database "osdbadb" as user "postgres".
osdbadb=# create table t1(id int,info text);
CREATE TABLE
osdbadb=#  insert into t1 select n,md5(n::text) from generate_series(1,1000000) n;
INSERT 0 1000000
osdbadb=# select pg_size_pretty(pg_table_size('t1'));
 pg_size_pretty 
----------------
 65 MB
(1 row)
$pgbackrest --stanza=pg01 --log-level-console=info backup --type=incr

image.png

6.3.差异备份

--差异备份是基于 full backup 的备份集的。
--再次登录数据库，创建一张t2的表，同样数据是100万，大小在65MB左右
testdb=# create table t2(id int,info text);

testdb=# insert into t2 select n,md5(n::text) from generate_series(1,1000000) n;

testdb=# select pg_size_pretty(pg_table_size('t2'));
pg_size_pretty 
----------------
65 MB
(1 row)

进行一个差异备份：
$pgbackrest --stanza=pg01 --log-level-console=info backup --type=diff

image.png

可以看到diff backup 的备份大小在 133MB左右， 正好上次 full 全备之后 是 手动创建的表 t1（65 MB） + t2（65 MB） 的大小。

6.4.备份监控

除了命令行的查询 pgbackrest info ， pgbackrest 还提供了 SQL 查询
在官方下载的源代码包中，有两个相关的SQL文件： 路径在pgbackrest-release-2.44/doc/example
[pgsql@k8s-node2:/dbms/pgbackrest-release-2.44/doc/example]$ll
total 8
-rw-r--r-- 1 root root 871 Jan 30  2023 pgsql-pgbackrest-info.sql
-rw-r--r-- 1 root root 608 Jan 30  2023 pgsql-pgbackrest-query.sql
[pgsql@k8s-node2:/dbms/pgbackrest-release-2.44/doc/example]$cp *.sql /home/pgsql/
[pgsql@k8s-node2:/dbms/pgbackrest-release-2.44/doc/example]$cd  /home/pgsql/
[pgsql@k8s-node2:/home/pgsql]$ll
total 8
-rw-r--r-- 1 pgsql pgsql 871 Aug 16 14:44 pgsql-pgbackrest-info.sql
-rw-r--r-- 1 pgsql pgsql 608 Aug 16 14:44 pgsql-pgbackrest-query.sql

[pgsql@k8s-node2:/home/pgsql]$psql -f pgsql-pgbackrest-info.sql
CREATE SCHEMA
CREATE FUNCTION

[pgsql@k8s-node2:/home/pgsql]$psql -f pgsql-pgbackrest-query.sql
name  | last_successful_backup |    last_archived_wal     
--------+------------------------+--------------------------
"pg01" | 2023-08-16 11:56:36+08 | 000000020000000200000002
(1 row)

[pgsql@k8s-node2:/home/pgsql]$pgbackrest info
stanza: pg01
status: ok
cipher: aes-256-cbc

db (current)
    wal archive min/max (12): 000000020000000100000000/000000020000000200000002

    full backup: 20230816-114512F
        timestamp start/stop: 2023-08-16 11:45:12 / 2023-08-16 11:45:24
        wal start/stop: 000000020000000100000002 / 000000020000000100000002
        database size: 39.2MB, database backup size: 39.2MB
        repo1: backup set size: 4.7MB, backup size: 4.7MB

    incr backup: 20230816-114512F_20230816-115307I
        timestamp start/stop: 2023-08-16 11:53:07 / 2023-08-16 11:53:21
        wal start/stop: 000000020000000200000000 / 000000020000000200000000
        database size: 104.3MB, database backup size: 67.5MB
        repo1: backup set size: 30.8MB, backup size: 26.4MB
        backup reference list: 20230816-114512F

    diff backup: 20230816-114512F_20230816-115623D
        timestamp start/stop: 2023-08-16 11:56:23 / 2023-08-16 11:56:36
        wal start/stop: 000000020000000200000002 / 000000020000000200000002
        database size: 169.5MB, database backup size: 132.7MB
        repo1: backup set size: 56.8MB, backup size: 52.5MB
        backup reference list: 20230816-114512F

七、pgBackRest恢复

--准备数据
osdbadb=# select count(*) from t1;
count  
---------
1000000
(1 row)

osdbadb=# select now();
          now              
-------------------------------
2023-08-16 17:39:57.891668+08
(1 row)

osdbadb=# truncate t1;
TRUNCATE TABLE

--关闭数据
pg_ctl stop

--基于时间点恢复
pgbackrest --stanza=pg01 --delta --log-level-console=detail --type=time "--target=2023-08-16 17:39:57.891668+08" restore

--启动数据库
pg_ctl start

--验证数据
osdbadb=# select  pg_wal_replay_resume() ;
pg_wal_replay_resume 
----------------------

(1 row)


osdbadb=# select count(*) from t1;
 count  
---------
1000000
(1 row)
墨力计划postgresql
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论