pg-autodump

README

pg-autodump

On-demand PostgreSQL logical-backup sidecar. Trigger it, and it writes a verified dump per database for your real backup tool to collect.

What it does

pg-autodump runs pg_dump (custom format) against every database in DB_SPECS, verifies each dump with pg_restore --list, and writes it atomically into a shared volume under a per-server <host>_<port>/ subdirectory, keeping the newest DUMP_KEEP copies per database (7 by default). It does its one job well and delegates the heavy lifting: no compression, encryption, or off-site sync — your backup tool (Kopia, Restic, Borg, rsync) already does those, and points at the /dumps volume. If the collector also versions your backups, set DUMP_KEEP=1 to keep a single stable <dbname>.dump and hand retention to it.

It connects to Postgres over the network (the PostgreSQL wire protocol, not HTTP) and runs as an ordinary unprivileged user.

Why this design

• Unprivileged and socket-less — non-root, cap_drop: [ALL], read_only. A compromise reads databases through a least-privilege role; it cannot reach the host. No Docker socket, so no root-equivalent surface.
• Verify before replace — each dump stages to a temp file, passes a non-empty and pg_restore --list (TOC) check, then atomically renames into place. The last known-good dump survives any failure (atomicfile adds the dir-fsync a plain mv lacks).
• Bounded parallelism — DUMP_CONCURRENCY dumps databases concurrently with no per-host serialization, so the common one-server-many-DBs case is not forced serial. One knob, safe default.
• Built-in retention — keeps the newest DUMP_KEEP timestamped dumps per database (7 by default), pruning older ones after each successful run, so it works as a self-contained incremental backup out of the box. Set DUMP_KEEP=1 to instead keep a single stable <dbname>.dump and delegate versioning to your backup tool.
• Standard surface — POST /dump, GET /healthz. Trigger by the built-in daily timer (default), over HTTP, docker exec ... pg-autodump trigger, or run one cycle as a batch job with pg-autodump run (see One-shot mode).

Quick start

The image is published to both GHCR (ghcr.io/cplieger/pg-autodump) and Docker Hub (cplieger/pg-autodump) — identical contents, use whichever you prefer.

1. Create a least-privilege backup role in each database:

   CREATE ROLE dbdumper_ro LOGIN PASSWORD 'choose-a-strong-password';
   GRANT pg_read_all_data TO dbdumper_ro;          -- PostgreSQL 14+
   GRANT CONNECT ON DATABASE myapp TO dbdumper_ro;
   

2. Create a .pgpass (mode 0600, or libpq silently ignores it). One line per host:port:dbname:user:

   mydb-host:5432:myapp:dbdumper_ro:choose-a-strong-password
   

3. Run it (see compose.yaml for the full example):

   services:
     pg-autodump:
       image: ghcr.io/cplieger/pg-autodump:latest
       container_name: pg-autodump
       restart: unless-stopped
       user: "1000:1000"  # match your host user
       read_only: true
       cap_drop: ["ALL"]
       security_opt: ["no-new-privileges:true"]
       environment:
         DB_SPECS: "mydb-host:5432:myapp:dbdumper_ro"
       ports:
         - "127.0.0.1:9847:9847"
       volumes:
         - "./secrets/.pgpass:/secrets/.pgpass:ro"   # mode 0600
         - "./dumps:/dumps"
       tmpfs:
         - "/tmp:size=16m,mode=1777"   # 1777 so the non-root user can write the health marker
   

   The container runs as the UID set in user: (1000 here), so the host ./secrets/.pgpass (mode 0600) and the host ./dumps directory must be owned by that UID: libpq ignores a .pgpass not owned by the running UID, and dumps can't be written otherwise.

   chown 1000:1000 ./secrets/.pgpass ./dumps
   

4. Trigger a backup:

   curl -fsS -X POST http://127.0.0.1:9847/dump
   # or: docker exec pg-autodump pg-autodump trigger
   

One-shot mode

pg-autodump run performs exactly one dump cycle and exits — for deployments where an external scheduler (cron, a systemd timer, a Kubernetes CronJob, Ofelia) owns the cadence and consumes the exit code as the result:

docker run --rm \
  -e DB_SPECS="mydb-host:5432:myapp:dbdumper_ro" \
  -v ./secrets/.pgpass:/secrets/.pgpass:ro \
  -v ./dumps:/dumps \
  ghcr.io/cplieger/pg-autodump:latest run

