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