oorabona/postgres repository overview

⁠PostgreSQL Database Container

Production-ready PostgreSQL containers with multiple flavors optimized for different workloads: AI/RAG, analytics, or general purpose. Built on Alpine Linux with pre-compiled extensions.

⁠Verify this image

Every build ships a Sigstore-signed SBOM and a full Trivy scan — verify them yourself, no login required:

gh attestation verify oci://ghcr.io/oorabona/postgres:17-alpine --owner oorabona

Full walkthrough (SBOM payload, Trivy findings, multi-arch manifest inspection, upstream dependency tracking) → https://oorabona.github.io/docker-containers/verify-images/⁠

⁠Quick Start

# Base PostgreSQL (smallest image)
docker pull ghcr.io/oorabona/postgres:17-alpine

# With pgvector for AI/RAG applications
docker pull ghcr.io/oorabona/postgres:17-alpine-vector

# With analytics extensions
docker pull ghcr.io/oorabona/postgres:17-analytics-alpine

# With TimescaleDB for time-series data
docker pull ghcr.io/oorabona/postgres:17-timeseries-alpine

# With PostGIS for geospatial data
docker pull ghcr.io/oorabona/postgres:17-spatial-alpine

# With Citus for distributed PostgreSQL
docker pull ghcr.io/oorabona/postgres:17-distributed-alpine

# All extensions included
docker pull ghcr.io/oorabona/postgres:17-alpine-full

⁠Available Flavors

⁠Flavor Details

⁠Base (*-alpine)

Standard PostgreSQL with built-in extensions:

• pg_stat_statements - Query statistics
• pgcrypto - Cryptographic functions
• uuid-ossp - UUID generation
• btree_gin, btree_gist - Additional index types
• pg_trgm - Trigram matching for fuzzy search

⁠Vector (*-alpine-vector)

Includes base + pgvector for AI/ML workloads:

-- Store embeddings from OpenAI, Anthropic, etc.
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content TEXT,
    embedding vector(1536)  -- OpenAI ada-002 dimension
);

-- Create HNSW index for fast similarity search
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

-- Find similar documents
SELECT * FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 10;

⁠Analytics (*-analytics-alpine)

Includes base + extensions for data warehousing:

• pg_partman - Automatic partition management for time-series data
• hypopg - Hypothetical indexes for query planning
• pg_qualstats - Predicate statistics for index suggestions
• pg_buffercache - Buffer cache inspection
• pg_prewarm - Data preloading

-- Auto-partition a time-series table
SELECT partman.create_parent(
    p_parent_table := 'public.events',
    p_control := 'created_at',
    p_interval := 'daily'
);

-- Check hypothetical index benefit
SELECT hypopg_create_index('CREATE INDEX ON users(email)');
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

⁠Timeseries (*-timeseries-alpine)

Includes base + extensions for time-series workloads:

• TimescaleDB - High-performance time-series database
• pg_partman - Automatic partition management

-- Create a hypertable for time-series data
CREATE TABLE metrics (
    time TIMESTAMPTZ NOT NULL,
    device_id TEXT,
    temperature DOUBLE PRECISION
);

SELECT create_hypertable('metrics', by_range('time'));

-- Use time_bucket for aggregations
SELECT time_bucket('1 hour', time) AS bucket,
       device_id,
       AVG(temperature) AS avg_temp
FROM metrics
WHERE time > NOW() - INTERVAL '1 day'
GROUP BY bucket, device_id
ORDER BY bucket DESC;

⁠Spatial (*-spatial-alpine)

Includes base + PostGIS for geospatial workloads:

• PostGIS - Geospatial types, indexing, and functions
• pg_cron - Scheduled jobs
• pg_ivm - Incremental materialized views

CREATE EXTENSION postgis;

CREATE TABLE places (
    id SERIAL PRIMARY KEY,
    name TEXT,
    location GEOGRAPHY(POINT, 4326)
);

-- Find places within 10km
SELECT name, ST_Distance(location, ST_MakePoint(-73.99, 40.73)::geography) AS dist
FROM places
WHERE ST_DWithin(location, ST_MakePoint(-73.99, 40.73)::geography, 10000)
ORDER BY dist;

⁠Distributed (*-distributed-alpine)

