---
title: "Postgres in Production"
slug: "postgres-prod"
category: "ops"
tags: ["ops", "postgres", "production", "backups", "failover"]
status: "stable"
last_updated: 2026-05-14
summary: "Managed vs self-hosted Postgres, the backup-and-restore drill, point-in-time recovery, and the failover playbook you should run before you need it."
related: ["[[backend/postgres]]", "[[backend/migrations]]", "[[backend/observability]]", "[[backend/postgres-replication]]", "[[backend/postgres-vacuum]]", "[[ops/hostinger-vps]]", "[[ops/cloudflare]]"]
---

## Overview

Run Postgres on a managed provider until you have a concrete reason not to. The provider handles backups, patching, replication, and pager duty; you handle schema and queries. This page covers production decisions downstream of [[backend/postgres]]: hosting, backups, PITR, failover, pooling, replicas, secrets, and observability.

## Default to a managed provider

Pick from Neon, Supabase, AWS RDS or Aurora, Google Cloud SQL, or Crunchy Bridge. Each ships automated backups, minor-version patching, monitoring, and a one-click read replica.

- Neon: branchable Postgres with separated storage and compute. Good for preview environments.
- Supabase: Postgres plus auth, storage, edge functions. Good when the app needs more than the database.
- RDS or Cloud SQL: boring, durable, expensive at the high end. Pick when you already live in AWS or GCP.

Self-host only when the managed bill is provably worse at scale, residency laws pin you to a region no provider serves, or you need an extension the provider blocks. See [[ops/hostinger-vps]] for the hardening baseline.

## Backups are useless until you have restored one

A backup you have not restored is a hope. Run a full restore drill quarterly and after any backup config change.

```bash
# Nightly logical dump, parallel, custom format
pg_dump --jobs=4 --format=directory --file=/backups/app-$(date +%F) app_prod

# Restore drill: into a scratch instance, end-to-end timed
pg_restore --jobs=4 --dbname=app_restore /backups/app-2026-05-14
```

Push dumps off-box with `restic` or the provider's snapshot tooling; see [[ops/hostinger-vps]]. Keep seven daily, four weekly, and twelve monthly snapshots. The runbook records wall-clock time to first query after restore.

## Layer WAL archiving for point-in-time recovery

Logical dumps give you a daily RPO. Pair them with continuous WAL archiving when you need minutes.

- Managed providers ship PITR by default. Confirm the retention window (Neon: 7 to 30 days; RDS: up to 35) and the latest restorable timestamp.
- Self-hosted: configure `archive_command` to ship WAL segments to S3. `pgBackRest` or `wal-g` wrap this with retention, encryption, and parallel restore.

RPO is the WAL archive interval, typically under a minute. RTO is dominated by replay since the last base backup; size that against your incident budget.

## Drill the failover before the incident

A failover playbook exists when someone has timed it end to end. Run the drill quarterly.

- Managed: trigger the provider's failover button. Time endpoint propagation, pool reconvergence, app reconnect.
- Self-hosted: promote a replica with `pg_ctl promote` or Patroni; update the connection string at the pool.

Write the steps, the expected duration, and the rollback path. The playbook lives next to the runbook.

## Pool connections, do not multiply them

Postgres is one process per connection. A hundred app workers each holding ten connections starves the server. Use PgBouncer in transaction mode for any service with more than a few workers; Supabase and Neon ship their own pooler. See pooling rules in [[backend/postgres]], Prisma flags in [[backend/prisma]], and the rollout discipline in [[backend/migrations]].

## Read replicas are for read scale, not safety

A replica that lags by ten seconds is not a backup. Use replicas to offload read-heavy queries (reporting, search indexing) and to keep a failover candidate warm. Do not point the nightly backup job at a replica; back up from primary or a dedicated backup-only node. See [[backend/postgres-replication]] for the streaming-vs-logical trade-offs.

## Rotate secrets out of plain env vars

Never store the production database URL in a `.env` committed to history. Use a secret manager (AWS Secrets Manager, GCP Secret Manager, Doppler, 1Password) and rotate on a schedule. Prefer short-lived IAM credentials where supported (RDS IAM auth, Cloud SQL IAM). [[backend/fastapi]] and other clients read the URL from the store at startup, not from disk.

## Wire observability before you need it

Three signals catch most production pain.

- `pg_stat_statements`: enable in `shared_preload_libraries`; review top time-consumers weekly. Feed it into [[backend/postgres-explain]] for the plan-level read, and into [[backend/postgres-indexes]] for the index decisions.
- Slow query log: log statements over 250 ms. Ship to the same log pipeline as the app.
- Autovacuum: alert on `n_dead_tup` ratios and `last_autovacuum` age for hot tables. Bloat is the slowest, most preventable production decay. See [[backend/postgres-vacuum]] for the per-table tuning.

Pair with host metrics from [[ops/hostinger-vps]] (CPU, memory, disk, IO wait) and app-side traces and SLOs from [[backend/observability]]. Alert on disk above 80 percent and replication lag above the RPO target.

## Related

- [[backend/postgres]]
- [[backend/migrations]]
- [[backend/observability]]
- [[ops/hostinger-vps]]
- [[ops/cloudflare]]