MOLT Fetch

On this page

MOLT Fetch moves data from a source database into CockroachDB as part of a database migration.

MOLT Fetch uses IMPORT INTO or COPY FROM to move the source data to cloud storage (Google Cloud Storage or Amazon S3), a local file server, or local memory. Once the data is exported, MOLT Fetch can load the data into a target CockroachDB database and replicate changes from the source database. For details, see Usage.

Supported databases

The following source databases are currently supported:

• PostgreSQL
• MySQL
• CockroachDB

Installation

To install MOLT Fetch, download the binary that matches your system. To download the latest binary:

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

Docker image

Docker multi-platform images containing both the AMD and ARM binaries are available. To pull the latest image:

docker pull cockroachdb/molt

To pull a specific version (e.g., 1.1.3):

docker pull cockroachdb/molt:1.1.3

For details on running in Docker, see Docker usage.

Setup

Complete the following items before using MOLT Fetch:

• Follow the recommendations in Best practices and Security recommendations.

• Ensure that the source and target schemas are identical, unless you enable automatic schema creation with the drop-on-target-and-recreate option. If you are creating the target schema manually, review the behaviors in Mismatch handling.

• Ensure that the SQL user running MOLT Fetch has SELECT privileges on the source and target CockroachDB databases, along with the required privileges to run IMPORT INTO or COPY FROM (depending on the command used for data movement) on CockroachDB, as described on their respective pages.

• If you plan to use continuous replication, using either the MOLT Fetch replication feature or an external change data capture (CDC) tool:

  • For PostgreSQL sources, enable logical replication. In postgresql.conf or in the SQL shell, set wal_level to logical.
  • For MySQL 8.0 and later sources, enable GTID consistency. Set the following values in mysql.cnf, in the SQL shell, or as flags in the mysql start command:
    • --enforce-gtid-consistency=ON
    • --gtid-mode=ON
    • --binlog-row-metadata=full
  • For MySQL 5.7 sources, set the following values. Note that binlog-row-image is used instead of binlog-row-metadata. Set server-id to a unique integer that differs from any other MySQL server you have in your cluster (e.g., 3).
    • --enforce-gtid-consistency=ON
    • --gtid-mode=ON
    • --binlog-row-image=full
    • --server-id={ID}
    • --log-bin=log-bin

• Percent-encode the connection strings for the source database and CockroachDB. This ensures that the MOLT tools can parse special characters in your password.

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

    molt escape-password 'a$52&'
    

    Substitute the following encoded password in your original connection url string:

    a%2452%26
    

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

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

• If you plan to use cloud storage for the data migration, follow the steps in Secure cloud storage.

Best practices

• To prevent connections from terminating prematurely during data export, set the following to high values on the source database:

  • Maximum allowed number of connections: MOLT Fetch can export data across multiple connections. The number of connections it will create is the number of shards (--export-concurrency) multiplied by the number of tables (--table-concurrency) being exported concurrently.
  • Maximum lifetime of a connection: This is particularly important for MySQL sources, which can only use a single connection to move data. See the following note.

• If a PostgreSQL database is set as a source, ensure that idle_in_transaction_session_timeout on PostgreSQL is either disabled or set to a value longer than the duration of data export. Otherwise, the connection will be prematurely terminated. To estimate the time needed to export the PostgreSQL tables, you can perform a dry run and sum the value of molt_fetch_table_export_duration_ms for all exported tables.

• If a MySQL database is set as a source, the --table-concurrency and --export-concurrency flags cannot be set above 1. If these values are changed, MOLT Fetch returns an error. This guarantees consistency when moving data from MySQL, due to MySQL limitations. MySQL data is migrated to CockroachDB one table and shard at a time, using WITH CONSISTENT SNAPSHOT transactions.

• To prevent memory outages during READ COMMITTED data export of tables with large rows, estimate the amount of memory used to export a table:

  --row-batch-size * --export-concurrency * average size of the table rows
  

  If you are exporting more than one table at a time (i.e., --table-concurrency is set higher than 1), add the estimated memory usage for the tables with the largest row sizes. Ensure that you have sufficient memory to run molt fetch, and adjust --row-batch-size accordingly.

• If a table in the source database is much larger than the other tables, filter and export the largest table in its own molt fetch task. Repeat this for each of the largest tables. Then export the remaining tables in another task.

• When using IMPORT INTO to load tables into CockroachDB, if the fetch task terminates before the import job completes, the hanging import job on the target database will keep the table offline. To make this table accessible again, manually resume or cancel the job. Then resume molt fetch using continuation, or restart the task from the beginning.

• Ensure that the machine running MOLT Fetch is large enough to handle the amount of data being migrated. Fetch performance can sometimes be limited by available resources, but should always be making progress. To identify possible resource constraints, observe the molt_fetch_rows_exported metric for decreases in the number of rows being processed. You can use the sample Grafana dashboard to view metrics.

Security recommendations

Cockroach Labs strongly recommends the following:

Secure connections

