Unable to create extension for pg_cron

[pshah]

I have 2 alloydbsuperusers and I have enabled database_flag alloydb.enable_pg_cron to on.

When i log in via a compute engine and use psql, i can connect successfully from both users to my database but when i try to edit or alter postgresql.conf for setting cron.database_name ="my_db" it says permission denied.

And same for creating extension.

my_db=> ALTER SYSTEM SET cron.database_name = 'my_db';
ERROR: permission denied to set parameter "cron.database_name"
my_db=> 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 = 'my_db' in postgresql.conf to use the current database.
CONTEXT: PL/pgSQL function inline_code_block line 4 at RAISE
my_db=> \q

Ultimately i just want to create extension for pg_cron in my_db database on alloydb and run cron queries. How do i achieve it?

[AndrewB · Accepted solution]

pg_cron is supported in AlloyDB: https://cloud.google.com/alloydb/docs/reference/extensions

Before you can use CREATE EXTENTSION to enable this extension, you must first set the database flag (https://cloud.google.com/alloydb/docs/instance-configure-database-flags) alloydb.enable_pg_cron to the value on in the AlloyDB instance where you want to use the extension.

After that, you will be able to CREATE EXTENTSIONpg_cron, or set other pg_cron flags like cron.database_name, cron.timezone, cron.log_statement, cron.log_run, cron.max_running_jobs, and cron.log_min_messages.

[pshah]

Thank you, It worked.

I realized that I should create stored procedure calls in my databases and then call them using cron.schedule() in postgres db as it is the default database configured for pg_cron.