Snapshotting a PostGIS Schema into a Versioned GeoPackage

An export is only a version if the same database state always produces the same bytes — otherwise the repository records the export process rather than the data. This page is a focused companion to database-native versioning in PostGIS and GeoPackage.

Concept & Context

A nightly export of a live schema is the standard bridge between a database that is edited continuously and a repository that publishes reviewed states. It fails in a specific and very common way: every run produces a new file hash, the repository grows nightly, and nobody can tell which nights contained real change.

Nothing about that is mysterious. Three things vary between runs unless they are pinned. Row order follows the planner, and a planner that picks an index scan today and a sequential scan tomorrow writes rows in a different order. Coordinate precision follows the driver’s default, so the same geometry gets a different number of decimals. And the container itself records a creation timestamp.

Pin all three and the export becomes a genuine fingerprint of database state — which is what makes it worth committing at all. The remaining requirement is consistency across tables: reading each table in its own transaction can produce a snapshot where a parcel exists in one table and not in the join table beside it.

The three things that vary between two identical exports A grid of the three sources of export churn — row order, coordinate precision and container metadata — against what causes each and the setting that pins it. Why it varies What pins it Row order the planner may choose a different scan ORDER BY the stable key, in the query Coordinate precision the driver's default rounding an explicit precision setting Container metadata a creation timestamp written on every export normalised after writing, then vacuumed Pin all three and an unchanged database produces an identical file; miss one and the repository grows nightly.

Core Algorithmic Pipeline

  1. Open one REPEATABLE READ transaction and export every table inside it, so all tables reflect the same database snapshot.
  2. Export each table with ORDER BY on its stable key and an explicit coordinate precision.
  3. Strip volatile container metadata after writing, so timestamps do not change the hash.
  4. Compute a content fingerprint for each layer, independent of container encoding.
  5. Write a manifest recording the database transaction, the fingerprints, and the commit that publishes them.
Why every table is read inside one transaction A sequence showing an export reading three tables while an editor commits between two of them. Inside one repeatable-read transaction every table reflects the same snapshot; without it, a parcel exists in one table and not in the one beside it. Exporter Database Editor BEGIN REPEATABLE READ the snapshot is fixed here export parcels commits a new parcel + its link row export parcel_links still the original snapshot internally consistent set Read each table in its own transaction and the export contains a link row pointing at a parcel it does not hold.

Working Implementation

"""Deterministic export of a PostGIS schema into a versioned GeoPackage."""
from __future__ import annotations

import hashlib
import json
import os
import sqlite3
import subprocess
from datetime import datetime, timezone
from pathlib import Path

import psycopg

COORD_PRECISION = 3        # millimetres in a metric CRS


