SNS

pg_cron Failure Alerts via Lambda and SNS

This setup ensures that failures in pg_cron jobs are detected and summarized by a Lambda function. The Lambda publishes a single consolidated alert to an Amazon SNS topic, which then delivers the alert to subscribed email addresses.

Architecture

1. pg_cron jobs → Log failures in PostgreSQL. select * from cron.job_run_details jrd where jrd.status = 'failed'
2. AWS Lambda function → Queries the database for recent failures and generates a summary. postgreCron_logs
3. Amazon SNS topic (pgcron-failure-alerts) → Receives the alert from Lambda. pgcron-failure-alerts
4. SNS Subscriptions → Deliver the alert to multiple email addresses.
5. EventBridge Scheduler → Triggers the Lambda function daily at 8:15 AM.

A Lambda function was created with the following responsibilities:

• Connect to PostgreSQL database.
• Query job failures in the last 25 hours (lookback period configurable).
• Consolidate all failures into a single summary message.
• Publish the summary to SNS.

Sample log output

🚨[FAILURE]🚨 runid=62994 jobid=105 status=failed cmd=REFRESH MATERIALIZED VIEW kotahi.mv_tta_clients

🚨[FAILURE]🚨 runid=62999 jobid=130 status=failed cmd=REFRESH MATERIALIZED VIEW kotahi.mvw_tta_report

🚨[FAILURE]🚨 runid=63009 jobid=76 status=failed cmd=select * from rpt.fn_risk_strat()

🚨[FAILURE]🚨 runid=63130 jobid=132 status=failed cmd=REFRESH MATERIALIZED VIEW CONCURRENTLY kotahi.mv_tta_report

[ALERT] Summary sent to SNS topic. count=4

2. SNS Topic

Topic ARN: arn:aws:sns:ap-southeast-2:516630523714:pgcron-failure-alerts

The Lambda IAM role was granted permission to publish to this SNS topic using this policy:

{

  "Version": "2012-10-17",

  "Statement": [

    {

      "Sid": "AllowPublishToPgCronTopic",

      "Effect": "Allow",

      "Action": "sns:Publish",

      "Resource": "arn:aws:sns:ap-southeast-2:516630523714:pgcron-failure-alerts"

    }

  ]

}

3. Email Subscriptions

Subscription Steps:

aws sns subscribe \

  --topic-arn arn:aws:sns:ap-southeast-2:516630523714:pgcron-failure-alerts \

  --protocol email \

  --notification-endpoint xxxxx@kautepasifika.co.nz

aws sns subscribe \

  --topic-arn arn:aws:sns:ap-southeast-2:516630523714:pgcron-failure-alerts \

  --protocol email \

  --notification-endpoint yyyyyy@kautepasifika.co.nz