Skip to content
Go back

Week 7: PostgreSQL in Production with Docker

Edit page

🐳 Docker 360 β€” Week 7 of 16 | Stateful services done right: Postgres in Docker

🎯 Learning Objective

By the end of this week, you will be able to run PostgreSQL in Docker the production way: configure it with POSTGRES_* variables, seed databases with /docker-entrypoint-initdb.d init scripts, persist data on named volumes, and perform real backup/restore drills with pg_dump. Iska matlab ye hai ke β€” your database will survive container restarts, rebuilds, and bad Mondays.

🎨 Visual Diagrams

Where Postgres keeps its data (and why volumes matter)

 WITHOUT a volume (data dies with the container):

 β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
 β”‚  postgres container     β”‚
 β”‚   /var/lib/postgresql/  β”‚
 β”‚        data/  ◀── lives in the container's writable layer
 β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜
        docker rm pg  ──▢  πŸ’₯ DATA GONE FOREVER

 WITH a named volume (data outlives the container):

 β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”        β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
 β”‚  postgres container     β”‚        β”‚  named volume    β”‚
 β”‚   /var/lib/postgresql/  │───────▢│  "pgdata"        β”‚
 β”‚        data/  (mount)   β”‚        β”‚  (managed by     β”‚
 β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜        β”‚   Docker, on disk)β”‚
        docker rm pg  ──▢  βœ… DATA SAFE β†’ attach to a new container

Init scripts: first-boot seeding

 FIRST EVER start (empty /var/lib/postgresql/data):

   postgres image boots
        β”‚
        β–Ό
   β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
   β”‚ /docker-entrypoint-initdb.d/             β”‚
   β”‚   01-schema.sql   ──▢ runs (alphabetical)β”‚
   β”‚   02-seed.sql     ──▢ runs               β”‚
   β”‚   03-setup.sh     ──▢ runs               β”‚
   β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜
        β”‚
        β–Ό
   database ready with schema + seed data βœ…

 SECOND start (volume already has data):

   init scripts are SKIPPED entirely ⚠️
   (iska matlab: ye sirf pehli dafa chalte hain!)

App β†’ Postgres connection on a user-defined network

 β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”                    β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
 β”‚  api         β”‚   postgres://      β”‚  pg          β”‚
 β”‚  container   β”‚ ─────────────────▢ β”‚  container   β”‚
 β”‚              β”‚   hostname = "pg"  β”‚  :5432       β”‚
 β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜   (container name  β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜
        β”‚            = DNS name!)            β–²
        └──────── appnet (user-defined) β”€β”€β”€β”€β”€β”˜
              automatic DNS resolution βœ…

Iska matlab ye hai ke β€” on a user-defined network, containers find each other by name. No hardcoded IPs, no --link hacks.

🧠 Theory Deep Dive

The official image’s contract: POSTGRES_* variables

The postgres image refuses to start without a password decision β€” a deliberate safety default:

VariablePurposeNotes
POSTGRES_PASSWORDSuperuser passwordRequired (or use _FILE variant / trust mode)
POSTGRES_USERSuperuser nameDefault: postgres
POSTGRES_DBDatabase created at initDefault: same as POSTGRES_USER
POSTGRES_INITDB_ARGSExtra initdb flagse.g. --auth-host=scram-sha-256

πŸ” Production tip: use POSTGRES_PASSWORD_FILE=/run/secrets/pg_password with Docker secrets instead of plain env vars β€” env values are visible in docker inspect.

Init scripts run once β€” design accordingly

Files in /docker-entrypoint-initdb.d/ (.sql, .sql.gz, .sh) execute only when the data directory is empty. Make them idempotent (CREATE TABLE IF NOT EXISTS, INSERT ... ON CONFLICT DO NOTHING) so re-runs never explode. For schema changes on an existing database, init scripts are the wrong tool β€” use a migration tool (Flyway, Prisma Migrate, Alembic) instead.

Tuning without rebuilding: -c flags

You don’t need a custom postgresql.conf for simple tuning β€” append -c options after the image name:

docker run ... postgres:17 -c max_connections=200 -c shared_buffers=256MB

For heavier customization, mount your own config: -v ./postgresql.conf:/etc/postgresql/postgresql.conf -c config_file=/etc/postgresql/postgresql.conf.

Backup strategy: pg_dump basics

MethodWhat it backs upRestore with
pg_dump dbnameOne database (schema + data, SQL text)psql
pg_dump -Fc dbnameOne database (custom compressed format)pg_restore
pg_dumpallAll databases + rolespsql

Iska matlab ye hai ke β€” pg_dump doesn’t back up the server config or roles (unless pg_dumpall). For a full disaster-recovery story, back up the volume too.

πŸ’» Hands-On Lab

Part A β€” Run Postgres properly

Step 1 β€” Create a network and a named volume

docker network create appnet
docker volume create pgdata

Step 2 β€” Prepare init scripts

