Traditional Barman Setup With WAL Streaming in PostgreSQL

July 17, 2021
()

Traditional Barman Setup With WAL Streaming in PostgreSQL

In the previous blog, we saw How To Configure Traditional Barman Backup Setup via rsync/ssh  Click here to read more.

And How To Configure Streaming Backup & WAL Streaming using BARMAN Click here to read more.

In this blog, we will combine features offered by both Setup and will configure Traditional Barman Setup With WAL Streaming.


This alternate approach requires:
1. A standard connection to PostgreSQL for management, coordination, and monitoring purposes.
2. An SSH connection for base backup operations to be used by rsync that allows the barman user on the Barman server to connect as postgres user on the PostgreSQL server.
3. An SSH connection for WAL archiving to be used by the archive_command in PostgreSQL and that allows the postgres user on the PostgreSQL server to connect as barman user on the Barman server.
4. A streaming replication connection that will be used by pg_receivewal (for WAL streaming).

Starting from PostgreSQL 9.2, you can add a streaming replication connection that is used for WAL streaming and significantly reduce RPO. With Standard archiving WAL files are archived only when PostgreSQL switches to a new WAL file. To keep it simple, this normally happens every 16MB worth of data changes. The advantage of streaming WAL records is that it doesn’t wait for the capacity to be full, these are streamed immediately. WAL streaming is able to reduce the risk of data loss, bringing RPO down to near-zero values.


Follow the below steps to Configure Traditional Barman Setup With WAL Streaming


Follow the same steps as in the blog How To Configure Traditional Barman Backup Setup via rsync/ssh  Click here to read more. And add below required steps as specified in the below Steps.


Step 7. Create barman & streaming_barman role: Create role barman & streaming_barman in PostgreSQL Database Server.  And verify that the streaming connection works through the command psql -U streaming_barman -h test-machine02 -c “IDENTIFY_SYSTEM” replication=1. Also make sure PostgreSQL configuration parameters like wal_levelmax_replication_slotsmax_wal_senders are set to the below value.

[root@test-machine02 ~]# su - postgres
Last login: Tue Jun 22 15:06:21 +03 2021 on pts/0
-bash-4.2$ psql
psql (13.2)
Type "help" for help.
postgres=#
postgres=# create user barman superuser  password 'barman';
CREATE ROLE
postgres=#
postgres=# create user streaming_barman with replication password 'barman';
CREATE ROLE
postgres=#
postgres=# \du+
                                                 List of roles
    Role name     |                         Attributes                         |    Member of     | Description
------------------+------------------------------------------------------------+------------------+-------------
 barman           | Superuser                                                  | {}               |
 postgres         | Superuser, Create role, Create DB, Replication, Bypass RLS | {}               |
 streaming_barman | Replication                                                | {}               |
 
postgres=#
postgres=# select name,setting,unit from pg_settings where name in ('wal_level','max_wal_senders','max_replication_slots');
         name          | setting | unit
-----------------------+---------+------
 max_replication_slots | 10      |
 max_wal_senders       | 10      |
 wal_level             | replica |
(3 rows)
 
-bash-4.2$ psql -U streaming_barman -h test-machine02 -c "IDENTIFY_SYSTEM"   replication=1
Password for user streaming_barman:
      systemid       | timeline |  xlogpos   | dbname
---------------------+----------+------------+--------
 6955437016975533153 |        2 | 0/440010B0 |
(1 row)
 
-bash-4.2$


Step 11.0. Configure Server configuration files in Barman Server: Server configuration files, one for each server to be backed up by Barman, is located in the /etc/barman.d directory and must have a .conf suffix. Please note user=barman is the same role we created in Step 6. Set the below parameters and save the file.

root@test-machine01 ~]# 
[root@test-machine01 ~]# cd /etc/barman.d
[root@test-machine01 barman.d]#
[root@test-machine01 barman.d]# ls -ltr
total 12
-rw-r--r--. 1 root root 1492 Nov  4  2020 streaming-server.conf-template
-rw-r--r--. 1 root root 1565 Nov  4  2020 ssh-server.conf-template
-rw-r--r--. 1 root root  947 Nov  4  2020 passive-server.conf-template
[root@test-machine01 barman.d]# vi test-machine02.conf
[test-machine02]
description =  "test-machine02 backup config"
ssh_command = ssh postgres@test-machine02
conninfo = host=test-machine02 user=barman port=5432
backup_options = concurrent_backup
backup_method = rsync
archiver = on
streaming_conninfo = host=test-machine02 user=streaming_barman port=5432
streaming_archiver = on
slot_name = barman_new
create_slot = auto
:wq!
 
