gem-hunter

This repository contains the logic to build SSL-enabled Postgres images.

By default, when you deploy Postgres from the official Postgres template on Railway, the image that is used is built from this repository!

The official Postgres image in Docker hub does not come with SSL baked in.

Since this could pose a problem for applications or services attempting to connect to Postgres services, we decided to roll our own Postgres image with SSL enabled right out of the box.

The Dockerfiles contained in this repository start with the official Postgres image as base. Then the init-ssl.sh script is copied into the docker-entrypoint-initdb.d/ directory to be executed upon initialization.

By default, the cert expiry is set to 820 days. You can control this by configuring the SSL_CERT_DAYS environment variable as needed.

When a redeploy or restart is done the certificates expiry is checked, if it has expired or will expire in 30 days a new certificate is automatically generated.

Postgres writes every log level it has — LOG through PANIC — to stderr, and the stream a line arrives on is what Railway reads its severity from: stdout is ingested as info, stderr as error. Left alone, a database that reports each checkpoint, connection and authentication on stderr fills a severity-filtered log view — including one spanning every service in the project — with lines that are not errors.

Set LOG_TO_STDOUT=true (or 1) to send the Postgres server log to stdout instead. Real server errors move with it, since Postgres emits all of its levels on the one stream, so this trades per-line severity for a log view where an error is an error. Unset — or any other value — keeps the upstream behavior.

The image's own diagnostics are unaffected either way: certificate and pgBackRest warnings and every boot refusal from wrapper.sh go to stderr, and notes that the boot continues (a lock file that could not be opened) go to stdout.

Images are automatically built weekly and tagged with multiple version levels for flexibility:

• Major version tags (e.g., :17, :16, :15): Always points to the latest minor version for that major release
• Minor version tags (e.g., :17.6, :16.10): Pins to specific minor version for stability
• Latest tag (:latest): Currently points to PostgreSQL 16

Example usage:

# Auto-update to latest minor versions (recommended for development)
docker run ghcr.io/railwayapp-templates/postgres-ssl:17

# Pin to specific minor version (recommended for production)
docker run ghcr.io/railwayapp-templates/postgres-ssl:17.6

Besides Postgres's own contrib modules, the image ships pgvector and pgaudit (PGDG's postgresql-<major>-pgvector and postgresql-<major>-pgaudit). Both are a CREATE EXTENSION away (vector, pgaudit), from psql or the dashboard's Extensions card.

pgaudit refuses to load any way but shared_preload_libraries, so the image preloads it next to pg_stat_statements on every volume: init-ssl.sh writes shared_preload_libraries = 'pg_stat_statements,pgaudit' at initdb and wrapper.sh adds whichever of the two is missing on every boot, to volumes initialized before this and to an operator's own ALTER SYSTEM list (which overrides postgresql.conf as a whole). Preloaded with pgaudit.log unset it logs nothing and costs nothing; turning auditing on is

CREATE EXTENSION pgaudit;
ALTER SYSTEM SET pgaudit.log = 'ddl, role';
SELECT pg_reload_conf();

Audit lines land in the server log stream. If you set shared_preload_libraries yourself, use list syntax (= pg_stat_statements, pgaudit): ALTER SYSTEM quotes a single 'a,b' string as one library name and the next boot refuses with could not access file "a,b".

The major version upgrade job starts pg_upgrade's target server with the same preload list the volume had, so an installed pgaudit passes --check, and the upgraded database boots with it preloaded again.

Every Dockerfile.<major> (and Dockerfile.upgrade) builds FROM postgres:<version>-${DEBIAN_RELEASE} with DEBIAN_RELEASE pinned explicitly — currently trixie (Debian 13, glibc 2.41) for every major.

