Create a Monitoring Dashboard for Azure Database for PostgreSQL

A dashboard visually summarizes all the important metrics you have selected to track, to give you a quick and easy overview of where everything stands. A real-time dashboard is a live view of exactly how your application information is performing. This helps in better decision making and helps in taking proactive actions.

  

This blog explains how you can use Azure Log Analytics to create a custom dashboard for Azure Database for PostgreSQL. 

  

This is a starter guide. You can use these ideas and customize as per your need.  

 

Steps 

1. Create a Custom Azure Dashboard in the portal  
2. Configure the database for Data Collection 
3. Send data to Log Analytics 
4. Create charts for the Dashboard. 
   1. Add Metrics 
   2. Write a Kusto Query for logs 
5. Tag it to the Custom Dashboard.  

 

Create Custom Azure Dashboard 

Log in to the Azure portal. Select Dashboard to open your default dashboard.  

Select New dashboard 

Give the Name for your Dashboard and Select Done Customizing for now 

For more details on how to create a Dashboard you can refer to: 

https://docs.microsoft.com/azure/azure-portal/azure-portal-dashboards  

  

When you create a dashboard, it is private by default, which means you are the only person who can see it. To make it visible to others, use the Share button that appears alongside the other dashboard commands. You are asked to choose a subscription and resource group for your dashboard to be published to.

Select the Subscription Name where you would like to share and select Publish 

 

Configure the database for Data Collection 

In this step, you configure logging on your Azure Database for PostgreSQL server.

 

• Metrics are available by default. There is no specific configuration that needs to be done. The metrics available in Azure Database for PostgreSQL are documented in the article below : 

https://docs.microsoft.com/azure/postgresql/concepts-monitoring  

 

• To get information from Azure Database for PostgreSQL server logs, audit logs and Query Store, you first have to enable that logging. Learn more about these three logging options and how to turn them on at the following links: 

Server Logs 

Audit Logs 

Query Store 

 

Depending on what you would like to see in the Dashboard, configure the logging as needed. 

 

Send data to Azure Monitor Logs (Log Analytics) 

 

Azure Database for PostgreSQL is integrated with Azure Monitor diagnostic settings. Through this service, Postgres logs and metrics can be emitted to Azure Monitor Logs, Event Hubs, or Azure Storage. For our scenario we will send to Azure Monitor Logs (also known as Log Analytics). 

In the Azure portal, select your Azure Database for PostgreSQL server. In the server's menu, select Diagnostic settings  

  

Click on Add diagnostic setting  

 

Select the data you would like to upload to the Azure Monitor Logs.
(If you don’t have a Log Analytics Workspace, create one. You can refer to  https://docs.microsoft.com/azure/azure-monitor/learn/quick-create-workspace).

Note: Audit logs are emitted as part of the PostgreSQLLogs category. 

 

Create charts for the Dashboard

Add the Metrics selected

 Go to Dashboard again from the Azure portal menu.

Select the custom dashboard you created initially and click on Edit. (In the picture below my custom dashboard is called Azure Database for Postgres). 

Click on Add Metrics Chart 

Click on Done Customizing. Once you are back on the dashboard page select Edit in Metrics on the chart. 

Use Select a scope to choose your Postgres server. Select the Metric you would like to see and the Aggregation.  

You can add multiple metrics for the same Postgres server in one chart. You could have multiple Postgres servers listed for the same metric. 

In this chart we have the CPU percentage of one PostgreSQL server. You can add metrics of your choice and then select Save to Dashboard.  

 

In the same way you can create more charts.  For our example I have charts for Avg CPU, Avg Memory and Max Storage. 

  

 

Write a Kusto Query for logs

Now let's see how we have bring the log data we sent to log analytics into the dashboard. 

  

First go to Monitor from the Azure portal menu, or using the search bar. Select Logs and click on Get started  

Select the Scope. (Here for example I selected postgreSQL server ) 

 

For the restarts I used the below query.  

 

AzureDiagnostics 
| where Category == "PostgreSQLLogs" 
| where LogicalServerName_s == "<ServerName>" 
| where Message contains "database system was shut down at" or Message contains "database system is ready to accept" 
| project LogicalServerName_s , Message, TimeGenerated 

 

Run your query to view results. Use Pin to dashboard to make the results of this query a chart.

 

 

A couple other query samples: 

  

Errors  

 

AzureDiagnostics 
| where Category == "PostgreSQLLogs" 
| where LogicalServerName_s == "<Server Name>" 
| where errorLevel_s contains "error" 
| project LogicalServerName_s , errorLevel_s , Message, TimeGenerated 

 

  

 Top 10 time consuming queries 

 

AzureDiagnostics 
| where Category == "QueryStoreRuntimeStatistics" 
| where db_id_s == "18153" 
| where LogicalServerName_s == "<Server Name>" 
| order by total_time_s desc 
| project query_id_s, total_time_s, rows_s, db_id_s , calls_s, LogicalServerName_s , user_id_s , runtime_stats_entry_id_s 
| limit 10 

 

Learn more about writing Kusto queries in this tutorial.
Learn about the PostgreSQLLogs schema in this doc. 

 

  

My final dashboard:

 

You can continue to add charts to your dashboard and edit its layout, customizing it to suit you.

Hope this helps,

Sudheesh Narayanaswamy