Docker Compose: PostgreSQL + pgAdmin Reference

TL;DR

• Bottom line: A production-ready PostgreSQL + pgAdmin stack requires named volumes for data persistence, health checks with pg_isready, Docker secrets for passwords, and init scripts in /docker-entrypoint-initdb.d/ for schema bootstrapping.
• Key tool/command: docker compose up -d with a docker-compose.yml defining postgres and pgadmin services on a shared network.
• Watch out for: Init scripts only run on first startup with an empty data directory -- if the volume already has data, scripts are silently skipped.
• Works with: PostgreSQL 15-18, pgAdmin 4 v8.x-9.x, Docker Compose V2 (plugin) and V1 (standalone). Linux, macOS, Windows (WSL2).

Constraints

• NEVER store POSTGRES_PASSWORD in plain text in docker-compose.yml for production -- use Docker secrets or _FILE suffix
• Init scripts in /docker-entrypoint-initdb.d only run on first startup with an EMPTY data directory
• pgAdmin container runs as UID 5050 / GID 5050 -- mounted volumes must be readable by this user
• POSTGRES_PASSWORD is the ONLY required environment variable for the postgres image
• PostgreSQL 18+ uses version-specific PGDATA path /var/lib/postgresql/18/docker -- do NOT hardcode /var/lib/postgresql/data for pg18+

Quick Reference

Service Configuration Summary

PostgreSQL Environment Variables

pgAdmin Environment Variables

Decision Tree

START: What environment are you deploying to?
├── Development (local)?
│   ├── YES → Use basic docker-compose.yml with env vars, no secrets needed
│   │         Map ports 5432 + 5050 to localhost
│   └── NO ↓
├── CI/Testing?
│   ├── YES → Use tmpfs for data (no persistence), health checks for readiness
│   │         Skip pgAdmin (use psql CLI instead)
│   └── NO ↓
├── Production / Staging?
│   ├── YES → Use Docker secrets for passwords (_FILE suffix)
│   │         Named volumes with backup strategy
│   │         Add health checks + restart policies
│   │         Restrict pgAdmin to internal network or disable
│   └── NO ↓
└── DEFAULT → Start with development config, then harden

Step-by-Step Guide

1. Create the project directory structure

Create a directory with the Compose file and optional init scripts. [src3]

mkdir -p postgres-pgadmin/{init-scripts,pgadmin-servers}
cd postgres-pgadmin

Verify: ls → should show init-scripts/ and pgadmin-servers/ directories.

2. Write the docker-compose.yml

Create the core Compose file defining both services with health checks, named volumes, and a shared network. [src1] [src2]

Full script: docker-compose.yml (47 lines)

# docker-compose.yml -- PostgreSQL 17 + pgAdmin 4
services:
  postgres:
    image: postgres:17
    container_name: pg-db
    restart: unless-stopped
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: changeme
      POSTGRES_DB: appdb
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres -d appdb"]
      interval: 10s
      timeout: 5s
      retries: 5

Verify: docker compose config → should parse without errors.

3. Create an init SQL script

Scripts placed in /docker-entrypoint-initdb.d/ run alphabetically on first startup only. Supports .sql, .sql.gz, .sql.xz, and .sh files. [src7]

Full script: 01-init.sql (25 lines)

-- 01-init.sql: Create application schema and default user
CREATE SCHEMA IF NOT EXISTS app;
CREATE USER app_user WITH PASSWORD 'app_password';
GRANT USAGE ON SCHEMA app TO app_user;
GRANT CREATE ON SCHEMA app TO app_user;

Verify: docker compose exec postgres psql -U postgres -d appdb -c '\dt app.*' → should list the tables.

4. Configure pgAdmin auto-connection

Create a servers.json file so pgAdmin automatically registers the PostgreSQL server on first launch. [src2]

{
  "Servers": {
    "1": {
      "Name": "Local PostgreSQL",
      "Group": "Docker",
      "Host": "postgres",
      "Port": 5432,
      "MaintenanceDB": "postgres",
      "Username": "postgres",
      "PassFile": "/pgadmin4/pgpass",
      "SSLMode": "prefer"
    }
  }
}

