Re: How to Handle Sequences in Logical Bi-Directional Replication

On Thu, 2024-06-13 at 09:23 +0800, PGSQL DBA wrote:
> I am reaching out to seek your guidance and expertise on handling sequences
> in a bi-directional logical replication setup.
>
> I am planning to set up two PostgreSQL-16 database servers with logical bi-directional
> replication, using the `origin=none` configuration. In this setup, my application will
> be connecting to either of the database servers for DML operations.

You will have to resolve conflicts manually.

> Given this scenario, I am looking for the best practices to handle sequence values to
> ensure consistency and avoid conflicts. Specifically, I would like to understand:
>
> 1. How to configure sequences to avoid duplicate values in both database servers.

On server 1:

CREATE TABLE tab (
id bigint BENERATED ALWAYS AS IDENTITY (START 1 INCREMENT 2)
PRIMARY KEY,
...
);

On server 2:

CREATE TABLE tab (
id bigint BENERATED ALWAYS AS IDENTITY (START 2 INCREMENT 2)
PRIMARY KEY,
...
);

Yours,
Laurenz Albe