Keeping data safe for years
A database is only as good as its last backup. Production PostgreSQL also needs schema changes without downtime, connection limits handled, and a plan for when a server dies.
The essentials
- Backups: pg_dump for logical backups (restore with pg_restore); continuous WAL archiving for point-in-time recovery. Test restores regularly.
- Migrations: schema changes live in versioned files (Prisma Migrate, Drizzle, Flyway). Add columns as nullable first, backfill, then add constraints — avoid long table locks.
- Connection pooling: each connection costs memory; serverless apps need a pooler like PgBouncer.
- Replication: streaming replicas for high availability and read scaling.
- Monitoring: slow query logs, pg_stat_statements, disk and connection alerts.
- Managed services (Neon, Supabase, RDS, Cloud SQL) handle much of this for you.
pg_dump --format=custom --file=cafe.dump "$DATABASE_URL" pg_restore --clean --dbname="$STAGING_URL" cafe.dump