--- title: "Standby Database Failback Merges in PostgreSQL 16: Preventing Primary Key Collisions With UUIDs and Split Sequence Ranges | GreenTree Field Notes" url: https://www.greentreehosting.com/field-notes/postgres-standby-failback-merge-id-collision-and-uuid-vs-sequence-ranges/ description: "By Gene Salvatore: Why an append-only failback merge using ON CONFLICT (id) DO NOTHING will silently skip standby form submissions if both nodes use default sequential integer IDs—and how UUID primary keys or odd/even split sequences prevent primary key collisions." format: text/markdown generated_from: prerendered HTML (build time) --- [ALL SYSADMIN FIELD NOTES & RUNBOOKS](https://www.greentreehosting.com/field-notes) FN-2026-07 // HA DATABASE OPSDNS & Linux Infrastructure · 8 min read # Writable Standby PostgreSQL Failback Merges: Why ON CONFLICT (id) DO NOTHING Silently Drops Rows on Sequential IDs (And How to Fix It With UUIDs or Split Sequence Ranges) When you run a writable warm-standby PostgreSQL database so contact forms and quote requests still save during primary maintenance, merging those standby rows back with ON CONFLICT (id) DO NOTHING looks safe—until you realize what happens if both servers hand out sequential integer IDs. Published: 2026-10-25•Last tested: 2026-10-25•Tested on PostgreSQL 16.x (Ubuntu 24.04 LTS) with built-in core gen_random_uuid() (UUIDv4, native since PG 13) and partitioned IDENTITY sequences 01 // THE SYMPTOM A form submission or lead saved on the warm-standby database during a brief failover disappears without any SQL error when the failback script merges standby rows back to the primary. 02 // THE ROOT CAUSE The table uses a standard sequential integer primary key (SERIAL or GENERATED BY DEFAULT AS IDENTITY) starting at the same counter on both servers. A row created on the standby gets id = 412 while a different row on the primary already has id = 412—so ON CONFLICT (id) DO NOTHING silently skips the standby row. 03 // THE PERMANENT FIX Audit every table in the failback merge list: use UUID primary keys (DEFAULT gen_random_uuid()) on append-only lead/submission tables, or configure non-overlapping odd/even sequence steps (INCREMENT BY 2) dynamically above MAX(id)—and check secondary unique constraints and payload hashes before skipping. PREFERRED PRIMARY KEY UUIDv4 (PG 13+) Built-in gen_random_uuid() makes 128-bit ID collisions astronomically improbable across nodes (native uuidv7() in PG 18+) INTEGER FALLBACK Odd / Even Step Primary uses INCREMENT BY 2 (odd IDs); Standby uses INCREMENT BY 2 (even IDs) seeded dynamically above MAX(id) MERGE SCOPE New Inserts Only Append-only merge reconciles new form submissions; row edits require NTP-synced updated_at LWW logic SECONDARY CONSTRAINTS Unique & Audit Account for secondary UNIQUE columns (e.g. email) and log payload mismatches so conflicts are never silent **Sysadmin Safety Notice:**Test sequence step alterations and failback merge scripts on a staging database snapshot first. Never run ON CONFLICT (id) DO NOTHING across two active/standby databases until you have verified that their primary key generators cannot assign the same ID to two different rows. INTERACTIVE SYSADMIN RUNBOOK · SAVED IN YOUR BROWSER ## Interactive Standby PostgreSQL Failback Merge Audit Checklist Run through these 6 checks before enabling automated standby-to-primary row reconciliation on any PostgreSQL database: PROGRESS: 0 OF 6 STEPS VERIFIED (0%)CLICK ANY STEP TO CHECK OFF Audit the data_type, column_default, and secondary UNIQUE constraints of every table Identify which tables use uuid (built-in gen_random_uuid() since PostgreSQL 13, no pgcrypto needed) versus integer/bigint backed by nextval() sequences, and list any secondary UNIQUE indexes (such as UNIQUE(email)) that could still conflict during a merge. Use UUID primary keys (DEFAULT gen_random_uuid()) for all form submissions, leads, and event logs With 122 bits of random entropy in UUIDv4 (and native time-ordered uuidv7() in PostgreSQL 18+), collisions across independent primary and standby nodes are astronomically improbable. For legacy integer/bigint tables, partition sequence ranges dynamically above MAX(id) (Odd on Primary, Even on Standby) Never hard-code a fixed restart number (which fails if the table already has higher IDs). Set INCREMENT BY 2 and seed above MAX(id) with explicit parity: setval(seq, COALESCE(MAX(id) - (1 - MAX(id) % 2), 1), true) on Primary (odd) and setval(seq, COALESCE(MAX(id) - (MAX(id) % 2), 2), true) on Standby (even). Document clearly which tables support append-only insert merges vs. NTP-synchronized row edits ON CONFLICT (id) DO NOTHING only merges newly inserted rows (such as contact forms and quote requests), not edits to existing records—and any updated_at last-write-wins update rule requires both servers’ clocks to be synchronized via NTP (chronyd). Detect and log payload mismatches or secondary UNIQUE conflicts before skipping Before ignoring a conflict on an integer-keyed table (or a table with a secondary unique constraint like email), check whether the created_at or payload matches; if not, route the row into a failback_conflict_quarantine table so zero data is silently dropped. Re-seed sequence parity explicitly after every failback merge or pg_restore After merging standby rows back into the primary, MAX(id) on the primary may be an even standby ID! Never run a plain setval(seq, MAX(id))—always enforce parity: Primary (odd) = setval(seq, COALESCE(MAX(id) - (1 - MAX(id) % 2), 1), true); Standby (even) = setval(seq, COALESCE(MAX(id) - (MAX(id) % 2), 2), true). FIELD NOTE 01 // THE SILENT COLLISION TRAP ## 1. Why ON CONFLICT (id) DO NOTHING Can Cause the Exact Data Loss You Built It to Prevent Suppose you run a primary PostgreSQL 16 server and a warm-standby PostgreSQL 16 server. During a brief network hiccup or maintenance window, your standby server accepts a new customer quote request and inserts it into the `leads` table. Five minutes later, your reconciliation script connects over the encrypted mesh and runs `INSERT INTO leads ... ON CONFLICT (id) DO NOTHING` to merge any new standby rows back to the primary. At first glance, `ON CONFLICT (id) DO NOTHING` looks completely idempotent: if the row is already on the primary, skip it; if it is new, insert it. **But that assumption only holds if `id` uniquely identifies the same logical submission across both servers.** If your `leads` table uses a standard sequential integer (`SERIAL` or `BIGSERIAL`) and both databases were cloned from the same nightly snapshot at `id = 411`, then: • **1. On the Primary:** A visitor on an ISP with a cached DNS record submits a form and gets assigned `id = 412`. • **2. On the Standby:** A second visitor routed to the standby submits a completely different form and *also* gets assigned `id = 412`. • **3. During Failback Merge:** When the merge script runs `INSERT INTO leads ... ON CONFLICT (id) DO NOTHING`, PostgreSQL sees that `id = 412` already exists on the primary, **silently skips the standby row without raising an error**, and drops the second customer’s lead on the floor! | Primary Key Strategy | Behavior During Standby Failback Merge | Production Safety Verdict | | --- | --- | --- | | Unpartitioned SERIAL / IDENTITY (1, 2, 3...) | Both nodes assign the same next integer to different submissions | UNSAFE — ON CONFLICT (id) DO NOTHING silently drops colliding rows | | UUID Primary Key (gen_random_uuid()) | Each node generates an independent 128-bit UUID per submission | SAFE FOR NEW INSERTS — Collisions are astronomically improbable | | Odd/Even Split Sequences (INCREMENT BY 2) | Primary issues 1, 3, 5...; Standby issues 2, 4, 6... | SAFE FOR NEW INSERTS — Integer IDs never overlap between nodes | | In-Place Row Edits (UPDATE existing row) | ON CONFLICT DO NOTHING ignores updates to existing IDs | Requires NTP-synced clocks (chronyd) + ON CONFLICT DO UPDATE WHERE EXCLUDED.updated_at > ... | | Secondary UNIQUE Constraints (e.g. UNIQUE(email)) | Can still raise constraint errors even when primary key IDs do not collide | Keep form/lead tables append-only without secondary unique indexes, or handle the secondary constraint explicitly | FIELD NOTE 02 // AUDITING & FIXING PRIMARY KEYS ## 2. Auditing Every Table’s ID Type and Configuring UUIDs or Dynamic Odd/Even Sequence Parity Before you claim that a standby database merges form submissions back cleanly, you have to inspect the actual schema of every table in your fleet (`information_schema.columns`)—and watch out for two subtle traps that catch even experienced DBAs. First, for greenfield tables—especially contact forms, quote requests, pin-board notes, and audit logs—`UUID` (`id UUID PRIMARY KEY DEFAULT gen_random_uuid()`) is the cleanest choice. Since PostgreSQL 13, `gen_random_uuid()` is built directly into core PostgreSQL (no `pgcrypto` extension required) and generates 128-bit UUIDv4 values (while native time-ordered `uuidv7()` arrived in PostgreSQL 18), making ID collisions across independent nodes astronomically improbable. Also ensure that append-only form submission tables do not carry secondary `UNIQUE` constraints (such as `UNIQUE (email)`), which would still raise a constraint error if the same customer submits twice. Second, for existing tables that use integer `SERIAL` or `BIGINT` primary keys, **never hard-code a fixed sequence restart like `RESTART WITH 10001`** (which will immediately fail with duplicate-key errors if the table already has rows above `10,001`), and **never run a plain `setval(seq, MAX(id))` after a failback merge**. Why? Because once the primary table merges in the standby’s even-numbered rows, `MAX(id)` on the primary might be an *even* number! If you set the primary’s `INCREMENT BY 2` sequence to an even `MAX(id)`, the primary will start handing out even numbers (`MAX(id) + 2`)—colliding directly with the standby’s range! Always calculate the parity offset explicitly from `MAX(id)` as shown below. PostgreSQL 16 Commands to Audit Primary Keys and Seed Dynamic Odd/Even Sequence Parity Above MAX(id) ``` -- 1. Audit primary key column types and defaults across all public tables SELECT tc.table_name, kcu.column_name, c.data_type, c.column_default, c.is_identity FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name AND tc.table_schema = kcu.table_schema JOIN information_schema.columns c ON c.table_schema = tc.table_schema AND c.table_name = tc.table_name AND c.column_name = kcu.column_name WHERE tc.constraint_type = 'PRIMARY KEY' AND tc.table_schema = 'public' ORDER BY tc.table_name; -- 2. Option A (Preferred for Lead/Form Tables): Core Built-In UUIDv4 Primary Key (No pgcrypto Needed in PG 13+) CREATE TABLE IF NOT EXISTS public.form_submissions ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), site_domain TEXT NOT NULL, submitted_at TIMESTAMPTZ NOT NULL DEFAULT now(), origin_node TEXT NOT NULL DEFAULT 'primary', payload JSONB NOT NULL ); -- 3. Option B (For Legacy Integer Tables): Dynamic Odd/Even Parity Above Current MAX(id) -- Run on PRIMARY node (nextval() is guaranteed to be the first ODD integer above MAX(id), even if MAX(id) is even): ALTER SEQUENCE public.leads_id_seq INCREMENT BY 2; SELECT setval( 'public.leads_id_seq', COALESCE((SELECT MAX(id) - (1 - (MAX(id) % 2)) FROM public.leads), 1), true ); -- Run on STANDBY node (nextval() is guaranteed to be the first EVEN integer above MAX(id), even if MAX(id) is odd): ALTER SEQUENCE public.leads_id_seq INCREMENT BY 2; SELECT setval( 'public.leads_id_seq', COALESCE((SELECT MAX(id) - (MAX(id) % 2) FROM public.leads), 2), true ); ``` ⚠ Test on a non-production system first and verify backups before running administrative commands. SYSADMIN Q&A ## Common Questions on Standby PostgreSQL ID Collisions & UUID/Sequence Fixes ### Does an append-only standby merge also synchronize edits (UPDATEs) to existing rows? No. An INSERT ... ON CONFLICT DO NOTHING reconciliation script only merges newly created rows (such as contact form submissions, quote requests, and new pin-board entries). If an application allows users to edit existing records during a failover window, those tables require an updated_at timestamp column, an explicit ON CONFLICT (id) DO UPDATE WHERE EXCLUDED.updated_at > table.updated_at rule, and tightly synchronized system clocks via NTP (chronyd) on both servers so clock skew never causes an older edit to overwrite a newer one. ### Why can’t you just run setval(seq, MAX(id)) after merging standby rows or restoring a dump? Because after a failback merge, the primary table now contains the standby’s even-numbered IDs, so MAX(id) on the primary can be even! If you run a plain setval(seq, MAX(id)) on a sequence with INCREMENT BY 2, the primary will start issuing even numbers (MAX(id) + 2)—putting it right into the standby’s even range. Instead, use explicit parity math: setval(seq, COALESCE(MAX(id) - (1 - MAX(id) % 2), 1), true) on the Primary so the next ID is always the first odd number above MAX(id), and setval(seq, COALESCE(MAX(id) - (MAX(id) % 2), 2), true) on the Standby so its next ID is always the first even number above MAX(id). ### How can I verify that no standby rows were skipped due to a collision? Before inserting from the standby staging table into the primary table, run a join on id where the payload or created_at timestamps differ. If any row shares an id with a different primary record, insert it with a newly generated ID or log it into a conflict review table rather than silently dropping it. COMPANION CORNERSTONE REFERENCE GUIDE (/LIBRARY) ### Inside GreenTree’s High-Availability Cloud Stack: Multi-Node NVMe Linux, Encrypted WireGuard Mesh, Authoritative 60-Second DNS & Standby Database Failback Read the complete 3,900+ word architectural walkthrough of our active/warm-standby Ubuntu 24.04 LTS NVMe cloud, encrypted private mesh, and standby PostgreSQL failback design. [Read Full Engineering Guide](https://www.greentreehosting.com/library/inside-greentrees-high-availability-linux-cloud-wireguard-mesh-and-60s-failover-stack) HIGH-AVAILABILITY CLOUD & POSTGRESQL ### Want Your Website & Database on Our Monitored Linux & PostgreSQL Cloud? Our $99/mo Core Cloud and $199/mo Core Cloud + Management plans include a dedicated PostgreSQL 16 database, 60-second external monitoring, and 1 SmarterMail Enterprise mailbox—with multi-node warm-standby failover and off-node replication available for high-availability deployments. [Explore Cloud & High-Availability Plans](https://www.greentreehosting.com/hosting)[Read the Full HA Cloud Architecture Guide](https://www.greentreehosting.com/library/inside-greentrees-high-availability-linux-cloud-wireguard-mesh-and-60s-failover-stack) RELATED FIELD NOTES & RUNBOOKS ## Related Notes From the Sysadmin Desk [View All 8 Field Notes](https://www.greentreehosting.com/field-notes) [DNS & Linux Infrastructure7 min readAuthoritative BIND DNS in the Real World: Why Secondary Nameservers Serve Stale TTLs (And How to Catch Zone Drift in 60 Seconds)Always increment the YYYYMMDDnn SOA serial before rndc reload, strip stale per-record TTL overrides, align registrar glue/NS records with the zone, and query every delegated nameserver directly.Open Field Note & Checklist](https://www.greentreehosting.com/field-notes/bind-dns-soa-serial-drift-and-60-second-ttl-troubleshooting)[Server Transfers & Cutovers9 min readThe Zero-Downtime Server Transfer Runbook: How We Move Live Websites, Databases, and Mailboxes Without Losing a Single Order or EmailExecute a 5-stage cutover: lower DNS TTLs to 60 seconds 48 hours early, stage and test via local hosts-file override, pre-issue TLS certificates, run a final sub-second rsync + database delta sync, and enforce a 5-source 301 redirect map.Open Field Note & Checklist](https://www.greentreehosting.com/field-notes/zero-downtime-server-migration-and-cutover-runbook)[Server Transfers & Cutovers9 min readMoving Off Usage-Metered Cloud (Netlify Credits & Non-Pausing Supabase Compute): Our Production Cutover Runbook to Flat-Rate Docker & PostgreSQL 16Export the Supabase PostgreSQL schema and data via pg_dump, replace client-side VITE_SUPABASE_ANON_KEY calls with server-side Express + pg pool endpoints, translate netlify.toml redirects/headers into server.mjs, and deploy inside an isolated Docker container.Open Field Note & Checklist](https://www.greentreehosting.com/field-notes/migrating-off-metered-netlify-and-supabase-to-flat-rate-docker-and-postgresql)