gcp cloudsql postgres pg_cron - ghdrako/doc_snipets GitHub Wiki

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. I'm using the cloudsql proxy to access the remote db:

127.0.0.1:2345 admin@postgres=> SELECT current_user, current_database();  --sprawdzic w jakiej bazie sie jest !!!
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');

Terafform

database-flags = {             
                    ...
                    "cloudsql.enable_pg_cron"="on"
                    "cron.database_name"="postgres"
                 }

"cron.database_name"="postgres" - ustawienie bazy danych na ktorej beda harmonogramy. Create etension trzeba zrobic z tej bazy jako jej wlasciciel czyli jesli bazas postgres to wlasciciel postgres. jest to default bo harmonogramy moga sie tyczyc wielu roznych baz w ramach instancji

name type min support vesrsion Restart required
cron.database_name String Pg10 Yes
cron.log_statement boolean on | off The default is on. Pg10 Yes
cron.log_run boolean on | off The default is on. Pg10 Yes
cron.max_running_jobs Integer 0 ... varies The default is 5. Pg10 Yes
cron.log_min_messages String debug5|debug4|debug3|debug2|debug1|debug| info|notice|warning|error|log|fatal|panic The default is warning. Pg10 No 
-- Uwaga czas UTF czyli -1h lub -2h zeby miec cz\as srodkowoeuropejski
SELECT cron.unschedule('daily-analyze' );
SELECT cron.schedule_in_database(
  job_name:='inwentura ohio',
  schedule:='50 11 * * *',    
  command:='call ohio.gen_dinw_day(current_date-1)',
  database:='ebkdb',
  username:='ebkadm'
);

Jeśli tabela cron.job_run_details jest pusta, zwykle trzeba włączyć: `` SET cron.log_run = on;

albo parametr instancji:

cron.log_run = on

(w Cloud SQL czasem przez flags instancji).
⚠️ **GitHub.com Fallback** ⚠️