EDB - Barman - Part III: Backup via Streaming protocol

In Part I, we have already configured backup via Streaming protocol. In this section, we will backup database with this method.

Note:

• The reuse_backup option can’t be used with the postgres backup method at this time
• If a backup has been compressed using the backup_compression option then barman recover is able to uncompress the backup on recovery. Using the recovery_staging_path option in order to choose a suitable location for the staging directory to unpcompress. Set recovery_staging_path in the global/server config or use the --recovery-staging-path option with the barman recover command

1. Check information

💩Database server

edb=# select * from pg_replication_slots;

-[ RECORD 1 ]-------+-----------
slot_name           | barman
plugin              |
slot_type           | physical
datoid              |
database            |
temporary           | f
active              | t
active_pid          | 11580
xmin                |
catalog_xmin        |
restart_lsn         | 0/C9000000
confirmed_flush_lsn |
wal_status          | reserved
safe_wal_size       |
two_phase           | f
conflicting         |

 

edb=# show archive_command ;
                     archive_command                     
---------------------------------------------------------
 barman-wal-archive barman-server employees4streaming %p
(1 row)

 

[enterprisedb@edb-saigon pg_wal]$ ls -ltr

total 98320

-rw-------. 1 enterprisedb enterprisedb 16777216 Jun 13 10:01 0000000100000000000000CB

-rw-------. 1 enterprisedb enterprisedb 16777216 Jun 13 10:01 0000000100000000000000CC

-rw-------. 1 enterprisedb enterprisedb 16777216 Jun 13 10:03 0000000100000000000000CD

-rw-------. 1 enterprisedb enterprisedb 16777216 Jun 13 10:03 0000000100000000000000CE

-rw-------. 1 enterprisedb enterprisedb      359 Jun 13 10:03 0000000100000000000000C8.00000028.backup

-rw-------. 1 enterprisedb enterprisedb 16777216 Jun 13 11:01 0000000100000000000000C9
drwx------. 2 enterprisedb enterprisedb     8192 Jun 13 11:01 archive_status
-rw-------. 1 enterprisedb enterprisedb 16777216 Jun 13 11:01 0000000100000000000000CA

💀Barman server

Start Log Streaming

[barman@barman-server ~]$ barman receive-wal employees4streaming & 

[barman@barman-server ~]$ ps -ef | grep pg_receivewal

barman     18845   18842  0 11:01 pts/0    00:00:00 /usr/edb/as16/bin/pg_receivewal --dbname=dbname=replication host=edb-saigon.taolaoxibup.com options=-cdatestyle=iso replication=true user=streaming_barman application_name=barman_receive_wal --verbose --no-loop --no-password --directory=/barman/backup/employees4streaming/streaming --slot=barman

Verify configuration

[barman@barman-server ~]$ barman show-server employees4streaming

[barman@barman-server ~]$ barman check employees4streaming

        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)
        backup minimum size: OK (0 B)
        wal maximum age: OK (no last_wal_maximum_age provided)
        wal size: OK (0 B)
        compression settings: OK
        failed backups: OK (there are 0 failed backups)
        minimum redundancy requirements: FAILED (have 0 backups, expected at least 2)
        pg_basebackup: OK
        pg_basebackup compatible: OK
        pg_basebackup supports tablespaces mapping: OK
        systemid coherence: OK
        pg_receivexlog: OK
        pg_receivexlog compatible: OK
        receive-wal running: OK
        archiver errors: OK
 
[barman@barman-server ~]$ ls -ltr /barman/backup/employees4streaming/streaming/
total 16384
-rw-------. 1 barman barman 16777216 Jun 13 11:01 0000000100000000000000CA.partial

 [barman@barman-server ~]$ ls -l /barman/backup/employees4streaming/wals/0000000100000000
total 20
-rw-------. 1 barman barman 16567 Jun 13 11:01 0000000100000000000000C9

2. Backup 

[barman@barman-server ~]$ barman backup employees4streaming

Starting backup using postgres method for server employees4streaming in /barman/backup/employees4streaming/base/20240613T112427
Backup start at LSN: 0/CA0001C0 (0000000100000000000000CA, 000001C0)
Starting backup copy via pg_basebackup for 20240613T112427
employees4streaming: pg_receivewal: finished segment at 0/CB000000 (timeline 1)
employees4streaming: pg_receivewal: finished segment at 0/CC000000 (timeline 1)
Copy done (time: 7 seconds)
Finalising the backup.
This is the first backup for server employees4streaming
WAL segments preceding the current backup have been found:
        0000000100000000000000C9 from server employees4streaming has been removed
