There might be situations where you or a group of people wants to get notified in case something goes wrong in any of your long-running PL/pgSQL code. This short article demonstrates how Azure Monitor can be leveraged for this kind of task.
First, we'll have to forward the server logs to a Logs Analytics workspace, which we then use to create an Azure Monitor Alert. So let's create a workspace in case there is not one in place yet.
Create a Log Analytics Workspace
There is nothing exciting about that. Just navigate to the Azure Portal, enter Log Analytics workspaces in the search bar, and follow the wizard.
Next, we'll want to forward the Postgres Server Logs to the Log Analytics workspace.
Configure Diagnostic settings
Navigate to Monitoring > Diagnostic settings and click Add diagnostic setting. Make sure PostgreSQL Server Logs is checked in the Logs section to the left. Select the Log Analytics workspace from the dropdown list and hit save.
🔎 In case you are running Postgres Single Server a pricing tier of General Purpose or Memory Optimized is required so that you are able to forward the PostgreSQL Server Logs to a Logs Analytics Workspace.
Before we now get to create the alert, let's first examine some example PL/pgSQL code snippets.
Example PL/pgSQL function
The following function is flawed in that it attempts to divide by zero which throws an exception.
The example makes use of the
GET STACKED DIAGNOSTICS command to get detailed information about the current exception and passes it on to a simple logger function.
This dedicated logger function helps standardize the error message that we later parse.
Creating an alert
Now that we know what an exception message will look like, we can create an alert from the Postgres blade by navigating to Monitoring > Alerts. First, create an alert rule and select Custom log search as the source signal.
When asked for a KQL query, enter the following and click on Continue Editing Alert. The query returns every row from the table
AzureDiagnostics where the Log-Message contains the keyword
Exception and belongs to the category
| where Category == "PostgreSQLLogs" and Message has "Exception"
| project TimeGenerated, Message
| order by TimeGenerated desc
From within the Measurement section, choose an Aggregation granularity that fits your purpose.
In the example from above, if e.g. 2 events occur within 1 minute the aggregated result will be 2 and passed on to the Alert Logic. Next, the logic defines when an alert should be fired. The example below will fire an alert, whenever a single exception occurs within 1 minute.
Don't forget to create an action group that defines notification types.
And with that, every exception will trigger an Azure Alert. You can find an example below. Unfortunately, the exception message does not get shown in the e-mail. However, by clicking on the Search Results link we will be forwarded to the Log Analytics workspace pointing to the event.
This short walkthrough demonstrated how we can easily receive Azure Alerts triggered by your PL/pgSQL code. Let me summarize the most important points.
- When using a single server, a pricing tier of General Purpose or Memory Optimized is required
- Be patient when looking for the Postgres Server Logs in your Log Analytics Workspace, it will take some time (~ 5 minutes) until they show up
- The log type is
AzureDiagnostics, and the category
- We can't directly propagate the exception message via the alert mail. Instead, we need to click the Search results link from the mail
I hope you found this post informative! 🔎 Happy hacking! 👨🏻💻