Skip to main content

Switching from initial load to continuous replication

If there may be open transactions in the source database when the initial load completes, take the following steps to ensure that they are replicated to the target.

Before running the initial load application, run the following command on the source database using an appropriate database client and record the value returned:

  • MariaDB:

    select @@gtid_current_pos;

  • MySQL:

    select current_timestamp;

  • Oracle:

    select min(start_scn) from gv$transaction;

  • PostgreSQL:

    • For PostgreSQL version 10 or higher:

      SELECT pg_current_wal_lsn();

    • For earlier versions:

      SELECT pg_current_xlog_location();

  • SQL Server:

    SELECT sys.fn_cdc_get_max_lsn()AS max_lsn;

When creating the continuous replication application:

  1. Enable recovery.

  2. Use the following setting for the CDC reader:

    • MariaDB Reader: set Start Position to the GTID you recorded before performing the initial load.

    • MySQL Reader, set Start Time to the timestamp you recorded before performing the initial load.

    • Oracle Reader, set Start SCN to the SCN you recorded before performing the initial load.

    • PostgreSQL Reader, set Start LSN to the LSN you recorded before performing the initial load.

    • SQL Server (MSSQL Reader): set Start Position to the LSN you recorded before performing the initial load.

  3. Use the following setting for Database Writer's Ignorable Exception Code property:

    DUPLICATE_ROW_EXISTS, NO_OP_UPDATE, NO_OP_DELETE

  4. When you know all open transactions have completed and been written to the target, undeploy and stop the application, edit the target, clear the Ignorable Exception Code value, save, and deploy and start the application. Since recovery is enabled, writing will resume where it left off, and there should be no missing or duplicate rows.