How to Automate Alerts for PostgreSQL Query Changes

In the modern data-driven landscape, being immediately aware of changes in your database is essential. As part of the monitoring system, organizations rely on alerts and notifications as their first line of defence to achieving high availability and consequently lowering outage costs. It allows for proactive monitoring and prompt action, ensuring seamless operations and data integrity. This helps you to be promptly informed about significant events such as new customer sign-ups, new orders being placed or any issues.

Method #1: Manual setup through PostgreSQL’s NOTIFY and LISTEN Clauses

PostgreSQL's NOTIFY and LISTEN clauses serve as a traditional mechanism for monitoring in real-time. In this method, the NOTIFY clause announces a message whenever a significant event occurs, and the LISTEN clause receives the messages and acts upon them. Let's see how we can configure them:

‍

1. Setting Up Notification Triggers:

Define triggers in PostgreSQL to NOTIFY a particular channel whenever a specific query change occurs like an INSERT, UPDATE, or DELETE operation.

CREATE OR REPLACE FUNCTION notify_trigger() RETURNS TRIGGER AS $$
BEGIN
    PERFORM pg_notify('query_changes', TG_TABLE_NAME || ' ' || TG_OP);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER query_changes_trigger AFTER INSERT OR UPDATE OR DELETE ON your_table
FOR EACH ROW EXECUTE FUNCTION notify_trigger();

‍

2. Creating a Listener:

In a separate application or service, create a listener to catch these notifications and process them.

import psycopg2
import select
import json
import requests

conn = psycopg2.connect("dbname=test user=postgres")
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)

# update with your webhook
SLACK_WEBHOOK_URL = "https://hooks.slack.com/services/..."

curs = conn.cursor()
curs.execute("LISTEN query_changes;")

# Send downstream slack notifications
def send_slack_notification(payload):
  payload = json.loads(payload)
  
  slack_data = {
    "text": f"Changes in {payload['table']}: {payload['operation']}"
  }
  
  requests.post(SLACK_WEBHOOK_URL, json=slack_data)

print("Waiting for notifications on channel 'query_changes'")

while True:
  if select.select([conn],[],[],5) == ([],[],[]):
    print("Timeout")

  else:
    conn.poll()
    
    while conn.notifies:
      notify = conn.notifies.pop(0)
      t = Thread(target=send_slack_notification, args=(notify.payload,))
      t.start()

‍

This above method would help you deliver notifications about query result changes but it comes with a set of limitations which may make the whole process complex for you:

• This becomes less efficient as the scale of data increases leading to bottlenecks in high-traffic databases.
• NOTIFY has a payload size limit (currently 8000 bytes). This limitation can restrict the amount of information that can be sent
• Implementing and maintaining triggers for multiple tables and operations can be complex and error-prone, and requires constant monitoring.
• NOTIFY does not support built-in filtering or aggregation of notifications. This means that listeners receive all messages leading to an overwhelming amount of noise.

Let’s look at the other method, which can help you avoid these issues and is easy to set up.

‍

Method #2: Getting Alerted Through Locale

Locale simplifies the process of real-time monitoring and alerting for PostgreSQL query changes. Locale offers a no-code platform to connect your data source, set up monitoring rules, and sit back as you receive notifications on the go.

‍

Login to Locale, Go to Org Setting and Connect your database from the data source. Now go to Alerts → Create New Alerts. Choose your database → Click on Setup data to be monitored.

‍

Write SQL Query to set the alert condition that will trigger Slack notification when the query results match:

‍

Now Setup Alert Frequency based on your requirement, this would automatically run the query and send a Slack message based on the frequency you set.

‍

You can also configure the Slack message to give the user a better idea. Click on Configure incidents and enter your message in the Incident Title.

💡 You can also customise the message based on the query result by clicking on { } icon.

‍

Final Step, Set up notification. With Locale, you can easily set up alerts to any channel such as Slack, Email, WhatsApp, MS Teams, Webhook and many more. In this guide, we will set up the alerts on Slack. Go to the Setup Notification section and click on Add Notification then select Send Slack Message. Select whether you want the Slack message to be sent to a channel or a user. You can also preview and send test notifications.

‍

💡 If you want to trigger notifications to Slack users, you can also dynamically send Slack messages based on query results.

‍

Save the alert. It will automatically run itself on the pre-set schedule and trigger a Slack notification.

‍

Conclusion

In today’s modern operations, quick response to changes is important which creates the necessity of real-time alert on the preferred channel. In this detailed guide, we looked at two methods which can help you to automate alerts for PostgreSQL query result changes. The first method involving PostgreSQL’s NOTIFY and LISTEN clauses, provides a foundation but comes with its limitations and complexities.

Locale eases out the process of sending alerts when query results change with minimal one-time setup. It helps you overcome all the limitations that come with the manual setups. It also provides you with the option to choose a schedule for sending messages based on your requirements. It can dynamically choose people to send Slack message based on the query result allowing alerts only go to the person it needs, and even allowing to group the notifications ensuring less noise to the users.

It also allows you to set up and provide a clear playbook for users to follow in case of any issue so that it can be resolved efficiently and set up an escalation rule to ensure issues are being resolved. Moreover, if you want to expand sending reports/data to other destinations like Gmail, Whatsapp, MS Teams etc, you get to do that with 100+ integration options.

‍

Excited to get your hands on? Secure early access – speak with us.