Backup size: 901.3 MiB
Backup end at LSN: 0/CC000000 (0000000100000000000000CB, 00000000)
Backup completed (start time: 2024-06-13 11:24:27.021476, elapsed time: 9 seconds)
Processing xlog segments from streaming for employees4streaming
        0000000100000000000000CA
        0000000100000000000000CB

 

[barman@barman-server ~]$ barman list-backup employees4streaming 

employees4streaming 20240613T112427 - Thu Jun 13 11:24:35 2024 - Size: 901.3 MiB - WAL Size: 0 B (tablespaces: tbs_ihrp_data:/u01/ihrp/data, tbs_ihrp_index:/u01/ihrp/index) 

[barman@barman-server ~]$ barman show-backup employees4streaming 20240613T112427

Backup 20240613T112427:

  Server Name            : employees4streaming

  System Id              : 7377775620992307471

  Status                 : DONE

  PostgreSQL Version     : 160003

  PGDATA directory       : /u01/edb/as16/data 

  Tablespaces:

    tbs_ihrp_data        : /u01/ihrp/data (oid: 16385)

    tbs_ihrp_index       : /u01/ihrp/index (oid: 16386) 

  Base backup information:

    Disk usage           : 901.3 MiB (901.3 MiB with WALs)

    Incremental size     : 901.3 MiB (-0.00%)

    Timeline             : 1

    Begin WAL            : 0000000100000000000000CB

    End WAL              : 0000000100000000000000CB

    WAL number           : 1

    WAL compression ratio: 99.90%

    Begin time           : 2024-06-13 11:24:27.374539+07:00

    End time             : 2024-06-13 11:24:35.219751+07:00

    Copy time            : 7 seconds

    Estimated throughput : 115.2 MiB/s

    Begin Offset         : 40

    End Offset           : 0

    Begin LSN            : 0/CB000028

    End LSN              : 0/CC000000 

  WAL information:

    No of files          : 0

    Disk usage           : 0 B

    Last available       : 0000000100000000000000CB

 

  Catalog information:

    Retention Policy     : VALID

    Previous Backup      : - (this is the oldest base backup)

    Next Backup          : - (this is the latest base backup)

 

[barman@barman-server ~]$ ls -ltr /barman/backup/employees4streaming/base/

total 0
drwxr-xr-x. 5 barman barman 63 Jun 13 11:24 20240613T112427

 

[barman@barman-server base]$ du -hs *

902M    20240613T112427 

[barman@barman-server ~]$ ls -ltr /barman/backup/employees4streaming/incoming/

total 32772

-rw-r--r--. 1 barman barman 16777216 Jun 13 11:24 0000000100000000000000CA

-rw-r--r--. 1 barman barman 16777216 Jun 13 11:24 0000000100000000000000CB

-rw-r--r--. 1 barman barman      341 Jun 13 11:24 0000000100000000000000CB.00000028.backup

[barman@barman-server ~]$ ls -ltr /barman/backup/employees4streaming/streaming/

total 16384

-rw-------. 1 barman barman 16777216 Jun 13 11:24 0000000100000000000000CC.partial

 

[barman@barman-server ~]$ ls -ltr /barman/backup/employees4streaming/wals/0000000100000000/

total 20

-rw-------. 1 barman barman 16464 Jun 13 11:24 0000000100000000000000CB

 

[enterprisedb@edb-saigon pg_wal]$ ls -ltr

total 81932

-rw-------. 1 enterprisedb enterprisedb 16777216 Jun 13 10:03 0000000100000000000000CD

-rw-------. 1 enterprisedb enterprisedb 16777216 Jun 13 10:03 0000000100000000000000CE

-rw-------. 1 enterprisedb enterprisedb 16777216 Jun 13 11:24 0000000100000000000000CA

-rw-------. 1 enterprisedb enterprisedb 16777216 Jun 13 11:24 0000000100000000000000CB

-rw-------. 1 enterprisedb enterprisedb      341 Jun 13 11:24 0000000100000000000000CB.00000028.backup

drwx------. 2 enterprisedb enterprisedb     4096 Jun 13 11:24 archive_status

-rw-------. 1 enterprisedb enterprisedb 16777216 Jun 13 11:24 0000000100000000000000CC

Change data 

employees=> create table employees.temp03 tablespace tbs_ihrp_data as select * from employees.employee ;

SELECT 300024

employees=> create table employees.temp04 tablespace tbs_ihrp_data as select * from employees.employee ;

SELECT 300024

