PostgreSQL on Docker: Production Setup, Backup, and Performance Tuning

Most Docker PostgreSQL tutorials stop at docker run postgres. That gets you a database, but not one you would trust with production data. No persistent volume, no backups, default config with 128MB shared buffers, and a single connection limit.

This guide covers setting up PostgreSQL in Docker with config that actually holds up under load: persistent storage, automated backups, connection pooling, and performance tuning based on your server's resources.

Prerequisites

• Docker Engine 24+ and Docker Compose v2
• A Linux server with at least 2GB RAM (4GB+ recommended for production)
• Enough disk space for your database and backups

Step 1: Project Structure

mkdir -p pg-docker && cd pg-docker
mkdir -p data backups config

Structure:

pg-docker/
  docker-compose.yml
  config/
    postgresql.conf    # performance tuning
    pg_hba.conf        # authentication rules
  data/                # persistent database storage
  backups/             # automated backup output

Step 2: Write the Docker Compose File

Create docker-compose.yml:

services:
  postgres:
    image: postgres:18.4-alpine
    container_name: postgres
    restart: unless-stopped
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: appuser
      POSTGRES_PASSWORD: changeme-strong-password-here
      POSTGRES_DB: myapp
      PGDATA: /var/lib/postgresql/data/pgdata
    volumes:
      - ./data:/var/lib/postgresql/data
      - ./config/postgresql.conf:/etc/postgresql/postgresql.conf:ro
      - ./config/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
    shm_size: '256mb'

Key details:

• PGDATA moves the data directory inside a subdirectory. This prevents PostgreSQL from refusing to initialize if the volume is not empty.
• The command override tells PostgreSQL to use our custom config files instead of the defaults.
• shm_size sets the shared memory limit. PostgreSQL uses shared memory for its buffer pool. The default 64MB is too low for anything beyond testing.
• The backups volume mount makes backup scripts accessible inside the container.

Step 3: Write the PostgreSQL Config

Create config/postgresql.conf. This is a tuned config for a server with 4GB RAM:

# Connection settings
listen_addresses = '*'
max_connections = 100

# Memory settings (tuned for 4GB RAM server)
shared_buffers = 1GB
effective_cache_size = 3GB
work_mem = 16MB
maintenance_work_mem = 512MB

# WAL settings
wal_buffers = 64MB
checkpoint_completion_target = 0.9
wal_compression = lz4

# Query planning
random_page_cost = 1.1
effective_io_concurrency = 200

# Logging
log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d.log'
log_rotation_age = 1d
log_rotation_size = 100MB
log_min_duration_statement = 1000
log_checkpoints = on
log_connections = on
log_disconnections = on

# Autovacuum
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 1min

How to tune these for your server:

Setting	Formula	Example (4GB RAM)
shared_buffers	25% of RAM	1GB
effective_cache_size	75% of RAM	3GB
work_mem	RAM / max_connections / 4	16MB (4096/100/4)
maintenance_work_mem	5-10% of RAM	512MB

For an 8GB server, double the shared_buffers to 2GB, effective_cache_size to 6GB, and keep work_mem proportional.

Step 4: Write the Authentication Config

Create config/pg_hba.conf:

# TYPE  DATABASE  USER      ADDRESS         METHOD
local   all       all                     scram-sha-256
host    all       all       127.0.0.1/32    scram-sha-256
host    all       all       ::1/128         scram-sha-256
host    myapp     appuser   172.16.0.0/12   scram-sha-256
host    all       all       0.0.0.0/0       reject

This config:

• Requires password authentication (scram-sha-256) for all connections
• Allows the app user from the Docker network (172.16.0.0/12 is the default range)
• Rejects all other remote connections

Adjust the CIDR range if your Docker network uses a different subnet. Find it with docker network inspect <network_name>.

Step 5: Start PostgreSQL

docker compose up -d

Verify it is running:

docker compose ps
docker compose logs postgres | tail -5

Expected log output:

LOG:  starting PostgreSQL 18.4 on x86_64-pc-linux-musl
LOG:  listening on IPv4 address "0.0.0.0", port 5432
LOG:  database system is ready to accept connections

Test the connection:

docker compose exec postgres psql -U appuser -d myapp -c "SELECT version();"

You should see the PostgreSQL version and system info.

Step 6: Create a Useful Schema

Connect and create a test table:

docker compose exec postgres psql -U appuser -d myapp

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users (email);

INSERT INTO users (email, name) VALUES
    ('[email protected]', 'Alice'),
    ('[email protected]', 'Bob');

SELECT * FROM users;

Type q to exit psql.

Step 7: Set Up Automated Backups

Create backups/backup.sh:

#!/bin/bash
set -euo pipefail

BACKUP_DIR="/backups"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/myapp_$TIMESTAMP.sql.gz"