The unsuffixed upstream tag (postgres:16) is a floating alias for whichever Debian release docker-library currently prefers. It moved from bookworm (glibc 2.36) to trixie (glibc 2.41) with no change in this repository, and the daily rebuild republished the same :16 tag on the new glibc. That is not a cosmetic change: a glibc bump changes libc collation order, which silently invalidates every btree index on collatable text columns of a volume initialized under the old glibc — rows become unfindable through the index, unique constraints stop holding, and foreign-key checks fail on rows that are demonstrably there. PostgreSQL warns about the collation version mismatch on every connection until the recorded version is refreshed; the wrapper now reindexes before it refreshes (see Collation drift at boot), but the distro must never move as a side effect of a nightly rebuild.

trixie was chosen because every published tag was already on it when the pin landed (the published image config carries PG_VERSION=<minor>-1.pgdg13+N — pgdg13 is trixie, pgdg12 bookworm — which is also how to check what a published tag runs on without pulling it). Moving a major back to bookworm would flip glibc a second time under every volume that has since been reindexed on trixie.

To move a major to another release deliberately:

1. Change ARG DEBIAN_RELEASE= in Dockerfile.<major> (and Dockerfile.upgrade if the upgrade job's target majors move — pg_upgrade stamps the new cluster's collation versions with the job image's glibc, so it must match the runtime image that will serve the upgraded cluster). One PR, reviewed as a glibc/ICU change, not a packaging tweak.
2. Confirm postgres:<minor>-<release> exists upstream for every per-minor tag the build workflow still rebuilds (it rebuilds every published minor, not just the newest; curl -s https://registry-1.docker.io/v2/library/postgres/tags/list with an anonymous pull token lists them).
3. Expect the next boot of every volume on that major to detect the collation version change and rebuild its collation-dependent indexes concurrently before refreshing the version stamps. Plan the rollout around that load; large text-heavy databases reindex for a while.

The build workflow's content gate reads DEBIAN_RELEASE from the Dockerfile and probes postgres:<minor>-<release>, so a rebuild is triggered by changes to the pinned base, never by upstream moving the alias.

The image ships with pgBackRest installed but dormant. When WAL_ARCHIVE_BUCKET is unset (and the service isn't a PITR-restored one — see below), the image behaves identically to a vanilla SSL Postgres image — no archiving, no extra processes, no config changes. When set, Postgres archives WAL segments continuously to S3-compatible storage in async mode with archive-push-queue-max=5GiB. If S3 stalls, WAL queues in the local spool ($PGDATA/pgbackrest-spool, on the data volume so it survives container restarts) without blocking Postgres; if the queue fills, pgBackRest drops WAL and keeps Postgres running rather than letting pg_wal fill the data volume.

The image reads a tool-agnostic WAL_ARCHIVE_* / WAL_RECOVER_FROM_* env contract and translates internally to pgBackRest's native PGBACKREST_REPO{1,2}_S3_*, so swapping pgBackRest for another archiver in the future is a wrapper change rather than a cross-repo rewrite.

Invariant: repo1 is always "this service's own destination bucket" — the only place this service writes WAL. repo2, when present, is a read-only recovery source. No two services ever share a destination bucket. Which role(s) the image takes is decided purely by which env contract is present:

• WAL_ARCHIVE_* only → archiving service. repo1 = own bucket.
• WAL_RECOVER_FROM_* (+ POSTGRES_RECOVERY_TARGET_TIME) only → restored service. The source bucket is mounted read-only for the empty-volume pgbackrest restore and for archive-get during replay; once Postgres promotes, the service runs as plain non-archiving Postgres. This is what Railway's managed PITR restore provisions: the new service gets the source's variables with WAL_ARCHIVE_* stripped and WAL_RECOVER_FROM_* mirrored in, so it does not archive WAL until you enable Point-in-Time Recovery on it (which gives it its own bucket) — see Point-in-Time Recovery.
• WAL_ARCHIVE_* + WAL_RECOVER_FROM_* together → restore with archiving from the start. repo1 = this service's own destination bucket (the promoted timeline is archived there from its first segment), repo2 = source's bucket (read-only during recovery; ignored after promote — the new timeline doesn't exist there). The image supports this for self-managed setups; the managed restore flow does not set both, so a managed restore always needs the separate enable step above to resume archiving.

archive_command points at /usr/local/bin/pgbackrest-archive-push-wrapper.sh rather than calling pgbackrest archive-push directly. The wrapper tries the real push; on failure it measures pg_wal/, and when it exceeds the threshold (sized to half the data volume, capped at 5 GiB, floor 128 MiB — override via WAL_DROP_THRESHOLD_MB) it returns success to Postgres anyway, dropping the segment. This is the never-halt safety net for failure modes that bypass pgBackRest's own queue-max — bad credentials, deleted bucket, expired keys, pgbackrest#1848, #1726. When the wrapper drops a segment the PITR window gets a coverage gap from that segment to the next post-recovery base snapshot; below the threshold the wrapper surfaces failures normally so transient errors retry on the next archive_timeout.

The two thresholds are deliberately sized identically (both min(volume/2, 5GiB), floor 128 MiB — see compute_volume_thresholds):

• archive-push-queue-max (image-computed) governs the spool. Trips when the async worker can't drain it — most segments eventually land once the outage clears.
• WAL_DROP_THRESHOLD_MB (image-computed) governs pg_wal/ when pgbackrest's foreground returns non-zero. Until 2026-07-02 this was a fixed 500 MiB cap — 10x smaller than queue-max. A 2026-07-01 Tigris sjc outage showed why that was wrong: transient S3 errors (500s, timeouts, connection resets) are exactly the failure queue-max's 5 GiB buffer is sized to absorb, but the smaller pg_wal check tripped first and silently dropped WAL well short of that budget (#104). Only two explicit no-recovery-possible errors (NoSuchBucket, InvalidAccessKeyId) still bypass the threshold and drop immediately — every other failure, hard or transient, gets the full computed budget before we give up on it.

Operator-facing env contract:

Image-level tuning knobs:

The four worker overrides are template settings rendered by wrapper.sh. The pgbackrest launcher removes them only from pgBackRest's environment to keep unknown-option warnings out of info --output=json. Native pgBackRest options remain available.

Per-command worker counts (process-max) are auto-sized at container start from the cgroup-reported vCPU allocation (cpu.max on cgroup v2, cpu.cfs_quota_us on v1, nproc as a fallback). The four commands have different bottleneck shapes — archive-push is gated by serial WAL arrival and S3 PUT overhead, archive-get by serial replay inside Postgres, backup by the need to leave CPU for live DB traffic, restore by nothing (DB is down) — so each gets its own derived default. The PGBACKREST_*_PROCESS_MAX env vars (table above) are escape hatches for workloads that disprove the heuristic. On vertical autoscale, the new values take effect on the next container restart.

Stanza initialization (pgbackrest stanza-create --repo=1) runs automatically the first time the container boots with WAL_ARCHIVE_BUCKET set: a background task waits for Postgres to accept connections, then writes the stanza metadata into the bucket. The command is idempotent and runs on every subsequent boot — already-correct repo metadata is a no-op; a mismatch (e.g. WAL_ARCHIVE_PATH pointing at another cluster's repo) errors loudly, which is the safety we want. The --repo=1 scope keeps stanza-create off repo2 on a fork, where source already owns the stanza and we have read-only intent.

All Postgres-side config the image manages (archive settings, recovery settings) is written to $PGDATA/conf.d/*.conf, with a one-time include_dir = 'conf.d' directive added to postgresql.conf. The image does not touch postgresql.auto.conf — Postgres rewrites that file on every ALTER SYSTEM call and strips comments, which would break any sentinel-based cleanup. With the include-directory approach, file existence is the sentinel: enable = write, disable = remove.

When POSTGRES_RECOVERY_TARGET_TIME is set, the container writes recovery_target_time, restore_command, and recovery_target_action='promote' into $PGDATA/conf.d/pgbackrest-recovery.conf and creates recovery.signal. Postgres enters archive recovery, replays WAL from the bucket to the target timestamp, and promotes. The "PITR done" sentinel ($PGDATA/.pitr_configured) is written on the boot after Postgres removes recovery.signal (which it only does on successful promote), at which point the recovery conf file is also removed. A failed replay leaves the volume re-stageable — fix env vars and restart, no manual file cleanup needed. Once the sentinel is written, later restarts skip recovery entirely even if POSTGRES_RECOVERY_TARGET_TIME is changed to a different value — the cluster has already promoted to a new timeline and replaying again would corrupt it. To probe a different target, restore from a fresh volume snapshot (or, advanced: remove $PGDATA/.pitr_configured before the next start — and, if the service was ever major-upgraded, the volume-root .railway-major-upgrade.json too, since a completed upgrade marker is also read as "recovery done").

When POSTGRES_RECOVERY_TARGET_TIME is set on a brand-new container (no $PGDATA/PG_VERSION), the wrapper runs pgbackrest --repo=2 restore --type=time --target=<T> --target-action=promote against the source bucket before docker-entrypoint initializes anything. pgBackRest pulls the most recent base backup ≤ T plus the WAL chain forward into $PGDATA, writes recovery.signal + recovery params, and Postgres boots straight into archive recovery. A .pgbackrest_restored marker is written on success; configure_pgbackrest_recovery defers to the restore's own settings on subsequent starts of the same volume.

When WAL_ARCHIVE_BUCKET is set, the wrapper forks a background watcher (pgbackrest-backup-watcher.sh) that polls Postgres every 60 s and runs pgbackrest backup against the archive bucket when one of three conditions holds:

1. Initial backup — pg_stat_archiver.archived_count > 0 and no full has been recorded on this volume. Triggers the first --type=full, anchoring the PITR window from the first archived LSN forward.
2. Gap recovery — either the archive-push wrapper dropped a segment (touches $PGDATA/.pgbackrest_gap_pending) or pg_stat_archiver.failed_count grew since the last full. Once archive failures have been quiescent for 5 minutes, runs a fresh full so the PITR window resumes from the new base. The dropped segment itself remains unrestorable; everything from the new base forward is.
3. Periodic — WAL_BACKUP_FULL_INTERVAL_HOURS (default 168 h / weekly) for fulls, WAL_BACKUP_DIFF_INTERVAL_HOURS (default 24 h) for differentials. Set either to 0 to disable that schedule.

State persists at $PGDATA/.pgbackrest_backup_state (key=value lines: last_full_at, last_diff_at, last_full_failed_count). The bucket-side pgbackrest --stanza=main info --output=json is the canonical source of truth for what actually exists in the repo; the local file is a cache that survives restarts. A wiped volume re-derives from a single redundant initial full — pgBackRest's stanza locks prevent concurrent backups across cluster nodes.

Each cluster archives under a sub-prefix derived from its system_identifier: ${WAL_ARCHIVE_PATH}/cluster-<system_identifier>. The path is persisted in $PGDATA/.pgbackrest_repo_path so the archive-push wrapper, the backup watcher, and pgbackrest stanza-create all converge on the same value.

Alongside it, $PGDATA/.pgbackrest_repo_anchor records the system_identifier and PG_VERSION the path was derived from. The marker wins verbatim on every boot, so this fingerprint is the only thing that can tell "same cluster, same path" apart from "a different cluster inherited this path" — see the archive re-anchoring paragraph under Major version upgrades. A volume predating the file adopts its live identity on first boot and keeps the path it already archives to.

Why per-cluster: a wipe-and-reuse-bucket cycle (operator drops the data volume, redeploys the service against the same WAL_ARCHIVE_BUCKET) produces a brand-new system_identifier from initdb. Without discrimination, pgBackRest's stanza-create would refuse the new cluster on system-id mismatch and the new cluster's WAL would never land — silent data loss for any operator who didn't notice. With per-cluster paths, the new cluster lands at cluster-<new_sysid>, the previous cluster's archive stays put at cluster-<old_sysid>, and both histories coexist. The bucket becomes a multi-history store: list its cluster-* sub-prefixes to enumerate every cluster that ever archived to it; pick a subprefix to restore from.

WAL_RECOVER_FROM_PATH on a restored service must point at the specific source-side cluster-<sysid> sub-prefix the user wants to restore from — pgbackrest restore reads from one path. Railway reads the bucket's per-cluster sub-prefixes and shows them as separate "histories" in the restore UI.

In HA, every Postgres node runs the watcher and standbys exit early on SELECT pg_is_in_recovery() — only the leader performs backups. v1 of the watcher backs up from the primary; --backup-standby is a follow-up. After a Patroni failover, the new leader's watcher takes over; if its local state is stale, an extra full may run, which is harmless.

Queue-max-trip detection: pgBackRest's archive-push-queue-max trip drops segments without going through the archive-push wrapper and without incrementing failed_count, so neither of those two gap signals fires on its own. The backup watcher closes this independently: every poll (WAL_BACKUP_POLL_INTERVAL_SECONDS, default 60s) it compares pg_stat_archiver.last_archived_wal against the repo's archived high-water mark (pgbackrest info --output=json); once the lag reaches WAL_LAG_GAP_THRESHOLD_SEGMENTS (default 32 ≈ 512 MiB) it enters the gap-recovery state machine the same way the other two signals do, so a fresh full fires once grace elapses instead of waiting for the next periodic full (#85, hardened by #86).

pgbackrest backup is invoked with --type=full or --type=diff depending on the trigger; the process-max=backup setting (default clamp(cpus/4, 1, 16)) caps copy concurrency to leave CPU for live DB traffic. Backups run with --no-expire-auto; pgbackrest expire is then called as its own step right after a successful backup, and removes fulls/diffs beyond WAL_BACKUP_RETENTION_FULL / _DIFF, plus the WAL their manifests no longer pin. Splitting the two matters because pgBackRest's default (expire-auto=y) folds expire into the same command, so a transient expire failure (e.g. a slow S3-compatible endpoint on the post-backup listing) would otherwise fail the whole backup invocation even though the backup itself landed and is durable — leaving the watcher's last_full_at unset and re-triggering a brand new full upload every retry cycle instead of just deferring retention to the next backup.

For PITR-enabled services, pgbackrest expire is the sole WAL retention authority — no bucket-side lifecycle policy. Backup manifests pin the WAL needed to make each backup restorable; expire releases both together when a backup ages out. Earlier iterations proposed a bucket-side TTL as a safety net but it's superfluous: any TTL shorter than expire's horizon would yank WAL out from under live manifests, and any TTL ≥ that horizon is redundant.

The default retention (full=4, diff=14, weekly fulls + daily diffs) covers approximately a four-week PITR window before the oldest full ages out. Tune via WAL_BACKUP_RETENTION_FULL, WAL_BACKUP_RETENTION_DIFF, WAL_BACKUP_FULL_INTERVAL_HOURS, WAL_BACKUP_DIFF_INTERVAL_HOURS.

When WAL_ARCHIVE_BUCKET is removed (the gating env var), the container on next start wipes the archive-side state so a later re-enable starts from a clean slate:

• $PGDATA/conf.d/pgbackrest.conf (archive settings)
• $PGDATA/.pgbackrest_backup_state and $PGDATA/.pgbackrest_gap_pending (backup-watcher state — bucket-scoped, so re-enable starts from NEEDS_INITIAL_BACKUP rather than a stale cache)
• /etc/pgbackrest/pgbackrest.conf (image-level operator policy — only removed when both WAL_ARCHIVE_BUCKET and WAL_RECOVER_FROM_BUCKET are unset, since recovery-only services still need it)
• $PGDATA/pgbackrest-spool (staged segments are useless without a repo to push to; any in-flight WAL was already covered by the archive-push wrapper's drop-on-failure path)
• $PGDATA/conf.d/pgbackrest-recovery.conf and the $PGDATA/.pitr_staging / $PGDATA/.pitr_configured / $PGDATA/.pgbackrest_restored markers are scoped to WAL_RECOVER_FROM_BUCKET and only cleared when that variable goes away.

With the conf-file-as-sentinel model, removal IS the disable — archive_mode, archive_command, and any recovery settings vanish on next start. The include_dir = 'conf.d' line in postgresql.conf is left in place; it's a no-op when the directory has no pgbackrest files, and any user-added include files in conf.d/ continue to work.

By default, this image is hardcoded to listen on port 5432 regardless of what is set in the PGPORT environment variable. We did this to allow connections to the postgres service over the RAILWAY_TCP_PROXY_PORT. If you need to change this behavior, feel free to build your own image without passing the --port parameter to the CMD command in the Dockerfile.

Dockerfile.upgrade builds a one-shot job image carrying two majors' server binaries, driven by upgrade-job.sh. CI publishes one image per supported (source → newer target) pair as ghcr.io/railwayapp-templates/postgres-ssl/upgrade:<from>-<to>, which is what the dashboard's upgrade workflow dispatches. Locally:

docker build -f Dockerfile.upgrade \
  --build-arg FROM_VERSION=16 --build-arg TO_VERSION=17 \
  -t postgres-upgrade:16-17 .

It runs against the database's own volume while the service is stopped, and selects its mode from the UPGRADE_JOB_MODE env var — never startCommand or a positional arg, which Railway can't rely on: how a deployment's startCommand composes with the image's own ENTRYPOINT isn't guaranteed. A positional arg still works for local/manual runs and the e2e harness, which invoke the script directly, but only as a fallback — an env var takes priority, and an unrecognized positional arg refuses rather than silently defaulting to upgrade:

The job connects as the cluster's actual install user, and initdb's the target cluster with the same name — pg_upgrade requires both clusters' install users to match. Which env var names that user depends on the template family, and the two disagree: on postgres-ssl the official entrypoint initdb's with --username="$POSTGRES_USER" (a custom-user service has no postgres role at all), while on postgres-ha POSTGRES_USER is the app user Patroni creates and the install user is PATRONI_SUPERUSER_USERNAME (default postgres). The job disambiguates by the volume itself — a Patroni-managed data dir always carries patroni.dynamic.json — so the same inherited service env resolves correctly on both. The target initdb also replays the service's POSTGRES_INITDB_ARGS (evaled, exactly as the official entrypoint does at init time), because pg_upgrade refuses any locale/encoding mismatch and a cluster initialized with --locale=C could otherwise never pair with an image-default target. Every RAILWAY_UPGRADE_RESULT: payload is built with jq --arg (compact, one line) so no on-disk byte a database superuser can write reaches the workflow's JSON parser unescaped — and every passthrough of pg_upgrade output, server logs, or failure lists is indented, so no attacker-controlled byte can start a line with the result sentinel either.

Volumes with recovery.signal or standby.signal are refused outright by both check and upgrade (exit 2): those shapes can't be upgraded in place, and the quiesce would consume a mid-restore volume's recovery intent. Finish or promote recovery first.

.railway-major-upgrade.json at the volume root is the commit point (fsynced through the parent directory, and every write checked before any directory is mutated): upgraded means pg_upgrade succeeded and recovery must roll forward; completed means the swap is done. Both phases are scoped to the marker's own version pair — a completed marker from a previous upgrade of the same volume is history, not state, so chained upgrades (16→17, later 17→18) work; an in-flight marker of a foreign pair is refused. pg_upgrade disables the old cluster (renames its pg_control to pg_control.old) before it links the first user relation file — verified empirically, and pg_upgrade's own output prescribes the rename-back as the recovery — so that rename alone never drives a roll-forward. If the marker is lost, the job rolls forward only when its own completion sentinel (written inside the target dir the moment pg_upgrade exits 0) is present; pg_control.old without the sentinel is a crash mid-link, and the job rolls back instead — it reverses the rename (safe while the target cluster has never been started) and redoes the upgrade from scratch. The same rename-back runs when pg_upgrade itself fails after disabling the old cluster, so that failure leaves a bootable FROM-major volume rather than forcing a backup restore. wrapper.sh refuses to boot while a non-completed marker exists, and refuses any image whose major differs from the on-disk PG_VERSION — so no mismatched boot can touch the data.

Both sides also hold a flock on the volume-root .railway-volume.lock: the job exclusively for its run, the runtime container shared for its lifetime. A job dispatched against a live database refuses i