Load and Replicate

On this page

Perform an initial bulk load of the source data using MOLT Fetch, then use MOLT Replicator to replicate ongoing changes to the target.

Before you begin

• Create a CockroachDB Cloud or self-hosted cluster.
• Install the MOLT (Migrate Off Legacy Technology) tools.
• Review the Fetch and Replicator best practices.
• Review Migration Strategy.

Limitations

Fetch limitations

• Only tables with primary key types of INT, FLOAT, or UUID can be sharded with --export-concurrency.

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

Prepare the source database

Create migration user on source database

Create a dedicated migration user (for example, MIGRATION_USER) on the source database. This user is responsible for reading data from source tables during the migration. You will pass this username in the source connection string.

CREATE USER migration_user WITH PASSWORD 'password';

GRANT CONNECT ON DATABASE source_database TO migration_user;
GRANT USAGE ON SCHEMA migration_schema TO migration_user;
GRANT SELECT ON ALL TABLES IN SCHEMA migration_schema TO migration_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA migration_schema GRANT SELECT ON TABLES TO migration_user;

ALTER USER migration_user WITH SUPERUSER;

ALTER USER migration_user WITH LOGIN REPLICATION;
GRANT CREATE ON DATABASE source_database TO migration_user;
ALTER TABLE migration_schema.table_name OWNER TO migration_user;

CREATE USER 'migration_user'@'%' IDENTIFIED BY 'password';

GRANT SELECT ON source_database.* TO 'migration_user'@'%';
FLUSH PRIVILEGES;

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'migration_user'@'%';
FLUSH PRIVILEGES;

CREATE USER MIGRATION_USER IDENTIFIED BY 'password';

-- Basic access
GRANT CONNECT TO C##MIGRATION_USER;
GRANT CREATE SESSION TO C##MIGRATION_USER;

-- General metadata access
GRANT EXECUTE_CATALOG_ROLE TO C##MIGRATION_USER;
GRANT SELECT_CATALOG_ROLE TO C##MIGRATION_USER;

-- Access to necessary V$ views
GRANT SELECT ON V_$LOG TO C##MIGRATION_USER;
GRANT SELECT ON V_$LOGFILE TO C##MIGRATION_USER;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO C##MIGRATION_USER;
GRANT SELECT ON V_$ARCHIVED_LOG TO C##MIGRATION_USER;
GRANT SELECT ON V_$DATABASE TO C##MIGRATION_USER;
GRANT SELECT ON V_$LOG_HISTORY TO C##MIGRATION_USER;

-- Direct grants to specific DBA views
GRANT SELECT ON ALL_USERS TO C##MIGRATION_USER;
GRANT SELECT ON DBA_USERS TO C##MIGRATION_USER;
GRANT SELECT ON DBA_OBJECTS TO C##MIGRATION_USER;
GRANT SELECT ON DBA_SYNONYMS TO C##MIGRATION_USER;
GRANT SELECT ON DBA_TABLES TO C##MIGRATION_USER;

-- Allow C##MIGRATION_USER to connect to the PDB and see active transaction metadata
GRANT CONNECT TO C##MIGRATION_USER;
GRANT CREATE SESSION TO C##MIGRATION_USER;

-- General metadata access
GRANT SELECT_CATALOG_ROLE TO C##MIGRATION_USER;

-- Access to necessary V$ views
GRANT SELECT ON V_$SESSION TO C##MIGRATION_USER;
GRANT SELECT ON V_$TRANSACTION TO C##MIGRATION_USER;

-- Grant these two for every table to migrate in the migration_schema
GRANT SELECT, FLASHBACK ON migration_schema.tbl TO C##MIGRATION_USER;

-- Basic access
GRANT CONNECT TO MIGRATION_USER;
GRANT CREATE SESSION TO MIGRATION_USER;

-- General metadata access
GRANT SELECT_CATALOG_ROLE TO MIGRATION_USER;
GRANT EXECUTE_CATALOG_ROLE TO MIGRATION_USER;

-- Access to necessary V$ views
GRANT SELECT ON V_$DATABASE TO MIGRATION_USER;
GRANT SELECT ON V_$SESSION TO MIGRATION_USER;
GRANT SELECT ON V_$TRANSACTION TO MIGRATION_USER;