[enterprisedb@edb-saigon pg_wal]$ ls -ltr

total 98316

-rw-------. 1 enterprisedb enterprisedb      341 Jun 13 11:24 0000000100000000000000CB.00000028.backup

-rw-------. 1 enterprisedb enterprisedb 16777216 Jun 13 12:56 0000000100000000000000CC

-rw-------. 1 enterprisedb enterprisedb 16777216 Jun 13 12:56 0000000100000000000000CD

-rw-------. 1 enterprisedb enterprisedb 16777216 Jun 13 12:56 0000000100000000000000CE

-rw-------. 1 enterprisedb enterprisedb 16777216 Jun 13 12:56 0000000100000000000000CF

-rw-------. 1 enterprisedb enterprisedb 16777216 Jun 13 12:56 0000000100000000000000D0

drwx------. 2 enterprisedb enterprisedb     4096 Jun 13 12:56 archive_status

-rw-------. 1 enterprisedb enterprisedb 16777216 Jun 13 12:56 0000000100000000000000D1

[barman@barman-server ~]$ barman list-backup employees4streaming

employees4streaming 20240613T112427 - Thu Jun 13 11:24:35 2024 - Size: 901.3 MiB - WAL Size: 27.9 MiB (tablespaces: tbs_ihrp_data:/u01/ihrp/data, tbs_ihrp_index:/u01/ihrp/index)

[barman@barman-server ~]$ barman backup employees4streaming

Starting backup using postgres method for server employees4streaming in /barman/backup/employees4streaming/base/20240613T125918

Backup start at LSN: 0/D12AAE70 (0000000100000000000000D1, 002AAE70)

Starting backup copy via pg_basebackup for 20240613T125918

Copy done (time: 6 seconds)

Finalising the backup.

Backup size: 959.9 MiB. Actual size on disk: 959.9 MiB (-0.00% deduplication ratio).

Backup end at LSN: 0/D3000000 (0000000100000000000000D2, 00000000)

Backup completed (start time: 2024-06-13 12:59:18.750619, elapsed time: 7 seconds)

Processing xlog segments from streaming for employees4streaming

        0000000100000000000000D1

        0000000100000000000000D2

 

[barman@barman-server ~]$ barman list-backup employees4streaming

employees4streaming 20240613T125918 - Thu Jun 13 12:59:25 2024 - Size: 960.0 MiB - WAL Size: 0 B (tablespaces: tbs_ihrp_data:/u01/ihrp/data, tbs_ihrp_index:/u01/ihrp/index)

employees4streaming 20240613T112427 - Thu Jun 13 11:24:35 2024 - Size: 901.3 MiB - WAL Size: 28.8 MiB (tablespaces: tbs_ihrp_data:/u01/ihrp/data, tbs_ihrp_index:/u01/ihrp/index)

 

[barman@barman-server ~]$ ls -ltr /barman/backup/employees4streaming/streaming/
total 16384
-rw-------. 1 barman barman 16777216 Jun 13 12:59 0000000100000000000000D3.partial
 
[barman@barman-server ~]$ ls -ltr /barman/backup/employees4streaming/wals/0000000100000000/
total 29476
-rw-------. 1 barman barman   16464 Jun 13 11:24 0000000100000000000000CB
-rw-------. 1 barman barman 5841245 Jun 13 12:56 0000000100000000000000CC
-rw-------. 1 barman barman 5848977 Jun 13 12:56 0000000100000000000000CD
-rw-------. 1 barman barman 5847637 Jun 13 12:56 0000000100000000000000CE
-rw-------. 1 barman barman 5848769 Jun 13 12:56 0000000100000000000000CF
-rw-------. 1 barman barman 5850809 Jun 13 12:56 0000000100000000000000D0
-rw-------. 1 barman barman  894289 Jun 13 12:59 0000000100000000000000D1
-rw-------. 1 barman barman   16468 Jun 13 12:59 0000000100000000000000D2
 
