MetricHarborDocs

Monitoring PostgreSQL

MetricHarbor reads PostgreSQL's own statistics views. It needs a login role with the pg_monitor grant and no table privileges at all — it never reads a row of your data.

1. Create the monitoring role

DROP ROLE IF EXISTS metricharbor;
CREATE ROLE metricharbor LOGIN;
\password metricharbor
GRANT pg_monitor TO metricharbor;

The DROP makes this safe to re-run: CREATE ROLE on an existing role fails, and re-running a block after a typo is the normal case rather than the exception. PostgreSQL refuses to drop a role that still owns objects, so this cannot quietly take anything with it.

Why \password and not PASSWORD 'secret'

\password prompts without echoing and hashes the password in the client, so the cleartext is never part of a statement you type. A statement you type is a statement psql appends to ~/.psql_history — and unlike the MySQL client, which drops password-bearing statements from its history, psql keeps them verbatim. This was found the obvious way, in a real /var/lib/pgsql/.psql_history:

CREATE ROLE metricharbor LOGIN PASSWORD 'the-actual-password';

It is the same rule the agent follows for its own credentials, for the same reason: a secret given as an argument ends up somewhere it outlives its usefulness. If you have already run the literal form, change the password with \password and then clear the history file — the old one is on disk until you do, and it travels into every backup of that machine taken since.

That is the whole grant. pg_monitor is a built-in role (PostgreSQL 10 and later) that bundles exactly three things — pg_read_all_settings, pg_read_all_stats and pg_stat_scan_tables — which is the full set the collector needs and nothing more.

Verified on PostgreSQL 18.4: every query in the table below succeeds under this grant, and SELECT against a real table is still refused with permission denied.

Why not less. Without pg_monitor, pg_stat_activity shows only the monitoring role's own queries and the replication views are refused. The collector then reports partial data rather than failing, which is the more confusing outcome: connection counts that are always near zero and look plausible.

Why not more. You do not need SUPERUSER, and you should not use it. Nothing here requires it, and a monitoring credential on a host you do not fully control is the wrong place for one.

2. Let the agent reach it

If the agent runs on the database host, keep PostgreSQL on loopback:

# postgresql.conf
listen_addresses = 'localhost'

# pg_hba.conf
host    all    metricharbor    127.0.0.1/32    scram-sha-256

Use scram-sha-256 rather than trust, even in a test environment. A setup that works only because authentication is switched off tells you nothing about whether the credential plumbing is right, and you will find out in production instead.

Reload: systemctl reload postgresql.

3. Supply the password to the agent

runuser -u metricharbor -- metricharbor-agent cred add postgres-monitor --type database_password

You are prompted for the username (metricharbor) and the password. See Credentials.

4. Create the target and add the check

In the web interface:

  1. Targets → Add target → PostgreSQL database. Host 127.0.0.1, port 5432, the database name, and the credential you just declared. Assign the agent on that host — a target with no agent collects nothing.
  2. On the target page → Add a check → PostgreSQL.
  3. Wait one config poll. The agent pulls its configuration; MetricHarbor never pushes.

The target asks for a database name, unlike the MySQL one. That is because pg_stat_user_tables is per-database: the per-table statistics you get are for the database the collector connects to.

What it reads

Exactly these, and nothing else:

Source For
pg_stat_activity connections, active, idle-in-transaction, waiting on locks, longest transaction
pg_stat_database commits, rollbacks, blocks read and hit, deadlocks, temp files
pg_stat_user_tables sequential and index scans, rows, dead tuples
pg_settings max_connections, for the usage percentage
pg_replication_slots inactive slots — the ones that quietly consume WAL
pg_database_size() size per database
pg_is_in_recovery() whether this instance is a replica
pg_postmaster_start_time() uptime

All catalog and statistics views. No table contents, which is why per-table statistics cost you nothing in data access.

Options on the check

Option Default Notes
Per-database statistics on Turn it off on an instance with hundreds of databases, where iterating them is the expensive part.
Statement timeout 10s Set on the connection, so a pathological query cannot make collection hang.

What you should see

On the target page, Recent collections shows success with a sample count, and Latest readings shows one row per label set — one per database for the per-database metrics, one per table for table statistics.

If it is not working:

Error Cause
authentication naming your credential The agent has no secret under that name, or the name does not match exactly.
connection naming the host listen_addresses, pg_hba.conf, a firewall, or the wrong port.
permission_denied The pg_monitor grant is missing.
success but suspiciously few connections Likely pg_monitor missing: pg_stat_activity is showing the monitoring role its own session only.

More in When nothing is collecting.