• Exit code is the result. 0 when every configured database dumped ok, non-zero when any failed or the preconditions (client binaries, writable /dumps, non-empty DB_SPECS) weren't met. SIGTERM mid-run cancels the in-flight pg_dump cleanly (reported as killed, non-zero exit).
• No listener, no timer. run binds no HTTP port and ignores LISTEN_ADDR, AUTH_TOKEN, DUMP_INTERVAL, and SHUTDOWN_GRACE — transport, scheduling, and drain belong to the invoking scheduler. The image's HEALTHCHECK is aimed at the resident server and reports nothing useful for a run-and-exit container.
• Runs never overlap, and contended runs are never lost. The server and one-shot runs in the same container coordinate through a cycle lock (a kernel-released flock under /tmp, so a crashed run can never wedge it). A run arriving while a cycle is already in flight queues its demand (depth 1) and exits 0 immediately; the active runner executes the queued cycle as soon as the current one finishes, and that cycle's per-database results land in the active runner's log stream. Further requests behind an already-queued one are discarded — the queued cycle already starts after they arrived. Pick one scheduling mode per deployment (resident server or one-shot); the coordination exists so a stray manual run is safe, not to run both on a schedule.

Configuration reference

Environment variables

Variable	Description	Default	Required
DB_SPECS	Space-separated host[:port]:dbname:user tuples (port defaults to 5432). Ids are [a-zA-Z0-9_-] (host also allows .), no leading -, no .., no control chars. IPv6 literal hosts use the bracketed form [2001:db8::1][:port]:dbname:user. Invalid entries are reported per-DB and skipped.	-	Yes
PGPASSFILE	Path to a read-only .pgpass (mode 0600). PGPASSWORD is also honoured by libpq but .pgpass is preferred (scoped per host/db/user).	/secrets/.pgpass	No
DUMP_DIR	Output directory; each database's dump lands under a per-server <host>_<port>/ subdirectory (see On-disk layout). A value with a .. path component is fatal — startup aborts rather than silently relocate backups to the default. Names that merely contain dots (/dumps/a..b) are fine.	/dumps	No
DUMP_TIMEOUT	Per-dump seconds (min 10).	300	No
DUMP_CONCURRENCY	Parallel dumps. Raise for many hosts / fast storage; set 1 for a single slow backup volume.	2	No
DUMP_INTERVAL	Built-in timer cadence (Go duration). On startup it runs one dump only when no existing dump is newer than one interval, so a deployment that restarts faster than its interval is never starved of backups. off / disabled / 0 hand scheduling to an external trigger.	24h	No
DUMP_KEEP	Retained dumps per database. >1 (default 7) writes timestamped <dbname>.<UTC>.dump files and prunes to the N newest. 1 writes a single stable <dbname>.dump, overwritten each run (delegate versioning to your backup tool).	7	No
DUMP_FREE_KB_WARN	Warn when free space on /dumps falls below this (KB) at run start. 0 disables.	1048576	No
AUTH_TOKEN	When set, /dump requires Authorization: Bearer <token>. Empty = open (fine on a private network / loopback); pg-autodump logs a startup warning when it is empty and LISTEN_ADDR is non-loopback.	""	No
LISTEN_ADDR	HTTP listen address.	:9847	No
SHUTDOWN_GRACE	Drain budget on SIGTERM. Set compose stop_grace_period >= this + ~5s (a cancelled in-flight dump gets a short extra window to reap pg_dump and clear its staged temp).	DUMP_TIMEOUT+15s	No

IPv6 hosts. Use the bracketed form in DB_SPECS ([2001:db8::1]:5432:db:user; the port may be omitted). libpq's .pgpass is colon-delimited, so an IPv6 host's colons must be backslash-escaped there (2001\:db8\:\:1:5432:db:user:pw) — or use PGPASSWORD instead.

Volumes

Mount	Description
/secrets/.pgpass	Read-only .pgpass (mode 0600). Optional when PGPASSWORD is used.
/dumps	Output directory; verified dumps under a per-server <host>_<port>/ subdirectory (one stable <dbname>.dump, or DUMP_KEEP timestamped copies).

Endpoints

• POST /dump — run all dumps; 200 if every database succeeded, 500 if any failed, 429 if a run is already in progress, 401 if AUTH_TOKEN is set and the bearer token is missing/wrong. The body has one host/db: <detail> line per database; for an execution-tool failure (pg_error / truncated / other) the line carries only the reason word — the raw pg_dump/pg_restore stderr is logged, not returned, so an open endpoint never discloses schema or object names.
• GET /healthz — 200 ok / 503 unhealthy. Reflects liveness preconditions (client binaries present, /dumps writable, DB_SPECS non-empty), not per-host database reachability, so a transiently-down database never flips the container unhealthy.

