tlf-pgreconcile

Normalization-aware schema and data reconciliation for two Postgres databases.

pgreconcile compares a source and a target Postgres database — schema and row data — and tells you not just what differs, but why: whether a mismatch is a real data discrepancy, or just a formatting difference (casing, whitespace, date format) that normalizes away to the same value.

It was built to solve a specific, recurring problem: reconciling a central server database against field devices that sync intermittently and don't always have internet access. But it's general-purpose — useful anywhere you have two Postgres databases that are supposed to represent the same data and you need to know exactly where, and why, they've drifted apart.

---

Table of contents

• Why this exists
• What makes this different from other tools
• Installation
• Quick start
• The config file
• Walkthrough: reconciling two tables
• Understanding the output
• The HTML report
• How it works internally
• Known limitations
• Contributing
• License

---

Why this exists

This project originated from a real pattern seen in Nepal's disaster reconstruction (RIMS) system: a central RIMS server, plus field inspection officers each carrying an ODK-based local database. Officers work in areas that often have no internet access, so their devices sync to the central server intermittently — sometimes hours or days later. That opportunistic sync pattern causes two kinds of problems:

1. Records that simply haven't synced yet — normal, expected, temporary.
2. Records that genuinely disagree — a value was entered differently on each side, and one of them is wrong.

Existing tools don't distinguish these well. Real-time CDC tools (Debezium, WAL-based replication) assume continuous connectivity, which doesn't hold here. General-purpose DB-diff tools report every difference flatly, without asking whether a "difference" is actually just a casing or whitespace inconsistency versus a real conflict that needs a human to resolve it.

pgreconcile is a periodic reconciliation/audit tool, not a real-time sync tool — designed to be run on a schedule (e.g. nightly) against whatever has synced so far, and to make the signal-to-noise ratio of its report as high as possible.

What makes this different from other tools

	pgreconcile	pgCompare / generic diff tools	Debezium / CDC
Requires continuous connectivity	No	No	Yes
Postgres-to-Postgres only	Yes (by design)	Usually multi-DB	N/A
Distinguishes real mismatch vs. formatting difference	Yes	No	N/A
Column-level classification	Yes	Row-level only (typically)	N/A
Designed for offline/opportunistic sync scenarios	Yes	No	No

The core differentiator is normalization-aware, three-way classification at the column level:

• same — values match exactly
• same_after_normalization — values differ raw, but match after applying a normalizer (casing, whitespace, digit format, date format, etc.)
• value_mismatch — values differ even after normalization; a genuine conflict

This means a report tells you "1 real mismatch, 1 same after normalization" instead of just "2 differences" — so you know where to actually spend your attention.

Installation

Requires Python 3.10+ and access to both Postgres databases you want to compare.

From PyPI (recommended)

pip install tlf-pgreconcile

This installs the pgreconcile command, plus its dependencies: psycopg[binary], pyyaml, and tlf-core (shared field/value normalizers).

Verify it installed correctly:

pgreconcile --help

From source (for development)

git clone https://github.com/PujanPandey07/tlf-pgreconcile.git
cd tlf-pgreconcile
python -m venv tlfenv
# Windows (PowerShell):
tlfenv\Scripts\Activate.ps1
# macOS/Linux:
source tlfenv/bin/activate

pip install -e .

The editable install (-e .) means changes to the source are picked up immediately without reinstalling — useful if you're contributing or customizing the tool.

Quick start

pgreconcile has three subcommands.

1. Generate a starter config

pgreconcile init --output config/my_config.yaml

This writes a template YAML file with placeholder connection details and a placeholder table mapping — edit it to match your real databases (see The config file below).

2. Validate your config

Before running a real comparison (which opens live DB connections), sanity check the config file itself:

pgreconcile validate --config config/my_config.yaml

This checks the YAML is well-formed and that every table mapping has the required fields — without connecting to either database.

3. Run the reconciliation

pgreconcile run --config config/my_config.yaml

This connects to both databases, diffs the schema, diffs the data table-by-table, and prints a full report to the terminal.

To also save a shareable, styled HTML report:

pgreconcile run --config config/my_config.yaml --html-output report.html

--html-output is optional and additive — it never replaces the terminal report, and a failure to write the HTML file won't fail a reconciliation that otherwise succeeded.

The config file

A config file has two top-level sections: databases and tables.

databases:
  source:
    host: localhost
    port: 5434
    dbname: sourcedb
    user: postgres
    password: changeme
  target:
    host: localhost
    port: 5433
    dbname: targetdb
    user: postgres
    password: changeme

tables:
  - source_table: inspections
    target_table: inspection_reports
    key_columns: [officer_name]
    columns: [officer_name, damage_severity]
    column_settings:
      damage_severity:
        normalizer: normalize_casing
        severity: medium

databases

Standard Postgres connection details for source and target. Each side can point at a different host/port/database — they don't need to be on the same server.

tables

A list, so one config file can describe multiple table-pair comparisons in a single run.

Field	Required	Meaning
source_table	Yes	Table name in the source database
target_table	Yes	Table name in the target database (can differ from source_table)
key_columns	Yes	Column(s) that uniquely identify a row across both databases. See warning below.
columns	Yes	Which columns to compare (usually the key columns plus the value columns you care about)
column_settings	No	Per-column overrides: a normalizer to apply before comparing, and a severity label

⚠️ Choosing key_columns correctly

This is the single most important thing to get right, and the most common way to get a misleading report:

