creating pg_cron extension within docker-entrypoint-initdb.d fails

pg_cron can be loaded only as shared library. You must specify it in postgres.conf file. Since all scripts in docker-entrypoint-init.d are executed after postgres server is started (with pg_ctl start), all changes to shared_preload_libraries in postgres.conf can become available after restart (with pg_ctl restart).

Real world example:

002-setup.sh:

#!/bin/sh# Remove last line "shared_preload_libraries='citus'"sed -i '$ d' ${PGDATA}/postgresql.confcat <<EOT >> ${PGDATA}/postgresql.confshared_preload_libraries='pg_cron,citus'cron.database_name='${POSTGRES_DB:-postgres}'EOT# Required to load pg_cronpg_ctl restart

003-main.sql:

CREATE EXTENSION pg_cron;

Notice:

1. script execution order matters and is ordered by file names
2. pg_cron becomes available in db specified with cron.database_name

You need to install & setup pg_cron extension.

I recommend you to use (or at least fork) this repository : ramazanpolat/postgres_cron: Dockerfile for building postgresql:11 with pg_cron extension

The proposed solution didn't work with a newly created container for me. So, I did it like this:

Docker file

FROM postgres:13.2RUN apt-get update && apt-get -y install git build-essential postgresql-server-dev-13RUN git clone https://github.com/citusdata/pg_cron.gitRUN cd pg_cron && make && make installRUN cd / && \        rm -rf /pg_cron && \        apt-get remove -y git build-essential postgresql-server-dev-13 && \        apt-get autoremove --purge -y && \        apt-get clean && \        apt-get purgeCOPY init-db /docker-entrypoint-initdb.d

init-db/002-pg-cron.sh

#!/usr/bin/env bash# use same db as the one from envdbname="$POSTGRES_DB"# create custom configcustomconf=/var/lib/postgresql/data/custom-conf.confecho "" > $customconfecho "shared_preload_libraries = 'pg_cron'" >> $customconfecho "cron.database_name = '$dbname'" >> $customconfchown postgres $customconfchgrp postgres $customconf# include custom config from main configconf=/var/lib/postgresql/data/postgresql.conffound=$(grep "include = '$customconf'" $conf)if [ -z "$found" ]; then  echo "include = '$customconf'" >> $conffi

Also, you can place other init files into init-db directory.

003-main.sql

CREATE EXTENSION pg_cron;

Docker compose file

version: '3.7'services:  postgres:    container_name: your-container    build: .    environment:      POSTGRES_DB: "your_db"      POSTGRES_USER: "your_user"      POSTGRES_PASSWORD: "your_user"    volumes:      - pgdata:/var/lib/postgresql/data    ports:      - "5432:5432"volumes:  pgdata:    driver: local