-- Direct grants to specific DBA views
GRANT SELECT ON ALL_USERS TO MIGRATION_USER;
GRANT SELECT ON DBA_USERS TO MIGRATION_USER;
GRANT SELECT ON DBA_OBJECTS TO MIGRATION_USER;
GRANT SELECT ON DBA_SYNONYMS TO MIGRATION_USER;
GRANT SELECT ON DBA_TABLES TO MIGRATION_USER;

-- Grant these two for every table to migrate in the migration_schema
GRANT SELECT, FLASHBACK ON migration_schema.tbl TO MIGRATION_USER;

Configure source database for replication

ALTER SYSTEM SET wal_level = 'logical';

-- Check current log mode
SELECT log_mode FROM v$database;

-- Enable ARCHIVELOG (requires database restart)
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

-- Verify ARCHIVELOG is enabled
SELECT log_mode FROM v$database; -- Expected: ARCHIVELOG

-- Enable minimal supplemental logging for primary keys
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

-- Verify supplemental logging status
SELECT supplemental_log_data_min, supplemental_log_data_pk FROM v$database;
-- Expected:
--   SUPPLEMENTAL_LOG_DATA_MIN: IMPLICIT (or YES)
--   SUPPLEMENTAL_LOG_DATA_PK: YES

ALTER DATABASE FORCE LOGGING;

-- Verify FORCE LOGGING is enabled
SELECT force_logging FROM v$database; -- Expected: YES

CREATE TABLE migration_schema."_replicator_sentinel" (
  keycol NUMBER PRIMARY KEY,
  lastSCN NUMBER
);

GRANT SELECT, INSERT, UPDATE ON migration_schema."_replicator_sentinel" TO C##MIGRATION_USER;

-- Access to necessary V$ views
GRANT SELECT ON V_$LOG TO C##MIGRATION_USER;
GRANT SELECT ON V_$LOGFILE TO C##MIGRATION_USER;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO C##MIGRATION_USER;
GRANT SELECT ON V_$ARCHIVED_LOG TO C##MIGRATION_USER;
GRANT SELECT ON V_$LOG_HISTORY TO C##MIGRATION_USER;

-- SYS-prefixed views (for full dictionary access)
GRANT SELECT ON SYS.V_$LOGMNR_DICTIONARY TO C##MIGRATION_USER;
GRANT SELECT ON SYS.V_$LOGMNR_LOGS TO C##MIGRATION_USER;
GRANT SELECT ON SYS.V_$LOGMNR_PARAMETERS TO C##MIGRATION_USER;
GRANT SELECT ON SYS.V_$LOGMNR_SESSION TO C##MIGRATION_USER;

-- Access to LogMiner views and controls
GRANT LOGMINING TO C##MIGRATION_USER;
GRANT EXECUTE ON DBMS_LOGMNR TO C##MIGRATION_USER;

SELECT
    l.GROUP#,
    lf.MEMBER,
    l.FIRST_CHANGE# AS START_SCN,
    l.NEXT_CHANGE# AS END_SCN
FROM
    V$LOG l
JOIN
    V$LOGFILE lf
ON
    l.GROUP# = lf.GROUP#;

   GROUP# MEMBER                                       START_SCN                END_SCN
_________ _________________________________________ ____________ ______________________
        3 /opt/oracle/oradata/ORCLCDB/redo03.log         1232896    9295429630892703743
        2 /opt/oracle/oradata/ORCLCDB/redo02.log         1155042                1232896
        1 /opt/oracle/oradata/ORCLCDB/redo01.log         1141934                1155042

3 rows selected.

SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
2358840

1 row selected.

EXEC DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/opt/oracle/oradata/ORCLCDB/redo01.log', OPTIONS => DBMS_LOGMNR.NEW);
EXEC DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/opt/oracle/oradata/ORCLCDB/redo02.log', OPTIONS => DBMS_LOGMNR.ADDFILE);
EXEC DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/opt/oracle/oradata/ORCLCDB/redo03.log', OPTIONS => DBMS_LOGMNR.ADDFILE);

EXEC DBMS_LOGMNR.START_LOGMNR(
  STARTSCN => 2358840,
  OPTIONS  => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG
);

PL/SQL procedure successfully completed.

Prepare the target database

Create the target schema

