Create a Monitoring Dashboard for Azure Database for PostgreSQL

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

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.  

1.Azure portal menu dashboard_P.png

Select New dashboard 

2.New dashboard_P.png

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

3.Done Customizing_P.png

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.

4.Share Dashboard_P.png

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

5.Publish Dashboard_P.png

 

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 settingsThrough 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, s
elect Diagnostic settings  

 

6.Azure Postgres menu diagnostic settings_P.png

Click on Add diagnostic setting  

 

7.Add Diag Setting_P.png

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).

8.Log analytics setting_P.png

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.

1.Azure portal menu dashboard .png

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

9.Edit Dashboard_P.png

Click on Add Metrics Chart 

10.Metrics Chart_P.png

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

11.Edit Metrics_P.png

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

12.Add Metric_P.png

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

13.Save to Dashboard_P.png

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. 

 

14.All 3 Mertics_P.png

 

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  

15.Log Analytics_P.png

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.

 

17.pin to dashboard_P.png

 

16.Pin To Dashboard apply_P.png

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:

 

18.Final Dashboard_P.png


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

Hope this helps,

Sudheesh Narayanaswamy

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.