---
title: "Set Up Database Monitoring for PostgreSQL"
description: "Prepare PostgreSQL for Middleware Database Monitoring. Enable pg_stat_statements, create a monitoring user, and turn on query sample, top query, and schema collection."
section: "APM"
breadcrumb: "APM > Database Monitoring > Set Up PostgreSQL"
canonical: "https://docs.middleware.io/get-started/database/postgresql"
---
# Set Up Database Monitoring for PostgreSQL

This guide prepares a PostgreSQL server for [Database Monitoring](/features/apm/database-monitoring) and turns on the collections that feed Query Metrics, Samples, Explain Plans, and Schemas. For every connection and TLS option in the integration form, see the [PostgreSQL Integration](/integrations/postgresql-integration) page.

## Prerequisites

1. **Middleware Agent** (latest version) installed on a host or Kubernetes cluster that can reach the PostgreSQL endpoint. See [Installing the Agent](/get-started/agent).
2. **Superuser access** to PostgreSQL, so you can change server settings and create a monitoring user.
3. **PostgreSQL 13 or later** is recommended. Older versions work with reduced data:

| Data | Minimum PostgreSQL version |
| --- | --- |
| Explain plans for queries with parameters | 12 |
| Execution and planning times from `pg_stat_statements` | 13 |
| Query ID on query samples | 14 |

## Setup

#### 1 Enable pg_stat_statements

`pg_stat_statements` provides the per-query statistics and explain plan candidates used by **Top Query Collection**.

Add these settings to `postgresql.conf` (for example, `/etc/postgresql/16/main/postgresql.conf`):

```bash
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000
track_io_timing = on
```

`track_io_timing` records block read and write time. Without it, the **Avg Block Read Time** and **Avg Block Write Time** charts stay at zero.

Restart PostgreSQL so the library loads:

```bash
sudo systemctl restart postgresql
```

Then create the extension **in every database you want to monitor**:

```sql
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
```

> **Warning:** `pg_stat_statements` is installed per database. If the extension is missing from all monitored databases, the agent turns off top query collection for that server, and explain plans do not appear.

Verify that statistics are being recorded:

```sql
SELECT calls, query FROM pg_stat_statements LIMIT 1;
```

#### 2 Create a Monitoring User

Create a dedicated user for the agent. The `pg_monitor` role lets the agent read every session in `pg_stat_activity`, which query samples require.

```sql
CREATE USER middleware_user WITH PASSWORD '<strong-password>';
GRANT pg_monitor TO middleware_user;

-- Repeat for each database you monitor
GRANT CONNECT ON DATABASE <database_name> TO middleware_user;
```

To collect **explain plans**, the user must also be able to read the tables your queries use. Run this in each monitored database, for each schema:

```sql
GRANT USAGE ON SCHEMA public TO middleware_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO middleware_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO middleware_user;
```

The agent runs `EXPLAIN`, not `EXPLAIN ANALYZE`, so queries are planned but never executed. Plans for `INSERT`, `UPDATE`, and `DELETE` statements need the matching privilege on the table. Without it, those queries have no plan, and every other feature keeps working.

#### 3 Turn On Collection in the PostgreSQL Integration

In Middleware, go to **Installation → All Integrations → PostgreSQL**. Enter the endpoint, database, and the `middleware_user` credentials as described in the [PostgreSQL Integration](/integrations/postgresql-integration) guide, then turn on:

- **Query Sample Collection**: samples active sessions from `pg_stat_activity`.
- **Top Query Collection**: collects statistics from `pg_stat_statements` and explain plans for the top queries. Use **Collect Top N Queries** to limit how many queries are tracked.
- **Schema Collection**: collects table and index definitions. Use **Exclude Schemas** to skip schemas you do not need.

Optionally, use **Databases to monitor** and **Databases to exclude** to limit which databases on the server are collected. Leave both empty to monitor every database.

Click **Save**. The agent picks up the new configuration on its next configuration check.

#### 4 Verify

Generate some database traffic, then open **APM → Database**:

1. The instance appears on the **Databases** tab under **PostgreSQL**.
2. **Query Metrics** and **Samples** list your queries.
3. Opening a query shows a plan in **Explain Plans**.
4. **Schemas** lists your tables. Schema data is refreshed periodically, so the first tables can take a few minutes to appear.

## What Each Collection Powers

| Collection | Source | Used by |
| --- | --- | --- |
| Metrics (always on) | PostgreSQL statistics views | Instance list, instance **Summary** and **Metrics** tabs |
| Query Sample Collection | `pg_stat_activity` | **Query Metrics**, **Samples**, Average Load, wait events, blocking queries, Calling Services, APM trace correlation |
| Top Query Collection | `pg_stat_statements` and `EXPLAIN` | **Explain Plans**, per-query block I/O, rows, and read/write time charts |
| Schema Collection | System catalogs and table statistics | **Schemas** tab and the instance **Schema** section |

## Managed PostgreSQL

Managed services do not allow editing `postgresql.conf` directly. Enable `pg_stat_statements` and `track_io_timing` through the provider's parameter settings, then run the agent on a VM that can reach the database:

- [AWS RDS PostgreSQL](/integrations/aws-rds-integration): DB parameter group
- [Azure Database for PostgreSQL](/integrations/azure-managed-postgresql): server parameters
- [GCP Cloud SQL for PostgreSQL](/integrations/gcp-cloudsql-postgresql): database flags

After that, follow steps 2 to 4 above.

## Troubleshooting

#### The instance does not appear in Database Monitoring

Check that the PostgreSQL integration is saved and the agent host can reach the endpoint. Confirm that your role has the Database Monitoring permission. If the page shows "Ready to Launch?", no PostgreSQL integration has been configured for this account yet.

#### Query Metrics and Samples are empty

Turn on Query Sample Collection in the integration. Make sure the monitoring user has the pg_monitor role. Without it, PostgreSQL hides other users' query text as "insufficient privilege", and the agent skips those sessions.

#### Explain Plans shows 'No explain plan found for this query in the selected time range'

Turn on Top Query Collection and confirm pg_stat_statements exists in that database. Grant USAGE and SELECT on the schemas the query uses. Plans are collected for SELECT, INSERT, UPDATE, DELETE, WITH, and TABLE statements that rank among the top queries. Widen the time range if the query runs rarely.

#### Avg Block Read Time and Avg Block Write Time are always zero

Set track_io_timing = on in postgresql.conf (or in your provider's parameters) and reload PostgreSQL.

#### The Schemas tab is empty

Turn on Schema Collection in the integration and check that the schema is not listed in Exclude Schemas. The Schemas tab always shows the past day of data.

#### The CPU Utilization chart is missing from the instance summary

The chart uses host metrics from the Middleware Agent running on the database server itself. It is hidden when the database host is not monitored by the agent, for example for managed databases or database pods in Kubernetes.

#### Calling Services and Traces are empty

Your application must add trace context to its SQL queries. See [Connect with APM Traces](/features/apm/database-monitoring/connect-apm-traces).

Need assistance or want to learn more about Middleware? Get in touch with us via our Contact Us page or join our Slack channel.