See also: location-db • usage • schema • misc-server
Health monitoring for the OwnTracks database host (gravlax). Two complementary ways to check it: the check MCP tool (one call, includes host disk which SQL cannot see) and raw pg_query SQL (all DB-internal metrics). Built 2026-08-24 after a low-disk scare on gravlax — the trigger metric (filesystem free space) is precisely the one not reachable from SQL.
Tool check on the misc MCP endpoint (https://www.critchley.biz/misc/mcp). Parameter target names what to inspect; omit it or pass all for a full rollup with overall_status. Every check returns status: ok | warn | crit | info. Source misc_mcp_server.py (function _run_check), deployed on branch m, commit 8d37ca5.
Targets:
• disk — host filesystem free space on / and /mnt (via os.statvfs; not visible to SQL)
• db_size — size of each database (info)
• connections — backend count vs max_connections
• long_queries — active queries running > 30s
• bloat — dead-tuple % and last autovacuum per table
• xid — transaction-id wraparound headroom
• locks — waiting / blocked locks
• cache — buffer cache hit ratio, deadlocks, temp usage
• freshness — latest OwnTracks fix age (info; Manual mode may be legitimately stale)
• instance — version, uptime, recovery/replication (info)
• all — run every check (default)
• disk: warn < 20% free, crit < 10% free
• connections: warn ≥ 80% of max, crit ≥ 90%
• long_queries: warn if any > 30s, crit if any ≥ 5 min
• bloat: warn dead ≥ 20%, crit ≥ 40%
• xid: warn ≥ 50% to wraparound, crit ≥ 80%
• locks: warn if any waiting
• cache: warn hit < 90%
• db_size, freshness, instance: informational (no alarm)
Env overrides on the misc service: MONITOR_DISK_PATHS (default /,/mnt), MONITOR_DISK_WARN_PCT (20), MONITOR_DISK_CRIT_PCT (10).
The twelve raw SQL checks, all confirmed runnable as owntracks_ro. The check tool wraps ten of these; the connection-detail (5) and checkpoint (10) queries are richer here for ad-hoc use.
1. Instance overview:
SELECT version(),
date_trunc('second', now()-pg_postmaster_start_time()) AS uptime,
current_setting('max_connections')::int AS max_connections,
(SELECT count(*) FROM pg_stat_activity) AS current_connections,
pg_is_in_recovery();2. Database sizes:
SELECT datname AS database,
pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database WHERE datistemplate=false
ORDER BY pg_database_size(datname) DESC;3. Largest relations:
SELECT relname,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
pg_size_pretty(pg_relation_size(relid)) AS heap,
n_live_tup
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;4. Throughput & cache hit:
SELECT numbackends, xact_commit, xact_rollback,
round(100.0*blks_hit/nullif(blks_hit+blks_read,0),2) AS cache_hit_pct,
deadlocks, temp_files, pg_size_pretty(temp_bytes) AS temp_written
FROM pg_stat_database WHERE datname=current_database();5. Connections by backend type / state (NULLs are backends the read-only role may not inspect):
SELECT coalesce(backend_type,'(hidden from read-only role)') AS backend,
coalesce(state,'—') AS state, count(*) AS n
FROM pg_stat_activity GROUP BY backend_type, state ORDER BY n DESC;6. Long-running queries (> 30s, excluding self):
SELECT pid, usename, state,
date_trunc('second', now()-query_start) AS running_for,
left(regexp_replace(query,'\s+',' ','g'),100) AS query
FROM pg_stat_activity
WHERE state='active' AND now()-query_start > interval '30 seconds'
AND pid<>pg_backend_pid()
ORDER BY now()-query_start DESC;7. Bloat / autovacuum:
SELECT relname, n_live_tup AS live, n_dead_tup AS dead,
round(100.0*n_dead_tup/nullif(n_live_tup+n_dead_tup,0),2) AS dead_pct,
last_autovacuum
FROM pg_stat_user_tables WHERE n_live_tup+n_dead_tup>0
ORDER BY n_dead_tup DESC LIMIT 10;8. XID wraparound headroom:
SELECT datname, age(datfrozenxid) AS xid_age,
round(100.0*age(datfrozenxid)/2000000000,4) AS pct_to_wraparound
FROM pg_database WHERE datistemplate=false
ORDER BY age(datfrozenxid) DESC;9. Locks / blocking:
SELECT (SELECT count(*) FROM pg_locks WHERE NOT granted) AS waiting_locks,
(SELECT count(*) FROM pg_stat_activity WHERE wait_event_type='Lock') AS sessions_waiting_on_lock;10. Checkpoints / bgwriter (stats survive a clean restart, so counts span since last stats_reset, not since boot):
SELECT checkpoints_timed, checkpoints_req,
round(100.0*checkpoints_req/nullif(checkpoints_timed+checkpoints_req,0),1) AS requested_pct,
date_trunc('second', now()-stats_reset) AS stats_age
FROM pg_stat_bgwriter;11. Replication / recovery:
SELECT pg_is_in_recovery() AS in_recovery,
(SELECT count(*) FROM pg_stat_replication) AS replica_count,
pg_current_wal_lsn();12. Data freshness — OwnTracks ingest (informational only):
SELECT max(to_timestamp(tst)) AS latest_fix_utc,
date_trunc('second', now()-max(to_timestamp(tst))) AS age_of_latest_fix,
count(*) FILTER (WHERE to_timestamp(tst) > now()-interval '24 hours') AS fixes_last_24h
FROM locations;The MCP role owntracks_ro is not a superuser and not a member of pg_monitor or pg_read_all_stats. Confirmed reachable: DB and relation sizes, pg_stat_database, pg_stat_activity (counts + own sessions), pg_stat_user_tables, age(datfrozenxid), pg_locks, pg_stat_bgwriter, pg_stat_replication, pg_current_wal_lsn().
Confirmed blocked (permission denied) — hence the external check tool exists:
• Filesystem free space — no SQL expresses it at all. This was the incident trigger.
• pg_ls_waldir() — pg_wal directory size (needs pg_monitor). Note the misc service runs as john and cannot read the postgres-owned pg_wal dir either; the disk check on /mnt covers the real WAL-growth risk (disk fill).
• data_directory (and other pg_read_all_settings items) — SQL will not even reveal where the data lives, so the check tool carries the paths as config.
• Per-session forensics of other roles' backends: backend_type, query, wait events come back NULL. Connection counting works; "what is that stuck backend running" does not.
• Short uptime is normal — the gravlax host powers off overnight, so "up since this morning" is expected, not a restart to investigate.
• Freshness is informational only. OwnTracks is often in Manual mode (no automatic fixes), so a multi-hour gap since the last fix can be entirely correct. Do not alarm on it; only treat it as meaningful when the phone is in an automatic/move mode.
• Postgres data dir /mnt/postgres/15/main → the database lives on /mnt (/dev/nvme1n1, ~8 GB). Root / is a separate ~16 GB volume. Monitor both.
• misc server: systemd unit misc-mcp-server.service, runs as john, deployed at ~/py/gdata-server on gravlax (a checkout of john-critchley/gdata-server, branch m). Deploy = push → git pull on gravlax → sudo systemctl restart misc-mcp-server.
• Alert cron wrapping check('all') that fires on overall_status != ok via the existing mail infra — the status shape is already designed for it. This is the proactive half of john/actions item 2 (the on-demand half is the tool itself).
• Smoke-test the deployed tool once the MCP client re-picks-up the new tool: check('all') should return all ok/info.
• Optional extra checks not yet added: per-table seq-scan vs index-scan ratio, and unused-index detection (tuning rather than health).
• Decision if we ever want WAL-dir size or per-session forensics: grant pg_monitor to owntracks_ro, weighing the wider read exposure that grant carries.