[root@test-machine01 barman.d]# ls -ltr
total 16
-rw-r--r--. 1 root root 1492 Nov  4  2020 streaming-server.conf-template
-rw-r--r--. 1 root root 1565 Nov  4  2020 ssh-server.conf-template
-rw-r--r--. 1 root root  947 Nov  4  2020 passive-server.conf-template
-rw-r--r--. 1 root root  226 Jun 22 11:50 test-machine02.conf
[root@test-machine01 barman.d]# 


Step 11.1. Create pgpass file: We are using pgpass file to avoid adding password in configuration files.
Format : hostname : port : database : username : passwordPlease note for user : streaming_barman we have to add keyword replication instead of database name.

-bash-4.2$ pwd
/var/lib/barman
-bash-4.2$ vi .pgpass
test-machine02:5432:postgres:barman:barman
test-machine02:5432:replication:streaming_barman:barman
:wq!
-bash-4.2$ chmod 0600 .pgpass
-bash-4.2$
-bash-4.2$ export PGPASSFILE=/var/lib/barman/.pgpass
-bash-4.2$
-bash-4.2$ echo $PGPASSFILE
/var/lib/barman/.pgpass
 
-bash-4.2$ ls -l /var/lib/barman/.pgpass
-rw-------. 1 barman barman 99 Jul 11 13:19 /var/lib/barman/.pgpass
-bash-4.2$


Step 11.2. Start  Log Streaming: Before starting log stream make sure PATH for utility pg_receivewal is set otherwise you will receive the error “ERROR: ArchiverFailure:pg_receivexlog not present in $PATH“. Use command barman receive-wal to start Log Streaming. Execute command barman check to check everything is fine.

bash-4.2$
-bash-4.2$ export PATH=$PATH:/usr/pgsql-13/bin/
You have new mail in /var/spool/mail/barman
-bash-4.2$
-bash-4.2$ whereis pg_receivewal
pg_receivewal: /usr/pgsql-13/bin/pg_receivewal
-bash-4.2$
 
-bash-4.2$
-bash-4.2$ barman receive-wal test-machine02 &
[1] 12820
-bash-4.2$ Starting receive-wal for server test-machine02
Creating replication slot 'barman_new'
Creating physical replication slot 'barman_new' on server 'test-machine02'
Replication slot 'barman_new' created
test-machine02: pg_receivewal: starting log streaming at 0/4D000000 (timeline 2)
-bash-4.2$
-bash-4.2$ barman check test-machine02
Server test-machine02:
        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 3 backups, expected at least 3)
        ssh: OK (PostgreSQL server)
        systemid coherence: OK
        pg_receivexlog: OK
        pg_receivexlog compatible: OK
        receive-wal running: OK
        archive_mode: OK
        archive_command: OK
        continuous archiving: OK
        archiver errors: OK

-bash-4.2$ ps -ef|grep barman
barman    12820  12609  0 15:05 pts/1    00:00:00 /usr/bin/python2 /bin/barman receive-wal test-machine02
barman    12822  12820  0 15:05 pts/1    00:00:00 /usr/pgsql-13/bin/pg_receivewal --dbname=dbname=replication host=test-machine02 options=-cdatestyle=iso port=5432 replication=true user=streaming_barman application_name=barman_receive_wal --verbose --no-loop --no-password --directory=/var/lib/barman/test-machine02/streaming --slot=barman_new
barman    13166  12609  0 15:10 pts/1    00:00:00 ps -ef
barman    13167  12609  0 15:10 pts/1    00:00:00 grep --color=auto barman
-bash-4.2$
-bash-4.2$


Step 11.3. Check barman WAL Streaming Configuration: There are many commands to check if the configuration is working fine. We have listed below, barman replication-statusbarman status. You can also login to PostgreSQL Database and check the process with a select query. Use command barman show-server to view full configuration settings.

-bash-4.2$ barman replication-status test-machine02
Status of streaming clients for server 'test-machine02':
  Current LSN on master: 0/4D000000
  Number of streaming clients: 2

  1. Async WAL streamer
     Application name: barman_receive_wal
     Sync stage      : 3/3 Remote write
     Communication   : TCP/IP
     IP Address      : 192.168.114.177 / Port: 31812 / Host: -
     User name       : streaming_barman
     Current state   : streaming (async)
     Replication slot: barman
     WAL sender PID  : 21865
     Started at      : 2021-07-15 14:19:57.468501+03:00
     Sent LSN   : 0/4D000000 (diff: 0 B)
     Write LSN  : 0/4D000000 (diff: 0 B)
     Flush LSN  : 0/4D000000 (diff: 0 B)
