Postgres + pgvector data migration plan for the self-host GPU host migration

[JSONbored]

Part of #4325.

Context

The review engine's primary datastore is Postgres with the pgvector extension (--profile postgres in docker-compose.yml). This needs to move to the new host with full data integrity and minimal write-downtime, while the current host stays authoritative until cutover.

Requirements

• A migration approach that supports at least one full sync followed by incremental catch-up (e.g. pg_dump/pg_restore for the initial copy, then either logical replication or a final short-downtime pg_dump delta at actual cutover time) — a single one-shot dump taken hours before cutover is not sufficient given the current host keeps taking live writes throughout this epic.
• Explicit row-count and checksum verification of at least the highest-value tables post-restore (e.g. pull_requests, ai_review_cache, audit_events, repositories) — silent partial restores are the failure mode to design against.
• pgvector extension version parity confirmed on the new host before restore (an extension-version mismatch is a plausible restore-time failure, not a hypothetical).
• A written rollback: if the restored data fails verification, cutover does not proceed and the current host remains authoritative.

Deliverables

• Migration method chosen and documented (dump/restore vs. logical replication) with the reasoning for the choice
• Initial full copy completed and row-count/checksum-verified against the source
• Incremental catch-up mechanism validated (a write made on the source after the initial copy is confirmed to reach the new host)
• pgvector version confirmed matching before any real restore is attempted
• Rollback criteria written down explicitly, not left implicit

Expected outcome

A validated, repeatable procedure to bring the new host's Postgres to full parity with the current host's live data, on demand, right up to the actual cutover moment.

Effort

L

[linear-code]

JSO-3304

[JSONbored]

Verified live via direct inspection of edge-nl-01 (2026-07-09):

• pgvector extension present, version 0.8.4.
• Real, substantial row counts: pull_requests 5333, ai_review_cache 5955, audit_events 256307, repositories 21.
• Most recent audit_events row is from minutes before this check — data is live and actively growing, not a stale one-time copy.

Closing as done based on this outcome verification. Note: I couldn't retroactively confirm the specific checksum-verification-against-source step from the original deliverables list, since the source host is reportedly already decommissioned (see the discussion on #4334) — if that verification was performed and documented somewhere private, no action needed; flagging only so it isn't silently assumed.