Percona Distribution for PostgreSQL 18 | pgBackRest Restore Issue with Patroni HA

PostgreSQL Percona Distribution for PostgreSQL
,
1

Hi

We have installed Percona Distribution for PostgreSQL 18 with High Availability (Patroni) in our test environment as part of our preparation for production.

Environment Overview
PostgreSQL: Percona Distribution for PostgreSQL 18
HA: Patroni (3-node cluster)
Backup Tool: pgBackRest
Additional Node: 1 dedicated node for pgBackRest backups

The PostgreSQL cluster was built successfully and is working fine. We then added an additional node specifically for pgBackRest to handle backups.

Backup Status
pgBackRest is configured successfully
Backups are completing without any issues

Issue During Restore
When we attempt to restore a backup, we encounter the following problem:
The percona-patroni service starts successfully
PostgreSQL does not fully start
The database remains stuck in the “starting up” state
PostgreSQL continuously waits for WAL files and does not progress further

Restore Command Used
sudo -iu postgres pgbackrest --stanza=cluster_1 --set=20251215-131545F restore

Error Messages Observed
2025-12-17 05:02:40.019 UTC [264813] FATAL: the database system is starting up
2025-12-17 05:02:40.023 UTC [264814] FATAL: the database system is starting up
2025-12-17 05:02:40.283 UTC [241849] LOG: waiting for WAL to become available at 0/B000098

Observation

It appears that after restore:

Patroni comes up
PostgreSQL waits indefinitely for WAL files
The restore process does not complete successfully

Request for Guidance
Could you please help us understand:

Whether there is a recommended restore procedure for pgBackRest when using Patroni-based HA clusters
Whether this behavior indicates a missing WAL archive or configuration issue
Any best practices for restoring backups in a Percona PostgreSQL HA setup

If required, we can share:
pgbackrest.conf
Patroni configuration
PostgreSQL logs
Backup info output

Thank you for your support.

2

HI,

We have installed Percona Distribution for PostgreSQL 18 with High Availability (Patroni) in our test environment as part of our preparation for production.

Understood

We then added an additional node specifically for pgBackRest to handle backups.

Okay so that means to me that you’ve created a host that is not under the control of Patroni, correct?

Is pgbackrest archiving WALs as a push or a pull mechanism?

When we attempt to restore a backup, we encounter the following problem...

Are restoring manually or have you configured Patroni to perform the restoration/provisioning of a new node?

PostgreSQL waits indefinitely for WAL files …

Error Messages Observed
2025-12-17 05:02:40.019 UTC [264813] FATAL: the database system is starting up
2025-12-17 05:02:40.023 UTC [264814] FATAL: the database system is starting up
2025-12-17 05:02:40.283 UTC [241849] LOG: waiting for WAL to become available at 0/B000098

The good news is that your problem is simple, you’re missing a WAL … so where is it?

select * from pg_walfile_name(‘0/B000098’);
pg_walfile_name

00000001000000000000000B

Back to you.

3

Hi Robert,

Thank you very much for responding to my query.

Let me explain my scenario clearly.

I have a 3-node Percona High Availability in PostgreSQL with Patroni and one separate pgBackRest backup node.

  1. I first took a full backup using pgBackRest.
  • At that time, my database contained tables:
    customers1 and customers21. After the backup completed, I created a new table customers3, which is not part of the full backup.
  1. Later, when I perform a full restore from pgBackRest, I observe that:
  • The customers3 table is also restored
  • This happens because PostgreSQL replays WAL files after the full backup

This behavior is technically correct, but in my case, I do NOT want customers3 to be restored.

4

Hi,

Understood, there are two steps:

  • Create a named marker record using function pg_create_restore_point just before the creation of your table.
  • Configure runtime parameter recovery_target_name on the REPLICA

Review the references that I’ve included below.

Happy holidays!

REFERENCES:

5

Hi,
Thank you for sharing the approach.
I tried the documented method, however it did not fully meet my requirements with Percona Server for PostgreSQL + Patroni in my environment, specifically for:

Backup control functions
Point-in-Time Recovery (PITR) target handling
recovery_target_name usage
Continuous archiving behavior

Based on these constraints, I implemented an alternative workflow that works reliably for my setup.

In my case, I first stop Patroni on all three nodes:
systemctl stop percona-patroni

Then I clean the data directory:
rm -rf /var/lib/postgresql/18/main/*

After that, I perform PITR using pgBackRest with a time-based target and pause action:
sudo -iu postgres pgbackrest
–stanza=cluster_1
–type=time
–target=“2026-01-28 00:04:45+05”
–target-action=pause
restore

Instead of starting Patroni immediately, I start PostgreSQL directly:
sudo -iu postgres /usr/lib/postgresql/18/bin/pg_ctl
-D /var/lib/postgresql/18/main
start

Once PostgreSQL is up, I manually promote the instance:
SELECT pg_promote();

After promotion, I start Patroni on the restored node and then bring up the remaining cluster nodes.

This approach satisfies my PITR and recovery control requirements.