Custom PostgreSQL Docker Image

Date: November 26, 2025 (updated 2026-06-10 for PG18)
Category: Infrastructure / Docker
Status: Active

Overview

Custom PostgreSQL 18 Docker image that includes pgvector and hypopg extensions. Builds automatically when running docker compose up.

Why Custom Image?

The standard pgvector/pgvector:pg18 image includes pgvector but not hypopg. We need both:

Extension	Purpose	Included in pgvector image?
pgvector	Vector similarity search	✅ Yes
pg_stat_statements	Query statistics	✅ Yes (requires preload)
hypopg	Hypothetical index analysis	❌ No

Dockerfile

docker/postgresql.Dockerfile

FROM pgvector/pgvector:pg18

RUN apt-get update && apt-get install -y --no-install-recommends \
    postgresql-18-hypopg \
    && rm -rf /var/lib/apt/lists/*

The real docker/postgresql.Dockerfile is parameterized (PG_MAJOR, PG_SUITE, BASE) and builds from an Ubuntu/Debian base rather than the pgvector/pgvector image — the snippet above is the conceptual shape. Dev/staging/prod all build PG_MAJOR=18 from it.

Docker Compose Configuration

docker-compose.yml

services:
  pg:
    build:
      context: .
      dockerfile: docker/postgresql.Dockerfile
      args:
        PG_MAJOR: "18"
    image: heatwave-postgres:18-noble
    restart: always
    shm_size: 2gb
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      DB_OWNER: deploy
      DB_OWNER_PASSWORD: deploy
      POSTGRES_MULTIPLE_DATABASES: heatwave, heatwave_versions
    ports:
      - '5432:5432'
    volumes:
      - '${HOME}/Projects/heatwave-infra/postgres/18/data:/var/lib/postgresql/data'
      - './docker/pg-init-scripts:/docker-entrypoint-initdb.d'
    command:
      - "postgres"
      - "-c"
      - "shared_buffers=1GB"
      - "-c"
      - "work_mem=128MB"
      - "-c"
      - "maintenance_work_mem=512MB"
      - "-c"
      - "shared_preload_libraries=pg_stat_statements"
      - "-c"
      - "pg_stat_statements.track=all"

Init Script

Extensions are created automatically on first run:

-- docker/pg-init-scripts/05-pghero-extensions.sql
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS hypopg;
GRANT pg_read_all_stats TO deploy;

Usage

First Time / Fresh Build

docker compose build pg
docker compose up -d pg

Verify Extensions

docker compose exec pg psql -U postgres -d heatwave -c "\dx"

Expected output:

      Name           | Version |   Schema   |         Description
---------------------+---------+------------+------------------------------
 hypopg              | 1.4.0   | public     | Hypothetical indexes
 pg_stat_statements  | 1.10    | public     | Query statistics
 vector              | 0.7.0   | public     | Vector similarity search

Rebuild Image

docker compose build --no-cache pg
docker compose up -d --force-recreate pg

Enable Extensions on Existing Database

If the database already exists (from before this change):

docker compose exec pg psql -U postgres -d heatwave -c "CREATE EXTENSION IF NOT EXISTS hypopg;"

PostgreSQL Configuration

Key settings in the command arguments:

Setting	Value	Purpose
shared_buffers	1GB	Memory for caching data
work_mem	128MB	Memory per query operation
maintenance_work_mem	512MB	Memory for VACUUM, CREATE INDEX
shared_preload_libraries	pg_stat_statements	Required for query stats
pg_stat_statements.track	all	Track all statements

Image Caching

The image is tagged as heatwave-postgres:18-noble. Docker caches the built image, so subsequent docker compose up commands are fast. The image only rebuilds when the Dockerfile changes.

Updating PostgreSQL Version

To upgrade to a newer PostgreSQL version (the live 16→18 upgrade used an in-place pg_upgrade --link; see doc/tasks/202606061949_PG18_UPGRADE_STRATEGY.md). The dump-and-restore path below stays valid for a clean rebuild:

1. Backup data:

   docker compose exec pg pg_dumpall -U postgres > backup.sql

2. Update the build arg / Dockerfile:

   docker-compose.yml

   build:
     args:
       PG_MAJOR: "19"  # Change version

3. Update volume path in docker-compose.yml

4. Rebuild and restore:

   docker compose down pg
   rm -rf "$HOME/Projects/heatwave-infra/postgres/18/data"
   docker compose build pg
   docker compose up -d pg
   cat backup.sql | docker compose exec -T pg psql -U postgres

See Also

• PgHero Integration