Verify: Open http://localhost:5050 → pgAdmin should show "Local PostgreSQL" under the "Docker" server group.

5. Start the stack

Launch all services and verify they are healthy. [src3]

# Start in detached mode
docker compose up -d

# Wait for health checks to pass
docker compose ps

# Check logs
docker compose logs postgres
docker compose logs pgadmin

Verify: docker compose ps → both services should show healthy status.

6. Set up backups

Create a backup script that runs pg_dump inside the running container. [src5]

#!/bin/bash
# backup.sh -- Dump PostgreSQL to compressed SQL
CONTAINER="pg-db"
docker exec "$CONTAINER" pg_dump -U postgres -d appdb \
  --no-owner --clean --if-exists | gzip -9 \
  > "backups/appdb_$(date +%Y%m%d_%H%M%S).sql.gz"

Verify: ls -la backups/ → should show the timestamped .sql.gz file.

Code Examples

Docker Compose: Production with Docker Secrets

Full script: docker-compose-production.yml (53 lines)

# Uses Docker secrets -- no plain text passwords
# Requires: echo "password" > ./secrets/postgres_password
services:
  postgres:
    image: postgres:17
    environment:
      POSTGRES_PASSWORD_FILE: /run/secrets/pg_password
    secrets:
      - pg_password
    ports:
      - "127.0.0.1:5432:5432"
secrets:
  pg_password:
    file: ./secrets/postgres_password

Docker Compose: CI/Testing (ephemeral)

# docker-compose.test.yml -- Ephemeral PostgreSQL for CI
services:
  postgres:
    image: postgres:17-alpine
    environment:
      POSTGRES_PASSWORD: test_password
      POSTGRES_DB: test_db
    tmpfs:
      - /var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: 2s
      timeout: 5s
      retries: 10
    ports:
      - "5432:5432"

Bash: Restore from Backup

#!/bin/bash
# restore.sh -- Restore PostgreSQL from compressed SQL dump
BACKUP_FILE="$1"
[ -z "$BACKUP_FILE" ] && { echo "Usage: ./restore.sh <file.sql.gz>"; exit 1; }
gunzip -c "$BACKUP_FILE" | docker exec -i pg-db psql -U postgres -d appdb
echo "Restore complete from: $BACKUP_FILE"

Python: Wait for PostgreSQL Readiness

import psycopg2  # psycopg2-binary==2.9.x
import time, os

def wait_for_postgres(max_retries=30, delay=2):
    dsn = os.environ.get("DATABASE_URL",
        "postgresql://postgres:changeme@localhost:5432/appdb")
    for attempt in range(max_retries):
        try:
            conn = psycopg2.connect(dsn)
            conn.close()
            return True
        except psycopg2.OperationalError:
            time.sleep(delay)
    raise TimeoutError("PostgreSQL not ready")

Anti-Patterns

Wrong: Hardcoding passwords in docker-compose.yml

# BAD -- passwords visible in version control and docker inspect
services:
  postgres:
    image: postgres:17
    environment:
      POSTGRES_PASSWORD: my_secret_password  # Exposed in git history!

Correct: Using Docker secrets or .env file

# GOOD -- passwords loaded from external file, not committed
services:
  postgres:
    image: postgres:17
    environment:
      POSTGRES_PASSWORD_FILE: /run/secrets/pg_password
    secrets:
      - pg_password
secrets:
  pg_password:
    file: ./secrets/postgres_password  # Add to .gitignore

Wrong: Using depends_on without health check condition

# BAD -- depends_on alone only waits for container to START, not be READY
services:
  app:
    depends_on:
      - postgres  # PostgreSQL may not be accepting connections yet!

Correct: Using depends_on with service_healthy condition

# GOOD -- waits for PostgreSQL to pass health check
services:
  app:
    depends_on:
      postgres:
        condition: service_healthy
  postgres:
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: 5s
      timeout: 5s
      retries: 5

