Classic Bulk Load Migration from Oracle

On this page

A Classic Bulk Load Migration is the simplest way of migrating data to CockroachDB. In this approach, you stop application traffic to the source database and migrate data to the target cluster using MOLT Fetch during a significant downtime window. Application traffic is then cut over to the target after schema finalization and data verification.

• All source data is migrated to the target at once.

• This approach does not utilize continuous replication.

• Rollback is manual, but in most cases it's simple, as the source database is preserved and write traffic begins on the target all at once.

This approach is best for small databases (<100 GB), internal tools, dev/staging environments, and production environments that can handle business disruption. It's a simple approach that guarantees full data consistency and is easy to execute with limited resources, but it can only be performed if your system can handle significant downtime.

This page describes an example scenario. While the commands provided can be copy-and-pasted, they may need to be altered or reconsidered to suit the needs of your specific environment.

Example scenario

You have a small (50 GB) database that provides the data store for a web application. You want to migrate the entirety of this database to a new CockroachDB cluster. You schedule a maintenance window for Saturday from 2 AM to 6 AM, and announce it to your users several weeks in advance.

The application runs on a Kubernetes cluster.

Estimated system downtime: 4 hours.

Before the migration

• Install the MOLT (Migrate Off Legacy Technology) tools.
• Review the MOLT Fetch documentation.
• Announce the maintenance window to your users.
• Recommended: Perform a dry run of this full set of instructions in a development environment that closely resembles your production environment. This can help you get a realistic sense of the time and complexity it requires.
• Understand the prequisites and limitations of the MOLT tools:

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.

Step 1: Prepare the source database

In this step, you will:

• Create a dedicated migration user on your 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 migration_db 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;

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

GRANT SELECT ON migration_db.* TO 'migration_user'@'%';
GRANT SELECT ON mysql.gtid_executed 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;

Checkpoint

By this point in the migration, your source database should be configured so that a dedicated migration user is able to read its data.

Step 2: Prepare the target database

In this step, you will:

• Provision and run a new CockroachDB cluster.
• Define the tables on the target cluster to match those on the source.
• Create a SQL user on the target cluster with the necessary write permissions.

Provision a CockroachDB cluster

Use one of the following options to create and run a new CockroachDB cluster. This is your migration target.

Option 1: Create a secure cluster locally

If you have the CockroachDB binary installed locally, you can manually deploy a multi-node, self-hosted CockroachDB cluster on your local machine.

Learn how to deploy a CockroachDB cluster locally.

Option 2: Create a CockroachDB Self-Hosted cluster on AWS

You can manually deploy a multi-node, self-hosted CockroachDB cluster on Amazon's AWS EC2 platform, using AWS's managed load-balancing service to distribute client traffic.

Learn how to deploy a CockroachDB cluster on AWS.

Option 3: Create a CockroachDB Cloud cluster

CockroachDB Cloud is a fully-managed service run by Cockroach Labs, which simplifies the deployment and management of CockroachDB.

Sign up for a CockroachDB Cloud account and create a cluster using trial credits.

Define the target tables

Convert the source table definitions into CockroachDB-compatible equivalents. CockroachDB supports the PostgreSQL wire protocol and is largely compatible with PostgreSQL syntax.

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

  For example, a PostgreSQL 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);
  

  MySQL tables belong directly to the database specified in the connection string. A MySQL source table defined as CREATE TABLE tbl (id INT PRIMARY KEY); should map to CockroachDB's default public schema:

  CREATE TABLE tbl (id INT PRIMARY KEY);
  

  For example, an Oracle 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 define matching CockroachDB tables using the drop-on-target-and-recreate option.
  • If you define the target tables manually, review how MOLT Fetch handles type mismatches. You can use the MOLT Schema Conversion Tool to create matching table definitions.

  • 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) converts source table definitions to CockroachDB-compatible syntax. 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 table definitions.

2. Import the converted table definitions to a CockroachDB cluster:

   • When migrating to CockroachDB Cloud, the Schema Conversion Tool automatically applies the converted table definitions to a new Cloud database.
   • When migrating to a self-hosted CockroachDB cluster, export a converted DDL 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 CockroachDB schema:

GRANT CREATE ON SCHEMA public TO crdb_user;

