Adding pg_cron to Alpine Linux docker image based on PostgreSQL 14.6

[espadarticoDBA]

I'm trying to add the pg_cron extension to a docker image based on Alpine Linux with PostgreSQL 14.6 (14.6-alpine). When I try to create the extension inside PgSQL I keep getting the following error:

postgres=# create extension pg_cron;
ERROR:  unrecognized configuration parameter "cron.database_name"
CONTEXT:  PL/pgSQL function inline_code_block line 3 at IF

I've test using pg_cron version 1.4.0 and latest one, all with the same issue.

The steps I'm doing are:

export PG_CRON_VERSION=1.4.0

wget -O /var/lib/postgresql/pg_cron.tgz https://github.com/citusdata/pg_cron/archive/v$PG_CRON_VERSION.tar.gz
tar xvzf /var/lib/postgresql/pg_cron.tgz

cd pg_cron-$PG_CRON_VERSION

sed -i.bak -e 's/-Werror//g' Makefile
sed -i.bak -e 's/-Wno-implicit-fallthrough//g' Makefile

export PATH=/usr/bin:$PATH

make && sudo PATH=$PATH make install

cp /usr/lib/postgresql15/pg_cron.so /usr/local/lib/postgresql/
cp /usr/share/postgresql15/extension/* /usr/local/share/postgresql/extension

Any recommendations on how to overcome this?

[marcocitus]

pg_cron needs to be added to shared_preload_libraries before you can create the extension

[espadarticoDBA]

Adding that doesn't help I get the same issue and the container terminates.

Entry from postgresql.conf:

shared_preload_libraries = 'pg_cron'    # (change requires restart)

Container log:

2023-04-06 15:15:09 2023-04-06 14:15:09.634 UTC [258] ERROR:  unrecognized configuration parameter "cron.database_name"
2023-04-06 15:15:09 2023-04-06 14:15:09.634 UTC [258] CONTEXT:  PL/pgSQL function inline_code_block line 3 at IF
2023-04-06 15:15:09 2023-04-06 14:15:09.634 UTC [258] STATEMENT:  create extension pg_cron;
2023-04-06 15:25:09 2023-04-06 14:25:09.266 UTC [270] ERROR:  unrecognized configuration parameter "cron.database"
2023-04-06 15:25:09 2023-04-06 14:25:09.266 UTC [270] STATEMENT:  alter system set cron.database = 'postgres';
2023-04-06 15:26:57 2023-04-06 14:26:57.045 UTC [270] ERROR:  relation "pg_catalog.current_setting" does not exist at character 15
2023-04-06 15:26:57 2023-04-06 14:26:57.045 UTC [270] STATEMENT:  select * from pg_catalog.current_setting;
2023-04-06 15:27:08 2023-04-06 14:27:08.117 UTC [270] ERROR:  unrecognized configuration parameter "cron.database"

[marcocitus]

There might be some information in https://github.com/citusdata/pg_cron/issues/17

[espadarticoDBA]

To me it seems there is an issue with Postgres 14.6 latest Alpine image, which is base on Alpine 3.17. The pg_cron extension gets installed into postgres15 folder. I was able to make it work on an image based on Alpine 3.16. Here is the Dockerfile I've used in case someone is looking for the same:

FROM postgres:14.6-alpine3.16

ENV PERL_MM_USE_DEFAULT=1
ENV PG_CRON_VERSION=1.4.0

RUN apk add --no-cache --update \
        build-base \
        curl \
        expat \
        expat-dev \
        git \
        gnupg \
        heirloom-mailx \
        llvm \
        llvm-dev \
        make \
        perl \
        perl-app-cpanminus \
        perl-dev \
        perl-dbd-pg \
        perl-dbi \
        perl-xml-libxml \
        perl-xml-parser \
        perl-xml-simple \
        postgresql-client \
        postgresql-dev \
        sudo \
        wget

RUN     rm -rf /var/cache/apk/* /tmp/*

RUN     cpan -i Module::Signature \
                TAP::Parser::SourceHandler::pgTAP \
                XML::SAX::Expat \ 
# install pg_prove
RUN cpanm TAP::Parser::SourceHandler::pgTAP
RUN cpanm Test::Deep
RUN cpanm TAP::Harness::JUnit

WORKDIR /var/lib/postgresql

# install pgtap
RUN git clone https://github.com/theory/pgtap.git \
    && cd pgtap \
    && make \
    && make install \
    && make clean

# install pg_partman
RUN git clone https://github.com/pgpartman/pg_partman.git \
    && cd pg_partman \
    && make NO_BGW=1 install

# install pg_cron
RUN wget -O /var/lib/postgresql/pg_cron.tgz https://github.com/citusdata/pg_cron/archive/v$PG_CRON_VERSION.tar.gz \
    && tar xvzf /var/lib/postgresql/pg_cron.tgz \
    && cd pg_cron-$PG_CRON_VERSION \
    && sed -i.bak -e 's/-Werror//g' Makefile \
    && sed -i.bak -e 's/-Wno-implicit-fallthrough//g' Makefile \
    && export PATH=/usr/bin:$PATH \
    && make \
    && sudo PATH=$PATH make install \
    && cp /usr/lib/postgresql14/pg_cron.so /usr/local/lib/postgresql/ \
    && cp /usr/share/postgresql14/extension/* /usr/local/share/postgresql/extension

CMD ["-c", "shared_preload_libraries=pg_cron", "-c", "cron.database_name=postgres"]

RUN mkdir ./tests

NOTE: I've used pg_cron 1.4.0 because that's the version that Aurora PostgreSQL 14.6 uses and due to the information on #17.