# Run pg_dump and compress
pg_dump -U appuser -d myapp --no-owner --no-privileges | gzip > "$BACKUP_FILE"

# Remove backups older than 7 days
find "$BACKUP_DIR" -name "myapp_*.sql.gz" -mtime +7 -delete

echo "Backup completed: $BACKUP_FILE"

Make it executable and test it:

chmod +x backups/backup.sh
docker compose exec postgres bash /backups/backup.sh

Verify the backup was created:

ls -la backups/

Automate Backups with Cron

Run the backup daily at 2 AM using the host's crontab:

crontab -e

Add:

0 2 * * * cd /path/to/pg-docker && docker compose exec -T postgres bash /backups/backup.sh >> /var/log/pg-backup.log 2>&1

The -T flag allocates a pseudo-TTY without stdin, which prevents cron from hanging.

Restore from Backup

# List available backups
ls backups/

# Restore a specific backup
gunzip -c backups/myapp_20260617_020000.sql.gz | docker compose exec -T postgres psql -U appuser -d myapp

# Or restore into a new database
docker compose exec postgres psql -U appuser -c "CREATE DATABASE myapp_restored;"
gunzip -c backups/myapp_20260617_020000.sql.gz | docker compose exec -T postgres psql -U appuser -d myapp_restored

Step 8: Add pg_stat_statements for Monitoring

pg_stat_statements tracks query performance. Add it to your config.

Update config/postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

Restart PostgreSQL:

docker compose restart postgres

Enable the extension:

docker compose exec postgres psql -U appuser -d myapp -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"

Check slow queries:

SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Step 9: Connection Pooling with PgBouncer

When your application opens many short-lived connections, a connection pooler prevents PostgreSQL from spending resources on connection overhead.

Add PgBouncer to docker-compose.yml:

services:
  postgres:
    image: postgres:18.4-alpine
    container_name: postgres
    restart: unless-stopped
    environment:
      POSTGRES_USER: appuser
      POSTGRES_PASSWORD: changeme-strong-password-here
      POSTGRES_DB: myapp
      PGDATA: /var/lib/postgresql/data/pgdata
    volumes:
      - ./data:/var/lib/postgresql/data
      - ./config/postgresql.conf:/etc/postgresql/postgresql.conf:ro
      - ./config/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
    shm_size: '256mb'
    networks:
      - backend

  pgbouncer:
    image: edoburu/pgbouncer:1.23.1
    container_name: pgbouncer
    restart: unless-stopped
    ports:
      - "6432:6432"
    environment:
      DATABASE_URL: postgres://appuser:***@postgres:5432/myapp
      POOL_MODE: transaction
      MAX_CLIENT_CONN: 1000
      DEFAULT_POOL_SIZE: 20
    depends_on:
      - postgres
    networks:
      - backend

networks:
  backend:
    driver: bridge

Key PgBouncer settings:

• POOL_MODE: transaction returns the connection to the pool after each transaction instead of each session. This lets 20 pool connections serve hundreds of clients.
• MAX_CLIENT_CONN is the maximum number of client connections PgBouncer accepts.
• DEFAULT_POOL_SIZE is how many connections PgBouncer opens to PostgreSQL per user/database pair.

Your application connects to PgBouncer on port 6432 instead of PostgreSQL on 5432. Everything else stays the same.

Common Issues

PostgreSQL refuses to start with "data directory has wrong ownership". The host directory (./data) has wrong permissions. Fix it:

sudo chown -R 999:999 ./data

UID 999 is the default postgres user inside the Alpine-based image.

"FATAL: could not create shared memory segment" The shm_size in Docker Compose is too low for your shared_buffers setting. Match them: if shared_buffers is 1GB, shm_size should be at least 1GB.

Backups are huge. pg_dump without compression produces large files. The backup script above uses gzip. For even better compression, use pg_dump --format=custom (produces a .dump file that pg_restore can read):

pg_dump -U appuser -d myapp --format=custom --file=/backups/myapp_$TIMESTAMP.dump

Connection refused from application container. Your app container is not on the same Docker network as PostgreSQL. Add networks: [backend] to both services, or use the host network for simpler setups.

Slow queries after moving to Docker. Check shared_buffers and effective_cache_size. Default PostgreSQL config assumes it runs on bare metal with full RAM access. In Docker, you need to set these explicitly based on the container's memory limit.

What to Do Next

• Add Grafana + pg_stat_statements dashboard to visualize query performance
• Set up WAL archiving for point-in-time recovery
• Use pg_basebackup for full physical backups (faster restore than pg_dump for large databases)
• Consider pgvector if you need vector search for AI/ML workloads

References

• PostgreSQL Docker Image Documentation
• PostgreSQL: Documentation: Tuning Your PostgreSQL Server
• PgBouncer Documentation