Contact us

News & Blog back

Subscribe
Filters

pgBackRest PITR in Docker: a simple demo

While moving production database workloads towards cloud-native (Kubernetes) environments has become very popular lately, plenty of users still rely on good old Docker containers. Compared to running PostgreSQL on bare metal, on virtual machines, or via a Kubernetes operator, Docker adds a bit of complexity, especially once you want to go beyond simple pg_dump / pg_restore for backups, upgrades, and disaster recovery.

A few years back, it was common to find open-sourced Dockerfiles from trusted companies bundling PostgreSQL with the extensions and tooling you needed. Most of those projects are now deprecated, as the ecosystem’s focus has shifted hard towards cloud-native patterns.

In many of my conversations with pgBackRest users, one theme comes up regularly: deploying pgBackRest for backups in Docker is straightforward, but restoring from those backups feels much harder. The usual advice was to “use a trusted Docker image and follow their guidelines”, but those images are mostly out-of-date now. These days you typically need to maintain your own image, and more importantly you need a reliable recovery playbook.

So I asked myself: how hard is point-in-time recovery (PITR) with pgBackRest for a PostgreSQL 18 Docker container? Turns out: not that hard, once you’ve seen it end-to-end.

This post is a small lab you can run locally. You’ll:

  • build a PostgreSQL 18 + pgBackRest image
  • take a full backup
  • create restore points
  • delete data on purpose
  • restore to the moment just before the delete

The tiny lab setup

The lab image is deliberately small. It just layers pgBackRest on top of the official PostgreSQL 18 image, adds a minimal config, and enables WAL archiving on first init.

Dockerfile

FROM postgres:18

# Install PGDG repository
RUN apt-get update && apt-get install -y \
wget \
gnupg \
lsb-release \
&& wget --quiet -O /usr/share/keyrings/postgresql-archive-keyring.asc https://www.postgresql.org/media/keys/ACCC4CF8.asc \
&& echo "deb [signed-by=/usr/share/keyrings/postgresql-archive-keyring.asc] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list \
&& apt-get update

