PostgreSQL in Docker: Production Setup, Tuning and High Availability

PostgreSQL is the most popular open-source relational database, and running it in Docker is now common even in production environments. But there is a substantial difference between spinning up a postgres:16 container for local development and operating a production-grade PostgreSQL deployment that handles real traffic, survives failures, and performs under load.

This guide covers everything required to run PostgreSQL in Docker for production: proper volume layout, postgresql.conf tuning for containerized environments, connection pooling with PgBouncer, backup strategies using pg_dump and pg_basebackup, streaming and logical replication for high availability, monitoring, resource limits, and essential extensions.

Production Docker Compose Setup

A minimal development Compose file with POSTGRES_PASSWORD=password and no volume configuration is a recipe for data loss. Here is a production-ready foundation:

services:
  postgres:
    image: postgres:16-alpine
    container_name: postgres
    restart: unless-stopped
    shm_size: 256mb
    ports:
      - "127.0.0.1:5432:5432"
    environment:
      POSTGRES_USER: ${PG_USER:-appuser}
      POSTGRES_PASSWORD_FILE: /run/secrets/pg_password
      POSTGRES_DB: ${PG_DB:-appdb}
      POSTGRES_INITDB_ARGS: "--data-checksums --encoding=UTF8 --locale=en_US.utf8"
    secrets:
      - pg_password
    volumes:
      - pgdata:/var/lib/postgresql/data
      - ./postgresql/postgresql.conf:/etc/postgresql/postgresql.conf:ro
      - ./postgresql/pg_hba.conf:/etc/postgresql/pg_hba.conf:ro
      - ./backups:/backups
    command: >
      postgres
        -c config_file=/etc/postgresql/postgresql.conf
        -c hba_file=/etc/postgresql/pg_hba.conf
    deploy:
      resources:
        limits:
          cpus: '4.0'
          memory: 8G
        reservations:
          cpus: '2.0'
          memory: 4G
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U ${PG_USER:-appuser} -d ${PG_DB:-appdb}"]
      interval: 10s
      timeout: 5s
      retries: 5
      start_period: 30s

secrets:
  pg_password:
    file: ./secrets/pg_password.txt

volumes:
  pgdata:
    driver: local

Several details here matter for production deployments:

• shm_size: 256mb - PostgreSQL uses shared memory for parallel query workers and other operations. The default Docker shared memory of 64MB causes cryptic "could not resize shared memory segment" errors under load.
• POSTGRES_PASSWORD_FILE - Uses Docker secrets instead of exposing the password as an environment variable, which would appear in docker inspect output.
• --data-checksums - Enables page-level checksums to detect silent data corruption. This cannot be enabled after initialization.
• 127.0.0.1:5432:5432 - Binds to localhost only. Never expose PostgreSQL to 0.0.0.0 in production.
• Resource limits - Prevents PostgreSQL from consuming all host memory during query spikes.

Volume Configuration

The volume strategy for PostgreSQL in Docker determines your data durability, backup options, and I/O performance.

Named Volumes vs Bind Mounts

For production on Linux, named volumes are recommended. They are managed by Docker, which avoids permission issues, and their backing directory can be placed on dedicated storage using a custom volume driver or by configuring Docker's data root.

Separate WAL and Data Volumes

For high-throughput workloads, separate the Write-Ahead Log (WAL) from the main data directory by placing them on different volumes backed by different disks:

volumes:
  - pgdata:/var/lib/postgresql/data
  - pgwal:/var/lib/postgresql/data/pg_wal

# Or with bind mounts on separate disks:
volumes:
  - /mnt/ssd-data/pgdata:/var/lib/postgresql/data
  - /mnt/nvme-wal/pgwal:/var/lib/postgresql/data/pg_wal

WAL writes are sequential and latency-sensitive. Isolating them on fast storage (NVMe) while keeping data on larger SSD storage improves both write throughput and checkpoint performance.

PostgreSQL.conf Tuning for Docker

The default postgresql.conf is configured for a machine with 128MB of RAM. For a container with 8GB allocated, you need significant tuning. Here are the critical parameters:

# Memory Configuration
shared_buffers = 2GB              # 25% of container memory limit
effective_cache_size = 6GB        # 75% of container memory limit
work_mem = 64MB                   # Per-operation sort/hash memory
maintenance_work_mem = 512MB      # VACUUM, CREATE INDEX, etc.
huge_pages = try                  # Use if kernel supports it

# WAL Configuration
wal_level = replica               # Required for replication
max_wal_size = 4GB                # Before forced checkpoint
min_wal_size = 1GB
wal_compression = zstd            # Reduce WAL I/O (PG 15+)
wal_buffers = 64MB

# Checkpoint Tuning
checkpoint_completion_target = 0.9  # Spread checkpoint I/O
checkpoint_timeout = 15min

# Query Planner
random_page_cost = 1.1            # SSD storage (default 4.0 is for HDD)
effective_io_concurrency = 200    # SSD: 200, HDD: 2
seq_page_cost = 1.0

# Connection Settings
max_connections = 200             # Keep low, use PgBouncer instead
listen_addresses = '*'

# Logging
log_min_duration_statement = 1000   # Log queries > 1 second
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
log_autovacuum_min_duration = 0

# Autovacuum Tuning
autovacuum_max_workers = 4
autovacuum_naptime = 30s
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.025

Connection Pooling with PgBouncer

PostgreSQL creates a new process for each connection, consuming roughly 10MB of memory per connection. With 200 connections, that is 2GB of overhead before any query work happens. PgBouncer solves this by multiplexing many client connections over a small number of server connections.

services:
  pgbouncer:
    image: edoburu/pgbouncer:1.22.0
    container_name: pgbouncer
    restart: unless-stopped
    ports:
      - "127.0.0.1:6432:6432"
    environment:
      DATABASE_URL: postgres://${PG_USER}:${PG_PASSWORD}@postgres:5432/${PG_DB}
      POOL_MODE: transaction
      MAX_CLIENT_CONN: 1000
      DEFAULT_POOL_SIZE: 50
      MIN_POOL_SIZE: 10
      RESERVE_POOL_SIZE: 10
      RESERVE_POOL_TIMEOUT: 3
      SERVER_IDLE_TIMEOUT: 600
      SERVER_LIFETIME: 3600
      LOG_CONNECTIONS: 1
      LOG_DISCONNECTIONS: 1
      STATS_PERIOD: 60
    depends_on:
      postgres:
        condition: service_healthy
    healthcheck:
      test: ["CMD", "pg_isready", "-h", "localhost", "-p", "6432"]
      interval: 10s
      timeout: 5s
      retries: 3

Applications connect to PgBouncer on port 6432 instead of PostgreSQL on 5432. In transaction mode, a server connection is assigned to a client only for the duration of a transaction, allowing 1000 application connections to share 50 database connections.

Important: Transaction pooling mode breaks session-level features like prepared statements, SET commands, and LISTEN/NOTIFY. If your application relies on these, use session mode or configure your ORM to avoid prepared statements (e.g., prepared_statements: false in Rails).

Backup Strategies

Two complementary backup methods should run together for production PostgreSQL.

Logical Backups with pg_dump

Logical backups produce SQL or custom-format dumps that are portable and selectively restorable:

# Custom format (compressed, supports parallel restore)
docker exec postgres pg_dump -U appuser -Fc appdb \
  > /backups/appdb_$(date +%Y%m%d_%H%M%S).dump

# Parallel dump for large databases (4 workers)
docker exec postgres pg_dump -U appuser -Fc -j 4 appdb \
  > /backups/appdb_$(date +%Y%m%d_%H%M%S).dump

# Restore with parallel workers
docker exec -i postgres pg_restore -U appuser -d appdb \
  -j 4 --clean --if-exists < /backups/appdb_20250516_120000.dump

# Dump all databases including roles
docker exec postgres pg_dumpall -U appuser \
  | gzip > /backups/all_$(date +%Y%m%d_%H%M%S).sql.gz

Physical Backups with pg_basebackup

Physical backups copy the entire data directory and support point-in-time recovery (PITR) when combined with WAL archiving:

