前言

上次分享了barman备份的安装部署过程，这次将继续分享一下barman备份恢复工具的使用过程。
barman是目前postgreSQL数据库的比较成熟的一款备份工具，是基于postgresql的一款物理备份工具。与oracle数据库中的rman较为类似。
其简单的工作原理即为：
物理全备+归档备份（WAL日志备份），从而达到任意时间点的恢复。即恢复全备后，通过指定WAL日志的时间点、LSN等即可恢复到数据库的任意时间点。
barman备份主要分为三类：
1.仅流复制；
2.ssh/rsync;
3.流复制+ssh/rsync

上一次，已经完成了barman工具的安装部署工作。本文中，将对postgresql和barman进行配置并测试备份恢复。

一、postgreSQL配置

参数配置（具体参数可以根据实际情况配置）：

listen_addresses = '*'
port = 18801
max_connections = 100 
superuser_reserved_connections = 3 
tcp_keepalives_idle = 7200
tcp_keepalives_interval = 130
tcp_keepalives_count = 3
shared_buffers = 256MB
work_mem = 16MB
maintenance_work_mem = 128MB
wal_level = logical
full_page_writes = on
max_wal_size = 1GB
min_wal_size = 80MB
wal_keep_segments = 32
logging_collector = on
log_directory = '/PgData/pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'                                                                                                
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
log_min_messages = info
log_min_error_statement = info

shared_preload_libraries = 'pg_stat_statements' #加载pg_stat_statements模块
track_io_timing = on              #跟踪IO消耗的时间
pg_stat_statements.max = 10000    #最多保留多少条统计信息，通过LRU算法，覆盖老的记录。
pg_stat_statements.track = all    #all：所有SQL包括函数内嵌套的SQL,top：直接执行的SQL(函数内的sql不被跟踪),none：不跟踪
pg_stat_statements.track_utility=on # 是否跟踪非DML语句 (例如DDL，DCL)，on表示跟踪, off表示不跟踪
pg_stat_statements.save = on      # 重启后是否保留统计信息 
#track_activity_query_size = 2048  #设置单条SQL的最长长度，超过被截断显示
复制

修改pg_hba.conf

host    all             barman          0.0.0.0/0               trust
host    replication     barmstr       0.0.0.0/0       trust
复制

postgresql数据库上创建用户（barman需要superuser权限，barmanStr需要replication权限）：

create user barman superuser password 'barman';
create user barmanStr replication password 'barman';
复制

重启数据库：

pg_ctl -D /PgData start
复制

连接尝试：

在备份服务器上尝试连接验证：

psql -h 192.168.138.141 -U barman -d wangxin -p 18801
psql -h 192.168.138.141 -U barmstr -d wangxin -p 18801
复制

二、barman配置：

创建用户：

useradd barman
复制

修改barman的配置文件：

vi /etc/barman.conf

[barman]
barman_user = barman
configuration_files_directory = /etc/barman.d
;指定barman_home，即备份文件的存放路径
barman_home = /home/barman
log_file = /home/barman/barman.log
log_level = INFO
compression = gzip
parallel_jobs = 1
immediate_checkpoint = true
basebackup_retry_times = 3
basebackup_retry_sleep = 60
check_timeout = 120
retention_policy = REDUNDANCY 2
retention_policy = RECOVERY WINDOW OF 4 WEEKS
复制

修改服务配置文件：

cp streaming-server.conf-template pgsql-streaming.conf
vi pgsql-streaming.conf
复制

内容如下：

[pgsql_streaming]
description =  "pgsql11-streaming (Streaming-Only)"
conninfo = host=pgsql11 user=barman dbname=wangxin port=18801
streaming_conninfo = host=pgsql11 user=barmanstr port=18801
backup_method = postgres
streaming_backup_name = barman_streaming_backup
streaming_archiver = on
archiver = on
slot_name = pgsql_streaming
create_slot = auto
streaming_archiver_name = barman_receive_wal
streaming_archiver_batch_size = 50
path_prefix = "/pg_client/bin"
复制

配置服务端和备份端的ssh免密登录：
pgsql:

ssh-keygen -t rsa

cat id_rsa.pub >> authorized_keys

scp authorized_keys >> barman@barman:/home/barman/.ssh/
复制

barman:

ssh-keygen -t rsa

cat id_rsa.pub >> authorized_keys

