how to modify postgresql.conf for pg_cron extension

[dududko]

I need to configure pg_cron extension in a non default database. To do this I need to set the property cron.database_name in a postgres.conf file. Can anyone please explain how to do this ? I did not find the solution both here and in spilo/patroni repositories.

[CyberDem0n]

You don't need to modify anything, pg_cron allows scheduling jobs in arbitrary databases.

[dududko]

Thank you for the link! Is it considered to be the best practice to interact with pg_cron in postgres-operator ?

[dududko]

In case you will need to schedule a job that works with a different database, you have to specify the path to UNIX socket it the nodename. Otherwise you will have to configure .pgpass as the documentation suggest to do.

Here is the example:

INSERT INTO cron.job (schedule, command, nodename, nodeport, database, username)
VALUES ('*/1 * * * *', '
DO $$
BEGIN
    delete from table_one;
    delete from table_two;
    delete from table_three;
END;
$$;', 
 '/run/postgresql', 5432, 'my_dataabse', 'postgres');

[bhanotjyoti]

@dududko : I have this. It installs the pg_cron in postgres db but does not execute it in arbitrary db.

apiVersion: "acid.zalan.do/v1"
kind: postgresql
metadata:
  name: {{ include "app.name" . }}
  namespace: {{ .Values.namespace }}
  labels:
    {{- include "app.labels" . | nindent 4 }}
spec:
  patroni:
    pg_hba:
      - local   all             all                                   trust
      - host    all             all                127.0.0.1/32       trust
      - host    all             all                ::1/128            trust
      - local   replication     standby                    trust
      - hostssl replication     standby all                md5
      - hostnossl all           all                all                trust
      - hostssl all             +zalandos    all                pam
      - hostssl all             all                all                md5
  {{- with .Values.databases }}
  databases:
  {{ toYaml . | indent 2 }}
  {{- end }}
  numberOfInstances: {{ .Values.replicas }}
  postgresql:
    parameters:
      shared_preload_libraries: pg_cron
    version: "15"