Setting Up a Production-Ready PostgreSQL Database

PostgreSQL is one of the most powerful open-source databases. But default configurations are meant for development, not production. This guide walks you through essential production settings.

1. Memory Configuration

The most impactful settings for performance:

# postgresql.conf

# Set to 25% of total RAM (e.g., 4GB for 16GB server)
shared_buffers = 4GB

# Set to 50-75% of total RAM
effective_cache_size = 12GB

# RAM per sort/hash operation
work_mem = 256MB

# For CREATE INDEX, VACUUM, etc.
maintenance_work_mem = 1GB

2. Connection Settings

# Maximum connections (use connection pooling!)
max_connections = 200

# Use PgBouncer for connection pooling
# pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 50

3. Write-Ahead Log (WAL) Settings

wal_level = replica
max_wal_senders = 5
wal_keep_size = 1GB
archive_mode = on
archive_command = 'cp %p /backup/wal_archive/%f'

4. Security Hardening

# pg_hba.conf - Restrict access
# TYPE  DATABASE  USER      ADDRESS       METHOD
local   all       postgres                peer
host    all       all       10.0.0.0/8    scram-sha-256
host    all       all       0.0.0.0/0     reject

# Enable SSL
ssl = on
ssl_cert_file = '/etc/ssl/certs/server.crt'
ssl_key_file = '/etc/ssl/private/server.key'

5. Automated Backups

#!/bin/bash
# backup.sh - Run daily via cron
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backup/postgresql"

pg_dump -Fc -Z9 mydb > "${BACKUP_DIR}/mydb_${DATE}.dump"

# Keep only last 7 days
find ${BACKUP_DIR} -name "*.dump" -mtime +7 -delete

echo "Backup completed: mydb_${DATE}.dump"

6. Monitoring Queries

-- Find slow queries
SELECT query, mean_exec_time, calls, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- Check table bloat
SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename))
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 10;

-- Active connections
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;

Production Checklist

• Memory settings tuned for your hardware
• Connection pooling configured (PgBouncer)
• SSL/TLS enabled
• Automated daily backups with retention policy
• WAL archiving for point-in-time recovery
• Monitoring dashboards set up
• pg_stat_statements extension enabled
• Regular VACUUM ANALYZE scheduled