Running PostgreSQL in Docker for Production: A Complete Guide

Running PostgreSQL in Docker for development is easy. Production requires more care. Here's what it takes to run reliably.

Production Docker Compose

# docker-compose.yml
services:
  postgres:
    image: postgres:16-alpine
    restart: unless-stopped
    environment:
      POSTGRES_DB: myapp
      POSTGRES_USER: myapp
      POSTGRES_PASSWORD_FILE: /run/secrets/postgres_password
      PGDATA: /var/lib/postgresql/data/pgdata  # Subdirectory avoids volume issues
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./postgres/init:/docker-entrypoint-initdb.d  # Run on first init
      - ./postgres/conf/postgresql.conf:/etc/postgresql/postgresql.conf
    command: postgres -c config_file=/etc/postgresql/postgresql.conf
    ports:
      - "127.0.0.1:5432:5432"  # Bind to localhost ONLY -- never expose to internet
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U myapp -d myapp"]
      interval: 10s
      timeout: 5s
      retries: 5
      start_period: 30s
    secrets:
      - postgres_password

  pgbouncer:
    image: bitnami/pgbouncer:latest
    restart: unless-stopped
    environment:
      POSTGRESQL_HOST: postgres
      POSTGRESQL_PORT: 5432
      POSTGRESQL_DATABASE: myapp
      POSTGRESQL_USERNAME: myapp
      PGBOUNCER_POOL_MODE: transaction
      PGBOUNCER_MAX_CLIENT_CONN: 1000
      PGBOUNCER_DEFAULT_POOL_SIZE: 25
    ports:
      - "127.0.0.1:6432:6432"  # App connects to PgBouncer, not Postgres directly
    depends_on:
      postgres:
        condition: service_healthy

volumes:
  postgres_data:
    driver: local

secrets:
  postgres_password:
    file: ./secrets/postgres_password.txt

Performance Configuration

# postgres/conf/postgresql.conf

# Memory (adjust for your server)
shared_buffers = 256MB          # 25% of RAM
effective_cache_size = 768MB    # 75% of RAM
work_mem = 16MB                 # Per-sort operation, can be high with many connections
maintenance_work_mem = 64MB     # For VACUUM, CREATE INDEX

# Write performance
wal_buffers = 16MB
checkpoint_completion_target = 0.9
max_wal_size = 1GB

# Query optimization
random_page_cost = 1.1          # SSD: set to 1.1 (HDD default is 4)
effective_io_concurrency = 200  # SSD: 200, HDD: 2

# Connections
max_connections = 100           # Don't set too high -- use PgBouncer instead

Automated Backups

#!/bin/bash
# backup.sh -- run via cron: 0 2 * * * /opt/backup.sh

set -e

BACKUP_DIR="/var/backups/postgres"
DATE=$(date +%Y%m%d_%H%M%S)
CONTAINER="myapp-postgres-1"
DB_NAME="myapp"
DB_USER="myapp"

mkdir -p "$BACKUP_DIR"

# Dump database
docker exec "$CONTAINER" \
    pg_dump -U "$DB_USER" -d "$DB_NAME" --format=custom \
    | gzip > "$BACKUP_DIR/backup_$DATE.pgdump.gz"

# Keep last 7 daily backups
find "$BACKUP_DIR" -name "backup_*.pgdump.gz" -mtime +7 -delete

# Upload to S3 (recommended for disaster recovery)
aws s3 cp "$BACKUP_DIR/backup_$DATE.pgdump.gz" \
    "s3://my-backups/postgres/backup_$DATE.pgdump.gz"

echo "Backup completed: backup_$DATE.pgdump.gz"

Restore Procedure

# Stop the app first
docker compose stop app

# Restore from dump
gunzip -c backup_20260401_020000.pgdump.gz | \
docker exec -i myapp-postgres-1 \
    pg_restore -U myapp -d myapp --clean --no-acl --no-owner

# Restart
docker compose start app

Test your restore procedure before you need it. Backups are only as good as a successful restore.

Read Replica for Scaling

  postgres-replica:
    image: postgres:16-alpine
    restart: unless-stopped
    environment:
      POSTGRES_MASTER_HOST: postgres
      PGUSER: replicator
      PGPASSWORD_FILE: /run/secrets/replication_password
    command: |
      bash -c "
        until pg_basebackup -h postgres -U replicator -p 5432 -D /var/lib/postgresql/data -Fp -Xs -P -R; do
          sleep 1
        done
        chmod 700 /var/lib/postgresql/data
        exec postgres
      "
    volumes:
      - postgres_replica_data:/var/lib/postgresql/data
    depends_on:
      postgres:
        condition: service_healthy

On the primary, create the replication user:

CREATE USER replicator WITH REPLICATION LOGIN PASSWORD 'secure_password';

Route read queries to the replica, writes to primary:

const readPool = new Pool({ connectionString: process.env.REPLICA_URL })
const writePool = new Pool({ connectionString: process.env.PRIMARY_URL })

Monitoring

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

-- Long-running queries
SELECT pid, query, now() - pg_stat_activity.query_start AS duration, state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '30 seconds';

-- Table sizes
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

-- Cache hit rate (should be >99%)
SELECT sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS ratio
FROM pg_statio_user_tables;

The most common production PostgreSQL problems are: insufficient shared_buffers, direct connections without a pooler, no backup testing, and forgetting to run VACUUM ANALYZE after bulk operations.