Skip to main content

Switching from initial load to continuous replication of PostgreSQL sources

When you use an automated pipeline wizard, switching from initial load to continuous replication is handled automatically. In all other cases, you must configure the switch manually.

Switching from initial load to continuous real-time replication of PostgreSQL sources

Do the following to make continuous real-time replication pick up where the initial load left off.

Before running the initial load application, get the current write-ahead log write location or transaction log write location. Run the following command on the source database using an appropriate database client and record the value returned:

  • For PostgreSQL version 10 or higher:

    SELECT pg_current_wal_lsn();

  • For earlier versions:

    SELECT pg_current_xlog_location();

When creating the continuous real-time replication application:

  1. Enable recovery.

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

  3. If the target writer has an Ignorable Exception Code property, set it as follows:

    DUPLICATE_ROW_EXISTS, NO_OP_UPDATE, NO_OP_DELETE

  4. If the target writer has an Ignorable Exception Code property, when you know all open transactions have completed and been written to the target, stop and undeploy 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.

Switching from initial load to continuous incremental replication of PostgreSQL sources

Do the following to make continuous incremental replication pick up where initial load left off:

  1. After initial load completes, query the target to get the highest check column value.

  2. In Incremental Batch Reader, set the Start Position value to the next valid value after the highest. For example:

    • If the check column is of type integer and the highest check column value in the target is 100000, set Start Position to 100001.

    • If the check column is of type timestamp and its precision is milliseconds, set Start Position to one millisecond after the highest check column value in the target.