Collects PostgreSQL performance and health metrics

Prerequisites

  1. Middleware Host agent should be already installed on your machine, in order to use PostgreSQL integration.

Setup

  1. You will have to create a yaml file in your system containing database collection details, as follows:

Ex. /home/ubuntu/postgres-creds.yaml

postgresql:
    endpoint: localhost:5432
    username: postgres
    password: postgres
  1. Go to Store, Enable PostgreSQL Integration, Select a host, and set YAML path to the file created in step 1.

Visualize Analytics

There are 2 possible ways to Visualize PostgreSQL Analytics :

1. Middleware’s Default PostgreSQL Dashboard

Once you setup the PostgreSQL Integration, You will be able to see different Analytics in the default PostgreSQL Dashboard.

2. Create your own dashboard

You can create a dashboard from scratch by yourself.

If you add a new widget to any existing dashboard, you will be able to see list of available PostgreSQL metrics under the “postgres” Data source.

You can visualize in many different Widget types i.e. Timeseries, Bar Chart, Pie Chart, etc.

The exhaustive list for PostgreSQL metrics can be seen here

Alerting

You can also setup alerting for your PostgreSQL metrics from the Middleware’s UI.

There is a section in alerts dedicated to “Database” Opt for the “PostgreSQL” database and select the metrics you want to setup alerting for.

Metrics collected

MetricDescription
postgresql.backendsThe number of backends.
postgresql.bgwriter.buffers.allocatedNumber of buffers allocated.
postgresql.bgwriter.buffers.writesNumber of buffers written.
postgresql.bgwriter.checkpoint.countThe number of checkpoints performed.
postgresql.bgwriter.durationTotal time spent writing and syncing files to disk by checkpoints.
postgresql.bgwriter.maxwrittenNumber of times the background writer stopped a cleaning scan because it had written too many buffers.
postgresql.blocks_readThe number of blocks read.
postgresql.commitsThe number of commits.
postgresql.connection.maxConfigured maximum number of client connections allowed
postgresql.database.countNumber of user databases.
postgresql.db_sizeThe database disk usage.
postgresql.index.scansThe number of index scans on a table.
postgresql.index.sizeThe size of the index on disk.
postgresql.operationsThe number of db row operations.
postgresql.rollbacksThe number of rollbacks.
postgresql.rowsThe number of rows in the database.
postgresql.table.countNumber of user tables in a database.
postgresql.table.sizeDisk space used by a table.
postgresql.table.vacuum.countNumber of times a table has manually been vacuumed.