> ## Documentation Index
> Fetch the complete documentation index at: https://docs.germainux.com/llms.txt
> Use this file to discover all available pages before exploring further.

# PostgreSQL Database Monitoring

> Catch connection leaks, deadlocks, table bloat and runaway scans on your PostgreSQL servers before your applications slow down.

Components
[`Engine`](https://docs.germainux.com/deploy/germain-ux-engine)

Availability
8.6.15 or later

> [card] [What Is Monitored](#what-is-monitored)
>
> Connections, rows, scans, I/O

> [card] [How It Works](#how-it-works)
>
> One monitor on the Engine

> [card] [Deploy](#deploy)
>
> Data source and profile

> [card] [KPIs](#kpis)
>
> 15 database KPIs

## Overview

GermainUX watches each PostgreSQL server your applications depend on and tells you when it's in trouble. You see open connections climb toward `max_connections`, deadlocks and dead rows pile up behind a stuck transaction, and sequential scans take over after an index goes missing. The **PostgreSQL Overview** dashboard and default SLAs on availability, connections and deadlocks work as soon as the monitor runs. Your team finds the database cause of a slow application without logging in to `psql` on every server.

This page covers PostgreSQL databases that you monitor. To use PostgreSQL as the GermainUX datastore, see [PostgreSQL Database Setup](https://docs.germainux.com/deploy/deployment-on-premise/postgresql-database.md).

> [card] Connection Pressure
>
> The number of open backend connections, with an SLA that fires before the server refuses new clients.

> [card] Locks and Bloat
>
> Deadlocks, dead rows and HOT updates show a long transaction or a vacuum that can't keep up.

> [card] Workload Shape
>
> Rows returned, fetched, inserted, updated and deleted per second, so a runaway query or a bulk load stands out.

> [card] Scans and I/O
>
> Sequential against index scans, temporary files written, and the time spent reading and writing blocks.

### Questions It Answers

* Is the PostgreSQL server up, and since when was it unreachable?
* Are we running out of connections, and on which server?
* Did a long transaction cause deadlocks or a pile of dead rows?
* Which server suddenly reads far more rows than usual?
* Did sequential scans jump after a deployment or a dropped index?
* Are queries spilling to temporary files or waiting on disk I/O?

## What Is Monitored

| Area | What GermainUX monitors |
| - | - |
| **Availability** | Whether the Engine can connect to the database on each poll. When it can't, GermainUX stores the connection error. |
| **Connections** | The number of backend connections open on the server |
| **Row throughput** | Rows returned, fetched, inserted, updated and deleted per second |
| **Contention and bloat** | Deadlocks, dead rows waiting for vacuum, and HOT (heap-only tuple) updates |
| **Access paths** | Sequential scans and index scans |
| **Temporary files and I/O** | Bytes written to temporary files, and the time spent reading and writing data blocks |
| **Custom queries** | Any value a SQL query returns, as its own KPI, with [query monitoring](https://docs.germainux.com/analytics/query-monitoring.md) |
| **Host** | CPU, memory, disk and network of the database server, and whether the `postgres` process is running. See [Linux monitoring](https://docs.germainux.com/integrations/linux-redhat-suse-monitoring.md) or [Windows monitoring](https://docs.germainux.com/integrations/windows-monitoring.md). |

## Analytics, Alerts and Automation

### Root Cause Analysis

Open a KPI in the [Analysis](https://docs.germainux.com/dashboards/analytics-dashboard/4-analysis-dashboard.md) screen to compare the selected period with the previous days, and see which servers and databases changed most. In the example below, connections on **pg-prod-01** climb past the SLA threshold of `1000` because an application leaks connections. The gap that follows is the time when the server refused new clients, the monitor included.

![Analysis screen for the Database Connections KPI, with connections on pg-prod-01 climbing past 1000 and then a gap while the server refuses new connections](https://mintcdn.com/germain-ux/e-lndhWzuMC2fgBs/images/main/postgresql-monitoring/postgresql-connections-analysis.png?fit=max\&auto=format\&n=e-lndhWzuMC2fgBs\&q=85\&s=190a294687efdb0fc44ae2b25fc9f3f5)

Analysis of Database Connections - GermainUX

### AI Analysis

Ask [AI Explore](https://docs.germainux.com/analytics/ai-driven-insights.md) questions such as "When did connections on pg-prod-01 last pass 900?" and get the answer without building a report. [Outlier detection](https://docs.germainux.com/analytics/outlier-detection.md) flags unusual spikes in row rates, scans or temporary files.

### Dashboards and Reports

GermainUX ships the **PostgreSQL Overview** dashboard. In the left menu, go to **Dashboards > All**, and then open **PostgreSQL Overview** in the **PostgreSQL** folder. It shows database availability, then one trend chart for each of the other KPIs: connections, deadlocks, the five row rates, HOT updates, dead rows, temporary files, read and write time, and sequential and index scans.

![PostgreSQL Overview dashboard with database availability and trend charts for connections, deadlocks, row rates, dead rows, temporary files, I/O time and scans, during a long transaction on pg-prod-01](https://mintcdn.com/germain-ux/e-lndhWzuMC2fgBs/images/main/postgresql-monitoring/postgresql-overview-dashboard.png?fit=max\&auto=format\&n=e-lndhWzuMC2fgBs\&q=85\&s=a66c5b4b888c7cc39e2375310c87e5ec)

PostgreSQL Overview Dashboard - GermainUX

Click a chart to open the KPI behind it in the [Analysis](https://docs.germainux.com/dashboards/analytics-dashboard/4-analysis-dashboard.md) screen, or build your own [dashboards and reports](https://docs.germainux.com/dashboards/dashboard-and-report.md) on the same KPIs.

### SLAs and Alerts

Three KPIs come with a default [SLA](https://docs.germainux.com/analytics/slas.md), evaluated per database data source:

| KPI | OK when |
| - | - |
| **Database Availability** | `97.5` % or more |
| **Database Connections** | Less than `1000` |
| **Database Deadlocks** | Less than `1` |

Change the thresholds to match your servers, for example a connection threshold below your `max_connections`. Add SLAs to the other KPIs, and get an [alert](https://docs.germainux.com/automation/notifications.md) by email, SMS, Slack or Teams when one is breached.

### Correlation with Other Technologies

[Correlate](https://docs.germainux.com/analytics/correlation-and-tracing.md) PostgreSQL KPIs with the application servers and user sessions that use the database, so you can tell whether a slow page comes from the database or from somewhere else.

## How It Works

pg\_stat views
PostgreSQL monitor
KPIs
SLAs
Alerts

GermainUX monitors PostgreSQL from a GermainUX Engine over JDBC. You don't install anything on the database server.

| Component | What it does |
| - | - |
| **Database Monitor - PostgreSQL** | Connects to the database on a fixed interval, reads `pg_stat_database` and `pg_stat_all_tables`, and sends availability and the metrics. Row rates and counts are the change since the previous poll, so they start with the second poll. |
| **PostgreSQL Database Server** profile | Deploys the monitor to the Engine of the host you apply it to, with the database and credentials you choose |

Each poll runs two short queries against these statistics views, so the monitor adds very little load to the server.

## Before You Start

* A GermainUX Engine is deployed and configured for the PostgreSQL host in **Settings > Data Sources**. See [GermainUX Engine](https://docs.germainux.com/deploy/germain-ux-engine.md).
* The Engine can reach the PostgreSQL port, `5432` by default, and `pg_hba.conf` allows connections from the Engine host.
* A PostgreSQL login with `CONNECT` on the database. The statistics views GermainUX reads are readable by every user by default.
* The PostgreSQL JDBC driver jar (`postgresql-<version>.jar`) from [jdbc.postgresql.org](https://jdbc.postgresql.org/download/). GermainUX doesn't ship it.
* For **Database Read Time** and **Database Write Time**, `track_io_timing = on` in `postgresql.conf`. Without it, both stay at `0`.

## Deploy

**1. Add the JDBC driver to the Engine**

Copy the PostgreSQL JDBC driver jar into the `lib` folder of the GermainUX Engine installation, the folder for third-party jars such as JDBC drivers. Then restart the Engine.

**2. Add the database credentials**

In the left menu, go to **Settings > Data Sources > Credentials**, and add the user name and password of the PostgreSQL login.

**3. Add PostgreSQL as a data source**

In the left menu, go to **Settings > Data Sources**. On the **Databases** tab, click **Add New Configuration**, and fill in the fields:

| Field | Value |
| - | - |
| **Name** | A name you recognize, for example `orders on pg-prod-01` |
| **Application** | The application that uses the database |
| **Host** | The PostgreSQL server |
| **Vendor** | `PostgreSQL` |
| **Port** | The PostgreSQL port, for example `5432` |
| **Database Name** | The database to connect to, for example `orders` |

Save the data source.

![Database data source form with PostgreSQL selected as the Vendor, host pg-prod-01, port 5432 and the database name inventory](https://mintcdn.com/germain-ux/e-lndhWzuMC2fgBs/images/main/postgresql-monitoring/database-data-source-postgresql.png?fit=max\&auto=format\&n=e-lndhWzuMC2fgBs\&q=85\&s=b646b0fcef9359a6428d92e27ba18e65)

Database Data Source for PostgreSQL - GermainUX

**4. Apply the PostgreSQL profile to the host**

On the **Hosts** tab, select the PostgreSQL host, and then click the **Apply Profile** icon. Select **PostgreSQL Database Server**, click **Apply**, and fill in the fields:

| Field | Value |
| - | - |
| **Application** | The application the data belongs to |
| **Credentials** | The credentials you added |
| **Database** | The data source you added |

Finish the dialog. GermainUX deploys the monitor to the Engine of that host and starts polling.

![Apply Profile dialog with the PostgreSQL Database Server profile and its Application, Credentials and Database fields](https://mintcdn.com/germain-ux/e-lndhWzuMC2fgBs/images/main/postgresql-monitoring/apply-profile-postgresql.png?fit=max\&auto=format\&n=e-lndhWzuMC2fgBs\&q=85\&s=c8cac3012fd27f837d9fc6067974d177)

Apply the PostgreSQL Database Server Profile - GermainUX

Repeat the last two steps for each PostgreSQL server, for example a primary and its standby.

## Validate

**1. Check availability**

In the left menu, go to **Dashboards > All**, and open **PostgreSQL Overview** in the **PostgreSQL** folder. After the first poll, **Database Availability** shows the database as up.

**2. Check the metrics**

After the second poll, the trend charts show data. Run a query that reads a table in the monitored database, such as `SELECT count(*) FROM <table>;`, and check that **Database Return Rate** or **Database Sequential Scans** rises.

## Troubleshooting

> [accordion] Applying the profile fails with "Unable to identify engine for server"
>
> No GermainUX Engine is configured for the host you selected. Deploy an Engine for that host, or apply the profile to a host that has one, and try again.

> [accordion] Database Availability shows the database as down
>
> GermainUX stores the connection error with each failed check, in the details of the **Database Availability** KPI. The usual causes are a missing JDBC driver in the Engine's `lib` folder, a `pg_hba.conf` entry that doesn't allow the Engine host, a firewall on the PostgreSQL port, or a wrong password in the credentials.

> [accordion] Database Read Time and Database Write Time stay at 0
>
> PostgreSQL records block I/O time only when `track_io_timing` is on. Set `track_io_timing = on` in `postgresql.conf`, and reload the configuration.

## Security and Privacy

PostgreSQL monitoring collects statistics, not table data or query text. It includes:

* **Infrastructure data**: the host name of the database server and the name of the data source
* **Connection errors**: when the database is down, the error message, which can contain the host, its address and the monitoring user name. Treat it as possibly containing personal or customer data.

To mask, anonymize or exclude this data, see [Data Privacy](https://docs.germainux.com/management/compliance-privacy-and-security/data-privacy.md).

## KPIs

| Group | KPI | What it holds |
| - | - | - |
| **Availability** | **Database Availability** | `100` when the Engine connects, `0` when it can't |
| **Connections** | **Database Connections** | Backend connections open on the server |
| **Row throughput** | **Database Return Rate** | Rows returned by queries, per second |
| | **Database Fetch Rate** | Rows fetched by queries, per second |
| | **Database Insert Rate** | Rows inserted, per second |
| | **Database Update Rate** | Rows updated, per second |
| | **Database Delete Rate** | Rows deleted, per second |
| **Contention and bloat** | **Database Deadlocks** | Deadlocks since the previous poll |
| | **Database Dead Rows** | Dead rows waiting for vacuum |
| | **Database Hot Updates** | HOT updates since the previous poll |
| **Access paths** | **Database Sequential Scans** | Sequential scans since the previous poll |
| | **Database Index Scans** | Index scans since the previous poll |
| **Temporary files and I/O** | **Database Temp Written** | Bytes written to temporary files since the previous poll |
| | **Database Read Time** | Milliseconds spent reading data blocks since the previous poll |
| | **Database Write Time** | Milliseconds spent writing data blocks since the previous poll |

All 15 KPIs use the `GenericMetric` data model, and other database integrations share some of them. See [SLAs and Alerts](#slas-and-alerts) for the default SLAs, and [KPIs](https://docs.germainux.com/analytics/kpis.md) for working with KPIs.

## Related Documentation

> [card] [Query Monitoring](https://docs.germainux.com/analytics/query-monitoring.md)
>
> KPIs from your own SQL

> [card] [GermainUX Engine](https://docs.germainux.com/deploy/germain-ux-engine.md)
>
> Where the monitor runs

> [card] [Analytics](https://docs.germainux.com/analytics.md)
>
> More Analytics Features

## Get Help

The GermainUX team can help you monitor PostgreSQL clusters with many databases, or add KPIs from your own statistics queries.

> [card] [Contact GermainUX Support](https://docs.germainux.com/getting-started/support.md)
>
> The GermainUX team helps you get up and running faster, empower your team, turn data into actionable insights, and optimize performance to deliver measurable business outcomes.

> [card] Can't Find What You Need?
>
> Search all GermainUX docs with **Ctrl + K**, or use the feedback buttons at the bottom of this page to tell us what is missing.

The content of this document is proprietary to GERMAIN SOFTWARE.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.