-bash-4.2$ 
-bash-4.2$  barman status test-machine02
Server test-machine02:
        Description: test-machine02 backup config
        Active: True
        Disabled: False
        PostgreSQL version: 13.2
        Cluster state: in production
        pgespresso extension: Not available
        Current data size: 47.4 MiB
        PostgreSQL Data directory: /var/lib/pgsql/13/data
        Current WAL segment: 00000002000000000000004C
        PostgreSQL 'archive_command' setting: barman-wal-archive test-machine01 test-machine02 %p
        Last archived WAL: 000000020000000000000049.00000028.backup, at Sun Jul 11 14:48:07 2021
        Failures of WAL archiver: 21240 (00000002.history at Sun Jul 11 12:42:25 2021)
        Server WAL archiving rate: 0.05/hour
        Passive node: False
        Retention policies: enforced (mode: auto, retention: RECOVERY WINDOW OF 7 DAYS, WAL retention: MAIN)
        No. of available backups: 3
        First available backup: 20210705T155400
        Last available backup: 20210711T144754
        Minimum redundancy requirements: satisfied (3/3)
-bash-4.2$

-bash-4.2$ barman show-server test-machine02
Server test-machine02:
        active: True
        archive_command: barman-wal-archive test-machine01 test-machine02 %p
        archive_mode: on
        archive_timeout: 21600
        archived_count: 11
        archiver: True
        archiver_batch_size: 0
        backup_directory: /var/lib/barman/test-machine02
        backup_method: rsync
        backup_options: BackupOptions(['concurrent_backup'])
        bandwidth_limit: None
        barman_home: /var/lib/barman
        barman_lock_directory: /var/lib/barman
        basebackup_retry_sleep: 30
        basebackup_retry_times: 3
        basebackups_directory: /var/lib/barman/test-machine02/base
        check_timeout: 30


Step 13.0. Configure WAL Shipping in PostgreSQL Database Server: Edit postgresql.conf and modify archive_command to copy WAL Files in barman server. As archive_command is a static parameter that required postgresql service restart.

[root@test-machine02 ~]# cd /var/lib/pgsql/13/data/
[root@test-machine02 data]# vi postgresql.conf
archive_mode = on
archive_command = 'barman-wal-archive test-machine01 test-machine02 %p'
:wq!
  
[root@test-machine02 system]# systemctl restart postgresql-13
[root@test-machine02 system]#
  
[root@test-machine02 ~]# su - postgres
Last login: Sun Jul 11 10:56:42 +03 2021 on pts/1
-bash-4.2$ psql
psql (13.2)
Type "help" for help.
postgres=#
postgres=# select name,setting,unit from pg_settings where name in ('archive_mode','wal_level','archive_command');
      name       |                       setting                       | unit
-----------------+-----------------------------------------------------+------
 archive_command | barman-wal-archive test-machine01 test-machine02 %p |
 archive_mode    | on                                                  |
 wal_level       | replica                                             |
(3 rows)
  
postgres=#


Step 13.1. Check barman-wal-archive working in Barman server: You can check that barman-wal-archive can connect to the Barman server, and that the required PostgreSQL server is configured in Barman to accept incoming WAL files with the following command:

-bash-4.2$
-bash-4.2$ barman-wal-archive --test test-machine01 test-machine02 DUMMY
barman@test-machine01's password:
Ready to accept WAL files for the server test-machine02
-bash-4.2$


Step 15. Configure cron: Since barman doesn’t include a long-running daemon or service file there’s nothing command like systemctl start barman cron or service start barman cron etc. So it is recommended to schedule barman cron to run every minute. The barman cron command ensures that WAL streaming is started for those servers that have requested it, by transparently executing the receive-wal command.

-bash-4.2$ crontab -l
* * * * * export PATH=$PATH:/usr/pgsql-13/bin; barman cron >/dev/null 2>&1
-bash-4.2$


References: BARMAN Document: http://docs.pgbarman.org/release/2.12/
For BARMAN Backup Command: https://dbsguru.com/physical-postgresql-backup-using-barman/
For BARMAN Restore Command: https://dbsguru.com/restore-backup-using-barman-in-postgresql/
For BARMAN PITR Comamnd: https://dbsguru.com/point-in-time-recovery-using-barman-in-postgresql/

 

This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article
!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.


Related articles


Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Hello and welcome to DBsGuru,I’m Jamsher Khan working as Senior Oracle DBA based in KSA-Jeddah, I have working experience in Oracle DBA, SQL Server, MySql, PostgreSQL, Linux, Golden Gate, ODA.Thanks for the visits!Share Learn Grow!

Leave a Reply

Your email address will not be published. Required fields are marked *