Feature Request: pg_cron configurable OR provide an image without it

[magnusfiorepalm]

For our official CircleCI Docker Convenience Image support policy, please see CircleCI docs.

This policy outlines the release, update, and deprecation policy for CircleCI Docker Convenience Images.

---

Describe the Feature Request
Allow for a way to exclude the pg_cron shared library OR provide a new image without pg_cron.

See support ticket https://support.circleci.com/hc/en-us/requests/145723

Is your feature request related to a particular problem?
The current image comes with pg_cron configured out of the box. This means that the pg_cron scheduler is launched on boot which will hold on to a connection to any test database. This is not ideal, since it breaks any build pipeline depending on dropping a test database as part of any test setup code (this is very common in a Rails app, for example).

We are unable to upgrade our build pipeline to anything after PG14.8. I believe there was a fix to pg_cron for 14.9. We are currently trying to upgrade to PG15.5, with the same problem.

How will this feature request benefit CircleCI jobs using this image?
We and others will be able to upgrade to an image after PG14.8 if we have jobs relying to dropping a database before setting it up for tests etc.

Describe the solution you would like to see
I'd like an easy configuration setting in the config.yml file OR a new image without the pg_cron shared libraries installed.

Describe alternatives you have considered
Adding custom logic to the build pipeline that terminates the pg_cron scheduler. This does add a certain amount of tech debt in our build pipeline - but, more importantly, it doesn't actually work since the pg_cron scheduler restarts itself when the pid is terminated.

[MichaelRando]

same. https://support.circleci.com/hc/en-us/requests/145425

[magnusfiorepalm]

Any possibility this could be prioritized? We're currently not able to use this image because of this issue.

[mohammed-io]

A workaround would be to use a different POSTGRES_DB in your env than your test db name because pg_cron uses that variable to run the cron job https://github.com/CircleCI-Public/cimg-postgres/blob/main/pg_cron.sh#L3

That would be:

- image: cimg/postgres:X.Y.Z
      environment:
        POSTGRES_DB: noop # Let Rails create the DB
        ...

Now the cron will run on noop DB, not your <project>_test DB.

[stevenharman]

We just tripped over this footgun as well. Can we please get an image where we can turn pg_cron off? It holding open a connection to the specified POSTGRES_DB completely breaks pipelines for things like Rails which expect to drop and set up fresh DBs in each run. For now we've used the hack @mohammed-io suggested and it's working. But it doesn't make me happy.

EDIT: To clarify, by "it doesn't make me happy," I mean that this hackery is needed at all does not make me happy. A big thanks 🎉 to @mohammed-io for the hackery!

[MichaelRando]

I like our hack:

...
volumes:
- /dev/null:/docker-entrypoint-initdb.d/pg_cron.sh
...

[MichaelRando]

That's better, plz merge.