How Can I Capture from PostgreSQL High Availability Setup?

Question

How can I capture from a PostgreSQL high-availability setup?

Environment

• DSS 6
• PostgreSQL 11 or higher
• Capture using Logical Replication (location property Capture_Method=SQL)

Answer

Background

It is a common practice to use PostgreSQL in a high-availability setup using physical replication. Such setup consists of a primary and one or more standby instances. One of these standby instances can be promoted when the primary becomes unavailable. It is desirable for DSS to continue capturing the changes from the new primary after this happens.

For more information about using PostgreSQL in a high-availability setup, refer to the High Availability, Load Balancing, and Replication chapter of PostgreSQL documentation.

Solution

PostgreSQL logical replication uses replication slots to track the progress of the replication.

DSS creates a replication slot while activating replication and advances its position during capture. This replication slot is named dss_hubname_**channelname_**locationname. DSS will continue capturing changes after an interruption from its last position as long as this replication slot is available.

To ensure continuous data capture in a high-availability setup, you must create this replication slot and synchronize its position on the standby instance based on the primary instance. You can achieve this using a PostgreSQL extension called pg_failover_slots. For more information, refer to pg_failover_slots documentation. Starting with PostgreSQL 17, this functionality is supported natively, so an extension is no longer required.