Monitoring

PostgreSQL Monitoring Built-in Monitoring Views

sql

-- Database activity monitoring
SELECT * FROM pg_stat_activity;
-- Database statistics
SELECT * FROM pg_stat_database;
-- Table statistics
SELECT * FROM pg_stat_user_tables;
-- Index usage statistics
SELECT * FROM pg_stat_user_indexes;
-- Lock monitoring
SELECT * FROM pg_locks;

Performance Monitoring

sql

-- Slow query identification
SELECT query, mean_time, calls, total_time
FROM pg_stat_statements
ORDER BY total_time DESC;
-- Buffer cache hit ratio
SELECT 
    round(100.0 * sum(blks_hit) / sum(blks_hit + blks_read), 2) as cache_hit_ratio
FROM pg_stat_database;
-- Connection monitoring
SELECT count(*) as total_connections,
       count(*) FILTER (WHERE state = 'active') as active_connections,
       count(*) FILTER (WHERE state = 'idle') as idle_connections
FROM pg_stat_activity;

Log Analysis

bash

# Enable query logging
log_statement = 'all'
log_duration = on
log_min_duration_statement = 1000
# Monitor PostgreSQL logs
sudo tail -f /var/log/postgresql/postgresql-17-main.log
# Parse slow queries
grep "slow query" /var/log/postgresql/postgresql-17-main.log

Third-party Monitoring

yaml

# Prometheus PostgreSQL Exporter
version: '3.8'
services:
  postgres-exporter:
    image: prometheuscommunity/postgres-exporter:latest
    ports:
      - "9187:9187"
    environment:
      - DATA_SOURCE_NAME=postgresql://postgres:password@localhost:5432/postgres?sslmod