using pg_cron extension on Cloud SQL

Question

I am trying to use pg_cron to schedule calls on stored procedure on several DBs in a Postgres Cloud SQL instance.

Unfortunately it looks like pg_cron can only be only created on postgres DB

When I try to use pg_cron on a DB different than postgres I get this message :

CREATE EXTENSION pg_cron;
 
ERROR: can only create extension in database postgres
Detail: Jobs must be scheduled from the database configured in 
cron.database_name, since the pg_cron background worker reads job 
descriptions from this database. Hint: Add cron.database_name = 
'mydb' in postgresql.conf to use the current database. 
Where: PL/pgSQL function inline_code_block line 4 at RAISE 

Query = CREATE EXTENSION pg_cron;

... I don't think I have access to postgresql.conf in Cloud SQL ... is there another way ? Maybe I could use postgres_fdw to achieve my goal ?

Thank you,

Nutgall

Answer 1

There's no need to edit any files. All you have to do is set the cloudsql.enable_pg_cron flag (see guide) and then create the extension in the postgres database.

You need to log onto the postgres database rather than the one you're using for your app. For me that's just replacing the name of my app database with 'postgres' e.g.

psql -U<username> -h<host ip> -p<port> postgres

Then simply run the create extension command and the cron.job table appears. Here's one I did a few minutes ago in our cloudsql database. I'm using the cloudsql proxy to access the remote db:

127.0.0.1:2345 admin@postgres=> create extension pg_cron;
CREATE EXTENSION
Time: 268.376 ms
127.0.0.1:2345 admin@postgres=> select * from cron.job;
 jobid | schedule | command | nodename | nodeport | database | username | active | jobname 

-------+----------+---------+----------+----------+----------+----------+--------+---------
(0 rows)

Time: 157.447 ms

Be careful to specify the correct target database when setting the schedule otherwise it will think that you want the job to run in the postgres database. The documentation has this example (but it's easily missed)

-- Vacuum every Sunday at 4:00am (GMT) in a database other than the one pg_cron is installed in
SELECT cron.schedule_in_database('weekly-vacuum', '0 4 * * 0', 'VACUUM', 'some_other_database');

Binal

Thank you Chris, updating database and username in cron.job table in postgres DB allows me to call any stored procedure I want to schedule in any db in the instance. – Nutgall

Answer 2

.. I don't think I have access to postgresql.conf in Cloud SQL ...

Actually there is, you can use the patch command.

according to pg_cron doc, you need two change two things in the conf file:

1. shared_preload_libraries = 'pg_cron'
2. cron.database_name = 'another_table' #optionnaly to change the database where pg_cron background worker expects its metadata tables to be created

Now, according to gcloud

You need to set up two flags on your instance:

gcloud sql instances patch [my_instance] --database-flags=cloudsql.enable_pg_cron=on,cron.database_name=[my_name]

CAREFUL, don't use twice the command "patch" as you would erase your first setting. Put all your changes in one command

Haydeehayden

Answer 3

You also might want set cron.database_name in postgresql.conf (or flag in CloudSQL)

cron.database_name = mydatabase

Brice