Barman Full Backup from Standby and WAL Streaming from Primary

This article details the procedure for configuring Barman to perform a full backup from a standby server while streaming WALs from the primary server in a PostgreSQL setup. This setup can be beneficial for offloading backup operations to the standby, thereby reducing the load on the primary server.

In this article, we used wal_streaming_conninfo parameter for wal streaming from primary. This article is useful in Barman version 3.10 and newer.

1. Default Behavior:

   • streaming_conninfo: This parameter is used by Barman to define the connection details (host, port, user, etc.) when it needs to perform two key tasks:
   • Streaming WAL segments using the pg_receivewal process, which is a tool that streams WAL files from a PostgreSQL server to a Barman server.
   • streaming_conninfo: is used for both backup and WAL streaming when only one connection configuration is applied.
   • Checking the status of replication slots on the PostgreSQL server. Replication slots are a feature in PostgreSQL that ensures WAL files needed by replicas (or by Barman) are not removed until they are no longer needed.
   • conninfo: This is a general connection string used by Barman for other interactions with the PostgreSQL server, such as taking backups.
   • Limitation: By default, because conninfo and streaming_conninfo are both used for these tasks, Barman would connect to the same host for both streaming WALs and taking backups. This means that both operations would be tied to a single server

2. Alternative Configuration:

   • Different Sources for Backups and WAL Streaming: In some scenarios, you might want to take full backups from a standby server (to offload work from the primary) while still streaming WALs from the primary server (to ensure you capture all WAL segments in real-time). This configuration requires Barman to use different connection settings for each task.
   • wal_streaming_conninfo: This parameter is introduced to provide a dedicated connection string specifically for the task of streaming WAL segments and checking replication slot status. It allows you to decouple the WAL streaming process from the backup process, enabling you to:
   • Stream WALs from the primary server using wal_streaming_conninfo.
   • Take backups from a standby server using conninfo

1. Flexibility in Backup Strategy:

   • With wal_streaming_conninfo, you are not restricted to performing both backups and WAL streaming from the same server. This flexibility is especially useful in high-availability environments where you want to minimize the impact on the primary server by taking backups from a standby.

2. Load Distribution:

   • Primary Server Load: The primary server often handles live traffic and should be kept as responsive as possible. By streaming WALs from the primary and taking backups from a standby, you distribute the load across your infrastructure. This can help maintain the performance of the primary server by offloading resource-intensive tasks like backups to a standby.
   • Standby Server Utilization: Standby servers typically mirror the primary but may not be as heavily utilized. By using them for backups, you make better use of available resources.

• Backup Server: 192.168.1.5
• Primary Server: 192.168.1.4
• Standby Server: 192.168.1.3
• Operating System: Rocky Linux 8.6 (Green Obsidian)
• Database Version: EnterpriseDB Advanced Server (EPAS) 15
• Barman Version: 3.10.1

1. Replication Setup: Ensure that the primary and standby servers are configured correctly for streaming replication.
2. User Privileges: Create appropriate roles for Barman and replication operations.
3. SSH Key Setup: Configure passwordless SSH access between the Barman server and the primary/standby servers.

On the primary server, create roles for Barman:

CREATE ROLE barman WITH SUPERUSER PASSWORD 'barman' LOGIN;
CREATE ROLE streaming_barman WITH REPLICATION PASSWORD 'barman' LOGIN;

Ensure passwordless SSH access for the barman user from the Barman server to both the primary and standby servers:

On Barman:

ssh-keygen -t rsa
ssh-copy-id -i ~/.ssh/id_rsa.pub enterprisedb@192.168.1.4  # Primary
ssh-copy-id -i ~/.ssh/id_rsa.pub enterprisedb@192.168.1.3  # Standby

On Primary:

ssh-keygen -t rsa
ssh-copy-id -i ~/.ssh/id_rsa.pub barman@192.168.1.5  # Barman

On Standby:

ssh-keygen -t rsa
ssh-copy-id -i ~/.ssh/id_rsa.pub barman@192.168.1.5  # Barman