Includes base + Citus for horizontal scaling:

• Citus - Distributed PostgreSQL for multi-node clusters

-- Create a distributed table
SELECT citus_set_coordinator_host('coordinator', 5432);
SELECT create_distributed_table('events', 'tenant_id');

-- Or create a reference table (replicated across nodes)
SELECT create_reference_table('config');

-- Queries are automatically distributed
SELECT tenant_id, COUNT(*)
FROM events
GROUP BY tenant_id;

⁠Full (*-alpine-full)

All extensions for development and testing. Includes everything from all other flavors (vector, analytics, timeseries, spatial, distributed).

⁠Supported Versions

⁠Image Tags

ghcr.io/oorabona/postgres:{version}-{flavor}-alpine

Examples:

• 17-alpine or 17-base-alpine - PG17 base
• 17-alpine-vector - PG17 with pgvector
• 16-analytics-alpine - PG16 with analytics extensions
• 17-alpine-full - PG17 with all extensions

⁠Usage

⁠Docker Compose

services:
  postgres:
    image: ghcr.io/oorabona/postgres:17-alpine-vector
    environment:
      POSTGRES_DB: myapp
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U myuser -d myapp"]
      interval: 30s
      timeout: 10s
      retries: 3

volumes:
  postgres_data:

⁠Docker Run

docker run -d \
  --name postgres \
  -e POSTGRES_DB=myapp \
  -e POSTGRES_USER=myuser \
  -e POSTGRES_PASSWORD=secret \
  -p 5432:5432 \
  -v postgres_data:/var/lib/postgresql/data \
  ghcr.io/oorabona/postgres:17-alpine-vector

⁠Building Locally

# Build a flavor through the supported generator path
./make build postgres --flavor vector

postgres/Dockerfile is a template, not a directly buildable Dockerfile. Alternatively, generate a flavor-specific Dockerfile first and build that generated file.

The generated file is not self-contained: it bind-mounts install-ext.sh from the build context, so the context must be postgres/. Building it with the repository root or an output directory as context fails on a source BuildKit cannot find, and the error names a cache key rather than the context. ./make build and the bake path both use postgres/ already.

docker build -f /path/to/generated.Dockerfile postgres/

⁠Configuration

⁠Environment Variables

⁠Initialization Scripts

Place .sql or .sh files in a volume mounted to /docker-entrypoint-initdb.d/:

volumes:
  - ./init:/docker-entrypoint-initdb.d

Scripts run alphabetically on first container start:

init/
├── 01-schema.sql
├── 02-seed-data.sql
└── 03-setup.sh

⁠Performance Tuning

For production workloads, consider these settings in postgresql.conf:

# Memory
shared_buffers = 256MB
effective_cache_size = 1GB
work_mem = 16MB
maintenance_work_mem = 128MB

# Write-Ahead Log
wal_buffers = 16MB
checkpoint_completion_target = 0.9
max_wal_size = 2GB

# Query Planner
random_page_cost = 1.1
effective_io_concurrency = 200
default_statistics_target = 100

⁠Extensions Reference

⁠Compiled Extensions

⁠Built-in Extensions

All flavors include these PostgreSQL contrib extensions:

• pg_stat_statements - Query performance statistics
• pgcrypto - Cryptographic functions
• uuid-ossp - UUID generation
• btree_gin / btree_gist - Additional index types
• pg_trgm - Fuzzy string matching

Analytics and full flavors also include:

• pg_buffercache - Shared buffer inspection
• pg_prewarm - Buffer cache preloading
• file_fdw / postgres_fdw - Foreign data wrappers (full only)

⁠TimescaleDB Version Retention

Why this exists. TimescaleDB uses a version-agnostic loader: on first backend access it dlopens the exact timescaledb-<version>.so recorded in the database's pg_extension catalog. If the image shipped only the current .so, a persisted database created on an older TimescaleDB version would become unstartable after an image upgrade (FATAL: could not access file "$libdir/timescaledb-<old>.so"). To prevent that, the timeseries and full flavors retain a window of TimescaleDB versions.

