乐趣区

关于数据库:PostgreSQL备份工具-pgBackRest使用

jiezi

前言

pgBackRest 是一款开源的备份还原工具，指标旨在为备份和还原提供牢靠易用的备份。

个性

并行备份和还原

备份操作期间压缩通常是其瓶颈所在。pgBackRest 通过并行处理解决了备份期间压缩呈现的瓶颈问题。

本地近程操作

自定义协定容许 pgBackRest 以最小化配置通过 SSH 在本地或者近程执行备份、还原和归档。并且该程序也通过协定层提供了 PostgreSQL 查问接口，以便于必须要再近程拜访 PostgreSQL，从而保障了其平安性能。

全量，增量和差别备份

反对全量，增量和差别备份。pgBackRest 不受异步工夫影响，因而差别和增量备份齐全平安。

备份保留策略和和归档过期

反对保留策略设置能够在任意工夫创立全备和差别备份的笼罩。

备份完整性

能够计算备份中每个文件的校验和，并在还原期间从新查看。备份实现文件复制后，将期待直到每个 WAL 段所需的备份保持一致而后存储到备份仓库中。

块校验和

断点备份

流压缩和校验和

增量还原

并行异步 WAL Push 和 Get

表空间从新映射和 Link 反对

S3 存储反对和 Azure 兼容对象存储反对

加密

应用

装置

解压 

[postgres@pgserver12 tools]$ tar -zxf pgbackrest-release-2.31.tar.gz 
[postgres@pgserver12 tools]$ ls

创立必要目录 

ostgres@sungsasong ~]$ sudo mkdir -p -m 770 /var/log/pgbackrest
[postgres@sungsasong ~]$ sudo chown postgres.postgres /var/log/pgbackrest/
[postgres@sungsasong ~]$ sudo mkdir -p /etc/pgbackrest
[postgres@sungsasong ~]$ sudo mkdir -p /etc/pgbackrest/conf.d
[postgres@sungsasong ~]$ sudo touch /etc/pgbackrest/pgbackrest.conf
[postgres@sungsasong ~]$ sudo chmod 640 /etc/pgbackrest/pgbackrest.conf 
[postgres@sungsasong ~]$ sudo chown postgres.postgres -R /etc/pgbackrest/
[postgres@sungsasong ~]$ sudo mkdir /usr/bin/pgbackrest
[postgres@sungsasong ~]$ sudo chown postgres.postgres /usr/bin/pgbackrest/
[postgres@sungsasong ~]$ sudo chmod 755 /usr/bin/pgbackrest/

编译装置 

[postgres@sungsasong src]$ ./configure --prefix=/usr/bin/pgbackrest/
[postgres@sungsasong src]$ make  -j24
[postgres@sungsasong src]$ make install -j24
install -d /usr/bin/pgbackrest/bin
install -m 755 pgbackrest /usr/bin/pgbackrest/bin

命令测试 

[postgres@sungsasong src]$ /usr/bin/pgbackrest/bin/pgbackrest 
pgBackRest 2.31 - 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.

配置数据库监听和拜访及日志（可选）

[postgres@sungsasong pgbackrest-release-2.31]$ egrep "10.10" $PGDATA/pg_hba.conf
host    all             all             10.10.20.0/24           trust
[postgres@sungsasong pgbackrest-release-2.31]$ egrep -v "^#" $PGDATA/postgresql.auto.conf
logging_collector = 'on'
listen_addresses = '*'

配置 PostgreSQL 数据库数据存储目录 

[postgres@sungsasong src]$ cat >>/etc/pgbackrest/pgbackrest.conf <<EOF
> [demo]
> pgl-path=/data/pg10/pgdata
> EOF

配置环境变量 

[postgres@sungsasong ~]$ echo "export PATH=/usr/bin/pgbackrest/bin:$PATH" >> .bashrc

创立备份和归档仓库 

[postgres@sungsasong ~]$ sudo mkdir -p /pgbackrest/repos
[postgres@sungsasong ~]$ sudo chmod 750 /pgbackrest/ -R
[postgres@sungsasong ~]$ sudo chown postgres.postgres /pgbackrest/  -R

将仓库门路加载在 pgBackRest 配置文件中 

