MOLT Replicator

On this page

MOLT Replicator continuously replicates changes from a source database to CockroachDB as part of a database migration. It supports migrations from a source database to CockroachDB with minimal downtime, and enables backfill from CockroachDB to your source database for failback scenarios to preserve a rollback option during a migration window.

MOLT Replicator consumes change data from PostgreSQL logical replication streams, MySQL GTID-based replication, Oracle LogMiner, and CockroachDB changefeeds (for failback).

Terminology

• Checkpoint: The position in the source database's transaction log from which replication begins or resumes: LSN (PostgreSQL), GTID (MySQL), or SCN (Oracle).
• Staging database: A CockroachDB database used by Replicator to store replication metadata, checkpoints, and buffered mutations. Specified with --stagingSchema and automatically created with --stagingCreateSchema. For details, refer to Staging database.
• Forward replication: Replicate changes from a source database (PostgreSQL, MySQL, or Oracle) to CockroachDB during a migration. For usage details, refer to Forward replication (after initial load).
• Failback: Replicate changes from CockroachDB back to the source database. Used for migration rollback or to maintain data consistency on the source during migration. For usage details, refer to Failback replication.

Prerequisites

Supported databases

MOLT Replicator supports the following source and target databases:

• PostgreSQL 11-16
• MySQL 5.7-8.4
• Oracle Database 19c (Enterprise Edition) and 21c (Express Edition)
• CockroachDB (all currently supported versions)

Database configuration

The source database must be configured for replication:

User permissions

The SQL user running MOLT Replicator requires specific privileges on both the source and target databases:

How it works

MOLT Replicator supports forward replication from PostgreSQL, MySQL, and Oracle, and failback from CockroachDB:

• PostgreSQL source (pglogical): MOLT Replicator uses PostgreSQL logical replication, which is based on publications and replication slots. You create a publication for the target tables, and a slot marks consistent replication points. MOLT Replicator consumes this logical feed directly and applies the data in sorted batches to the target.

• MySQL source (mylogical): MOLT Replicator relies on MySQL GTID-based replication to read change data from MySQL binlogs. It works with MySQL versions that support GTID-based replication and applies transactionally consistent feeds to the target. Binlog features that do not use GTIDs are not supported.

• Oracle source (oraclelogminer): MOLT Replicator uses Oracle LogMiner to capture change data from Oracle redo logs. Both Oracle Multitenant (CDB/PDB) and single-tenant Oracle architectures are supported. Replicator periodically queries LogMiner-populated views and processes transactional data in ascending SCN windows for reliable throughput while maintaining consistency.

• Failback from CockroachDB (start): MOLT Replicator acts as an HTTPS webhook sink for a single CockroachDB changefeed. Replicator receives mutations from source cluster nodes, can optionally buffer them in a CockroachDB staging cluster, and then applies time-ordered transactional batches to the target database. Mutations are applied as UPSERT or DELETE statements while respecting foreign-key and table dependencies.

Replicator commands

MOLT Replicator provides four commands for different replication scenarios. For example commands, refer to Common uses.

Use pglogical to replicate from PostgreSQL to CockroachDB:

replicator pglogical

Use mylogical to replicate from MySQL to CockroachDB:

replicator mylogical

Use oraclelogminer to replicate from Oracle to CockroachDB:

replicator oraclelogminer

Use start to replicate from CockroachDB to PostgreSQL, MySQL, or Oracle (failback):

replicator start

Source connection strings

--sourceConn specifies the connection string of the source database for forward replication.

PostgreSQL connection string:

--sourceConn 'postgresql://{username}:{password}@{host}:{port}/{database}'

MySQL connection string:

--sourceConn 'mysql://{username}:{password}@{protocol}({host}:{port})/{database}'

Oracle connection string:

--sourceConn 'oracle://{username}:{password}@{host}:{port}/{service_name}'

For Oracle Multitenant databases, --sourcePDBConn specifies the pluggable database (PDB) connection. --sourceConn specifies the container database (CDB):

--sourceConn 'oracle://{username}:{password}@{host}:{port}/{cdb_service_name}'
--sourcePDBConn 'oracle://{username}:{password}@{host}:{port}/{pdb_service_name}'

For failback, --stagingConn specifies the CockroachDB connection string:

--stagingConn 'postgresql://{username}:{password}@{host}:{port}/{database}'

Target connection strings

--targetConn specifies the connection string of the target CockroachDB database for forward replication:

--targetConn 'postgresql://{username}:{password}@{host}:{port}/{database}'

Replication checkpoints

MOLT Replicator requires a checkpoint value to start replication from the correct position in the source database's transaction log.

For PostgreSQL, use --slotName to specify the replication slot created during the data load. The slot automatically tracks the LSN (Log Sequence Number):

--slotName molt_slot

For MySQL, set --defaultGTIDSet to the cdc_cursor value from the MOLT Fetch output:

--defaultGTIDSet '4c658ae6-e8ad-11ef-8449-0242ac140006:1-29'

For Oracle, set --scn and --backfillFromSCN to the cdc_cursor values from the MOLT Fetch output:

--scn 26685786
--backfillFromSCN 26685444

Staging database

The staging database stores replication metadata, checkpoints, and buffered mutations. Specify the staging database with --stagingSchema in fully-qualified database.schema format and create it automatically with --stagingCreateSchema:

--stagingSchema defaultdb._replicator
--stagingCreateSchema

The staging database is used to:

• Store checkpoints that enable resuming from the correct point after interruptions.
• Buffer mutations before applying them to the target in transaction order.
• Maintain consistency for time-ordered transactional batches while respecting table dependencies.
• Provide restart capabilities after failures.

Consistency modes

Consistency modes control how MOLT Replicator balances throughput and transactional guarantees.

Failback mode (CockroachDB source)

When using the start command to replicate from CockroachDB to another database, you can configure one of the following consistency modes:

1. Consistent (default): Preserves per-row order and source transaction atomicity. Mutations are buffered in memory and are flushed out to the target database when --flushSize is reached or once --flushPeriod has passed since the previous flush. Concurrent transactions are controlled by --parallelism.

2. BestEffort: Relaxes atomicity across tables that do not have foreign key constraints between them (maintains coherence within FK-connected groups). Mutations are buffered in memory and are flushed out to the target database when --flushSize is reached or once --flushPeriod has passed since the previous flush. Enable with --bestEffortOnly or allow auto-entry via --bestEffortWindow set to a positive duration (such as 1s).

   Note:

   For independent tables (with no foreign key constraints), BestEffort mode applies changes immediately as they arrive, without waiting for the resolved timestamp. This provides higher throughput for tables that have no relationships with other tables.

3. Immediate: Applies updates as they arrive to Replicator with no buffering or waiting for resolved timestamps. Enable with --immediate. Provides highest throughput but requires no foreign keys on the target schema.

Forward replication (PostgreSQL, MySQL, Oracle sources)

When using pglogical, mylogical, or oraclelogminer commands to replicate from PostgreSQL, MySQL, or Oracle sources to CockroachDB, Replicator always preserves per-row order and source transaction atomicity for any changes. This behavior cannot be configured. The consistency mode flags (--immediate, --bestEffortOnly, --bestEffortWindow) do not apply to these commands and will have no effect if specified.

Userscripts

MOLT Replicator can apply userscripts, specified with the --userscript flag, to customize how data is processed and transformed as it moves through the live replication pipeline. Userscripts are customized TypeScript files that apply transformation logic to rows of data on a per-schema and per-table basis.

Userscripts are intended to address unique business or data transformation needs. They perform operations that cannot be handled by the source change data capture (CDC) stream, such as filtering out specific tables, rows, or columns; routing data from a single source table to multiple target tables; transforming column values or adding computed columns; and implementing custom error handling. These tranformations occur in-flight, between the source and target databases.

To have MOLT Replicator apply a userscript, include the --userscript flag with any Replicator command. The flag accepts a path to a TypeScript filename.

--userscript 'path/to/script.ts'

For more information, read the userscript documentation. Learn how to use the userscript API and refer to the userscript cookbook examples.

Monitoring

Metrics

MOLT Replicator metrics are not enabled by default. Enable Replicator metrics by specifying the --metricsAddr flag with a port (or host:port) when you start Replicator. This exposes Replicator metrics at http://{host}:{port}/_/varz. For example, the following flag exposes metrics on port 30005:

--metricsAddr :30005

Metrics can additionally be written to snapshot files at repeated intervals. Metrics snapshotting is disabled by default. If metrics have been enabled, metrics snapshotting can also be enabled with the --metricsSnapshotPeriod flag. For example, the following flag enables metrics snapshotting every 15 seconds:

--metricsSnapshotPeriod 15s

Metrics snapshots enable access to metrics when the Prometheus server is unavailable, and they can be sent to CockroachDB support to help quickly resolve an issue.

For guidelines on using and interpreting replication metrics, refer to Replicator Metrics.

Logging

By default, MOLT Replicator writes two streams of logs: operational logs to stdout (including warning, info, trace, and some errors) and final errors to stderr.

Redirect both streams to ensure all logs are captured for troubleshooting:

# Merge both streams to console
./replicator ... 2>&1

# Redirect both streams to a file
./replicator ... > output.log 2>&1

# Merge streams to console while saving to file
./replicator > >(tee replicator.log) 2>&1

# Use logDestination flag to write all logs to a file
./replicator --logDestination replicator.log ...

Enable debug logging with -v. For more granularity and system insights, enable trace logging with -vv. Pay close attention to warning- and error-level logs, as these indicate when Replicator is misbehaving.

Docker usage

Local connection strings

When testing locally, specify the host as follows:

• For macOS, use host.docker.internal. For example:

  --sourceConn 'postgres://postgres:postgres@host.docker.internal:5432/migration_db?sslmode=disable'
  --targetConn "postgres://root@host.docker.internal:26257/defaultdb?sslmode=disable"
  

• For Linux and Windows, use 172.17.0.1. For example:

  --sourceConn 'postgres://postgres:postgres@172.17.0.1:5432/migration_db?sslmode=disable'
  --targetConn "postgres://root@172.17.0.1:26257/defaultdb?sslmode=disable"
  

Common uses

Forward replication (after initial load)

In a migration that utilizes continuous replication, run the replicator command after using MOLT Fetch to perform the initial data load. Run the replicator command with the required flags, as shown below:

replicator pglogical

replicator mylogical

replicator oraclelogminer

Specify the source and target database connections. For connection string formats, refer to Source connection strings and Target connection strings:

--sourceConn $SOURCE
--targetConn $TARGET

--sourcePDBConn $SOURCE_PDB

--sourceSchema MIGRATION_USER

Specify the target schema on CockroachDB with --targetSchema in fully-qualified database.schema format:

--targetSchema defaultdb.migration_schema

To replicate from the correct position, specify the appropriate checkpoint value.

--slotName molt_slot

--defaultGTIDSet '4c658ae6-e8ad-11ef-8449-0242ac140006:1-29'

--scn 26685786
--backfillFromSCN 26685444

Use --stagingSchema to specify the staging database in fully-qualified database.schema format. Use --stagingCreateSchema to create it automatically on first run:

--stagingSchema defaultdb._replicator
--stagingCreateSchema

At minimum, the replicator command should include the following flags:

replicator pglogical \
--sourceConn $SOURCE \
--targetConn $TARGET \
--targetSchema defaultdb.migration_schema \
--slotName molt_slot \
--stagingSchema defaultdb._replicator \
--stagingCreateSchema

replicator mylogical \
--sourceConn $SOURCE \
--targetConn $TARGET \
--targetSchema defaultdb.public \
--defaultGTIDSet '4c658ae6-e8ad-11ef-8449-0242ac140006:1-29' \
--stagingSchema defaultdb._replicator \
--stagingCreateSchema

replicator oraclelogminer \
--sourceConn $SOURCE \
--sourcePDBConn $SOURCE_PDB \
--targetConn $TARGET \
--sourceSchema MIGRATION_USER \
--targetSchema defaultdb.migration_schema \
--scn 26685786 \
--backfillFromSCN 26685444 \
--stagingSchema defaultdb._replicator \
--stagingCreateSchema

For detailed walkthroughs of migrations that use replicator in this way, refer to these common migration approaches:

• Delta Migration
• Phased Delta Migration with Failback Replication

Failback replication

A migration that utilizes failback replication replicates data from the CockroachDB cluster back to the source database. In this case, MOLT Replicator acts as an HTTPS webhook sink for a CockroachDB changefeed.

Use the start command for failback:

replicator start

Specify the target database connection (the database you originally migrated from) with --targetConn. For connection string formats, refer to Target connection strings:

--targetConn $TARGET

Specify the CockroachDB connection string with --stagingConn. For details, refer to Connect using a URL.

--stagingConn $STAGING

Specify the staging database name with --stagingSchema in fully-qualified database.schema format. This should be the same staging database created during Forward replication with initial load:

--stagingSchema defaultdb._replicator

Specify a webhook endpoint address for the changefeed to send changes to with --bindAddr. For example:

--bindAddr :30004

Specify TLS certificate and private key file paths for secure webhook connections with --tlsCertificate and --tlsPrivateKey:

--tlsCertificate ./certs/server.crt
--tlsPrivateKey ./certs/server.key

At minimum, the replicator command should include the following flags:

replicator start \
--targetConn $TARGET \
--stagingConn $STAGING \
--stagingSchema defaultdb._replicator \
--bindAddr :30004 \
--tlsCertificate ./certs/server.crt \
--tlsPrivateKey ./certs/server.key

Resume after an interruption

Whether you're using Replicator to perform forward replication or failback replication, an unexpected issue may cause replication to stop.

replicator pglogical \
--sourceConn $SOURCE \
--targetConn $TARGET \
--targetSchema defaultdb.migration_schema \
--slotName molt_slot \
--stagingSchema defaultdb._replicator \
--metricsAddr :30005 \
-v

replicator mylogical \
--sourceConn $SOURCE \
--targetConn $TARGET \
--targetSchema defaultdb.public \
--stagingSchema defaultdb._replicator \
--metricsAddr :30005 \
--userscript table_filter.ts \
-v

replicator oraclelogminer \
--sourceConn $SOURCE \
--sourcePDBConn $SOURCE_PDB \
--sourceSchema MIGRATION_USER \
--targetSchema defaultdb.migration_schema \
--targetConn $TARGET \
--stagingSchema defaultdb._replicator \
--metricsAddr :30005 \
--userscript table_filter.ts \
-v

Replication resumes from the last checkpoint without performing a fresh load. Monitor the metrics endpoint at http://localhost:30005/_/varz to track replication progress.

Known limitations

• Replication modes require connection to the primary instance (PostgreSQL primary, MySQL primary/master, or Oracle primary). MOLT cannot obtain replication checkpoints or transaction metadata from replicas.
• Running DDL on the source or target while replication is in progress can cause replication failures.
• TRUNCATE operations on the source are not captured. Only INSERT, UPDATE, UPSERT, and DELETE events are replicated.
• Changes to virtual columns are not replicated automatically. To migrate these columns, you must define them explicitly with transformation rules.

The following limitation is specific to MySQL sources:

• MySQL replication is supported only with GTID-based configurations. Binlog-based features that do not use GTID are not supported.

The following limitations are specific to Oracle sources:

• Replication will not work for tables or column names exceeding 30 characters. This is a limitation of Oracle LogMiner.
• The following data types are not supported for replication:
  • User-defined types (UDTs)
  • Nested tables
  • VARRAY
  • LONGBLOB/CLOB columns (over 4000 characters)
• If your Oracle workload executes UPDATE statements that modify only LOB columns, these UPDATE statements are not supported by Oracle LogMiner and will not be replicated.
• If you are using Oracle 11 and execute UPDATE statements on XMLTYPE or LOB columns, those changes are not supported by Oracle LogMiner and will be excluded from ongoing replication.
• If you are migrating LOB columns from Oracle 12c, use AWS DMS Binary Reader instead of LogMiner. Oracle LogMiner does not support LOB replication in 12c.

See also

• MOLT Replicator Installation
• MOLT Replicator Flags
• MOLT Replicator Best Practices
• MOLT Replicator Troubleshooting
• Migration Overview
• Migration Strategy
• MOLT Fetch