Thread: The logical replica crashes when a failover occurs in the physical replica
Hi Community,
I have a problem related to native PostgreSQL logical replication.
I have three servers, two of them (node1 and node2) are configured in physical replica working correctly. The failover happens between them also without problems.
Between them there is a floating virtual IP that always is up on the server that has the role of Primary.
The Primary server has been configured as a Publisher and a third server (node3) has been configured as a Subscriber for a cluster database by connecting to the virtual IP.
The objective is that the Subscriber server receives the data from the Primary server, either (node1 or node2) one is.
The objective is that the Subscriber server receives the data from the Primary server, either (node1 or node2) one is.
But it does not work. When the failover occurs, the Standby server promotes as Primary, acquires the Virtual IP but the Subscriber stops replicating.
The following lines are recorded in the subscriber log:
The following lines are recorded in the subscriber log:
2023-04-05 13:16:10.575 CEST [7154] LOG: el ayudante «apply» de replicación lógica para la suscripción «db_demo_sub» ha iniciado
2023-04-05 13:16:10.590 CEST [7154] ERROR: no se pudo iniciar el flujo de WAL: ERROR: no existe el slot de replicación «db_demo_sub»
2023-04-05 13:16:10.592 CEST [6694] LOG: proceso ayudante «logical replication worker» (PID 7154) terminó con código de salida 1
2023-04-05 13:16:10.590 CEST [7154] ERROR: no se pudo iniciar el flujo de WAL: ERROR: no existe el slot de replicación «db_demo_sub»
2023-04-05 13:16:10.592 CEST [6694] LOG: proceso ayudante «logical replication worker» (PID 7154) terminó con código de salida 1
Any ideas?
We want the Subscriber always replicate the data from the Primary server, whatever it is. That's why the virtual IP, but it's not working.
Thanks!
Re: The logical replica crashes when a failover occurs in the physical replica
From
Peter Gram
Date:
Hi Loles
The problem is that the replication slots for logical replication on the primary are not replicated to the standby server.
EDB is working on a general fix to postgres version 16 or 17.
I think patroni har work around you can look on.
Best regards/Venlig hilsen
Peter Gram
Sæbyholmsvej 18 DK-2500 Valby
Peter Gram
Sæbyholmsvej 18 DK-2500 Valby
Mobile: (+45) 5374 7107
Email: peter.m.gram@gmail.com
On Wed, 5 Apr 2023 at 13:18, Loles <lolesft@gmail.com> wrote:
Hi Community,I have a problem related to native PostgreSQL logical replication.I have three servers, two of them (node1 and node2) are configured in physical replica working correctly. The failover happens between them also without problems.Between them there is a floating virtual IP that always is up on the server that has the role of Primary.The Primary server has been configured as a Publisher and a third server (node3) has been configured as a Subscriber for a cluster database by connecting to the virtual IP.
The objective is that the Subscriber server receives the data from the Primary server, either (node1 or node2) one is.But it does not work. When the failover occurs, the Standby server promotes as Primary, acquires the Virtual IP but the Subscriber stops replicating.
The following lines are recorded in the subscriber log:2023-04-05 13:16:10.575 CEST [7154] LOG: el ayudante «apply» de replicación lógica para la suscripción «db_demo_sub» ha iniciado
2023-04-05 13:16:10.590 CEST [7154] ERROR: no se pudo iniciar el flujo de WAL: ERROR: no existe el slot de replicación «db_demo_sub»
2023-04-05 13:16:10.592 CEST [6694] LOG: proceso ayudante «logical replication worker» (PID 7154) terminó con código de salida 1Any ideas?We want the Subscriber always replicate the data from the Primary server, whatever it is. That's why the virtual IP, but it's not working.Thanks!
Right, I've already seen it.
I have found a solution for my case and it is to recreate the slot in the same script as the promote.
Thanks Peter ❤️
El mié., 5 abr. 2023 14:00, Peter Gram <peter.m.gram@gmail.com> escribió:
Hi LolesThe problem is that the replication slots for logical replication on the primary are not replicated to the standby server.EDB is working on a general fix to postgres version 16 or 17.I think patroni har work around you can look on.Best regards/Venlig hilsen
Peter Gram
Sæbyholmsvej 18 DK-2500 ValbyMobile: (+45) 5374 7107Email: peter.m.gram@gmail.comOn Wed, 5 Apr 2023 at 13:18, Loles <lolesft@gmail.com> wrote:Hi Community,I have a problem related to native PostgreSQL logical replication.I have three servers, two of them (node1 and node2) are configured in physical replica working correctly. The failover happens between them also without problems.Between them there is a floating virtual IP that always is up on the server that has the role of Primary.The Primary server has been configured as a Publisher and a third server (node3) has been configured as a Subscriber for a cluster database by connecting to the virtual IP.
The objective is that the Subscriber server receives the data from the Primary server, either (node1 or node2) one is.But it does not work. When the failover occurs, the Standby server promotes as Primary, acquires the Virtual IP but the Subscriber stops replicating.
The following lines are recorded in the subscriber log:2023-04-05 13:16:10.575 CEST [7154] LOG: el ayudante «apply» de replicación lógica para la suscripción «db_demo_sub» ha iniciado
2023-04-05 13:16:10.590 CEST [7154] ERROR: no se pudo iniciar el flujo de WAL: ERROR: no existe el slot de replicación «db_demo_sub»
2023-04-05 13:16:10.592 CEST [6694] LOG: proceso ayudante «logical replication worker» (PID 7154) terminó con código de salida 1Any ideas?We want the Subscriber always replicate the data from the Primary server, whatever it is. That's why the virtual IP, but it's not working.Thanks!