PostgreSQL with Docker¶

PostgreSQL in Docker is a standard pattern for local development, integration testing, and repeatable CI workflows.

This page is a practical cheatsheet for the commands you use most: run, connect, inspect, back up, restore, and debug.

What It Is¶

Running PostgreSQL with Docker means:

A PostgreSQL server process runs inside a container
Data is stored in a Docker volume or bind mount
You connect through mapped host ports or Docker networks
Initialization is controlled by environment variables and optional init scripts

The official PostgreSQL Docker image initializes a new database cluster on first startup, then reuses existing data on subsequent starts.

When to Use It¶

Use PostgreSQL with Docker when:

You need a fast local database for application development
You want reproducible database setup in CI pipelines
You need isolated versions across projects
You want disposable environments for testing migrations
You need a quick debugging environment for SQL or connection issues

Core Commands¶

Pull and Pin an Image Version¶

docker pull postgres:18

Why it matters:

Pinning a major version avoids unexpected breaking changes from moving tags
Aligns local development with the target runtime version

Run PostgreSQL with Persistent Storage¶

docker volume create pgdata

docker run -d \
  --name dojo-postgres \
  -e POSTGRES_USER=appuser \
  -e POSTGRES_PASSWORD=appsecret \
  -e POSTGRES_DB=appdb \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql \
  --restart unless-stopped \
  postgres:18

Why it matters:

Named volume preserves data across container restarts/recreation
POSTGRES_* values define initial user/database during first initialization

Version note:

PostgreSQL Docker image 18+: mount at /var/lib/postgresql
PostgreSQL Docker image 17 and below: mount at /var/lib/postgresql/data

Check Container Health and Logs¶

docker ps
docker logs dojo-postgres --tail 100
docker logs -f dojo-postgres
docker inspect dojo-postgres --format '{{.State.Status}}'
docker exec dojo-postgres pg_isready -U appuser -d appdb

Why it matters:

Confirms container state and database readiness separately
Startup logs usually reveal auth, config, or init-script issues quickly

Note:

docker inspect ... '{{json .State.Health}}' returns null unless a healthcheck is explicitly configured for the container.
pg_isready is the reliable readiness check even when Docker healthcheck is not configured.

Optional (if you configured --health-cmd on docker run):

docker inspect dojo-postgres --format '{{json .State.Health}}'

Connect with `psql`¶

From host (if psql installed locally):

psql "host=127.0.0.1 port=5432 dbname=appdb user=appuser password=appsecret sslmode=disable"

From inside container:

docker exec -it dojo-postgres psql -U appuser -d appdb

Why it matters:

Separates client-install issues from server/network issues
Gives immediate SQL access for verification and troubleshooting

Run SQL Non-Interactively¶

Run inline SQL:

docker exec -i dojo-postgres psql -U appuser -d appdb -c "SELECT now();"

Run a local SQL file:

cat schema.sql | docker exec -i dojo-postgres psql -U appuser -d appdb

What schema.sql is:

A plain SQL file in your project (for example ./db/schema.sql)
Usually contains DDL statements such as CREATE TABLE, CREATE INDEX, and optional seed inserts
Used to bootstrap a database structure consistently across local/dev/CI environments

Why it matters:

Useful for bootstrapping schemas and automation
Easy to script in CI and test environments

Useful `psql` Meta Commands¶

Inside psql:

\l
\du
\dt
\dn
\d table_name
\conninfo

Why it matters:

Quickly verifies database, role, and table state
Reduces guesswork during debugging

Create Database and Role¶

docker exec -i dojo-postgres psql -U appuser -d appdb -c "CREATE ROLE service_user LOGIN PASSWORD 'change_me';"
docker exec -i dojo-postgres psql -U appuser -d appdb -c "CREATE DATABASE service_db OWNER service_user;"
docker exec -i dojo-postgres psql -U appuser -d service_db -c "GRANT ALL PRIVILEGES ON DATABASE service_db TO service_user;"

Why it matters:

Helps isolate app credentials per service/environment
Keeps ownership and permissions explicit

Back Up and Restore¶

Plain SQL backup:

docker exec -t dojo-postgres pg_dump -U appuser -d appdb > appdb.sql

Custom-format backup:

docker exec -t dojo-postgres pg_dump -U appuser -d appdb -Fc > appdb.dump

