Configure Oracle GoldenGate CDC Extract for EnterpriseDB (EDB) Subscriber Node

In EnterpriseDB (EDB) Postgres bidirectional replication environments, Oracle GoldenGate CDC Extract can capture changes from a dedicated subscriber-only node instead of directly from the active database nodes. This deployment model reduces the capture workload on production systems while providing a consolidated source for downstream change data capture. For details on bidirectional replication in EnterpriseDB Postgres Distributed, see Replication Sets.

Oracle GoldenGate bidirectional replication for EnterpriseDB Postgres requires configuring the subscriber-only node. A subscriber-only node is basically a downstream replication node or a CDC aggregation node that receives changes from active active bidirectional data sources and provides a dedicated database from which Oracle GoldenGate can capture them.

For explanation purposes, the subscriber-only node is referred to as Master 3 and the active active bidirectional data sources are referred to as Master 1 and Master 2.

Here is the topology for Oracle GoldenGate CDC Extract to capture from the subscriber-only node, (Master 3):

• Master 1 and Master 2 are configured in the active-active mode using bidirectional replication.
• Master 3 is subscribed to both Master 1 and Master 2.
• Oracle GoldenGate CDC Extract run exclusively on the subscriber-only node, Master 3.

Based on this topology, here are the responsibilities for each node:

• Master 1 and Master 2: Accepts application reads and writes
• Master 3: Subscriber-only node used for Oracle GoldenGate CDC Extract

While Master 1 and Master 2 nodes accept reads and writes from applications, Master 3 is intended to function as a subscriber-only node for Oracle GoldenGate CDC Extract. It contains the consolidated data originating from the active nodes.

Configure Oracle GoldenGate CDC Extract for Performing Operations on the Subscriber Node

Before you begin configuring the CDC Extract, consider the following best practices:

• Ensure that replication from Master 1 and Master 2 to Master 3 is fully synchronized before registering Oracle GoldenGate CDC Extract.
• Monitor both replication lag (Master to Subscriber) and CDC Extract lag regularly.
• Verify WAL retention and replication slot health to prevent data loss.

The following steps are required on the subscriber node and on the Oracle GoldenGate side to enable performing various DDL and DML operations:

1. Peform DDL Operations: Execute all DDL statement on Master 1 and Master 2 only. These changes are replicated automatically to Master 3 through bidirectional replication.

2. Enable supplemental logging: After DDL changes are replicated successfully, connect to Master 3 and run the trandata command for the required tables. This enables supplemental logging for required columns, which is mandatory to capture before images for UPDATE and DELETE operations.

3. Perform DML Operations: Perform all DML operations on Master 1 or Master 2 only. Ensure that Master 3 is fully configured before starting any DML activity.

4. Set up the Replication Slot and Publication: Create the replication slot and publication on Master 3.

5. Add Tables to the Publication: Before performing any DML operations, add all required tables to the publication created on Master 3. Only the tables included in the publication are going to be captured by the CDC Extract.

   Note: Ensure that publication is not created on Master 1 and/or Master 2.

6. Register and Create a CDC Extract: Connect to Master 3 and register the Extract to create a replication slot in the subscriber node and then add the CDC Extract. For steps to register a CDC Extract for PostgreSQL, see Register Extract for PostgreSQL from the Admin Client.

   Note: Ensure that Extract is not connected to Master 1 or Master 2.

7. Configure CDC Extract: Configure the CDC Extract to connect only to Master 3 or the subscriber node. In this topology, the Extract must not connect directly to Master 1 or Master 2.

8. Configure Heartbeat (Optional, Recommended): If you use the heartbeat functionality:

   a. Create the heartbeat seed table on Master 1 or Master 2.

   b. After it replicates to Master 3, add the heartbeat table to the publication on Master 3.

   c. Configure the heartbeat for the CDC Extract in Oracle GoldenGate to keep the CDC pipeline active and to monitor latency.

   Note: To configure heartbeat functionality for Extract in Oracle GoldenGate, see Add Heartbeat Table. Also see ADD HEARTBEATTABLE in the Oracle GoldenGate Command Line Reference guide.

9. Verify the Configured Processes: Check that the replication slot, publication, and CDC Extract processes exist and are working as expected. Ensure that the replication lag and Extract lag is normal.