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 instructions below apply 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.
Activity logs. Provides insight into any subscription-level or management group-level events that have occurred in Azure. To learn more, refer to the Azure 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 Azure Metrics Source.

You must explicitly enable diagnostic settings for each Azure Database for the 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 metrics collection

To set up the Azure Metrics source in Sumo Logic, refer to Azure Metrics Source.

Configure logs collection

Diagnostic logs

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

To set up the Azure Event Hubs source in Sumo Logic, refer to the Azure Event Hubs Source for Logs.
To create the diagnostic settings in the 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.
Set server parameters as given below:
- wal_level. Set to logical.
- log_statement_stats. Set to ON.
- log_statement. Select ALL.
- log_lock_waits. Set to ON. Set deadlock_timeout
- log_recovery_conflict_waits. Set to ON.
Tag the location field in the source with the 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, do the following:

note

Next-Gen App: To install or update the app, you must be an account administrator or a user with Manage Apps, Manage Monitors, Manage Fields, Manage Metric Rules, and Manage Collectors capabilities depending upon the different content types part of the app.

Select App Catalog.
In the 🔎 Search Apps field, run a search for your desired app, then select it.
Click Install App.
note
Sometimes this button says Add Integration.
Click Next in the Setup Data section.
In the Configure App section of your respective app, complete the following field.
1. Index. Specify value for _index if the collection is configured with custom partition. Learn more. Default value is set to sumologic_default (default partition)
Click Next. You will be redirected to the Preview & Done section.

Post-installation

Once your app is installed, it will appear in your Installed Apps folder, and dashboard panels will start to fill automatically.

Each panel slowly fills with data matching the time range query received since the panel was created. Results will not immediately be available but will be updated with full graphs and charts over time.

As part of the app installation process, the following fields will be created by default:

tenant_name. This field is tagged at the collector level. You can get the tenant name using the instructions here.
location. The region 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 an Azure resource.
service_name. Services that can be accessed with an Azure resource (for example, in Azure Container Instances the service is Subscriptions).

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.
Many of the Next-Gen apps allow you to provide the Index at the installation time and a default value for this key (sumologic_default). Based on your input, the app dashboards will be parameterized with a dashboard variable, allowing you to change the data partition queried by all panels. This restricts the query scope of all the dashboard queries to a specific data partition.

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