Delete the Database, Then Time the Restore: A pgBackRest Recovery Drill

We ran delete from orders where id % 2 = 0 against a 105,000-row table, threw away the Postgres data volume, and asked pgBackRest for the database as it stood just before the delete. It came back with all 105,000 rows. Five thousand of them were written after the last backup and existed only in the write-ahead log (WAL) archive. The restore command took 2,707 ms.

A Postgres backup isn’t a backup until a timed restore of it has succeeded. If your only backup is a nightly pg_dump, a restore can only put you back where you were when the dump started; WAL archiving is what lets you pick the moment you return to.

This Postgres backup restore drill ran in a lab: one container on Docker Desktop for Windows 11, PostgreSQL 17.11, pgBackRest 2.59.3, and a 29.4 MB cluster. A restore that small doesn’t predict how long yours will take at production size. We built and ran it on October 13, 2026, and every timing below comes from that run’s log.

The lab: one container, two volumes

The image is postgres:17 with pgBackRest installed through apt-get. On the day we built it, that tag resolved to PostgreSQL 17.11.

FROM postgres:17
RUN apt-get update \
 && apt-get install -y --no-install-recommends pgbackrest \
 && rm -rf /var/lib/apt/lists/* \
 && mkdir -p /var/lib/pgbackrest /var/log/pgbackrest /etc/pgbackrest \
 && chown -R postgres:postgres /var/lib/pgbackrest /var/log/pgbackrest
COPY pgbackrest.conf /etc/pgbackrest/pgbackrest.conf

The pgBackRest configuration:

[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
start-fast=y
log-level-console=info

[demo]
pg1-path=/var/lib/postgresql/data

PostgreSQL’s archiving needs three settings: wal_level at replica or higher, archive_mode on, and an archive_command that returns zero only when the segment really has been archived.^[1] Compose sets all three, with pgBackRest’s archive-push as the command.

services:
  db:
    build: .
    environment:
      POSTGRES_PASSWORD: lab
    command:
      - postgres
      - -c
      - archive_mode=on
      - -c
      - archive_command=pgbackrest --stanza=demo archive-push %p
      - -c
      - wal_level=replica
    volumes:
      - pgdata:/var/lib/postgresql/data
      - pgrepo:/var/lib/pgbackrest
volumes:
  pgdata:
  pgrepo:

pgdata holds the cluster; pgrepo holds the backups and the archived WAL. Our disaster was losing pgdata and keeping pgrepo. In the lab both volumes sat on one disk, so this repository guards against a deleted volume and nothing worse.

The drill, with the clock running

Here’s the drill as one script. We ran these commands one at a time on October 13; the script is the same sequence. The target’s format string and the || exit 1 after the restore are explained in the two sections below.

#!/usr/bin/env bash
# Point-in-time restore drill: back up, write more rows, record a target time, delete rows,
# destroy the data volume, restore to the target, verify the row counts.
set -u
C="docker compose -p pglab"
PG="$C exec -T -u postgres db"

$C build
$C up -d
until $PG pg_isready -q; do sleep 2; done

$PG pgbackrest --stanza=demo stanza-create
$PG pgbackrest --stanza=demo check

$PG psql -q -c "create table orders(id serial primary key, note text, created timestamptz default now());
                insert into orders(note) select 'order '||g from generate_series(1,100000) g;"
$PG psql -At -c "select count(*) from orders"                       # 100000

$PG pgbackrest --stanza=demo backup --type=full

$PG psql -q -c "insert into orders(note) select 'late order '||g from generate_series(1,5000) g;"
$PG psql -At -c "select count(*) from orders"                       # 105000

# Recovery target: the instant before the mistake. Keep the offset attached to the seconds
# (...USTZH); a space before it ("... 347165 +00") made pgBackRest fail with error [029].
TARGET=$($PG psql -At -c "select to_char(now(),'YYYY-MM-DD HH24:MI:SS.USTZH')")

$PG psql -q -c "delete from orders where id % 2 = 0;"
$PG psql -At -c "select count(*) from orders"                       # 52500
$PG psql -q -c "select pg_switch_wal();"                           # push the latest WAL into the archive

# Disaster: stop, delete the data volume, keep the repository volume.
$C down
docker volume rm pglab_pgdata

# Restore. Stop here if it fails; never start the server on an empty data volume.
$C run --rm --no-deps -T -u postgres --entrypoint pgbackrest db \
  --stanza=demo --type=time "--target=$TARGET" --target-action=promote restore || exit 1

$C up -d
until $PG pg_isready -q; do sleep 1; done
$PG psql -At -c "select count(*), count(*) filter (where note like 'late order%') from orders"   # 105000|5000
$PG psql -At -c "select pg_is_in_recovery()"                                                     # f

The 100,000 starting rows carry an order note, and the 5,000 written after the backup carry late order, so one count query can tell them apart. The full backup’s log:

2026-10-13 20:24:36.426 P00   INFO: new backup label = 20261013-202432F
2026-10-13 20:24:36.467 P00   INFO: full backup size = 29.4MB, file total = 972
2026-10-13 20:24:36.467 P00   INFO: backup command end: completed successfully (4250ms)

That’s 4,250 ms by pgBackRest’s own timer and 5,241 ms wrapped in docker compose exec. The repository holds the 29.4 MB cluster in 3.8 MB. After pg_switch_wal(), pgbackrest info showed status: ok and an archive running from 000000010000000000000001 to 000000010000000000000005.

--type=time recovers to the time given in --target.^[2] PostgreSQL’s default recovery_target_action is pause, which leaves the server waiting for someone to decide.^[3] That suits a person checking the data by hand. A script wants promote, which finishes recovery and starts accepting connections.

The restore wrapper took 4,327 ms, 2,707 of them inside pgBackRest, and Postgres accepted connections 1,607 ms after up. From an empty volume to a server answering queries: 5,934 ms. Then the count:

== rows after restore (expect 105000 total, 5000 late)
105000|5000
in recovery: f

All 105,000 rows, the 5,000 late ones among them, and pg_is_in_recovery() false because the server promoted. The delete was gone.

The second number matters as much as the first. A count of 100,000 would mean the restore stopped at the base backup and replayed none of the archived WAL. Only 105000|5000 shows the archive carried the cluster past the backup and up to the target.

Troubleshooting: pgBackRest error [029] on a time target

If restore --type=time stops with this, the problem is the target string:

2026-10-13 20:25:02.385 P00  ERROR: [029]: automatic backup set selection cannot be performed with provided time '2026-10-13 20:24:40.347165 +00'
2026-10-13 20:25:02.385 P00   INFO: restore command end: aborted with exception [029]

The message says nothing about formatting, so the natural next step is hunting for a missing backup. In our lab the string above came from to_char(now(),'YYYY-MM-DD HH24:MI:SS.US TZH'), which puts a space before the offset. The same instant written 2026-10-13 20:24:40.347165+00, from the pattern 'YYYY-MM-DD HH24:MI:SS.USTZH', restored cleanly. Generate the target in UTC with the offset attached to the seconds, and use that exact form in the drill.

We didn’t isolate which part of the first string pgBackRest rejects. Its own documentation shows an example with a space before a zone abbreviation (2015-01-30 14:15:11 EST), so the space may not be the whole story. If the format is right and you still see [029], the other requirements come from PostgreSQL’s recovery rules: the target has to fall after the base backup finished, and the archive has to hold an unbroken run of WAL from the start of that backup.^[1:1] PostgreSQL prefers a numeric UTC offset over a zone abbreviation for recovery_target_time.^[3:1]

Hazard: a failed restore followed by up starts an empty cluster

If the restore step fails and the script carries on to docker compose up, the container starts against an empty data volume. The image’s documentation says its initialization only happens when the container starts with an empty data directory, and that any existing database is left untouched.^[4] So it runs initdb, and the server comes up healthy with none of your data:

== start and wait until it accepts connections
 Container pglab-db-1  Started
[ready after 7451 ms]
== rows after restore (expect 105000, the state just before the delete)
ERROR:  relation "orders" does not exist
LINE 1: ... count(*) filter (where note like 'late order%') from orders
                                                                 ^
== is it in recovery or promoted?
f

pg_is_in_recovery() returns f for the empty cluster and for a correctly promoted restore, and pg_isready can’t tell them apart. The one visible difference in our lab was startup time: 7,451 ms for the empty cluster against 1,607 ms after the real restore. It’s the green-checkmark problem from The Backup Illusion in Postgres form: the server was up, out of recovery, and missing its orders table.

Two rules follow:

• A failed restore ends the drill. Nonzero exit, stop, no up.
• The pass condition is a row count recorded before the disaster. “The server came up” was true of the empty cluster too.

Does the stray cluster damage the repository? In our lab it didn’t. It ran with an archive_command pointed at the same stanza, and pgbackrest info still showed status: ok and the same full backup. The newest archived segment had moved from 000000010000000000000005 to 000000010000000000000006. In a separate rerun, a clean stop and start of the original cluster moved the newest segment from ...04 to ...05 with no second cluster involved, so the move is consistent with the original cluster’s shutdown, not with the empty one. To recover, delete the volume again and run the restore with the fixed target.

What pg_dump can’t restore

All of this rests on WAL archiving. A pg_dump is “a snapshot of the database at the time pg_dump began running,”^[5] and the docs say dumps “cannot be used as part of a continuous-archiving solution” because they don’t carry enough information for WAL replay.^[1:2] A physical base backup plus continuously archived WAL is what restores a cluster “to its state at any time since your base backup was taken,” provided the archive is an unbroken sequence back to the start of that backup and the target falls after it finished.^[1:3] A dump taken alongside our base backup would’ve come back without the 5,000 late orders. If a nightly dump is your only backup, you’ve decided to lose everything since the last dump started: a day if last night’s ran, more if nobody checked. The 3 AM phone call walks through how that discovery goes on SQL Server.

What the lab didn’t cover

We didn’t exercise --delta, which restores into an existing data directory using checksums, where a plain restore expects the directory to be present but empty.^[2:1] Retention, encryption, cloud object-store repositories and backups taken from a standby were out of scope. So was PostgreSQL 18, whose image keeps its data in /var/lib/postgresql/18/docker and declares its volume at /var/lib/postgresql, so the mount paths in our files would change.^[4:1]

Schedule the Postgres backup restore drill

Put it on a calendar: monthly at minimum, and again after any upgrade of PostgreSQL or pgBackRest. Run it at production size, on the hardware you’d actually restore to. That applies to whatever the cluster holds, including a PPDM database on the platform we weighed against SQL Server.

Restore onto a volume other than the one you’re pretending to lose. If the restore goes wrong, the copy you’d fall back on is still there.

Log three times on every run: pgBackRest’s own restore time, the time until Postgres accepts connections, and the total from empty volume to a server accepting connections. Ours were 2,707 ms, 1,607 ms and 5,934 ms. A restore time someone measured last month tells you whether your recovery time objective (RTO) holds; a policy that was never run doesn’t.

---

Get in touch

---

1. PostgreSQL Global Development Group, “25.3. Continuous Archiving and Point-in-Time Recovery (PITR),” PostgreSQL 17 Documentation. https://www.postgresql.org/docs/17/continuous-archiving.html ↩︎¹ ↩︎² ↩︎³ ↩︎⁴

2. pgBackRest, “Command Reference,” restore command (--type, --target, --delta). https://pgbackrest.org/command.html ↩︎¹ ↩︎²

3. PostgreSQL Global Development Group, “19.5. Write Ahead Log,” section “19.5.6. Recovery Target” (recovery_target_time, recovery_target_action), PostgreSQL 17 Documentation. https://www.postgresql.org/docs/17/runtime-config-wal.html ↩︎¹ ↩︎²

4. Docker Hub, “postgres” Docker Official Image documentation (retrieved October 13, 2026). https://hub.docker.com/_/postgres ↩︎¹ ↩︎²

5. PostgreSQL Global Development Group, “25.1. SQL Dump,” PostgreSQL 17 Documentation. https://www.postgresql.org/docs/17/backup-dump.html ↩︎