Docker Compose: run PostgreSQL with persistent storage

Setting up a robust PostgreSQL instance for development, testing, or even light production environments often involves more than just pulling an image.…

Setting up a robust PostgreSQL instance for development, testing, or even light production environments often involves more than just pulling an image. Containerization with Docker Compose streamlines this process significantly, providing declaritive infrastructure as code. This article details the construction of a production-ready PostgreSQL setup using Docker Compose, focusing on persistent data storage, secure configuration, and essential health checks.

We'll explore the critical components for a reliable PostgreSQL service, including volume management, environment variables for initial setup, port exposure, and a robust health check mechanism to ensure database availability before dependent services attempt connections. The examples provided target PostgreSQL version 16, running on an Alpine Linux base image for reduced footprint.

Basic PostgreSQL Service Definition

The core of our setup begins with defining the PostgreSQL service within docker-compose.yml. This foundational block specifies the image, initial credentials, and database name.

version: '3.8'
services:
  db:
    image: postgres:16-alpine
    container_name: myapp_postgres
    environment:
      POSTGRES_PASSWORD: ${DB_PASSWORD}
      POSTGRES_USER: ${DB_USER}
      POSTGRES_DB: ${DB_NAME}
    ports:
      - "5432:5432"
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U ${DB_USER} -d ${DB_NAME}"]
      interval: 5s
      timeout: 5s
      retries: 5
      start_period: 10s
    volumes:
      - pgdata:/var/lib/postgresql/data
      # - ./initdb.d:/docker-entrypoint-initdb.d # Optional: for custom initialization scripts
volumes:
  pgdata:
    driver: local

Image Selection

postgres:16-alpine is chosen for its balance of features and minimal size. Alpine-based images are typically smaller, leading to faster downloads and reduced attack surface. For specific use cases, other tags like postgres:16 (Debian-based) or specific patch versions (e.g., postgres:16.2-alpine) might be preferred.

Environment Variables

Initial database configuration is primarily handled via environment variables passed to the PostgreSQL container. It's crucial to use Docker Compose's variable interpolation (e.g., ${DB_PASSWORD}) and define these sensitive values in a .env file or inject them via your CI/CD system. Storing secrets directly in docker-compose.yml is not recommended for production.

• POSTGRES_PASSWORD: The password for the POSTGRES_USER (or postgres user if POSTGRES_USER is not set). Mandatory for non-development environments.
• POSTGRES_USER: The default user created. If not set, defaults to postgres.
• POSTGRES_DB: The default database created. If not set, defaults to the POSTGRES_USER name.
• PGDATA: (Optional) Specifies the subdirectory to create the database cluster. Defaults to /var/lib/postgresql/data. Overriding this is generally not recommended unless you have specific needs.

Example .env file:

DB_PASSWORD=your_strong_password_here
DB_USER=appuser
DB_NAME=myapp_db

Port Mapping

The ports: ["5432:5432"] line maps port 5432 on the host machine to port 5432 inside the container. This allows external applications (e.g., database clients, ORMs on your host) to connect to the PostgreSQL instance. For increased security or to avoid port conflicts, you can map it to a different host port (e.g., "5433:5432").

Persistent Data Storage with Docker Volumes

The most critical aspect of running a stateful service like PostgreSQL in Docker is ensuring data persistence. Without it, all your data will be lost when the container is removed or recreated. Docker volumes provide this capability.

Named Volumes vs. Bind Mounts

For PostgreSQL data, named volumes are the recommended approach over bind mounts.

The pgdata:/var/lib/postgresql/data line in our docker-compose.yml does two things:

1. It maps a named volume called pgdata to the /var/lib/postgresql/data directory inside the container, which is PostgreSQL's default data directory (specified by the PGDATA environment variable).
2. The volumes: pgdata: driver: local block at the bottom defines this named volume. The driver: local is the default and explicitly states that Docker should manage this volume on the local filesystem.

To confirm the volume creation and its location on the host:

docker volume ls
# Output similar to:
# DRIVER    VOLUME NAME
# local     myapp_pgdata

docker volume inspect myapp_pgdata
# Look for "Mountpoint" in the JSON output, e.g., /var/lib/docker/volumes/myapp_pgdata/_data

Health Checks for Robustness

A health check is vital for ensuring that the database container is not just running, but actually ready to accept connections. This prevents dependent application containers from trying to connect prematurely, leading to connection refused errors.

healthcheck:
  test: ["CMD-SHELL", "pg_isready -U ${DB_USER} -d ${DB_NAME}"]
  interval: 5s
  timeout: 5s
  retries: 5
  start_period: 10s

