PostgreSQL

Monitor and optimize your PostgreSQL databases

Overview

DB24x7 provides comprehensive monitoring for PostgreSQL databases, including query performance analysis, slow query detection, index recommendations, and real-time metrics tracking.

Supported Versions

PostgreSQL 12+

Real-time Monitoring

Live query tracking

Query Analysis

Performance insights

Cloud Support

AWS RDS, Azure, GCP

Prerequisites

PostgreSQL 12 or higher installed
pg_stat_statements extension enabled
Network access from DB24x7 collector
User account with monitoring privileges

Enable pg_stat_statements

The pg_stat_statements extension is required for query performance tracking.

Step 1: Edit postgresql.conf

# Add to postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000

Step 2: Restart PostgreSQL

sudo systemctl restart postgresql

Step 3: Create the extension

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Create Monitoring User

Create a dedicated user with minimal required permissions for monitoring.

-- Create monitoring user
CREATE USER db24x7_monitor WITH PASSWORD 'your_secure_password';

-- Grant connection privileges
GRANT CONNECT ON DATABASE your_database TO db24x7_monitor;

-- Grant usage on schema
GRANT USAGE ON SCHEMA public TO db24x7_monitor;

-- Grant select on system catalogs and statistics views
GRANT pg_read_all_stats TO db24x7_monitor;

-- Allow reading pg_stat_statements
GRANT EXECUTE ON FUNCTION pg_stat_statements_reset() TO db24x7_monitor;

-- For PostgreSQL 14+, grant monitoring role
GRANT pg_monitor TO db24x7_monitor;

Note

For PostgreSQL versions below 14, use pg_read_all_stats instead of pg_monitor.

Connection String Format

Use the following connection string format to connect DB24x7 to your PostgreSQL database:

Standard Connection String

postgresql://db24x7_monitor:your_secure_password@hostname:5432/database_name?sslmode=require

AWS RDS Connection String

postgresql://db24x7_monitor:password@mydb.abc123.us-east-1.rds.amazonaws.com:5432/postgres?sslmode=require

Connection Parameters

hostname	Database server address
5432	Default PostgreSQL port
sslmode	require, verify-ca, or verify-full
connect_timeout	Connection timeout in seconds

PostgreSQL-Specific Features

Query Performance

EXPLAIN plan analysis
Slow query detection
Query execution statistics

Index Optimization

Unused index detection
Missing index suggestions
Index bloat analysis

Connection Monitoring

Active connection tracking
Connection pool analysis
Long-running query alerts

Replication Monitoring

Replication lag tracking
Streaming replication status
WAL archive monitoring

Troubleshooting

Connection Failed

If you cannot connect to PostgreSQL, verify:

•Check pg_hba.conf allows connections from DB24x7 IP
•Verify listen_addresses in postgresql.conf
•Ensure firewall allows port 5432
•Verify user credentials are correct

pg_stat_statements Not Available

If the extension is not working:

-- Verify extension is installed
SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';

-- Check if module is loaded
SHOW shared_preload_libraries;

-- Verify statistics are being collected
SELECT count(*) FROM pg_stat_statements;

Permission Denied Errors

Verify the monitoring user has all required permissions:

-- Check user roles
SELECT rolname, rolsuper, rolinherit, rolcreaterole
FROM pg_roles
WHERE rolname = 'db24x7_monitor';

-- Verify pg_monitor membership (PostgreSQL 14+)
SELECT pg_has_role('db24x7_monitor', 'pg_monitor', 'member');

Next Steps

Once your PostgreSQL database is connected, you can:

Analyze Queries

Learn how to analyze slow queries and optimize performance

Set Up Alerts

Configure alerts for critical database metrics