Applying Minutely Diffs to a PostGIS Database Jump to heading#

Keep an osm2pgsql- or imposm-loaded PostGIS database current by appending minutely OSM change files incrementally, so only the rows that actually changed are touched — never a multi-hour full reimport.

Prerequisites Jump to heading#

• A PostGIS database already loaded from a .osm.pbf, and PostGIS 3.x on PostgreSQL 14+.
• osm2pgsql 1.9+ imported in slim mode — updates are impossible against a non-slim import (see the note below).
• pyosmium 3.6+ for pyosmium-get-changes, or a direct .osc.gz fetch, as set up in Catching Up a Stale OSM Extract with pyosmium.
• The database’s current replication sequence recorded — its origin is explained in Replication Sequence Numbers and State.
• Python 3.10+ and a scheduler (cron or a timer) to fire the loop.
• Disk headroom for the slim tables, which roughly double the import footprint but are what make incremental updates possible.

Conceptual minimum Jump to heading#

A file-based update rewrites the entire .osm.pbf for every diff; a database-based update instead applies the diff’s create/modify/delete operations as row-level INSERT/UPDATE/DELETE against the rendered tables, so cost tracks the number of changed objects rather than database size. That is why a minutely cadence — impractical against a large file, as the parent guide Applying .osc Change Files with osmium notes — is entirely comfortable against PostGIS.

The load-bearing requirement is slim mode. To turn a diff’s modify way 12345 v8 into the right SQL, the updater must know that way’s previous geometry and which rendered rows it produced — it must resolve node references to coordinates and remember prior state. osm2pgsql --slim persists that bookkeeping in the planet_osm_nodes, planet_osm_ways, and planet_osm_rels tables; without them the tool has no way to reconstruct the delta and simply refuses to append. imposm keeps the equivalent state in its own cache directory. Either way, the update path is: fetch the ordered diffs, apply them with --append, and advance the recorded sequence only after the database transaction commits — the same atomic-at-the-state-boundary discipline the OSM Replication & Diff Sync pillar insists on.

Runnable solution Jump to heading#

The orchestration below fetches the next span of minutely diffs with pyosmium-get-changes, appends them with osm2pgsql, and advances a sequence file only when both the fetch and the append succeed. It shells out to the two CLIs so the exact flags used in a manual run are visible and reproducible.

from __future__ import annotations

import logging
import subprocess
from pathlib import Path

logging.basicConfig(level=logging.INFO, format="%(levelname)s: %(message)s")
logger = logging.getLogger("osm.pg_update")

REPL_URL = "https://planet.openstreetmap.org/replication/minute/"
SEQ_FILE = Path("state.seq")            # last committed sequence
DIFF_FILE = Path("update.osc.gz")       # merged diff for this cycle
STYLE = Path("openstreetmap-carto.style")
DB = "osm"


def read_sequence() -> int:
    return int(SEQ_FILE.read_text().strip())


def fetch_diffs(start: int, max_diffs: int = 120) -> int | None:
    """Fetch up to max_diffs minutely diffs after `start` into one .osc.gz.

    Returns the new head sequence, or None when already current.
    """
    result = subprocess.run(
        ["pyosmium-get-changes", "--server", REPL_URL,
         "--start-id", str(start), "--size", str(max_diffs),
         "-o", str(DIFF_FILE), "--verbose"],
        capture_output=True, text=True,
    )
    if result.returncode == 3:            # pyosmium: nothing new to fetch
        logger.info("no new diffs after sequence %d", start)
        return None
    if result.returncode != 0:
        raise RuntimeError(f"fetch failed: {result.stderr.strip()}")
    # pyosmium-get-changes writes the reached sequence to a sidecar .osc.gz.seq
    return int(Path(str(DIFF_FILE) + ".seq").read_text().strip())


def append_to_postgis() -> None:
    """Apply the fetched diff to PostGIS in slim append mode (single txn)."""
    subprocess.run(
        ["osm2pgsql", "--append", "--slim",
         "--database", DB, "--style", str(STYLE),
         "--number-processes", "4", str(DIFF_FILE)],
        check=True,
    )


def run_once() -> None:
    start = read_sequence()
    head = fetch_diffs(start)
    if head is None:
        return
    append_to_postgis()               # raises on any non-zero exit
    SEQ_FILE.write_text(str(head))    # advance ONLY after append committed
    logger.info("PostGIS updated through sequence %d", head)


if __name__ == "__main__":
    run_once()

For an imposm-managed schema the append step is a single command instead — imposm tracks its own diff state in its cache and reads the replication URL from its config:

# imposm equivalent: run continuous minutely updates against its own cache.
imposm run -config imposm.json -connection "postgis://osm@localhost/osm"

Step-by-step walkthrough Jump to heading#

1. Read the committed sequence. read_sequence loads the last sequence that was fully applied and committed, not merely fetched — the file is the single source of truth for where the database stands.
2. Fetch a bounded span. fetch_diffs caps the request at max_diffs minutely diffs, so a run that fell behind catches up in bounded chunks; return code 3 is pyosmium-get-changes’s “already current” signal and exits cleanly.
3. Append in slim mode. append_to_postgis runs osm2pgsql --append --slim, which reads the slim bookkeeping tables to turn each diff operation into the correct row-level SQL against the rendered tables. --number-processes parallelizes geometry building.
4. Commit then advance. osm2pgsql applies the diff in its own transaction; only after it returns success does run_once write the new sequence. A crash before the write leaves the database behind the sequence file’s prior value, which is safe because re-fetching and re-applying is idempotent.
5. Schedule the loop. Fire run_once every minute from cron or a timer; the scheduling and locking details are the subject of Building a Minutely Update Pipeline, including how to prevent two runs overlapping.

Verification Jump to heading#

• Sequence file moved. After a cycle, state.seq holds a higher number and matches the .osc.gz.seq sidecar the fetch wrote.
• Row counts changed. SELECT count(*) FROM planet_osm_point; before and after should differ on an active region; a static count means nothing was appended.
• A known edit landed. Pick a recently edited object id and query planet_osm_line / planet_osm_polygon for it; its geometry or tags should reflect the change.
• No slim-mode error. The absence of Cannot apply diffs to a database that was not imported with --slim in the log confirms the base import was slim.
• Timestamps track live. The osm2pgsql replication status (or imposm’s log) should report a lag of a few minutes at most under a minutely schedule.

Common errors and fixes Jump to heading#

Specification reference Jump to heading#

Incremental updates require the slim schema and the append mode. See the official osm2pgsql updating documentation for the --append --slim requirement and the middle-table bookkeeping, and the imposm3 documentation for its equivalent diff-update workflow and cache. The replication directory and state.txt format the fetch reads are documented on the OSM Wiki “Planet.osm/diffs” page.

Frequently Asked Questions Jump to heading#

Related Jump to heading#

• Applying .osc Change Files with osmium — the diff-application semantics that also govern the database path.
• Catching Up a Stale OSM Extract with pyosmium — the file-based counterpart and the fetch tooling reused here.
• Replication Sequence Numbers and State — where the database’s tracked sequence comes from.
• Building a Minutely Update Pipeline — scheduling and locking the loop shown here.
• Spatial Indexing for OSM Extracts — keeping spatial indexes current as rows change.

Up one level: Applying .osc Change Files with osmium.