---
title: "Deploy PostgreSQL 18 with Point-in-Time Recovery"
description: "Postgres 18 with continuous WAL backups to a bucket, PgBouncer and restore."
category: "Storage"
url: https://railway.com/deploy/postgresql-18-with-point-in-time-recover
---

# Deploy PostgreSQL 18 with Point-in-Time Recovery

Postgres 18 with continuous WAL backups to a bucket, PgBouncer and restore.

**[Deploy PostgreSQL 18 with Point-in-Time Recovery on Railway](https://railway.com/template/postgresql-18-with-point-in-time-recover)**

Machine-readable deploy manifest (JSON, validated by TemplateCI): https://railway.com/deploy/postgresql-18-with-point-in-time-recover/manifest.json

- **Creator:** bento
- **Category:** Storage
- **Total deploys:** 1

## Template content

### Postgres https://devicons.railway.app/i/postgresql.svg

- **Image:** ghcr.io/railwayapp-templates/postgres-ssl:18
- **Start command:** `/usr/bin/tini -g -- /bin/bash -c 'set -e; D="${PGDATA%/}"; F="$D/pitr-tuning.conf"; C="$D/postgresql.conf"; I=/docker-entrypoint-initdb.d; X=/tmp/pitr-tuning.conf; R="${WAL_RECOVER_FROM_BUCKET:-}${POSTGRES_RECOVERY_TARGET_TIME:-}${POSTGRES_RECOVERY_TARGET_XID:-}"; if [ -n "$R" ] && [ -n "${WAL_ARCHIVE_BUCKET:-}" ] && [ -s "$D/PG_VERSION" ] && [ ! -e "$D/.pgbackrest_restored" ] && [ ! -e "$D/.pitr_staging" ] && [ ! -e "$D/.pitr_configured" ]; then echo "pitr-guard: WAL_RECOVER_FROM_* or POSTGRES_RECOVERY_TARGET_* is set on this live database; ignoring it so this data directory is never rewound in place. Set PITR_RESTORE_TARGET_TIME on the Postgres Restore service instead, and remove these variables here." >&2; unset WAL_RECOVER_FROM_BUCKET WAL_RECOVER_FROM_ENDPOINT WAL_RECOVER_FROM_REGION WAL_RECOVER_FROM_KEY WAL_RECOVER_FROM_SECRET WAL_RECOVER_FROM_PATH WAL_RECOVER_FROM_S3_URI_STYLE POSTGRES_RECOVERY_TARGET_TIME POSTGRES_RECOVERY_TARGET_XID; fi; echo "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;" > $I/zz-pitr-extensions.sql; k() { v=$1; if [ $v -lt $2 ]; then v=$2; fi; if [ $v -gt $3 ]; then v=$3; fi; echo $v; }; n() { case "$1" in ""|*[!0-9]*) echo $2 ;; *) echo $1 ;; esac; }; w() { echo "$1 = $2" >> $X; }; if [ "${PG_TUNING:-auto}" = off ]; then rm -f $F $I/zz-pitr-conf.sh; echo "pitr-tune: PG_TUNING=off, Postgres defaults apply"; else H=$(( $(n "$(grep -m 1 MemTotal /proc/meminfo | tr -s " " | cut -d " " -f 2)" 1048576) / 1024 )); M=$(( $(n "$(cat /sys/fs/cgroup/memory.max 2>/dev/null)" $((H * 1048576))) / 1048576 )); if [ $M -gt $H ]; then M=$H; fi; B=$(k $(n "$PG_MEMORY_LIMIT_MB" $(k $M 256 8192)) 256 4194304); N=$(nproc); read -r Q P 2>/dev/null < /sys/fs/cgroup/cpu.max || Q=max; case "$Q" in ""|*[!0-9]*) ;; *) N=$(k $(( (Q + P - 1) / P )) 1 $N) ;; esac; N=$(k $(n "$PG_CPU_LIMIT" $(k $N 1 8)) 1 256); V=$(( $(n "$(df -Pk "${D%/*}" 2>/dev/null | tail -n 1 | tr -s " " | cut -d " " -f 2)" 5242880) / 1024 )); Z=pglz; L=$(ldd "$(command -v postgres)" 2>/dev/null || true); case "$L" in *libzstd*) Z=zstd ;; *liblz4*) Z=lz4 ;; esac; PW=$(k $N 2 64); echo "# PITR template: rewritten at every boot from budget ${B}MB (container ${M}MB), ${N} cpus, volume ${V}MB; ALTER SYSTEM overrides it" > $X; w shared_buffers $(k $((B / 4)) 128 1048576)MB; w effective_cache_size $((B * 3 / 4))MB; w maintenance_work_mem $(k $((B / 16)) 64 1024)MB; w work_mem $(k $((B / 256)) 4 64)MB; w min_dynamic_shared_memory $(k $((B / 32)) 32 512)MB; w max_worker_processes $(k $((PW + 4)) 8 128); w max_parallel_workers $PW; w max_parallel_workers_per_gather $(k $((N / 2)) 0 2); w max_parallel_maintenance_workers $(k $((N / 2)) 1 4); w random_page_cost 1.1; w effective_io_concurrency 200; w maintenance_io_concurrency 200; w track_io_timing on; w wal_compression $Z; w checkpoint_timeout 15min; w max_wal_size $(k $((V / 16)) 1024 8192)MB; chmod 644 $X; if [ -f $D/PG_VERSION ] && [ -f $C ]; then install -o postgres -g postgres -m 600 $X $F; grep -qsF "include_if_exists = pitr-tuning.conf" $C || echo "include_if_exists = pitr-tuning.conf" >> $C; sync $F $C 2>/dev/null || true; else echo "cp $X $F && chmod 600 $F && { grep -qsF pitr-tuning.conf $C || echo include_if_exists = pitr-tuning.conf >> $C; }" > $I/zz-pitr-conf.sh; fi; echo "pitr-tune: $(paste -sd " " $X)"; fi; exec /usr/local/bin/wrapper.sh "$@"' pitr-start postgres -p 5432 -c listen_addresses=*`

### Postgres Restore https://cdn.jsdelivr.net/gh/homarr-labs/dashboard-icons/svg/postgresql.svg

- **Source:** baranberkay96/postgres-pitr-railway

### PITR Monitor https://cdn.jsdelivr.net/gh/homarr-labs/dashboard-icons/svg/healthchecks.svg

- **Source:** baranberkay96/postgres-pitr-railway

### PgBouncer https://cdn.jsdelivr.net/gh/homarr-labs/dashboard-icons/svg/postgresql.svg

- **Image:** edoburu/pgbouncer:v1.26.1-p0

## Buckets

- **Postgres-PITR**

## Documentation

# Deploy and Host PostgreSQL PITR with Railway

[![Deploy on Railway](https://railway.com/button.svg)](https://railway.com/deploy/postgresql-18-with-point-in-time-recovery?referralCode=20H68q)

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

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


## Similar templates

- [RustFS | (Just Updated) S3 Object Store, Public URL and Keys Set](https://railway.com/deploy/rustfs-or-just-updated-s3-object-store-p) — S3-compatible object store, keys and public URL preset, volume kept
- [TimescaleDB](https://railway.com/deploy/timescaledb-railway-template) — A PostgreSQL-based time-series database for analytics on Railway.
- [Garage S3 Storage](https://railway.com/deploy/garage-s3-storage) — Ultra-light S3 server: fast, open-source, plug-and-play.

Open this page in a browser: https://railway.com/deploy/postgresql-18-with-point-in-time-recover