On-disk layout

Each database's dump is written under a per-server subdirectory of DUMP_DIR named <host>_<port>, so two databases that share a name on different servers never collide on one file:

/dumps/
  db1.example.com_5432/myapp.dump
  db2.example.com_5432/myapp.dump        # same dbname, different host — no clash
  apphost_5433/myapp.dump                # same host, a second instance on :5433
  @2001-db8--1_5432/myapp.dump           # IPv6 host (':' encoded as '-', '@'-prefixed)

With DUMP_KEEP>1 the timestamped <dbname>.<UTC>.dump files live inside that subdirectory and are pruned per server, so retention never counts one server's dumps against another's. Upgrading from a flat layout: dumps previously written as <dbname>.dump at the DUMP_DIR root are no longer updated; new dumps appear under <host>_<port>/. Root-level files are invisible to the app — never read, moved, or deleted, and they don't count for the startup recency check, so the first start after upgrading runs a dump immediately (timer on) — remove the old flat files once at your convenience. A versioning collector (Kopia, etc.) simply begins a fresh chain at the new paths.

Alerting

pg-autodump has no metrics endpoint; its operational state is in its logs (structured slog written to stderr). Ship the container's logs to Loki (Grafana Alloy's Docker log discovery does this with no configuration) and evaluate this rule with Loki's ruler; firing alerts deliver through your Alertmanager exactly like Prometheus metric alerts.

Two rules cover the two failure shapes: a loud failure (a dump ran and reported an error) and a silent non-run (no cycle completed at all — container down, timer disabled, or every trigger dying before the dump starts). Every completed cycle, whatever triggered it, emits one dump cycle complete heartbeat line; the absence rule keys on it. One visibility caveat: the rules match the container's main log stream, which covers the resident server (timer, HTTP, and trigger all dump in that process) and a one-shot container running run as its command — but a run invoked through docker exec logs to the exec session instead, so alert on your scheduler's job result in that shape.

groups:
  - name: pg-autodump
    rules:
      - alert: PgAutodumpDumpFailed
        expr: |
          sum by (container) (count_over_time(
            {container="pg-autodump"} |= `level=ERROR` |= `reason=` [15m]
          )) > 0
        for: 0m
        labels:
          severity: critical
        annotations:
          summary: "pg-autodump: database dump failed"
          description: >
            A pg-autodump database dump failed (level=ERROR with a reason=
            field). Verify-before-replace keeps the last good .dump, so the
            affected database's backup is now stale. reasons:
            connect_error/auth_error/pg_error = misconfig or database down;
            timeout = exceeded the DUMP_TIMEOUT budget; truncated/empty =
            bad/partial dump. (A graceful-shutdown cancel logs reason=killed
            at level=WARN and does not trip this alert.)
      - alert: PgAutodumpCycleMissing
        expr: |
          absent_over_time(
            {container="pg-autodump"} |= `dump cycle complete` [26h]
          )
        for: 0m
        labels:
          severity: warning
        annotations:
          summary: "pg-autodump: no dump cycle completed in 26h"
          description: >
            No "dump cycle complete" heartbeat in 26h. With the default 24h
            timer (or a daily external trigger) backups have silently
            stopped: the container may be down, the timer disabled, or every
            trigger failing before a dump starts. Size the window to ~2x your
            dump cadence.

Thresholds and the severity label are starting points; adjust the [15m] / [26h] windows and the container selector to your deployment (the absence window tracks your cadence, not the failure window), and route by whatever labels your Alertmanager uses. If your scheduler already alerts on a missing scheduled run (as an exec-based scheduler like Ofelia can), the absence rule is redundant — keep whichever vantage point you trust more.

Healthcheck

The Docker HEALTHCHECK runs the pg-autodump health subcommand — a file-marker probe, so no shell, curl, or open port is needed in the image. The main process writes the marker once liveness preconditions hold (the client binaries resolve, /dumps is writable, DB_SPECS is non-empty); a transiently-down database does not flip the container unhealthy, because per-host reachability is a per-dump concern reported in POST /dump, not liveness.

The backup role

pg_read_all_data (PostgreSQL 14+) grants read on all ordinary tables, views, and sequences — exactly what a logical dump needs. Caveats to document for your databases:

