repmgr, barman and wal's

[Paul B]

I need to understand the capabilities of using barman with repmgr, as I'm not sure I've got my head around how this works.

My aim is to have two postgres serves primary and standby.

I have configured them with repmgr using barman-wal-archive and restore

When I create a standby repmgr pulls from barman

What I don't understand is what happens on failover/switchover. Does barman then not carry out any backups until it fails back to the original primary?

Do I even configure barman with conninfo as I'm using barman-wal-archive?

What is the best practice to continue with backups after a failover? Can it follow the primary?

Config

 

repmgr.conf (obvious differences on each host to account for node name)

node_id=2
node_name='S00601'
conninfo='host=S00601 user=repmgr dbname=repmgr connect_timeout=2'
data_directory='/var/lib/postgresql/15/main'
barman_host='barman@S00602'
barman_server='pg'
failover='automatic'
follow_command='repmgr standby follow'
promote_command='repmgr standby promote'
restore_command='/usr/bin/barman-wal-restore S00602 pg %f %p'

service_start_command   = 'sudo systemctl start postgresql'
service_stop_command    = 'sudo systemctl stop postgresql'
service_restart_command = 'sudo systemctl restart postgresql'
service_reload_command  = 'sudo systemctl reload systemctl'
repmgrd_service_start_command = 'sudo systemctl start repmgrd'
repmgrd_service_stop_command = 'sudo systemctl stop repmgrd'
pg_bindir='/usr/lib/postgresql/15/bin'

postgresql.conf

archive_command = '/usr/bin/barman-wal-archive S00602 pg %p'
archive_mode = 'on'
hot_standby = 'on'
listen_addresses = '*'
max_replication_slots = 10
max_wal_senders = 10
shared_preload_libraries = 'repmgr'
wal_level = 'replica'
wal_log_hints = 'on'

barman.conf

[pg]
description =  "PostgreSQL Database (Streaming-Only)"
conninfo = host=192.168.121.56 port=5432 user=barman dbname=postgres
streaming_conninfo = host=192.168.121.56 port=5432 user=streaming_barman
backup_method = postgres
streaming_archiver = on
slot_name = barman
create_slot = auto
#minimum_redundancy = 4
archiver = on

Thankful for any responses.

[Paul B]

Not sure if this is a good idea, but it seems to work.

Failing Over Barman

Barman needs to be told that the primary has changed, or it will fail to continue doing backups.

There is a feature of postgres that will trigger a script hook when a recovery is completed - recovery_end_command.

I have used this to ssh onto the barman server and run a command to change the config file so that it points to the new primary.

postgresql.conf

recovery_end_command = 'ssh S00602 /usr/local/bin/recovery_end --host S00600'

For this to work, it must be able to ssh onto the barman server as the barman user with no password prompt. If it is not able to do this then the switchover will fail and the postgres server will not be started.

Ensure this is able to log on by adding a ~/.ssh/config file for the postgres user, eg.

Host S00602 

   User barman

/usr/local/bin/recovery_end

#!/bin/bash

function render_template() { 

 eval "echo \"$(cat $1)\""

}
while [ $# -gt 0 ]; do 

  case "$1" in 

  -h|--host) 

    shift; 

    PGHOST=$1 

    ;;
  *)
    exit 1
    ;;

  esac 

  shift

done
render_template /etc/barman.d/pg.template > /etc/barman.d/pg.conf
barman cron

It processes this template file and overwrites the pg.conf

[pg]

description = "PostgreSQL Database"

conninfo = host=${PGHOST} port=5432 user=barman dbname=postgres

streaming_conninfo = host=${PGHOST} port=5432 user=streaming_barman

backup_method = postgres

streaming_archiver = on

slot_name = barman

create_slot = auto

#minimum_redundancy = 4

archiver = on

retention_policy = RECOVERY WINDOW OF 7 DAYS