PostgreSQL 18 with Point-in-Time Recovery

Deploys PostgreSQL 18 with point-in-time recovery from the first boot: WAL segments go to a Railway Bucket via pgBackRest, base backups run on a schedule, a cron job checks that backups work, and a restore slot rebuilds the database as of a chosen time. PgBouncer is included.

What you get

  • PostgreSQL 18 on Railway's postgres-ssl:18 image with TLS, a volume and a TCP proxy
  • WAL archiving to a Railway Bucket, a weekly full and daily differential backup, with 4 fulls kept (about 3 to 4 weeks of window)
  • PgBouncer 1.26.1 for transaction pooling on the private network
  • A PITR Monitor cron service every 30 minutes that proves a fresh commit reaches the bucket and logs the restore window
  • A Postgres Restore slot with its own volume, driven by one variable, that never touches the live database

Good for

  • Undo an accidental DELETE, UPDATE, DROP or bad migration by restoring to a minute before
  • Recover from a lost or damaged volume, losing at most about a minute of writes
  • Inspect data as it was at a given time for audits or debugging
  • Run regular restore drills that prove backups work

Services in this template

  • Postgres · image ghcr.io/railwayapp-templates/postgres-ssl:18
  • Postgres Restore · from baranberkay96/postgres-pitr-railway
  • PITR Monitor · from baranberkay96/postgres-pitr-railway
  • PgBouncer · image edoburu/pgbouncer:v1.26.1-p0

Questions people ask

How do I connect an app?

Reference DATABASE_POOL_URL for application queries, DATABASE_URL for migrations and session features, or DATABASE_PUBLIC_URL from outside Railway (add ?sslmode=require; the certificate is self-signed). Variable names match Railway's Postgres. The password is generated at deploy.

How do I restore to a point in time?

Pick a UTC time inside the window shown in the latest PITR Monitor run. On Postgres Restore, set PITR_RESTORE_TARGET_TIME to that time (or latest) and deploy. The slot replays WAL into its own empty volume; check the data over railway ssh, copy rows back or point an app at it, then wipe the slot's volume to reset. After losing the Postgres volume, use an explicit time from before the loss.

How do I know backups work?

Open PITR Monitor, Cron Runs: each run logs OK or FAIL lines and the restore window, and a failed run exits with an error. You can run a check now with railway cron run.

What limits should I know?

Railway Buckets have no versioning or object lock, and the bucket keys can read and delete backups, so limit who can see variables and keep an off-site copy if you need immutable backups. Give Postgres Restore a volume as large as the database. A single node; no read replicas.

What does it cost, and how do I upgrade?

The author's estimate is about 2 dollars a month idle and about 17 dollars for a 5 GB database writing about 1 GB of WAL a day, including bucket storage and upload egress (an estimate, not a measurement). Postgres follows Railway's postgres-ssl:18 tag on redeploy; PgBouncer is pinned and upgraded by changing its tag.

Full template README

Deploy and Host PostgreSQL PITR with Railway

PostgreSQL 18 with point-in-time recovery (PITR) from the first boot: every WAL segment goes to a Railway Bucket, base backups run on a schedule with retention, a cron job checks that backups really work, and a restore slot rebuilds the database as of any moment in the window. Community template, with PgBouncer.

About Hosting PostgreSQL PITR

Daily dumps can lose a day of data, and they cannot bring back the state just before a bad migration or an accidental DELETE. PITR keeps base backups plus the continuous write-ahead log, so the database can be replayed to a chosen second. Railway's own Postgres image (postgres-ssl:18) already contains pgBackRest and the archiving, backup and restore logic behind Railway's managed PITR. This template switches it on at deploy, with the archive in a Bucket inside your project, and adds the operating pieces: an independent health check, a one-variable restore that never touches the live database, memory-based tuning, pg_stat_statements and connection pooling.

Common Use Cases

  • Undo an accidental DELETE, UPDATE, DROP or a bad migration: restore the database as of a minute before, then copy the rows back
  • Recover from a lost or damaged volume, losing at most about a minute of writes
  • Inspect data as it was at a given time for audits or debugging, without touching production
  • Run regular restore drills that prove the backups work
  • Replace Railway's Postgres in apps that need stronger recovery than daily dumps

Dependencies for PostgreSQL PITR Hosting

  • PostgreSQL 18 with pgBackRest (included: ghcr.io/railwayapp-templates/postgres-ssl:18)
  • A Railway Bucket for WAL and base backups (created by the template)
  • PgBouncer for connection pooling (included: edoburu/pgbouncer 1.26.1)

Deployment Dependencies

Implementation Details

Service Source Purpose
Postgres ghcr.io/railwayapp-templates/postgres-ssl:18 PostgreSQL 18, TLS, volume, TCP proxy. Archives WAL with pgBackRest, takes weekly full and daily differential backups, expires old ones
PgBouncer edoburu/pgbouncer:v1.26.1-p0 Transaction pooling on the private network (port 6432)
PITR Monitor built from this repo (services/pitr) Cron every 30 minutes: proves a fresh commit reaches the bucket, checks backup age, logs the restore window. A failed run = a problem
Postgres Restore built from this repo (services/pitr) Restore slot with its own volume. Idle until you set a target time
Postgres-PITR Railway Bucket Private S3 storage for WAL and base backups

Connection strings on the Postgres service (reference them as ${{Postgres.NAME}}): DATABASE_POOL_URL for application queries, DATABASE_URL for migrations and session features, DATABASE_PUBLIC_URL from outside Railway (add ?sslmode=require; the certificate is self-signed). The variable names match Railway's Postgres, so existing references keep working.

How PITR works here

  • WAL_ARCHIVE_* points Railway's image at the bucket. Postgres archives each WAL segment (zstd-compressed) under /pgbackrest/cluster-{system id}. POSTGRES_ARCHIVE_TIMEOUT=60 closes a segment within a minute after changes, so at most about a minute of writes is at risk.
  • The first full backup starts minutes after the first deploy. Then a full runs weekly and a differential daily. 4 fulls are kept (WAL_BACKUP_RETENTION_FULL), which gives about 3 to 4 weeks of recovery window.
  • A start command sizes memory, parallel workers, wal_compression and max_wal_size from the container limits at every boot. It also creates pg_stat_statements and refuses to rewind the live data directory if recovery variables are set on it by mistake.

Checking backup health

  • Open PITR Monitor → Cron Runs. Every run logs OK: or FAIL: lines, the restore window (restore window: {from} to {to}), the archive path and pgbackrest info. A run that exits with an error is a failed run: check its log.
  • Run a check now: railway cron run --service "PITR Monitor".
  • The Postgres log shows pgbackrest: and pgbackrest-watcher: lines for archiving and backups. In SQL: SELECT * FROM pg_stat_archiver;.
  • The monitor commits one empty logical message per run (no table is written). This keeps even idle databases restorable up to the last run.

Restoring to a point in time

  1. Choose the time in UTC inside the window from the latest PITR Monitor run, for example 2026-10-31T09:15:00Z.
  2. On Postgres Restore → Variables, set PITR_RESTORE_TARGET_TIME to that time, or to latest for the newest state, and deploy.
  3. The slot marks the newest point on the live Postgres (one empty commit and a WAL switch). It then picks the backup history that covers the time and hands over to Railway's image: pgbackrest restore, WAL replay, promote. The log ends with database system is ready to accept connections.
  4. Open a shell with railway ssh --service "Postgres Restore" and check the data: psql "$DATABASE_URL".
  5. Use it. Copy rows back from that shell, where both databases are reachable: pg_dump --data-only --table=public.orders "$DATABASE_URL" | psql "$PITR_SOURCE_DATABASE_URL" (adapt to your tables and conflicts). Or point an app at ${{Postgres Restore.DATABASE_URL}}. The copy does not archive WAL.
  6. Reset the slot: Postgres Restore → Settings → Volume → Wipe Volume, clear PITR_RESTORE_TARGET_TIME (or set the next time) and deploy.

Restores only run into the empty volume of Postgres Restore. They never write to the bucket, and a changed target on a filled volume is ignored with a warning. After losing the Postgres volume, use an explicit time from before the loss, not latest.

Scaling and tuning: raise the Postgres service's RAM and CPU, then set PG_MEMORY_LIMIT_MB / PG_CPU_LIMIT so caches follow (the auto budget is capped at 8 GB). ALTER SYSTEM overrides any tuned value. Raise DEFAULT_POOL_SIZE on PgBouncer for more concurrent queries. Give Postgres Restore a volume as large as the database. Hobby volumes start at 5 GB, Pro volumes at 50 GB and can be resized.

Versions and upgrades: Postgres and pgBackRest follow Railway's postgres-ssl:18 tag (minor and security updates on redeploy). The PITR tools are built from the same tag. PgBouncer is pinned to 1.26.1; change the tag to upgrade. Use Railway's PgBouncer or connection pooling, not both.

Cost: Railway's usage prices apply. About $2 per month idle. For a 5 GB database writing about 1 GB of WAL a day, expect about $17 per month. That includes about 25 GB of bucket storage ($0.015 per GB-month) and about 35 GB of monthly uploads, which count as service egress. Fewer kept fulls or a longer POSTGRES_ARCHIVE_TIMEOUT lower it.

Security: the database password is generated at deploy. Only Postgres has a public TCP proxy (remove it in Settings → Networking for a private database). The bucket keys can read and delete backups, and Railway Buckets have no versioning or object lock, so limit who can see variables. Keep an extra off-site copy if you need immutable backups.

Why Deploy PostgreSQL PITR on Railway?

Railway runs the database, the pooler, the backup checks and the restore slot together on a private network, with volumes and a bucket in the same project. You get a Postgres that can be rewound to any second of the last weeks, without operating backup servers, on the same image and variables as Railway's own Postgres.

Guides for this template

View PostgreSQL 18 with Point-in-Time Recovery on railway.com

Similar templates

TemplateCategoryDeploymentsDeploys
PostgreSQL
PostgreSQL database service
Storage394,257
MySQL
Deploy a MySQL database service
Storage269,034
MongoDB
MongoDB database service
Storage40,707
Redis
Deploy and Host Redis with Railway
Storage25,288
Redis (Bitnami)
Redis key-value data store
Storage15,087
pgvector
Open-source vector similarity search for Postgres
Storage2,077