# Configure WAL archiving in postgresql.conf
archive_mode = on
archive_command = 'cp %p /backups/wal/%f'
archive_timeout = 300  # Archive at least every 5 minutes

# Take a base backup
docker exec postgres pg_basebackup \
  -U replicator -D /backups/base_$(date +%Y%m%d) \
  -Ft -z -Xs -P

# For PITR, restore base backup then replay WAL files up to target time
# recovery.conf (or postgresql.auto.conf in PG 12+):
restore_command = 'cp /backups/wal/%f %p'
recovery_target_time = '2025-05-16 14:30:00'

Streaming Replication

Streaming replication creates real-time copies of your primary database on standby servers. This provides both high availability (automatic failover) and read scaling.

# docker-compose.yml for primary + replica
services:
  postgres-primary:
    image: postgres:16-alpine
    container_name: pg-primary
    environment:
      POSTGRES_USER: appuser
      POSTGRES_PASSWORD: ${PG_PASSWORD}
      POSTGRES_DB: appdb
    volumes:
      - pgdata-primary:/var/lib/postgresql/data
      - ./primary/postgresql.conf:/etc/postgresql/postgresql.conf:ro
      - ./primary/pg_hba.conf:/etc/postgresql/pg_hba.conf:ro
    command: postgres -c config_file=/etc/postgresql/postgresql.conf -c hba_file=/etc/postgresql/pg_hba.conf
    ports:
      - "127.0.0.1:5432:5432"

  postgres-replica:
    image: postgres:16-alpine
    container_name: pg-replica
    environment:
      PGUSER: replicator
      PGPASSWORD: ${REPL_PASSWORD}
    volumes:
      - pgdata-replica:/var/lib/postgresql/data
      - ./replica/setup-replica.sh:/docker-entrypoint-initdb.d/setup-replica.sh:ro
    depends_on:
      postgres-primary:
        condition: service_healthy
    ports:
      - "127.0.0.1:5433:5432"

volumes:
  pgdata-primary:
  pgdata-replica:

The primary needs these settings in postgresql.conf:

# Primary postgresql.conf additions
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB
synchronous_commit = on
# For synchronous replication (ensures no data loss):
# synchronous_standby_names = 'replica1'

And the primary's pg_hba.conf needs a replication entry:

# pg_hba.conf - allow replication connections
host replication replicator 0.0.0.0/0 scram-sha-256

The replica setup script initializes from the primary using pg_basebackup:

