Add column to source table

Helyx supports the addition of new columns to Oracle source tables while the replication process is running. When a column is added at the source, the schema change is automatically detected and propagated to the corresponding PostgreSQL target table during runtime, without requiring the replication pipeline to be recreated or stopped permanently. This enables schema evolution with minimal operational disruption.

Please note that newly added columns may not appear immediately in the destination table until data is inserted or updated in those columns at the Oracle source. Once records containing values for the new columns are captured by the replication process, Helyx applies the corresponding schema and data changes to the PostgreSQL target table.

Follow the steps below to add new columns to Oracle source tables during an active replication setup:

1. Pause the publication service to temporarily stop capturing changes from the Oracle source.
2. Pause the subscription service to temporarily stop applying changes to the PostgreSQL target.
3. Add the required column or columns to the Oracle source table using the approved database change process.
4. Resume the publication service so that Helyx can continue capturing changes from the Oracle source.
5. Resume the subscription service so that the captured changes can be applied to the PostgreSQL target table.

During the pause state, no committed data changes are lost. Helyx retains the replication position and resumes processing from the last captured offset or SCN when the publication and subscription services are started again. This ensures replication continuity and maintains data consistency between the Oracle source and PostgreSQL target.

Pause and Resume Replication

Use the following commands to pause or resume replication services.

Pause Replication

Pause the publication or subscription service when required.

<path_to_serverconfig> -pause -pub <path_to_publication.config>

<path_to_serverconfig> -pause -sub <path_to_subscription.config>

Resume Replication

Resume the publication or subscription service after the required database changes have been completed.

<path_to_serverconfig> -resume -pub <path_to_publication.config>

<path_to_serverconfig> -resume -sub <path_to_subscription.config>

Tables Without Primary Keys

For tables configured without a primary key or unique key, use the -nopk commands to pause and resume replication.

Pause Replication for Non-Primary-Key Tables

<path_to_serverconfig> -pause-nopk -pub <path_to_publication.config>

<path_to_serverconfig> -pause-nopk -sub <path_to_subscription.config>

Resume Replication for Non-Primary-Key Tables

<path_to_serverconfig> -resume-nopk -pub <path_to_publication.config>

<path_to_serverconfig> -resume-nopk -sub <path_to_subscription.config>