Edit Barman’s configuration file (/etc/barman.conf) to define the primary and standby servers. Example:

[standby-backup]
description = "Taking Backup from standby"
ssh_command = ssh enterprisedb@192.168.1.3
conninfo = host=192.168.1.3 user=barman dbname=edb 
streaming_conninfo = host=192.168.1.3 user=streaming_barman dbname=edb
wal_streaming_conninfo = host=192.168.1.4 user=streaming_barman dbname=edb 
streaming_archiver = on
archiver = off
slot_name = barmanslot
create_slot = auto
backup_method = rsync
backup_options = concurrent_backup
reuse_backup = link
path_prefix = "/usr/edb/as15/bin"

Run barman check:

[barman@backup barman]$ barman check standby-backup 
Server standby-backup:
	PostgreSQL: OK
	superuser or standard user with backup privileges: OK
	PostgreSQL streaming: OK
	wal_level: OK
	has monitoring privileges (WAL streaming): OK
	PostgreSQL streaming (WAL streaming): OK
	wal_level (WAL streaming): OK
	systemid coherence (WAL streaming): OK
	replication slot (WAL streaming): OK
	directories: OK
	retention policy settings: OK
	backup maximum age: OK (no last_backup_maximum_age provided)
	backup minimum size: OK (55.3 MiB)
	wal maximum age: OK (no last_wal_maximum_age provided)
	wal size: OK (16.0 MiB)
	compression settings: OK
	failed backups: OK (there are 0 failed backups)
	minimum redundancy requirements: OK (have 1 backups, expected at least 0)
	ssh: OK (PostgreSQL server)
	systemid coherence: OK
	pg_receivexlog: OK
	pg_receivexlog compatible: OK
	receive-wal running: OK
	archiver errors: OK

On Barman, initiate a full backup from the standby server:

barman backup standby-backup --wait

[barman@backup ~]$ barman backup standby-backup --wait
Starting backup using rsync-concurrent method for server standby-backup in /var/lib/barman/standby-backup/base/20240822T215403
Backup start at LSN: 0/F000148 (00000001000000000000000F, 00000148)
This is the first backup for server standby-backup
WAL segments preceding the current backup have been found:
	000000010000000000000009 from server standby-backup has been removed
	000000010000000000000006 from server standby-backup has been removed
	000000010000000000000007 from server standby-backup has been removed
	000000010000000000000008 from server standby-backup has been removed
	00000001000000000000000A from server standby-backup has been removed
	00000001000000000000000B from server standby-backup has been removed
	00000001000000000000000C from server standby-backup has been removed
	00000001000000000000000D from server standby-backup has been removed
	00000001000000000000000E from server standby-backup has been removed
Starting backup copy via rsync/SSH for 20240822T215403
Copy done (time: 2 seconds)
This is the first backup for server standby-backup
Asking PostgreSQL server to finalize the backup.
Backup size: 55.3 MiB. Actual size on disk: 55.3 MiB (-0.00% deduplication ratio).
Backup end at LSN: 0/F0001C0 (00000001000000000000000F, 000001C0)
Backup completed (start time: 2024-08-22 21:54:03.406589, elapsed time: 2 seconds)
Waiting for the WAL file 00000001000000000000000F from server 'standby-backup'

Ensure that Barman successfully performs the backup by checking the logs and verifying the backup on the Barman server.

Barman logs indicate that streaming is happening from the primary server and full backup is occurring from the standby server:

Streaming from Primary:

2024-08-22 22:00:10,101 [4489] barman.cli DEBUG: Initialised Barman version 3.10.1 (config: /etc/barman.conf, args: {'color': 'auto', 'quiet': False, 'debug': False, 'format': 'console', 'command': 'put-wal', 'server_name': 'standby-backup', 'func': <function put_wal at 0x7f78c384c0d0>})
2024-08-22 22:00:10,461 [4489] barman.server INFO: Received file '00000001000000000000000F' with checksum 'f4882c991dcf4027658ea2733671578f' by put-wal for server 'standby-backup' (SSH host: 192.168.1.4)

