Issue Description

I'm attempting to install several postgreSQL extension on an alphine container. I'm using apk since there are packages that exist but when I'm attempting to load the extensions I'm hitting an error that says the extensions don't exist.

I am copying the .control and .sql files to the directory that I think is the correct extensions directory (/usr/local/share/postgresql/extension/) but if I was doing this 100% correctly I wouldn't e asking this question. =P

I would love to know why I'm not able to load pg_cron in particular for this example and hopefully that'll let me use any other extensions loaded through apk going forward.

Error Message

data-pipeline-db-1  | 2024-02-22 00:58:00.127 UTC [37] LOG:  database system is ready to accept connections                                                                                                     
data-pipeline-db-1  |  done 
data-pipeline-db-1  | server started
data-pipeline-db-1  | CREATE DATABASE 
data-pipeline-db-1  |
data-pipeline-db-1  |  
data-pipeline-db-1  | /usr/local/bin/docker-entrypoint.sh: sourcing /docker-entrypoint-initdb.d/001-create-db.sh 
data-pipeline-db-1  | CREATE DATABASE 
data-pipeline-db-1  | CREATE ROLE
data-pipeline-db-1  | GRANT
data-pipeline-db-1  |
data-pipeline-db-1  | /usr/local/bin/docker-entrypoint.sh: sourcing /docker-entrypoint-initdb.d/002-setup.sh
data-pipeline-db-1  | waiting for server to shut down...2024-02-22 00:58:00.584 UTC [37] LOG:  received fast shutdown request
data-pipeline-db-1  | .2024-02-22 00:58:00.584 UTC [37] LOG:  aborting any active transactions
data-pipeline-db-1  | PostgreSQL Database directory appears to contain a database; Skipping initialization
data-pipeline-db-1  | 
data-pipeline-db-1  | 2024-02-22 00:58:02.475 UTC [1] FATAL:  could not access file "pg_cron": No such file or directory
data-pipeline-db-1  | 2024-02-22 00:58:02.475 UTC [1] LOG:  database system is shut down

Files Required To Reproduce Issue

Dockerfile

FROM postgres:16.2-alpine

# These are special environment variables that are used by the postgreSQL init
# script. They'll create a DB and user based on whatever values they're populated
# with.
ENV POSTGRES_DB ""
ENV POSTGRES_USER ""
ENV POSTGRES_PASSWORD ""
ENV POSTGRES_CONF ""
ENV PATH="/usr/pgsql-16/bin:${PATH}"

# Variables needed at runtime to configure postgres and run the initdb scripts
ARG PG_PARTMAN_VERSION
ARG PG_CRON_VERSION
ARG PG_GIS_VERSION
ARG USER="data-pipeline"

RUN apk add --no-cache postgresql-pg_cron="${PG_CRON_VERSION}-r0" --repository=http://dl-cdn.alpinelinux.org/alpine/edge/community \
    && apk add --no-cache postgresql-pg_partman="${PG_PARTMAN_VERSION}-r0" --repository=http://dl-cdn.alpinelinux.org/alpine/edge/testing \
    && apk add --no-cache postgis="${PG_GIS_VERSION}-r0" --repository=http://dl-cdn.alpinelinux.org/alpine/edge/community \
    && cp /usr/share/postgresql/extension/* /usr/local/share/postgresql/extension/
# Tried to copy the .so file but that didn't solve my issue.
#RUN cp -r /usr/lib/postgresql16/ usr/local/lib/postgresql/


# Question: Why do we need this entry point if we've already installed the extensions?
# Answer: PostgreSQL needs to be running in order to install extensions and
#         we can't start PostgreSQL until after we're done building the container
#         So we're chosing to add scripts to run on container start to circumvent
#         the chicken or egg problem.
# Copy in the load-extensions script
COPY db/initdb_scripts /docker-entrypoint-initdb.d/

docker-compose.yml

version: '3'
services:
  data-pipeline-db:
    image: data-pipeline-db
    restart: always
    shm_size: 512mb
    build:
      context: ../
      dockerfile: ./docker/db/Dockerfile
      args:
          PG_CRON_VERSION: 1.6.2
          PG_PARTMAN_VERSION: 5.0.0
          PG_GIS_VERSION: 3.4.2
    ports:
      - "5432:5432"
    volumes:
      - ./data/pgsql:/var/lib/postgresql/data
    environment:
      - POSTGRES_DB=testing
      - POSTGRES_USER=data-pipeline
      - POSTGRES_PASSWORD=test

  data-pipeline-testing:
    image: data-pipeline-testing
    build:
      context: ../
      dockerfile: ./docker/testing/Dockerfile
    ports:
      - "8090:8090"

The content of db/initdb_scripts:

$ tree db/initdb_scripts
db/initdb_scripts
├── 001-create-db.sh
├── 002-setup.sh
├── 003-load-extensions.sql
└── README.md

001-create-db.sh

#!/bin/sh
set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    CREATE DATABASE "$POSTGRES_USER";
    CREATE USER new_user_name WITH ENCRYPTED PASSWORD '"$POSTGRES_PASSWORD"';
    GRANT ALL PRIVILEGES ON DATABASE "$POSTGRES_USER" TO "$POSTGRES_USER";
EOSQL

002-setup.sh

cat <<EOT >> /var/lib/postgresql/data/postgresql.conf
shared_preload_libraries='pg_cron'
cron.database_name='${POSTGRES_DB:-postgres}'
EOT

pg_ctl restart

003-load-extensions.sql

CREATE EXTENSION pg_cron;
SELECT * FROM pg_extension;

CREATE SCHEMA partman;
CREATE EXTENSION pg_partman SCHEMA partman;
select * FROM pg_extension;

Other Things I've Tried

• I tried to change the postgres.conf location from /var/lib/postgresql/data/postgresql.conf to /etc but this had no effect.
• I tried doing a cp /usr/lib/postgresql16/pg_cron.so /usr/local/lib/pg_cron/ given that $LIBDIR is in /usr/local/lib
• • I tried doing a cp /usr/lib/postgresql16/pg_cron.so /usr/local/lib/ instead of in pg_cron.

Here's a minimal setup which will resolve your problem. I believe that the crux is copying the files across from /usr/lib/ to /usr/local/lib/ and from /usr/share/ to /usr/local/share/.

For clarity I have stripped down the files to contain just what's required to get pg_cron loaded.

🗎 docker-compose.yml

version: '3'
services:
  data-pipeline-db:
    image: data-pipeline-db
    restart: always
    container_name: data-pipeline-db
    shm_size: 512mb
    build:
      context: .
      dockerfile: ./Dockerfile
    environment:
      - POSTGRES_DB=testing
      - POSTGRES_USER=data-pipeline
      - POSTGRES_PASSWORD=test

🗎 Dockerfile

FROM postgres:16.2-alpine

ENV POSTGRES_DB ""
ENV POSTGRES_USER ""
ENV POSTGRES_PASSWORD ""

RUN apk add --no-cache postgresql-pg_cron && \
    cp /usr/lib/postgresql16/pg_cron.so /usr/local/lib/postgresql/ && \
    cp /usr/share/postgresql16/extension/* /usr/local/share/postgresql/extension

COPY setup-pg-cron.sh /docker-entrypoint-initdb.d/

🗎 setup-pg-cron.sh

#!/bin/bash

cat <<EOT >> /var/lib/postgresql/data/postgresql.conf
shared_preload_libraries='pg_cron'
cron.database_name='postgres'
EOT

It made more sense to me to append the entry to /var/lib/postgresql/data/postgresql.conf at build time rather than initialisation time.