citusdata/pg_cron

Problem when installing pg_cron. Can you help me fix my Dockerfile?

ember11498 opened this issue · 4 comments

I know this is a very common error when installing pg_cron but I cannot make it work even after reading.

i get this error when i run "CREATE EXTENSION pg_cron;"

ERROR: unrecognized configuration parameter "cron.database_name"
CONTEXT: PL/pgSQL function inline_code_block line 3 at IF

SQL state: 42704

my Dockerfile looks like this (i created an image of postgres with postgis, pgvector and cron all extensions work bu cron).

`
FROM postgres:15

ENV POSTGRES_DB mydatabase
ENV POSTGRES_USER myuser
ENV POSTGRES_PASSWORD mypassword

# Update package lists
RUN apt-get update

# Install PostgreSQL extensions
RUN apt-get install -y --no-install-recommends \
    postgresql-contrib \
    postgresql-15-pgvector \
    postgresql-15-cron \
    postgresql-15-postgis

# Create a custom configuration file
RUN mkdir -p /var/lib/postgresql/data/conf.d \
    && echo "shared_preload_libraries = 'pg_cron'" > /var/lib/postgresql/data/conf.d/pg_cron.conf \
    && echo "cron.database_name = 'mydatabase'" >> /var/lib/postgresql/data/conf.d/pg_cron.conf \
    && chown -R postgres:postgres /var/lib/postgresql/data/conf.d

# Include the custom configuration file
RUN echo "include_dir = '/var/lib/postgresql/data/conf.d'" >> /var/lib/postgresql/data/postgresql.conf

# Clean up
RUN apt-get clean && rm -rf /var/lib/apt/lists/*

# Expose the PostgreSQL port
EXPOSE 5432

`

im working on the same problem

Me too.

Please check if your shared_preload_libraries = 'pg_cron' in your pg_cron.conf is created correctly. I did run into the same error because of a mistake in my Dockerfile where it didn't append to the postgresql.conf correctly and therefore the postgres couldn't load the extension

I fixed this via docker-compose.yaml:

  db:
    container_name: postgres
    build:
      dockerfile: Dockerfile
    restart: always
    ports:
      - "5432:5432"
    volumes:
      - postgresdata01:/var/lib/postgresql/data
    command:
      [
        "postgres",
        "-c",
        "log_statement=all",
        "-c",
        "log_destination=stderr",
        "-c",
        "shared_preload_libraries=pg_cron",
        "-c",
        "cron.database_name=<DB_NAME>",
      ]