scp authorized_keys >> pgsql@pgsql11:/home/pgsql/.ssh/
复制

pgsql:

chmod -R 600 /home/pgsql/.ssh/authorized_keys
复制

barman:

chmod -R 600 /home/barman/.ssh/authorized_keys

复制

创建slot槽：

barman receive-wal --create-slot pgsql_streaming
复制

进行check检查：

barman check pgsql_streaming
复制

查看连接服务的信息

[barman@barman ~]$ barman show-server pgsql_streaming
Server pgsql_streaming:
        active: True
        archive_command: (disabled)
        archive_mode: off
        archive_timeout: 0
        archived_count: 0
        archiver: True
        archiver_batch_size: 0
        backup_directory: /home/barman/pgsql_streaming
        backup_method: postgres
        backup_options: BackupOptions({'concurrent_backup'})
        bandwidth_limit: None
        barman_home: /home/barman
        barman_lock_directory: /home/barman
        basebackup_retry_sleep: 60
        basebackup_retry_times: 3
        basebackups_directory: /home/barman/pgsql_streaming/base
        check_timeout: 120
        checkpoint_timeout: 300
        compression: gzip
        config_file: /PgData/postgresql.conf
        connection_error: None
        conninfo: host=pgsql11 user=barman dbname=wangxin port=18801
        create_slot: auto
        current_archived_wals_per_second: 0.0
        current_lsn: 0/669C3780
        current_size: 1134777182
        current_xlog: 000000010000000000000066
        custom_compression_filter: None
        custom_decompression_filter: None
        data_checksums: off
        data_directory: /PgData
        description: pgsql11-streaming (Streaming-Only)
        disabled: False
        errors_directory: /home/barman/pgsql_streaming/errors
        failed_count: 0
        has_backup_privileges: True
        hba_file: /PgData/pg_hba.conf
        hot_standby: on
        ident_file: /PgData/pg_ident.conf
        immediate_checkpoint: True
        **incoming_wals_directory: /home/barman/pgsql_streaming/incoming**
        is_archiving: False
        is_in_recovery: False
        is_superuser: True
        last_archived_time: None
        last_archived_wal: None
        last_backup_maximum_age: None
        last_failed_time: None
        last_failed_wal: None
        max_incoming_wals_queue: None
        max_replication_slots: 10
        max_wal_senders: 10
        minimum_redundancy: 0
        msg_list: []
        name: pgsql_streaming
        network_compression: False
        parallel_jobs: 1
        passive_node: False
        path_prefix: /pg_client/bin
        pg_basebackup_bwlimit: True
        pg_basebackup_compatible: True
        pg_basebackup_installed: True
        pg_basebackup_path: /pg_client/bin/pg_basebackup
        pg_basebackup_tbls_mapping: True
        pg_basebackup_version: 11.4
        pg_receivexlog_compatible: True
        pg_receivexlog_installed: True
        pg_receivexlog_path: /pg_client/bin/pg_receivewal
        pg_receivexlog_supports_slots: True
        pg_receivexlog_synchronous: False
        pg_receivexlog_version: 11.4
        pgespresso_installed: False
        post_archive_retry_script: None
        post_archive_script: None
        post_backup_retry_script: None
        post_backup_script: None
        post_delete_retry_script: None
        post_delete_script: None
        post_recovery_retry_script: None
        post_recovery_script: None
        post_wal_delete_retry_script: None
        post_wal_delete_script: None
        postgres_systemid: 6901481653023568481
        pre_archive_retry_script: None
        pre_archive_script: None
        pre_backup_retry_script: None
        pre_backup_script: None
        pre_delete_retry_script: None
        pre_delete_script: None
        pre_recovery_retry_script: None
        pre_recovery_script: None
        pre_wal_delete_retry_script: None
        pre_wal_delete_script: None
        primary_ssh_command: None
        recovery_options: RecoveryOptions()
        replication_slot: Record(slot_name='pgsql_streaming', active=False, restart_lsn=None)
        replication_slot_support: True
        retention_policy: RECOVERY WINDOW OF 4 WEEKS
        retention_policy_mode: auto
        reuse_backup: None
        server_txt_version: 11.4
        slot_name: pgsql_streaming
        ssh_command: None
        stats_reset: 2020-12-02 10:33:56.983254+08:00
        streaming: True
        streaming_archiver: True
        streaming_archiver_batch_size: 50
        streaming_archiver_name: barman_receive_wal
        streaming_backup_name: barman_streaming_backup
        streaming_conninfo: host=pgsql11 user=barmanstr port=18801
        streaming_supported: True
        streaming_systemid: 6901481653023568481
        streaming_wals_directory: /home/barman/pgsql_streaming/streaming
        synchronous_standby_names: ['']
        tablespace_bandwidth_limit: None
        timeline: 1
        wal_compression: off
        wal_keep_segments: 0
        wal_level: logical
        wal_retention_policy: MAIN
        wals_directory: /home/barman/pgsql_streaming/wals
        xlog_segment_size: 16777216
        xlogpos: 0/669C3780
