PostgreSQL Table Changes Not Captured After HVR Channel Reactivation

Issue

After deactivating and reactivating an HVR channel, some changes aren't capture from PostgreSQL tables configured with REPLICA IDENTITY FULL.

Environment

• HVR 6
• Source: PostgreSQL

Resolution

To resolve this issue:

1. Run the following command to verify that each affected table uses REPLICA IDENTITY FULL:

   ALTER TABLE <table_name> REPLICA IDENTITY FULL;
   

2. Re-enroll the affected tables in the HVR channel:
   • Run Refresh to recreate them on the target.
   • Run Activate Replication to enroll them for replication.
3. If the issue persists, add the following environmental actions to enable detailed tracing:
   • HVR_SCAN_TRACE=2
   • HVR_TXGROUP_TRACE=1
4. Review the capture logs for error messages or missed transactions. You can also:
   • Use pg_logical_slot_peek_changes() to troubleshoot replication issues without advancing the replication slot.
   • Use PostgreSQL commands to check the status of the active WAL and slot.

If the issue continues, contact our support team with your log files and capture configurations.

Cause

This issue occurs when the REPLICA IDENTITY setting isn't reapplied correctly during channel deactivation and reactivation, or when the tables aren't re-enrolled in the HVR channel after the setting changes. When this happens, we don't correctly capture changes for the affected tables.

For more information, see PostgreSQL as a Source.