Data Sync

Keep two systems in sync with change-data-capture, conflict resolution, and backfill.

Why

When your app spans two databases (e.g. Postgres for transactions, Elasticsearch for search, or a CRM mirror), you need changes in one to appear in the other. Polling every 5 min is slow and load-heavy; change-data-capture (CDC) via Postgres logical replication or Debezium streams every row change in real time. Conflict resolution (last-write-wins, or CRDT for collaborative fields) prevents the two systems from diverging when both are writable. Backfill is the unglamorous half: when you add a new sync target, you need to copy all existing data once before the stream can take over. Without a documented sync strategy, teams end up with cron jobs that 'mostly work' and a 3am reconciliation script nobody understands.

When

When you have ≥2 systems that must reflect the same data. Takes 1-3 days for CDC. Skip for single-database apps.

How

1. Choose the CDC source: Postgres logical replication (wal2json) or Debezium
   - Enable wal_level=logical on Postgres, create a replication slot
   - Debezium streams row-level inserts/updates/deletes as Kafka events

2. Set up the consumer: a Kafka Connect sink or a custom stream processor
   - For ES: the sink writes each change to the search index
   - For a CRM: transform the row to the CRM's schema, then upsert

3. Define conflict resolution for bidirectional sync
   - Unidirectional (DB → search): no conflicts, just apply
   - Bidirectional: use updated_at timestamp (last-write-wins) or field-level CRDTs

4. Backfill: copy existing data before starting the stream
   - Run a bulk export → import job (paginated, resumable)
   - Start the CDC stream AFTER backfill completes, using the slot's start position

5. Monitor lag: measure the delay between source commit and sink apply
   - Alert if lag > 60s — a stuck consumer means the search index is stale
   - Expose a /sync/status endpoint showing last-synced LSN and lag

Example

-- Enable Postgres logical replication
ALTER SYSTEM SET wal_level = 'logical';
SELECT pg_create_logical_replication_slot('sync_slot', 'wal2json');

-- Debezium connector config (Kafka Connect)
{
  "name": "pg-connector",
  "config": {
    "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
    "database.hostname": "pg.internal",
    "database.dbname": "app",
    "table.include.list": "public.users,public.orders",
    "plugin.name": "pgoutput"
  }
}

-- Backfill (run before starting the connector)
COPY (SELECT * FROM users WHERE updated_at < '2026-01-01') TO '/tmp/users.csv';
-- Then bulk-import to ES, then start the connector from the slot position

Common Problems

⚠Starting the CDC stream before backfill, so the target misses all pre-existing rows.
⚠Not handling deletes in the stream, so deleted rows linger in the search index as ghosts.
⚠Bidirectional sync without conflict resolution, so the two systems overwrite each other in a loop.
⚠Ignoring replication slot growth when the consumer is down — the Postgres WAL grows until disk fills.

Best Practices

✓Use a replication slot with a max_slot_wal_keep_size so a downed consumer can't fill the disk.
✓Transform in the stream processor, not in the source — keep the DB schema clean of sync concerns.
✓Run a daily reconciliation job that counts rows in source and sink; alert on any drift > 0.1%.
✓Version the sync schema: when the source adds a column, the consumer must handle both old and new event shapes.