How to use pg_cron extension in multiple databases in azure flexible postgres Server

Mani Mahesh Sivakumar 56 Reputation points
2022-10-25T17:11:55.49+00:00

Hi team,

I have enabled pg_cron extension by making changes in server parameters. Enabling pg_cron in azure.extenssion and shared_preload_libraries.

We have multiple databases in our cluster.

I need to configure jobs in all the DBs using Pg_cron

I have used pg_cron.schedule_in_database for this scenario.

After enabling extension, I tried running below mentioned commands in postgres Database(Default DB).

select Cron.Schedule_in_database('MV_REFRESH','*/5 * * * ','REFRESH MATERIALIZEWD VIEW schema.materialized_view','Dbname_1','Username_1',TRUE)
select Cron.Schedule_in_database('MV_REFRESH','/5 * * * *','REFRESH MATERIALIZEWD VIEW schema.materialized_view','Dbname_2','Username_2',TRUE)

I am getting error as
ERROR: must be superuser to create a job for another role
SQL state: XX000

Is there a way to configure multiple DB Names in Cron.database_name parameter in server parameter. Or How to handle this issue. Kindly Guide.

Thanks and regards,
Manimahesh S

Azure Database for PostgreSQL

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. GeethaThatipatri-MSFT 27,717 Reputation points Microsoft Employee
    2022-10-25T19:08:47.437+00:00

    Hi, @Mani Mahesh Sivakumar Thanks for the ask and for using the Microsoft Q&A platform.
    If my understanding is correct you want to know if pg_cron allows scheduling a cron job for a database other than the one it was created in
    pg_cron can only run in Postgres DB and Azure Postgres Flexible server doesn't support it on another database different than Postgres DB.
    As a workaround on FSPG is to set up a cross-reference database with DBLINK to connect back to the local database. however, this will make it harder to develop code and complicate the database administration

    Please, refer to the link for more information about DBLINK

    -- Schedule query that returns results
    SELECT cron.schedule('select-1-in-other', '* * * * *', $$
    select * from dblink('host=xxxx-test.postgres.database.azure.com user=xxxx dbname=other password=MyPass', 'select 1') as res (x int);
    $$);

    I hope this information helps, let me know if you have any additional questions.

    Regards
    Geetha

    1 person found this answer helpful.
  2. Alicja Kucharczyk 180 Reputation points Microsoft Employee
    2023-08-17T19:03:19.39+00:00

    It's no longer necessary to use dblink. You can simply schedule a cron job in specific database using the following query:

    SELECT cron.schedule_in_database('VACUUM','*/5 * * * * ','VACUUM','your_db_name');

    Please learn more in the docs: https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-extensions#pg_cron

    1 person found this answer helpful.
    0 comments
  3. Syed Waleed Aftab-JCI 1 Reputation point
    2022-11-11T17:30:01.417+00:00

    @GeethaThatipatri-MSFT Hi, I'm able to set the cron after following your cmd like this 

    SELECT   
cron.schedule(  
'eventdelete',  
'2 * * * *',  
$$  
select from dblink('host=XXXXXX.postgres.database.azure.com user=XXXX dbname=XXX password=XXXXX', 'select * from delete_events_func(1)') as res (x int);  
$$  
);

    The highlighted dblink cmd is working as standalone but cron is not getting triggered.

    select * from cron.job_run_details This cmd also don't show the fresh runs for the cron which is schedule for every two minutes.

    Let me know if I'm missing out something

    Thanks for your help

    0 comments