What is retained. The image ships up to version_set.retain_count (default 12) of the most-recent TimescaleDB versions per PostgreSQL major that successfully build for the Alpine/musl platform. A persisted database on any version within that window starts without any migration — PostgreSQL loads the .so matching the catalog version. Versions that fail to compile under musl are tolerated and excluded; they are recorded in the versionset artifact (.build-lineage/ext-timescaledb-pg<major>-versionset.json, excluded[]) and not shipped. The retain count is configurable via version_set.retain_count in postgres/extensions/config.yaml. The retained window is derived from the upstream timescale/timescaledb-ha support matrix, so it differs per PostgreSQL major (a TimescaleDB version that never supported a given PostgreSQL major is not retained for it):

The table shows the upstream support window per PostgreSQL major. The shipped image retains the 12 most-recent versions from that window (configurable via version_set.retain_count in postgres/extensions/config.yaml). Databases on versions older than the retained window use ALTER EXTENSION timescaledb UPDATE to migrate to the ceiling.

The ceiling — currently 2.27.1 — is the pinned version in postgres/extensions/config.yaml; the floor and retained count track upstream and refresh automatically as new TimescaleDB versions are released and the pin is bumped.

The timescaledb.control file sets default_version to the pinned ceiling, so a fresh CREATE EXTENSION timescaledb installs the latest version.

How retained versions are shipped. All retained versions for a given PostgreSQL major are assembled into a single per-major bundle image (ghcr.io/<owner>/ext-timescaledb:pg<major>-bundle). The bundle stores each version's files under /<ver>/{extension,lib}/. The postgres Dockerfile consumes the bundle with one COPY --from=<bundle> / /tmp/ext/timescaledb/ instruction — one layer regardless of how many versions are retained (up to retain_count default 12). The install_ext shell function in postgres/install-ext.sh, which the Dockerfile bind-mounts and sources at build time, iterates /tmp/ext/timescaledb/ subdirectories and installs each version's .so; the ceiling version's .control file is written last so default_version reflects the pinned ceiling. To move an existing database to a newer version, run ALTER EXTENSION timescaledb UPDATE; (see below).

⁠Moving an existing database to the pinned version

To upgrade TimescaleDB in a persisted database (e.g. after pulling an image with a newer ceiling), connect to each database that has TimescaleDB installed and run:

ALTER EXTENSION timescaledb UPDATE;

The migration SQL for all retained versions is bundled in the image. The database continues to start on the old .so until you run this command — no forced migration on boot.

⁠Security

⁠Credential Management

Never hardcode passwords:

# BAD
environment:
  POSTGRES_PASSWORD: mysecretpassword

# GOOD - Environment variable
environment:
  POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}

# BETTER - Docker secrets
secrets:
  postgres_password:
    file: ./secrets/postgres_password.txt

⁠Runtime Hardening

services:
  postgres:
    image: ghcr.io/oorabona/postgres:17-alpine
    read_only: true
    tmpfs:
      - /tmp
      - /run/postgresql
    cap_drop:
      - ALL
    cap_add:
      - CHOWN
      - SETGID
      - SETUID
      - DAC_OVERRIDE
    security_opt:
      - no-new-privileges:true
    ports:
      - "127.0.0.1:5432:5432"  # Local only

⁠Network Security

• Bind to 127.0.0.1 for local-only access
• Use Docker networks for service communication
• Enable SSL for remote connections

⁠Monitoring

⁠Health Check

# Check if PostgreSQL is ready
docker exec postgres pg_isready -U myuser -d myapp

# Connection test
docker exec postgres psql -U myuser -d myapp -c "SELECT 1"

⁠Query Statistics

-- Enable pg_stat_statements (already enabled in all flavors)
-- Top 10 slowest queries
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

⁠Buffer Cache Analysis (analytics/full)

-- Buffer cache usage by table
SELECT c.relname,
       count(*) AS buffers,
       pg_size_pretty(count(*) * 8192) AS size
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = c.relfilenode
GROUP BY c.relname
ORDER BY buffers DESC
LIMIT 10;

⁠Backup & Restore

⁠Create Backup

# SQL dump
docker exec postgres pg_dump -U myuser myapp > backup.sql

# Binary backup (faster for large DBs)
docker exec postgres pg_basebackup -U myuser -D /backup -Ft -z

⁠Restore

