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

Question

If I create the pg_cron extension in a docker-entrypoint-initdb.d/init.sql file, the docker image fails to run and docker logs <id> just says "No such container." Here's the relevant .sql snippet:

CREATE DATABASE my_database;
\c my_database;
CREATE EXTENSION IF NOT EXISTS postgis CASCADE;
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
CREATE EXTENSION IF NOT EXISTS pg_cron CASCADE;

However, if I create the pg_cron extension after the docker run command completes (i.e. remove the last line above and run it separately with psql --file after docker run completes), the extension gets created successfully (postgis and timescaledb extensions seem to be fine regardless).

Is there a reason I can't create the pg_cron extension from docker-entrypoint-init.d? Is there a correct place?

My docker run command is as follows:

 docker run -d \
        --name my_container --rm \
        -p 5432:5432 \
        -clog_line_prefix="%m [%p]: [%l-1] %u@%d" \
        -clog_error_verbosity=VERBOSE \
        -cshared_preload_libraries='timescaledb,pg_cron' \
        -ccron.database_name='my_database'

score 5 · Answer 1 · answered Aug 11 '18 at 07:36

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.conf

cat <<EOT >> ${PGDATA}/postgresql.conf
shared_preload_libraries='pg_cron,citus'
cron.database_name='${POSTGRES_DB:-postgres}'
EOT

# Required to load pg_cron
pg_ctl restart

003-main.sql:

CREATE EXTENSION pg_cron;

Notice:

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

This is not enough, you actually need to install the extension prior to reference it in PostgreSQL configuration — Natalie Perret, May 14 '20 at 21:34

score 2 · Answer 2 · answered Oct 08 '21 at 11:59

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

Docker file

FROM postgres:13.2

RUN apt-get update && apt-get -y install git build-essential postgresql-server-dev-13

RUN git clone https://github.com/citusdata/pg_cron.git
RUN cd pg_cron && make && make install

RUN 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 purge

COPY init-db /docker-entrypoint-initdb.d

init-db/002-pg-cron.sh

#!/usr/bin/env bash

# use same db as the one from env
dbname="$POSTGRES_DB"

# create custom config
customconf=/var/lib/postgresql/data/custom-conf.conf
echo "" > $customconf
echo "shared_preload_libraries = 'pg_cron'" >> $customconf
echo "cron.database_name = '$dbname'" >> $customconf
chown postgres $customconf
chgrp postgres $customconf

# include custom config from main config
conf=/var/lib/postgresql/data/postgresql.conf
found=$(grep "include = '$customconf'" $conf)
if [ -z "$found" ]; then
  echo "include = '$customconf'" >> $conf
fi

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

Hi Petro, How 002-pg-cron.sh file will be executed in the above steps? I was expecting some ENTRYPOINT ["init-db/002-pg-cron.sh"] at the end of the DockerFile. Am I missing something? — Rizwan, Apr 05 '22 at 13:09
Hi @Rizwan. Please pay attention to the last row of the Dockerfile. The [official postgres docker image](https://hub.docker.com/_/postgres/) will run `.sql` and `.sh` scripts found in the `/docker-entrypoint-initdb.d/` folder. — Petro Prydorozhnyi, Apr 06 '22 at 14:11
Thank you Petro for your message and clarification. This now makes complete sense to me. — Rizwan, Apr 08 '22 at 11:21
In your solution is missing `pg_ctl restart` at the end of the `002-pg-cron.sh file` and with that everything is running smoothly. — ontananza, Aug 27 '23 at 05:51

score 0 · Answer 3 · answered Oct 14 '20 at 14:50

0

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

answered Oct 14 '20 at 14:50

alexandre-rousseau

2,321
26
33

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

3 Answers3

Linked