When setting up multi-master synchronization between two PostgreSQL databases—such as one on a local server and another on an external or remote server—ID conflicts can arise due to auto-incrementing primary key sequences. If both databases generate IDs independently, there’s a risk that the same ID may be used for different records, leading to data conflicts during sync.
This post explains how to configure PostgreSQL sequences to prevent ID collisions in a multi-master environment.
1. If You Are Using a Fresh Database
If your databases are new and don’t contain any data yet, you can safely assign each server a unique sequence pattern for all tables. This approach uses separate ID ranges—for example, one server uses odd numbers while the other uses even numbers.
Step 1: Connect to your PostgreSQL database
psql -U your_pg_user -d your_database_name
Replace your_pg_user and your_database_name with your actual PostgreSQL username and database name.
Step 2: Apply Sequence Configuration
On Server 1 (Odd IDs):
DO $$
DECLARE
seq_name TEXT;
BEGIN
FOR seq_name IN
SELECT sequence_name
FROM information_schema.sequences
WHERE sequence_schema = 'public'
LOOP
EXECUTE format('ALTER SEQUENCE %I RESTART WITH 1 INCREMENT BY 2', seq_name);
END LOOP;
END $$;
On Server 2 (Even IDs):
DO $$
DECLARE
seq_name TEXT;
BEGIN
FOR seq_name IN
SELECT sequence_name
FROM information_schema.sequences
WHERE sequence_schema = 'public'
LOOP
EXECUTE format('ALTER SEQUENCE %I RESTART WITH 2 INCREMENT BY 2', seq_name);
END LOOP;
END $$;
This ensures each server generates unique, non-overlapping ID values, making your sync process conflict-free from the start.
2. If Your Databases Already Contain Data
If your databases already have existing records, resetting sequences arbitrarily could lead to duplicate IDs. Instead, you must first determine the maximum ID already used in each table and configure the sequence to continue from the next available number, ensuring IDs remain unique across systems.
This ensures:
* No overlapping IDs during sync
* Continuity of existing data without duplication or corruption
For Server 1 (Odd IDs):
DO $$
DECLARE
seq_name TEXT;
tbl_name TEXT;
col_name TEXT := 'id';
max_id BIGINT;
col_exists BOOLEAN;
BEGIN
FOR seq_name IN
SELECT sequence_name
FROM information_schema.sequences
WHERE sequence_schema = 'public'
LOOP
tbl_name := replace(seq_name, '_id_seq', '');
-- Check if the table has an 'id' column
SELECT EXISTS (
SELECT 1
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name = tbl_name
AND column_name = col_name
) INTO col_exists;
IF col_exists THEN
BEGIN
EXECUTE format('SELECT MAX(%I) FROM %I', col_name, tbl_name) INTO max_id;
IF max_id IS NULL THEN
max_id := 0;
END IF;
-- Set starting point to next available odd number
IF (max_id + 1) % 2 = 0 THEN
max_id := max_id + 2;
ELSE
max_id := max_id + 1;
END IF;
EXECUTE format('ALTER SEQUENCE %I RESTART WITH %s INCREMENT BY 2', seq_name, max_id);
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE 'Skipped sequence % due to table error', seq_name;
END;
ELSE
RAISE NOTICE 'Skipped sequence % (no "id" column in table %)', seq_name, tbl_name;
END IF;
END LOOP;
END $$;
For Server 2 (Even IDs):
The logic is the same, except we ensure the next available number is even:
Replace this block:
IF (max_id + 1) % 2 = 0 THEN
max_id := max_id + 2;
ELSE
max_id := max_id + 1;
END IF;
With this:
IF (max_id + 1) % 2 = 1 THEN
max_id := max_id + 1;
ELSE
max_id := max_id + 2;
END IF;
By separating ID sequences between servers using even/odd logic, you can safely support bi-directional data sync across multiple PostgreSQL instances without ID collisions. Discover practical strategies for database monitoring in How to Monitor PostgreSQL Internals Using pg_stat Catalog Views. This guide explains how to use PostgreSQL’s pg_stat catalog views to gain insight into internal processes, track performance metrics, and troubleshoot issues more effectively—helping you maintain optimal database health and efficiency.