postgresqlgoogle-cloud-platformgoogle-cloud-sqlpg-cron

using pg_cron extension on Cloud SQL

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,

Solution

  • 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');