• Never use an auto-incrementing ID as a key column. Independent auto-increment counters on two separate databases can produce the same ID for two completely unrelated rows. Use a column that's genuinely the same identity on both sides — a natural key like a name, a UUID generated at creation time, or a composite of several columns.
• Never put a column you're actually comparing into key_columns. If damage_severity is a value you want to check for mismatches, it must not also be part of how you identify the row — otherwise a real value mismatch just looks like "this row only exists in source" and "this row only exists in target" as two separate ghost rows, instead of one row with a mismatched column.

key_columns can be a single column or a list (composite key) — either way, at least one key column is required; an empty key list is rejected rather than silently guessing.

Available normalizers

Provided via tlf-core and referenced by name in column_settings:

• normalize_casing — case-insensitive comparison
• normalize_devanagari_digits — treats Devanagari and Arabic numerals as equivalent
• normalize_date — compares dates regardless of format/separator

Walkthrough: reconciling two tables

Given a source.inspections table and a target.inspection_reports table with these rows:

officer_name	damage_severity (source)	damage_severity (target)
Raju	Minor	Minor
Sita	Minor	Moderate
Kiran	Severe	SEVERE
Hari	Minor	(missing)
Gita	(missing)	Moderate

Running:

pgreconcile run --config config/example_config.yaml

Produces:

============================================================
SCHEMA COMPARISON
============================================================
Tables only in SOURCE: inspections
Tables only in TARGET: inspection_reports
Comparing: inspections (source) vs inspection_reports (target)
  [MISSING] Hari — exists in source only
  [MISSING] Gita — exists in target only
  [MISMATCH] Sita
      damage_severity: "Minor" (source) vs "Moderate" (target)
  [SAME AFTER NORMALIZATION] Kiran
      damage_severity: "Severe" (source) vs "SEVERE  " (target)
Summary: 4 rows compared, 1 real mismatch(es), 1 same after normalization, 2 missing
--- Column-level differences: inspections vs inspection_reports ---
KEY             COLUMN               SOURCE VALUE         TARGET VALUE         STATUS
--------------------------------------------------------------------------------------------------------
Sita            damage_severity      Minor                Moderate             REAL MISMATCH
Kiran           damage_severity      Severe               SEVERE               SAME (normalized)

Raju doesn't appear in the mismatch table at all — it's an exact match on every compared column, which is exactly the point: the report surfaces what needs attention, not everything that was checked.

Understanding the output

Every row lands in exactly one of these categories:

Status	Meaning
(not shown)	Row and all compared columns match exactly — no action needed
SAME AFTER NORMALIZATION	Values differ raw, but are equivalent once normalized (e.g. Severe vs SEVERE ) — usually just a formatting inconsistency, not a data problem
MISMATCH	Values genuinely disagree even after normalization — needs a human to decide which value is correct
MISSING (only in source / only in target)	The row hasn't synced to the other side yet, or was deleted on one side. Not automatically an error — in an opportunistic-sync scenario, this is expected for recently-created records. It only becomes a concern if the same record stays unsynced across repeated runs.

The HTML report

--html-output report.html produces a single self-contained HTML file (no external assets, no server needed) with:

• Summary counts across all tables at a glance
• One collapsible section per table
• A sortable mismatch table (click any column header to sort)
• Color-coded rows by classification

Useful for sharing a reconciliation run with someone who doesn't have terminal access, or for keeping a dated archive of past runs.

How it works internally

1. introspect.py reads both databases' schemas via information_schema and pg_catalog.
2. schema_diff.py compares the two schemas — table and column names are matched exactly, falling back to a basic casing/whitespace-only normalization when an exact match isn't found (see Known limitations for what this does and doesn't catch).
3. data_diff.py fetches rows from both tables by the configured key_columns, and does a column-by-column comparison (not a whole-row hash), applying any configured normalizers before classifying each difference.
4. reconcile.py orchestrates this across every table pair in the config, using one connection per database, reused across all tables.
5. reports/ builds the terminal report (printer.py) and, if requested, the HTML report (html_renderer.py) from the same underlying comparison results.

row_hash.py also computes Merkle-style table/database-level hashes, intended as a future fast-path to skip unchanged tables entirely — this is computed today but not yet wired into the actual comparison flow.

Known limitations

Being upfront about what this tool doesn't do (yet):

• No fuzzy schema/column name matching. Table and column names are matched exactly, with one deliberately narrow exception: casing and surrounding-whitespace differences (Inspections vs inspections, or " damage_severity " vs "damage_severity") are recognized automatically as the same table/column, the same way value-level normalization already works for row data. A genuinely different name — e.g. inspections vs inspection_reports — is not matched; it's still reported as a real schema difference, since guessing at a rename is a much riskier failure mode than just flagging it for a human to confirm. Full fuzzy/similarity-based matching (rapidfuzz-style) was considered and intentionally deprioritized: an incorrect fuzzy match on a data-integrity tool is worse than no match at all, so it needs its own careful design pass rather than a quick addition.
• Append-style sync is assumed. The tool doesn't yet distinguish "a record was edited after its first sync" from "a record was never synced" — it currently assumes field devices only append new records rather than editing previously-submitted ones.
• No drift-over-time tracking yet. Each run is a snapshot; the tool doesn't currently remember previous runs, so it can't yet tell you whether a MISSING row is brand new or has been stuck for a week.
• Postgres-to-Postgres only, by design — not a general multi-database tool.

Contributing

Issues and PRs welcome. If you're proposing a new normalizer or comparison strategy, a quick note on the motivating use case helps a lot — this project grew out of one very specific real-world scenario, and it's most useful when new features are grounded in an actual reconciliation problem rather than a hypothetical one.

License

MIT — see LICENSE.