PostgreSQL Docker and Operations

Introduction

Running PostgreSQL in Docker is the fastest way to share a dev database across teammates. Production adds persistent volumes, dedicated app roles, backups, monitoring, and upgrade planning. This chapter covers Compose patterns, docker-entrypoint-initdb.d, postgresql.conf / pg_hba.conf overview, backup habits, and links to managed hosting. Compare with MongoDB Docker ops and Linux Docker.

Prerequisites

• Installation and psql
• Backup and Restore
• Roles and Permissions

Single-Node Docker (Development)

Connect:

psql "postgresql://postgres:devpass@127.0.0.1:5432/hello_demo"

Code explanation:

• pg_data volume survives container recreation
• Pin postgres:16 tag for reproducible dev—avoid latest in teams

Init Scripts on First Boot

Mount SQL or shell into /docker-entrypoint-initdb.d/:

volumes:
  - pg_data:/var/lib/postgresql/data
  - ./init:/docker-entrypoint-initdb.d:ro

init/01_schema.sql:

CREATE TABLE IF NOT EXISTS app_meta (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL
);
 
INSERT INTO app_meta (key, value) VALUES ('schema_version', '1')
ON CONFLICT (key) DO NOTHING;

Scripts run only when the data directory is empty. To re-run: docker volume rm (destructive) or use migration tools (Flyway, Alembic).

See Project deploy for full Compose with API + Redis.

App User (Least Privilege)

After container starts, create a non-superuser role:

CREATE ROLE app_user WITH LOGIN PASSWORD 'app_pass';
GRANT CONNECT ON DATABASE hello_demo TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;
 
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

App connection string:

postgresql://app_user:app_pass@127.0.0.1:5432/hello_demo

Never use postgres superuser in application code—Roles and Permissions.

Configuration Files

Mount custom config (advanced):

volumes:
  - ./conf/postgresql.conf:/etc/postgresql/postgresql.conf:ro
  - ./conf/pg_hba.conf:/etc/postgresql/pg_hba.conf:ro
command: postgres -c config_file=/etc/postgresql/postgresql.conf -c hba_file=/etc/postgresql/pg_hba.conf

Useful dev logging:

log_min_duration_statement = 500
log_line_prefix = '%m [%p] %u@%d '

Reload without restart:

docker exec postgres pg_ctl reload -D /var/lib/postgresql/data

Production tuning: shared_buffers, work_mem, max_connections—Performance chapter.

Backup in Docker

Dump from running container:

docker exec postgres pg_dump -U postgres -d hello_demo -Fc -f /tmp/hello_demo.dump
docker cp postgres:/tmp/hello_demo.dump ./backups/

Schedule cron on host or CI:

pg_dump "$DATABASE_URL" -Fc -f "backup_$(date +%F).dump"

Test restore monthly to a throwaway database—Backup and Restore.

Upgrades

Always backup before upgrade.

Production Checklist

Managed PostgreSQL (Awareness)

Same SQL and drivers—connection strings and roles differ from self-hosted Docker.

Multi-Service Compose

Full stack from chapter 27:

postgres (5432) + redis (6379) + api (8000)

depends_on with condition: service_healthy avoids API starting before Postgres accepts connections.

FAQ

Data lost after docker compose down?

Only if you removed the volume. docker compose down keeps pg_data; docker volume rm pg_data wipes data.

Port 5432 already in use?

Stop local Postgres or map 5433:5432.

Init script did not run?

Volume already initialized—drop volume or run migrations manually.

Where are logs?

docker logs postgres; file logs depend on log_destination in postgresql.conf.

Replica set in Docker?

Streaming replication is advanced—Replication intro; use managed HA for production learning path.

Nginx in front?

Terminate TLS at Nginx; app connects to Postgres on private network.