The problem
Take an online shop split into Symfony services:
order, stock,
shipping and billing. Each service
has its own PostgreSQL database, in its own container, on
its own volume: one database to run per service.
The operations plan often fits in one sentence: "we have
dumps". A pg_dump goes to some storage every
night, and nobody has ever restored it. The day a Doctrine
migration drops one column too many in the
order database, three questions have no answer.
- What do we lose? Everything written since the last dump: up to a day of orders.
- How long to get back? Nobody has ever timed a restore.
- Is the dump usable? A file being there is not a successful restore.
The rest has no tool either: why is stock reservation
getting slower? Which index is missing? Who granted
DELETE on the customer table to an
application account?
The options
Scheduled logical dumps
-
What they solve: a readable copy,
portable from one major version to another and from one
system to another.
pg_dumpdoes not depend on the system's sort order. - What they cost: everything after the last dump is lost, and the restore replays all the SQL, indexes included. No return to a precise time, and nothing for monitoring or auditing.
A managed PostgreSQL service
- What it solves: backups, point-in-time recovery, metrics and version upgrades are provided.
- What it costs: it is not always possible, when the data must stay inside a zone or with a given host. Its cost follows the number of databases, and the restore test is still yours to write.
One open-source tool per need, around each database
- What it solves: one specialised, documented tool per need, enabled database by database, on the same hosting as the services.
- What it costs: several tools to keep up to date, extensions to preload, an image to build. Barman and WAL-G are serious alternatives to pgBackRest; the approach does not change.
Our recommendation
Four needs, one open-source tool per need, and a restore tested every day without anyone stepping in.
-
Monitor, with pgwatch. A collector reads
the statistics views of each database with a
pg_monitorrole. It stores the metrics (PostgreSQL, TimescaleDB or Prometheus), and Grafana displays them. -
Understand slow queries, with PoWA. It
archives the statistics of
pg_stat_statements, the predicates ofpg_qualstatsand the waits ofpg_wait_sampling. Its index suggestions are tested by HypoPG, with hypothetical indexes: nothing is created on the database. - Trace access, with pgaudit. DDL and changes to roles and privileges are logged. Writes on a sensitive table are logged as soon as the audit role is granted write access to it.
- Back up to return to a point in time, with pgBackRest. Full, differential and incremental backups, plus continuous WAL archiving. Going back to 10:41, just before the faulty migration, becomes one command.
-
Test the restore. A script restores the
latest backup into a throwaway instance and replays the
WAL. It runs a business query then
pg_amcheck, and a failure raises an alert like a missed backup.
The stack is opt-in: one more Compose file
per service, compose.ops.yml, plugs it into its
database. The shared tools run only once: the pgwatch
collector, the PoWA repository and UI, the pgBackRest
repository. They reach each database through
a network alias, order-database or
stock-database: database may join
the ops network, because only these tools run
on it.
The trade-offs we accept
A mandatory Debian image. The PGDG packages
of the extensions exist for Debian: the tooled image starts
from postgres:18.7-trixie. It never mounts a
volume initialised by the Alpine image, because musl and
glibc do not sort text the same way.
B-tree indexes on text would become wrong, and searches
would miss existing rows. In development, a fresh volume
filled by the Doctrine migrations is enough. With real data,
we go through pg_dump, which the sort order
does not affect.
Preloaded extensions.
shared_preload_libraries only changes on
restart, and each extension costs some memory and CPU. In
production, we keep the default sampling of
pg_qualstats: one query out of
max_connections.
Broad privileges for PoWA. For its
suggestions, PoWA has the queries planned under hypothetical
indexes. Its documentation asks for SELECT on
the tables and on pg_statistic; without them,
no suggestion. pg_read_all_data grants
everything at once, and in production we prefer targeted
GRANT SELECT.
A best-effort audit. pgaudit writes to the PostgreSQL log, without transactional guarantees according to its documentation. The logs go to central, write-only storage.
A repository and a key to protect. A repository on the database host disappears with it. In production, it goes to S3, Azure Blob or GCS. The encryption key is stored like a production secret: without it, no restore at all.
A partial check.
pg_amcheck checks tables and B-tree indexes,
and skips other index types. Each test also takes the space
of a copy of the database.
The signal that should make us revisit this choice: dozens of databases to equip. A shared instance, with one database and one role per service, then cuts the number of collectors, stanzas and tests.
Implementation
The examples use PostgreSQL 18.7, pgBackRest 2.60, pgaudit 18.0, PoWA 5.3 and pgwatch 6, with Docker Compose.
1. A tooled image
# ops/postgres/Dockerfile
# Debian (glibc): never on a volume initialised by an Alpine image (musl).
FROM postgres:18.7-trixie
RUN apt-get update \
&& apt-get install -y --no-install-recommends pgbackrest \
postgresql-${PG_MAJOR}-pgaudit postgresql-${PG_MAJOR}-powa \
postgresql-${PG_MAJOR}-pg-qualstats postgresql-${PG_MAJOR}-pg-stat-kcache \
postgresql-${PG_MAJOR}-pg-wait-sampling postgresql-${PG_MAJOR}-pg-track-settings \
postgresql-${PG_MAJOR}-hypopg \
&& rm -rf /var/lib/apt/lists/* \
&& install -d -o postgres -g postgres /var/lib/pgbackrest /var/log/pgbackrest /var/spool/pgbackrest
The official Debian image already declares the PGDG
repository, and PG_MAJOR comes from it.
2. Plugging in a database
The usual order database runs on
postgres:18.7-trixie, with its volume mounted
on /var/lib/postgresql. The overlay changes its
image and settings, and adds a backup sidecar.
# services/order/compose.ops.yml
services:
database:
build: ../../ops/postgres
image: app-local/postgres-ops:18.7
command:
- postgres
- -c
- shared_preload_libraries=pg_stat_statements,pg_stat_kcache,pg_qualstats,pg_wait_sampling,pgaudit
- -c
- pgaudit.log=ddl,role
- -c
- pgaudit.log_catalog=off
- -c
- pgaudit.role=auditor
- -c
- archive_mode=on
- -c
- archive_command=pgbackrest --stanza=order archive-push %p
- -c
- archive_timeout=60
environment:
PGBACKREST_REPO1_CIPHER_PASS: ${PGBACKREST_CIPHER_PASS:?required}
networks:
default: {}
ops:
aliases: [order-database]
volumes:
- database_socket:/var/run/postgresql
- pgbackrest_repo:/var/lib/pgbackrest
# Default path: every pgbackrest call finds it without an option.
- ../../ops/postgres/pgbackrest.conf:/etc/pgbackrest/pgbackrest.conf:ro
backup:
build: ../../ops/postgres
image: app-local/postgres-ops:18.7
entrypoint: [sh, /ops/backup-loop.sh]
user: postgres
network_mode: none # talks to its database over the socket
environment:
PGBACKREST_STANZA: order
PGBACKREST_REPO1_CIPHER_PASS: ${PGBACKREST_CIPHER_PASS:?required}
PGUSER: app
volumes_from: [database]
volumes:
- ../../ops/postgres/scripts:/ops:ro
depends_on:
database:
condition: service_healthy
restart: unless-stopped
volumes:
database_socket:
pgbackrest_repo:
external: true
name: ops-pgbackrest-repo
networks:
ops:
external: true
docker compose -f compose.yml -f compose.ops.yml up
-d
turns the stack on; removing it first requires a
DROP EXTENSION pgaudit, whose event triggers
would otherwise block any DDL. On an Alpine database, the
overlay would replace its mounts with a fresh volume (volumes: !override).
No pull_policy: build: Compose would rebuild
the image on every up. With the containerd
image store, BuildKit attaches a dated provenance
attestation by default. Even from cache, the image gets a
new ID and Compose recreates the database: after a
Dockerfile change, up -d --build is enough.
3. Technical roles, no superuser
-- ops/postgres/init-ops.sql (once, by the superuser of the app database)
CREATE ROLE pgwatch LOGIN PASSWORD :'pgwatch_password';
GRANT pg_monitor TO pgwatch;
CREATE ROLE powa LOGIN PASSWORD :'powa_password';
GRANT pg_read_all_stats TO powa;
GRANT pg_read_all_data TO powa; -- index suggestions read the tables
CREATE ROLE auditor NOLOGIN; -- target of pgaudit's object audit
GRANT INSERT, UPDATE, DELETE ON customer TO auditor;
CREATE EXTENSION IF NOT EXISTS pgaudit;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pg_qualstats;
CREATE EXTENSION IF NOT EXISTS hypopg;
Every write on customer is then logged. PoWA
also needs a powa database on the instance,
with its extensions, and a call to
powa_register_server() in its repository.
4. Backing up continuously
# ops/postgres/pgbackrest.conf (the encryption passphrase comes from the environment)
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-cipher-type=aes-256-cbc
compress-type=zst
start-fast=y
[order]
pg1-path=/var/lib/postgresql/18/docker
pg1-user=app
[stock]
pg1-path=/var/lib/postgresql/18/docker
pg1-user=app
#!/bin/sh
# ops/postgres/scripts/backup-loop.sh
set -eu
until pg_isready -h /var/run/postgresql -q; do sleep 2; done
# After a major upgrade, the stanza records the new version.
pgbackrest stanza-create || pgbackrest stanza-upgrade
pgbackrest check
while true; do
case "$(date +%u%H)" in
701) type=full ;; # Sunday, 1 a.m.
?01) type=diff ;; # every day, 1 a.m.
*) type=incr ;;
esac
pgbackrest --type="$type" backup || echo "BACKUP FAILED [$PGBACKREST_STANZA]"
if [ "$type" != incr ]; then
sh /ops/restore-test.sh || echo "RESTORE TEST FAILED [$PGBACKREST_STANZA]"
fi
sleep $((3600 - $(date +%s) % 3600)) # until the next full hour
done
Without a full backup, pgBackRest turns the first request
into a full one. The FAILED lines feed an alert
on the logs.
5. Testing the restore
#!/bin/sh
# ops/postgres/scripts/restore-test.sh
set -eu
dir=$(mktemp -d /tmp/restore-test.XXXXXX)
# Exported: PostgreSQL calls pgbackrest again to replay the WAL, with this path.
export PGBACKREST_PG1_PATH="$dir" PGHOST="$dir" PGPORT=5499
trap 'pg_ctl -D "$dir" -m immediate stop >/dev/null 2>&1 || true; rm -rf "$dir"' EXIT
pgbackrest --log-level-console=warn restore
# No archiving to the repository, no TCP port, pgaudit preloaded.
pg_ctl -D "$dir" -l "$dir/server.log" -w -t 600 start -o "-c port=5499 \
-c listen_addresses= -c unix_socket_directories=$dir -c archive_mode=off \
-c shared_preload_libraries=pgaudit -c pgaudit.log=none"
i=0
until [ "$(psql -d postgres -Atc 'select pg_is_in_recovery()' 2>/dev/null)" = f ]; do
i=$((i + 1)); [ "$i" -le 3600 ] || exit 1; sleep 1
done
echo "Data up to: $(psql -d postgres -Atc 'select pg_last_xact_replay_timestamp()')"
psql -d app -Atc 'select count(*) from doctrine_migration_versions' # business check
pg_amcheck --all --install-missing --heapallindexed
echo "RESTORE TEST OK [$PGBACKREST_STANZA]"
Three precautions avoid pitfalls.
archive_mode=off keeps the throwaway instance
from writing to the production repository. Without pgaudit
preloaded, its event triggers make any DDL fail, including
the CREATE EXTENSION amcheck run by
pg_amcheck --install-missing.
Finally, the test runs in the same image as the database: a
physical backup restored under another libc inherits the
sort-order problem.
pg_last_xact_replay_timestamp() gives the
freshness of the restored data, and so the actual loss in an
incident.
6. Returning to a point in time
# services/order: go back to 10:41 (UTC), just before the faulty migration
dc() { docker compose -f compose.yml -f compose.ops.yml "$@"; }
dc exec database psql -U app -d app -c 'select last_archived_time from pg_stat_archiver'
dc stop api backup database
dc run --rm --no-deps --entrypoint pgbackrest backup \
--delta --type=time --target="2026-09-14 10:41:00+00" --target-action=promote restore
dc start database backup api
last_archived_time must be past the target
time. pgBackRest picks a backup that ended before that time,
PostgreSQL replays the WAL up to it, then the instance is
promoted.
7. Upgrading the major version
The official image changed its layout in 18:
PGDATA is
/var/lib/postgresql/18/docker, and the declared
volume is /var/lib/postgresql. It refuses to
start on data from an older major, without touching it.
# services/order/compose.yml
services:
database:
image: postgres:18.7-trixie # major pinned, never read from a variable
environment:
POSTGRES_DB: app
POSTGRES_USER: app
POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:?required}
healthcheck:
test: pg_isready -h 127.0.0.1
volumes:
- database_data:/var/lib/postgresql # 18+ layout: data in 18/docker
volumes:
database_data:
An older image, read from a forgotten variable, would look
in /var/lib/postgresql/data: it would
initialise an empty database next to the real one.
For a database created on 16 or 17, pgautoupgrade runs
pg_upgrade --link, moves the data into
18/docker and reindexes. It also aligns the
data checksums, on by default since 18, with the old
cluster.
# services/order/compose.upgrade.yml (a single deploy)
services:
database:
image: pgautoupgrade/pgautoupgrade:18-trixie # same libc as the database
healthcheck: !reset null # the image has its own
The upgrade happens inside the database service
itself: Compose stops the old server before starting the new
one. A separate service would start next to the old server,
on the same volume, while pg_upgrade requires both servers
to be stopped.
POSTGRES_DB must be passed when it differs from
the user: otherwise pgautoupgrade opens the old cluster on a
database named after the user, and fails.
--link reuses the files: once 18 has started,
only the backup taken before lets you go back.
The tooled database asks for more: pg_upgrade requires, in
the new major, the libraries of every installed extension.
The pgautoupgrade image has neither pgaudit nor PoWA: we
derive an upgrade image that installs them, rehearsed on a
copy of the volume. After the upgrade, the sidecar runs
stanza-upgrade, and we trigger a full backup
right away.
Checklist
- A tooled image on the same libc as the database, never on an Alpine volume.
-
Technical roles (
pg_monitor, read access for PoWA, an audit role), no shared superuser. -
WAL archived continuously, with
archive_timeoutset. - An encrypted repository off the host, its key stored with the production secrets.
- A scheduled restore test: throwaway instance, no archiving, pgaudit preloaded.
-
A business check and
pg_amcheckin that test, and an alert on every failure. - A point-in-time recovery rehearsed before it is needed.
-
The major pinned in the image tag, and the upgrade done in
the
databaseservice.
Sources
Versions checked on 15 November 2026: PostgreSQL 18.7 (12
November), pgBackRest 2.60.0 (18 October), pgaudit 18.0,
PoWA 5.3 (PGDG package postgresql-18-powa) and
pgwatch 6.2.0 (14 November).
- pgBackRest, User Guide, Command Reference, Configuration Reference and releases.
- PostgreSQL 18, pg_amcheck, pg_upgrade, predefined roles and system administration functions.
- PostgreSQL 18, locales, initdb, release notes and supported versions.
- PostgreSQL wiki, Locale data changes.
- pgaudit, README and source code.
- PoWA, documentation, remote setup, security and HypoPG; pg_qualstats.
- pgwatch, documentation, preparing databases and releases.
-
Official PostgreSQL image,
documentation
(
PGDATA,VOLUME), Dockerfile 18 and entrypoint script. - pgautoupgrade, README and entrypoint script.
- Docker, Compose services, merging files, docker compose up, attestations and provenance.
- Barman and WAL-G.
- Lychen, pull requests 265 and 269: an open-source implementation of this operations stack, then of the move to PostgreSQL 18 with pgautoupgrade, in a moon monorepo.