PostgreSQL Integration

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

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

  1# /etc/postgresql/14/main/postgresql.conf
  2  shared_preload_libraries = 'pg_stat_statements'
  3  pg_stat_statements.track = all
  4  pg_stat_statements.max   = 10000
  5  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):

  1ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';

• Restart PostgreSQL for the preload to take effect:

  1sudo systemctl restart postgresql

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

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

You should see a row like:

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

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

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

• Create the user

  1CREATE USER lpu WITH PASSWORD 'pass';

  Verify:

  1\du

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

  1-- allow connections to the database you'll monitor
  2GRANT CONNECT ON DATABASE <your_db_name> TO lpu;
  3
  4-- permit reading statement stats
  5GRANT SELECT ON pg_stat_statements TO lpu;
  6
  7-- permit reading global stats where needed
  8GRANT pg_read_all_stats TO lpu;

Test with the lpu account:

1SELECT calls, query FROM pg_stat_statements LIMIT 1;

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

On the host where the Middleware Host Agent runs, create a small YAML file with connection details:

1# /home/ubuntu/postgres_creds.yaml
2postgresql:
3  endpoint: localhost:5423
4  username: postgres   # or the user you created in Step 2
5  password: postgres   # or that user's password

Use the actual host/port for your deployment (the example uses localhost:5423). The file path you choose will be referenced inside Middleware in Step 5.

In Middleware, go to Installations → All Integrations → PostgreSQL. This is where you’ll bind a host and point Middleware to your credentials file.

In the PostgreSQL integration form:

• Select the host (from the dropdown) where the cred file resides.
• Enter the credentials file path (from Step 3).
• Click Save.
  

Once saved, the integration starts ingesting metrics.