# PIL live sync · Magic → PostgreSQL

Read-only continuous sync that keeps the royalty office's PostgreSQL copy of Magic current: 558 tables, 29 databases and 94M rows. New imports land in seconds, edits reconcile in minutes, and Magic receives nothing but SELECTs — no logins, schema, CDC or config changes.

**Status:** Live · Projects · 2026

- **Local checkout:** `/Users/simon/Developer/pil/sqlserver-to-pg`
- **Review #14:** https://ai-pricing.navonsimon.com/#/sync
- **Where it runs:** Two loops: identity/import tails every ~1.5s (new rows in seconds) and a DMV-gated key+hash diff (edits in minutes). Writes only to PostgreSQL live_shdb*; the SQL Server only receives SELECT … WITH (NOLOCK) and read-only DMV queries.
- **Note:** Internal office infrastructure · read-only from Magic, writes only to PostgreSQL.

## Stack

Python 3.12, pymssql, pg8000, SQL Server 2017 Express (source, read-only), PostgreSQL 17, Windows scheduled tasks

## Start here

- `sync_magic.py` — the sync engine: discover, setup, seed, index, reconcile, run --mode tails|diffs, status
- `copy_magic.py` — the one-off raw copy plus the shared helpers (source credentials from the Magic INI, row hashing, pgpass)
- `C:\Users\USER\magic-copy on win01` — deployed scripts, manifest.json, .venv, logs and run_*.cmd wrappers
- `raw_control.sync_plan / sync_state / sync_log / sync_seed` — control tables in magic_raw
- `live_shdb*` — the current-state mirror in PostgreSQL; Superset reads here
- `raw_shdb*` — the frozen load-2 snapshot (558 tables, 94,032,648 rows) kept as the archive

## Commands

- **Status:** `ssh win01 '"C:/Users/USER/magic-copy/.venv/Scripts/python.exe" "C:/Users/USER/magic-copy/sync_magic.py" status'`
- **Full verification pass (weekend):** `ssh win01 '"...python.exe" "...sync_magic.py" reconcile --skip-done'`
- **Start the loops:** `schtasks /Run /TN MagicSyncTails ; schtasks /Run /TN MagicSyncDiffs`

## Deploy and where it runs

- Runs on win01. Windows scheduled tasks MagicSyncTails and MagicSyncDiffs at logon. State in raw_control on magic_raw; logs in C:\Users\USER\magic-copy\logs. The weekend reconcile is launched detached (Win32_Process.Create).
- Survives a reboot at user logon only: three Windows ONLOGON tasks — MagicSyncTails, MagicSyncDiffs and MagicReconcile (reconcile --skip-done, so it only checks tables not yet verified). Watermarks and state live in PostgreSQL, so a restart loses nothing. A machine left at the login screen starts nothing.

## Current state

Built and running 2026-11-06. Seed complete: 558 tables, 94,032,648 rows, 530 unique update keys, zero writes to Magic. Full verification runs in the weekend window; the two loops and a resumable reconcile start again at the next Windows logon.

## Watch out

- No change feed exists: SQL Server 2017 Express, no primary keys, no rowversion, no CDC/Change Tracking, and Magic's audit log froze in 2017. Seconds only for appends and small tables; big-table edits take minutes.
- Never add triggers, logins, CDC or schema to Magic; all source access is read-only.
- The loops must not read the PostgreSQL admin password (the scheduled-task token cannot); ensure_control stays out of the loop.
- pg8000.native needs timeout=None for long merges; timeout=0 makes the socket non-blocking and fails.
- Three parallel reconcile workers overloaded Express and caused TDS connection timeouts; use one worker.
- Big tables have no index on the change columns, so a tail or diff is a full scan.
- Reboot resilience is at logon, not at boot: the tasks run as the Windows user because they need that user's pgpass; if nobody logs in, the sync does not start.

## Secrets — locations only, never print values

- win01: C:\Users\USER\magic-copy\Hafill.ini holds the Magic SQL login; pgpass.conf holds magic_loader/magic_monitor

## Tags

PostgreSQL, SQL Server, Read-only, Sync, Windows, Magic/Hafill

---

Brief generated 2026-11-08 from `~/Developer/my-projects/context/`. Canonical URL: https://projects.navonsimon.com/context/pil-live-sync.md
