Saiful Islam

Posted on Nov 29

Setting Up PostgreSQL with Docker Compose for Development and Production

#docker #postgres

Working with PostgreSQL becomes much easier when you run it inside Docker. It gives you a clean environment, consistent versions, and a quick setup for both development and production. In this guide, we will walk step by step through a practical setup using Docker Compose.

This setup includes:

A custom PostgreSQL configuration
Automatic initialisation scripts
Separate Docker Compose files for development and production
A clear folder structure
Easy commands for starting and stopping the containers

Let us begin by reviewing the folder layout.

1. Folder Structure

Here is the structure used in this guide:

infra/
├─ docker/
│  ├─ docker-compose-dev.yml
│  └─ docker-compose.yml
├─ postgres/
│  ├─ init-scripts/
│  │  └─ init.sql
│  └─ postgres.conf
├─ package.json

Each file has its own purpose:

**postgres.conf* → Custom PostgreSQL configuration
init.sql → Creates roles, permissions, and databases
docker-compose-dev.yml → Development compose file
docker-compose.yml → Production compose file
package.json scripts → Simple commands for starting and stopping services

2. Custom PostgreSQL Configuration

The file postgres.conf defines PostgreSQL tuning parameters.
Some of the key settings include:

Connection limits
Memory tuning
WAL optimisation
Logging options
Security settings

An example:

# Connection settings
listen_addresses = '*'
port = 5432
max_connections = 200

# Memory settings
shared_buffers = 512MB
effective_cache_size = 1GB
work_mem = 4MB
maintenance_work_mem = 128MB

# WAL settings
wal_buffers = 16MB
checkpoint_completion_target = 0.9
wal_compression = on
min_wal_size = 512MB
max_wal_size = 2GB

# Logging
log_destination = 'stderr'
logging_collector = off
log_min_messages = warning
log_min_error_statement = error
log_min_duration_statement = 1000

# Security
ssl = off
password_encryption = scram-sha-256

# Performance
random_page_cost = 1.1
effective_io_concurrency = 200

Keeping this file external makes it easy to adjust performance for development and production without modifying container images.

3. Database Initialisation with init.sql

The init.sql script runs automatically the first time the container starts.
It performs tasks such as:

Creating roles (db_migrator, db_rw, db_ro)
Creating the test database
Setting permissions for each role
Configuring default privileges
Enforcing secure database access

An example:

-- ---------- 1. Create roles ----------
DO $$
BEGIN
    IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'db_migrator') THEN
        CREATE ROLE db_migrator LOGIN PASSWORD '12345678';
    END IF;

    IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'db_rw') THEN
        CREATE ROLE db_rw LOGIN PASSWORD '12345678';
    END IF;

    IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = 'db_ro') THEN
        CREATE ROLE db_ro LOGIN PASSWORD '12345678';
    END IF;
END
$$;

-- ---------- 2. Create databases ----------
CREATE DATABASE test;

-- ============================================================
-- Apply permissions for each database
-- ============================================================

------------------------------------------------------------
-- Setup for project_db
------------------------------------------------------------
\connect project_db

-- Remove unsafe default privileges
REVOKE ALL ON DATABASE project_db FROM PUBLIC;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- Prisma requires the db_migrator to own the schema
ALTER SCHEMA public OWNER TO db_migrator;

-- db_Migrator full control
GRANT USAGE, CREATE ON SCHEMA public TO db_migrator;

-- Read/Write application user
GRANT USAGE ON SCHEMA public TO db_rw;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO db_rw;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO db_rw;

-- Readonly user
GRANT USAGE ON SCHEMA public TO db_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO db_ro;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO db_ro;

-- Default privileges for FUTURE objects created by db_migrator
ALTER DEFAULT PRIVILEGES FOR ROLE db_migrator IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO db_rw;

ALTER DEFAULT PRIVILEGES FOR ROLE db_migrator IN SCHEMA public
    GRANT SELECT ON TABLES TO db_ro;

ALTER DEFAULT PRIVILEGES FOR ROLE db_migrator IN SCHEMA public
    GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO db_rw;

ALTER DEFAULT PRIVILEGES FOR ROLE db_migrator IN SCHEMA public
    GRANT USAGE, SELECT ON SEQUENCES TO db_ro;

-- Allow connections
GRANT CONNECT ON DATABASE project_db TO db_migrator, db_rw, db_ro;

-------------------------------
-- Setup for test database
-------------------------------
\connect test

REVOKE ALL ON DATABASE test FROM PUBLIC;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

ALTER SCHEMA public OWNER TO db_migrator;
GRANT USAGE, CREATE ON SCHEMA public TO db_migrator;

GRANT USAGE ON SCHEMA public TO db_rw, db_ro;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO db_rw;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO db_rw;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO db_ro;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO db_ro;

ALTER DEFAULT PRIVILEGES FOR ROLE db_migrator IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO db_rw;

ALTER DEFAULT PRIVILEGES FOR ROLE db_migrator IN SCHEMA public
    GRANT SELECT ON TABLES TO db_ro;

ALTER DEFAULT PRIVILEGES FOR ROLE db_migrator IN SCHEMA public
    GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO db_rw;

ALTER DEFAULT PRIVILEGES FOR ROLE db_migrator IN SCHEMA public
    GRANT USAGE, SELECT ON SEQUENCES TO db_ro;

