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.
[ ] URGENT — Investigate broken OwnTracks → PostgreSQL ingestion. Automatic reporting appears to have stopped after id 57286 (fix 2026-09-18 09:04:47 BST / received 09:04:48), despite there normally being many location fixes over the following two days. A manual OwnTracks push on 2026-09-20 also failed to create a row. This indicates a broader ingestion failure affecting both automatic and manual reports, somewhere upstream of the PostgreSQL locations table.
• 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)