A PostgreSQL extension that makes PostGIS the source of truth — with full geodatabase semantics and bidirectional sync to ArcGIS Online.
v0.1.0 foundation release · by TechMaven
Why agol_fdw
ArcGIS Online hosts your feature services in Esri's cloud. But when you need a real relational database as your system of record — with enforced domains, subtypes, attribute rules, and relationships — there has been no clean way to make PostGIS authoritative and keep AGOL in step. Until now.
AGOL is the only home for your data. No enforced geodatabase semantics in a real RDBMS, no SQL-grade integrity, and data sovereignty lives outside your walls.
AGOL layers become native Postgres tables with full geodatabase semantics. PostGIS is the source of truth; AGOL is a synchronized projection.
No silent data loss. Dead-letter queues, conflict logs, and validation-issue logging mean every edit is accounted for — never silently dropped.
Capabilities
Read an AGOL layer's definition and register fields, geometry, domains, subtypes, rules, and relationships.
Domains, subtypes, attribute rules, relationships, and contingent values — translated to native Postgres.
Introspect → generate DDL → apply → backfill. Idempotent and re-runnable.
Outbox + webhooks with optimistic concurrency. AGOL and PostGIS stay aligned asynchronously.
Dead-letter queues, conflict logs, validation issues, and a periodic reconcile audit.
OAuth2 secrets encrypted at rest with pgcrypto; tokens cached and refreshed proactively.
Optional ogr_fdw foreign tables for live, read-only AGOL queries straight from SQL.
Every operation is a plpython3u function callable from plain SQL — DBA-friendly.
A browser UI — map viewer + admin console — built as GeoLibre plugins over the SQL functions.
How it works
agol_fdw introspects an AGOL hosted layer and materializes it as native Postgres tables with equivalent constraints, triggers, lookups, and relationships. Edits then flow bidirectionally and asynchronously — PostGIS authoritative, AGOL synchronized.
See the architecture →Get started
# install the python package
pip install agol_fdw
# enable the extension in your Postgres + PostGIS database
CREATE EXTENSION agol_fdw;