[postgres@sungsasong ~]$ cat /etc/pgbackrest/pgbackrest.conf 
[demo]
pgl-path=/data/pg10/pgdata

[global]
repol-path=/pgbackrest/repos

配置数据库归档 

[postgres@sungsasong ~]$ egrep -v "^#" $PGDATA/postgresql.auto.conf
logging_collector = 'on'
archive_mode = 'on'
archive_command = 'pgbackrest --stanza=demo archive-push %p'
listen_addresses = '*'
log_filename = 'postgresql.log'
log_line_prefix = ''max_wal_senders ='3'wal_level ='replica'

重新启动数据库 

[postgres@sungsasong ~]$ pg_ctl  restart -D $PGDATA -l /tmp/logfile
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started

可选配置归档压缩 

[postgres@sungsasong ~]$ cat /etc/pgbackrest/pgbackrest.conf 
[demo]
pgl-path=/data/pg10/pgdata

[global]
repol-path=/pgbackrest/repos

[global:archive_push]
compress-level=3

配置基于保留策略的归档 

[postgres@sungsasong ~]$ cat /etc/pgbackrest/pgbackrest.conf 
[demo]
pg1-path=/data/pg10/pgdata
pg1-host-config-path=/data/pg10/pgbackrest
pg1-host-port=22
pg1-host-user=postgres
pg1-host=sungsasong
pg1-port=10001
pg1-user=postgres

[global]
repo1-path=/pgbackrest/repos
repo1-retention-full=2

[global:archive_push]
compress-level=3

创立存储空间并查看配置 

[postgres@sungsasong ~]$ pgbackrest --stanza=demo --log-level-console=info  stanza-create 
2021-01-08 20:42:58.887 P00   INFO: stanza-create command begin 2.31: --exec-id=9283-e9744c3e --log-level-console=info --pg1-host=sungsasong --pg1-host-config-path=/data/pg10/pgbackrest --pg1-host-port=22 --pg1-host-user=postgres --pg1-path=/data/pg10/pgdata --pg1-port=10001 --pg1-user=postgres --repo1-path=/pgbackrest/repos --stanza=demo
2021-01-08 20:42:59.835 P00   INFO: stanza-create command end: completed successfully (948ms)

查看配置 

[postgres@sungsasong ~]$ pgbackrest --stanza=demo --log-level-console=info check
2021-01-08 21:41:29.851 P00   INFO: check command begin 2.31: --exec-id=21648-1862ac0d --log-level-console=info --pg1-path=/data/pg10/pgdata --pg1-port=10001 --pg1-user=postgres --repo1-path=/pgbackrest/repos --stanza=demo
2021-01-08 21:41:32.826 P00   INFO: WAL segment 000000010000000000000006 successfully archived to '/pgbackrest/repos/archive/demo/10-1/0000000100000000/000000010000000000000006-2f027934f4f35cd3983ca4b1b7b43c32ab089448.gz'
2021-01-08 21:41:32.826 P00   INFO: check command end: completed successfully (2975ms)

执行备份

默认为增量备份，增量备份将会申请一个根底全备，如果没有根底全备，增量备份将会变更到全备。

pgbackrest --stanza=demo --log-level-console=info backup
2021-01-08 21:43:55.014 P00   INFO: backup command begin 2.31: --exec-id=21661-13c54272 --log-level-console=info --pg1-path=/data/pg10/pgdata --pg1-port=10001 --pg1-user=postgres --repo1-path=/pgbackrest/repos --repo1-retention-full=2 --stanza=demo
WARN: no prior backup exists, incr backup has been changed to full
...
此处省略很多输入
...
2021-01-08 21:44:00.951 P00   INFO: full backup size = 23.3MB
2021-01-08 21:44:00.951 P00   INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
2021-01-08 21:44:01.153 P00   INFO: backup stop archive = 000000010000000000000008, lsn = 0/8000130
2021-01-08 21:44:01.154 P00   INFO: check archive for segment(s) 000000010000000000000008:000000010000000000000008
2021-01-08 21:44:01.273 P00   INFO: new backup label = 20210108-214355F
2021-01-08 21:44:01.301 P00   INFO: backup command end: completed successfully (6288ms)
2021-01-08 21:44:01.301 P00   INFO: expire command begin 2.31: --exec-id=21661-13c54272 --log-level-console=info --repo1-path=/pgbackrest/repos --repo1-retention-full=2 --stanza=demo
2021-01-08 21:44:01.304 P00   INFO: expire command end: completed successfully (3ms)

