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. You can achieve this using a PostgreSQL extension called pg_failover_slots. PostgreSQL 18 is the final release for which EDB supports the extension. For more information, see pg_failover_slots documentation.
Starting with PostgreSQL 17, PostgreSQL can alternatively synchronize logical replication slots natively. However, this applies only to slots created with the failover option enabled and requires configuring the PostgreSQL servers. On the standby, set sync_replication_slots, hot_standby_feedback, and primary_slot_name, and specify a valid dbname in primary_conninfo. On the primary, set synchronized_standby_slots to prevent the failover slot from advancing beyond the standby. For more information, see Replication Slot Synchronization in the PostgreSQL documentation.
HVR doesn't create its replication slot with the failover option enabled, so native PostgreSQL slot synchronization doesn't copy the HVR replication slot to the standby. On PostgreSQL 17 and 18, use the pg_failover_slots extension instead, on platforms where EDB supports it. On PostgreSQL 19 and later, the HVR replication slot can't be synchronized to the standby.
If the replication slot is not available on the promoted standby, HVR cannot resume capture from its last position, and you must perform a Refresh.