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_level, max_replication_slots, max_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 : password, Please 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-status, barman 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
- Setup Logical Replication using pglogical in PostgreSQL
- Backups from the Standby Server using pgBackRest in PostgreSQL
- Setup Streaming Replication with pgBackRest in PostgreSQL
- Configure automatic failover with repmgrd in PostgreSQL
- Perform Manual Failover & Switchover using repmgr in PostgreSQL
