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_dump does 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_monitor role. 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 of pg_qualstats and the waits of pg_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.

One database, four functions, and a restore tested every day order-database PostgreSQL 18, tooled image enabled by compose.ops.yml dedicated technical roles Monitor pgwatch + Grafana pg_monitor role Analyse queries PoWA, pg_qualstats indexes tested by HypoPG Trace access pgaudit DDL, roles, writes Back up pgBackRest, PITR WAL archived continuously backups and WAL restore tested, in the same image pgBackRest repo a stanza per database Restore throwaway instance Business check query, freshness pg_amcheck tables, B-tree indexes every day, after the backup: OK report, or an alert

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_timeout set.
  • 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_amcheck in 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 database service.

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).