alerts for blocking locks on RDS Postgresql or Aurora Postgresql

Hi there,

Looks like you need to combine multiple pieces here:

1. Query Blocking queries: --https://repost.aws/knowledge-center/rds-aurora-postgresql-query-blocked

2. Run cron jobs periodically to check if blocking exists and put them in user created table: --https://aws.amazon.com/blogs/database/schedule-jobs-with-pg_cron-on-your-amazon-rds-for-postgresql-or-amazon-aurora-for-postgresql-databases/ --https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL_pg_cron.html

3. Send an email: --https://aws.amazon.com/blogs/database/sending-notifications-from-amazon-aurora-postgresql/

Hi Jeremy, have you tried this?

1. Enabling Enhanced Monitoring: Start by enabling Enhanced Monitoring for your RDS or Aurora instance. You can enable Enhanced Monitoring when creating a new instance or modify an existing one. You can select the desired granularity level (e.g., 1 second, 10 seconds, etc.) depending on your needs.

2. Set up CloudWatch Alarms: Once Enhanced Monitoring is enabled, you can set up CloudWatch Alarms to monitor for blocking locks. In the metrics list, search for the relevant metric related to locks. It might be under the "Database" or "PostgreSQL" namespace, and the metric name might contain "lock" or "blocking." Define your threshold for the alarm.

3. Set Up Actions: Define actions to take when the alarm state is triggered. Common actions include sending notifications through Amazon SNS (Simple Notification Service), executing an AWS Lambda function, or running a custom script.

4. Notification Configuration: Configure your notification targets (e.g., email, SMS, or a webhook) to receive alerts when the alarm state changes (e.g., from OK to ALARM).

[Blog Post Link that might help] https://aws.amazon.com/blogs/database/monitor-amazon-rds-for-postgresql-and-amazon-aurora-for-postgresql-database-log-errors-and-set-up-notifications-using-amazon-cloudwatch/

1. Publish the PostgreSQL log to CloudWatch Logs.
2. Create an SNS topic.
3. Create an SNS subscription.
4. Create a metric filter on the log group in CloudWatch Logs.
5. Create an alarm in CloudWatch.