PostgreSQL备份工具 pgBackRest使用




  • 前言

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

    特性

    并行备份和还原

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

    本地远程操作

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

    全量,增量和差异备份

    支持全量,增量和差异备份。pgBackRest不受异步时间影响,因此差异和增量备份完全安全。

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

    支持保留策略设置可以在任意时间创建全备和差异备份的覆盖。

    备份完整性

    可以计算备份中每个文件的校验和,并在还原期间重新检查。备份完成文件复制后,将等待直到每个WAL段所需的备份保持一致然后存储到备份仓库中。

    块校验和

    断点备份

    流压缩和校验和

    增量还原

    并行异步WAL Push和Get

    表空间重新映射和Link支持

    S3存储支持和Azure兼容对象存储支持

    加密

    使用

    安装

    解压

    <code class="language-sql hljs">[postgres@pgserver12 tools]$ tar -zxf pgbackrest-release-2.31.tar.gz 
    [postgres@pgserver12 tools]$ ls

    创建必要目录

    <code class="language-sql hljs">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/

    编译安装

    <code class="language-sql hljs">[postgres@sungsasong src]$ ./configure <span class="hljs-comment">--prefix=/usr/bin/pgbackrest/</span>
    [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

    命令测试

    <code class="language-sql hljs">[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.

    配置数据库监听和访问及日志(可选)

    <code class="language-sql hljs">[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数据库数据存储目录

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

    配置环境变量

    <code class="language-sql hljs">[postgres@sungsasong ~]$ echo "export PATH=/usr/bin/pgbackrest/bin:\$PATH" >> .bashrc </code></pre>
    

     

    创建备份和归档仓库

     

     

     

     

    <code class="language-sql hljs">[postgres@sungsasong ~]$ sudo mkdir -p /pgbackrest/repos
    [postgres@sungsasong ~]$ sudo chmod 750 /pgbackrest/ -R
    [postgres@sungsasong ~]$ sudo chown postgres.postgres /pgbackrest/  -R

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

    <code class="language-sql hljs">[postgres@sungsasong ~]$ cat /etc/pgbackrest/pgbackrest.conf 
    [demo]
    pgl-path=/data/pg10/pgdata
    
    [global]
    repol-path=/pgbackrest/repos

    配置数据库归档

    <code class="language-sql hljs">[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'

    重新启动数据库

    <code class="language-sql hljs">[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

    可选配置归档压缩

    <code class="language-sql hljs">[postgres@sungsasong ~]$ cat /etc/pgbackrest/pgbackrest.conf 
    [demo]
    pgl-path=/data/pg10/pgdata
    
    [global]
    repol-path=/pgbackrest/repos
    
    [global:archive_push]
    compress-level=3

    配置基于保留策略的归档

    <code class="language-sql hljs">[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

    创建存储空间并检查配置

    <code class="language-sql hljs">[postgres@sungsasong ~]$ pgbackrest <span class="hljs-comment">--stanza=demo --log-level-console=info  stanza-create </span>
    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)

    检查配置

    <code class="language-sql hljs">[postgres@sungsasong ~]$ pgbackrest <span class="hljs-comment">--stanza=demo --log-level-console=info check</span>
    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)

    执行备份

    默认为增量备份,增量备份将会请求一个基础全备,如果没有基础全备,增量备份将会变更到全备。
    <code class="language-sql hljs">pgbackrest <span class="hljs-comment">--stanza=demo --log-level-console=info backup</span>
    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参数指定
    <code class="language-sql hljs">[postgres@sungsasong ~]$ pgbackrest <span class="hljs-comment">--stanza=demo --log-level-console=info --type=diff backup</span>
    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)

    备份信息查看

    <code class="language-sql hljs">[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

    还原一个备份

    模拟数据库损坏
    <code class="language-sql hljs">[postgres@sungsasong ~]$ cd $PGDATA/
    [postgres@sungsasong pgdata]$ rm -rf *
    #100分警告:千万不要拿生产库执行

    执行还原

    <code class="language-sql hljs">[postgres@sungsasong pgdata]$ pgbackrest <span class="hljs-comment">--stanza=demo restore</span></code></pre>
    

     

    重新启动数据库

     

     

     

     

    <code class="language-sql hljs">[postgres@sungsasong pgdata]$ pg_ctl  <span class="hljs-operator"><span class="hljs-keyword">start</span> -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版本。感兴趣的同学可以下去试一下。

    Speak Your Mind

    *