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

pgEdge/snowflake: Snowflake Sequences for PostgreSQL


github.com

David Mitchell 

(571) 577-5914

On Jun 13, 2024, at 2:16 AM, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:

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