Hands-on guide series
PostgreSQL for Production: A Hands-On Guide
A four-part, hands-on guide to running PostgreSQL in production on a single VPS: least-privilege roles, backups that actually restore, connection pooling, and tuning. Every command tested on a live 2-vCPU server.
What you will build
Security is layers, not a switch. Each part closes a different category of attack, from the automated sweep that finds a brand-new server, to the day one gets in anyway. Read them in order, or jump to what you need.
- 1
Roles and Least PrivilegeBuild PostgreSQL roles with least privilege on a live database, then prove each limit holds by watching it refuse the operations an attacker would try.9 min - 2
Backups That Actually RestoreTake a logical backup of a live PostgreSQL database, then do the step everyone skips: restore it into a clean database and check the row counts match.9 min - 3
Connections and PoolingPostgreSQL runs one process per connection. Put PgBouncer connection pooling in front so hundreds of app connections ride on a handful of real ones.9 min - 4
Tuning and MonitoringPostgreSQL tuning and monitoring on a 1.9 GB VPS: size its memory to your RAM, then use EXPLAIN and pg_stat_statements to find the query costing you the most.10 min