复制

检查barman是否满足备份要求：

[barman@barman ~]$ barman check pgsql_streaming    
Server pgsql_streaming:
        WAL archive: FAILED (please make sure WAL shipping is setup)
        PostgreSQL: OK
        superuser or standard user with backup privileges: OK
        PostgreSQL streaming: OK
        wal_level: OK
        replication slot: FAILED (slot 'pgsql_streaming' not initialised: is 'receive-wal' running?)
        directories: OK
        retention policy settings: OK
        backup maximum age: OK (no last_backup_maximum_age provided)
        compression settings: OK
        failed backups: OK (there are 0 failed backups)
        minimum redundancy requirements: OK (have 0 backups, expected at least 0)
        pg_basebackup: OK
        pg_basebackup compatible: OK
        pg_basebackup supports tablespaces mapping: OK
        systemid coherence: OK (no system Id stored on disk)
        pg_receivexlog: OK
        pg_receivexlog compatible: OK
        receive-wal running: FAILED (See the Barman log file for more details)
        archive_mode: FAILED (please set it to 'on' or 'always')
        archive_command: FAILED (please set it accordingly to documentation)
        archiver errors: OK

复制

可以关注到一个错误：
replication slot: FAILED (slot ‘pgsql_streaming’ not initialised: is ‘receive-wal’ running?)

解决该错误，我们需要执行以下命令，让barman服务器来强制接受源端的wal日志：

nohup barman receive-wal pgsql_streaming &
复制

再次进行check：

[barman@barman ~]$ barman check pgsql_streaming
Server pgsql_streaming:
        WAL archive: FAILED (please make sure WAL shipping is setup)
        PostgreSQL: OK
        superuser or standard user with backup privileges: OK
        PostgreSQL streaming: OK
        wal_level: OK
        replication slot: OK
        directories: OK
        retention policy settings: OK
        backup maximum age: OK (no last_backup_maximum_age provided)
        compression settings: OK
        failed backups: OK (there are 0 failed backups)
        minimum redundancy requirements: OK (have 0 backups, expected at least 0)
        pg_basebackup: OK
        pg_basebackup compatible: OK
        pg_basebackup supports tablespaces mapping: OK
        systemid coherence: OK (no system Id stored on disk)
        pg_receivexlog: OK
        pg_receivexlog compatible: OK
        receive-wal running: OK
        archive_mode: FAILED (please set it to 'on' or 'always')
        archive_command: FAILED (please set it accordingly to documentation)
        archiver errors: OK
复制

此时，可以发现，错误只有以下内容：
WAL archive: FAILED (please make sure WAL shipping is setup)
archive_mode: FAILED (please set it to ‘on’ or ‘always’)
archive_command: FAILED (please set it accordingly to documentation)

但是这三个错误是通过ssh/rsync的方式，如果我们只用streaming的方式可以忽略，然后直接发起备份，当然如果想要处理这些问题，可以这样做：

1.问题一

执行barman cron:

Starting WAL archiving for server pgsql_streaming

执行barman switch-xlog

barman switch-xlog pgsql_streaming
复制

2.问题二

根据提示在server端设置archive_mod和archive_command参数：

vi postgresql.conf

archive_mod = on
archive_command = 'rsync -a %p barman@barman:/home/barman/pgsql_streaming/incoming/%f' #该路径就是我们刚在barman show-server pg_streaming中查询的
复制

