Running PostgreSQL in Docker: A Complete Guide for Developers

PostgreSQL 16 is here, and running it in Docker makes life much easier. No more compatibility issues between different machines or operating systems. Let's dive into setting it up properly.

Understanding PostgreSQL Versions

PostgreSQL 16 (released September 2023) brings significant improvements:

• Better performance for parallel queries
• Improved logical replication
• Enhanced monitoring capabilities

When choosing a version tag for Docker:

• postgres:16 - Latest PostgreSQL 16.x
• postgres:16.1 - Specific version
• postgres:16-alpine - Smaller image size, Alpine-based

Basic Docker Compose Setup

Here's a basic setup to get PostgreSQL running:

version: '3.8'
services:
  postgres:
    image: postgres:16
    container_name: my_postgres
    environment:
      POSTGRES_USER: admin
      POSTGRES_PASSWORD: your_password
      POSTGRES_DB: mydb
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U admin -d mydb"]
      interval: 10s
      timeout: 5s
      retries: 5
    restart: unless-stopped

volumes:
  postgres_data:

Let's break down each part:

Environment Variables

PostgreSQL container recognizes several environment variables:

environment:
  POSTGRES_USER: admin          # Database superuser
  POSTGRES_PASSWORD: password   # Superuser password
  POSTGRES_DB: mydb            # Default database
  # Optional variables
  POSTGRES_INITDB_ARGS: "--data-checksums"
  POSTGRES_HOST_AUTH_METHOD: "scram-sha-256"

Volume Management

Data persistence is crucial. There are two approaches:

1. Named volumes (recommended):

volumes:
  - postgres_data:/var/lib/postgresql/data

2. Bind mounts (for development):

volumes:
  - ./data:/var/lib/postgresql/data

Named volumes are preferred because Docker manages them, ensuring proper permissions.

Container Health Checks

Health checks verify database availability:

healthcheck:
  test: ["CMD-SHELL", "pg_isready -U admin -d mydb"]
  interval: 10s    # How often to check
  timeout: 5s      # When to consider check failed
  retries: 5       # How many retries before unhealthy

Running Multiple PostgreSQL Versions

Sometimes you need different versions running simultaneously:

services:
  postgres16:
    image: postgres:16
    ports:
      - "5432:5432"
    # ... other config

  postgres15:
    image: postgres:15
    ports:
      - "5433:5432"
    # ... other config

Custom Configuration

Override PostgreSQL settings using a custom config file:

1. Create postgresql.conf:

max_connections = 100
shared_buffers = 128MB
effective_cache_size = 512MB
maintenance_work_mem = 128MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 8MB
min_wal_size = 1GB
max_wal_size = 4GB

2. Mount it in docker-compose:

services:
  postgres:
    volumes:
      - ./postgresql.conf:/etc/postgresql/postgresql.conf
    command: postgres -c 'config_file=/etc/postgresql/postgresql.conf'

Connecting to Your Container

From another container:

services:
  app:
    environment:
      DATABASE_URL: "postgresql://admin:password@postgres:5432/mydb"

From your machine:

psql -h localhost -p 5432 -U admin -d mydb

Backup and Restore

Create backup script backup.sh:

#!/bin/bash
docker exec my_postgres pg_dump -U admin mydb > backup.sql

Restore script restore.sh:

#!/bin/bash
docker exec -i my_postgres psql -U admin mydb < backup.sql

Advanced Topics

SSL Support

Enable SSL by mounting certificates:

services:
  postgres:
    volumes:
      - ./server.key:/var/lib/postgresql/server.key
      - ./server.crt:/var/lib/postgresql/server.crt
    command: -c ssl=on -c ssl_cert_file=/var/lib/postgresql/server.crt -c ssl_key_file=/var/lib/postgresql/server.key

Replication Setup

For primary-replica setup:

services:
  primary:
    environment:
      POSTGRES_REPLICA_MODE: primary
      
  replica:
    environment:
      POSTGRES_REPLICA_MODE: replica
      POSTGRES_MASTER_HOST: primary

Ready to start using PostgreSQL with Docker? Run:

docker-compose up -d

Remember to replace default passwords in production environments and follow security best practices.