pg_cron is a simple cron-based job scheduler for PostgreSQL that runs inside the database as an extension. It allows you to schedule PostgreSQL commands directly from your database, similar to using cron jobs at the operating system level. pg_cron on PG Flex is pretty easy to use, making it easy to schedule regular database maintenance and processing tasks directly from within PostgreSQL.

Enabling pg_cron

1. Navigate to your Azure Database for PostgreSQL Flexible Server in the Azure portal
2. Go to “Server parameters” under Settings in the left navigation panel
3. Search for the parameter “azure.extensions”
4. Add “pg_cron” to the list of extensions (use a comma to separate if there are multiple extensions)
5. Also, add “pg_cron” to the shared_preload_libraries
6. Save the changes
7. Restart your server so the changes take effect

After a restart, verify that these parameters are configured. These may happen by default. I didn’t purposefully set these, so want you to ensure you have something set there.

After your server restarts, connect to your database with psql or the db client of your choice. I use Azure Data Studio, if you are wondering.

Create the extension:

CREATE EXTENSION pg_cron;

To verify that pg_cron is installed correctly:

SELECT * FROM pg_available_extensions WHERE name = 'pg_cron';

This should return a row confirming that pg_cron is installed in your database.

Executing Jobs with a Cron Role

To keep your cron jobs cleanly separated from your admin login, creating a dedicated Postgres role just for scheduled jobs is a good idea. This approach improves security and auditability and avoids accidentally running jobs with superuser privileges.

CREATE ROLE cron_admin WITH LOGIN PASSWORD 'your_strong_password';
GRANT CONNECT ON DATABASE your_db_name TO cron_admin;

It really depends on what you want to do with your cron jobs and what permissions you need. In my case, I want to do vacuum and reindex, which need owner-like permissions. Only do this if the user is well-contained and intended solely for job execution.

-- Allow usage and object creation in the schema
GRANT USAGE, CREATE ON SCHEMA your_schema TO cron_admin;

-- Grant full privileges on existing tables, sequences, and functions
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA your_schema TO cron_admin;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA your_schema TO cron_admin;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA your_schema TO cron_admin;

-- Ensure all future objects get the same privileges automatically
ALTER DEFAULT PRIVILEGES IN SCHEMA your_schema 
GRANT ALL PRIVILEGES ON TABLES TO cron_admin;

ALTER DEFAULT PRIVILEGES IN SCHEMA your_schema 
GRANT ALL PRIVILEGES ON SEQUENCES TO cron_admin;

ALTER DEFAULT PRIVILEGES IN SCHEMA your_schema 
GRANT ALL PRIVILEGES ON FUNCTIONS TO cron_admin;

If you want long-running operations such as VACUUM to have a custom timeout, set it on the role.

ALTER ROLE cron_admin SET statement_timeout = '1h';

This ensures jobs won’t hang or fail. You can check the current timeout when logged in as cron_admin.

SHOW statement_timeout; 

Creating and Managing Jobs

First, you need to know what all those characters and numbers mean for scheduling purposes. Check out crontab.guru to figure out what you want your schedule to look like.

Remember, Postgres Flex is in UTC timezone, so schedule accordingly with cron. To check the timezone of your server for yourself, run this:

SHOW timezone;

Basic Job Creation

Note: This is my process for small databases, and I mean really small. Do not do this on large databases, or you will be manually vacuuming and reindexing to the point of bringing down your server because it will hammer it hard. I will post soon about vacuuming or reindexing only if it needs to be done.

Since I put cron in the Postgres db and you can only install it in that one db, which is set in the portal parameters, then you will need to call a function (cron.schedule_in_database) to run it in other dbs, because I’m guessing you don’t have all your tables in Postgres, hopefully. This will run nightly at 3 am UTC on the specified db. I have multiple databases on the same server, and will stagger them a bit, so they don’t all fire off simultaneously.