Restore plain SQL:

cat appdb.sql | docker exec -i dojo-postgres psql -U appuser -d appdb

Restore custom-format backup:

cat appdb.dump | docker exec -i dojo-postgres pg_restore -U appuser -d appdb --clean --if-exists

Why it matters:

Enables safe reset/recovery workflows
Standardizes migration and local refresh procedures

Copy Files Between Host and Container¶

docker cp dojo-postgres:/var/lib/postgresql /tmp/postgresql-copy
docker cp ./seed.sql dojo-postgres:/tmp/seed.sql
docker exec -i dojo-postgres psql -U appuser -d appdb -f /tmp/seed.sql

Why it matters:

Simplifies moving seed files and diagnostics artifacts

Stop, Start, Restart, Remove¶

docker stop dojo-postgres
docker start dojo-postgres
docker restart dojo-postgres
docker rm -f dojo-postgres

Remove container and volume (destructive):

docker rm -f dojo-postgres
docker volume rm pgdata

Why it matters:

Supports clean environment resets
Keeps destructive cleanup explicit and intentional

Initialize with SQL or Scripts on First Boot¶

The image runs scripts in /docker-entrypoint-initdb.d/ during first initialization only.

docker run -d \
  --name dojo-postgres-init \
  -e POSTGRES_USER=appuser \
  -e POSTGRES_PASSWORD=appsecret \
  -e POSTGRES_DB=appdb \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql \
  -v "$(pwd)/init:/docker-entrypoint-initdb.d" \
  postgres:18

Why it matters:

Good for repeatable local bootstrap (schema, seed, extensions)
Critical to remember: these scripts do not rerun on an already initialized data directory

Real-World Example¶

Scenario: your app fails on startup with password authentication failed for user.

Step-by-step workflow:

Verify container state and readiness:

docker ps --filter name=dojo-postgres
docker exec dojo-postgres pg_isready -U appuser -d appdb

Inspect logs for auth/startup errors:

docker logs dojo-postgres --tail 200

Validate current roles/databases directly:

docker exec -it dojo-postgres psql -U appuser -d appdb -c "\du"
docker exec -it dojo-postgres psql -U appuser -d appdb -c "\l"

Confirm app connection string matches host/port/db/user:
host: 127.0.0.1 (or service name in Docker network)
port: 5432
database: appdb
user: appuser
If credentials were changed after first initialization, recreate with clean data volume:

docker rm -f dojo-postgres
docker volume rm pgdata

Then start again with the desired POSTGRES_* values.

Likely outcomes:

Wrong app credentials or db name in connection string
Misunderstanding that POSTGRES_* vars only apply on first cluster initialization
Volume reusing old cluster state

Debugging Pattern¶

Use this repeatable sequence:

Check process/container state (docker ps, docker logs)
Check readiness (pg_isready)
Check connectivity (psql from host and from container)
Check identity and privileges (\du, \l, grants)
Check initialization assumptions (fresh volume vs existing data)
Check backup/restore path for data recovery

Decision shortcuts:

connection refused: server not listening, container stopped, or port not mapped
password authentication failed: wrong credentials or role mismatch
database does not exist: wrong DB name or initialization did not create expected DB
init scripts not applied: existing data directory prevented first-boot init flow

Common Pitfalls¶

Using latest tags instead of pinning a major version
Forgetting a persistent volume and losing data on container removal
Expecting POSTGRES_USER/POSTGRES_DB/POSTGRES_PASSWORD to update an already initialized cluster
Publishing 5432 to all interfaces unnecessarily on shared machines
Storing credentials directly in shell history or committed files
Running destructive restore flags (--clean) against the wrong database
Assuming container running means database ready; always verify with pg_isready

PostgreSQL with Docker¶

What It Is¶

When to Use It¶

Core Commands¶

Pull and Pin an Image Version¶

Run PostgreSQL with Persistent Storage¶

Check Container Health and Logs¶

Connect with psql¶

Run SQL Non-Interactively¶

Useful psql Meta Commands¶

Create Database and Role¶

Back Up and Restore¶

Copy Files Between Host and Container¶

Stop, Start, Restart, Remove¶

Initialize with SQL or Scripts on First Boot¶

Real-World Example¶

Debugging Pattern¶

Common Pitfalls¶

Connect with `psql`¶

Useful `psql` Meta Commands¶