# Install pgBackRest from PGDG repository
RUN apt-get install -y pgbackrest \
&& rm -rf /var/lib/apt/lists/*

# pgBackRest config
RUN mkdir -p /etc/pgbackrest
COPY pgbackrest.conf /etc/pgbackrest/pgbackrest.conf

# Enable archive_mode + archive_command on first initdb
RUN mkdir -p /docker-entrypoint-initdb.d && \
cat >/docker-entrypoint-initdb.d/pgbackrest-archive.sh <<'EOF'
#!/bin/bash
set -e

echo "archive_mode = on" >> "$PGDATA/postgresql.auto.conf"
echo "archive_command = 'pgbackrest --stanza=demo archive-push %p'" >> "$PGDATA/postgresql.auto.conf"
EOF
RUN chmod +x /docker-entrypoint-initdb.d/pgbackrest-archive.sh

USER postgres
EXPOSE 5432

A couple of points worth calling out:

  • We install pgBackRest from the PGDG APT repository, so we are not relying on distro packages that might lag behind.
  • The small init script dropped into /docker-entrypoint-initdb.d/ runs only on the first initdb. It flips archive_mode on and sets an archive_command that pushes WAL to pgBackRest. Without WAL archiving, PITR cannot work.
  • Everything else is standard Postgres image behaviour, so you keep the normal entrypoint and defaults.

pgbackrest.conf

[global]
repo1-type=posix
repo1-path=/var/lib/pgbackrest/repo1
repo1-retention-full=1
repo1-bundle=y
repo1-block=y
start-fast=y
delta=y
process-max=2
log-level-console=info
log-level-file=detail
compress-type=zst

[demo]
pg1-path=/var/lib/postgresql/18/docker

This config keeps things simple:

  • A single POSIX repo at /var/lib/pgbackrest/repo1.
  • repo1-retention-full=1 to avoid filling your disk during the lab.
  • Zstandard compression (compress-type=zst) to keep backups small and fast.
  • A single stanza called demo pointing at the default PGDATA path in the Postgres 18 image.

The image bundles PostgreSQL 18 and pgBackRest. For the sake of a simple local test, we will use a Docker volume as POSIX repository for pgBackRest backups and WAL archives. In a real setup you could switch to any pgBackRest repo type you like (for example S3), but POSIX is the quickest way to demonstrate the end-to-end flow.

Because we want both the database files and the backup repository to persist across container restarts, we will mount two named Docker volumes later on:

  • one for PGDATA
  • one for the pgBackRest repo

That gives us a repeatable lab where backups survive even if the container does not.

Build and run the container

1) Build the image

docker build -t pgbackrest-postgres-posix . --no-cache

2) Create the volumes

Two named volumes are used:

  • pg-data: PostgreSQL data directory
  • pgbr-repo: pgBackRest backup repository
docker volume create pgbr-repo
docker volume create pg-data

3) Run PostgreSQL + pgBackRest

docker run -d \
--name pg18-pgbackrest \
-e POSTGRES_PASSWORD=mysecretpassword \
-p 5432:5432 \
-v pg-data:/var/lib/postgresql \
-v pgbr-repo:/var/lib/pgbackrest \
pgbackrest-postgres-posix

4) Initialise pgBackRest and take a backup

A stanza is a pgBackRest concept that groups config + backups for one PostgreSQL cluster.

# Initialise pgBackRest stanza
docker exec pg18-pgbackrest pgbackrest --stanza=demo stanza-create

# Run a full backup
docker exec pg18-pgbackrest pgbackrest --stanza=demo backup --type=full

# List backups
docker exec pg18-pgbackrest pgbackrest --stanza=demo info

At this point you have a working full backup stored in your POSIX repo.

Testing point-in-time recovery

The PITR flow is:

  1. create restore point RP1
  2. insert important data
  3. create restore point RP2
  4. delete the data
  5. restore to RP2

1) Create a database and connect

docker exec -it pg18-pgbackrest psql -U postgres -c "CREATE DATABASE testdb;"
docker exec -it pg18-pgbackrest psql -U postgres -d testdb

2) Generate some test activity

Run the following in psql:

SELECT pg_create_restore_point('RP1');
BEGIN;
CREATE TABLE important_table (field text);
INSERT INTO important_table VALUES ('important data');
COMMIT;
SELECT field FROM important_table;

SELECT pg_create_restore_point('RP2');
BEGIN;
DELETE FROM important_table;
COMMIT;
SELECT field FROM important_table;
SELECT pg_switch_wal();

What this does:

  • RP1 is created before we touch the table
  • we insert a row with “important data“
  • RP2 is created after the insert but before the delete
  • we delete the data
  • pg_switch_wal() forces a WAL segment switch so the restore target is definitely archived

Now the database is in a “bad” state on purpose.

3) Restore to RP2 (before deletion)

Stop PostgreSQL first:

docker stop pg18-pgbackrest

Then run restore using a temporary container that mounts the same volumes:

docker run --rm \
-v pg-data:/var/lib/postgresql \
-v pgbr-repo:/var/lib/pgbackrest \
pgbackrest-postgres-posix \
pgbackrest restore --stanza=demo --delta --log-level-console=info \
--type=name --target=RP2 --target-action=promote

A quick breakdown of the flags:

  • --delta: restores only what differs, instead of wiping the directory first
  • --type=name --target=RP2: tells PostgreSQL that we want to recover up until the restore point named RP2
  • --target-action=promote: tells PostgreSQL to promote after recovery and start a new timeline

Start PostgreSQL again:

docker start pg18-pgbackrest

Verify the data is back:

docker exec -it pg18-pgbackrest psql -U postgres -d testdb -c "SELECT field FROM important_table;"

That’s PITR done: we recovered to the state just before the delete.

Test restore on a separate container

Even when we do not want to touch or erase our production system, it is important to test restoring backups regularly. That is the only way to validate that backups are usable and your recovery procedure actually works. With Docker, we can do this safely by restoring into a fresh volume and starting a second container that only has read-only access to the backup repository.

1) Create a fresh PGDATA volume

We restore into a brand new volume so we do not overwrite the main database volume.

docker volume create pg-test-restore-data

2) Restore the latest backup into that volume

Mount the backup repository as read-only (:ro) to avoid any accidental writes to your backups. We also disable archiving on this restored instance to prevent it from pushing WAL archives back to the production repo.

docker run --rm \
-v pg-test-restore-data:/var/lib/postgresql \
-v pgbr-repo:/var/lib/pgbackrest:ro \
pgbackrest-postgres-posix \
pgbackrest restore --stanza=demo --no-delta --log-level-console=info \
--archive-mode=off --target-timeline=current

Notes on the flags:

  • --no-delta restores into an empty directory.
  • --archive-mode=off stops the restored server from archiving WAL.
  • --target-timeline=current asks PostgreSQL to recover along the same timeline that was current when the backup was taken.

3) Start a temporary PostgreSQL container from the restored volume

We map container port 5432 to host port 5433 so it does not clash with the main container.

docker run -d \
--name pg18-pgbackrest-restored \
-e POSTGRES_PASSWORD=mysecretpassword \
-p 5433:5432 \
-v pg-test-restore-data:/var/lib/postgresql \
-v pgbr-repo:/var/lib/pgbackrest:ro \
pgbackrest-postgres-posix

4) Verify the restored data

Because we recovered the cluster following the initial backup timeline, PostgreSQL ignores the previous promote and you should see the table empty again.

docker exec -it pg18-pgbackrest-restored psql -U postgres -d testdb -c "SELECT field FROM important_table;"

5) Clean up the restore test

docker stop pg18-pgbackrest-restored
docker rm pg18-pgbackrest-restored
docker volume rm pg-test-restore-data

Final clean up

When you’re finished:

docker stop pg18-pgbackrest
docker rm pg18-pgbackrest
docker volume rm pgbr-repo
docker volume rm pg-data
docker rmi pgbackrest-postgres-posix

Takeaways

If you’re running PostgreSQL in Docker, pgBackRest is still a powerful backup and recovery option. The restore path is the part most people worry about, but the key steps are simple:

  • keep your data and your backups on persistent volumes
  • make sure WALs are archived (backups alone aren’t enough)
  • stop Postgres, restore from a throwaway container, then start again

After you’ve done it once, it’s not scary anymore and you now have a repeatable playbook for real incidents.

You may also like: