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). For details, refer to How it works.

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 with 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 to source database.

Prerequisites

Supported databases

MOLT Replicator supports the following source and target databases:

• PostgreSQL 11-16
• MySQL 5.7, 8.0 and later
• 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:

Installation

To install MOLT, download the binary that matches your architecture and source database:

The following binaries are included:

• molt
• replicator

To display the current version of each binary, run molt --version and replicator --version.

For previous binaries, refer to the MOLT version manifest. For release details, refer to the MOLT changelog.

Docker images

MOLT Replicator

Docker images for MOLT Replicator are also available as a standalone binary:

docker pull cockroachdb/replicator

To pull a specific version (for example, v1.1.1):

docker pull cockroachdb/replicator:v1.1.1

Docker usage

Performance

MOLT Fetch, Verify, and Replicator are likely to run more slowly in a Docker container than on a local machine. To improve performance, increase the memory or compute resources, or both, on your Docker container.

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/molt?sslmode=disable'
  --targetConn "postgres://root@host.docker.internal:26257/molt?sslmode=disable"
  

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

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

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 HTTP 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.

Consistency modes

MOLT Replicator supports three consistency modes for balancing throughput and transactional guarantees:

1. Consistent (failback mode only, default for CockroachDB sources): Preserves per-row order and source transaction atomicity. Concurrent transactions are controlled by --parallelism.

2. BestEffort (failback mode only): Relaxes atomicity across tables that do not have foreign key constraints between them (maintains coherence within FK-connected groups). 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 (default for PostgreSQL, MySQL, and Oracle sources): Applies updates as they arrive to Replicator with no buffering or waiting for resolved timestamps. For CockroachDB sources, provides highest throughput but requires no foreign keys on the target schema.

Commands

MOLT Replicator provides the following commands:

For command-specific flags and examples, refer to Usage and Common workflows.

Flags

Global flags

pglogical replication flags

The following flags are used when replicating from a PostgreSQL source database.

mylogical replication flags

The following flags are used when replicating from a MySQL source database.

oraclelogminer replication flags

The following flags are used when replicating from an Oracle source database.

start failback flags

The following flags are used for failback from CockroachDB.

make-jwt flags

The following flags are used with the make-jwt command to generate JWT tokens for changefeed authentication.

Usage

Replicator commands

MOLT Replicator provides four commands for different replication scenarios. For detailed workflows, refer to Common workflows.

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, use --defaultGTIDSet with the GTID set from the MOLT Fetch output:

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

For Oracle, use --scn and --backfillFromSCN with the SCN 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 and create it automatically with --stagingCreateSchema:

--stagingSchema _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.

Security

Cockroach Labs strongly recommends the following:

Connection security and credentials

• To keep your database credentials out of shell history and logs, follow these best practices when specifying your source and target connection strings:

  • Avoid plaintext connection strings.

  • Provide your connection strings as environment variables. For example:

    export SOURCE="postgres://migration_user:a%2452%26@localhost:5432/molt?sslmode=verify-full"
    export TARGET="postgres://root@localhost:26257/molt?sslmode=verify-full"
    

    Afterward, reference the environment variables in MOLT commands:

    --sourceConn $SOURCE
    --targetConn $TARGET
    

  • If possible, use an external secrets manager to load the environment variables from stored secrets.

• Use TLS-enabled connection strings to encrypt data in transit from MOLT to the database. When using TLS certificates, ensure certificate files are accessible to the MOLT binary on the same machine.

  For example, a PostgreSQL connection string with TLS certificates:

  postgresql://migration_user@db.example.com:5432/appdb?sslmode=verify-full&sslrootcert=/etc/molt/certs/ca.pem&sslcert=/etc/molt/certs/client.crt&sslkey=/etc/molt/certs/client.key
  

• URL-encode connection strings for the source database and CockroachDB so special characters in passwords are handled correctly.

  • Given a password a$52&, pass it to the molt escape-password command with single quotes:

    molt escape-password --password 'a$52&'
    

    Use the encoded password in your connection string. For example:

    postgres://migration_user:a%2452%26@localhost:5432/replicationload
    

• Remove sslmode=disable from production connection strings.

CockroachDB changefeed security

For failback scenarios, secure the connection from CockroachDB to MOLT Replicator using TLS certificates. Generate TLS certificates using self-signed certificates, certificate authorities like Let's Encrypt, or your organization's certificate management system.

TLS from CockroachDB to Replicator

Configure MOLT Replicator with server certificates using the --tlsCertificate and --tlsPrivateKey flags to specify the certificate and private key file paths. For example:

replicator start \
--tlsCertificate ./certs/server.crt \
--tlsPrivateKey ./certs/server.key \
...

These server certificates must correspond to the client certificates specified in the changefeed webhook URL to ensure proper TLS handshake.

Encode client certificates for changefeed webhook URLs:

• Webhook URLs: Use both URL encoding and base64 encoding: base64 -i ./client.crt | jq -R -r '@uri'
• Non-webhook contexts: Use base64 encoding only: base64 -w 0 ca.cert

JWT authentication

You can use JSON Web Tokens (JWT) to authorize incoming changefeed connections and restrict writes to a subset of SQL databases or user-defined schemas in the target cluster.

Replicator supports JWT claims that allow writes to specific databases, schemas, or all of them. JWT tokens must be signed using RSA or EC keys. HMAC and None signatures are automatically rejected.

To configure JWT authentication:

1. Add PEM-formatted public signing keys to the _replicator.jwt_public_keys table in the staging database.

2. To revoke a specific token, add its jti value to the _replicator.jwt_revoked_ids table in the staging database.

The Replicator process re-reads these tables every minute to pick up changes.

To pass the JWT token from the changefeed to the Replicator webhook sink, use the webhook_auth_header option:

CREATE CHANGEFEED ... WITH webhook_auth_header='Bearer <encoded_token>';

Token quickstart

The following example uses OpenSSL to generate keys, but any PEM-encoded RSA or EC keys will work.

# Generate an EC private key using OpenSSL.
openssl ecparam -out ec.key -genkey -name prime256v1

# Write the public key components to a separate file.
openssl ec -in ec.key -pubout -out ec.pub

# Upload the public key for all instances of Replicator to find it.
cockroach sql -e "INSERT INTO _replicator.jwt_public_keys (public_key) VALUES ('$(cat ec.pub)')"

# Reload configuration, or wait one minute.
killall -HUP replicator

# Generate a token which can write to the ycsb.public schema.
# The key can be decoded using the debugger at https://jwt.io.
# Add the contents of out.jwt to the CREATE CHANGEFEED command:
# WITH webhook_auth_header='Bearer {out.jwt}'
replicator make-jwt -k ec.key -a ycsb.public -o out.jwt

External JWT providers

The make-jwt command also supports a --claim flag, which prints a JWT claim that can be signed by your existing JWT provider. The PEM-formatted public key or keys for that provider must be inserted into the _replicator.jwt_public_keys table. The iss (issuers) and jti (token id) fields will likely be specific to your auth provider, but the custom claim must be retained in its entirety.

You can repeat the -a flag to create a claim for multiple schemas:

replicator make-jwt -a 'database.schema' --claim

{
  "iss": "replicator",
  "jti": "d5ffa211-8d54-424b-819a-bc19af9202a5",
  "https://github.com/cockroachdb/replicator": {
    "schemas": [
      [
        "database",
        "schema"
      ]
    ]
  }
}

Production considerations

• Avoid --disableAuthentication and --tlsSelfSigned flags in production environments. These flags should only be used for testing or development purposes.

Supply chain security

Use the version command to verify the integrity of your MOLT Replicator build and identify potential upstream vulnerabilities.

replicator version

The output includes:

• Module name
• go.mod checksum
• Version

Use this information to determine if your build may be subject to vulnerabilities from upstream packages. Cockroach Labs uses Dependabot to automatically upgrade Go modules, and the team regularly merges Dependabot updates to address security issues.

Common workflows

Forward replication with initial load

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_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. Use --stagingCreateSchema to create it automatically on first run:

--stagingSchema _replicator
--stagingCreateSchema

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

replicator pglogical \
--sourceConn $SOURCE \
--targetConn $TARGET \
--slotName molt_slot \
--stagingSchema _replicator \
--stagingCreateSchema

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

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

Resume after interruption

When resuming replication after an interruption, MOLT Replicator automatically uses the stored checkpoint to resume from the correct position.

Rerun the same replicator command used during forward replication, specifying the same --stagingSchema value as before. Omit --stagingCreateSchema and any checkpoint flags. For example:

replicator pglogical \
--sourceConn $SOURCE \
--targetConn $TARGET \
--slotName molt_slot \
--stagingSchema _replicator

replicator mylogical \
--sourceConn $SOURCE \
--targetConn $TARGET \
--stagingSchema _replicator

replicator oraclelogminer \
--sourceConn $SOURCE \
--sourcePDBConn $SOURCE_PDB \
--sourceSchema migration_schema \
--targetConn $TARGET \
--stagingSchema _replicator

Failback to source database

When replicating from CockroachDB back to the source database, MOLT Replicator acts as a 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). For connection string formats, refer to Target connection strings:

--targetConn $TARGET

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

--stagingConn $STAGING

Specify the staging database name. This should be the same staging database created during Forward replication with initial load:

--stagingSchema _replicator

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

--bindAddr :30004

Specify TLS certificate and private key file paths for secure webhook connections:

--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 _replicator \
--bindAddr :30004 \
--tlsCertificate ./certs/server.crt \
--tlsPrivateKey ./certs/server.key

For detailed steps, refer to Migration failback.

Monitoring

Metrics

MOLT Replicator can export Prometheus metrics by setting the --metricsAddr flag to a port (for example, --metricsAddr :30005). Metrics are not enabled by default. When enabled, metrics are available at the path /_/varz. For example: http://localhost:30005/_/varz.

For a list of recommended metrics to monitor during replication, refer to:

• Forward replication metrics (PostgreSQL, MySQL, and Oracle sources)
• Failback replication metrics (CockroachDB source)

You can use the Replicator Grafana dashboard to visualize the metrics. For Oracle-specific metrics, import the Oracle Grafana dashboard.

To check MOLT Replicator health when metrics are enabled, run curl http://localhost:30005/_/healthz (replacing the port with your --metricsAddr value). This returns a status code of 200 if Replicator is running.

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.

Best practices

Test and validate

To verify that your connections and configuration work properly, run MOLT Replicator in a staging environment before replicating any data in production. Use a test or development environment that closely resembles production.

Optimize performance

Configure the following replicator flags to optimize replication throughput and resource usage. Test different combinations in a pre-production environment to find the optimal balance of stability and performance for your workload.

Troubleshooting

Forward replication issues

Performance troubleshooting

If MOLT Replicator appears hung or performs poorly:

1. Enable trace logging with -vv to get more visibility into the replicator's state and behavior.

2. If MOLT Replicator is in an unknown, hung, or erroneous state, collect performance profiles to include with support tickets. Replace {host} and {metrics-port} with your Replicator host and the port specified by --metricsAddr:

   curl '{host}:{metrics-port}/debug/pprof/trace?seconds=15' > trace.out
   curl '{host}:{metrics-port}/debug/pprof/profile?seconds=15' > profile.out
   curl '{host}:{metrics-port}/debug/pprof/goroutine?seconds=15' > gr.out
   curl '{host}:{metrics-port}/debug/pprof/heap?seconds=15' > heap.out
   

3. Monitor lag metrics and adjust performance parameters as needed.

SHOW wal_level;

ALTER SYSTEM SET wal_level = 'logical';

ERROR: replication slot "molt_slot" already exists

SELECT pg_drop_replication_slot('molt_slot');

run CREATE PUBLICATION molt_fetch FOR ALL TABLES;

CREATE PUBLICATION molt_publication FOR ALL TABLES;
SELECT pg_create_logical_replication_slot('molt_slot', 'pgoutput');

could not connect to source database: failed to connect to `user=migration_user database=source_database`

run SELECT pg_create_logical_replication_slot('molt_slot', 'pgoutput'); in source database

SELECT pg_create_logical_replication_slot('molt_slot', 'pgoutput');

-- Check if GTID is in executed set
SELECT GTID_SUBSET('your-gtid-set', @@GLOBAL.gtid_executed) AS in_executed;

-- Check if GTID is purged
SELECT GTID_SUBSET('your-gtid-set', @@GLOBAL.gtid_purged) AS in_purged;

Schema drift errors

Indicates source and target schemas are mismatched:

WARNING: schema drift detected in "database"."table" at payload object offset 0: unexpected columns: column_name

Resolution: Align schemas or use userscripts to transform data.

Apply flow failures

Apply flow failures occur when the target database encounters error conditions such as unique constraint violations, target database being unavailable, or incorrect data (missing or extraneous columns) during apply operations:

WARNING: warning during tryCommit: ERROR: duplicate key value violates unique constraint
ERROR: maximum number of retries (10) exceeded

Resolution: Check target database constraints and connection stability. MOLT Replicator will log warnings for each retry attempt. If you see warnings but no final error, the apply succeeded after retrying. If all retry attempts are exhausted, Replicator will surface a final error and restart the apply loop to continue processing.

Failback issues

If the changefeed shows connection errors in SHOW CHANGEFEED JOB:

Connection refused

transient error: Post "https://replicator-host:30004/molt/public": dial tcp [::1]:30004: connect: connection refused

This indicates that Replicator is down, the webhook URL is incorrect, or the port is misconfigured.

Resolution: Verify that MOLT Replicator is running on the port specified in the changefeed INTO configuration. Confirm the host and port are correct.

Incorrect schema path errors

This error occurs when the CockroachDB changefeed webhook URL path does not match the target database schema naming convention:

transient error: 400 Bad Request: unknown schema:

The webhook URL path is specified in the INTO clause when you create the changefeed. For example: webhook-https://replicator-host:30004/database/schema.

Resolution: Verify the webhook path format matches your target database type:

• PostgreSQL or CockroachDB targets: Use /database/schema format. For example, webhook-https://replicator-host:30004/migration_schema/public.
• MySQL targets: Use /database format (schema is implicit). For example, webhook-https://replicator-host:30004/migration_schema.
• Oracle targets: Use /DATABASE format in uppercase. For example, webhook-https://replicator-host:30004/MIGRATION_SCHEMA.

For details on configuring the webhook sink URI, refer to Webhook sink.

GC threshold error

batch timestamp * must be after replica GC threshold

This indicates starting from an invalid cursor that has been garbage collected.

Resolution: Double-check the cursor to ensure it represents a valid range that has not been garbage collected, or extend the GC TTL on the source CockroachDB cluster:

ALTER DATABASE defaultdb CONFIGURE ZONE USING gc.ttlseconds = {gc_ttl_in_seconds};

Duplicated data re-application

This occurs when resuming a changefeed from a cursor causes excessive data duplication.

Resolution: Clear the staging database to prevent duplication. This deletes all checkpoints and buffered data, so use with caution:

DROP DATABASE _replicator;

For more targeted cleanup, delete mutations from specific staging tables:

DELETE FROM _replicator.employees WHERE true;

Examples

For detailed examples of using MOLT Replicator usage, refer to the migration workflow tutorials:

• Load and Replicate: Load data with MOLT Fetch and set up ongoing replication with MOLT Replicator.
• Resume Replication: Resume replication after an interruption.
• Migration failback: Replicate changes from CockroachDB back to the initial source database.

See also

• Migration Overview
• Migration Strategy
• MOLT Fetch