Source Code

Go to your cloned repo:

cd /path/to/watchlog-agent
vim integration.json

Locate the PostgreSQL config:

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

3. Update `"monitor"` to `true` and set the fields:
```diff
-  "monitor": false
+  "monitor": true

Example database array:
```
"database": ["db1","db2"]
```
Cluster-wide metrics need no list. This array decides which databases get per-table and per-index detail, because those views only describe the connected database — each one costs an extra connection, which is why storage collection is throttled to 300s.
Restart the agent:
```
pm2 restart watchlog-agent
```

Run the collector directly

Useful for seeing exactly what a given server exposes without waiting for the 60s tick:

node -e "
const list = require('./integration.json');
const cfg = list.find(i => i.service === 'postgresql');
require('./app/integrations/postgresql/index').collect(cfg, (err, res) => {
  if (err) return console.error('failed:', err.message);
  const a = res.advanced;
  console.log('version      :', a.server.version, '| role:', a.server.role);
  console.log('capabilities :', JSON.stringify(a.capabilities));
  console.log('databases    :', (a.databases || []).length);
  console.log('statements   :', (a.statements || []).length);
  console.log('tables       :', (a.tables || []).length);
  console.log('errors       :', JSON.stringify(a.collectorErrors || []));
});
"

collectorErrors names the exact reason a section is missing — a permission gap, an absent extension, or a view that does not exist on this version.

Testing against a throwaway server

docker run -d --name wl-pg-test -p 15432:5432 \
  -e POSTGRES_PASSWORD=secret -e POSTGRES_DB=shop postgres:15

# `docker exec` needs -i to attach stdin — without it a heredoc is a silent no-op.
docker exec -i wl-pg-test psql -U postgres -d shop <<'SQL'
CREATE TABLE orders (id serial primary key, customer text, total numeric);
INSERT INTO orders (customer, total) SELECT 'c' || g, g FROM generate_series(1, 50000) g;
SQL

Pick the version deliberately: PostgreSQL 15 has pg_stat_wal (14+) but not pg_stat_checkpointer (17+), which exercises the version-aware fallback.

Clean up afterwards — drop the synthetic series from InfluxDB scoped to the test uuid, delete the Elasticsearch documents, then remove the container.

Running the tests

npm test

Fixtures cover what a live server cannot show you on demand: an old-version row shape, a counter reset, and a permission-denied path.

Development notes

The advanced collector is app/integrations/postgresql/index.js; the legacy one is app/integrations/postgresql.js beside it. require('./integrations/postgresql') resolves to the legacy file — always require the advanced one by full path, including in tests.
pg_stat_statements timings are milliseconds. MySQL's are picoseconds. No conversion here.
server_version_num is MMmmpp before 10 and MMpppp after. Compare the raw number; a derived "major" ranks 9.6 above 15.
current_setting() applies units and returns '1kB'. Read numerics from pg_settings.
The connection summary excludes pid <> pg_backend_pid() so Watchlog never counts itself.
Counters reset two ways: a restart (uptime goes backwards) and an explicit pg_stat_statements_reset(), which the agent never issues but an operator can. The reset check is per entry, not just global — one statement's counters can reset while its neighbour's keep climbing.
Queries are ranked by impact — calls in the interval × interval mean duration — not by average latency.
Watch for queries that fail as a unit: a single pg_database_size() on an inaccessible database once failed the entire pg_stat_database statement and lost every readable database with it.
Never log query text in agent or server-agent error output. Driver errors echo the failing statement, and statements carry literals.