Docker部署PostgreSQL+pg_cron遇cron.database_name错误，已解决

问题已解决：需加载自定义postgresql.conf文件 #

我在Docker中搭建PostgreSQL 16.1容器并搭配pg_cron扩展时，启动出现以下错误：

2024-02-12 07:15:16.320 UTC [54] ERROR:  unrecognized configuration parameter "cron.database_name"
2024-02-12 07:15:16.320 UTC [54] CONTEXT:  PL/pgSQL function inline_code_block line 3 at IF
2024-02-12 07:15:16.320 UTC [54] STATEMENT:  CREATE EXTENSION pg_cron SCHEMA pg_catalog;
ERROR:  unrecognized configuration parameter "cron.database_name"
CONTEXT:  PL/pgSQL function inline_code_block line 3 at IF

我尝试过类似问题的解决方案，但均无效。dblink、pg_partman、pg_jobmon等其他扩展运行正常。

docker-compose.yaml #

services:
    edge-postgresql:
        build: ./postgres
        image: edge-postgres:16.1-alpine
        restart: always
        environment:
            POSTGRES_USER: ***
            POSTGRES_PASSWORD: ***
            PGDATA: /var/lib/postgresql/data
            POSTGRES_MULTIPLE_DATABASES: db_1,db_2
        volumes:
            - ./cfg-data/:/cfg-data/
            - edge-postgresql-data:/var/lib/postgresql/data
        ports:
            - "35432:5432"
        mem_limit: 2048m
        networks:
            - proxy-redirect

volumes:
    edge-postgresql-data:
        name: edge-postgresql-data

networks:
    proxy-redirect:
        name: proxy-redirect

Dockerfile #

########################################################################
#   POSTGRESQL
########################################################################

FROM postgres:alpine
USER root

# pg_jobmon
ENV PG_JOBMON_VERSION v1.4.1
# pg_partman
ENV PG_PARTMAN_VERSION v5.0.1
# pg_cron
ENV PG_CRON_VERSION v1.6.2

# Install pg_jobmon.
RUN set -ex \
    && apk add --no-cache --virtual .fetch-deps \
        ca-certificates \
        openssl \
        tar \
    && wget -O pg_jobmon.tar.gz "https://github.com/omniti-labs/pg_jobmon/archive/$PG_JOBMON_VERSION.tar.gz" \
    && mkdir -p /usr/src/pg_jobmon \
    && tar \
        --extract \
        --file pg_jobmon.tar.gz \
        --directory /usr/src/pg_jobmon \
        --strip-components 1 \
    && rm pg_jobmon.tar.gz \
    && apk add --no-cache --virtual .build-deps \
        autoconf \
        automake \
        g++ \
        clang15 \
        llvm15 \
        libtool \
        libxml2-dev \
        make \
        perl \
    && cd /usr/src/pg_jobmon \
    && make \
    && make install \
    && cd / \
    && rm -rf /usr/src/pg_jobmon

# Install pg_partman.
RUN set -ex \
    && wget -O pg_partman.tar.gz "https://github.com/pgpartman/pg_partman/archive/$PG_PARTMAN_VERSION.tar.gz" \
    && mkdir -p /usr/src/pg_partman \
    && tar \
        --extract \
        --file pg_partman.tar.gz \
        --directory /usr/src/pg_partman \
        --strip-components 1 \
    && rm pg_partman.tar.gz \
    && cd /usr/src/pg_partman \
    && make \
    && make install \
    && rm -rf /usr/src/pg_partman

# Install pg_cron.
RUN set -ex \
    && wget -O pg_cron.tar.gz "https://github.com/citusdata/pg_cron/archive/$PG_CRON_VERSION.tar.gz" \
    && mkdir -p /usr/src/pg_cron \
    && tar \
        --extract \
        --file pg_cron.tar.gz \
        --directory /usr/src/pg_cron \
        --strip-components 1 \
    && rm pg_cron.tar.gz \
    && cd /usr/src/pg_cron \
    && make \
    && make install \
    && rm -rf /usr/src/pg_cron \
    && apk del .fetch-deps .build-deps

RUN mkdir -p /docker-entrypoint-initdb.d && chown -R 70:70 /docker-entrypoint-initdb.d

USER postgres
COPY ./custom/postgresql.conf.sample /usr/local/share/postgresql/postgresql.conf.sample
COPY ./docker-entrypoint-initdb.d/ /docker-entrypoint-initdb.d/

docker-entrypoint-initdb.d/00-data.sh #

#! /bin/bash

set -e
set -u

## 创建并配置POSTGRES_MULTIPLE_DATABASES环境变量中定义的数据库
function create_user_and_database() {
    local db=$1 file=$2

    echo "Creating user and database '$db'."
    psql -v ON_ERROR_STOP=1 -U "$POSTGRES_USER" <<-EOSQL
        CREATE USER $db;
        CREATE DATABASE $db;
        GRANT ALL PRIVILEGES ON DATABASE $db TO $db;
EOSQL

    echo "Setting up database '$db' tables."
    psql -U "$POSTGRES_USER" -d $db -f './cfg-data/sql/'$file'.sql'
}

## 用pg_partman配置分区
function partitioning_management() {
    local db=$1

    echo "Creating pg_dblink extension."
    psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$db" <<-EOSQL
        CREATE SCHEMA dblink;
        CREATE EXTENSION dblink SCHEMA dblink;
EOSQL

    # 日志扩展
    echo "Creating pg_jobmon extension."
    psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$db" <<-EOSQL
        CREATE SCHEMA jobmon;
        CREATE EXTENSION pg_jobmon SCHEMA jobmon;
        INSERT INTO jobmon.dblink_mapping_jobmon (username, pwd) VALUES ('$POSTGRES_USER', '$POSTGRES_PASSWORD');
EOSQL

    echo "Creating pg_partman extension."
    psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$db" <<-EOSQL
        CREATE SCHEMA partman;
        CREATE EXTENSION pg_partman SCHEMA partman;
EOSQL

    echo "添加jobmon权限."
    psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$db" <<-EOSQL
        GRANT USAGE ON SCHEMA jobmon TO $POSTGRES_USER;
        GRANT USAGE ON SCHEMA dblink TO $POSTGRES_USER;
        GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA jobmon TO $POSTGRES_USER;
        GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA jobmon TO $POSTGRES_USER;
        GRANT ALL ON ALL SEQUENCES IN SCHEMA jobmon TO $POSTGRES_USER;
EOSQL
}

## 配置pg_cron
function pg_cron() {
    local db="postgres"

    echo "Creating pg_cron extension."
    psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$db" <<-EOSQL
        CREATE EXTENSION pg_cron;
EOSQL

    # 加载pg_cron需要重启服务
    pg_ctl restart
}

## 主逻辑
if [ -n "$POSTGRES_MULTIPLE_DATABASES" ]; then
    echo "Setting up pg_cron."
    pg_cron

    echo "Databases creation requested: $POSTGRES_MULTIPLE_DATABASES."

    for db in $(echo $POSTGRES_MULTIPLE_DATABASES | tr "," " "); do
        if [ $db == "db_1" ]; then
            create_user_and_database $db "db_1"
            partitioning_management $db "db_1"
        elif [ $db == "db_2" ]; then
            create_user_and_database $db "db_2"
            partitioning_management $db "db_2"
        fi
    done

    echo "Databases created."
fi

postgresql.conf.sample #

#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------

# 扩展配置项
pg_partman_bgw.interval = 3600
pg_partman_bgw.role = postgres
pg_partman_bgw.dbname = 'db_1,db_2'
cron.database_name = postgres

内容的提问来源于stack exchange，提问作者DrOutis