SELECT cron.schedule_in_database('nightly_vacuum_dbname', '0 3 * * *', 'VACUUM ANALYZE;','your_db_name');

A similar concept for reindexing would be scheduled weekly at 1 am UTC. Also, the other databases would be staggered so they don’t run simultaneously. Again, I want to determine if the index needs a reindex, but that’s for later. I want to get something set up so it’s happening on a schedule for now. Again, they are really small databases, so the full treatment here isn’t going to break the bank, so to speak. Also, again, don’t go nuts on big indexes.

SELECT cron.schedule_in_database('weekly_reindex_dbname', '0 1 * * *', 'REINDEX DATABASE;','your_db_name');

View Your Scheduled Jobs

SELECT * FROM cron.job;

Get Details about Executed Jobs

SELECT * FROM cron.job_run_details; 

Delete a Job

Note: To update a job with a new command, you have to delete it first and then re-add it; otherwise, you will end up with a scheduled job with the old code in it. If you just want to change the schedule, you don’t have to delete it and recreate it.

SELECT cron.unschedule('my_job_name');

Use Cases for pg_cron

Here are some common tasks you might want to schedule—just some sample ideas. Not saying yeah, do these things exactly.

Database Maintenance

Again, remember that this needs to be more fine-tuned on larger tables/databases.

-- Daily vacuum at 3 AM
SELECT cron.schedule('nightly_vacuum', '0 3 * * *', 'VACUUM ANALYZE');

-- Weekly reindex on Sundays at 1 AM
SELECT cron.schedule('weekly_reindex', '0 1 * * 0', 'REINDEX DATABASE current_database()');

Data Aggregation

