Skip to content

PostgreSQL Monitoring

CLI Pal can monitor your PostgreSQL databases, providing performance metrics and query analysis via pg_stat_statements.

Requirements

  • PostgreSQL 12+
  • Agent v0.1.1+

Enabling PostgreSQL Monitoring

Use the --pg-superuser-password flag:

curl -sSL https://clipal.me/install.sh | sudo bash -s -- \
  --token=YOUR_TOKEN \
  --pg-superuser-password=YOUR_POSTGRES_PASSWORD

The installer automatically:

  • ✅ Creates a clipal monitoring user with a secure random password
  • ✅ Grants the pg_monitor role for read-only access
  • ✅ Enables pg_stat_statements in postgresql.conf
  • ✅ Restarts PostgreSQL to apply changes
  • ✅ Creates the pg_stat_statements extension
  • ✅ Writes configuration to /opt/clipal/clipal.conf

Manual Configuration

If you prefer manual setup, edit /opt/clipal/clipal.conf:

# PostgreSQL Monitoring
pg_enabled=true
pg_host=localhost
pg_port=5432
pg_user=clipal
pg_password=your_password
pg_database=postgres
pg_slow_threshold_ms=200

Then restart the agent:

sudo systemctl restart clipal-agent

Manual PostgreSQL Setup

If the automatic setup didn't work, or you need to configure manually:

1. Enable pg_stat_statements

Edit postgresql.conf (location shown by SHOW config_file;):

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000

Restart PostgreSQL:

sudo systemctl restart postgresql

2. Create the Extension

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

3. Create Monitoring User

-- Create user with secure password
CREATE USER clipal WITH PASSWORD 'secure_password';

-- Grant monitoring role (PostgreSQL 10+)
GRANT pg_monitor TO clipal;

-- Grant access to pg_stat_statements
GRANT SELECT ON pg_stat_statements TO clipal;

What Gets Monitored

Metric Description
Active Connections Current database connections
Connection Utilization % of max_connections used
Cache Hit Ratio Buffer cache efficiency (target: >99%)
Transactions/sec Commits + rollbacks per second
Tuples Fetched Rows returned by queries
Dead Tuples Rows needing VACUUM

Query Statistics (via pg_stat_statements)

  • Total execution time
  • Average execution time
  • Call count
  • Rows returned
  • Shared blocks hit/read

Verify Configuration

Check the agent can connect:

# View agent logs
journalctl -u clipal-agent -f

# Should see:
# PostgreSQL: Connected successfully
# PostgreSQL: pg_stat_statements available

Test manually:

psql -h localhost -U clipal -d postgres -c "SELECT 1;"

Troubleshooting

Connection refused

  • Check PostgreSQL is running: systemctl status postgresql
  • Verify pg_hba.conf allows the clipal user
  • Check the port (default 5432)

pg_stat_statements not available

  • Ensure shared_preload_libraries = 'pg_stat_statements' is set
  • Restart PostgreSQL after changing postgresql.conf
  • Run CREATE EXTENSION pg_stat_statements; in your database

No metrics appearing

  • Check agent logs: journalctl -u clipal-agent -f
  • Verify pg_enabled=true in config
  • Test connection manually with psql

Permission denied

Grant the monitoring role:

GRANT pg_monitor TO clipal;
GRANT SELECT ON pg_stat_statements TO clipal;