PostgreSQL in Production: A Practical Checklist

PostgreSQL in Production: A Practical Checklist

Getting Postgres running locally is straightforward. Running it reliably in production requires attention to configuration, backups, observability, and security. This lesson covers the practical checklist that CPCODELAB follows on every project.

Connection & Security

• Never use the postgres superuser from application code. Create a dedicated application role with the minimum necessary privileges.
• Restrict pg_hba.conf to only allow connections from known IP ranges. Disable remote connections for the superuser.
• Use SSL/TLS for all connections (?sslmode=require in the connection string).
• Store credentials in environment variables or a secrets manager — never in code.
• Enable log_connections and log_disconnections so you can audit who is connecting.

-- Create a limited application role
CREATE ROLE app_user LOGIN PASSWORD 'a-very-strong-password';
GRANT CONNECT ON DATABASE cpcode_app TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- For future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

Backups

• pg_dump — logical backup of a single database. Portable and human-readable.
• pg_basebackup — physical backup of the entire cluster. Required for PITR.
• WAL archiving + PITR — stream WAL files to object storage for point-in-time recovery. The gold standard for production.
• Test restores regularly — a backup you have never tested is not a backup.
• Managed services like Neon, Supabase, and RDS handle backups automatically.

# Create a compressed logical backup
pg_dump -U myuser -d cpcode_app -Fc -f backup.dump

# Restore from a pg_dump backup
pg_restore -U myuser -d cpcode_app_restore -Fc backup.dump

Observability & Tuning

• Enable pg_stat_statements to track the slowest queries over time.
• Set log_min_duration_statement = 1000 to log queries slower than 1 second.
• Monitor replication lag if you use read replicas.
• Set work_mem, shared_buffers, and effective_cache_size based on your server RAM.
• Run VACUUM regularly (autovacuum is on by default; tune its thresholds for high-write tables).
• Use pg_activity or pgBadger for real-time and historic query analysis.

-- Enable pg_stat_statements (add to postgresql.conf: shared_preload_libraries = 'pg_stat_statements')
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find the slowest queries
SELECT
  round(total_exec_time::numeric, 2) AS total_ms,
  calls,
  round(mean_exec_time::numeric, 2)  AS mean_ms,
  left(query, 120)                   AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;