[barman@barman-server ~]$ ls -ltr /barman/backup/employees4streaming/incoming/
total 147464
-rw-r--r--. 1 barman barman 16777216 Jun 13 11:24 0000000100000000000000CA
-rw-r--r--. 1 barman barman 16777216 Jun 13 11:24 0000000100000000000000CB
-rw-r--r--. 1 barman barman      341 Jun 13 11:24 0000000100000000000000CB.00000028.backup
-rw-r--r--. 1 barman barman 16777216 Jun 13 12:56 0000000100000000000000CC
-rw-r--r--. 1 barman barman 16777216 Jun 13 12:56 0000000100000000000000CD
-rw-r--r--. 1 barman barman 16777216 Jun 13 12:56 0000000100000000000000CE
-rw-r--r--. 1 barman barman 16777216 Jun 13 12:56 0000000100000000000000CF
-rw-r--r--. 1 barman barman 16777216 Jun 13 12:56 0000000100000000000000D0
-rw-r--r--. 1 barman barman 16777216 Jun 13 12:59 0000000100000000000000D1
-rw-r--r--. 1 barman barman 16777216 Jun 13 12:59 0000000100000000000000D2
-rw-r--r--. 1 barman barman      341 Jun 13 12:59 0000000100000000000000D2.00000028.backup
 
[barman@barman-server ~]$ ls -ltr /barman/backup/employees4streaming/base/
total 0
drwxr-xr-x. 5 barman barman 63 Jun 13 11:24 20240613T112427
drwxr-xr-x. 5 barman barman 63 Jun 13 12:59 20240613T125918
 
[barman@barman-server base]$ du -hs *
902M    20240613T112427
961M    20240613T125918 --> FULL BACKUP
 
[barman@barman-server ~]$ barman show-backup employees4streaming 20240613T125918
Backup 20240613T125918:
  Server Name            : employees4streaming
  System Id              : 7377775620992307471
  Status                 : DONE
  PostgreSQL Version     : 160003
  PGDATA directory       : /u01/edb/as16/data
 
  Tablespaces:
    tbs_ihrp_data        : /u01/ihrp/data (oid: 16385)
    tbs_ihrp_index       : /u01/ihrp/index (oid: 16386)
 
  Base backup information:
    Disk usage           : 959.9 MiB (960.0 MiB with WALs)
    Incremental size     : 959.9 MiB (-0.00%)
    Timeline             : 1
    Begin WAL            : 0000000100000000000000D2
    End WAL              : 0000000100000000000000D2
    WAL number           : 1
    WAL compression ratio: 99.90%
    Begin time           : 2024-06-13 12:59:19.067727+07:00
    End time             : 2024-06-13 12:59:25.204707+07:00
    Copy time            : 6 seconds
    Estimated throughput : 156.7 MiB/s
    Begin Offset         : 40
    End Offset           : 0
    Begin LSN            : 0/D2000028
    End LSN              : 0/D3000000
 
  WAL information:
    No of files          : 0
    Disk usage           : 0 B
    Last available       : 0000000100000000000000D2
 
  Catalog information:
    Retention Policy     : VALID
    Previous Backup      : 20240613T112427
    Next Backup          : - (this is the latest base backup) 

Change data 
employees=> create table employees.temp06 tablespace tbs_ihrp_data as select * from employees.employee ;
SELECT 300024
employees=> create table employees.temp07 tablespace tbs_ihrp_data as select * from employees.employee ;
SELECT 300024
 

[enterprisedb@edb-saigon pg_wal]$ ls -ltr

total 114704
-rw-------. 1 enterprisedb enterprisedb 16777216 Jun 13 12:56 0000000100000000000000D7
-rw-------. 1 enterprisedb enterprisedb 16777216 Jun 13 12:59 0000000100000000000000D8
-rw-------. 1 enterprisedb enterprisedb 16777216 Jun 13 12:59 0000000100000000000000D9
-rw-------. 1 enterprisedb enterprisedb      341 Jun 13 12:59 0000000100000000000000D2.00000028.backup
-rw-------. 1 enterprisedb enterprisedb 16777216 Jun 13 13:12 0000000100000000000000D3
-rw-------. 1 enterprisedb enterprisedb 16777216 Jun 13 13:12 0000000100000000000000D4
-rw-------. 1 enterprisedb enterprisedb 16777216 Jun 13 13:12 0000000100000000000000D5
drwx------. 2 enterprisedb enterprisedb     8192 Jun 13 13:12 archive_status
-rw-------. 1 enterprisedb enterprisedb 16777216 Jun 13 13:12 0000000100000000000000D6
 

[barman@barman-server ~]$ barman status employees4streaming

Server employees4streaming:
        Description: Backup employees database via Streaming protocol
        Active: True
        Disabled: False
        PostgreSQL version: 16.3
        Cluster state: in production
        Current data size: 535.0 MiB
        PostgreSQL Data directory: /u01/edb/as16/data
        Current WAL segment: 0000000100000000000000D6
        Passive node: False
        Retention policies: enforced (mode: auto, retention: REDUNDANCY 3, WAL retention: MAIN)
        No. of available backups: 2
        First available backup: 20240613T112427
        Last available backup: 20240613T125918
        Minimum redundancy requirements: satisfied (2/2)

