Monday, June 10, 2024

EDB - Barman - Part I: Installation and Configuration

 1. System information

Barman server

  • IP : 192.168.56.101
  • Barman version: 3.10.0 Barman by EnterpriseDB (www.enterprisedb.com)
  • The pg_basebackup,pg_receivewal/pg_receivexlog, pg_verifybackup binaries are installed with the PostgreSQL client/server packages
    • [root@barman-server ~]# dnf install barman barman-cli edb-as16-server

Database server

  • IP: 192.168.56.102
  • Database version: PostgreSQL 16.3 (EnterpriseDB Advanced Server 16.3.0) on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1 20231218 (Red Hat 11.4.1-3), 64-bit
  • Package barman-cli:
    • Using barman-wal-archive instead of rsync/SSH reduces the risk of data corruption of the shipped WAL file on the Barman server. When using rsync/SSH as archive_command a WAL file, there is no mechanism that guarantees that the content of the file is flushed and fsync-ed to disk on destination.
    • [root@edb-saigon ~]# dnf install barman-cli
    • After configuring "employees-ssh" on barman-server, we can check:
      • [enterprisedb@edb-saigon ~]$ barman-wal-archive --test barman-server employees4ssh /barman/backup/employees4ssh/incoming
        Ready to accept WAL files for the server employees4ssh 
  • Database parameters:
    • archive_mode = on
    • archive_command = ' test ! -f /u01/edb/as16/data/archivelog/%f && cp %p /u01/edb/as16/data/archivelog/%f && rsync -a %p barman@192.168.56.101:/barman/backup/employees4ssh/incoming/%f'
    • Or archive_command = 'barman-wal-archive barman-server employees4ssh %p'
    • wal_level = replica (optional)
    • max_replication_slots = 4
    • max_wal_senders = 4
  • Database users for backup by barman:
    • barman user
      • [enterprisedb@edb-saigon ~]$ createuser -s -P barman
    • streaming_barman user
      • [enterprisedb@edb-saigon ~]$ createuser -P --replication streaming_barman
  • pg_hba.conf 
    • host    all                  all             0.0.0.0/0           trust
    • host    replication     all             0.0.0.0/0            trust

2. Two typical scenarios for backups

Scenario 1: Backup via streaming protocol




Scenario 2: Backup via rsync/SSH


3. Configure SSH - Passwordless Authentication (no password)

SSH key exchange is a very common practice that is used to implement secure passwordless connections between users on different machines, and it's needed to use rsync for WAL archiving and for backups.

We can combine 2 types of backup. So, configuring SSH passwordless is required, especially in remote restore database.

Create an SSH key
  • On Barman server
            [root@barman-server ~]# su - barman 
            [barman@barman-server ~]$ pwd
            /var/lib/barman

            [barman@barman-server ~]$ ssh-keygen -t rsa 
  • On Database server
            [root@edb-saigon ~]# su - enterprisedb 
            [enterprisedb@edb-saigon ~]$ pwd
            /var/lib/edb

            [enterprisedb@edb-saigon ~]$ ssh-keygen -t 

Copy key to servers
  • On Barman server
            [barman@barman-server ~]$ ssh-copy-id -i ~/.ssh/id_rsa.pub enterprisedb@192.168.56.102
            [barman@barman-server ~]$ ssh 'enterprisedb@192.168.56.102' 
  • On Database server
            [enterprisedb@edb-saigon ~]$  ssh-copy-id -i ~/.ssh/id_rsa.pub barman@192.168.56.101
            [enterprisedb@edb-saigon ~]$ ssh 'barman@192.168.56.101'

4. Barman server configuration

Configuring ~/.pgpass

[barman@barman-server ~]$ vi .pgpass
edb-saigon:*:*:barman:<password>
edb-saigon:*:replication:streaming_barman:<password>

[barman@barman-server ~]$ chmod 0600 ~/.pgpass

Configuring env

[barman@barman-server ~]$ vi ~/.bash_profile 
export PGPASSFILE=/var/lib/barman/.pgpass
export PATH=$PATH:/usr/edb/as16/bin/

Configuring Global File

[root@barman-server ~]# vi /etc/barman.conf
[barman]
barman_user = barman
configuration_files_directory = /etc/barman.d
barman_home = /barman/backup
log_file = /var/log/barman/barman.log
log_level = INFO
compression = gzip
parallel_jobs = 2
immediate_checkpoint = true
basebackup_retry_times = 3
minimum_redundancy = 3
retention_policy = REDUNDANCY 3


Configuring backup database via streaming protocol

[root@barman-server ~]# vi /etc/barman.d/employees-ssh.conf
[employees.streaming]
description =  "Backup employees database via Streaming protocol"
conninfo = host=edb-saigon.taolaoxibup.com user=barman dbname=employees
streaming_conninfo = host=edb-saigon.taolaoxibup.com user=streaming_barman
backup_method = postgres 
backup_compression = gzip
parallel_jobs = 4
streaming_archiver = on
slot_name = barman
create_slot = auto
minimum_redundancy = 3
retention_policy = RECOVERY WINDOW OF 7 DAYS

IMPORTANT: 
  • The reuse_backup option can’t be used with the backup_method = postgres
  • The retention_policy option is also help to remove unused wal files automatically
[barman@barman-server ~]$ barman receive-wal --create-slot employees4streaming
Creating physical replication slot 'barman' on server 'employees4streaming'
Replication slot 'barman' created

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

Configuring backup database via rsync/SSH

[root@barman-server ~]# vi /etc/barman.d/employees-streaming.conf
[employees.ssh]
description =  "Backup employees database via rsync/SSH"
ssh_command = ssh postgres@edb-saigon.taolaoxibup.com
conninfo = host=edb-saigon.taolaoxibup.com user=barman dbname=employees
backup_options = concurrent_backup
backup_method = rsync
; wal compression
compression = gzip
parallel_jobs = 4
; incremental backup
reuse_backup = link
archiver = on
minimum_redundancy = 3
retention_policy = RECOVERY WINDOW OF 7 DAYS

Configuration barman cron
  • Recommendation is to schedule barman cron to run every minute.
  • barman cron executes WAL archiving operations concurrently on a server basis, and this also enforces retention policies on those servers that have:
    • retention_policy not empty and valid;
    • retention_policy_mode set to auto.
  • The cron command ensures that WAL streaming is started for those servers that have requested it, by transparently executing the receive-wal command.
[barman@barman ~]$ crontab -e
* * * * * export PATH=$PATH:/usr/edb/as16/bin/ barman cron >/dev/null 2>&1

Verify configuration

[barman@barman-server ~]$ barman list-servers
employees4ssh - Backup employees database via rsync/SSH
employees4streaming - Backup employees database via Streaming protocol

[barman@barman-server ~]$ barman check employees4ssh
Server employees4ssh:
        PostgreSQL: OK
        superuser or standard user with backup privileges: OK
        wal_level: 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: OK (have 0 backups, expected at least 0)
        ssh: OK (PostgreSQL server)
        systemid coherence: OK
        archive_mode: OK
        archive_command: OK
        continuous archiving: OK
        archiver errors: OK

[barman@barman-server ~]$ barman check employees4streaming
Server 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: 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
        archiver errors: OK

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


Ref:

at
Labels: