21.12. PostgreSQL with Docker

Table of Contents

Why Run PostgreSQL in Docker?

Running PostgreSQL inside Docker gives you a reproducible, disposable database environment. You can:

Spin up PostgreSQL in seconds.
Keep your local machine clean.
Use different PostgreSQL versions per project.
Share a standard setup with your team.

In this chapter you will learn practical ways to run PostgreSQL with Docker for local development and testing. Production details will be handled in the deployment chapters, so we keep the focus here on local use.

Getting a PostgreSQL Image

The official PostgreSQL image is on Docker Hub with the name postgres.

You can see it with:

bash

docker pull postgres:16

postgres:16 means:

Repository: postgres
Tag (version): 16

You can omit the tag:

bash

docker pull postgres

This pulls the latest stable version, but for projects it is usually better to pin a specific version, for example postgres:15 or postgres:16.

Always pin a PostgreSQL version in your Dockerfile or docker-compose.yml, for example postgres:16.
Do not rely on latest for real projects.

Running PostgreSQL with `docker run`

The simplest PostgreSQL container only needs a password:

bash

docker run --name my-postgres \
  -e POSTGRES_PASSWORD=mysecretpassword \
  -p 5432:5432 \
  -d postgres:16

What each flag does:

Flag / option	Meaning
`--name my-postgres`	Gives the container a readable name
`-e POSTGRES_PASSWORD=…`	Sets the `postgres` superuser password inside the container
`-p 5432:5432`	Maps host port 5432 to container port 5432
`-d`	Detached mode, container runs in the background
`postgres:16`	Image name and tag, here PostgreSQL version 16

Now you can connect from your host using any PostgreSQL client.

Example using psql on your machine:

bash

psql -h localhost -p 5432 -U postgres

It will ask for the password mysecretpassword.

If you want to override the default database and user that are created, the image supports:

POSTGRES_DB
POSTGRES_USER
POSTGRES_PASSWORD

Example:

bash

docker run --name my-postgres \
  -e POSTGRES_DB=myapp_db \
  -e POSTGRES_USER=myapp_user \
  -e POSTGRES_PASSWORD=myapp_password \
  -p 5432:5432 \
  -d postgres:16

This creates:

Database: myapp_db
User: myapp_user
Password: myapp_password

You can then connect with:

bash

psql "postgresql://myapp_user:myapp_password@localhost:5432/myapp_db"

Persisting PostgreSQL Data with Volumes

By default, if the container is removed, your data is lost. For development this is sometimes fine, but usually you want persistent data.

The PostgreSQL image stores its data in /var/lib/postgresql/data inside the container. You can map this path to a Docker volume or a host directory.

Using a named volume

bash

docker volume create pgdata

Then run:

bash

docker run --name my-postgres \
  -e POSTGRES_PASSWORD=mysecretpassword \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql/data \
  -d postgres:16

Now even if you remove the container:

bash

docker rm -f my-postgres

Your data remains in the pgdata volume. You can reuse it:

bash

docker run --name my-postgres \
  -e POSTGRES_PASSWORD=mysecretpassword \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql/data \
  -d postgres:16

Using a host directory

You can also store data in a folder on your machine:

bash

mkdir -p ~/docker-data/postgres
docker run --name my-postgres \
  -e POSTGRES_PASSWORD=mysecretpassword \
  -p 5432:5432 \
  -v ~/docker-data/postgres:/var/lib/postgresql/data \
  -d postgres:16

Now your database files live in ~/docker-data/postgres.

Never store PostgreSQL data on a folder that is inside a Git repository or in a sync folder like Dropbox or Google Drive.
Use a dedicated data directory or named volume instead.

Connecting an Application Container to PostgreSQL

The typical setup is:

One container for your app (for example FastAPI).
One container for PostgreSQL.
Both containers on the same Docker network.

You can create a network:

bash

docker network create myapp-network

Run PostgreSQL on that network:

bash

docker run --name my-postgres \
  --network myapp-network \
  -e POSTGRES_DB=myapp_db \
  -e POSTGRES_USER=myapp_user \
  -e POSTGRES_PASSWORD=myapp_password \
  -v pgdata:/var/lib/postgresql/data \
  -d postgres:16

Then run your app container on the same network:

bash