Backup from Standby:

2024-08-22 21:54:05,591 [4063] barman.command_wrappers DEBUG: Command: ['/usr/bin/rsync', '-e', "ssh 'enterprisedb@192.168.1.3' '-o' 'BatchMode=yes' '-o' 'StrictHostKeyChecking=no'", '--exclude=/pg_log/*', '--exclude=/log/*', '--exclude=/pg_xlog/*', '--exclude=/pg_wal/*', '--exclude=/global/pg_control', '--exclude=pgsql_tmp*', '--exclude=postgresql.auto.conf.tmp', '--exclude=current_logfiles.tmp', '--exclude=pg_internal.init', '--exclude=postmaster.pid', '--exclude=postmaster.opts', '--exclude=recovery.conf', '--exclude=standby.signal', '--exclude=pg_dynshmem/*', '--exclude=pg_notify/*', '--exclude=pg_replslot/*', '--exclude=pg_serial/*', '--exclude=pg_stat_tmp/*', '--exclude=pg_snapshots/*', '--exclude=pg_subtrans/*', '-rLKpts', '--delete-excluded', '--inplace', '--ignore-missing-args', '--itemize-changes', '--itemize-changes', ' :/var/lib/edb/as15/data/', '/var/lib/barman/standby-backup/base/20240822T215403/

List backup :

[barman@backup barman]$ barman list-backup standby-backup 
standby-backup 20240822T215403 - Thu Aug 22 21:54:05 2024 - Size: 71.3 MiB - WAL Size: 16.0 MiB

Run barman show-backup:

[barman@backup barman]$ barman show-backup standby-backup 20240822T215403
Backup 20240822T215403:
  Server Name            : standby-backup
  System Id              : 7403408056550895458
  Status                 : DONE
  PostgreSQL Version     : 150008
  PGDATA directory       : /var/lib/edb/as15/data

  Base backup information:
    Disk usage           : 55.3 MiB (71.3 MiB with WALs)
    Incremental size     : 55.3 MiB (-0.00%)
    Timeline             : 1
    Begin WAL            : 00000001000000000000000F
    End WAL              : 00000001000000000000000F
    WAL number           : 1
    Begin time           : 2024-08-22 21:54:03.405034+05:30
    End time             : 2024-08-22 21:54:05.993101+05:30
    Copy time            : 2 seconds
    Estimated throughput : 26.9 MiB/s
    Begin Offset         : 328
    End Offset           : 448
    Begin LSN            : 0/F000148
    End LSN              : 0/F0001C0

  WAL information:
    No of files          : 1
    Disk usage           : 16.0 MiB
    WAL rate             : 0.03/hour
    Last available       : 000000010000000000000010

  Catalog information:
    Retention Policy     : not enforced
    Previous Backup      : - (this is the oldest base backup)
    Next Backup          : - (this is the latest base backup)
[barman@backup barman]$ 

Verify backup status:

barman show-backup standby-backup <backup_id>/latest

NOTE:

• It is essential to verify backups by restoring them to ensure they are valid and can be used for recovery if needed. This step confirms the integrity and reliability of the backups created by Barman.

This setup allows for efficient management of backups by offloading the task to a standby server while ensuring that the primary server continues to handle WAL streaming. This method minimizes the performance impact on the primary server during backup operations, ensuring a stable and resilient PostgreSQL environment.

• SSH Issues: Ensure that all servers have proper SSH configurations and that keys are correctly distributed.
• Replication Delays: Monitor replication lag and adjust wal_keep_size or max_wal_senders if necessary.
• Barman Configuration: Regularly check Barman logs for any issues related to backups or WAL streaming.

This KB article provides a comprehensive guide to configuring Barman for full backups from a standby server with WAL streaming from the primary. By following these steps, you can ensure a reliable and efficient backup strategy for your PostgreSQL environment.

Reference link : Barman Manual