If you manually defined the target tables (which means that 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 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 being migrated:

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

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

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

GRANT SYSTEM 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;

Checkpoint

By this point in the migration, your source and target tables should match one another, although you may have dropped non-PRIMARY KEY constraints and indexes from the target to optimize data load performance. Additionally, both databases should have the correct read/write permissions to allow the MOLT tools to perform the migration.

Step 3: Stop application traffic

With both the source and target databases prepared for the data load, it's time to stop application traffic to the source. At the start of the maintenance window, scale down the Kubernetes cluster to zero pods.

kubectl scale deployment app --replicas=0

Step 4: Load data into CockroachDB

In this step, you will:

• Configure MOLT Fetch with the flags needed for your migration.
• Run MOLT Fetch.
• Understand how to continue a load after an interruption.

Configure MOLT 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/migration_db?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/migration_db?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/migration_db?sslmode=verify-full"
    export TARGET="postgres://root@localhost:26257/defaultdb?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/migration_db/certs/ca.pem&sslcert=/etc/migration_db/certs/client.crt&sslkey=/etc/migration_db/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/migration_db
    

• 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

Use the following flags to filter the data to be migrated.

--schema-filter 'migration_schema'

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:

To visualize the preceding metrics, use the Grafana dashboard bundled with your binary (grafana_dashboard.json). The bundled dashboard matches your binary version. Alternatively, you can download the latest dashboard.

Run MOLT Fetch

Perform the bulk load of the source data.

1. Run the MOLT Fetch command to move the source data into 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 limits the migration to a single schema and filters for three specific tables. The data load mode defaults to IMPORT INTO. Include the --ignore-replication-check flag to skip replication checkpoint queries, which eliminates the need to configure the source database for logical replication.

   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 \
   --ignore-replication-check
   

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

   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 \
   --ignore-replication-check
   

2. Check the output to observe fetch progress.

   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-29","time":"2025-02-10T14:28:11-05:00","message":"starting fetch"}
   

   {"level":"info","type":"summary","num_tables":3,"cdc_cursor":"backfillFromSCN=26685444,scn=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"}
   

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

   {"level":"info","table":"public.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":"public.employees","time":"2025-02-10T14:28:12-05:00","message":"starting data import on target"}
   

   {"level":"info","table":"public.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","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":"backfillFromSCN=26685444,scn=26685786","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":["public.employees","public.payments","public.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"}
   

   {"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":"backfillFromSCN=26685444,scn=26685786","net_duration_ms":6752.847625,"net_duration":"000h 00m 06s","time":"2024-03-18T12:30:37-04:00","message":"fetch complete"}
   

Continue MOLT Fetch after an interruption

If MOLT Fetch fails while loading data into CockroachDB from intermediate files, it exits with an error message, fetch ID, and continuation token for each table that failed to load on the target database.

{"level":"info","table":"public.employees","file_name":"shard_01_part_00000001.csv.gz","message":"creating or updating token for duplicate key value violates unique constraint \"employees_pkey\"; Key (id)=(1) already exists."}
{"level":"info","table":"public.employees","continuation_token":"a1b2c3d4-e5f6-7890-abcd-ef1234567890","message":"created continuation token"}
{"level":"info","fetch_id":"f5cb422f-4bb4-4bbd-b2ae-08c4d00d1e7c","message":"continue from this fetch ID"}
{"level":"error","message":"Error: error from fetching table for public.employees: error importing data: duplicate key value violates unique
  constraint \"employees_pkey\" (SQLSTATE 23505)"}

You can use this information to continue the task from the continuation point where it was interrupted.

Continuation is only possible under the following conditions:

• All data has been exported from the source database into intermediate files on cloud or local storage.
• The initial load of source data into the target CockroachDB database is incomplete.
• The load uses IMPORT INTO rather than COPY FROM.

The following command reattempts the data load starting from a specific continuation file, but you can also use individual continuation tokens to reattempt the data load for individual tables.

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 \
--ignore-replication-check \
--fetch-id f5cb422f-4bb4-4bbd-b2ae-08c4d00d1e7c \
--continuation-file-name shard_01_part_00000001.csv.gz

molt fetch \
--source $SOURCE \
--target $TARGET \
--table-filter 'employees|payments|orders' \
--bucket-path 's3://migration/data/cockroach' \
--table-handling truncate-if-exists \
--ignore-replication-check \
--fetch-id f5cb422f-4bb4-4bbd-b2ae-08c4d00d1e7c \
--continuation-file-name shard_01_part_00000001.csv.gz

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 \
--ignore-replication-check \
--fetch-id f5cb422f-4bb4-4bbd-b2ae-08c4d00d1e7c \
--continuation-file-name shard_01_part_00000001.csv.gz

Step 5: Verify the data

In this step, you will use MOLT Verify to confirm that the source and target data is consistent. This ensures that the data load was successful.

Run MOLT Verify

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 is written after each table is compared. If num_success equals num_truth_rows and the error counters (num_missing, num_mismatch, num_extraneous, and num_column_mismatch) are all 0, the table verified successfully. Any non-zero values in the error counters indicate data discrepancies that need investigation. For details on each field, 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"}
   

Step 6: Finalize the target schema

Add constraints and indexes

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.

Step 7: Cut over application traffic

With the target cluster verified and finalized, it's time to resume application traffic.

Modify application code

In the application back end, make sure that the application now directs traffic to the CockroachDB cluster. For example:

env:
  - name: DATABASE_URL
    value: postgres://root@localhost:26257/defaultdb?sslmode=verify-full

Resume application traffic

Scale up the Kubernetes deployment to the original number of replicas:

kubectl scale deployment app --replicas=3

This ends downtime.

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.

failed to export snapshot: please ensure that you have GTID-based replication enabled: sql: no rows in result set

START TRANSACTION;
SELECT 1;
COMMIT;

SHOW MASTER STATUS;

-- 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;

See also

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