< Back to blog home PostgreSQL Zone

Standby re-synchronization using pgBackRest

Vincent O'Dea | January 17, 2025

The basics

When a switchover or failover happens, a standby is promoted as the new primary. When using Fujitsu Enterprise Postgres Mirroring Controller, the former primary is automatically fenced and will subsequently require reinstating back into the replication cluster as standby.

Reinstating a standby requires re-synching from the new primary, and for most people this involves a full restore from the new primary.

The problem here is that, for large databases, the time to perform a full restore may become excessive and of course add load to the primary.

As an alternative to a full restore, many people use pg_rewind, which can do a delta change recovery of the former primary, which is to be reinstated as standby. However pg_rewind limitations include requiring having already enabled data checksums or config parameter wal_log_hints.

If you would like to learn more about Mirroring Controller, you can check:

• Cluster Operation Guide - Database multiplexing mode
• Trunk-line article Ensuring business continuity: The critical role of Mirroring Controller in high-availability systems

These add overhead, and pg_rewind still fails if PGDATA files are not all writeable, which often contradicts with customer preferences for permissions on files like certificates or keys.

Alternatively you can use pgBackRest, which is already one of the most popular PostgreSQL backup/restore tools in the community and is shipped with Fujitsu Enterprise Postgres. When customers use pgBackRest for their backups and WAL archiving to a common repository, then it makes sense to use it to perform a delta re-instatement of a failed primary as a new standby instance.

The advantages for this approach are as follows:

• It does not require additional primary configuration parameter settings
• It does not require extra file permission changes
• It is is faster
• It does not add extra load to the primary as it pulls what it needs directly from the repo

Preliminary checks

Before starting, this is what you need to check:

• You don’t need to take an additional full backup for this.
• Make sure you have an existing full backup in the pgBackRest repo (use pgbackrest info to check), along with the necessary archived WAL.
• Make sure that archive_command is working using pgBackRest.
• Ensure that both hosts are using the same version of pgBackRest.

Reinstanting a failed server back to its role as primary

The below steps describe the procedure for the most common scenario, where we want to reinstate a failed former primary back as a synchronous standby, which is under control of Mirroring Controller.

1. Fail over to new primary hostB (former primary was hostA).
2. Shut down the database components on former primary hostA:

   $ mc_ctl stop -M /etc/mc

3. On the former primary hostA, back up the configuration settings to directory /tmp.

   $ cp $PGDATA/*.conf /tmp/

4. On the former primary hostA, recover the instance from the pgBackRest repo.

   If the database crashed, you may need to first remove postmaster.pid (use rm $PGDATA/postmaster.pid).

   $ pgbackrest --config=<pgbackrest_config_path>/pgBackRest.conf --log-level-file=info 
                --stanza=<stanza_name>  --delta  restore

   Don't worry if you notice that the TimeLine on hostA has gone back to the ID from the last full backup, as recovery followed by replication of the next WAL, will roll it forwards.

5. On the former primary hostA, create the recovery file.

   $ touch $PGDATA/standby.signal

6. On the new primary hostB, create the physical replication slot.

   SELECT * FROM pg_create_physical_replication_slot('slot');

7. On the new primary hostB, check the replication slot now exists.

   SELECT * FROM pg_replication_slots;

8. On hostA, copy the configuration you saved in /tmp.

   $ cp /tmp/*.conf  ${PGDATA}/

9. If you don't have the correct restore_command in postgresql.auto.conf or postgresql.conf, then add it accordingly.

   restore_command = 'pgbackrest --config=<pgbackrest_config_path>/pgBackRest.conf 
                                 --stanza=<stanza_name> archive-get %f "%p"'

10. If using Transparent Data Encryption, enable auto-open of the Transparent Data Encryption key file.

    $ pgx_keystore --enable-auto-open <keystore_folder>/keystore.ks

11. Start up the Fujitsu Enterprise Postgres instance on the former primary hostA (new standby).

    $ mc_ctl start -M /etc/mc

12. Check hostA instance is in recovery mode.

    SELECT * FROM pg_is_in_recovery();

13. Force a log switch on the primary hostB to see the timeline on hostA synchronize.

    SELECT pg_switch_wal();

14. Check the timelines are in sync on both hosts (hostA and hostB).

    $ pg_controldata | grep "Latest checkpoint.*s T"

15. Check the Mirroring Controller health on either host.

    $ mc_ctl status -M /etc/mc

Wrapping up

For the record, in these steps I was using pgBackRest 2.46 on Fujitsu Enterprise Postgres 15 SP1 (PostgreSQL 15.4), but the pgBackRest delta option has been around since at least v0.90, which was compatible with PostgreSQL 9.5, so versions should not be an obstacle.

Note that this approach also helps to routinely prove your pgBackRest backup strategy, which is a regular audit requirement for environments such as in financial organizations.

I wrote about a previous experience with a customer using pgBackRest in my Trunk-line article Proving a negative: A lesson learned.

---

For details on how integrate pgBackRest, check Integrating pgBackRest in Fujitsu Enterprise Postgres.