Installation of pg_cron on Azure Flexible PostgeSQL

Question

I am trying to install pg-cron extension for Azure PostgreSQL Flexible server. According to documentation found here: https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-extensions#postgres-13-extensions pg_cron is available extension, but when I am trying to install it:

create schema cron_pg;
CREATE EXTENSION pg_cron SCHEMA cron_pg;

What I get is:

SQL Error [0A000]: ERROR: extension "pg_cron" is not allow-listed for "azure_pg_admin" users in Azure Database for PostgreSQL
  Hint: to see the full allow list of extensions, please run: "show azure.extensions;"

When executing:

show azure.extensions;

pg_cron is missing:

address_standardizer,address_standardizer_data_us,amcheck,bloom,btree_gin,btree_gist,citext,cube,dblink,dict_int,dict_xsyn,earthdistance,fuzzystrmatch,hstore,intagg,intarray,isn,lo,ltree,pageinspect,pg_buffercache,pg_freespacemap,pg_partman,pg_prewarm,pg_stat_statements,pg_trgm,pg_visibility,pgaudit,pgcrypto,pgrowlocks,pglogical,pgstattuple,plpgsql,postgis,postgis_sfcgal,postgis_tiger_geocoder,postgis_topology,postgres_fdw,sslinfo,tablefunc,tsm_system_rows,tsm_system_time,unaccent,uuid-ossp,lo,postgis_raster

What am I doing wrong?

Baroque

Answer 1

Pretty late but this issue showed up when I was searching for same problem but with pg_trgm extension. After some looking around eventually realised you just need to update the database settings.

Go to Database in Azure Portal, then to Server parameters and search for azure.extensions parameter. You can then click on the list and enable/disable desired extensions (PG_CRON is available), the server will restart on save and then you will be able to enable the extensions in database.

Limnology

This should be the correct answer. Had the same problem restoring a database from a different server and got the error message: "ERROR: extension "pgrowlocks" is not allow-listed for "azure_pg_admin" users in Azure Database for PostgreSQL". – Writer

Answer 2

You can tell pg_cron to run jobs in another database by updating the database column job in the jobs table.

For example:

UPDATE cron.job SET database = 'wordpress' WHERE jobname = 'wordpress-job';

Noleta

Answer 3

Seems that the pg_cron extension is already enabled, by default, in the default 'postgres' database. The reason why I was not seeing this is because I am not using the default 'postgres' database. I have created my own DB which I was connected to. This actually does not resolve my problem, because I can't execute jobs from pg_cron across databases...

Baroque