Convert the source schema into a CockroachDB-compatible schema. CockroachDB supports the PostgreSQL wire protocol and is largely compatible with PostgreSQL syntax.

• The source and target schemas must match. Review Type mapping to understand which source types can be mapped to CockroachDB types.

  For example, a source table defined as CREATE TABLE migration_schema.tbl (pk INT PRIMARY KEY); must have a corresponding schema and table in CockroachDB:

  CREATE SCHEMA migration_schema;
  CREATE TABLE migration_schema.tbl (pk INT PRIMARY KEY);
  

  • MOLT Fetch can automatically create a matching CockroachDB schema using the drop-on-target-and-recreate option.
  • If you create the target schema manually, review how MOLT Fetch handles type mismatches. You can use the MOLT Schema Conversion Tool to create a matching schema.

  • By default, table and column names are case-insensitive in MOLT Fetch. If using the --case-sensitive flag, schema, table, and column names must match Oracle's default uppercase identifiers. Use quoted names on the target to preserve case. For example, the following CockroachDB SQL statement will error:

    CREATE TABLE co.stores (... store_id ...);
    

    It should be written as:

    CREATE TABLE "CO"."STORES" (... "STORE_ID" ...);
    

    When using --case-sensitive, quote all identifiers and match the case exactly (for example, use "CO"."STORES" and "STORE_ID").

• Every table must have an explicit primary key. For more information, refer to Primary key best practices.

  Warning:

  Avoid using sequential keys. To learn more about the performance issues that can result from their use, refer to the guidance on indexing with sequential keys. If a sequential key is necessary in your CockroachDB table, you must create it manually, after using MOLT Fetch to load and replicate the data.

• Review Transformations to understand how computed columns and partitioned tables can be mapped to the target, and how target tables can be renamed.

• By default on CockroachDB, INT is an alias for INT8, which creates 64-bit signed integers. PostgreSQL and MySQL default to 32-bit integers. Depending on your source database or application requirements, you may need to change the integer size to 4. For more information, refer to Considerations for 64-bit signed integers.

Schema Conversion Tool

The MOLT Schema Conversion Tool (SCT) automates target schema creation. It requires a free CockroachDB Cloud account.

1. Upload a source .sql file to convert the syntax and identify unimplemented features and syntax incompatibilities in the schema.

2. Import the converted schema to a CockroachDB cluster:

   • When migrating to CockroachDB Cloud, the Schema Conversion Tool automatically applies the converted schema to a new Cloud database.
   • When migrating to a self-hosted CockroachDB cluster, export a converted schema file and pipe the data definition language (DDL) directly into cockroach sql.

SELECT array_position(ARRAY[4,1,3,2],1);

  array_position
------------------
               2
(1 row)

SELECT * FROM table_a ORDER BY COALESCE(array_position(ARRAY[4,1,3,2],5),999);

Drop constraints and indexes

To optimize data load performance, drop all non-PRIMARY KEY constraints and indexes on the target CockroachDB database before migrating:

• FOREIGN KEY
• UNIQUE
• Secondary indexes
• CHECK
• DEFAULT
• NOT NULL (you do not need to drop this constraint when using drop-on-target-and-recreate for table handling)

You can recreate the constraints and indexes after loading the data.

Create the SQL user

Create a SQL user in the CockroachDB cluster that has the necessary privileges.

To create a user crdb_user in the default database (you will pass this username in the target connection string):

CREATE USER crdb_user WITH PASSWORD 'password';

Grant database-level privileges for schema creation within the target database:

GRANT ALL ON DATABASE defaultdb TO crdb_user;

Grant user privileges to create internal MOLT tables like _molt_fetch_exceptions in the public schema:

GRANT CREATE ON SCHEMA public TO crdb_user;

If you manually created the target schema (i.e., drop-on-target-and-recreate will not be used), grant the following privileges on the schema:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA migration_schema TO crdb_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA migration_schema
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO crdb_user;

Grant the same privileges for internal MOLT tables:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO crdb_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO crdb_user;

Depending on the MOLT Fetch data load mode you will use, grant the necessary privileges to run either IMPORT INTO or COPY FROM on the target tables:

IMPORT INTO privileges

Grant SELECT, INSERT, and DROP (required because the table is taken offline during the IMPORT INTO) privileges on all tables in the target schema:

GRANT SELECT, INSERT, DROP ON ALL TABLES IN SCHEMA migration_schema TO crdb_user;

If you plan to use cloud storage with implicit authentication for data load, grant the EXTERNALIOIMPLICITACCESS system-level privilege:

GRANT EXTERNALIOIMPLICITACCESS TO crdb_user;

COPY FROM privileges

Grant admin privileges to the user:

GRANT admin TO crdb_user;

Replication privileges

Grant permissions to create the staging schema for replication:

ALTER USER crdb_user CREATEDB;

Configure GC TTL

Before starting the initial data load, configure the garbage collection (GC) TTL on the source CockroachDB cluster to ensure that historical data remains available when replication begins. The GC TTL must be long enough to cover the full duration of the data load.

Increase the GC TTL before starting the data load. For example, to set the GC TTL to 24 hours:

ALTER DATABASE defaultdb CONFIGURE ZONE USING gc.ttlseconds = 86400;

Once replication has started successfully (which automatically protects its own data range), you can restore the GC TTL to its original value. For example, to restore to 5 minutes:

ALTER DATABASE defaultdb CONFIGURE ZONE USING gc.ttlseconds = 300;

For details, refer to Protect Changefeed Data from Garbage Collection.

Configure Fetch

When you run molt fetch, you can configure the following options for data load:

• Connection strings: Specify URL‑encoded source and target connections.
• Intermediate file storage: Export data to cloud storage or a local file server.
• Table handling mode: Determine how existing target tables are initialized before load.
• Schema and table filtering: Specify schema and table names to migrate.
• Data load mode: Choose between IMPORT INTO and COPY FROM.
• Fetch metrics: Configure metrics collection during initial data load.

Connection strings

Define the connection strings for the source and target databases, and keep them secure.

Source connection string

The --source flag specifies the connection string for the source database:

--source 'postgres://{username}:{password}@{host}:{port}/{database}?sslmode=verify-full'

--source 'postgres://migration_user:password@localhost:5432/molt?sslmode=verify-full'

--source 'mysql://{username}:{password}@{host}:{port}/{database}?sslmode=verify-full&sslcert={path_to_client_crt}&sslkey={path_to_client_key}&sslrootcert={path_to_ca_crt}'

--source 'mysql://migration_user:password@localhost/molt?sslcert=.%2fsource_certs%2fclient.root.crt&sslkey=.%2fsource_certs%2fclient.root.key&sslmode=verify-full&sslrootcert=.%2fsource_certs%2fca.crt'

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

--source 'oracle://{username}:{password}@{host}:{port}/{PDB_service_name}'
--source-cdb 'oracle://{username}:{password}@{host}:{port}/{CDB_service_name}'

--source 'oracle://C%23%23MIGRATION_USER:password@host:1521/ORCLPDB1'
--source-cdb 'oracle://C%23%23MIGRATION_USER:password@host:1521/ORCLCDB'

Target connection string

The --target flag specifies the connection string for the target CockroachDB database:

--target 'postgres://{username}:{password}@{host}:{port}/{database}?sslmode=verify-full'

For example:

--target 'postgres://crdb_user:password@localhost:26257/defaultdb?sslmode=verify-full'

For details, refer to Connect using a URL.

Secure connections

• 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:

    --source $SOURCE
    --target $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.

Intermediate file storage

MOLT Fetch can write intermediate files to either a cloud storage bucket or a local file server:

Cloud storage authentication

Ensure that access control is properly configured for Amazon S3, Google Cloud Storage, or Azure Blob Storage.

Table handling mode

MOLT Fetch can initialize target tables on the CockroachDB database in one of three modes using --table-handling:

• Use none when you need to retain existing data and schema.
• Use --table-handling truncate-if-exists to clear existing data while preserving schema definitions.
• Use --table-handling drop-on-target-and-recreate for initial imports or when source and target schemas differ, letting MOLT Fetch generate compatible tables automatically.

To guide schema creation with drop-on-target-and-recreate, you can explicitly map source types to CockroachDB types. Refer to Type mapping.

Schema and table filtering

MOLT Fetch can restrict which schemas (or users) and tables are migrated by using the following filter flags:

--schema-filter 'migration_schema'

import * as api from "replicator@v1";

// List the source tables (matching source names and casing) to include in replication
const allowedTables = ["EMPLOYEES", "PAYMENTS", "ORDERS"];

// Update this to your target CockroachDB database and schema name
api.configureSource("defaultdb.migration_schema", {
  dispatch: (doc: Document, meta: Document): Record<Table, Document[]> | null => {
    // Replicate only if the table matches one of the allowed tables
    if (allowedTables.includes(meta.table)) {
      let ret: Record<Table, Document[]> = {};
      ret[meta.table] = [doc];
      return ret;
    }
    // Ignore all other tables
    return null;
  },
  deletesTo: (doc: Document, meta: Document): Record<Table, Document[]> | null => {
    // Optionally filter deletes the same way
    if (allowedTables.includes(meta.table)) {
      let ret: Record<Table, Document[]> = {};
      ret[meta.table] = [doc];
      return ret;
    }
    return null;
  },
});

--userscript table_filter.ts

import * as api from "replicator@v1";

// List the source tables (matching source names and casing) to include in replication
const allowedTables = ["EMPLOYEES", "PAYMENTS", "ORDERS"];

// Update this to your target CockroachDB database and schema name
api.configureSource("defaultdb.migration_schema", {
  dispatch: (doc: Document, meta: Document): Record<Table, Document[]> | null => {
    // Replicate only if the table matches one of the allowed tables
    if (allowedTables.includes(meta.table)) {
      let ret: Record<Table, Document[]> = {};
      ret[meta.table] = [doc];
      return ret;
    }
    // Ignore all other tables
    return null;
  },
  deletesTo: (doc: Document, meta: Document): Record<Table, Document[]> | null => {
    // Optionally filter deletes the same way
    if (allowedTables.includes(meta.table)) {
      let ret: Record<Table, Document[]> = {};
      ret[meta.table] = [doc];
      return ret;
    }
    return null;
  },
});

--userscript table_filter.ts

Data load mode

MOLT Fetch can use either IMPORT INTO or COPY FROM to load data into CockroachDB:

• Use IMPORT INTO (the default mode) for large datasets, wide rows, or partitioned tables.
• Use --use-copy when tables must remain online during data load.
• Use --direct-copy only when you cannot move data to a public cloud, or want to perform local testing without intermediate storage. In this case, no intermediate file storage is used.

Fetch metrics

By default, MOLT Fetch exports Prometheus metrics at http://127.0.0.1:3030/metrics. You can override the address with --metrics-listen-addr '{host}:{port}', where the endpoint will be http://{host}:{port}/metrics.

Cockroach Labs recommends monitoring the following metrics during data load:

You can also use the sample Grafana dashboard to view the preceding metrics.

Start Fetch

Perform the initial load of the source data.

1. Issue the MOLT Fetch command to move the source data to CockroachDB. This example command passes the source and target connection strings as environment variables, writes intermediate files to S3 storage, and uses the truncate-if-exists table handling mode to truncate the target tables before loading data. It also limits the migration to a single schema and filters three specific tables to migrate. The data load mode defaults to IMPORT INTO.

   You must include --pglogical-replication-slot-name and --pglogical-publication-and-slot-drop-and-recreate to automatically create the publication and replication slot during the data load.

   molt fetch \
   --source $SOURCE \
   --target $TARGET \
   --schema-filter 'migration_schema' \
   --table-filter 'employees|payments|orders' \
   --bucket-path 's3://migration/data/cockroach' \
   --table-handling truncate-if-exists \
   --pglogical-replication-slot-name molt_slot \
   --pglogical-publication-and-slot-drop-and-recreate
   

   molt fetch \
   --source $SOURCE \
   --target $TARGET \
   --schema-filter 'migration_schema' \
   --table-filter 'employees|payments|orders' \
   --bucket-path 's3://migration/data/cockroach' \
   --table-handling truncate-if-exists \
   

   The command assumes an Oracle Multitenant (CDB/PDB) source. --source-cdb specifies the container database (CDB) connection string.

   molt fetch \
   --source $SOURCE \
   --source-cdb $SOURCE_CDB \
   --target $TARGET \
   --schema-filter 'migration_schema' \
   --table-filter 'employees|payments|orders' \
   --bucket-path 's3://migration/data/cockroach' \
   --table-handling truncate-if-exists \
   

