Articles › Backups

Point-in-time restore of a Patroni cluster with pgBackRest (tested)

Published 5 October 2026 · 6 min read · Tested on a 3-node Patroni cluster · Patroni 4.1.5, etcd 3.6.15, PostgreSQL 16.15, pgBackRest 2.50

Someone ran DROP TABLE on production. You have pgBackRest backups and a WAL archive, so the data can come back. The hard part is not pgBackRest. It's Patroni, which will undo your restore if you do it underneath a running cluster. Here is the procedure we tested: 1,000 rows written, the table dropped, the cluster restored to the moment before the drop, all 1,000 rows back, replicas rebuilt, and ha-check reporting a healthy cluster.

First: do you need a restore at all?

A point-in-time restore replaces the cluster's current data. Everything written after the target time is gone, including legitimate writes made after the mistake. That is a business decision, not a DBA one: get it in writing before you start. If one table was damaged and the rest of the database is still taking writes, the last row of the table is usually the better answer.

Why you can't restore into the running cluster

The naive approach is to stop PostgreSQL on one node, run pgbackrest restore, and start it again. Patroni is still running, and etcd still holds its view of the cluster: who the leader is, and which timeline it's on.

Restore on a replica and Patroni treats the node as a replica that has drifted. It will try to make it follow the current leader, the one with the dropped table, or re-clone it from that leader and throw your restore away. Restore on the leader and the other nodes still carry the damaged history, and you end up with nodes competing for the leader key.

So the order matters. Stop Patroni everywhere, clear the cluster's state in etcd, restore one node, let it take the leader lock as if it were a new cluster, then rebuild the others from it.

The procedure

1. Find the target time. Take it from the application log or the PostgreSQL log, just before the bad statement. Always include a time zone:

TARGET="2026-09-26 14:06:23+00"

2. Stop Patroni on all nodes, replicas first, leader last. On pg3, then pg2, then pg1:

systemctl stop patroni

3. Clear the cluster state in etcd. From any node:

patronictl -c /etc/patroni/patroni.yml remove pg-ha

It asks you to type the cluster name, then Yes I am aware. This deletes the cluster's keys in etcd. It does not touch any data directory.

4. Restore on one node, keeping the old data. pgBackRest restores into an empty directory, so move the current one aside instead of deleting it. It's your way back.

mv /var/lib/postgresql/16/main /var/lib/postgresql/16/main.before-restore
install -d -o postgres -g postgres -m 700 /var/lib/postgresql/16/main
sudo -u postgres pgbackrest --stanza=pg-ha --type=time "--target=$TARGET" \
    --target-action=promote restore

5. Let PostgreSQL finish recovery, check, then stop it. Start PostgreSQL by hand, without Patroni:

sudo -u postgres /usr/lib/postgresql/16/bin/pg_ctl -D /var/lib/postgresql/16/main -w -t 3600 start
sudo -u postgres psql -c "select pg_is_in_recovery()"

f means recovery finished and the server promoted itself. t means it is still replaying WAL: wait and ask again. Then run a sanity query on the data you came for, such as select count(*) from orders, and stop the server:

sudo -u postgres /usr/lib/postgresql/16/bin/pg_ctl -D /var/lib/postgresql/16/main -w stop -m fast

6. Start Patroni on the restored node.

systemctl start patroni

It finds no cluster in etcd and a valid data directory, so it takes the leader lock. patronictl list shows pg1 as Leader, running, on a new timeline.

7. Rebuild the other nodes, pg2 then pg3. On each, move the old data directory aside, recreate the empty one with the same install -d command, and start Patroni. It clones a fresh copy from the new leader.

8. Take a full backup immediately, then check the cluster.

sudo -u postgres pgbackrest --stanza=pg-ha --type=full backup

Then run ha-check against the cluster. Delete the main.before-restore directories once you're sure you won't need them.

Common mistakes

• No time zone in the target. A bare 2026-09-26 14:06:23 is read in the server's time zone setting, which may not be the zone of the log you took it from. An hour's difference either loses good data or brings the bad statement back.
• Forgetting --target-action=promote. pgBackRest's default is pause: PostgreSQL stops at the target and waits, still in recovery and read-only. pg_is_in_recovery() keeps returning t, and it looks as if the restore hung.
• Starting Patroni before recovery ends. Check for f in step 5 first. Step 6 relies on a finished, promoted data directory; a node still replaying WAL is not that.
• Not taking a fresh full backup. The promotion starts a new timeline. Backups taken after the target time belong to the old branch, which the cluster no longer follows. Whether an older backup can later be replayed across the timeline switch depends on your archive and settings; check it in a lab rather than rely on it. A full backup straight away removes the question.
• Deleting the old data directory early. Keep main.before-restore until the application owners have confirmed the data. It's the only copy of anything written after the target time.

Rehearse it before you need it

The first time you run this should not be during an incident, with someone waiting for the table to come back. Rehearse monthly in a lab cluster: write rows, drop the table, restore, count.