Deploy PostgreSQL 18 with Point-in-Time Recovery

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

• PostgreSQL continuous archiving and PITR: https://www.postgresql.org/docs/18/continuous-archiving.html
• pgBackRest user guide: https://pgbackrest.org/user-guide.html
• Railway Postgres image: https://github.com/railwayapp-templates/postgres-ssl
• Railway point-in-time recovery: https://docs.railway.com/volumes/point-in-time-recovery
• Railway Storage Buckets: https://docs.railway.com/storage-buckets
• Railway cron jobs: https://docs.railway.com/cron-jobs
• PgBouncer configuration: https://www.pgbouncer.org/config.html

Implementation Details

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.