Скачать или смотреть Resolving the replication slot "pgl_testdb_pgnode_pdaaa79d_sub1" does not exist Error in PostgreSQL

Learn how to handle the common PostgreSQL error related to replication slots and improve your logical replication setup for increased reliability.
---
This video is based on the question https://stackoverflow.com/q/67076327/ asked by the user 'Dushyant Sapra' ( https://stackoverflow.com/u/2644597/ ) and on the answer https://stackoverflow.com/a/67076705/ provided by the user 'Laurenz Albe' ( https://stackoverflow.com/u/6464308/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Getting error `replication slot "pgl_testdb_pgnode_pdaaa79d_sub1" does not exist`

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Understanding the replication slot "pgl_testdb_pgnode_pdaaa79d_sub1" does not exist Error in PostgreSQL

If you’re operating a PostgreSQL database cluster with logical replication enabled, you may have stumbled upon the error message: replication slot "pgl_testdb_pgnode_pdaaa79d_sub1" does not exist. This error usually arises when the master node crashes or goes down, leading to unexpected interruptions in data replication. In this guide, we will delve deeper into the cause of this error and propose effective solutions to prevent it in the future.

What Causes the Error?

In a PostgreSQL cluster with logical replication:

Replication Slots: These are specific slots on the primary server that keep track of where the subscriber is in the replication stream. They ensure the stability and integrity of data being replicated from the master to its subscribers.

Primary Server Failure: If your main master database goes offline and a replica is promoted to be the new master, any replication slots defined on the now-dead master are lost. This means that when you try to use existing replication slots on the new master, they won't be found, leading to the error.

The crux of the problem is that replication slots exist only on the primary node. Hence, when the primary node goes down (and does not recover), you can’t expect the new master to automatically have all the replication slots active as they were defined before.

How to Address the Issue?

Rebuilding Logical Replication Standbys

The most secure and reliable way to deal with this situation is to rebuild your logical replication standbys from scratch after any failover event. Here are the steps involved:

Identify the New Master: Ensure you know which node has been promoted to be the new master following the failure of the primary node.

Drop Existing Subscribers: On the new master, you may need to drop any existing subscribers that reference the old replication slots (which no longer exist). You can do this with the following command:

[[See Video to Reveal this Text or Code Snippet]]

Replace subscription_name with the actual name of your subscription.

Create New Replication Slots: Create new replication slots as follows:

[[See Video to Reveal this Text or Code Snippet]]

Again, make sure to provide the appropriate names according to your needs.

Set Up New Subscribers: After creating the replication slots, you'll need to set up new subscribers pointing to your new master. This will usually involve re-running the initial setup steps used when you first configured logical replication.

Considerations for High Availability

While logical replication provides several benefits, it may not be sufficient for a comprehensive high-availability solution. Consider the following recommendations:

Automated Failover Tools: Utilize automated failover systems, such as Patroni, to manage replication slots more effectively and minimize downtime.

Redundant Configuration: Have a well-thought-out redundant configuration that includes backup and failover nodes so that disruptions can be easily mitigated.

Monitoring and Alerts: Implement a monitoring system that can alert you to potential errors or downtime in the database system before they escalate into noticeable issues.

Conclusion

The error replication slot "pgl_testdb_pgnode_pdaaa79d_sub1" does not exist is a common problem in PostgreSQL when it comes to logical replication and cluster failover. By understanding the underlying structure of replication slots and the necessary steps for setting them up anew after a primary failure, you can optimize your logical replication setup to be more resilient in the face of disruptions.

Feel free to reach out if you