• Large objects (pg_largeobject) are not covered by pg_read_all_data (BUG #19379). A database using them needs an owning/superuser role, lo_compat_privileges, or --no-large-objects if blobs are not part of the backup contract.
• Row-level security requires BYPASSRLS (a superuser-granted attribute, more than read-only) for pg_dump to read RLS-protected tables.
• The role is cluster-level and SELECT-only: it cannot modify the database and is unaffected by application updates. A fresh data-directory re-init drops it (recreate it), and a dump holds ACCESS SHARE locks, so schedule dumps outside heavy DDL/migration windows.
• On PostgreSQL < 14, grant SELECT on all tables plus schema USAGE instead.

Versioning

The image ships the newest PostgreSQL client major it is built with. pg_dump requires the client major to be >= the server major, so a client can dump any server up to its own version. Bump the client major when you upgrade a server ahead of it; a too-old client is reported per-DB as version_mismatch with a clear message rather than a cryptic pg_dump abort.

Security

• No Docker socket, no root. The container needs only network reach to the databases, a read-only .pgpass, and a writable /dumps.
• Credentials never on a command line or in logs. They live in .pgpass (or PGPASSWORD); pg_dump is invoked with --no-password so it never prompts.
• No shell, explicit argv. DB_SPECS is validated once, and identifiers are passed as long options (--dbname=, --username=) so a value can never be read as a flag. No shell is ever invoked.
• Keep it private or set AUTH_TOKEN. A stray trigger can at most write a read-only-role dump to the volume. When the endpoint is open (AUTH_TOKEN empty) on a non-loopback LISTEN_ADDR, pg-autodump logs a startup warning; and POST /dump returns only the reason word for execution-tool failures (never the raw pg_dump/pg_restore stderr), so an open endpoint discloses no schema or object names.

The CI battery runs govulncheck, golangci-lint (gosec, gocritic), trivy, grype, gitleaks, semgrep, and hadolint on every change; DB_SPECS parsing is fuzzed.

Dependencies

Updated automatically via Renovate and pinned by digest. Builds carry signed SBOMs and provenance attestations verifiable with gh attestation verify.

Dependency	Source
golang	Go
alpine	Alpine
postgresql18-client	PostgreSQL
tini	GitHub
github.com/cplieger/atomicfile	GitHub
github.com/cplieger/health	GitHub
pgregory.net/rapid	pkg.go.dev

tini (PID 1) is fetched as the pinned upstream static binary (TINI_VERSION, SHA256-verified per arch, fail-closed) and Renovate-tracked via GitHub releases.

The postgresql-client (pg_dump/pg_restore/psql + libpq) is a required, irreducible dependency, and the reason the image is Alpine (libc) rather than distroless. It deliberately stays the Alpine package rather than a pinned upstream build: the client major must track the newest PostgreSQL server major you dump (see Versioning), and the digest-pinned base fixes the Alpine release line and starting filesystem while postgresql18-client and its libraries deliberately float to the current package revisions in that line at each rebuild (apk add resolves against the live index at build time). See the PostgreSQL documentation for what a logical dump entails.

Credits

The PostgreSQL client tools pg_dump, pg_restore, and psql are part of PostgreSQL (PostgreSQL License). The pg_dump argument construction and exit-code handling were informed by orgrim/pg_back (2-clause BSD), used as a reference only — not vendored.

Migrating from db-dumper 1.x

pg-autodump succeeds db-dumper, which ran pg_dump via docker exec over the root-equivalent Docker socket; pg-autodump is an unprivileged network client instead (no socket, no root). To migrate:

• Remove the Docker socket mount and user: "0:0".
• Rewrite DB_SPECS from container:dbname:user to host[:port]:dbname:user.
• Provide credentials via a read-only .pgpass and a least-privilege role (see above).
• Move triggers from GET /cgi-bin/dump to POST /dump (or pg-autodump trigger), and health to GET /healthz.
• Set the healthcheck to ["CMD", "pg-autodump", "health"] and stop_grace_period >= SHUTDOWN_GRACE.

Contributing

Issues and pull requests are welcome. Please open an issue first for larger changes so the approach can be discussed before implementation. See CONTRIBUTING.md for the package layout, load-bearing invariants, and local checks.

Disclaimer

This project is built with care and follows security best practices, but it is intended for personal / self-hosted use. No guarantees of fitness for production environments. Use at your own risk.

This project was built with AI-assisted tooling using Claude, GPT, and Kiro. The human maintainer defines architecture, supervises implementation, and makes all final decisions.

License

This project is licensed under the GNU General Public License v3.0.