def spatial_tables(conn, schema: str = "public") -> list[tuple[str, str, str]]:
    """(table, geometry column, primary key) for every registered spatial table."""
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT g.f_table_name, g.f_geometry_column, a.attname
              FROM geometry_columns g
              JOIN pg_class c   ON c.relname = g.f_table_name
              JOIN pg_index i   ON i.indrelid = c.oid AND i.indisprimary
              JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = ANY(i.indkey)
             WHERE g.f_table_schema = %s
             ORDER BY g.f_table_name
            """,
            (schema,),
        )
        return cur.fetchall()


def export_schema(dsn: str, out_path: Path, schema: str = "public") -> dict:
    out_path.parent.mkdir(parents=True, exist_ok=True)
    if out_path.exists():
        out_path.unlink()                       # append mode is not deterministic

    with psycopg.connect(dsn) as conn:
        conn.execute("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ")
        with conn.cursor() as cur:
            cur.execute("SELECT txid_current_snapshot()::text")
            snapshot = cur.fetchone()[0]

        tables = spatial_tables(conn, schema)

        for i, (table, geom_col, pk) in enumerate(tables):
            sql = (f'SELECT * FROM "{schema}"."{table}" ORDER BY "{pk}"')
            subprocess.run(
                ["ogr2ogr",
                 "-f", "GPKG", str(out_path), f"PG:{dsn}",
                 "-sql", sql, "-nln", table,
                 "-lco", "SPATIAL_INDEX=NO",          # rebuilt on read; not content
                 "-lco", f"GEOMETRY_NAME={geom_col}",
                 "-lco", "FID=fid",
                 "--config", "OGR_GEOMETRY_PRECISION", str(COORD_PRECISION),
                 *(["-update"] if i else []),
                 ],
                check=True,
                env={**os.environ, "PGCLIENTENCODING": "UTF8"},
            )

    strip_volatile_metadata(out_path)
    fingerprints = {t: layer_fingerprint(out_path, t, pk)
                    for t, _, pk in tables}

    manifest = {
        "schema": "gdv-snapshot/1",
        "database_snapshot": snapshot,
        "exported_at": datetime.now(timezone.utc).isoformat(timespec="seconds"),
        "tables": fingerprints,
        "container_sha256": file_hash(out_path),
    }
    out_path.with_suffix(".manifest.json").write_text(json.dumps(manifest, indent=2))
    return manifest


def strip_volatile_metadata(gpkg: Path) -> None:
    """Remove the fields that change on every write without any data changing."""
    con = sqlite3.connect(gpkg)
    con.execute("UPDATE gpkg_contents SET last_change = '1970-01-01T00:00:00.000Z'")
    con.execute("PRAGMA application_id = 0x47504B47")   # 'GPKG', as the spec requires
    con.commit()
    con.execute("VACUUM")                               # deterministic page layout
    con.close()


def file_hash(path: Path) -> str:
    h = hashlib.sha256()
    with open(path, "rb") as fh:
        while block := fh.read(1 << 20):
            h.update(block)
    return h.hexdigest()

Publishing then links the two histories, which is the whole reason for the manifest:

python scripts/export_schema.py --out exports/parcels.gpkg
git add exports/parcels.gpkg exports/parcels.manifest.json
git commit -m "Snapshot $(jq -r .database_snapshot exports/parcels.manifest.json)"

psql -c "INSERT INTO export_log (db_snapshot, commit_sha, container_sha256)
         SELECT '$(jq -r .database_snapshot exports/parcels.manifest.json)',
                '$(git rev-parse HEAD)',
                '$(jq -r .container_sha256 exports/parcels.manifest.json)'"

Validation & Output Verification

The property that matters is determinism, and it is directly testable:

# Two exports of an unchanged database must be byte-identical
python scripts/export_schema.py --out /tmp/a.gpkg
python scripts/export_schema.py --out /tmp/b.gpkg
cmp /tmp/a.gpkg /tmp/b.gpkg && echo "deterministic" \
  || echo "FAIL: unchanged database produced different bytes"

# The container must still be a valid GeoPackage after metadata stripping
ogrinfo -so /tmp/a.gpkg | head -20
python -c "
import sqlite3
con = sqlite3.connect('/tmp/a.gpkg')
assert con.execute('PRAGMA application_id').fetchone()[0] == 0x47504B47
print('application_id intact')
"
# A real edit must change the fingerprint of exactly one table
import json, subprocess
before = json.load(open("exports/parcels.manifest.json"))["tables"]
subprocess.run(["psql", "-c",
                "UPDATE parcels SET land_use='industrial' "
                "WHERE parcel_uid='EAST-7K3M2P9Q4R'"], check=True)
subprocess.run(["python", "scripts/export_schema.py", "--out", "exports/parcels.gpkg"],
               check=True)
after = json.load(open("exports/parcels.manifest.json"))["tables"]

changed = [t for t in before if before[t] != after[t]]
assert changed == ["parcels"], f"unexpected tables changed: {changed}"
print("edit isolated to one table's fingerprint")

Run the determinism check in CI on a fixture database. It is the check that stops the repository quietly reverting to nightly churn after somebody adds a table without an ORDER BY.

Scheduling the Snapshot

The snapshot’s timing matters as much as its determinism. Taking it mid-edit produces a consistent database snapshot — the repeatable-read transaction guarantees that — of a state no editor intended to publish, so the repository accumulates half-finished work. Scheduling it against the editing day rather than against midnight avoids most of that: after the last editing session, before the overnight processing, with a documented cut-off that editors know.

Where editing genuinely runs around the clock, the alternative is to snapshot on a signal rather than a clock. A steward marks a state as publishable, the job runs against that transaction, and the export names it. That costs a step of human work per snapshot and removes the entire class of “why does this release contain a boundary somebody was still drawing”.

Scheduling against the editing day, not the clock A timeline of an editing day: edits run through the afternoon, the documented cut-off passes, the snapshot is taken against a settled state, and overnight processing follows — rather than a midnight snapshot landing mid-edit. Editing begins continuous, concurrent 09:00 Documented cut-off editors know it 17:30 Snapshot a settled, publishable state 18:00 Overnight processing runs from the snapshot 22:00 A midnight snapshot is consistent and publishes work nobody had finished — consistency is not the same as intent.

Failure Modes

  • A new file hash every night — symptom: the repository grows with no edits. Root cause: unordered export, unpinned precision, or the container timestamp. Fix: all three, as above; verify with the byte-comparison check.

  • Tables inconsistent with each other — symptom: a feature present in one table and missing from its join partner. Root cause: each table exported in its own transaction. Fix: one REPEATABLE READ transaction for the whole export.

  • The container is rejected by other tools — symptom: a GeoPackage that GDAL reads and QGIS refuses. Root cause: metadata stripping went too far and cleared application_id. Fix: reset it explicitly after stripping, and validate the container as part of the export.

  • Fingerprints change after a GDAL upgrade — symptom: every table reports change after a toolchain bump. Root cause: container encoding changed. Fix: compare content fingerprints rather than container hashes, as covered in hashing spatial datasets for reproducible fingerprints.

Back to Database-Native Versioning in PostGIS and GeoPackage