Use the troubleshooting guides in Azure Database for PostgreSQL flexible server

This article covers the mechanics that apply to all six guides: enabling the telemetry they read, opening a guide, working through it, and resolving the empty charts that come from missing data. For what each guide is for, see What are the troubleshooting guides? For chart-by-chart interpretation, see the individual guide articles.

flowchart LR
    A[1 Enable telemetry] --> B[2 Open the guide]
    B --> C[3 Set the time range]
    C --> D[4 Clear the banners]
    D --> E[5 Work the tabs in order]
    E --> F[6 Apply a fix]
    F --> G[7 Re-check]
    G -->|Still unresolved| E

Prerequisites

Enable telemetry before you need it. Data starts accumulating only from the moment a source is turned on, so enabling it during an incident doesn't help you investigate what already happened. Treat this condition as day-one server configuration.

Two places to configure things:

1. Diagnostic settings, to route log categories to a Log Analytics workspace. Follow Configure and access logs.
2. Server parameters, to enable Query Store, enhanced metrics, and individual parameters. See Server parameters.

What each guide requires

For information about what each source contains and how much you can trust it, see Telemetry reference.

Viewing queries through native logging

Query Store is the primary source of query data on both primaries and read replicas. Native logging is an alternative view of the same queries. Use it if you want it instead of, or alongside, Query Store. It needs two parameters:

• log_line_prefix set to exactly time=%t, session=%c, pid=%p, user=%u, db=%d, client=%h, app=%a including the trailing space, because the guide parses these named fields out of your log lines. A different prefix, even one carrying the same information in another order, leaves the log-based charts empty.
• log_min_duration_statement set to a threshold in milliseconds. Don't use 0.

Open a guide

1. In the Azure portal, go to your Azure Database for PostgreSQL flexible server instance.

2. Under Monitoring in the left menu, select Troubleshooting guides.

3. Select the tab for the problem you're investigating.

4. Set the time range you want to analyze.

   Note

   One hour is the minimum. A shorter selection falls back to a one-hour capture ending at your chosen end time, so expect a wider window than you asked for.

Work through a guide

Every guide follows the same rhythm. Once you complete one, the rest are familiar.

1. Clear the banners first

Banners at the top of a guide come in two kinds, and you need to tell them apart:

• Missing data warnings, for example "Enhanced metrics are currently disabled" or "Query Store is not enabled." Resolve these warnings before reading anything else. Charts fed by a disabled source render empty, which looks identical to a healthy server.
• Detected problems, for example worker saturation, long-running transactions, or excessive logging. These problems are findings. They usually point directly at the tab you should open first.

2. Confirm the symptom and the window

The first tab always shows the metric that defines the problem. Verify there's a real spike in the window you care about, and note exactly when it starts and ends. Every subsequent tab is read against that window. If you drill into queries across a four-hour range when the spike lasted fifteen minutes, the ranking is dominated by normal traffic.

3. Separate "more work" from "less efficient work"

The Workload tab exists to answer one question: did the amount of work increase? If tuple activity rises in lockstep with the resource metric, the server is doing more legitimate work, and your options are tuning the top queries or scaling. If the resource climbed while workload stayed flat, something got less efficient: bloat, a plan change, lock contention, or a configuration change.

4. Narrow to a cause

Move outward-in through the remaining tabs: sessions, then queries, then waits, checkpoints, or configuration. Grids are interactive. Select a row to expand per-query history, or to read what a wait event means and what causes it.

5. Apply a fix, then re-check

Most tabs end with recommendations split into immediate mitigations and durable fixes. Apply one change at a time, then refresh the guide and confirm the metric moved. Changing several parameters at once makes it impossible to attribute the improvement, or the regression.

When a chart is empty or the data looks wrong

An empty chart nearly always means missing telemetry, not a healthy server. Work through these in order. They're listed roughly by how often they turn out to be the answer.

Retrieve the text of a query

For privacy, the portal shows a numeric query ID rather than SQL text. To resolve this issue:

1. Connect to the azure_sys database, where Query Store keeps its data.

   psql -h <server_name>.postgres.database.chinacloudapi.cn -U <admin_user_name> -d azure_sys
   

2. Look up the identifier shown in the guide.

   SELECT query_sql_text
   FROM query_store.query_texts_view
   WHERE query_text_id = <query_id>;
   

On a read replica

Query Store is supported on read replicas, so the preceding steps work there too. Connect to azure_sys on the replica and query query_store.query_texts_view exactly as you would on a primary.

There's a fallback for cases where that approach isn't possible, such as when you can't connect to the replica directly, or you need text for a window that already aged out of the replica's Query Store retention. In those cases, resolve text through Log Analytics instead:

1. Set pg_qs.emit_query_text = on on the replica.
2. In Diagnostic settings, enable the allLogs or audit category group.
3. Look up the identifier in the PGSQLQueryStoreQueryText table by using the KQL the guide generates.

Allow up to 30 minutes after enabling, and ensure you have at least Reader access to the workspace.

Retrieve a username or role

The portal shows a numeric role ID from pg_catalog instead of a username. Cast it to regrole to resolve it:

SELECT <role_id>::regrole;

For example, for role ID 24776:

SELECT 24776::regrole;

You can also query pg_roles directly.

Related content

• What are the troubleshooting guides?
• Telemetry reference
• Troubleshoot high CPU
• Troubleshoot high memory
• Troubleshoot high IOPS
• Troubleshoot high temporary files
• Monitor autovacuum
• Troubleshoot autovacuum blockers