2. Check the output to observe fetch progress.

   The following message shows the appropriate values for the --backfillFromSCN and --scn replication flags to use when starting Replicator:

   replication-only mode should include the following replicator flags: --backfillFromSCN 26685444 --scn 26685786
   

   A starting fetch message indicates that the task has started:

   {"level":"info","type":"summary","num_tables":3,"cdc_cursor":"0/43A1960","time":"2025-02-10T14:28:11-05:00","message":"starting fetch"}
   

   {"level":"info","type":"summary","num_tables":3,"cdc_cursor":"4c658ae6-e8ad-11ef-8449-0242ac140006:1-28","time":"2025-02-10T14:28:11-05:00","message":"starting fetch"}
   

   {"level":"info","type":"summary","num_tables":3,"cdc_cursor":"26685786","time":"2025-02-10T14:28:11-05:00","message":"starting fetch"}
   

   data extraction messages are written for each table that is exported to the location in --bucket-path:

   {"level":"info","table":"migration_schema.employees","time":"2025-02-10T14:28:11-05:00","message":"data extraction phase starting"}
   

   {"level":"info","table":"migration_schema.employees","type":"summary","num_rows":200000,"export_duration_ms":1000,"export_duration":"000h 00m 01s","time":"2025-02-10T14:28:12-05:00","message":"data extraction from source complete"}
   

   data import messages are written for each table that is loaded into CockroachDB:

   {"level":"info","table":"migration_schema.employees","time":"2025-02-10T14:28:12-05:00","message":"starting data import on target"}
   

   {"level":"info","table":"migration_schema.employees","type":"summary","net_duration_ms":1899.748333,"net_duration":"000h 00m 01s","import_duration_ms":1160.523875,"import_duration":"000h 00m 01s","export_duration_ms":1000,"export_duration":"000h 00m 01s","num_rows":200000,"cdc_cursor":"0/43A1960","time":"2025-02-10T14:28:13-05:00","message":"data import on target for table complete"}
   

   {"level":"info","table":"migration_schema.employees","type":"summary","net_duration_ms":1899.748333,"net_duration":"000h 00m 01s","import_duration_ms":1160.523875,"import_duration":"000h 00m 01s","export_duration_ms":1000,"export_duration":"000h 00m 01s","num_rows":200000,"cdc_cursor":"4c658ae6-e8ad-11ef-8449-0242ac140006:1-29","time":"2025-02-10T14:28:13-05:00","message":"data import on target for table complete"}
   

   {"level":"info","table":"migration_schema.employees","type":"summary","net_duration_ms":1899.748333,"net_duration":"000h 00m 01s","import_duration_ms":1160.523875,"import_duration":"000h 00m 01s","export_duration_ms":1000,"export_duration":"000h 00m 01s","num_rows":200000,"cdc_cursor":"2358840","time":"2025-02-10T14:28:13-05:00","message":"data import on target for table complete"}
   

   A fetch complete message is written when the fetch task succeeds:

   {"level":"info","type":"summary","fetch_id":"f5cb422f-4bb4-4bbd-b2ae-08c4d00d1e7c","num_tables":3,"tables":["migration_schema.employees","migration_schema.payments","migration_schema.payments"],"cdc_cursor":"0/3F41E40","net_duration_ms":6752.847625,"net_duration":"000h 00m 06s","time":"2024-03-18T12:30:37-04:00","message":"fetch complete"}
   

   {"level":"info","type":"summary","fetch_id":"f5cb422f-4bb4-4bbd-b2ae-08c4d00d1e7c","num_tables":3,"tables":["migration_schema.employees","migration_schema.payments","migration_schema.payments"],"cdc_cursor":"4c658ae6-e8ad-11ef-8449-0242ac140006:1-29","net_duration_ms":6752.847625,"net_duration":"000h 00m 06s","time":"2024-03-18T12:30:37-04:00","message":"fetch complete"}
   

   This message includes a cdc_cursor value. You must set the --defaultGTIDSet replication flag to this value when starting Replicator:

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

   {"level":"info","type":"summary","fetch_id":"f5cb422f-4bb4-4bbd-b2ae-08c4d00d1e7c","num_tables":3,"tables":["migration_schema.employees","migration_schema.payments","migration_schema.payments"],"cdc_cursor":"2358840","net_duration_ms":6752.847625,"net_duration":"000h 00m 06s","time":"2024-03-18T12:30:37-04:00","message":"fetch complete"}
   