重启数据库
此时再次执行check可以看到状态都正常了：
[root@barman barman.d]# barman check pgsql_streaming
Server pgsql_streaming:
PostgreSQL: OK
superuser or standard user with backup privileges: OK
PostgreSQL streaming: OK
wal_level: OK
replication slot: OK
directories: OK
retention policy settings: OK
backup maximum age: OK (no last_backup_maximum_age provided)
compression settings: OK
failed backups: OK (there are 0 failed backups)
minimum redundancy requirements: OK (have 0 backups, expected at least 0)
pg_basebackup: OK
pg_basebackup compatible: OK
pg_basebackup supports tablespaces mapping: OK
systemid coherence: OK (no system Id stored on disk)
pg_receivexlog: OK
pg_receivexlog compatible: OK
receive-wal running: OK
archive_mode: OK
archive_command: OK
continuous archiving: OK
archiver errors: OK

全部正常以后，我们则可以开始进行备份。

三、barman备份：
进行备份：

barman backup pgsql_streaming
复制

查看备份：

barman list-backup pgsql_streaming
复制

查看备份详细信息

barman show-backup pgsql_streaming 20201204T164310

Backup 20201204T164310:
  Server Name            : pgsql_streaming
  System Id              : 6901481653023568481
  Status                 : DONE
  PostgreSQL Version     : 110004
  PGDATA directory       : /PgData

  Base backup information:
    Disk usage           : 1.1 GiB (1.1 GiB with WALs)
    Incremental size     : 1.1 GiB (-0.00%)
    Timeline             : 1
    Begin WAL            : 00000001000000000000006F
    End WAL              : 00000001000000000000006F
    WAL number           : 1
    WAL compression ratio: 99.90%
    Begin time           : 2020-12-04 16:43:10+08:00
    End time             : 2020-12-04 16:43:39.799061+08:00
    Copy time            : 29 seconds
    Estimated throughput : 37.4 MiB/s
    Begin Offset         : 40
    End Offset           : 0
    Begin LSN           : 0/6F000028
    End LSN             : 0/70000000

  WAL information:
    No of files          : 0
    Disk usage           : 0 B
    Last available       : 00000001000000000000006F

  Catalog information:
    Retention Policy     : VALID
    Previous Backup      : - (this is the oldest base backup)
    Next Backup          : - (this is the latest base backup)
复制

实时查看备份情况:

[root@barman .ssh]# barman status pgsql_streaming
Server pgsql_streaming:
        Description: pgsql11-streaming (Streaming-Only)
        Active: True
        Disabled: False
        PostgreSQL version: 11.4
        Cluster state: in production
        pgespresso extension: Not available
        Current data size: 1.1 GiB
        PostgreSQL Data directory: /PgData
        Current WAL segment: 000000010000000000000073
        PostgreSQL 'archive_command' setting: rsync -a %p barman@barman:/home/barman/pgsql_streaming/incoming/%f
        Last archived WAL: 000000010000000000000072, at Fri Dec  4 16:50:13 2020
        Failures of WAL archiver: 0
        Server WAL archiving rate: 0.26/hour
        Passive node: False
        Retention policies: enforced (mode: auto, retention: RECOVERY WINDOW OF 4 WEEKS, WAL retention: MAIN)
        No. of available backups: 1
        First available backup: 20201204T164310
        Last available backup: 20201204T164310
        Minimum redundancy requirements: satisfied (1/0)
复制

说明：
配置文件中的barman_home，即为备份存放路径
打开barman_home下对应的备份服务下，可以看到以下目录：
drwxrwxr-x. 2 barman barman 6 Dec 4 15:36 errors
-rw-r–r--. 1 barman barman 63 Dec 4 16:43 identity.json
drwxrwxr-x. 3 barman barman 45 Dec 4 16:43 wals
drwxrwxr-x. 5 barman barman 75 Dec 4 17:14 base
drwxrwxr-x. 2 barman barman 78 Dec 4 17:14 streaming
drwxrwxr-x. 2 barman barman 6 Dec 4 17:14 incoming
其中，比较重要的有wals、base、streaming、incoming

base中，存放的是数据库的基本备份文件(最近的一份全备)
streaming中，存放的是实时传过来的wal文件
incoming中，存放的是主库传输过来的归档文件
wals中，是所有的归档文件，可以根据这个和基础备份恢复到任意时间点

barman恢复：

查看拥有的备份集：