能够定义备份的类型为增量还是差别备份

应用—type 参数指定 

[postgres@sungsasong ~]$ pgbackrest --stanza=demo --log-level-console=info --type=diff backup
2021-01-08 21:47:00.964 P00   INFO: backup command begin 2.31: --exec-id=21671-d3f8f8bf --log-level-console=info --pg1-path=/data/pg10/pgdata --pg1-port=10001 --pg1-user=postgres --repo1-path=/pgbackrest/repos --repo1-retention-full=2 --stanza=demo --type=diff
2021-01-08 21:47:01.770 P00   INFO: last backup label = 20210108-214355F, version = 2.31
2021-01-08 21:47:01.770 P00   INFO: execute non-exclusive pg_start_backup(): backup begins after the next regular checkpoint completes
2021-01-08 21:47:02.309 P00   INFO: backup start archive = 00000001000000000000000A, lsn = 0/A000028
2021-01-08 21:47:03.617 P01   INFO: backup file /data/pg10/pgdata/global/pg_control (8KB, 99%) checksum 5f020e7df484269ea245041be3228673560184ef
2021-01-08 21:47:03.721 P01   INFO: backup file /data/pg10/pgdata/pg_logical/replorigin_checkpoint (8B, 100%) checksum 347fc8f2df71bd4436e38bd1516ccd7ea0d46532
2021-01-08 21:47:03.722 P00   INFO: diff backup size = 8KB
2021-01-08 21:47:03.722 P00   INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
2021-01-08 21:47:03.942 P00   INFO: backup stop archive = 00000001000000000000000A, lsn = 0/A0000F8
2021-01-08 21:47:03.943 P00   INFO: check archive for segment(s) 00000001000000000000000A:00000001000000000000000A
2021-01-08 21:47:04.062 P00   INFO: new backup label = 20210108-214355F_20210108-214701D
2021-01-08 21:47:04.092 P00   INFO: backup command end: completed successfully (3129ms)
2021-01-08 21:47:04.092 P00   INFO: expire command begin 2.31: --exec-id=21671-d3f8f8bf --log-level-console=info --repo1-path=/pgbackrest/repos --repo1-retention-full=2 --stanza=demo
2021-01-08 21:47:04.095 P00   INFO: expire command end: completed successfully (3ms)

备份信息查看 

[postgres@sungsasong ~]$ pgbackrest info
stanza: demo
    status: ok
    cipher: none

    db (current)
        wal archive min/max (10-1): 000000010000000000000001/00000001000000000000000A

        full backup: 20210108-214355F
            timestamp start/stop: 2021-01-08 21:43:55 / 2021-01-08 21:44:01
            wal start/stop: 000000010000000000000008 / 000000010000000000000008
            database size: 23.3MB, backup size: 23.3MB
            repository size: 2.7MB, repository backup size: 2.7MB

        diff backup: 20210108-214355F_20210108-214701D
            timestamp start/stop: 2021-01-08 21:47:01 / 2021-01-08 21:47:03
            wal start/stop: 00000001000000000000000A / 00000001000000000000000A
            database size: 23.3MB, backup size: 8.2KB
            repository size: 2.7MB, repository backup size: 425B
            backup reference list: 20210108-214355F

还原一个备份

模仿数据库损坏 

[postgres@sungsasong ~]$ cd $PGDATA/
[postgres@sungsasong pgdata]$ rm -rf *
#100 分正告：千万不要拿生产库执行

执行还原 

[postgres@sungsasong pgdata]$ pgbackrest --stanza=demo restore

重新启动数据库 

[postgres@sungsasong pgdata]$ pg_ctl  start -D $PGDATA -l /tmp/logfile
waiting for server to start.... done
server started
[postgres@sungsasong pgdata]$ psql
psql (10.13)
Type "help" for help.

以上就是 pgbackrest 介绍及应用。以后应用的最新版本曾经能够反对 PostgreSQL13 版本，仅仅是文档上标注反对最新到 PostgreSQL11 版本。感兴趣的同学能够上来试一下。

退出移动版