Azure Database for PostgreSQL

Azure Database for PostgreSQL is a fully managed relational database service in the Microsoft cloud based on the PostgreSQL community edition. This integration helps in monitoring resource utilization and identifying slow queries to optimize your workloads and configure your server for the best performance.

The below instructions applies to Azure Database for PostgreSQL with Flexible Server only.

Log and metric types

For Azure Database for PostgreSQL, you can collect the following logs and metrics:

• PostgreSQL Logs. These logs can be used to identify, troubleshoot, and repair configuration errors and suboptimal performance. To learn more about the log format, refer to the Azure documentation.
• Audit Logs. Audit logging of database activities is available through pgAudit extension. By default, pgAudit log statements are emitted along with your regular log statements by using Postgres's standard logging facility. To learn more about the audit log format, refer to the pgAudit documentation.
• Metrics. These metrics are available for a flexible server instance of Azure Database for PostgreSQL. For more information on supported metrics and instructions for enabling them, refer to the Azure documentation.

Setup

Azure service sends monitoring data to Azure Monitor, which can then stream data to Eventhub. Sumo Logic supports:

• PostgreSQL Audit logs. Azure Database for PostgreSQL flexible server provides users with the ability to configure audit logs. Audit logs can be used to track database-level activity including connection, admin, DDL, and DML events. These types of logs are commonly used for compliance purposes. To learn more about the different log types and schemas collected for Azure Database for PostgreSQL, refer to the Azure documentation.
• Logs collection from Azure Monitor using our Azure Event Hubs source.
• Metrics collection using our HTTP Logs and Metrics source via Azure Functions deployed using the ARM template.

You must explicitly enable diagnostic settings for each Azure Database for PostgreSQL server you want to monitor. You can forward logs to the same event hub provided they satisfy the limitations and permissions as described here.

When you configure the event hubs source or HTTP source, plan your source category to ease the querying process. A hierarchical approach allows you to make use of wildcards. For example: Azure/DatabaseForPostgreSQL/Logs, Azure/DatabaseForPostgreSQL/Metrics.

Configure field in field schema

1. Classic UI. In the main Sumo Logic menu, select Manage Data > Logs > Fields.
   New UI. In the top menu select Configuration, and then under Logs select Fields. You can also click the Go To... menu at the top of the screen and select Fields.
2. Search for the following fields:
   • tenant_name. This field is tagged at the collector level. You can get the tenant name using the instructions in the Microsoft Documentation.
   • location. The region to which the resource name belongs to.
   • subscription_id. ID associated with a subscription where the resource is present.
   • resource_group. The resource group name where the Azure resource is present.
   • provider_name. Azure resource provider name (for example, Microsoft.Network).
   • resource_type. Azure resource type (for example, storage accounts).
   • resource_name. The name of the resource (for example, storage account name).
   • service_type. Type of the service that can be accessed with a Azure resource.
   • service_name. Services that can be accessed with an Azure resource (for example, Azure SQL databases in Azure SQL Server).
3. Create the fields if they are not present. Refer to Manage fields.

Configure Field Extraction Rules

Create the following Field Extraction Rules (FER) for Azure Storage by following the instructions in the Create a Field Extraction Rule.

Azure location extraction FER

Rule Name: AzureLocationExtractionFER
Applied at: Ingest Time
Scope (Specific Data): tenant_name=*

Parse Expression

json "location", "properties.resourceLocation", "properties.region" as location, resourceLocation, service_region nodrop
| replace(toLowerCase(resourceLocation), " ", "") as resourceLocation
| if (!isBlank(resourceLocation), resourceLocation, location) as location
| if (!isBlank(service_region), service_region, location) as location 
| if (isBlank(location), "global", location) as location
| fields location

Resource ID extraction FER

Rule Name: AzureResourceIdExtractionFER
Applied at: Ingest Time
Scope (Specific Data): tenant_name=*

Parse Expression

json "resourceId", "ResourceId" as resourceId1, resourceId2 nodrop
| if (isBlank(resourceId1), resourceId2, resourceId1) as resourceId
| toUpperCase(resourceId) as resourceId
| parse regex field=resourceId "/SUBSCRIPTIONS/(?<subscription_id>[^/]+)" nodrop
| parse field=resourceId "/RESOURCEGROUPS/*/" as resource_group nodrop
| parse regex field=resourceId "/PROVIDERS/(?<provider_name>[^/]+)" nodrop
| parse regex field=resourceId "/PROVIDERS/[^/]+(?:/LOCATIONS/[^/]+)?/(?<resource_type>[^/]+)/(?<resource_name>.+)" nodrop
| parse regex field=resource_name "(?<parent_resource_name>[^/]+)(?:/PROVIDERS/[^/]+)?/(?<service_type>[^/]+)/?(?<service_name>.+)" nodrop
| if (isBlank(parent_resource_name), resource_name, parent_resource_name) as resource_name
| fields subscription_id, location, provider_name, resource_group, resource_type, resource_name, service_type, service_name