• test: ["CMD-SHELL", "pg_isready -U ${DB_USER} -d ${DB_NAME}"]: This command checks the availability of the PostgreSQL server. pg_isready is a utility provided by PostgreSQL that checks the connection status. It will return 0 for a healthy server, 1 if the server is rejecting connections (e.g., still starting up), and 2 for no response.
• interval: 5s: Docker will run this health check every 5 seconds.
• timeout: 5s: If the command takes longer than 5 seconds to execute, the check is considered to have failed.
• retries: 5: The container is considered unhealthy after 5 consecutive failed health checks.
• start_period: 10s: Allows an initial grace period of 10 seconds for the container to start up and the database to initialize before health checks begin counting failures. This is particularly useful for databases that might take a little longer to become fully operational.

You can monitor the health status of your container using docker ps (it will show (healthy) or (unhealthy) in the status column) or docker inspect <container_id> and looking under the "Health" section.

Connecting from Another Service

For application services within the same docker-compose.yml file, connecting to the PostgreSQL container is straightforward using the service name as the hostname.

version: '3.8'
services:
  db:
    image: postgres:16-alpine
    container_name: myapp_postgres
    environment:
      POSTGRES_PASSWORD: ${DB_PASSWORD}
      POSTGRES_USER: ${DB_USER}
      POSTGRES_DB: ${DB_NAME}
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U ${DB_USER} -d ${DB_NAME}"]
      interval: 5s
      timeout: 5s
      retries: 5
      start_period: 10s
    volumes:
      - pgdata:/var/lib/postgresql/data

  app:
    build: .
    container_name: myapp_web
    environment:
      DATABASE_URL: postgres://${DB_USER}:${DB_PASSWORD}@db:5432/${DB_NAME}
    ports:
      - "8000:8000"
    depends_on:
      db:
        condition: service_healthy # Ensure 'db' is healthy before starting 'app'
    # Optional: networks can be used for more explicit network configuration
    # networks:
    #   - myapp_net

volumes:
  pgdata:
    driver: local

# networks:
#   myapp_net:
#     driver: bridge

Notice the depends_on: db: condition: service_healthy in the app service definition. This tells Docker Compose to wait until the db service reports a healthy status before starting the app service. This is critical for preventing startup race conditions.

Custom Initialization Scripts (Optional)

The official PostgreSQL Docker image supports executing SQL scripts or shell scripts placed in /docker-entrypoint-initdb.d/. These scripts are run only when the container is first created and the PGDATA directory is empty (i.e., on initial database creation). This is useful for:

• Creating additional users or roles.
• Creating schemas or tables.
• Populating initial data.

To use this feature, create a directory (e.g., ./initdb.d) on your host with your .sql or .sh scripts and bind-mount it:

volumes:
  - pgdata:/var/lib/postgresql/data
  - ./initdb.d:/docker-entrypoint-initdb.d

Any .sql files will be executed by psql, and .sh files will be executed by bash.

Example ./initdb.d/01_create_tables.sql:

CREATE TABLE IF NOT EXISTS products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

INSERT INTO products (name, price) VALUES ('Laptop', 1200.00), ('Keyboard', 75.00) ON CONFLICT DO NOTHING;

The files are executed in alphabetical order. Using numerical prefixes (e.g., 01_, 02_) is a good practice to control execution order.

Common Pitfalls

• Forgetting persistent storage: Running without a volume mapping means all your data is ephemeral and will be lost on container removal (docker-compose down or docker rm).
• Hardcoding passwords: Storing sensitive information like POSTGRES_PASSWORD directly in docker-compose.yml is a security risk. Always use environment variables from a .env file or a secrets management system.
• No health check: Without a health check, dependent services might try to connect to the database before it's fully ready, leading to startup failures or retries. depends_on alone only ensures container startup order, not service readiness.
• Volume permission issues (less common with named volumes): If using bind mounts, ensure the PostgreSQL user inside the container (typically UID 999 for Alpine, 70 for Debian) has appropriate read/write permissions on the host directory. Named volumes generally abstract this away.
• Conflicting port mappings: If another process on your host is already using port 5432, your PostgreSQL container will fail to start. Either stop the conflicting process or map PostgreSQL to a different host port (e.g., 5433:5432).
• Upgrading PostgreSQL versions: Simply changing the image tag (e.g., from postgres:15-alpine to postgres:16-alpine) and restarting the container is generally NOT safe for major version upgrades. PostgreSQL major versions often have incompatible data formats. A proper upgrade strategy involves creating a new container with the new version, migrating data, and then switching.