[pgpool-general: 7719] Re: PGpool "recovery_1st_stage" script and integration with Barman wal archiver

[Láznička Vladimír]

Hello Luca,

We don’t use the Barman tool for PostgreSQL backups, but we are using pgBackRest which requires similar “archive_mode” parameter:

archive_command = 'pgbackrest --stanza=<stanza_name> archive-push %p'

We have decided to directly stream WAL records (with a replication slot) from the PRIMARY PostgreSQL node to STANDBYs instead of relying on archived WAL files. That way the functionality is separated – pgBackRest/Barman would gather archived WAL files as an offline backup, while the online recovery from pgpool and PRIMARY-STANDBY replication process in general would utilize a replication slot to stream WAL records directly.

In our “recovery_1st_stage” script it looks like this:

# Create a replication slot on the PRIMARY with node ID used as an identifier of the specific slot.
$PG_UTILS_DIR/psql -p $SOURCE_PORT -c "SELECT pg_create_physical_replication_slot('$TARGET_NODE');"

…
…
…

# Use pg_basebackup on the STANDBY to restore the database from the PRIMARY (with streaming option enabled).
ssh -T $TARGET_OS_USER@$TARGET_HOST "$PG_UTILS_DIR/pg_basebackup -h $SOURCE_HOST -p $SOURCE_PORT -U $REPL_USER -D $TARGET_PGDATA -T $SOURCE_TABLESPACE_AISG_DATA=$TARGET_TABLESPACE_AISG_DATA -T $SOURCE_TABLESPACE_AISG_IDX=$TARGET_TABLESPACE_AISG_IDX -S $TARGET_NODE -F p --waldir=$TARGET_PGWAL -X s -c fast"

…
…
…

# Create a recovery.conf file (we are using PostgreSQL 11, in PostgreSQL 12 you would directly edit the postgresql.conf of the STANDBY), the replication will use the created replication slot.
ssh -T $TARGET_OS_USER@$TARGET_HOST "
cd $TARGET_PGDATA

cat > recovery.conf << EOT
standby_mode = on
primary_conninfo = 'host=$SOURCE_HOST port=$SOURCE_PORT user=$REPL_USER application_name=$TARGET_HOST'
primary_slot_name = '$TARGET_NODE'
EOT
"
…
…
…

Then you just start the STANDBY with the “remote_start” script.

If you want to stick with archived WALs from Barman, you would have to edit the “restore_command“ to use whatever command Barman uses (check the Barman documentation) to restore WAL changes after restoring the base backup. You would probably also use some specific Barman command to restore the base backup instead of using directly “pg_basebackup” (again, look up the Barman documentation), so the backups you use for the recovery are consistent with Barman.

Hope this helps you.

With regards,
Vladimír Láznička

From: pgpool-general <pgpool-general-bounces at pgpool.net> On Behalf Of Luca Maranzano
Sent: Sunday, September 26, 2021 3:18 PM
To: pgpool-general <pgpool-general at pgpool.net>
Subject: [pgpool-general: 7716] PGpool "recovery_1st_stage" script and integration with Barman wal archiver

Hi all,

we setup a 3 nodes cluster with PGPool-II version 4.2.3 on 3 physical servers with CentOS 7 and PostgreSQL 12.8, like described here:
https://www.pgpool.net/docs/latest/en/html/example-cluster.html

The above procedure says to configure archiving with this command:

archive_command = 'cp "%p" "/var/lib/pgsql/archivedir/%f"'
but since we setup also Barman to perform Backup with Zero Data loss, the archive commandmust be this one:
archive_command = '/usr/bin/barman-wal-archive  BARMAN_HOST SERVER_NAME  %p'

so WALs get archived continuously by the barman process.

In the "recovery_1st_stage" script that is used by pgpool to implement failover and restore, there are these references to the Archivedir:
ARCHIVEDIR=/pgprdarch
    rm -rf $ARCHIVEDIR/*
restore_command = 'scp ${PRIMARY_NODE_HOST}:${ARCHIVEDIR}/%f %p'

So my question is: which is the correct way to integrate Barman Wal archiving with PGPool-II recovery feature?

Thanks in advance
Regards
Luca





-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20210927/097a2b26/attachment-0001.htm>