Note:

• Every backup is a FULL backup. We shouldn't need to depend on having a base backup for every change made to the database.
• Check .../employees4streaming/streaming folder when switch log occurs in Database server. This folder is managed by retention policy. If a backup is deleted by barman or manually, all wal files correspond to this backup will also be deleted.
• Check .../employees4streaming/incomming folder which contains archivelogs shipped from Database server. 
• Expired archivelogs is not deleted automatically. 
• After backup, barman will mark archivelog with suffix .backup and all older archivelogs will be expired.
• Using pg_archivecleanup to clear all expired archivelogs. How to configure deleting expired archivelogs automatically with pg_archivecleanup ? Writing bash script ?
• arch_path = /barman/backup/employees4streaming/incoming 
• expired_arch = ls -ltr *.backup | awk '{print $9}' | tail -n1
• pg_archivecleanup $arch_path $expired_arch

3. COPY BACKUP SET TO TAPE & RESTORE EXPIRED BACKUP SET

Because of organization's policy, we must keep backup sets a long time, maybe 5 years. So we must copy the oldest backup set to tape or staging area before it is expired.

Configuration in [barman] section

• minimum_redundancy = 2
• retention_policy = REDUNDANCY 3

[barman@barman-server ~]$ barman list-backup employees4streaming 

employees4streaming 20240628T133810 - Fri Jun 28 13:38:17 2024 - Size: 989.2 MiB - WAL Size: 7.6 MiB (tablespaces: tbs_ihrp_data:/u01/ihrp/data, tbs_ihrp_index:/u01/ihrp/index)

employees4streaming 20240614T131133 - Fri Jun 14 13:11:40 2024 - Size: 940.8 MiB - WAL Size: 64.3 KiB (tablespaces: tbs_ihrp_data:/u01/ihrp/data, tbs_ihrp_index:/u01/ihrp/index)

employees4streaming 20240614T130751 - Fri Jun 14 13:07:58 2024 - Size: 921.2 MiB - WAL Size: 19.3 MiB (tablespaces: tbs_ihrp_data:/u01/ihrp/data, tbs_ihrp_index:/u01/ihrp/index)

[root@barman-server ~]# tree -L 4 /barman/backup/

...

└── employees4streaming

    ├── base

    │   ├── 20240614T130751

    │   │   ├── 16385

    │   │   ├── 16386

    │   │   ├── backup.info

    │   │   └── data

    │   ├── 20240614T131133

    │   │   ├── 16385

    │   │   ├── 16386

    │   │   ├── backup.info

    │   │   └── data

    │   └── 20240628T133810

    │       ├── 16385

    │       ├── 16386

    │       ├── backup.info

    │       └── data

    ├── errors

    ├── identity.json

    ├── incoming

    │   ├── 000000010000000100000047

    │   ├── 000000010000000100000048

    │   ├── 000000010000000100000048.00000028.backup

    │   ├── 000000010000000100000049

    │   ├── 00000001000000010000004A

    │   └── 00000001000000010000004B

    ├── streaming

    │   └── 00000001000000010000004C.partial

    └── wals

        ├── 0000000100000001

        │   ├── 000000010000000100000027

        │   ├── 000000010000000100000028

        │   ├── 000000010000000100000029

        │   ├── 00000001000000010000002A

        │   ├── 00000001000000010000002B

        │   ├── 00000001000000010000002C

        │   ├── 00000001000000010000002D

        │   ├── 00000001000000010000002E

        │   ├── 000000010000000100000047

        │   ├── 000000010000000100000048

        │   ├── 000000010000000100000049

        │   ├── 00000001000000010000004A

        │   └── 00000001000000010000004B

        └── xlog.db

Because of retaining only 3 full backup sets, so we need copy the oldest backup set in /base, such as  (20240614T130751) plus identity.json, incoming,streaming,wals to tape before the next full backup.

When restoring the expired backup set to new server, we must configure right path of backup location in /etc/barman.conf. 

• Retrieve the backup files (typically the entire backup directory) from tape to a local directory.
• Ensure the backup directory structure ('base', 'incoming', 'streaming', 'wals', etc.) is intact.
• Use barman recover restore command to restore the backup to the new server.

EDB - Barman - Part I: Installation and Configuration

EDB - Barman - Part II: Backup via rsync/SSH

Ref:

• Barman Manual (pgbarman.org)
• EDB Docs - Barman (enterprisedb.com)