GRANT CONNECT ON DATABASE test TO db_migrator, db_rw, db_ro;

This ensures a predictable PostgreSQL environment and follows best practices for separating permissions.

Docker automatically executes any .sql file placed inside:

/docker-entrypoint-initdb.d

This is why we mount that directory inside the container.

4. Development Compose File

The development file is simple and focuses on flexibility.
Key features include:

PGAdmin for database management
Exposed ports for local access
Mounted configuration and initialisation scripts
Health checks
Volumes for persistent storage

Example:

services:
  postgres:
    image: postgres:16.11-alpine3.22
    container_name: postgres
    restart: unless-stopped
    ports:
      - "5432:5432"
    env_file:
      - ../../.env
      - ../../.env.development.local
    volumes:
      - pg_volume:/var/lib/postgresql/data:rw
      - ../postgres/postgres.conf:/etc/postgresql/postgresql.conf:ro
      - ../postgres/init-scripts:/docker-entrypoint-initdb.d:ro
    command: [ "postgres", "-c", "config_file=/etc/postgresql/postgresql.conf", "-c", "log_statement=none", "-c", "log_min_duration_statement=1000", "-c", "log_connections=on", "-c", "log_disconnections=on" ]
    user: "70:70" # Postgres user
    healthcheck:
      test: [ "CMD-SHELL", 'pg_isready -U "$${POSTGRES_USER:-postgres}" -d "$${POSTGRES_DB:-postgres}" -h localhost || exit 1' ]
      interval: 30s
      timeout: 10s
      retries: 5
      start_period: 60s
    stop_grace_period: 120s
    networks:
      - project_network

  pgadmin:
    image: elestio/pgadmin:latest
    container_name: pgadmin
    restart: unless-stopped
    ports:
      - "5050:8080"
    env_file:
      - ../../.env
      - ../../.env.development.local
    volumes:
      - pgadmin_volume:/var/lib/pgadmin:rw
    depends_on:
      - postgres
    networks:
      - project_network

networks:
  project_network:
    driver: bridge

volumes:
  pg_volume:
    driver: local
  pgadmin_volume:
    driver: local

Development environments usually need visibility, so logging and access remain more open.

5. Production Compose File

Production requires a stricter setup.
This file includes:

Resource limits
Security enhancements
Custom network settings
Logging configuration
No PGAdmin
No unnecessary services

Example:

services:
  postgres:
    image: postgres:16.11-alpine3.22
    container_name: postgres
    restart: unless-stopped
    ports:
      - "5432:5432"
    env_file:
      - ../../.env
      - ../../.env.production.local
    volumes:
      - pg_volume:/var/lib/postgresql/data:rw
      - ../postgres/postgres.conf:/etc/postgresql/postgresql.conf:ro
      - ../postgres/init-scripts:/docker-entrypoint-initdb.d:ro
    command: [ "postgres", "-c", "config_file=/etc/postgresql/postgresql.conf", "-c", "log_statement=none", "-c", "log_min_duration_statement=1000", "-c", "log_connections=on", "-c", "log_disconnections=on" ]
    user: "70:70" # Postgres user
    healthcheck:
      test: [ "CMD-SHELL", 'pg_isready -U "$${POSTGRES_USER:-postgres}" -d "$${POSTGRES_DB:-postgres}" -h localhost || exit 1' ]
      interval: 30s
      timeout: 10s
      retries: 5
      start_period: 60s
    stop_grace_period: 120s
    networks:
      - project_network
    security_opt:
      - no-new-privileges:true
    shm_size: "512mb"
    logging:
      driver: "json-file"
      options:
        max-size: "100m"
        max-file: "5"
    deploy:
      resources:
        limits:
          cpus: "1.5"
          memory: 1G
        reservations:
          cpus: "0.5"
          memory: 512M
    labels:
      - "com.docker.compose.service=postgres"
      - "maintainer=your.dev.mail@mail.com"

networks:
  project_network:
    driver: bridge
    driver_opts:
      com.docker.network.bridge.name: ma_bridge
    ipam:
      config:
        - subnet: 172.20.0.0/16
    labels:
      - "com.docker.compose.network=project_name"

volumes:
  pg_volume:
    driver: local
    labels:
      - "com.docker.compose.volume=pg_volume"

Production setups focus on stability, performance, and security.

6. Running PostgreSQL with NPM Scripts

To simplify the workflow, the package.json includes convenient commands:

"scripts": {
  "docker-dev:up": "docker compose -f ./infra/docker/docker-compose-dev.yml -p project_name up -d",
  "docker-dev:down": "docker compose -f ./infra/docker/docker-compose-dev.yml -p project_name down",
  "docker:up": "docker compose -f ./infra/docker/docker-compose.yml -p project_name up -d",
  "docker:down": "docker compose -f ./infra/docker/docker-compose.yml -p project_name down"
}

Now starting PostgreSQL is as simple as:

npm run docker-dev:up

And stopping:

npm run docker-dev:down

The same applies to production.

7. Conclusion

Using separate Docker Compose files for development and production provides a cleaner and safer workflow. Your development environment becomes flexible, while the production environment stays stable and optimised.

This approach also ensures:

Reproducible environments
Clear separation of roles and permissions
Easier onboarding for other developers
Stronger security controls
Scalable and maintainable infrastructure

You can now use PostgreSQL with confidence in any setup.

DEV Community