--- # === IDENTITY === id: software/devops/docker-postgresql-pgadmin/2026 canonical_question: "Docker Compose reference: PostgreSQL + pgAdmin" aliases: - "docker compose postgres pgadmin setup" - "postgresql pgadmin docker-compose.yml" - "run postgres and pgadmin in docker" - "docker postgres pgadmin development environment" - "pgadmin docker compose configuration" - "containerized postgresql with pgadmin" - "docker compose database admin gui" - "postgresql docker dev stack" entity_type: software_reference domain: software > devops > Docker PostgreSQL + pgAdmin region: global jurisdiction: global temporal_scope: 2024-2026 # === VERIFICATION === last_verified: 2026-02-27 confidence: 0.94 version: 1.0 first_published: 2026-02-27 # === TEMPORAL VALIDITY === temporal_validity: status: stable last_breaking_change: "PostgreSQL 18 changed PGDATA to /var/lib/postgresql/18/docker (version-specific path, 2025); pgAdmin 4 migrated to React 19 and python:3-alpine base (2025)" next_review: 2026-08-26 change_sensitivity: medium # === CONSTRAINTS === 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+" # === SKIP CONDITIONS === skip_this_unit_if: - condition: "Need PostgreSQL on Kubernetes, not Docker Compose" use_instead: "software/devops/k8s-statefulset-databases/2026" - condition: "Need full ELK/monitoring stack with PostgreSQL" use_instead: "software/devops/docker-prometheus-grafana/2026" - condition: "Need MongoDB instead of PostgreSQL" use_instead: "software/devops/docker-mongodb-replicaset/2026" # === AGENT HINTS === inputs_needed: - key: "environment" question: "Is this for development, testing, or production?" type: choice options: ["development", "testing", "production"] - key: "pg_version" question: "Which PostgreSQL version do you need?" type: choice options: ["17 (current stable)", "16 (LTS)", "15", "18 (latest)"] # === DISTRIBUTION === canonical_source: "https://knowledgelib.io/software/devops/docker-postgresql-pgadmin/2026" suggested_citation: "Source: knowledgelib.io -- AI Knowledge Library (verified 2026-02-27)" # === RELATED UNITS === related_kos: depends_on: [] related_to: - id: "software/devops/docker-elk-stack/2026" label: "Docker Compose: ELK Stack" - id: "software/security/container-security/2026" label: "Container Security Checklist (Docker)" - id: "software/security/secrets-management/2026" label: "Secrets Management Best Practices" solves: [] alternative_to: - id: "software/devops/docker-supabase/2026" label: "Docker Compose: Supabase Self-Hosted" often_confused_with: - id: "software/devops/docker-mongodb-replicaset/2026" label: "Docker Compose: MongoDB Replica Set" # === SOURCES (7 authoritative sources) === # Types: official_docs, technical_blog, rfc_spec, academic_paper, community_resource, industry_report # Reliability: high, moderate_high, moderate, moderate_low, low, authoritative sources: - id: src1 title: "postgres - Official Image" author: Docker Library url: https://hub.docker.com/_/postgres type: official_docs published: 2025-12-01 reliability: authoritative - id: src2 title: "Container Deployment -- pgAdmin 4 Documentation" author: pgAdmin Development Team url: https://www.pgadmin.org/docs/pgadmin4/latest/container_deployment.html type: official_docs published: 2025-10-01 reliability: authoritative - id: src3 title: "Visualizing your PostgreSQL databases with pgAdmin" author: Docker Docs url: https://docs.docker.com/guides/pgadmin/ type: official_docs published: 2025-06-01 reliability: high - id: src4 title: "How to Use the Postgres Docker Official Image" author: Docker Blog url: https://www.docker.com/blog/how-to-use-the-postgres-docker-official-image/ type: technical_blog published: 2025-03-01 reliability: high - id: src5 title: "Docker Postgres Backup/Restore Guide (with examples)" author: SimpleBackups url: https://simplebackups.com/blog/docker-postgres-backup-restore-guide-with-examples type: technical_blog published: 2025-01-15 reliability: moderate_high - id: src6 title: "Best Practices for Running PostgreSQL in Docker" author: Sliplane url: https://sliplane.io/blog/best-practices-for-postgres-in-docker type: technical_blog published: 2025-04-01 reliability: moderate_high - id: src7 title: "Pre-seeding database" author: Docker Docs url: https://docs.docker.com/guides/pre-seeding/ type: official_docs published: 2025-06-01 reliability: high --- # 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 | Image | Ports | Volumes | Key Env | |---|---|---|---|---| | PostgreSQL | `postgres:17` | `5432:5432` | `pgdata:/var/lib/postgresql/data` | `POSTGRES_PASSWORD`, `POSTGRES_USER`, `POSTGRES_DB` | | pgAdmin 4 | `dpage/pgadmin4:latest` | `5050:80` | `pgadmin_data:/var/lib/pgadmin` | `PGADMIN_DEFAULT_EMAIL`, `PGADMIN_DEFAULT_PASSWORD` | **PostgreSQL Environment Variables:** | Variable | Required | Default | Description | |---|---|---|---| | `POSTGRES_PASSWORD` | Yes | -- | Superuser password (only required var) | | `POSTGRES_USER` | No | `postgres` | Superuser name and default DB name | | `POSTGRES_DB` | No | Value of `POSTGRES_USER` | Default database name | | `POSTGRES_INITDB_ARGS` | No | -- | Extra args to `initdb` (e.g., `--data-checksums`) | | `POSTGRES_HOST_AUTH_METHOD` | No | `scram-sha-256` (pg14+) | Authentication method for host connections | | `PGDATA` | No | `/var/lib/postgresql/data` (pre-18) | Data directory path | **pgAdmin Environment Variables:** | Variable | Required | Default | Description | |---|---|---|---| | `PGADMIN_DEFAULT_EMAIL` | Yes | -- | Admin login email | | `PGADMIN_DEFAULT_PASSWORD` | Yes | -- | Admin login password | | `PGADMIN_LISTEN_PORT` | No | `80` | Internal listen port | | `PGADMIN_SERVER_JSON_FILE` | No | `/pgadmin4/servers.json` | Auto-register server definitions | | `PGADMIN_DISABLE_POSTFIX` | No | unset | Set to disable mail server | | `PGADMIN_CONFIG_*` | No | -- | Override any pgAdmin `config.py` setting | ## 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] ```bash 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](scripts/docker-compose.yml) (47 lines) ```yaml # docker-compose.yml -- PostgreSQL 17 + pgAdmin 4 services: postgres: image: postgres:17 # ... (see full script) ``` **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](scripts/01-init.sql) (25 lines) ```sql -- 01-init.sql: Create application schema and default user -- Runs automatically on first container start # ... (see full script) ``` **Verify**: After first startup, `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] ```json { "Servers": { "1": { "Name": "Local PostgreSQL", "Group": "Docker", "Host": "postgres", "Port": 5432, "MaintenanceDB": "postgres", "Username": "postgres", "PassFile": "/pgadmin4/pgpass", "SSLMode": "prefer" } } } ``` Create the pgpass file for passwordless login (mount alongside servers.json): ```text postgres:5432:*:postgres:changeme ``` **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] ```bash # Start in detached mode docker compose up -d # Wait for health checks to pass docker compose ps # Check PostgreSQL logs docker compose logs postgres # Check pgAdmin logs docker compose logs pgadmin ``` **Verify**: `docker compose ps` → both services should show `healthy` status. `http://localhost:5050` → pgAdmin login page. ### 6. Set up backups Create a backup script that runs `pg_dump` inside the running container. [src5] ```bash #!/bin/bash # backup.sh -- Dump PostgreSQL database to compressed SQL file CONTAINER="postgres-pgadmin-postgres-1" DB_NAME="appdb" DB_USER="postgres" BACKUP_DIR="./backups" TIMESTAMP=$(date +%Y%m%d_%H%M%S) mkdir -p "$BACKUP_DIR" docker exec "$CONTAINER" pg_dump -U "$DB_USER" -d "$DB_NAME" \ --no-owner --clean --if-exists | gzip -9 \ > "$BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.sql.gz" echo "Backup saved: $BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.sql.gz" ``` **Verify**: `ls -la backups/` → should show the timestamped `.sql.gz` file. `gunzip -t backups/*.sql.gz` → should pass integrity check. ## Code Examples ### Docker Compose: Production with Docker Secrets > Full script: [docker-compose-production.yml](scripts/docker-compose-production.yml) (53 lines) ```yaml # docker-compose-production.yml -- Uses Docker secrets, no plain text passwords # Requires: echo "your_secure_password" > ./secrets/postgres_password # echo "admin@example.com" > ./secrets/pgadmin_email # echo "your_pgadmin_password" > ./secrets/pgadmin_password services: postgres: image: postgres:17 # ... (see full script) ``` ### Docker Compose: CI/Testing (ephemeral, no persistence) ```yaml # 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 ```bash #!/bin/bash # restore.sh -- Restore PostgreSQL from a compressed SQL dump CONTAINER="postgres-pgadmin-postgres-1" DB_USER="postgres" DB_NAME="appdb" BACKUP_FILE="$1" if [ -z "$BACKUP_FILE" ]; then echo "Usage: ./restore.sh " exit 1 fi gunzip -c "$BACKUP_FILE" | docker exec -i "$CONTAINER" \ psql -U "$DB_USER" -d "$DB_NAME" echo "Restore complete from: $BACKUP_FILE" ``` ### Python: Wait for PostgreSQL Readiness ```python import psycopg2 # psycopg2-binary==2.9.x import time import os def wait_for_postgres(max_retries=30, delay=2): """Wait for PostgreSQL to be ready before running migrations.""" 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() print(f"PostgreSQL ready after {attempt * delay}s") return True except psycopg2.OperationalError: time.sleep(delay) raise TimeoutError("PostgreSQL not ready") ``` ## Anti-Patterns ### Wrong: Hardcoding passwords in docker-compose.yml ```yaml # 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 ```yaml # 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 ```yaml # 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 ```yaml # GOOD -- waits for PostgreSQL to pass health check before starting app 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 ```yaml # BAD -- bind mounts cause permission issues, especially on macOS/Windows services: postgres: volumes: - ./pgdata:/var/lib/postgresql/data # Permission denied errors likely ``` ### Correct: Using named volumes for database data ```yaml # 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 ```yaml # 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 ```yaml # GOOD -- PostgreSQL only accessible from localhost ports: - "127.0.0.1:5432:5432" # OR: Don't expose ports at all -- let other 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. Use `docker compose down` (without `-v`) to preserve volumes. [src4] - **PGDATA path changed in PostgreSQL 18**: PostgreSQL 18+ uses `/var/lib/postgresql/18/docker` instead of `/var/lib/postgresql/data`. If upgrading, update your volume mount path. [src1] - **pgAdmin servers.json ignored on restart**: Server definitions from `servers.json` only load on first database creation. Fix: Set `PGADMIN_REPLACE_SERVERS_ON_STARTUP=True` to reload on every start. [src2] - **Connection refused from pgAdmin to PostgreSQL**: pgAdmin uses the Docker service name as hostname, not `localhost`. Fix: Use `postgres` (the service name) as the host in pgAdmin connection settings. [src3] - **Large Docker image size**: `postgres:17` is ~400MB. Fix: Use `postgres:17-alpine` (~80MB) for development and CI. Note: Alpine uses musl libc which may have edge-case behavior differences. [src6] ## Diagnostic Commands ```bash # 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 postgres-pgadmin-postgres-1 postgres-pgadmin-pgadmin-1 # Check volume disk usage docker system df -v | grep pgdata # Verify health check status docker inspect --format='{{json .State.Health}}' postgres-pgadmin-postgres-1 # Test network connectivity between containers docker compose exec pgadmin ping -c 3 postgres # Check PostgreSQL configuration docker compose exec postgres psql -U postgres -c 'SHOW ALL' | grep -i "max_connections\|shared_buffers\|work_mem" ``` ## Version History & Compatibility | Component | Version | Status | Notes | |---|---|---|---| | PostgreSQL 18 | 18.x | Latest | Version-specific PGDATA path; new features | | PostgreSQL 17 | 17.x | Current stable | Recommended for production | | PostgreSQL 16 | 16.x | Supported | LTS, widely deployed | | PostgreSQL 15 | 15.x | Supported | MERGE command introduced | | PostgreSQL 14 | 14.x | Supported | scram-sha-256 default auth | | PostgreSQL 13 | 13.x | EOL Nov 2025 | Upgrade recommended | | pgAdmin 4 v9.x | 9.12 | Current | React 19 UI, python:3-alpine base | | pgAdmin 4 v8.x | 8.14 | Previous | Legacy UI framework | | Docker Compose V2 | 2.x | Current | Plugin-based (`docker compose`) | | Docker Compose V1 | 1.29 | Deprecated | Standalone (`docker-compose`) | ## When to Use / When Not to Use | Use When | Don't Use When | Use Instead | |---|---|---| | Local development requiring PostgreSQL + GUI admin | Production database that needs managed service | AWS RDS, Cloud SQL, Supabase | | CI/CD pipeline needs ephemeral PostgreSQL | Need multi-region database replication | PostgreSQL on Kubernetes with Patroni | | Quick prototyping or demo environments | Team prefers CLI-only database management | Just `postgres` service + `psql` | | Teaching/learning PostgreSQL administration | Need advanced monitoring dashboards | Prometheus + Grafana stack | | Self-hosted staging environments | Need automated failover and HA | pgBouncer + Patroni + etcd | ## 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 ## Related Units - [Docker Compose: ELK Stack](/software/devops/docker-elk-stack/2026) - [Container Security Checklist (Docker)](/software/security/container-security/2026) - [Secrets Management Best Practices](/software/security/secrets-management/2026) - [Docker Compose: Supabase Self-Hosted](/software/devops/docker-supabase/2026) - [Docker Compose: MongoDB Replica Set](/software/devops/docker-mongodb-replicaset/2026)