• Use secure connections to the source and target CockroachDB database whenever possible.
• When performing failback, use a secure changefeed connection by overriding the default configuration.
• By default, insecure connections (i.e., sslmode=disable on PostgreSQL; sslmode not set on MySQL) are disallowed. When using an insecure connection, molt fetch returns an error. To override this check, you can enable the --allow-tls-mode-disable flag. Do this only when testing, or if a secure SSL/TLS connection to the source or target database is not possible.

Connection strings

• Avoid plaintext connection strings.
• Provide your connection strings as environment variables.

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

  For example, to export connection strings as environment variables:

  export SOURCE="postgres://postgres:postgres@localhost:5432/molt?sslmode=verify-full"
  export TARGET="postgres://root@localhost:26257/molt?sslmode=verify-full"
  

  Afterward, to pass the environment variables in molt fetch commands:

  molt fetch \
  --source $SOURCE \
  --target $TARGET \
  --table-filter 'employees' \
  --bucket-path 's3://molt-test' \
  --table-handling truncate-if-exists
  

Secure cloud storage

• When using cloud storage for your intermediate store, ensure that access control is properly configured.

  • If you are using Amazon S3 for cloud storage:

    • Ensure that the environment variable and access tokens are set appropriately in the terminal running molt fetch. For example:

      export AWS_REGION='us-east-1'
      export AWS_SECRET_ACCESS_KEY='key'
      export AWS_ACCESS_KEY_ID='id'
      

    • Alternatively, set the --use-implicit-auth flag to use implicit authentication.

    • Ensure the S3 bucket is created and accessible by CockroachDB.

  • If you are using Google Cloud Storage for cloud storage:

    • Ensure that your local environment is authenticated using Application Default Credentials:

      Using gcloud:

      gcloud init
      gcloud auth application-default login
      

      Using the environment variable:

      export GOOGLE_APPLICATION_CREDENTIALS={path_to_cred_json}
      

    • Alternatively, set the --use-implicit-auth flag to use implicit authentication.

    • Ensure the Google Cloud Storage bucket is created and accessible by CockroachDB.

• Do not use public cloud storage in production.

Perform a dry run

To verify that your connections and configuration work properly, run MOLT Fetch in a staging environment before moving any data in production. Use a test or development environment that is as similar as possible to production.

Commands

Subcommands

Flags

Global flags

tokens list flags

Replication flags

The following flags are set with --replicator-flags and can be used in any Fetch mode that involves replication.

PostgreSQL replication flags

The following flags are set with --replicator-flags and can be used in any Fetch mode that involves replication from a PostgreSQL source database.

MySQL replication flags

The following flags are set with --replicator-flags and can be used in any Fetch mode that involves replication from a MySQL source database.

Failback replication flags

The following flags are set with --replicator-flags and can be used in failback mode.

Usage

The following sections describe how to use the molt fetch flags.

Source and target databases

--source specifies the connection string of the source database.

PostgreSQL or CockroachDB:

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

MySQL:

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

--target specifies the CockroachDB connection string:

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

Fetch mode

--mode specifies the MOLT Fetch behavior:

• Load data into CockroachDB
• Load data and replicate changes to CockroachDB
• Replicate changes to CockroachDB
• Export the data to storage
• Fail back to source database

Load data

data-load (default) instructs MOLT Fetch to load the source data into CockroachDB. It does not replicate any subsequent changes on the source.

--mode data-load

If the source is a PostgreSQL database and you intend to replicate changes afterward, also specify a replication slot name with --pglogical-replication-slot-name. MOLT Fetch will create a replication slot with this name. For example, the following snippet instructs MOLT Fetch to create a slot named replication_slot to use for replication:

--mode data-load
--pglogical-replication-slot-name 'replication_slot'

Load data and replicate changes

data-load-and-replication instructs MOLT Fetch to load the source data into CockroachDB, and replicate any subsequent changes on the source.

--mode data-load-and-replication

If the source is a PostgreSQL database, you must also specify a replication slot name with --pglogical-replication-slot-name. MOLT Fetch will create a replication slot with this name. For example, the following snippet instructs MOLT Fetch to create a slot named replication_slot to use for replication:

--mode data-load-and-replication
--pglogical-replication-slot-name 'replication_slot'

Continuous replication begins once the initial load is complete, as indicated by a fetch complete message in the output. If replication is interrupted, you can resume replication.

To cancel replication, enter ctrl-c to issue a SIGTERM signal. This returns an exit code 0. If replication fails, a non-zero exit code is returned.

To customize the replication behavior (an advanced use case), use --replicator-flags to specify one or more replication-specific flags.

--mode data-load-and-replication
--replicator-flags "--applyTimeout '1h' --parallelism 64"

Replicate changes

replication-only instructs MOLT Fetch to replicate ongoing changes on the source to CockroachDB, using the specified replication marker. This assumes you have already run --mode data-load to load the source data into CockroachDB.