mkdir -p ~/docker360/week7/initdb && cd ~/docker360/week7
cat > initdb/01-schema.sql <<'EOF'
CREATE TABLE IF NOT EXISTS students (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  course TEXT NOT NULL DEFAULT 'docker-360'
);
EOF
cat > initdb/02-seed.sql <<'EOF'
INSERT INTO students (name, course) VALUES
  ('Ali', 'docker-360'),
  ('Sana', 'docker-360')
ON CONFLICT DO NOTHING;
EOF
ls initdb/

Step 3 β€” Start Postgres with init scripts mounted

docker run -d --name pg \
  --network appnet \
  -e POSTGRES_USER=appuser \
  -e POSTGRES_PASSWORD='Str0ng!Pass' \
  -e POSTGRES_DB=appdb \
  -v pgdata:/var/lib/postgresql/data \
  -v "$PWD/initdb":/docker-entrypoint-initdb.d:ro \
  postgres:17
sleep 5
docker logs pg --tail 8

βœ… Expected result: logs show database system is ready to accept connections and lines about executing the init files.

Step 4 β€” Verify the seed data

docker exec -it pg psql -U appuser -d appdb -c "TABLE students;"

βœ… Expected result:

 id | name |  course
----+------+-----------
  1 | Ali  | docker-360
  2 | Sana | docker-360

Iska matlab ye hai ke β€” the init scripts ran exactly once on first boot and seeded your database.

Part B β€” Prove the volume survives

Step 5 β€” Delete the container, keep the volume, start fresh

docker stop pg && docker rm pg
# NOTE: we do NOT remove the pgdata volume!
docker run -d --name pg2 \
  --network appnet \
  -e POSTGRES_USER=appuser \
  -e POSTGRES_PASSWORD='Str0ng!Pass' \
  -e POSTGRES_DB=appdb \
  -v pgdata:/var/lib/postgresql/data \
  postgres:17
sleep 5
docker exec pg2 psql -U appuser -d appdb -tAc "SELECT count(*) FROM students;"

βœ… Expected result: 2 β€” the data survived the container’s death because it lives in the volume, not the container.

⚠️ Try starting pg2 without -v pgdata:... once and re-running the count β€” you’ll get an error that the table doesn’t exist. That’s the lesson, burned into memory.

Part C β€” Backup and restore drill

Step 6 β€” Take a backup with pg_dump

docker exec pg2 pg_dump -U appuser appdb > ~/docker360/week7/appdb-backup.sql
ls -lh ~/docker360/week7/appdb-backup.sql
head -20 ~/docker360/week7/appdb-backup.sql

βœ… Expected result: a SQL file (a few KB) starting with PostgreSQL dump header comments.

Step 7 β€” Simulate disaster and restore

# Disaster: drop the table
docker exec pg2 psql -U appuser -d appdb -c "DROP TABLE students;"
docker exec pg2 psql -U appuser -d appdb -c "TABLE students;"  # β†’ ERROR, table gone

# Restore from backup
cat ~/docker360/week7/appdb-backup.sql | docker exec -i pg2 psql -U appuser -d appdb
docker exec pg2 psql -U appuser -d appdb -tAc "SELECT count(*) FROM students;"

βœ… Expected result: after restore, the count is 2 again. Iska matlab ye hai ke β€” you just survived a (simulated) disaster with a one-line backup and a one-line restore. Schedule this with cron in real life.

Part D β€” Connect an app container

Step 8 β€” Verify DNS-by-name from a client container

docker run --rm --network appnet postgres:17 \
  psql "postgres://appuser:Str0ng!Pass@pg2:5432/appdb" -tAc "SELECT 'connected via hostname pg2 βœ…';"

βœ… Expected result: connected via hostname pg2 βœ… β€” the hostname is just the container name. In your app code, the connection string is postgres://appuser:<pass>@pg2:5432/appdb.

Step 9 β€” Clean up

docker stop pg2 && docker rm pg2
docker volume rm pgdata
docker network rm appnet

βœ… Checkpoint

Q1: You restart your Postgres container and the init scripts don’t run again. Is something broken? A1: No β€” that’s by design. /docker-entrypoint-initdb.d scripts run only when the data directory is empty (first init). On restarts with existing data they’re skipped.

Q2: Your container was removed with docker rm but the volume still exists. Is your data safe? A2: Yes. Data in a named volume lives outside the container’s filesystem. Attach the same volume to a new container and everything is back.

Q3: How does an app container reach Postgres β€” IP address or something else? A3: By container name as hostname (e.g. pg2:5432) on a shared user-defined network, which gives automatic DNS resolution. Never hardcode container IPs β€” they change.

Q4: What’s the difference between pg_dump and pg_dumpall? A4: pg_dump backs up a single database; pg_dumpall backs up the whole cluster including all databases, roles, and global objects. Use pg_dumpall (or volume backups) for full disaster recovery.

πŸ”œ Next Week

Week 8: automation time β€” build a real CI/CD pipeline with GitHub Actions: lint, build, scan, push to GHCR, and deploy with Compose.


Edit page
Share this post on:

Previous Post
Week 8: CI/CD Pipeline for Docker Apps
Next Post
Week 6: Containerizing a Node.js Backend