Thread: How to Handle Sequences in Logical Bi-Directional Replication
Hi Experts,
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.
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.
2. Whether there are specific settings or configurations in PostgreSQL that facilitate the management of sequences in a bi-directional replication environment.
3. Any recommendations or strategies to ensure smooth and consistent sequence management across the two servers.
I appreciate any guidance you can provide on this matter.
Thank you!
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.
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.
2. Whether there are specific settings or configurations in PostgreSQL that facilitate the management of sequences in a bi-directional replication environment.
3. Any recommendations or strategies to ensure smooth and consistent sequence management across the two servers.
I appreciate any guidance you can provide on this matter.
Thank you!
Hi!
I have planned replacing sequences with UUIDs, which work in distributed networks.
On Thursday, June 13th, 2024 at 04.23, PGSQL DBA <pgsqldba.1987@gmail.com> wrote:
Hi Experts,
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.
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.
2. Whether there are specific settings or configurations in PostgreSQL that facilitate the management of sequences in a bi-directional replication environment.
3. Any recommendations or strategies to ensure smooth and consistent sequence management across the two servers.
I appreciate any guidance you can provide on this matter.
Thank you!
Hi PGSQL DBA,
Here are my 2 cents on this
- Using different ranges for node1 and node2 ( Odd, Even)
- Concatenating node_id to sequence number. ( myseq.nextval || node_id).
- UUID , though there won't be any risk of conflict/collusion but will take more storage.
Regards,
Muhammad Ikram
Bitnine global.
On Thu, Jun 13, 2024 at 9:13 AM Matti Linnanvuori <mattilinnanvuori@protonmail.com> wrote:
Hi!I have planned replacing sequences with UUIDs, which work in distributed networks.On Thursday, June 13th, 2024 at 04.23, PGSQL DBA <pgsqldba.1987@gmail.com> wrote:Hi Experts,
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.
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.
2. Whether there are specific settings or configurations in PostgreSQL that facilitate the management of sequences in a bi-directional replication environment.
3. Any recommendations or strategies to ensure smooth and consistent sequence management across the two servers.
I appreciate any guidance you can provide on this matter.
Thank you!
Muhammad Ikram
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