PostgreSQL 15 + PostGIS geospatial database on gravlax storing OwnTracks location history. Dual-written from the /loc HTTP endpoint alongside the existing SQLite store. Exposed as a read-only MCP tool (pg_query) via the misc MCP server.
• 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
• Gazetteer — named-point reference table (stations, GWML neutral sections) for matching GPS fixes against known places
• 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, table: locations
• Write user: owntracks (INSERT + SELECT, trust auth on local socket)
• Read-only user: owntracks_ro (SELECT only, used by MCP tool)
• 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
Move the pg_query tool's inline column-by-column schema description out of the MCP tool definition and into location-db/schema (already has it) — tool description should just point to the note by name, not repeat the schema.
• using-gps-data — cross-project GPS conventions (fresh-fix checks, gazetteer comparisons, PostGIS query patterns, visualisation convention)