-- Hourly aggregation of analytics data
SELECT cron.schedule('hourly_stats', '0 * * * *', 
  'INSERT INTO hourly_summary (hour, count, avg_duration) 
   SELECT date_trunc(''hour'', created_at), COUNT(*), AVG(duration) 
   FROM events 
   WHERE created_at >= NOW() - INTERVAL ''1 hour''
   GROUP BY 1');

Data Retention/Cleanup

-- Delete old logs every day at midnight
SELECT cron.schedule('cleanup_logs', '0 0 * * *', 
  'DELETE FROM event_logs WHERE created_at < NOW() - INTERVAL ''90 days''');

Best Practices for pg_cron on Azure

1. Monitor job execution: Check cron.job_run_details regularly to ensure your jobs are running successfully. Better yet, set up alerting. Instructions included below.
2. Control permissions: Create a dedicated role for running maintenance tasks.
3. Error handling: Consider using PL/pgSQL functions to handle errors for complex jobs.
4. Avoid resource-intensive tasks during peak hours to maintain application performance.
5. Test thoroughly: Validate job behavior before implementing in production.

Limitations on Azure PostgreSQL Flexible Server

When using pg_cron on Azure, be aware of these limitations:

• Your server resources limit the maximum number of concurrent jobs
• Long-running jobs may be terminated if they exceed timeout thresholds
• You need appropriate permissions to create and manage cron jobs

Alerting on Failed Jobs

A cron job running successfully is great, but you need to know if it fails.

Enable PostgreSQL Logs for Cron Failures

Go to Azure Portal > Your PostgreSQL Server > Server Parameters, and set this level at a minimum so it captures your logs.

• log_min_messages = error
• log_min_error_statement = error
  I have mine set to warning
• log_statement = none or ddl (optional, to reduce noise)
  I have mine set to none currently

Enable Diagnostic Settings

1. Go to:
   Azure Portal > PostgreSQL Flexible Server > Diagnostic Settings
2. Click + Add diagnostic setting
3. Choose Logs to export:
   • PostgreSQLLogs (this includes pg_cron messages, but not failed jobs. There’s something else to add for that.)
4. Send to:
   • Log Analytics workspace (best for querying/alerting)
   • Optionally: Storage account or Event Hub for archiving/streaming
5. Save the setting.

Cron job to log failed jobs

This job will log the jobs that failed to your Postgres log, and then your diagnostic setting from above gets the log into Log Analytics. This gets failed jobs every 6 hours and sends them to the log. This is plenty as I will only be running jobs once a day. Update as you see fit for the frequency of your jobs and how soon after you need to know when they fail.

Something I do wonder about is if this job to check for failed jobs fails, then what happens? But it turns into a turtles all the way down situation, and I’m going to trust that cron will run the jobs that I scheduled. But manually check that this job is not failing after setting it up to ensure something didn’t go haywire.

SELECT cron.schedule_in_database(
  'cron_monitor_logger',
  '* /6 * * *',
  $job$
  DO $$
  DECLARE
    r record;
  BEGIN
    FOR r IN
      SELECT d.jobid, j.jobname, d.start_time, d.return_message
      FROM cron.job_run_details d
      JOIN cron.job j ON d.jobid = j.jobid
      WHERE d.status = 'failed'
        AND d.end_time > now() - interval '24 hours'
    LOOP
      RAISE LOG 'CRON JOB FAILED: % (jobid %), started at %, message: %',
        r.jobname, r.jobid, r.start_time, r.return_message;
    END LOOP;
  END;
  $$;
  $job$,
  'postgres'
);

And here’s a job you can set up so that you have something to test your query in Log Analytics for failed jobs.

SELECT cron.schedule_in_database(
  'failing_test_job',
  '*/5 * * * *',
  'SELECT 1 / 0;',
  'postgres'
);

Query for Failed Jobs in Log Analytics

Once logs flow into Log Analytics (usually within minutes), run this query:

AzureDiagnostics 
| where ResourceType == "FLEXIBLESERVERS" 
| where Category == "PostgreSQLLogs" 
| where Message contains "CRON JOB FAILED:" 
| where not(Message contains "DO $$")
| project TimeGenerated, Message, Resource

You’ll see logs if a job has failed. In my case, I had that job running every 5 minutes and failing with a divide by zero error to make sure I saw something in the log.

Create an Alert Rule on a Kusto Query

You will need to create an alert rule in Azure Monitor. For basic instructions on doing that, visit this link.

I set these values:

• Scope = I selected the PG flex server, but you may have more so you could choose a resource group or select each PG server.
• Signal name = Custom log search
• Search query = Log analytics query from above
• Measure = Table rows
• Aggregation type = Count
• Aggregation granularity = 6 hours – set this based on how often your jobs run and how soon you want to know if they failed
• Frequency of evaluation – 6 hours (changed by default with the aggregation granularity)
• Actions – I have an action group that sends an email
• Details
  • Severity = 1 – Error
  • Alert rule name = Postgres Flex Cron Job Failed
  • Region = your choice
  • Identity = Default

This alert will fire every 6 hours, starting 6 hours after you set it up. If you set it up to run once a day, it’s fired off at the time you set it up the next day. If you don’t like that time, you must recreate it at a different time, or you could schedule it to fire more often. You could tag it with the created time, so you won’t lose track later of what time it will run like createdAt = 2025-04-17T14:34Z

It’s very inexpensive for a month, with my estimated cost being 50 cents. Even when I set it to run hourly, it’s still that cheap. Every 5 mins is $1.50, so the alerts won’t break your bank. I don’t recommend setting it that often unless you have a corn job running that often.

Alert email example

It gives you the count of the jobs failed during the alert window.

But it does also give you a way to see the errors by clicking on the links farther down in the email, and this is good enough for my purposes in the end.

I clicked on View search results and can see the error, which helps me determine which cron job is failing.

Now, you have cron setup and maybe some jobs in it. Like I said, I’m going to post about my more carefully crafted process for vacuuming and reindexing in the near future. I want to vacuum manually and reindex only if it’s needed, not just do everything, but for now, with tiny dbs, I’m just going to put in a vacuum analyze nightly and a reindex weekly since it’s not a heavy load.