• For a PostgreSQL source, you should have already created a replication slot when loading data. Specify the same replication slot name using --pglogical-replication-slot-name. For example:

  --mode replication-only 
  --pglogical-replication-slot-name 'replication_slot'
  

  Tip:

  In case you want to run replication-only without already having loaded data (e.g., for testing), also include --pglogical-publication-and-slot-drop-and-recreate to ensure that the publication and replication slot are created in the correct order. For details on this flag, refer to Global flags.

• For a MySQL source, first get your GTID record:

  SELECT source_uuid, min(interval_start), max(interval_end)
    FROM mysql.gtid_executed
    GROUP BY source_uuid;
  

  In the molt fetch command, specify a GTID set using the --defaultGTIDSet replication flag and the format source_uuid:min(interval_start)-max(interval_end). For example:

  --mode replication-only 
  --replicator-flags "--defaultGTIDSet 'b7f9e0fa-2753-1e1f-5d9b-2402ac810003:3-21'"
  

If replication is interrupted, you can resume replication.

Resume replication

replication-only can be used to resume replication if it is interrupted in either data-load-and-replication or replication-only mode.

Specify the staging schema with the --stagingSchema replication flag. MOLT Fetch outputs the schema name as staging database name: {schema_name} after the initial replication run.

--mode replication-only
--replicator-flags "--stagingSchema {schema_name}"

You must include the --stagingSchema replication flag when resuming replication, as the schema provides a replication marker for streaming changes.

To cancel replication, enter ctrl-c to issue a SIGTERM signal. This returns an exit code 0. If replication fails, a non-zero exit code is returned.

Export data to storage

export-only instructs MOLT Fetch to export the source data to the specified cloud storage or local file server. It does not load the data into CockroachDB.

--mode export-only

Import data from storage

import-only instructs MOLT Fetch to load the source data in the specified cloud storage or local file server into the CockroachDB target.

--mode import-only

Fail back to source database

If you encounter issues after moving data to CockroachDB, you can use failback mode to replicate changes on CockroachDB back to the initial source database. In case you need to roll back the migration, this ensures that data is consistent on the initial source database.

failback mode creates a CockroachDB changefeed and sets up a webhook sink to pass change events from CockroachDB to the failback target. In production, you should secure the connection by specifying changefeed override settings in a JSON file. These settings override the [default insecure changefeed]#default-insecure-changefeed] values, which are suited for testing only. Include the --changefeeds-path flag to indicate the path to the JSON file.

--mode failback
--changefeeds-path 'changefeed-settings.json'

When running molt fetch --mode failback, --source is the CockroachDB connection string and --target is the connection string of the database you migrated from. --table-filter specifies the tables to watch for change events. For example:

--source 'postgresql://{username}:{password}@{host}:{port}/{database}'
--target 'mysql://{username}:{password}@{protocol}({host}:{port})/{database}'
--table-filter 'employees, payments'

Changefeed override settings

You can specify the following CREATE CHANGEFEED parameters in the override JSON. If any parameter is not specified, its default value is used.

• The following CREATE CHANGEFEED sink URI parameters:
  • host: The hostname or IP address of the webhook sink where change events are sent. The applicable certificates of the failback target (i.e., the source database from which you migrated) must be located on this machine.
  • port: The port of the webhook sink.
  • sink_query_parameters: A comma-separated list of CREATE CHANGEFEED query parameters. This includes the base64-encoded client certificate (client_cert), key (client_key), and CA (ca_cert) for a secure webhook sink.
• The following CREATE CHANGEFEED options:
  • resolved
  • min_checkpoint_frequency
  • initial_scan
  • webhook_sink_config
    

Use a secure changefeed connection whenever possible. The default insecure configuration is not recommended in production. To secure the changefeed connection, define sink_query_parameters in the JSON as follows:

{
    "sink_query_parameters": "client_cert={base64 cert}&client_key={base64 key}&ca_cert={base64 CA cert}"
}

client_cert, client_key, and ca_cert are webhook sink parameters that must be base64- and URL-encoded (for example, use the command base64 -i ./client.crt | jq -R -r '@uri').

In the molt fetch command, also include --replicator-flags to specify the paths to the server certificate and key that correspond to the client certs defined in sink_query_parameters. For example:

--changefeeds-path 'changefeed-secure.json'
--replicator-flags "--tlsCertificate ./certs/server.crt --tlsPrivateKey ./certs/server.key"

For a complete example of using molt fetch in failback mode, see Fail back securely from CockroachDB.

Default insecure changefeed

When molt fetch --mode failback is run without specifying --changefeeds-path, the following CREATE CHANGEFEED parameters are used for the changefeed:

{
    "host": "localhost",
    "port": 30004,
    "sink_query_parameters": "insecure_tls_skip_verify=true",
    "resolved": "1s",
    "min_checkpoint_frequency": "1s",
    "initial_scan": "no",
    "webhook_sink_config": "{\"Flush\":{\"Bytes\":1048576}}"
}

The default parameters specify a local webhook sink ("localhost") and an insecure sink connection ("insecure_tls_skip_verify=true"), which are suited for testing only. In order to run failback with the default insecure configuration, you must also include the following flags:

--allow-tls-mode-disable
--replicator-flags '--tlsSelfSigned --disableAuthentication'

Data movement

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

By default, MOLT Fetch uses IMPORT INTO:

• IMPORT INTO achieves the highest throughput, but requires taking the CockroachDB tables offline to achieve its import speed. Tables are taken back online once an import job completes successfully. See Best practices.
• IMPORT INTO supports compression using the --compression flag, which reduces the amount of storage used.

--use-copy configures MOLT Fetch to use COPY FROM:

• COPY FROM enables your tables to remain online and accessible. However, it is slower than using IMPORT INTO.
• COPY FROM does not support compression.

Data path

MOLT Fetch can move the source data to CockroachDB via cloud storage, a local file server, or directly without an intermediate store.

Cloud storage

--bucket-path instructs MOLT Fetch to write intermediate files to a path within a Google Cloud Storage or Amazon S3 bucket to which you have the necessary permissions. For example:

Google Cloud Storage:

--bucket-path 'gs://migration/data/cockroach'

Amazon S3:

--bucket-path 's3://migration/data/cockroach'

Cloud storage can be used to move data with either IMPORT INTO or COPY FROM.

Local file server

--local-path instructs MOLT Fetch to write intermediate files to a path within a local file server. local-path-listen-addr specifies the address of the local file server. For example:

--local-path /migration/data/cockroach
--local-path-listen-addr 'localhost:3000'

In some cases, CockroachDB will not be able to use the local address specified by --local-path-listen-addr. This will depend on where CockroachDB is deployed, the runtime OS, and the source dialect.

For example, if you are migrating to CockroachDB Cloud, such that the Cloud cluster is in a different physical location than the machine running molt fetch, then CockroachDB cannot reach an address such as localhost:3000. In these situations, use --local-path-crdb-access-addr to specify an address for the local file server that is publicly accessible. For example:

--local-path /migration/data/cockroach
--local-path-listen-addr 'localhost:3000'
--local-path-crdb-access-addr '44.55.66.77:3000'

A local file server can be used to move data with either IMPORT INTO or COPY FROM.

Direct copy

--direct-copy specifies that MOLT Fetch should use COPY FROM to move the source data directly to CockroachDB without an intermediate store:

• Because the data is held in memory, the machine must have sufficient RAM for the data currently in flight:

  average size of each row * --row-batch-size * --export-concurrency * --table-concurrency
  

• Direct copy does not support compression or continuation.

• The --use-copy flag is redundant with --direct-copy.

Schema and table selection

By default, MOLT Fetch moves all data from the --source database to CockroachDB. Use the following flags to move a subset of data.

--schema-filter specifies a range of schema objects to move to CockroachDB, formatted as a POSIX regex string. For example, to move every table in the source database's public schema:

--schema-filter 'public'

--table-filter and --table-exclusion-filter specify tables to include and exclude from the migration, respectively, formatted as POSIX regex strings. For example, to move every source table that has "user" in the table name and exclude every source table that has "temp" in the table name:

--table-filter '.*user.*' --table-exclusion-filter '.*temp.*'

Target table handling

--table-handling defines how MOLT Fetch loads data on the CockroachDB tables that match the selection.

To load the data without changing the existing data in the tables, use none:

--table-handling none

To truncate tables before loading the data, use truncate-if-exists:

--table-handling truncate-if-exists

To drop existing tables and create new tables before loading the data, use drop-on-target-and-recreate:

--table-handling drop-on-target-and-recreate

When using the drop-on-target-and-recreate option, MOLT Fetch creates a new CockroachDB table to load the source data if one does not already exist. To guide the automatic schema creation, you can explicitly map source types to CockroachDB types.

Mismatch handling

If either none or truncate-if-exists is set, molt fetch loads data into the existing tables on the target CockroachDB database. If the target schema mismatches the source schema, molt fetch will exit early in certain cases, and will need to be re-run from the beginning.

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 and table primary key have mismatching types.
• 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.

Type mapping

If drop-on-target-and-recreate is set, MOLT Fetch automatically creates a CockroachDB schema that is compatible with the source data. The column types are determined as follows:

• PostgreSQL types are mapped to existing CockroachDB types that have the same OID.

• The following MySQL types are mapped to corresponding CockroachDB types:

  MySQL type	CockroachDB type
  CHAR, CHARACTER, VARCHAR, NCHAR, NVARCHAR	VARCHAR
  TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT	STRING
  GEOMETRY	GEOMETRY
  LINESTRING	LINESTRING
  POINT	POINT
  POLYGON	POLYGON
  MULTIPOINT	MULTIPOINT
  MULTILINESTRING	MULTILINESTRING
  MULTIPOLYGON	MULTIPOLYGON
  GEOMETRYCOLLECTION, GEOMCOLLECTION	GEOMETRYCOLLECTION
  JSON	JSONB
  TINYINT, INT1	INT2
  BLOB	BYTES
  SMALLINT, INT2	INT2
  MEDIUMINT, INT, INTEGER, INT4	INT4
  BIGINT, INT8	INT
  FLOAT	FLOAT4
  DOUBLE	FLOAT
  DECIMAL, NUMERIC, REAL	DECIMAL (Negative scale values are autocorrected to 0)
  BINARY, VARBINARY	BYTES
  DATETIME	TIMESTAMP
  TIMESTAMP	TIMESTAMPTZ
  TIME	TIME
  BIT	VARBIT
  DATE	DATE
  TINYBLOB, MEDIUMBLOB, LONGBLOB	BYTES
  BOOL, BOOLEAN	BOOL
  ENUM	ANY_ENUM

