postgres

Postgres

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

timescaledb (time series db)
pgvector (vector db)
apache age (graph db)
h3 (spatial indexing)

docker-compose.yml
services:
  postgres:
    build:
      dockerfile: Dockerfile.postgres
    ports:
      - '5432:5432'
    volumes:
      - $PWD/bootstrap/init-mydb-db.sh:/docker-entrypoint-initdb.d/init-keycloak-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=16

FROM postgres:$PG_MAJOR
ARG PG_MAJOR

RUN apt-get update && \
		apt-mark hold locales && \
		apt-get install -y --no-install-recommends git cmake flex bison build-essential ca-certificates postgresql-plpython3-$PG_MAJOR postgresql-server-dev-$PG_MAJOR

# CREATE EXTENSION vector;
RUN git clone https://github.com/pgvector/pgvector.git /tmp/pgvector && \
		cd /tmp/pgvector && \
		make clean && \
		make OPTFLAGS="" && \
		make install && \
		mkdir /usr/share/doc/pgvector && \
		cp LICENSE README.md /usr/share/doc/pgvector && \
        rm -r /tmp/pgvector

# CREATE EXTENSION age;
RUN git clone https://github.com/apache/incubator-age /tmp/age && \
        cd /tmp/age && \
        make && \
        make install && \
        rm -r /tmp/age

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

# CREATE EXTENSION h3;
RUN git clone https://github.com/zachasme/h3-pg /tmp/h3 && \
        cd /tmp/h3 && \
        cmake -B build -DCMAKE_BUILD_TYPE=Release && \
        cmake --build build && \
        cmake --install build --component h3-pg && \
        rm -r /tmp/h3

RUN apt-get remove -y build-essential git cmake flex bison build-essential postgresql-plpython3-$PG_MAJOR postgresql-server-dev-$PG_MAJOR && \
		apt-get autoremove -y && \
		apt-mark unhold locales && \
		rm -rf /var/lib/apt/lists/*

RUN echo "en_US.UTF-8 UTF-8" > /etc/locale.gen \
    && locale-gen \
    && update-locale LANG=en_US.UTF-8

ENV LANG=en_US.UTF-8
ENV LC_COLLATE=en_US.UTF-8
ENV LC_CTYPE=en_US.UTF-8

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

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
	CREATE EXTENSION postgis;
	GRANT ALL ON SCHEMA public TO $DB_USER;
EOSQL

Postgres​

Postgres