Wrong: Using bind mounts for database data

# BAD -- bind mounts cause permission issues on macOS/Windows
services:
  postgres:
    volumes:
      - ./pgdata:/var/lib/postgresql/data  # Permission errors likely

Correct: Using named volumes for database data

# GOOD -- named volumes managed by Docker, no permission issues
services:
  postgres:
    volumes:
      - pgdata:/var/lib/postgresql/data
volumes:
  pgdata:
    driver: local

Wrong: Exposing PostgreSQL port to all interfaces in production

# BAD -- PostgreSQL accessible from any network interface
ports:
  - "5432:5432"  # Binds to 0.0.0.0 -- exposed to LAN/internet

Correct: Binding to localhost or using internal network only

# GOOD -- PostgreSQL only accessible from localhost
ports:
  - "127.0.0.1:5432:5432"
# OR: Don't expose ports -- let containers connect via Docker network

Common Pitfalls

• Init scripts not running: Scripts in /docker-entrypoint-initdb.d/ only execute when the data volume is empty. If you change a script, you must remove the volume first: docker compose down -v && docker compose up -d. [src1]
• pgAdmin permission denied on mounted volume: pgAdmin runs as UID 5050. Fix: sudo chown -R 5050:5050 ./pgadmin-data/ or use a named volume. [src2]
• PostgreSQL not ready when app starts: depends_on without condition: service_healthy only waits for container start, not readiness. Fix: Add a healthcheck with pg_isready and use condition: service_healthy. [src3]
• Data loss on docker compose down -v: The -v flag removes named volumes. Fix: Never use -v unless you intend to destroy all data. [src4]
• PGDATA path changed in PostgreSQL 18: PostgreSQL 18+ uses /var/lib/postgresql/18/docker instead of /var/lib/postgresql/data. Fix: Update volume mount path when upgrading. [src1]
• pgAdmin servers.json ignored on restart: Server definitions only load on first database creation. Fix: Set PGADMIN_REPLACE_SERVERS_ON_STARTUP=True. [src2]
• Connection refused from pgAdmin to PostgreSQL: pgAdmin uses the Docker service name as hostname. Fix: Use postgres (service name) as host, not localhost. [src3]
• Large Docker image size: postgres:17 is ~400MB. Fix: Use postgres:17-alpine (~80MB) for development/CI. Note: Alpine uses musl libc. [src6]

Diagnostic Commands

# Check if PostgreSQL is accepting connections
docker compose exec postgres pg_isready -U postgres

# Connect to PostgreSQL via psql
docker compose exec postgres psql -U postgres -d appdb

# List all databases
docker compose exec postgres psql -U postgres -c '\l'

# List tables in a database
docker compose exec postgres psql -U postgres -d appdb -c '\dt'

# Check PostgreSQL logs
docker compose logs --tail 50 postgres

# Check pgAdmin logs
docker compose logs --tail 50 pgadmin

# Inspect container resource usage
docker stats pg-db pg-admin

# Check volume disk usage
docker system df -v | grep pgdata

# Verify health check status
docker inspect --format='{{json .State.Health}}' pg-db

# Check PostgreSQL config settings
docker compose exec postgres psql -U postgres \
  -c 'SHOW ALL' | grep -i "max_connections\|shared_buffers\|work_mem"

Version History & Compatibility

When to Use / When Not to Use

Important Caveats

• The postgres:latest tag may jump major versions unexpectedly -- always pin a specific major version (e.g., postgres:17) in production
• Docker Desktop for macOS/Windows uses a VM, so volume performance is significantly slower than native Linux -- consider :delegated mount options for development
• pgAdmin stores its configuration in an internal SQLite database at /var/lib/pgadmin -- losing this volume means re-entering all saved queries and preferences
• The POSTGRES_HOST_AUTH_METHOD=trust setting disables ALL password authentication and should NEVER be used outside of throwaway test containers
• When upgrading PostgreSQL major versions, you must pg_dump and restore -- in-place upgrades of the data directory between major versions are not supported via Docker