• To override the default mappings for automatic schema creation, you can map source to target CockroachDB types explicitly. These are defined in the JSON file indicated by the --type-map-file flag. The allowable custom mappings are valid CockroachDB aliases, casts, and the following mappings specific to MOLT Fetch and Verify:

  • TIMESTAMP <> TIMESTAMPTZ
  • VARCHAR <> UUID
  • BOOL <> INT2
  • VARBIT <> TEXT
  • VARBIT <> BOOL
  • JSONB <> TEXT
  • INET <> TEXT

--type-map-file specifies the path to the JSON file containing the explicit type mappings. For example:

--type-map-file 'type-mappings.json'

The following JSON example defines two type mappings:

[
  {
    "table": "public.t1",
    "column-type-map": [
      {
        "column": "*",
        "type-kv": {
          "source-type": "int",
          "crdb-type": "INT2"
        }
      },
      {
        "column": "name",
        "type-kv": {
          "source-type": "varbit",
          "crdb-type": "string"
        }
      }
    ]
  }
]

• table specifies the table that will use the custom type mappings in column-type-map, written as {schema}.{table}.
• column specifies the column that will use the custom type mapping in type-kv. If * is specified, then all columns in the table with the matching source-type are converted.
• type-kv specifies the source-type that maps to the target crdb-type.

Transformations

You can define transformation rules to be performed on the target schema during the fetch task. These can be used to:

• Map computed columns to a target schema.
• Map partitioned tables to a single target table.
• Rename tables on the target schema.

Transformation rules are defined in the JSON file indicated by the --transformations-file flag. For example:

--transformations-file 'transformation-rules.json'

The following JSON example defines two transformation rules:

{
  "transforms": [
    {
      "id": 1,
      "resource_specifier": {
        "schema": ".*",
        "table": ".*"
      },
      "column_exclusion_opts": {
        "add_computed_def": true,
        "column": "^age$"
      }
    },
    {
      "id": 2,
      "resource_specifier": {
        "schema": "public",
        "table": "charges_part.*"
      },
      "table_rename_opts": {
        "value": "charges"
      }
    }
  ]
}

• resource_specifier configures the following options for transformation rules:
  • schema specifies the schemas to be affected by the transformation rule, formatted as a POSIX regex string.
  • table specifies the tables to be affected by the transformation rule, formatted as a POSIX regex string.
• column_exclusion_opts configures the following options for column exclusions and computed columns:
  • column specifies source columns to exclude from being mapped to regular columns on the target schema. It is formatted as a POSIX regex string.
  • add_computed_def, when set to true, specifies that each matching column should be mapped to a computed column on the target schema. Instead of being moved from the source, the column data is generated on the target using ALTER TABLE ... ADD COLUMN and the computed column definition from the source schema. This assumes that all matching columns are computed columns on the source.
    Warning:

    Columns that match the column regex will not be moved to CockroachDB if add_computed_def is omitted or set to false (default), or if a matching column is a non-computed column.

• table_rename_opts configures the following option for table renaming:

  • value specifies the table name to which the matching resource_specifier is mapped. If only one source table matches resource_specifier, it is renamed to table_rename_opts.value on the target. If more than one table matches resource_specifier (i.e., an n-to-1 mapping), the fetch task assumes that all matching tables are partitioned tables with the same schema, and moves their data to a table named table_rename_opts.value on the target. Otherwise, the task will error.

    Additionally, in an n-to-1 mapping situation:

    • Specify --use-copy or --direct-copy for data movement. This is because the data from the source tables is loaded concurrently into the target table.
    • Create the target table schema manually, and do not use --table-handling drop-on-target-and-recreate for target table handling.

The preceding JSON example therefore defines two rules:

• Rule 1 maps all source age columns on the source database to computed columns on CockroachDB. This assumes that all matching age columns are defined as computed columns on the source.
• Rule 2 maps all table names with prefix charges_part from the source database to a single charges table on CockroachDB (i.e., an n-to-1 mapping). This assumes that all matching charges_part.* tables have the same schema.

Each rule is applied in the order it is defined. If two rules overlap, the later rule will override the earlier rule.

To verify that the logging shows that the computed columns are being created:

When running molt fetch, set --logging debug and look for ALTER TABLE ... ADD COLUMN statements with the STORED or VIRTUAL keywords in the log output:

{"level":"debug","time":"2024-07-22T12:01:51-04:00","message":"running: ALTER TABLE IF EXISTS public.computed ADD COLUMN computed_col INT8 NOT NULL AS ((col1 + col2)) STORED"}