# From SQL dump
docker exec -i postgres psql -U myuser -d myapp < backup.sql

# Create fresh database from backup
docker exec postgres createdb -U myuser myapp_restored
docker exec -i postgres psql -U myuser -d myapp_restored < backup.sql

⁠Version Management

# Check current version
./version.sh

# Check latest upstream version
./version.sh latest

# Output format (JSON for CI integration)
./version.sh --json

⁠Architecture

postgres/
├── Dockerfile              # Multi-flavor build
├── variants.yaml           # Version/flavor matrix
├── extensions/
│   ├── config.yaml         # Extension definitions
│   ├── build/              # Build scripts per extension
│   └── artifacts/          # Compiled extension tarballs
├── flavors/
│   ├── base.yaml           # Base flavor config
│   ├── vector.yaml         # Vector flavor config
│   ├── analytics.yaml      # Analytics flavor config
│   ├── timeseries.yaml     # Timeseries flavor config
│   ├── spatial.yaml        # Spatial flavor config
│   ├── distributed.yaml    # Distributed flavor config
│   └── full.yaml           # Full flavor config
└── custom-init/            # Custom initialization scripts

⁠Creating Custom Flavors

You can create your own flavor by combining extensions to match your specific needs.

A flavour is declared in two places, and both are required. extensions/config.yaml is what the build reads: its flavors section lists which compiled extensions the image assembles, and generation fails if the flavour is missing there. The file in flavors/ carries the flavour's description and is what the cache-digest path uses to recognise a postgres-style build.

⁠Step 1: Declare the extensions the flavour assembles

In extensions/config.yaml, under flavors, add the flavour and the extensions it includes. Each name must be an extension declared in the same file's extensions section, listed once:

flavors:
  myapp:
    - pgvector
    - pg_partman

⁠Step 2: Create the Flavor Definition

Create a new file in flavors/:

# flavors/myapp.yaml
name: myapp
description: "Custom flavor for my application"
extends: base

# Select which compiled extensions to include
extensions:
  - pgvector      # For AI features
  - pg_partman    # For time-series data

# Additional built-in extensions
builtin_extensions:
  - pg_buffercache

# Extensions requiring shared_preload_libraries
shared_preload_libraries: []

# Image tags to publish
tags:
  - "{version}-myapp-alpine"
  - "{major}-myapp-alpine"

⁠Step 3: Update variants.yaml

Add your variant to the version matrix:

# variants.yaml
versions:
  - tag: "17"
    variants:
      # ... existing variants ...
      - name: myapp
        suffix: "-myapp"
        flavor: myapp
        description: "Custom flavor for my application"

⁠Step 4: Declare Initdb Policy

Every compiled extension needs an explicit initdb.mode in config.yaml:

extensions:
  myext:
    initdb:
      mode: create
      # sql_name: my_extension_name  # optional; defaults to the config key

Use mode: manual with a reason when creating the extension cannot be done reliably during initdb. Flavor installs and 01-init-flavor.sql are generated from the same filtered flavor list.

⁠Step 5: Build Your Flavor

# Build locally through the supported generator path
./make build postgres --flavor myapp

# Test it
docker run -d --name pg-test \
  -e POSTGRES_PASSWORD=test \
  postgres:17-myapp

# Verify extensions
docker exec pg-test psql -U postgres -c "\dx"

⁠Adding New Extensions

To add an extension not yet supported:

1. Create build script in extensions/build/:

   # extensions/build/myext.sh
   #!/bin/bash
   git clone https://github.com/org/myext.git
   cd myext
   make USE_PGXS=1
   make USE_PGXS=1 install DESTDIR=/output
   

   Copy

2. Add to config.yaml:

   extensions:
     myext:
       version: "1.0.0"
       description: "My custom extension"
       repo: "org/myext"
       build_deps:
         - build-base
       shared_preload: false
   

   Copy

3. Build the extension:

   ./scripts/build-extensions.sh postgres myext
   

   Copy

4. Reference in Dockerfile using COPY --from=

⁠Roadmap

⁠Planned Extensions

(none currently — all target extensions have been integrated)

⁠Future Improvements

• PostgreSQL 18 extension support (once extensions are updated)
• ARM64 optimized builds (native CI runners)
• pg_stat_monitor integration