PostgreSQL

The PostgreSQL Integration allows you to ingest and monitor the performance and health metrics of your object-relational database management system (ORDBMS) data. This integration is useful for tracking the total number of blocks read into memory, identifying disk space utilization per table, tallying insert operations, and rollbacks triggered.

Prerequisites

Middleware Host Agent (MW Agent) must be installed on your local machine. To install the MW Agent, see our Installation Guide.

Setup

Step 1: Enable pg_stat_statements

This is needed to collect statistics about postgresql statements.

Login to postgres shell as root user.

First we need to ensure pg_stat_statements is working. This view has the statistics about the statements executed by the postgres engine.

For this we need to modify a config file. You can find that file at: /etc/postgresql/14/main/postgresql.conf.

Add/Uncomment the following lines:

shared_preload_libraries = 'pg_stat_statements'

pg_stat_statements.track = all
pg_stat_statements.max = 10000
track_io_timing = on

With root user logged in,

ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';

Now restart the server,

sudo systemctl restart postgresql

Lets verify, As a superuser, run the following statements:

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

If you have configured your database correctly, this will return a result like this

calls | query
-------+-------
     8 | SELECT * FROM t WHERE field = ?
(1 row)

Note: This result may have different data as this is info about queries that you have run.

Step 2 (Optional): Creating a least privileged user.

Lets create a least privileged user.

CREATE USER lpu WITH PASSWORD 'pass';

Check if the user is created by running:

\du

This should give you a table where you can see:

Role name |                         Attributes                         | Member of  
-----------+------------------------------------------------------------+------------
 lpu       |                                                            | {}

Now we need our user to permission to connect to the db,

GRANT CONNECT ON DATABASE <your db name> TO lpu;

Grant permissions to read stats,

GRANT SELECT ON pg_stat_statements TO lpu;

GRANT pg_read_all_stats TO lpu;

Now login with the user we've created,

SELECT calls, query FROM pg_stat_statements LIMIT 1;

calls |                  query                  
-------+-----------------------------------------
     3 | SELECT                                 +
       |                 queryid,               +
       |                 calls,                 +
       |                 total_exec_time        +
       |                 FROM pg_stat_statements
(1 row)

Note: This result may have different data as this is info about queries that you have run.

Step 3: Create Database Credentials

Create a .yaml file containing database credentials like the below example:

/home/ubuntu/postgres_creds.yaml

postgresql:
  endpoint: localhost:5423
  username: postgres #Or the <USERNAME> created in step 2
  password: postgres #Or the <PASSWORD> created in step 2

Step 4: Access Integrations

Log in to Middleware, navigate to the Installations Page in the bottom left corner, select All Integration and click PostgreSQL

Step 5: Enable Integration

Add a host machine from the dropdown list, input the credential path from Step 1, and Save

Visualize Your Data

Default PostgreSQL Dashboard

Once the PostgreSQL integration setup is complete, a new PostgreSQL-specific dashboard will appear in the Dashboard Builder. This default dashboard serves as a jumping off point for visualizing and analyzing PostgreSQL data.

Create PostgreSQL Widget

PostgreSQL data can be added to dashboards as a custom widget. When adding a new widget, select the postgresql data source to view a full list of available PostgreSQL data.

Alerts

Alerts can be configured for any PostgreSQL metrics. When creating a new rule select the Database detection method and PostgreSQL database type for available metrics to appear in the Metrics dropdown list. Select the desired metric and continue configuring the alert conditions.

Metrics Collected

Troubleshooting

Missing Integrations Menu

If you do not see the Integrations Menu in Middlware, that means your account has not been granted Installation permissions. Contact your system administrator to add the Installation permission to your user role in Settings.

Next Steps

• How to Create Alerts
• Dashboard Basics & Customization
• Custom Telemetry Ingestion
• Getting Started With Real User Monitoring (RUM)
• Data Ingestion APIs