After running molt fetch, issue a SHOW CREATE TABLE statement on CockroachDB:

SHOW CREATE TABLE computed;

  table_name |                         create_statement
-------------+-------------------------------------------------------------------
  computed   | CREATE TABLE public.computed (
  ...
             |     computed_col INT8 NOT NULL AS (col1 + col2) STORED
             | )

Fetch continuation

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. You can use this information to continue the task from the continuation point where it was interrupted. For an example, see Continue fetch after encountering an error.

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. This means that ongoing replication of source data has not begun.

To retry all data starting from the continuation point, reissue the molt fetch command and include the --fetch-id.

--fetch-id d44762e5-6f70-43f8-8e15-58b4de10a007

To retry a specific table that failed, include both --fetch-id and --continuation-token. The latter flag specifies a token string that corresponds to a specific table on the source database. A continuation token is written in the molt fetch output for each failed table. If the fetch task encounters a subsequent error, it generates a new token for each failed table. See List active continuation tokens.

--fetch-id d44762e5-6f70-43f8-8e15-58b4de10a007
--continuation-token 011762e5-6f70-43f8-8e15-58b4de10a007

To retry all data starting from a specific file, include both --fetch-id and --continuation-file-name. The latter flag specifies the filename of an intermediate file in cloud or local storage. All filenames are prepended with part_ and have the .csv.gz or .csv extension, depending on compression type (gzip by default). For example:

--fetch-id d44762e5-6f70-43f8-8e15-58b4de10a007
--continuation-file-name part_00000003.csv.gz

List active continuation tokens

To view all active continuation tokens, issue a molt fetch tokens list command along with --conn-string, which specifies the connection string for the target CockroachDB database. For example:

molt fetch tokens list \
--conn-string 'postgres://root@localhost:26257/defaultdb?sslmode=verify-full'

+--------------------------------------+--------------------------------------+------------------+----------------------+
|                  ID                  |               FETCH ID               |    TABLE NAME    |      FILE NAME       |
+--------------------------------------+--------------------------------------+------------------+----------------------+
| f6f0284c-d9c1-43c9-8fde-af609d0dbd82 | 66443597-5689-4df3-a7b9-9fc5e27180eb | public.employees | part_00000001.csv.gz |
+--------------------------------------+--------------------------------------+------------------+----------------------+
Continuation Tokens.

CDC cursor

A change data capture (CDC) cursor is written to the output as cdc_cursor at the beginning and end of the fetch task. For example:

{"level":"info","type":"summary","fetch_id":"735a4fe0-c478-4de7-a342-cfa9738783dc","num_tables":1,"tables":["public.employees"],"cdc_cursor":"0/3F41E40","net_duration_ms":4879.890041,"net_duration":"000h 00m 04s","time":"2024-03-18T12:37:02-04:00","message":"fetch complete"}

You can use the cdc_cursor value with an external change data capture (CDC) tool to continuously replicate subsequent changes on the source data to CockroachDB.

Metrics

By default, MOLT Fetch exports Prometheus metrics at 127.0.0.1:3030/metrics. You can configure this endpoint with the --metrics-listen-addr flag.

Cockroach Labs recommends monitoring the following metrics:

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

Docker usage

For details on pulling Docker images, see Docker image.

Performance

MOLT Fetch and Verify 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.

Authentication

When using MOLT Fetch with cloud storage, it is necessary to specify volumes and environment variables, as described in the following sections for Google Cloud Storage and Amazon S3.

No additional configuration is needed when running MOLT Fetch with a local file server or in direct copy mode:

docker run -it cockroachdb/molt fetch ...

For more information on docker run, see the Docker documentation.

Google Cloud Storage

If you are using Google Cloud Storage for cloud storage:

Volume map the application_default_credentials.json file into the container, and set the GOOGLE_APPLICATION_CREDENTIALS environment variable to point to this file.

docker run \
  -v ~/.config/gcloud/application_default_credentials.json:/gcp/creds.json:ro \
  -e GOOGLE_APPLICATION_CREDENTIALS=/gcp/creds.json \
  -it \
  cockroachdb/molt fetch ...

In case the previous authentication method fails, you can volume map the entire Google Cloud configuration directory into the container. In addition to setting the GOOGLE_APPLICATION_CREDENTIALS environment variable, set CLOUDSDK_CONFIG to point to the configuration directory:

docker run \
  -v ~/.config/gcloud:/gcp/config:ro \
  -e CLOUDSDK_CONFIG=/gcp/config \
  -e GOOGLE_APPLICATION_CREDENTIALS=/gcp/config/application_default_credentials.json \
  -it \
  cockroachdb/molt fetch ...

For details on Google Cloud Storage authentication, see Application Default Credentials.

Amazon S3

If you are using Amazon S3 for cloud storage:

Volume map the host's ~/.aws directory to the /root/.aws directory inside the container, and set the required AWS_REGION, AWS_SECRET_ACCESS_KEY, and AWS_ACCESS_KEY_ID environment variables:

docker run \
  -v ~/.aws:/root/.aws \
  -e AWS_REGION=your-region \
  -e AWS_SECRET_ACCESS_KEY=your-secret-access-key \
  -e AWS_ACCESS_KEY_ID=your-access-key-id \
  -it \
  cockroachdb/molt fetch ...

Local connection strings

When testing locally, specify the host as follows:

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

--source 'postgres://postgres:postgres@host.docker.internal:5432/molt?sslmode=disable'
--target "postgres://root@host.docker.internal:26257/molt?sslmode=disable"

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

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

Examples

The following examples demonstrate how to issue molt fetch commands to load data into CockroachDB. These examples assume that secure connections to the source and target database are used.

Load PostgreSQL data via S3 with continuous replication

The following molt fetch command uses IMPORT INTO to load a subset of tables from a PostgreSQL database to CockroachDB.

molt fetch \
--source 'postgres://postgres:postgres@localhost/molt' \
--target 'postgres://root@localhost:26257/defaultdb?sslmode=verify-full' \
--table-handling 'truncate-if-exists' \
--table-filter 'employees' \
--bucket-path 's3://migration/data/cockroach' \
--cleanup \
--pglogical-replication-slot-name 'replication_slot' \
--mode data-load-and-replication

• --table-handling specifies that existing tables on CockroachDB should be truncated before the source data is loaded.
• --table-filter filters for tables with the employees string in the name.
• --bucket-path specifies a directory on an Amazon S3 bucket where intermediate files will be written.
• --cleanup specifies that the intermediate files should be removed after the source data is loaded.
• --pglogical-replication-slot-name specifies a replication slot name to be created on the source PostgreSQL database. This is used in continuous replication.
• --mode data-load-and-replication starts continuous replication of data from the source database to CockroachDB after the fetch task succeeds.

If the fetch task succeeds, the output displays a fetch complete message like the following:

{"level":"info","type":"summary","fetch_id":"f5cb422f-4bb4-4bbd-b2ae-08c4d00d1e7c","num_tables":1,"tables":["public.employees"],"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"}

Continuous replication begins immediately afterward:

{"level":"info","time":"2024-05-13T14:33:07-04:00","message":"starting replicator"}
{"level":"info","time":"2024-05-13T14:36:22-04:00","message":"creating publication"}

To cancel replication, enter ctrl-c to issue a SIGTERM signal.

Load MySQL data via GCP with continuous replication

The following molt fetch command uses COPY FROM to load a subset of tables from a MySQL database to CockroachDB.

molt fetch \
--source 'mysql://root:password@localhost/molt?sslcert=.%2fsource_certs%2fclient.root.crt&sslkey=.%2fsource_certs%2fclient.root.key&sslmode=verify-full&sslrootcert=.%2fsource_certs%2fca.crt' \
--target 'postgres://root@localhost:26257/defaultdb?sslmode=verify-full' \
--table-handling 'truncate-if-exists' \
--table-filter 'employees' \
--bucket-path 'gs://migration/data/cockroach' \
--use-copy \
--cleanup \
--mode data-load-and-replication

• --source specifies the MySQL connection string and the certificates in URL-encoded format. Secure connections should be used by default. Refer to Best practices.
• --table-handling specifies that existing tables on CockroachDB should be truncated before the source data is loaded.
• --table-filter filters for tables with the employees string in the name.
• --bucket-path specifies a directory on an Google Cloud Storage bucket where intermediate files will be written.
• --use-copy specifies that COPY FROM is used to load the tables, keeping the source tables online and queryable but loading the data more slowly than IMPORT INTO.
• --cleanup specifies that the intermediate files should be removed after the source data is loaded.
• --mode data-load-and-replication starts continuous replication of data from the source database to CockroachDB after the fetch task succeeds.

If the fetch task succeeds, the output displays a fetch complete message like the following:

{"level":"info","type":"summary","fetch_id":"f5cb422f-4bb4-4bbd-b2ae-08c4d00d1e7c","num_tables":1,"tables":["public.employees"],"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"}

Continuous replication begins immediately afterward:

{"level":"info","time":"2024-05-13T14:33:07-04:00","message":"starting replicator"}

To cancel replication, enter ctrl-c to issue a SIGTERM signal.

Load CockroachDB data via direct copy

The following molt fetch command uses COPY FROM to load all tables directly from one CockroachDB database to another.

molt fetch \
--source 'postgres://root@localhost:26257/defaultdb?sslmode=disable' \
--target 'postgres://root@localhost:26258/defaultdb?sslmode=disable' \
--table-handling 'none' \
--direct-copy \
--allow-tls-mode-disable

• --source specifies sslmode=disable to establish an insecure connection. By default, insecure connections are disallowed and should be used only for testing or if a secure SSL/TLS connection to the source or target database is not possible.
• --table-handling specifies that existing tables on the target CockroachDB database should not be modified before the source data is loaded.
• --direct-copy specifies that COPY FROM is used to load the tables directly, without creating intermediate files.
• --allow-tls-mode-disable enables insecure connections to the source and target databases. Refer to Secure connections.