docker run --name myapp \
  --network myapp-network \
  -e DATABASE_URL=postgresql://myapp_user:myapp_password@my-postgres:5432/myapp_db \
  -p 8000:8000 \
  myapp-image

Notice that in DATABASE_URL the host is my-postgres. Inside the network, containers can reach each other using their container names.

Example connection string

For a Python backend with SQLAlchemy:

python

DATABASE_URL = "postgresql+psycopg2://myapp_user:myapp_password@my-postgres:5432/myapp_db"

The database connection URL structure is:

$$
\text{postgresql://user:password@host:port/database}
$$

For SQLAlchemy with psycopg2 driver:

$$
\text{postgresql+psycopg2://user:password@host:port/database}
$$

Inside Docker networks use the container name as the host, for example my-postgres.
From your host machine use localhost (with port mapping).

Using Docker Compose for PostgreSQL

docker-compose (or docker compose in newer Docker versions) lets you define multi-container setups in one YAML file.

Create a docker-compose.yml:

yaml

version: "3.9"
services:
  db:
    image: postgres:16
    container_name: myapp-postgres
    environment:
      POSTGRES_DB: myapp_db
      POSTGRES_USER: myapp_user
      POSTGRES_PASSWORD: myapp_password
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data
  app:
    build: .
    container_name: myapp-api
    environment:
      DATABASE_URL: postgresql://myapp_user:myapp_password@db:5432/myapp_db
    ports:
      - "8000:8000"
    depends_on:
      - db
volumes:
  pgdata:

Key points:

The database service is called db.
The app connects to host db on port 5432.
The pgdata named volume keeps data persistent.
depends_on ensures PostgreSQL container is started before app is started.

Start everything:

bash

docker compose up -d

Stop containers but keep data:

bash

docker compose down

Stop and remove data too:

bash

docker compose down -v

Use docker compose down -v carefully.
The -v flag removes named volumes, so it deletes all data in your PostgreSQL database.

Seeding and Initializing the Database

The PostgreSQL image supports running initialization scripts on first startup.

Any .sql or .sh files placed in /docker-entrypoint-initdb.d/ are executed the first time the database is created.

Example directory structure:

text

.
├── docker-compose.yml
└── db-init
    └── 01_create_tables.sql

01_create_tables.sql:

sql

CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Update docker-compose.yml:

yaml

services:
  db:
    image: postgres:16
    environment:
      POSTGRES_DB: myapp_db
      POSTGRES_USER: myapp_user
      POSTGRES_PASSWORD: myapp_password
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data
      - ./db-init:/docker-entrypoint-initdb.d

On the first run of this database volume, PostgreSQL will:

Create the database and user.
Run all scripts in /docker-entrypoint-initdb.d in alphabetical order.

If you want to re-run the initialization, you must remove the volume:

bash

docker compose down -v
docker compose up -d

You can also use shell scripts:

bash

# db-init/02_extra.sh
#!/bin/bash
set -e
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-SQL
  INSERT INTO users (email) VALUES ('admin@example.com')
  ON CONFLICT DO NOTHING;
SQL

Remember to make it executable:

bash

chmod +x db-init/02_extra.sh

Running `psql` inside the Container

You do not always need psql on your host. You can use the psql client that is already inside the PostgreSQL container.

Connect to the container:

bash

docker exec -it my-postgres bash

Then:

bash

psql -U myapp_user -d myapp_db

Or in one command from outside:

bash

docker exec -it my-postgres \
  psql -U myapp_user -d myapp_db

Some useful psql commands for quick checks:

Command	Description
`\l`	List databases
`\c myapp_db`	Connect to database
`\dt`	List tables
`\d table_name`	Describe a table
`\q`	Quit `psql`

Environment Variables and Secrets

In examples above we put passwords directly in the compose file. For real projects, you want to keep secrets out of the repository.

Common patterns:

Using `.env` file with docker compose

Create a .env file:

env

POSTGRES_DB=myapp_db
POSTGRES_USER=myapp_user
POSTGRES_PASSWORD=my_strong_password

In docker-compose.yml:

yaml

services:
  db:
    image: postgres:16
    environment:
      POSTGRES_DB: ${POSTGRES_DB}
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}

Docker compose automatically loads .env in the same directory.

Your app service can also read from .env and build a DATABASE_URL.

Using environment variables directly

You can also export variables in your shell before running compose:

bash

export POSTGRES_PASSWORD=my_strong_password
docker compose up -d

Never commit real database passwords or connection strings to Git.
Use .env files ignored by Git or environment variables in your shell or CI.

Common Problems and Debugging

Container cannot connect to PostgreSQL

If your app shows errors like:

could not connect to server: Connection refused

Check:

Is the database running?

bash

   docker ps

Logs of the database:

bash

   docker logs my-postgres

Correct host name in the connection string:

From host: localhost
From another container on same network: container name, for example my-postgres or service name db.

Correct port:

Default PostgreSQL port is 5432.
If you mapped port 5433:5432, then from the host you must use 5433.

Port already in use

Error on docker run:

Bind for 0.0.0.0:5432 failed: port is already allocated

Someone is already using port 5432 on your host, maybe a local PostgreSQL installation.

Solutions:

Stop the local PostgreSQL service.
Or run PostgreSQL on a different host port:

bash

  docker run --name my-postgres \
    -e POSTGRES_PASSWORD=mysecretpassword \
    -p 5433:5432 \
    -d postgres:16

Then connect with localhost:5433.

Resetting everything in development

If your local development database is broken or you want a fresh start:

Stop containers:

bash

   docker compose down

Remove database volume:

bash

   docker volume rm <project_name>_pgdata

Or simply:

bash

   docker compose down -v

Start again:

bash

   docker compose up -d

This will recreate the database from scratch and rerun your initialization scripts.

Putting It All Together: Example FastAPI + PostgreSQL Setup

Here is a minimal structure for a FastAPI backend with PostgreSQL, all in Docker.

text

.
├── app
│   ├── main.py
│   └── requirements.txt
└── docker-compose.yml

app/requirements.txt:

text

fastapi
uvicorn[standard]
sqlalchemy
psycopg2-binary

app/main.py (very simplified):

python

from fastapi import FastAPI
from sqlalchemy import create_engine, text
DATABASE_URL = "postgresql+psycopg2://myapp_user:myapp_password@db:5432/myapp_db"
engine = create_engine(DATABASE_URL, echo=True, future=True)
app = FastAPI()
@app.get("/ping-db")
def ping_db():
    with engine.connect() as conn:
        result = conn.execute(text("SELECT 1")).scalar_one()
    return {"db": result}

docker-compose.yml:

yaml

version: "3.9"
services:
  db:
    image: postgres:16
    environment:
      POSTGRES_DB: myapp_db
      POSTGRES_USER: myapp_user
      POSTGRES_PASSWORD: myapp_password
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data
  app:
    build:
      context: ./app
      dockerfile: Dockerfile
    environment:
      # Could also be read from env or .env
      DATABASE_URL: postgresql+psycopg2://myapp_user:myapp_password@db:5432/myapp_db
    ports:
      - "8000:8000"
    depends_on:
      - db
volumes:
  pgdata:

app/Dockerfile:

dockerfile

FROM python:3.12-slim
WORKDIR /app
COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt
COPY . .
CMD ["uvicorn", "main:app", "--host", "0.0.0.0", "--port", "8000"]

Start everything:

bash

docker compose up -d

Test from your browser or curl:

bash

curl http://localhost:8000/ping-db

You should get:

json

{"db": 1}

Your FastAPI app is now talking to PostgreSQL, both running in Docker containers.

You now have all the practical tools you need to:

Run PostgreSQL with docker run and with Docker Compose.
Persist data using volumes.
Connect containerized backends to PostgreSQL.
Seed and initialize databases with scripts.
Handle common connection and port issues.

More advanced PostgreSQL performance and tuning topics are covered in the dedicated PostgreSQL and deployment chapters.

21.12. PostgreSQL with Docker

Why Run PostgreSQL in Docker?

Getting a PostgreSQL Image

Running PostgreSQL with `docker run`

Persisting PostgreSQL Data with Volumes

Using a named volume

Using a host directory

Connecting an Application Container to PostgreSQL

Example connection string

Using Docker Compose for PostgreSQL

Seeding and Initializing the Database

Running `psql` inside the Container

Environment Variables and Secrets

Using `.env` file with docker compose

Using environment variables directly

Common Problems and Debugging

Container cannot connect to PostgreSQL

Port already in use

Resetting everything in development

Putting It All Together: Example FastAPI + PostgreSQL Setup

Comments

Where to Move