"Works on my machine" usually means "my machine has Postgres 14 with a database I created by hand two years ago." A Compose file fixes that: every developer runs docker compose up and gets the same database.
The file
# docker-compose.yml services: db: image: postgres:17 restart: unless-stopped environment: POSTGRES_USER: app POSTGRES_PASSWORD: app POSTGRES_DB: app_dev ports: - "5432:5432" volumes: - pgdata:/var/lib/postgresql/data - ./db/init:/docker-entrypoint-initdb.d:ro healthcheck: test: ["CMD-SHELL", "pg_isready -U app -d app_dev"] interval: 5s timeout: 3s retries: 10 pgadmin: image: dpage/pgadmin4 restart: unless-stopped environment: PGADMIN_DEFAULT_EMAIL: dev@example.com PGADMIN_DEFAULT_PASSWORD: dev PGADMIN_CONFIG_SERVER_MODE: "False" ports: - "5050:80" volumes: - pgadmin:/var/lib/pgadmin depends_on: db: condition: service_healthy volumes: pgdata: pgadmin:
Start it:
docker compose up -d docker compose ps # both should be "running" / "healthy"
pgAdmin is at http://localhost:5050.
Pin the Postgres version
Use the same major version as production (postgres:16, postgres:17). A data directory created by one major version won't start under another, so changing the tag later means dumping and restoring.
Connecting
The host name depends on where the client runs:
| Client | Host | Port |
|---|---|---|
| Your app running on your laptop | localhost | 5432 |
| pgAdmin (inside Compose) | db | 5432 |
| Another Compose service | db | 5432 |
Connection string for your app:
DATABASE_URL=postgres://app:app@localhost:5432/app_dev
In pgAdmin: Add New Server → Connection → Host db, user app, password app.
If port 5432 is taken because Postgres is already installed on your machine, map a different host port: "5433:5432", then use localhost:5433.
Seed data with init scripts
Any .sql or .sh file in ./db/init runs on first start only, in alphabetical order:
-- db/init/01-extensions.sql CREATE EXTENSION IF NOT EXISTS pgcrypto; CREATE EXTENSION IF NOT EXISTS pg_trgm;
If you edit a script and nothing happens, that's why: the volume already has data. Reset to rerun them.
For schema, prefer your migration tool (Prisma, Drizzle, Flyway) over init scripts, so local and production follow the same path.
Everyday commands
docker compose logs -f db # watch logs docker compose exec db psql -U app -d app_dev # psql shell docker compose stop # stop, keep data docker compose down # remove containers, keep data docker compose down -v # remove containers AND data (full reset)
Backup and restore
docker compose exec -T db pg_dump -U app app_dev > backup.sql docker compose exec -T db psql -U app -d app_dev < backup.sql
A test database too
Add a second service on another port with tmpfs storage. It's fast, and it's wiped every time it restarts:
db_test: image: postgres:17 environment: POSTGRES_USER: app POSTGRES_PASSWORD: app POSTGRES_DB: app_test ports: - "5434:5432" tmpfs: - /var/lib/postgresql/data
Don't ship these passwords
These credentials are for local development only. Keep production credentials out of the Compose file and the repo. See How to Generate Strong API Keys and Secrets.