Verify the data load

Use MOLT Verify to confirm that the source and target data is consistent. This ensures that the data load was successful.

1. Run the MOLT Verify command, specifying the source and target connection strings and the tables to validate.

   molt verify \
   --source $SOURCE \ 
   --target $TARGET \
   --table-filter 'employees|payments|orders'
   

   molt verify \
   --source $SOURCE \ 
   --target $TARGET \
   --table-filter 'employees|payments|orders'
   

   molt verify \
   --source $SOURCE \ 
   --target $TARGET \
   --table-filter 'employees|payments|orders'
   

   Note:

   With Oracle Multitenant deployments, while --source-cdb is required for fetch, it is not necessary for verify.

2. Check the output to observe verify progress.

   A verification in progress indicates that the task has started:

   {"level":"info","time":"2025-02-10T15:35:04-05:00","message":"verification in progress"}
   

   starting verify messages are written for each specified table:

   {"level":"info","time":"2025-02-10T15:35:04-05:00","message":"starting verify on public.employees, shard 1/1"}
   

   A finished row verification message containing a summary is written after each table is compared. For details on the summary fields, refer to the MOLT Verify page.

   {"level":"info","type":"summary","table_schema":"public","table_name":"employees","num_truth_rows":200004,"num_success":200004,"num_conditional_success":0,"num_missing":0,"num_mismatch":0,"num_extraneous":0,"num_live_retry":0,"num_column_mismatch":0,"time":"2025-02-10T15:35:05-05:00","message":"finished row verification on public.employees (shard 1/1)"}
   

   A verification complete message is written when the verify task succeeds:

   {"level":"info","net_duration_ms":699.804875,"net_duration":"000h 00m 00s","time":"2025-02-10T15:35:05-05:00","message":"verification complete"}
   

Configure Replicator

When you run replicator, you can configure the following options for replication:

• Replication connection strings: Specify URL-encoded source and target database connections.
• Replication flags: Specify required and optional flags to configure replicator behavior.
• Tuning parameters: Optimize replication performance and resource usage.
• Replicator metrics: Monitor replication progress and performance.

Replication connection strings

MOLT Replicator uses --sourceConn and --targetConn to specify the source and target database connections.

--sourceConn specifies the connection string of the source database:

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

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

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

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

--targetConn specifies the target CockroachDB connection string:

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

Replication flags

The following MOLT Replicator flags are required for continuous replication. For details on all available flags, refer to the MOLT Replicator documentation.

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

Cockroach Labs recommends monitoring the following metrics during replication:

You can use the Replicator Grafana dashboard to visualize the metrics.

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.

Start Replicator

With initial load complete, start replication of ongoing changes on the source to CockroachDB using MOLT Replicator.

Verify replication