#!/bin/bash
# setup-replica.sh
if [ ! -f /var/lib/postgresql/data/PG_VERSION ]; then
  rm -rf /var/lib/postgresql/data/*
  pg_basebackup -h postgres-primary -U replicator -D /var/lib/postgresql/data \
    -Fp -Xs -P -R
  echo "primary_conninfo = 'host=postgres-primary port=5432 user=replicator password=${REPL_PASSWORD}'" \
    >> /var/lib/postgresql/data/postgresql.auto.conf
fi

Logical Replication

For selective table replication or cross-version replication, PostgreSQL's logical replication is more flexible:

# On the primary (publisher)
CREATE PUBLICATION my_pub FOR TABLE users, orders, products;

# On the replica (subscriber)
CREATE SUBSCRIPTION my_sub
  CONNECTION 'host=postgres-primary port=5432 dbname=appdb user=replicator password=secret'
  PUBLICATION my_pub;

Logical replication allows different indexes on publisher and subscriber, replication of specific tables, and even cross-version upgrades where the subscriber runs a newer PostgreSQL version.

Monitoring PostgreSQL in Docker

Add postgres_exporter as a sidecar to expose metrics to Prometheus:

  postgres-exporter:
    image: prometheuscommunity/postgres-exporter:v0.15.0
    container_name: pg-exporter
    restart: unless-stopped
    environment:
      DATA_SOURCE_NAME: "postgresql://appuser:${PG_PASSWORD}@postgres:5432/appdb?sslmode=disable"
    ports:
      - "127.0.0.1:9187:9187"
    depends_on:
      postgres:
        condition: service_healthy

Key metrics to alert on:

Useful diagnostic queries to run periodically:

-- Check cache hit ratio (should be > 99% for OLTP)
SELECT
  sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS ratio
FROM pg_statio_user_tables;

-- Find long-running queries
SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
  AND state != 'idle'
ORDER BY duration DESC;

-- Check bloat and autovacuum status
SELECT schemaname, relname, n_dead_tup, n_live_tup,
  round(n_dead_tup::numeric / greatest(n_live_tup, 1) * 100, 2) AS dead_pct,
  last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Resource Limits and Docker Settings

Docker's resource controls are critical for PostgreSQL. Without them, a runaway query or connection storm can starve the host of resources.

deploy:
  resources:
    limits:
      cpus: '4.0'
      memory: 8G
    reservations:
      cpus: '2.0'
      memory: 4G

Important kernel parameters to set on the Docker host for PostgreSQL performance:

# /etc/sysctl.conf on the Docker host
vm.overcommit_memory = 2
vm.overcommit_ratio = 80
vm.swappiness = 1
kernel.shmmax = 8589934592    # 8GB
kernel.shmall = 2097152

Essential Extensions

The official PostgreSQL Docker image includes contrib extensions. Enable the most useful ones:

-- Performance monitoring
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- Add to postgresql.conf: shared_preload_libraries = 'pg_stat_statements'

-- UUID generation
CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- Or for v4 UUIDs without pgcrypto:
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Full-text search helpers
CREATE EXTENSION IF NOT EXISTS unaccent;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Check top queries by total time
SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

For extensions not included in the base image, build a custom Dockerfile:

FROM postgres:16-alpine
RUN apk add --no-cache postgresql16-postgis postgresql16-pgvector
# Extensions are now available for CREATE EXTENSION

Security Hardening

Beyond the basics of strong passwords and network restrictions:

1. Use scram-sha-256 authentication - Set password_encryption = scram-sha-256 in postgresql.conf. MD5 is considered weak.
2. Restrict pg_hba.conf - Only allow connections from known networks. Never use trust authentication in production.
3. Enable SSL - Mount TLS certificates and set ssl = on with ssl_cert_file and ssl_key_file.
4. Use a dedicated database user per application - Never let applications connect as the superuser.
5. Run as non-root - The official PostgreSQL image already runs as the postgres user (UID 999), but verify with docker exec postgres id.
6. Enable row-level security - For multi-tenant applications, use RLS policies to enforce data isolation at the database level.

# pg_hba.conf - production example
# TYPE  DATABASE  USER        ADDRESS          METHOD
local   all       postgres                     peer
host    appdb     appuser     172.16.0.0/12    scram-sha-256
host    replication replicator 172.16.0.0/12   scram-sha-256
host    all       all         0.0.0.0/0        reject

Docker management platforms like usulnet provide visibility into which containers expose PostgreSQL ports, making it straightforward to audit your database security posture across multiple hosts. Combined with Docker resource monitoring, you can track connection counts, memory usage, and container health from a single dashboard.

Putting It All Together

A production PostgreSQL deployment in Docker is not a single container. It is an architecture that includes the database, a connection pooler, a backup sidecar, a metrics exporter, and potentially replicas. Here is the checklist:

1. Data durability: Named volumes with data checksums enabled, WAL archiving configured
2. Performance: postgresql.conf tuned for container resources, PgBouncer for connection pooling, separate WAL volume for high-throughput workloads
3. Backup: pg_dump for logical backups, pg_basebackup + WAL archiving for PITR, automated scheduling, and regular restore tests
4. High availability: Streaming replication with at least one synchronous standby, automated failover with Patroni or pg_auto_failover
5. Monitoring: postgres_exporter for Prometheus, alerts on connection saturation, replication lag, and autovacuum health
6. Security: SCRAM-SHA-256 auth, restrictive pg_hba.conf, SSL/TLS, non-superuser application accounts
7. Resource limits: Docker memory and CPU limits aligned with postgresql.conf settings, proper shm_size

Each of these layers is necessary. Skip one and you have a gap that will surface during an incident. Start with the Compose file and tuned postgresql.conf, add PgBouncer and backups next, then layer on replication and monitoring as your traffic grows.