How to Monitor PostgreSQL Database

Overview

PostgreSQL (Postgres) is a powerful open-source relational database known for its extensibility and SQL compliance. Xitoring's PostgreSQL integration provides comprehensive monitoring of performance, connections, query statistics, and resource usage — helping you keep your databases healthy and performant.

What Can It Monitor?

CPU — Database server CPU utilization
Memory — RAM consumed by PostgreSQL processes
Active Connections — Currently executing queries
Idle Connections — Open connections waiting for queries
Cache Hit Ratio — Percentage of reads served from shared buffers
Total Disk Space — Storage used by databases
Database Deadlocks — Deadlock detection count
Active Queries / Waiting Queries — Query execution states
Committed / Rollback Transactions — Transaction outcome rates
Read Disk Blocks — Physical disk reads (cache misses)
Rows Returned / Selected / Inserted / Updated / Deleted — Row-level operation rates

Prerequisites

1. Create a Monitoring User

Connect to PostgreSQL and run:

CREATE USER xitogent WITH PASSWORD 'YOUR_PASSWORD';
GRANT pg_monitor TO xitogent;
GRANT SELECT ON pg_stat_database TO xitogent;

Verify the user can connect:

psql -h localhost -U xitogent postgres -c "select * from pg_stat_database LIMIT(1);"

2. Enable Performance Tracking

Edit your postgresql.conf file:

Linux: /etc/postgresql/14/main/postgresql.conf
Windows: C:\Program Files\PostgreSQL\16\data\postgresql.conf

Add or modify these settings:

track_activities = on
track_counts = on
track_io_timing = on
track_functions = all
shared_preload_libraries = 'pg_stat_statements'

Restart PostgreSQL, then enable the extension:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Verify with:

SELECT * FROM pg_stat_statements LIMIT 1;

How to Activate the Integration

Run the Xitogent CLI:

xitogent integrate

Select PostgreSQL from the list. Provide:

Host (typically localhost)
Port (default: 5432)
Username and password
SSL mode

Xitogent tests the connection and completes setup automatically. Within a minute, graphs and data appear on your server page.

Supported versions: PostgreSQL 12, 13, 14, 15, 16 on both Linux and Windows.

Setting Up Triggers

Available trigger parameters:

CPU / Memory
Active Connections / Idle Connections
Cache Hit Ratio
Total Disk Space / Database Deadlocks
Active Queries / Waiting Queries
Committed / Rollback Transactions
Read Disk Blocks
Row operation rates (returned, selected, inserted, updated, deleted)

Navigate to Triggers on your server page, select PostgreSQL, choose a metric, and configure thresholds.

Tips

Enable pg_stat_statements for the most detailed query-level insights
Monitor Cache Hit Ratio — it should stay above 95% for optimal performance
Set alerts on Deadlocks to catch concurrency issues early
Track Idle Connections to detect connection pool leaks
Use Rows Returned vs Rows Selected ratio to find inefficient queries