1. Verify that Replicator is processing changes successfully. To do so, check the MOLT Replicator logs. Since you enabled debug logging with -v, you should see connection and row processing messages:

   You should see periodic primary keepalive messages:

   DEBUG  [Aug 25 14:38:10] primary keepalive received                    ReplyRequested=false ServerTime="2025-08-25 14:38:09.556773 -0500 CDT" ServerWALEnd=0/49913A58
   DEBUG  [Aug 25 14:38:15] primary keepalive received                    ReplyRequested=false ServerTime="2025-08-25 14:38:14.556836 -0500 CDT" ServerWALEnd=0/49913E60
   

   When rows are successfully replicated, you should see debug output like the following:

   DEBUG  [Aug 25 14:40:02] upserted rows                                 conflicts=0 duration=7.855333ms proposed=1 target="\"molt\".\"public\".\"tbl1\"" upserted=1
   DEBUG  [Aug 25 14:40:02] progressed to LSN: 0/49915DD0
   

   You should see binlog syncer connection and row processing:

   [2025/08/25 15:29:09] [info] binlogsyncer.go:463 begin to sync binlog from GTID set 77263736-7899-11f0-81a5-0242ac120002:1-38
   [2025/08/25 15:29:09] [info] binlogsyncer.go:409 Connected to mysql 8.0.43 server
   INFO   [Aug 25 15:29:09] connected to MySQL version 8.0.43
   

   When rows are successfully replicated, you should see debug output like the following:

   DEBUG  [Aug 25 15:29:38] upserted rows                                 conflicts=0 duration=1.801ms proposed=1 target="\"molt\".\"public\".\"tbl1\"" upserted=1
   DEBUG  [Aug 25 15:29:38] progressed to consistent point: 77263736-7899-11f0-81a5-0242ac120002:1-39
   

   When transactions are read from the Oracle source, you should see registered transaction IDs (XIDs):

   DEBUG  [Jul  3 15:55:12] registered xid 0f001f0040060000
   DEBUG  [Jul  3 15:55:12] registered xid 0b001f00bb090000
   

   When rows are successfully replicated, you should see debug output like the following:

   DEBUG  [Jul  3 15:55:12] upserted rows                                 conflicts=0 duration=2.620009ms proposed=13 target="\"molt_movies\".\"USERS\".\"CUSTOMER_CONTACT\"" upserted=13
   DEBUG  [Jul  3 15:55:12] upserted rows                                 conflicts=0 duration=2.212807ms proposed=16 target="\"molt_movies\".\"USERS\".\"CUSTOMER_DEVICE\"" upserted=16
   

   These messages confirm successful replication. You can disable verbose logging after verifying the connection.

Stop replication and verify data

1. Stop application traffic to your source database. This begins downtime.

2. Wait for replication to drain, which means that all transactions that occurred on the source database have been fully processed and replicated to CockroachDB. There are two ways to determine that replication has fully drained:

   • When replication is caught up, you will not see new upserted rows logs.

   • If you set up the replication metrics endpoint with --metricsAddr in the preceding steps, metrics are available at:

     http://{host}:{port}/_/varz
     

     Use the following Prometheus alert expression to observe when the combined rate of upserts and deletes is 0 for each schema:

     sum by (schema) (rate(apply_upserts_total[$__rate_interval]) + rate(apply_deletes_total[$__rate_interval]))
     

3. Cancel replication to CockroachDB by entering ctrl-c to issue a SIGTERM signal. This returns an exit code 0.

4. Repeat Verify the data load to verify the updated data.

Modify the CockroachDB schema

Add any constraints or indexes that you previously removed from the CockroachDB schema to facilitate data load.

For the appropriate SQL syntax, refer to ALTER TABLE ... ADD CONSTRAINT and CREATE INDEX. Review the best practices for creating secondary indexes on CockroachDB.

Cutover

Perform a cutover by resuming application traffic, now to CockroachDB.

Troubleshooting

Fetch issues

Fetch exits early due to mismatches

When run in none or truncate-if-exists mode, molt fetch exits early in the following cases, and will output a log with a corresponding mismatch_tag and failable_mismatch set to true:

• A source table is missing a primary key.

• A source primary key and target primary key have mismatching types.

  Tip:

  These restrictions (missing or mismatching primary keys) can be bypassed with --skip-pk-check.

• A STRING primary key has a different collation on the source and target.

• A source and target column have mismatching types that are not allowable mappings.

• A target table is missing a column that is in the corresponding source table.

• A source column is nullable, but the corresponding target column is not nullable (i.e., the constraint is more strict on the target).

molt fetch can continue in the following cases, and will output a log with a corresponding mismatch_tag and failable_mismatch set to false:

• A target table has a column that is not in the corresponding source table.
• A source column has a NOT NULL constraint, and the corresponding target column is nullable (i.e., the constraint is less strict on the target).
• A DEFAULT, CHECK, FOREIGN KEY, or UNIQUE constraint is specified on a target column and not on the source column.

-- change UNLIMITED to a suitable limit for the table owner
ALTER USER migration_schema QUOTA UNLIMITED ON USERS;

GRANT SELECT, FLASHBACK ON migration_schema.employees TO C##MIGRATION_USER;
GRANT SELECT, FLASHBACK ON migration_schema.payments TO C##MIGRATION_USER;
GRANT SELECT, FLASHBACK ON migration_schema.orders TO C##MIGRATION_USER;

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.

See also

• Migration Overview
• Migration Strategy
• MOLT Schema Conversion Tool
• MOLT Fetch
• MOLT Verify
• Migration Failback