PostgreSQL Integration

The PostgreSQL integration enables you to ingest performance and health metrics from your PostgreSQL instance(s) into Middleware, allowing you to track blocks read versus hits, table/index sizes, transactions (including commits/rollbacks), per-statement activity, and more.

Prerequisites#

• Middleware Host Agent is installed on the host that can reach your PostgreSQL instance. See Installing the Agent

Setup#

1 Enable pg_stat_statements#

pg_stat_statements exposes per-statement stats that the integration relies on.

• Edit postgresql.conf. (path example below; adjust for your distro/version)

  # /etc/postgresql/14/main/postgresql.conf
    shared_preload_libraries = 'pg_stat_statements'
    pg_stat_statements.track = all
    pg_stat_statements.max   = 10000
    track_io_timing          = on

  This ensures the extension is preloaded and IO timing is captured for accurate statement metrics. Save the file.
• (Alternative via SQL): As a superuser, you can also set the preload parameter from SQL (still requires a restart to take effect):

  ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';

• Restart PostgreSQL for the preload to take effect:

  sudo systemctl restart postgresql

• Verify the extension and data flow (as superuser):

  CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
  SELECT calls, query FROM pg_stat_statements LIMIT 1;

You should see a row like:

calls | query
------+-------------------------------
    8 | SELECT * FROM t WHERE field = ?

(Your values will differ. This view reflects your executed statements.)

2 (optional): Create a least-privileged user#

If you don’t want to use the superuser for monitoring, create a read-only user with just what’s needed:

• Create the user

  CREATE USER lpu WITH PASSWORD 'pass';

  Verify:

  \du

  You should see lpu in the roles listing.
• Grant minimal access

  -- allow connections to the database you'll monitor
  GRANT CONNECT ON DATABASE <your_db_name> TO lpu;
  
  -- permit reading statement stats
  GRANT SELECT ON pg_stat_statements TO lpu;
  
  -- permit reading global stats where needed
  GRANT pg_read_all_stats TO lpu;

Test with the lpu account:

SELECT calls, query FROM pg_stat_statements LIMIT 1;

(The output will reflect queries observed under your workload.)

3 Open the Integration in Middleware#

In Middleware, go to Installations → All Integrations → PostgreSQL.

4 Fill the Integration Form#

In the PostgreSQL integration form:

• Select the host (from the dropdown) where the database is reachable.
• Enter the required connection details:
  • Database name
  • Endpoint
  • Username
  • Password
• (Optional) Query Collection:
  • Query Sample Collection captures sampled query activity (query text, state, wait events, and client/application details) for troubleshooting.
  • Top Query Collection captures aggregated high-impact query metrics (calls, rows, block I/O, and planning/execution timings) from pg_stat_statements.
• (Optional) Schema Collection captures database schema and table metadata for schema visibility in Middleware.
• TLS Settings:
  • If Allow Insecure is false and Skip Certificate Verification is false, provide:
    • Client certificate file path
    • Client key file path
    • CA certificate file path

5 Save and Enable the Integration#

• Click Save.
  

Once saved, the integration starts ingesting metrics.

Visualise your Data#

Default PostgreSQL Dashboard#

After setup, Middleware adds a PostgreSQL dashboard to the Dashboard Builder. Use it as a starting point to explore key DB metrics without building widgets from scratch.

Create Custom Widgets#

When adding a widget, choose data source postgresql to browse all exposed metrics and craft charts for your SLOs or run-book checks.

Alerts#

You can alert on any PostgreSQL metric. Create a rule using Database as the detection method and PostgreSQL as the database type; the Metrics dropdown then lists all available metrics for this integration. Configure conditions, thresholds and recipients as usual.

Metrics Collected#

The following metrics are emitted when enabled in the PostgreSQL receiver (names and descriptions align with the integration’s metric definitions).

Core Engine & Storage#

Tables, Indexes & Maintenance#

TOAST#

Transactions & Row Activity#

Checkpointing & Background Writer#

Connections & Limits#

Statement-level (via pg_stat_statements)#

Replication & WAL#

Live Row Estimates#

Active Transaction Duration#

AWS RDS PostgreSQL#

If you use AWS RDS for PostgreSQL, monitoring is host-agent-based: run the Middleware Host Agent on an EC2 instance that can reach your RDS endpoint and complete the same PostgreSQL integration steps in Middleware. For RDS-specific setup (parameter groups, connection details in AWS, and networking), see the AWS RDS PostgreSQL integration.

Azure Database for PostgreSQL#

If you use Azure Database for PostgreSQL, monitoring is host-agent-based: run the Middleware Host Agent on an Azure VM that can reach your PostgreSQL server endpoint and complete the same PostgreSQL integration steps in Middleware. For Azure-specific setup (server parameters, firewall rules, and networking), see the Azure Database for PostgreSQL integration.

GCP Cloud SQL for PostgreSQL#

If you use GCP Cloud SQL for PostgreSQL, monitoring is host-agent-based: run the Middleware Host Agent on a GCE VM that can reach your Cloud SQL endpoint and complete the same PostgreSQL integration steps in Middleware. For Cloud SQL-specific setup (database flags, SSL client certificates, and networking), see the GCP Cloud SQL for PostgreSQL integration.

Troubleshooting#

“Integrations” menu not visible

If you don’t see Integrations in Middleware, your account probably lacks Installation permissions. Ask an admin to add Installation to your role in Settings.