PostgreSQL 15 + PostGIS geospatial database on gravlax storing OwnTracks location history. Dual-written from the /loc HTTP endpoint alongside the existing SQLite store. Exposed through pg_query via the misc MCP server, with read-only access by default and selectable write/admin logins.
RESOLVED 2026-10-12 — OwnTracks → PostgreSQL ingestion failure (reported 2026-09-20). The report said automatic reporting stopped after id 57286 (fix 2026-09-18 09:04:47 BST) and that a manual push on 2026-09-20 created no row. Re-checked against the locations table on 2026-10-12: there are fixes for every day since. 18 Sep has 471 fixes, all received within the hour and running to 23:59; 19 Sep has 534, of which 139 were received over an hour late (consistent with the phone buffering and delivering later); 20 Sep has 46; 21 Sep has 962; and ingestion has been continuous since (779 so far on 12 Oct, last at 09:00). The original diagnosis is therefore not borne out. Either the outage was transient and recovered by itself, or it was observed through a different path. The cause was not identified, and the failed manual push on 20 Sep remains unexplained. If a gap recurs, compare received_at with tst to tell a phone-side buffer from a server-side drop, and check Monitoring.
• Architecture — PostgreSQL setup, asyncpg pool, dual-write module, retry pattern
• Hibernate on Approach — (now a separate top-level project) using this database's neutral-section gazetteer + router power-loss evidence to trigger a clean hibernate on a battery-less SBC before a phase break
• Schema — table DDL, column descriptions, indexes
• Usage — pg_query MCP tool, PostGIS query examples
• Monitoring — health checks: the check MCP tool (adds host disk) + the pg_query metric catalogue, thresholds, and read-only-role limits
• Gazetteer — named-point reference table (stations, GWML neutral sections) for matching GPS fixes against known places
• WTT Train Timetable — Network Rail Working Timetable train schedules (journeys + calling points) linked to gazetteer stations; scheduled times for delay-repay and GPS comparison
• UK Outward Codes within 1° of Home — postcode-district centroid table (Bristol/Bath/Newport/Cardiff/Gloucester/Swindon area), snapshot note not a DB table
• Host: gravlax.critchley.biz
• PostgreSQL 15, data dir: /mnt/postgres/15/main (NVMe, ~7 GB)
• Extension: PostGIS 3.3 (geometry, geography, GiST index)
• Database: owntracks; tables: locations (GPS), gazetteer (named points), and the WTT train tables train_journey / train_calls / wtt_location (see WTT Train Timetable)
• Write user: owntracks (INSERT + SELECT, trust auth on local socket)
• MCP logins: owntracks_ro (default, SELECT), owntracks_rw (SELECT/INSERT/UPDATE/DELETE), owntracks_adm (also CREATE and ALTER where ownership permits). Usage and limitations: location-db/usage
• Direct tunnel access (from pomelo): [postgres] stunnel entry, local 127.0.0.1:5432 → gravlax 15432 (server-side [pg] stunnel block) → the owntracks Unix socket. Works with either owntracks_ro (read) or owntracks (write) as the connection user — trust auth on the DB side doesn't check the originating host, just the requested db+role. Added 2026-07-13, added client entry to /etc/stunnel/stunnel.conf on pomelo, reload via kill -HUP <pid> (pid file /run/stunnel.pid).
• MCP endpoint: https://www.critchley.biz/misc/mcp — tool: pg_query
• Source: john-critchley/gdata-server — misc_mcp_server.py, owntracks_pg.py
• OwnTracks — HTTP receiver, SQLite store, /location read API
• misc-server — MCP server hosting pg_query
Deployed the login parameter and per-login pools. The pg_query description now points to location-db/usage and location-db/schema instead of repeating the inline schema.
• using-gps-data — cross-project GPS conventions (fresh-fix checks, gazetteer comparisons, PostGIS query patterns, visualisation convention)