Continue fetch after encountering an error

If the fetch task encounters an error, it exits with an error message, fetch ID, and continuation token for each table that failed to load on the target database. You can use these values to continue the fetch task from where it was interrupted.

{"level":"info","table":"public.tbl1","file_name":"shard_01_part_00000001.csv","message":"creating or updating token for duplicate key value violates unique constraint \"tbl1_pkey\"; Key (id)=(22) already exists."}
{"level":"info","table":"public.tbl1","continuation_token":"5e7c7173-101c-4539-9b8d-28fad37d0240","message":"created continuation token"}
{"level":"info","fetch_id":"87bf8dc0-803c-4e26-89d5-3352576f92a7","message":"continue from this fetch ID"}

To retry a specific table, reissue the initial molt fetch command and include the fetch ID and a continuation token:

molt fetch \
... \
--fetch-id '87bf8dc0-803c-4e26-89d5-3352576f92a7' \
--continuation-token '5e7c7173-101c-4539-9b8d-28fad37d0240'

To retry all tables that failed, exclude --continuation-token from the command. When prompted, type y to clear all active continuation tokens. To avoid the prompt (e.g., when running molt fetch in a job), include the --non-interactive flag:

molt fetch \
... \
--fetch-id '87bf8dc0-803c-4e26-89d5-3352576f92a7' \
--non-interactive

Fail back securely from CockroachDB

The following molt fetch command uses failback mode to securely replicate changes from CockroachDB back to a MySQL database. This assumes that you migrated data from MySQL to CockroachDB, and want to keep the data consistent on MySQL in case you need to roll back the migration.

molt fetch \
--source 'postgres://root@localhost:26257/defaultdb?sslmode=verify-full' \
--target 'mysql://root:password@localhost/molt?sslcert=.%2fsource_certs%2fclient.root.crt&sslkey=.%2fsource_certs%2fclient.root.key&sslmode=verify-full&sslrootcert=.%2fsource_certs%2fca.crt' \
--table-filter 'employees, payments' \
--non-interactive \
--logging debug \
--replicator-flags "--tlsCertificate ./certs/server.crt --tlsPrivateKey ./certs/server.key" \
--mode failback \
--changefeeds-path 'changefeed-secure.json'

• --source specifies the connection string of the CockroachDB database to which you migrated.
• --target specifies the connection string of the MySQL database acting as the failback target.
• --table-filter specifies that the employees and payments tables should be watched for change events.
• --replicator-flags specifies the paths to the server certificate (--tlsCertificate) and key (--tlsPrivateKey) that correspond to the client certs defined by sink_query_parameters in the changefeed override JSON file.

• --changefeeds-path specifies the path to changefeed-secure.json, which contains the following setting override:

  {
      "sink_query_parameters": "client_cert={base64 cert}&client_key={base64 key}&ca_cert={base64 CA cert}"
  }
  

  client_cert, client_key, and ca_cert are webhook sink parameters that must be base64- and URL-encoded (for example, use the command base64 -i ./client.crt | jq -R -r '@uri').

  Tip:

  For details on the default changefeed settings and how to override them, see Changefeed override settings.

The preceding molt fetch command issues the equivalent CREATE CHANGEFEED command, using the default and explicitly overriden changefeed settings:

CREATE CHANGEFEED FOR TABLE employees, payments 
  INTO 'webhook-https://localhost:30004/defaultdb/public?client_cert={base64 cert}&client_key={base64 key}&ca_cert={base64 CA cert}' 
  WITH updated, resolved = '1s', min_checkpoint_frequency = '1s', initial_scan = 'no', cursor = '2024-09-11T16:33:35Z', webhook_sink_config = '{\"Flush\":{\"Bytes\":1048576,\"Frequency\":\"1s\"}}'

The initial output looks like the following:

INFO   [Sep 11 11:03:54] Replicator starting                           -buildmode=exe -compiler=gc CGO_CFLAGS= CGO_CPPFLAGS= CGO_CXXFLAGS= CGO_ENABLED=1 CGO_LDFLAGS= GOARCH=arm64 GOOS=darwin vcs=git vcs.modified=true vcs.revision=c948b78081a37aacf37a82eac213aa91a2828f92 vcs.time="2024-08-19T13:39:37Z"
INFO   [Sep 11 11:03:54] Server listening                              address="[::]:30004"
DEBUG  [Sep 11 11:04:00]                                               httpRequest="&{0x14000156ea0 0 401 32 101.042µs   false false}"
DEBUG  [Sep 11 11:04:00]                                               httpRequest="&{0x14000018b40 0 401 32 104.417µs   false false}"
DEBUG  [Sep 11 11:04:01]                                               httpRequest="&{0x140000190e0 0 401 32 27.958µs   false false}"

See also

• MOLT Verify
• Migration Overview
• Migrate from PostgreSQL
• Migrate from MySQL
• Migrate from CSV

Was this helpful?