Configure metric rules

Create the following metrics rules by following the instructions in Create a metrics rule.

Azure observability metadata extraction flexible postgresql server level

Rule Name: AzureObservabilityMetadataExtractionAzureDatabaseForPostgreSQLLevel

Metric match expression

resourceId=/SUBSCRIPTIONS/*/RESOURCEGROUPS/*/PROVIDERS/MICROSOFT.DBFORPOSTGRESQL/FLEXIBLESERVERS/* tenant_name=*

Fields extracted	Metric rule
subscription_id	$resourceId._1
resource_group	$resourceId._2
provider_name	MICROSOFT.DBFORPOSTGRESQL
resource_type	FLEXIBLESERVERS
resource_name	$resourceId._3

Configure metrics collection

In this section, you will configure a pipeline for shipping metrics from Azure Monitor to an Event Hub, on to an Azure Function, and finally to an HTTP Source on a hosted collector in Sumo Logic.

1. Create hosted collector and tag tenant_name field.
   
2. Configure an HTTP Source.
3. Configure and deploy the ARM Template.
4. Export metrics to Event Hub. Perform below steps for each Flexible PostgreSQL Server resource that you want to monitor.
   1. Choose Stream to an event hub as destination.
   2. Select AllMetrics.
   3. Use the Event hub namespace created by the ARM template in Step 2 above. You can create a new Event hub or use the one created by ARM template. You can use the default policy RootManageSharedAccessKey as the policy name.
      
5. Tag the location field in the source with right location value.
   

Configure logs collection

Diagnostic logs

In this section, you will configure a pipeline for shipping diagnostic logs from Azure Monitor to an Event Hub.

1. To set up the Azure Event Hubs source in Sumo Logic, refer to the Azure Event Hubs Source for Logs.
2. To create the diagnostic settings in Azure portal, refer to the Azure documentation. Perform the steps below for each Azure Redis cache account that you want to monitor.
   1. Choose Stream to an event hub as the destination.
   2. Select allLogs.
   3. Use the Event Hub namespace and Event Hub name configured in the previous step in the destination details section. You can use the default policy RootManageSharedAccessKey as the policy name.
      
3. Set server parameters as below:
   • wal_level. Set to logical.
   • cron.log_statement. Set to ON.
   • log_statement_stats. Set to ON.
   • pgaudit.log_statement_once. Set to ON.
   • log_statement. Select ALL.
   • log_lock_waits. Set to ON.
   • log_recovery_conflict_waits. Set to ON.
4. Tag the location field in the source with right location value.
   

Activity Logs

To collect activity logs, follow the instructions here. Skip this step if you are already collecting activity logs for a subscription.

note

Since this source contains logs from multiple regions, ensure that you do not tag this source with the location tag.

Installing the Azure Flexible Database for PostgreSql app

Now that you have set up data collection, install the Azure Database for PostgreSQL Sumo Logic app to use the pre-configured dashboards that provide visibility into your environment for real-time analysis of overall usage.

To install the app:

1. Select App Catalog.
2. In the Search Apps field, search for and then select your app.
3. Optionally, you can scroll down to preview the dashboards included with the app. Then, click Install App (sometimes this button says Add Integration).
4. Click Next.
5. Look for the dialog confirming that your app was installed successfully.
   

Once an app is installed, it will appear in your Personal folder or the folder that you specified. From here, you can share it with other users in your organization. Dashboard panels will automatically start to fill with data matching the time range query received since you created the panel. Results won't be available immediately, but within about 20 minutes, you'll see completed graphs and maps.

Viewing the Flexible Database for PostgreSql dashboards

All dashboards have a set of filters that you can apply to the entire dashboard. Use these filters to drill down and examine the data to a granular level.

• You can change the time range for a dashboard or panel by selecting a predefined interval from a drop-down list, choosing a recently used time range, or specifying custom dates and times. Learn more.
• You can use template variables to drill down and examine the data on a granular level. For more information, see Filtering Dashboards with Template Variables.
• Most Next-Gen apps allow you to provide the scope at the installation time and are comprised of a key (_sourceCategory by default) and a default value for this key. Based on your input, the app dashboards will be parameterized with a dashboard variable, allowing you to change the dataset queried by all panels. This eliminates the need to create multiple copies of the same dashboard with different queries.

Disk

The Azure Database for PostgreSQL - Disk dashboard provides insights on Number of Temporary Files Created, Blocks Hit Count, Number of I/O Operations, Number of Temp Files Created, Total Bytes Written to Temp Files (Bytes), Disk I/Os Consumed/min (%), Disk Bandwidth Consumed/min (%), Number of Outstanding I/O Operations, Blocks Read Count, Total Bytes Written to Temp Files, Disk Bandwidth Consumed/min (%), and Disk I/Os Consumed/min (%).

Administrative Operations

The Azure Database for PostgreSQL - Administrative Operations dashboard provides insights on Top 10 operations that caused the most errors, Distribution by Operation Type (Read, Write, and Delete), Distribution by Operations, Recent Write Operations, Recent Delete Operations, Users/Applications by Operation type, and Distribution by Status.

Autovacuum

The Azure Database for PostgreSQL - Autovacuum dashboard provides insights on Estimated Dead Rows User Tables, Estimated Live Rows User Tables, Estimated Modifications User Tables, Analyze Counter User Tables, Vacuum Counter User Tables, User Tables Vacuumed vs AutoVacuumed, and User Tables Analyzed vs User Tables Auto.

Connections

The Azure Database for PostgreSQL - Connections dashboard provides insights on Active Connections, Failed Connections, Succeeded Connections, Max Connections, and Active vs Succeeded vs Failed Connections.

Error Logs

The Azure Database for PostgreSQL - Error Logs dashboard provides insights on Log by Sql Errcode, Log by Severity, Database Shut Down Events, Log by Backend Type, Database System Up Events, Top Error Statements, and Top Fatal Errors.

Overview

The Azure Database for PostgreSQL - Overview dashboard provides insights on Requests by Location, Is DB Alive, Number of Backends Connected to Database, and Number of Deadlocks Detected in Database.

Performance

The Azure Database for PostgreSQL - Performance dashboard provides insights on Max CPU (%), Max Memory (%), Cpu Credits Consumed, Cpu Credits Remaining, Read Throughput, Read Iops, Write Throughput, and Write Iops.

Policy and Recommendations

The Azure Database for PostgreSQL - Policy and Recommendations dashboard provides insights on Total Success Policy Events, Total Success Policy Events, Total Failed Policy Events, Failed Policy Events, Total Recommendation Events, and Recent Recommendation Events.

Health

The Azure Application Gateway - Health dashboard provides insights on recent alerts, resource health incidents, recent resource health status by resource name, trend by event type, downtime by causes, and trend of unavailable, degraded, and available.

Replication

The Azure Database for PostgreSQL - Replication dashboard provides insights on Average Replication Lag, Physical Replication Lag, and Logical Replication Lag.

Schema Overview

The Azure Database for PostgreSQL - Schema Overview dashboard provides insights on Indexes Scanned By Schema, Rows Inserted By Schema, Rows Updated By Schema, Rows Deleted By Schema, Dead Rows By Schema, Live Rows By Schema, Sequential Scan By Schema, and Tables Vacuumed By Schema.

Sessions

The Azure Database for PostgreSQL - Sessions dashboard provides insights on Longest Transaction Time (Sec), Oldest Backend Time (Sec), Longest Query Time (Sec), Oldest Backend Xmin (Sec), Oldest Backend Xmin Age, Application Name with Most Sessions, and Session duration distribution.

Storage Overview

The Azure Database for PostgreSQL - Storage Overview dashboard provides insights on Storage Used (Bytes), Storage Used (%), Storage Used by Transaction Logs(Bytes), Max Backup Storage Used (Bytes), Database Size (Bytes), Storage Free (Bytes), Egress (Bytes), and Ingress (Bytes).

Transactions

The Azure Database for PostgreSQL - Transactions dashboard provides insights on Transactions Per Second, Total Transactions, Transactions Commit, Transactions Rollback, Maximum Used TransactionIDs, Delete Transactions, Insert Transactions, Fetched Transactions, Returned Transactions, Returned Transactions, and Update Transactions.

Troubleshooting

HTTP Logs and Metrics Source used by Azure Functions

To troubleshoot metrics collection, follow the instructions in Collect Metrics from Azure Monitor > Troubleshooting metrics collection.

Upgrade/Downgrade the Azure Flexible Database for PostgreSql app (Optional)

To update the app, do the following:

1. Select App Catalog.
2. In the Search Apps field, search for and then select your app.
   Optionally, you can identify apps that can be upgraded in the Upgrade available section.
3. To upgrade the app, select Upgrade from the Manage dropdown.
   1. If the upgrade does not have any configuration or property changes, you will be redirected to the Preview & Done section.
   2. If the upgrade has any configuration or property changes, you will be redirected to Setup Data page.
      1. In the Configure section of your respective app, complete the following fields.
         • Key. Select either of these options for the data source.
           • Choose Source Category and select a source category from the list for Default Value.
           • Choose Custom and enter a custom metadata field. Insert its value in Default Value.
      2. Click Next. You will be redirected to the Preview & Done section.

Post-update

Your upgraded app will be installed in the Installed Apps folder, and dashboard panels will start to fill automatically.

note

See our Release Notes changelog for new updates in the app.

To revert the app to a previous version, do the following:

1. Select App Catalog.
2. In the Search Apps field, search for and then select your app.
3. To version down the app, select Revert to < previous version of your app > from the Manage dropdown.

Uninstalling the Azure Flexible Database for PostgreSql app (Optional)

To uninstall the app, do the following:

1. Select App Catalog.
2. In the 🔎 Search Apps field, run a search for your desired app, then select it.
3. Click Uninstall.