Installing and using pg_cron extension on Postgres running inside of Docker container

Question

I tried installing pg_cron on Postgres running inside a Docker container but getting this error could not access file "pg_cron": No such file or directory. Any ideas on how to resolve?

Based on https://stackoverflow.com/a/51797554, I tried the following:

docker-compose.yml

version: '3.7'
services:
  pg:
    container_name: pg-container
    image: postgres:11.5
    environment:
      POSTGRES_DB: "pgdb"
      POSTGRES_USER: "pguser"
      POSTGRES_PASSWORD: "pgpass"
    volumes:
      - ./:/docker-entrypoint-initdb.d
      - pgstorage
    ports:
      - "5432:5432"

volumes:
  pgstorage: 

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'
cron.database_name='${POSTGRES_DB:-postgres}'
EOT
# Required to load pg_cron
pg_ctl restart 

003-main.sql

CREATE EXTENSION pg_cron; 

Interlay

Answer 1

From what I can see you are not installing pg_cron anywhere. Since it is not packaged with the default Postgres Docker image you will have to care of that.

For example by extending the Image and using a build entry in your docker-compose.yml.

# Dockerfile relative to docker-compose.yml

FROM postgres:11.5

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

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

version: '3.7'
services:
  pg:
    container_name: pg-container
    build: .
    environment:
      POSTGRES_DB: "pgdb"
      POSTGRES_USER: "pguser"
      POSTGRES_PASSWORD: "pgpass"
    volumes:
      - ./:/docker-entrypoint-initdb.d
    ports:
      - "5432:5432"

In newer versions of Postgres - you can install the pg_cron extension with apt-get.

FROM postgres:16-bookworm
RUN apt-get update &&  \ 
  apt-get -y install postgresql-16-cron && \ 
  apt-get clean \ 
  && rm -rf /var/lib/apt/lists/*

This will take care of the "installation" part. It is also necessary to configure pg_cron and finally create the extension.

# add to postgresql.conf

# required to load pg_cron background worker on start-up
shared_preload_libraries = 'pg_cron'

# optionally, specify the database in which the pg_cron background worker should run (defaults to postgres)
cron.database_name = 'postgres'

Alternatively, with Docker Compose these configuration values can also be passed as a command entry:

services:
  db:
    build: .
    command:
      [
        "postgres",
        "-c",
        "log_statement=all",
        "-c",
        "log_destination=stderr",
        "-c",
        "shared_preload_libraries=pg_cron",
        "-c",
        "cron.database_name=${POSTGRES_DB-postgres}",
      ]

Finally, you will have to create the extension (in a migration script, init script or else).

-- run as superuser:
CREATE EXTENSION pg_cron;

-- optionally, grant usage to regular users:
GRANT USAGE ON SCHEMA cron TO marco;

Loading the extension and activating it (create extension...) can be done in multiple ways in Docker: https://hub.docker.com/_/postgres/

This worked for me - it probably needs some more optimization.

Bargeman

This solution isn't working for me, unfortunately. Any hints? #77980410 – Geocentric

Not working for me as well, something has changed recently because it was working before using this method – Confiteor

This worked for me. It required one following through as stated. One issue might be the DB it's being used on: postgres or the DB you created. Pay close attention – Sloth

Answer 2

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

Docker file

FROM postgres:11.5

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

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-11 && \
        apt-get autoremove --purge -y && \
        apt-get clean && \
        apt-get purge

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

init-db/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.

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

Cumquat

Answer 3

For postgres 15.4 you can just do:

FROM postgres:15.4
RUN apt-get update && apt-get install -y curl


RUN apt-get -y install postgresql-15-cron
RUN echo "shared_preload_libraries='pg_cron'" >> /usr/share/postgresql/postgresql.conf.sample
RUN echo "cron.database_name='your_db_name'" >> /usr/share/postgresql/postgresql.conf.sample

And after that you can create

Wilterdink

Answer 4

Nothing above worked for me. Used following procedure in the end:

git clone https://github.com/ramazanpolat/postgres_cron.git
cd postgres_cron

In this directory edit Dockerfile to this:

    FROM postgres:15.1
LABEL MAINTAINER Michael Spitzer <[email protected]>

ENV PG_CRON_VERSION "1.4.2"

RUN apt-get update && apt-get install -y --no-install-recommends \
    postgresql-server-dev-15 postgresql-contrib-15 \
    libcurl4-openssl-dev \
    wget jq cmake build-essential ca-certificates && \
    mkdir /build && \
    cd /build && \
    wget https://github.com/citusdata/pg_cron/archive/v$PG_CRON_VERSION.tar.gz && \
    tar xzvf v$PG_CRON_VERSION.tar.gz && \
    cd pg_cron-$PG_CRON_VERSION && \
    make && \
    make install && \
    cd / && \
    rm -rf /build && \
    apt-get remove -y wget jq cmake build-essential ca-certificates && \
    apt-get autoremove --purge -y && \
    apt-get clean && \
    apt-get purge && \
    rm -rf /var/lib/apt/lists/*

RUN echo "shared_preload_libraries = 'pg_cron'" >> /var/lib/postgresql/data/postgresql.conf
RUN echo "cron.database_name = '${PG_CRON_DB:-pg_cron}'" >> /var/lib/postgresql/data/postgresql.conf

COPY ./docker-entrypoint.sh /usr/local/bin/

RUN chmod a+x /usr/local/bin/docker-entrypoint.sh

ENTRYPOINT ["docker-entrypoint.sh"]

EXPOSE 5432
CMD ["postgres"]

After that

docker build -t postgres_cron:15 .

Run it:

docker run --name postgresql -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=YourPassword -p 5432:5432 -v /data:/var/lib/postgresql/data -d postgres_cron:15

Replace YourPassword with your password.

Python