How Can I Capture from PostgreSQL High Availability Setup?

Question

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

Environment

• HVR 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 HVR 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.

HVR creates a replication slot while activating replication and advances its position during capture. This replication slot is named hvr_hubname_channelname_locationname. HVR 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. It's possible to do this using a PostgreSQL extension called pg_failover_slots. For more information, refer to PG Failover Slots documentation.