barman list-backup pgsql_streaming
pgsql_streaming 20201207T231544 - Sun Dec  6 23:15:56 2020 - Size: 356.9 MiB - WAL Size: 0 B
pgsql_streaming 20201207T225457 - Sun Dec  6 22:55:44 2020 - Size: 1.1 GiB - WAL Size: 4.4 MiB
pgsql_streaming 20201204T171433 - Fri Dec  4 17:14:42 2020 - Size: 1.1 GiB - WAL Size: 6.3 MiB
pgsql_streaming 20201204T170853 - Fri Dec  4 17:09:11 2020 - Size: 1.1 GiB - WAL Size: 503.1 KiB
pgsql_streaming 20201204T164310 - Fri Dec  4 16:43:39 2020 - Size: 1.1 GiB - WAL Size: 2.6 MiB
复制

检查选定的备份的详细信息:

[barman@barman 0000000100000000]$ barman show-backup pgsql_streaming 20201207T225457
Backup 20201207T225457:
  Server Name            : pgsql_streaming
  System Id              : 6901481653023568481
  Status                 : DONE
  PostgreSQL Version     : 110004
  PGDATA directory       : /PgData

  Base backup information:
    Disk usage           : 1.1 GiB (1.1 GiB with WALs)
    Incremental size     : 1.1 GiB (-0.00%)
    Timeline             : 1
    Begin WAL            : 00000001000000000000007C
    End WAL              : 00000001000000000000007E
    WAL number           : 3
    WAL compression ratio: 99.90%
    Begin time           : 2020-12-06 22:55:02+08:00
    End time             : 2020-12-06 22:55:44.674452+08:00
    Copy time            : 42 seconds
    Estimated throughput : 26.4 MiB/s
    Begin Offset         : 40
    End Offset           : 96
    Begin LSN           : 0/7C000028
    End LSN             : 0/7E000060

  WAL information:
    No of files          : 4
    Disk usage           : 4.4 MiB
    Compression ratio    : 93.14%
    Last available       : 000000010000000000000082

  Catalog information:
    Retention Policy     : VALID
    Previous Backup      : 20201204T171433
    Next Backup          : 20201207T231544
复制

barman恢复（恢复到远端数据库）：

[barman@barman PgData_recover]$ barman recover pgsql_streaming 20201207T225457 /PgData --remote-ssh-command "ssh postgres@pgsql11-recover" --target-time "2020-12-06 22:57:00.0000"
Processing xlog segments from streaming for pgsql_streaming
        000000010000000000000083
Processing xlog segments from file archival for pgsql_streaming
        000000010000000000000083
The authenticity of host 'pgsql11-recover (192.168.138.143)' can't be established.
ECDSA key fingerprint is SHA256:vu/gUNno3I8feQxKWcPkcm2kSS+g72zAE2JafM9CBF4.
ECDSA key fingerprint is MD5:59:a7:b9:12:29:31:b3:78:7f:ed:71:44:7a:7e:f2:fc.
Are you sure you want to continue connecting (yes/no)? yes
postgres@pgsql11-recover's password: 
Starting remote restore for server pgsql_streaming using backup 20201207T225457
Destination directory: /PgData
Remote command: ssh postgres@pgsql11-recover
Doing PITR. Recovery target time: '2020-12-06 22:57:00+08:00'
postgres@pgsql11-recover's password: 
postgres@pgsql11-recover's password: 
postgres@pgsql11-recover's password: 
postgres@pgsql11-recover's password: 
Copying the base backup.
postgres@pgsql11-recover's password: 
postgres@pgsql11-recover's password: 
postgres@pgsql11-recover's password: 
postgres@pgsql11-recover's password: 
postgres@pgsql11-recover's password: 
Copying required WAL segments.
postgres@pgsql11-recover's password: 
Generating recovery configuration
postgres@pgsql11-recover's password: 
postgres@pgsql11-recover's password: 
postgres@pgsql11-recover's password: 
postgres@pgsql11-recover's password: 
Identify dangerous settings in destination directory.
postgres@pgsql11-recover's password: 

IMPORTANT
These settings have been modified to prevent data losses

postgresql.conf line 706: archive_command = false

Recovery completed (start time: 2020-12-07 23:57:04.056352, elapsed time: 1 minute, 54 seconds)

Your PostgreSQL server has been successfully prepared for recovery!
复制

出现：
Your PostgreSQL server has been successfully prepared for recovery!
则表示恢复成功。

通过pg_dump/dblink等方式，将恢复的表或者数据回写到源端。