Seeking Clarification on Logical Replication Start LSN

From: Pradeep Kumar <spradeepkumar29(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Seeking Clarification on Logical Replication Start LSN
Date: 2024-02-27 11:50:07
Message-ID: CAJ4xhPn3fXffM+t0d5NqjXXg+XgBAzpO5jf9z4O3AjcFOYf0Qw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Dear Postgres Community,

I hope this email finds you well. I am reaching out to seek clarification
on an issue I am encountering with logical replication in PostgreSQL.

My specific question pertains to determining the appropriate LSN (Log
Sequence Number) from which to start logical replication. Allow me to
provide detailed context for better understanding:

During the process of performing a parallel pg_basebackup, I concurrently
execute DML queries. As part of the pg_basebackup command, I utilize the
option create-slot to create a replication slot. Subsequently, upon
completion of the base backup, I initiate logical replication using the
restart_lsn obtained during the execution of the pg_basebackup command. My
intention is to ensure consistency between two database clusters.

However, I am encountering errors during this process. Specifically, I
receive the following error message on the source side:

"""
2024-02-27 16:20:09.271 IST [2838457] ERROR: duplicate key value violates
unique constraint "table_15_36_pkey"
2024-02-27 16:20:09.271 IST [2838457] DETAIL: Key (col_1, col_2)=(23,
2024-02-27 15:14:24.332557) already exists.
2024-02-27 16:20:09.272 IST [2834967] LOG: background worker "logical
replication worker" (PID 2838457) exited with exit code 1
Upon analysis, it appears that the errors stem from starting the logical
replication with an incorrect LSN, one that has already been applied to the
target side, leading to duplicate key conflicts.
"""

In light of this issue, I seek guidance on determining the appropriate LSN
from which to commence logical replication.

To further clarify my problem:

1)I have a source machine and a target machine.
2) I perform a backup from the source to the target using pg_basebackup.
3) Prior to initiating the base backup, I create logical replication slots
on the source machine.
4) During the execution of pg_basebackup, DML queries are executed, and I
aim to replicate this data on the target machine.
5) My dilemma lies in determining the correct LSN to begin the logical
replication process.
Your insights and guidance on this matter would be immensely appreciated.
Thank you for your time and assistance.

Warm regards,
Pradeep

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jelte Fennema-Nio 2024-02-27 11:52:22 Re: Improve readability by using designated initializers when possible
Previous Message Andrey M. Borodin 2024-02-27 11:49:03 Re: Injection points: some tools to wait and wake