Ubuntu

1. Create a monitoring role

CREATE USER watchlog_monitor WITH PASSWORD 'your_password';
GRANT pg_monitor TO watchlog_monitor;

-- Per-table and per-index views only describe the database you are
-- connected to, so grant CONNECT on each one you want covered.
GRANT CONNECT ON DATABASE myapp TO watchlog_monitor;
GRANT CONNECT ON DATABASE analytics TO watchlog_monitor;

pg_monitor exists from PostgreSQL 10. On 9.6 use GRANT pg_read_all_stats TO watchlog_monitor;.

Allow the role to connect in pg_hba.conf:

host    all    watchlog_monitor    127.0.0.1/32    scram-sha-256

sudo systemctl reload postgresql

2. Install pg_stat_statements (optional but recommended)

This is what unlocks the Queries and Slow Queries tabs. Watchlog will never install it for you — creating an extension changes your database.

Add to postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = top

Restart (a reload is not enough for shared_preload_libraries):

sudo systemctl restart postgresql

Then, once per database:

\c myapp
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

3. Configure the agent

Navigate to the agent config directory:

cd /opt/watchlog-agent
sudo vim integration.json

Locate the PostgreSQL object:

{ "service": "postgresql", "monitor": false, "host": "localhost", "port": "5432", "username": "", "password": "", "database": [] }

3. Change `"monitor"` from `false` to `true`:
```diff
-  "monitor": false
+  "monitor": true

Populate the fields:
- host, port, username, password
- database: list of database names, e.g. ["myapp","analytics"]
Cluster-wide metrics — connections, transactions, cache ratio, WAL, replication — need no list at all. The database array decides which databases get per-table and per-index detail, because those views only ever describe the database you are connected to.
```
{
  "service": "postgresql",
  "monitor": true,
  "host": "localhost",
  "port": "5432",
  "username": "watchlog_monitor",
  "password": "your_password",
  "database": ["myapp", "analytics"]
}
```
Save the file and reload the agent:
```
sudo pm2 reload watchlog-agent
```

Verify

Check the connection and each capability the agent probes:

PGPASSWORD='your_password' psql -h 127.0.0.1 -U watchlog_monitor -d postgres -c "
  SELECT current_setting('server_version') AS version;
  SELECT count(*) AS databases FROM pg_stat_database;
  SELECT count(*) AS locks FROM pg_locks;
  SELECT to_regclass('public.pg_stat_statements') IS NOT NULL AS pg_stat_statements;
"

Then confirm the query text is actually readable — installed is not the same as readable:

PGPASSWORD='your_password' psql -h 127.0.0.1 -U watchlog_monitor -d myapp -c "
  SELECT left(query, 40) FROM pg_stat_statements LIMIT 1;
"

If that returns <insufficient privilege>, the role is missing pg_read_all_stats — grant pg_monitor.

TLS

Set "ssl": true:

{
  "service": "postgresql",
  "monitor": true,
  "host": "pg.internal",
  "port": "5432",
  "username": "watchlog_monitor",
  "password": "your_password",
  "database": ["myapp"],
  "ssl": true
}

To require it for this role in pg_hba.conf:

hostssl    all    watchlog_monitor    10.0.0.0/8    scram-sha-256

Slow queries

There is no log file to enable. Watchlog reads pg_stat_statements, so query shapes, timings and IO appear as soon as the extension is installed.

slowQuery.thresholdMs only decides what the dashboard labels slow:

"slowQuery": {
  "thresholdMs": 100
}

Query text is already normalized by PostgreSQL — literals are $1, $2, so parameter values never leave your host. Utility statements such as CREATE ROLE … PASSWORD are stored verbatim by pg_stat_statements, so Watchlog scrubs those in full rather than trusting the normalized path.

Activity thresholds

These decide when a session becomes a finding, and are worth matching to your workload:

"activity": {
  "longQuerySeconds": 30,
  "longTransactionSeconds": 60,
  "idleTransactionSeconds": 300
}

A long query may be a legitimate report — raise longQuerySeconds on a reporting database. A long transaction is different: it pins the snapshot, so vacuum cannot reclaim any row it might still see, anywhere in the cluster.