postgres

Postgres

This image is based on the official Postgres image, but with some additional extensions installed:

docker-compose.yml

services:
  postgres:
    build:
      dockerfile: Dockerfile.postgres
    ports:
      - '5432:5432'
    volumes:
      - $PWD/bootstrap/init-mydb-db.sh:/docker-entrypoint-initdb.d/init-mydb-db.sh
    environment:
      - POSTGRES_USER=admin
      - POSTGRES_PASSWORD=admin
      - POSTGRES_DB=admin
    restart: unless-stopped
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: 3s
      timeout: 3s
      retries: 10

Dockerfile.postgres

ARG PG_MAJOR=17

FROM postgres:$PG_MAJOR

# Install dependencies for extensions
RUN apt-get update && apt-get install -y \
    build-essential git libreadline-dev zlib1g-dev flex bison cmake \
    python3 python3-pip \
    jq pkg-config \
    rustc rustfmt \
    postgresql-server-dev-$PG_MAJOR \
    postgresql-plpython3-$PG_MAJOR \
    postgresql-$PG_MAJOR-pgvector \
    postgresql-$PG_MAJOR-cron \
    postgresql-$PG_MAJOR-postgis-3 \
    postgresql-$PG_MAJOR-h3 \
    postgresql-$PG_MAJOR-pgaudit

# https://github.com/timescale/timescaledb
RUN git clone --branch 2.25.0 https://github.com/timescale/timescaledb /tmp/timescaledb && \
		cd /tmp/timescaledb && \
		./bootstrap && \
		cd build && \
		make && \
		make install && \
		rm -r /tmp/timescaledb

# https://github.com/timescale/pg_textsearch
RUN git clone --branch v0.5.1 https://github.com/timescale/pg_textsearch /tmp/pg_textsearch && \
    cd /tmp/pg_textsearch && \
    make && \
    make install && \
    rm -r /tmp/pg_textsearch

# https://github.com/apache/age
RUN git clone --branch PG$PG_MAJOR/v1.7.0-rc0 https://github.com/apache/age /tmp/age && \
    cd /tmp/age && \
    make && \
    make install && \
    rm -r /tmp/age

# https://github.com/timescale/pgvectorscale
RUN git clone --branch 0.9.0 https://github.com/timescale/pgvectorscale /tmp/pgvectorscale && \
    cd /tmp/pgvectorscale/pgvectorscale && \
    cargo install --locked cargo-pgrx --version $(cargo metadata --format-version 1 | jq -r '.packages[] | select(.name == "pgrx") | .version') && \
    cargo pgrx init --pg$PG_MAJOR pg_config && \
    cargo pgrx install --release && \
    rm -r /tmp/pgvectorscale

# https://github.com/timescale/pgai
# RUN git clone --branch extension-0.11.2 https://github.com/timescale/pgai /tmp/pgai && \
#     cd /tmp/pgai && \
#     rm -r /tmp/pgai

# https://github.com/pgmq/pgmq
RUN git clone --branch v1.10.0 https://github.com/pgmq/pgmq /tmp/pgmq && \
    cd /tmp/pgmq/pgmq-extension && \
    make && \
    make install && \
    rm -r /tmp/pgmq

# RUN apt uninstall -y \
#     build-essential libreadline-dev zlib1g-dev flex bison \
#     git \
#     python3 python3-pip \
#     jq pkg-config \
#     rustc rustfmt \
#     apt-get autoremove -y && \
#     apt-mark unhold locales && \
#     rm -rf /var/lib/apt/lists/*

# Copy init scripts
COPY init-extensions.sql /docker-entrypoint-initdb.d/


CMD ["postgres", "-c", "shared_preload_libraries=timescaledb,age,pg_cron,pgaudit"]

bootstrap/init-mydb-db.sh

#!/bin/bash
set -e

DB_NAME=mydb
DB_USER=mydb
DB_PASSWORD=mydb

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
	CREATE USER $DB_USER with password '$DB_PASSWORD';
	CREATE DATABASE $DB_NAME;
	GRANT ALL PRIVILEGES ON DATABASE $DB_NAME TO $DB_USER;
	\c $DB_NAME
	GRANT ALL ON SCHEMA public TO $DB_USER;
EOSQL