> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# MOLT Fetch

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

MOLT Fetch moves data from a source database into CockroachDB as part of a <InternalLink path="migration-overview">database migration</InternalLink>. You can use MOLT Fetch to migrate data from a PostgreSQL, MySQL, or Oracle source database.

MOLT Fetch uses <InternalLink version="stable" path="import-into">`IMPORT INTO`</InternalLink> or <InternalLink version="stable" path="copy">`COPY FROM`</InternalLink> to move the source data to cloud storage (Google Cloud Storage, Amazon S3, or Azure Blob Storage), a local file server, or local memory. Once the data is exported, MOLT Fetch loads the data into a target CockroachDB database.

## Prerequisites

### Supported databases

The following source databases are supported:

* PostgreSQL 11-16
* MySQL 5.7-8.4
* Oracle Database 19c (Enterprise Edition) and 21c (Express Edition)

### Database configuration

Ensure that the source and target schemas are identical, unless you enable automatic schema creation with the <InternalLink path="molt-fetch#handle-target-tables">`drop-on-target-and-recreate`</InternalLink> option. If you are creating the target schema manually, review the behaviors in <InternalLink path="molt-fetch#mismatch-handling">Mismatch handling</InternalLink>.

<Note>
  MOLT Fetch does not support migrating sequences. If your source database contains sequences, refer to the <InternalLink version="stable" path="sql-faqs#how-do-i-generate-unique-slowly-increasing-sequential-numbers-in-cockroachdb">guidance on indexing with sequential keys</InternalLink>. If a sequential key is necessary in your CockroachDB table, you must create it manually. After using MOLT Fetch to load the data onto the target, but before cutover, make sure to update each sequence's current value using <InternalLink version="stable" path="functions-and-operators">`setval()`</InternalLink> so that new inserts continue from the correct point.
</Note>

If you plan to use cloud storage for the data migration, follow <InternalLink path="molt-fetch-best-practices#cloud-storage-security">Cloud storage security</InternalLink> best practices.

### User permissions

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

| Database           | Required Privileges                                                                                                                                                                                                                                                                                                                      | Examples                                                                                                                                 |
| ------------------ | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------- |
| PostgreSQL source  | <ul><li>`CONNECT` on database.</li><li>`USAGE` on schema.</li><li>`SELECT` on tables to migrate.</li></ul>                                                                                                                                                                                                                               | <InternalLink path="classic-bulk-load-postgres#create-migration-user-on-source-database">Create PostgreSQL migration user</InternalLink> |
| MySQL source       | <ul><li>`SELECT` on tables to migrate.</li></ul>                                                                                                                                                                                                                                                                                         | <InternalLink path="classic-bulk-load-mysql#create-migration-user-on-source-database">Create MySQL migration user</InternalLink>         |
| Oracle source      | <ul><li>`CONNECT` and `CREATE SESSION`.</li><li>`SELECT` and `FLASHBACK` on tables to migrate.</li><li>`SELECT` on metadata views (`ALL_USERS`, `DBA_USERS`, `DBA_OBJECTS`, `DBA_SYNONYMS`, `DBA_TABLES`).</li></ul>                                                                                                                     | <InternalLink path="classic-bulk-load-oracle#create-migration-user-on-source-database">Create Oracle migration user</InternalLink>       |
| CockroachDB target | <ul><li>`ALL` on target database.</li><li>`CREATE` on schema.</li><li>`SELECT`, `INSERT`, `UPDATE`, `DELETE` on target tables.</li><li>For `IMPORT INTO`: `SELECT`, `INSERT`, `DROP` on target tables. Optionally `EXTERNALIOIMPLICITACCESS` for implicit cloud storage authentication.</li><li>For `COPY FROM`: `admin` role.</li></ul> | <InternalLink path="classic-bulk-load-postgres#create-the-sql-user">Create CockroachDB user</InternalLink>                               |

### Target cluster session variable

Ensure that the `statement_timeout` <InternalLink version="stable" path="set-vars">session variable</InternalLink> is set to `0s` for the CockroachDB user on the target cluster:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER ROLE {crdb_user} SET statement_timeout = '0s';
```

## How it works

MOLT Fetch operates in two distinct phases to move data from the source databases to CockroachDB. The [data export phase](#data-export-phase) moves data to intermediate storage (either cloud storage or a local file server). The [data import phase](#data-import-phase) moves data from that intermediate storage to the CockroachDB cluster. For details on available modes, refer to [Define fetch mode](#define-fetch-mode).

<img src="https://mintcdn.com/cockroachlabs/mEy4bzaxzRbvNkMw/images/molt/molt-fetch-flow-1.png?fit=max&auto=format&n=mEy4bzaxzRbvNkMw&q=85&s=a9ca5f815a4caa80a684e1bdee2e73b3" alt="MOLT Fetch flow draft" style={{ maxWidth: "100%", display: "block", marginLeft: "auto", marginRight: "auto" }} width="4000" height="1500" data-path="images/molt/molt-fetch-flow-1.png" />

<img src="https://mintcdn.com/cockroachlabs/mEy4bzaxzRbvNkMw/images/molt/molt-fetch-flow-1.png?fit=max&auto=format&n=mEy4bzaxzRbvNkMw&q=85&s=a9ca5f815a4caa80a684e1bdee2e73b3" alt="MOLT Fetch flow" width="4000" height="1500" data-path="images/molt/molt-fetch-flow-1.png" />

### Data export phase

In this first phase, MOLT Fetch connects to the source database and exports table data to intermediate storage.

* [**Selective data movement**](#select-data-to-migrate): By default, MOLT Fetch moves all data from the --source database to CockroachDB. If instead you want to move a subset of the available data, use the <InternalLink path="molt-fetch-commands-and-flags#schema-filter">`--schema-filter`</InternalLink>, <InternalLink path="molt-fetch-commands-and-flags#table-filter">`--table-filter`</InternalLink>, and <InternalLink path="molt-fetch-commands-and-flags#schema-filter">`--filter-path`</InternalLink> flags.

* [**Table sharding for concurrent export**](#shard-tables-for-concurrent-export): Multiple tables and *table shards* can be exported simultaneously using <InternalLink path="molt-fetch-commands-and-flags#table-concurrency">`--table-concurrency`</InternalLink> and <InternalLink path="molt-fetch-commands-and-flags#export-concurrency">`--export-concurrency`</InternalLink>, with large tables divided into shards for parallel processing.

* [**Load into intermediate storage**](#define-intermediate-storage): Define whether data is written to cloud storage (Amazon S3, Google Cloud Storage, Azure Blob Storage), a local file server, or directly to CockroachDB memory. Intermediate storage enables [continuation after a MOLT Fetch failure](#continue-molt-fetch-after-interruption) by storing *continuation tokens*.

### Data import phase

MOLT Fetch loads the exported data from intermediate storage to the target CockroachDB database.

* [**`IMPORT INTO` vs. `COPY FROM`**](#import-into-vs-copy-from): This phase uses <InternalLink version="stable" path="import-into">`IMPORT INTO`</InternalLink> (faster, tables offline during import) or <InternalLink version="stable" path="copy">`COPY FROM`</InternalLink> (slower, tables remain queryable) to move data.

* [**Target table handling**](#handle-target-tables): Target tables can be automatically created, truncated, or left unchanged based on <InternalLink path="molt-fetch-commands-and-flags#table-handling">`--table-handling`</InternalLink> settings.

* [**Schema/table transformations**](#define-transformations): Use JSON to map computed columns from source to target, map partitioned tables to a single target table, rename tables on the target database, or rename database schemas.

Refer to <InternalLink path="molt-fetch-commands-and-flags#global-flags">the MOLT Fetch flags</InternalLink> to learn how to use any flag for the `molt fetch` command.

## Run MOLT Fetch

The following section describes how to use the <InternalLink path="molt-fetch-commands-and-flags#commands">`molt fetch`</InternalLink> command and how to set its main <InternalLink path="molt-fetch-commands-and-flags#global-flags">flags</InternalLink>.

### Specify source and target databases

<Tip>
  Follow the recommendations in <InternalLink path="molt-fetch-best-practices#connection-security">Connection security</InternalLink>.
</Tip>

<InternalLink path="molt-fetch-commands-and-flags#source">`--source`</InternalLink> specifies the connection string of the source database.

PostgreSQL or CockroachDB connection string:

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

MySQL connection string:

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

Oracle connection string:

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

For Oracle Multitenant databases, <InternalLink path="molt-fetch-commands-and-flags#source-cdb">`--source-cdb`</InternalLink> specifies the container database (CDB) connection. <InternalLink path="molt-fetch-commands-and-flags#source">`--source`</InternalLink> specifies the pluggable database (PDB):

```
--source 'oracle://{username}:{password}@{host}:{port}/{pdb_service_name}'
--source-cdb 'oracle://{username}:{password}@{host}:{port}/{cdb_service_name}'
```

<InternalLink path="molt-fetch-commands-and-flags#target">`--target`</InternalLink> specifies the <InternalLink version="stable" path="connection-parameters#connect-using-a-url">CockroachDB connection string</InternalLink>:

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

### Define fetch mode

<InternalLink path="molt-fetch-commands-and-flags#mode">`--mode`</InternalLink> specifies the MOLT Fetch behavior.

`data-load` (default) instructs MOLT Fetch to load the source data into CockroachDB:

```
--mode data-load
```

`export-only` instructs MOLT Fetch to export the source data to the specified [cloud storage](#bucket-path) or [local file server](#local-path). It does not load the data into CockroachDB:

```
--mode export-only
```

`import-only` instructs MOLT Fetch to load the source data in the specified [cloud storage](#bucket-path) or [local file server](#local-path) into the CockroachDB target:

```
--mode import-only
```

### Select data to migrate

By default, MOLT Fetch moves all data from the <InternalLink path="molt-fetch-commands-and-flags#source">`--source`</InternalLink> database to CockroachDB. Use the following flags to move a subset of data.

#### Schema and table selection

<InternalLink path="molt-fetch-commands-and-flags#schema-filter">`--schema-filter`</InternalLink> 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 `migration_schema` schema:

```
--schema-filter 'migration_schema'
```

<Note>
  <InternalLink path="molt-fetch-commands-and-flags#schema-filter">`--schema-filter`</InternalLink> does not apply to MySQL sources because MySQL tables belong directly to the database specified in the connection string, not to a separate schema.
</Note>

<InternalLink path="molt-fetch-commands-and-flags#table-filter">`--table-filter`</InternalLink> and <InternalLink path="molt-fetch-commands-and-flags#table-exclusion-filter">`--table-exclusion-filter`</InternalLink> 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.*'
```

To filter tables during replication, use <InternalLink path="molt-replicator">MOLT Replicator</InternalLink> with <InternalLink path="userscript-cookbook#filter-a-single-table">userscripts</InternalLink>.

#### Row-level filtering

Use <InternalLink path="molt-fetch-commands-and-flags#filter-path">`--filter-path`</InternalLink> to specify the path to a JSON file that defines row-level filtering for data load. This enables you to move a subset of data in a table, rather than all data in the table. To apply row-level filters during replication, use <InternalLink path="molt-replicator">MOLT Replicator</InternalLink> with <InternalLink path="userscript-cookbook#select-data-to-replicate">userscripts</InternalLink>.

```
--filter-path 'data-filter.json'
```

The JSON file should contain one or more entries in `filters`, each with a `resource_specifier` (`schema` and `table`) and a SQL expression `expr`. For example, the following example exports only rows from `migration_schema.t1` where `v > 100`:

```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
{
  "filters": [
    {
      "resource_specifier": {
        "schema": "migration_schema",
        "table": "t1"
      },
      "expr": "v > 100"
    }
  ]
}
```

`expr` is case-sensitive and must be valid in your source dialect. For example, when using Oracle as the source, quote all identifiers and escape embedded quotes:

```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
{
  "filters": [
    {
      "resource_specifier": {
        "schema": "C##FETCHORACLEFILTERTEST",
        "table": "FILTERTBL"
      },
      "expr": "ABS(\"X\") > 10 AND CEIL(\"X\") < 100 AND FLOOR(\"X\") > 0 AND ROUND(\"X\", 2) < 100.00 AND TRUNC(\"X\", 0) > 0 AND MOD(\"X\", 2) = 0 AND FLOOR(\"X\" / 3) > 1"
    }
  ]
}
```

<Note>
  If the expression references columns that are not indexed, MOLT Fetch will emit a warning like: `filter expression 'v > 100' contains column 'v' which is not indexed. This may lead to performance issues.`
</Note>

### Shard tables for concurrent export

During the [data export phase](#data-export-phase), MOLT Fetch can divide large tables into multiple shards for concurrent export.

To control the number of shards created per table, use the <InternalLink path="molt-fetch-commands-and-flags#export-concurrency">`--export-concurrency`</InternalLink> flag. For example:

```
--export-concurrency=4
```

<Tip>
  For performance considerations with concurrency settings, refer to <InternalLink path="molt-fetch-best-practices">Best practices</InternalLink>.
</Tip>

Two sharding mechanisms are available:

* **Range-based sharding (default):** Tables are divided based on numerical ranges found in primary key values. Only tables with <InternalLink version="stable" path="int">`INT`</InternalLink>, <InternalLink version="stable" path="float">`FLOAT`</InternalLink>, or <InternalLink version="stable" path="uuid">`UUID`</InternalLink> primary keys can use range-based sharding. Tables with other primary key data types export as a single shard.

* **Stats-based sharding (PostgreSQL only):** Enable with <InternalLink path="molt-fetch-commands-and-flags#use-stats-based-sharding">`--use-stats-based-sharding`</InternalLink> for PostgreSQL 11+ sources. Tables are divided by analyzing the [`pg_stats`](https://www.postgresql.org/docs/current/view-pg-stats.htm) view to create more evenly distributed shards, up to a maximum of 200 shards. Primary keys of any data type are supported.

Stats-based sharding requires that the user has `SELECT` permissions on source tables and on each table's `pg_stats` view. The latter permission is automatically granted to users that can read the table.

To optimize stats-based sharding, run [`ANALYZE`](https://www.postgresql.org/docs/current/sql-analyze.html) on source tables before migration to ensure that table statistics are up-to-date and shards are evenly distributed. This requires `MAINTAIN` or `OWNER` permissions on the table. You can analyze specific primary key columns or the entire table. For example:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ANALYZE table_name(PK1, PK2, PK3);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ANALYZE table_name;
```

Large tables may take time to analyze, but `ANALYZE` can run in the background. You can run `ANALYZE` with `MAINTAIN` or `OWNER` privileges during migration preparation, then perform the actual migration with standard `SELECT` privileges.

<Note>
  Migration without running `ANALYZE` will still work, but shard distribution may be less even.
</Note>

When using <InternalLink path="molt-fetch-commands-and-flags#use-stats-based-sharding">`--use-stats-based-sharding`</InternalLink>, monitor the log output for each table you want to migrate.

If stats-based sharding is successful on a table, MOLT logs the following `INFO` message:

```
Stats based sharding enabled for table {table_name}
```

If stats-based sharding fails on a table, MOLT logs the following `WARNING` message and defaults to range-based sharding:

```
Warning: failed to shard table {table_name} using stats based sharding: {reason_for_failure}, falling back to non stats based sharding
```

The number of shards is dependent on the number of distinct values in the first primary key column of the table to be migrated. If this is different from the number of shards requested with <InternalLink path="molt-fetch-commands-and-flags#export-concurrency">`--export-concurrency`</InternalLink>, MOLT logs the following `WARNING` and continues with the migration:

```
number of shards formed: {num_shards_formed} is not equal to number of shards requested: {num_shards_requested} for table {table_name}
```

Because stats-based sharding analyzes the entire table, running <InternalLink path="molt-fetch-commands-and-flags#use-stats-based-sharding">`--use-stats-based-sharding`</InternalLink> with <InternalLink path="molt-fetch-commands-and-flags#filter-path">`--filter-path`</InternalLink> (refer to [Selective data movement](#select-data-to-migrate)) will cause imbalanced shards to form.

### Define intermediate storage

MOLT Fetch can move the source data to CockroachDB via [cloud storage](#bucket-path), a [local file server](#local-path), or [directly](#direct-copy) without an intermediate store.

#### Bucket path

<Tip>
  Only the path specified in <InternalLink path="molt-fetch-commands-and-flags#bucket-path">`--bucket-path`</InternalLink> is used. Query parameters, such as credentials, are ignored. To authenticate cloud storage, follow the steps in <InternalLink path="molt-fetch-best-practices#cloud-storage-security">Secure cloud storage</InternalLink>.
</Tip>

<InternalLink path="molt-fetch-commands-and-flags#bucket-path">`--bucket-path`</InternalLink> instructs MOLT Fetch to write intermediate files to a path within [Google Cloud Storage](https://cloud.google.com/storage/docs/buckets), [Amazon S3](https://aws.amazon.com/s3/), or [Azure Blob Storage](https://azure.microsoft.com/en-us/products/storage/blobs) to which you have the necessary permissions. Use additional <InternalLink path="molt-fetch-commands-and-flags#global-flags">flags</InternalLink>, shown in the following examples, to specify authentication or region parameters as required for bucket access.

Connect to a Google Cloud Storage bucket with <InternalLink version="stable" path="cloud-storage-authentication#google-cloud-storage-implicit">implicit authentication</InternalLink> and <InternalLink version="stable" path="cloud-storage-authentication#set-up-google-cloud-storage-assume-role">assume role</InternalLink>:

```
--bucket-path 'gs://migration/data/cockroach'
--assume-role 'user-test@cluster-ephemeral.iam.gserviceaccount.com'
--use-implicit-auth
```

Connect to an Amazon S3 bucket and explicitly specify the `ap_south-1` region:

```
--bucket-path 's3://migration/data/cockroach'
--import-region 'ap-south-1'
```

<Note>
  When <InternalLink path="molt-fetch-commands-and-flags#import-region">`--import-region`</InternalLink> is set, `IMPORT INTO` must be used for [data movement](#import-into-vs-copy-from).
</Note>

Connect to an Azure Blob Storage container with <InternalLink version="stable" path="cloud-storage-authentication?filters=azure#azure-blob-storage-implicit-authentication">implicit authentication</InternalLink>:

```
--bucket-path 'azure-blob://migration/data/cockroach'
--use-implicit-auth
```

#### Local path

<InternalLink path="molt-fetch-commands-and-flags#local-path">`--local-path`</InternalLink> instructs MOLT Fetch to write intermediate files to a path within a <InternalLink version="stable" path="use-a-local-file-server">local file server</InternalLink>. <InternalLink path="molt-fetch-commands-and-flags#local-path-listen-addr">`--local-path-listen-addr`</InternalLink> 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 <InternalLink path="molt-fetch-commands-and-flags#local-path-listen-addr">`--local-path-listen-addr`</InternalLink>. 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 <InternalLink path="molt-fetch-commands-and-flags#local-path-crdb-access-addr">`--local-path-crdb-access-addr`</InternalLink> 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'
```

<Tip>
  [Cloud storage](#bucket-path) is often preferable to a local file server, which can require considerable disk space.
</Tip>

#### Direct copy

<InternalLink path="molt-fetch-commands-and-flags#direct-copy">`--direct-copy`</InternalLink> specifies that MOLT Fetch should use `COPY FROM` to move the source data directly to CockroachDB without an intermediate store:

* Direct copy does not support compression or [continuation](#continue-molt-fetch-after-interruption).
* The [`--use-copy`](#import-into-vs-copy-from) flag is redundant with <InternalLink path="molt-fetch-commands-and-flags#direct-copy">`--direct-copy`</InternalLink>.

### `IMPORT INTO` vs. `COPY FROM`

MOLT Fetch can use either <InternalLink version="stable" path="import-into">`IMPORT INTO`</InternalLink> or <InternalLink version="stable" path="copy">`COPY FROM`</InternalLink> to load data into CockroachDB.

By default, MOLT Fetch uses `IMPORT INTO`:

* `IMPORT INTO` achieves the highest throughput, but <InternalLink version="stable" path="import-into#considerations">requires taking the CockroachDB tables **offline**</InternalLink> to achieve its import speed. Tables are taken back online once an <InternalLink version="stable" path="import-into#view-and-control-import-jobs">import job</InternalLink> completes successfully. See <InternalLink path="molt-fetch-best-practices">Best practices</InternalLink>.
* `IMPORT INTO` supports compression using the <InternalLink path="molt-fetch-commands-and-flags#compression">`--compression`</InternalLink> flag, which reduces the amount of storage used.

<InternalLink path="molt-fetch-commands-and-flags#use-copy">`--use-copy`</InternalLink> configures MOLT Fetch to use `COPY FROM`:

* `COPY FROM` enables your tables to remain online and accessible. However, it is slower than using <InternalLink version="stable" path="import-into">`IMPORT INTO`</InternalLink>.
* `COPY FROM` does not support compression.

<Note>
  `COPY FROM` is also used for [direct copy](#direct-copy).
</Note>

### Handle target tables

<InternalLink path="molt-fetch-commands-and-flags#table-handling">`--table-handling`</InternalLink> defines how MOLT Fetch loads data on the CockroachDB tables that [match the selection](#schema-and-table-selection).

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

```
--table-handling none
```

To <InternalLink version="stable" path="truncate">truncate</InternalLink> 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](#type-mapping). `drop-on-target-and-recreate` does **not** create indexes or constraints other than <InternalLink version="stable" path="primary-key">`PRIMARY KEY`</InternalLink> and <InternalLink version="stable" path="not-null">`NOT NULL`</InternalLink>.

#### Mismatch handling

If either [`none`](#handle-target-tables) or [`truncate-if-exists`](#handle-target-tables) 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. For details, refer to <InternalLink path="molt-fetch-troubleshooting#fetch-exits-early-due-to-mismatches">Fetch exits early due to mismatches</InternalLink>.

<Note>
  This does not apply when [`drop-on-target-and-recreate`](#handle-target-tables) is specified, since this option automatically creates a compatible CockroachDB schema.
</Note>

#### Skip primary key matching

<InternalLink path="molt-fetch-commands-and-flags#skip-pk-check">`--skip-pk-check`</InternalLink> removes the <InternalLink path="molt-fetch-troubleshooting#fetch-exits-early-due-to-mismatches">requirement that source and target tables share matching primary keys</InternalLink> for data load. When this flag is set:

* The data load proceeds even if the source or target table lacks a primary key, or if their primary key columns do not match.
* [Table sharding](#shard-tables-for-concurrent-export) is disabled. Each table is exported in a single batch within one shard, bypassing <InternalLink path="molt-fetch-commands-and-flags#export-concurrency">`--export-concurrency`</InternalLink> and <InternalLink path="molt-fetch-commands-and-flags#row-batch-size">`--row-batch-size`</InternalLink>. As a result, memory usage and execution time may increase due to full table scans.
* If the source table contains duplicate rows but the target has <InternalLink version="stable" path="primary-key">`PRIMARY KEY`</InternalLink> or <InternalLink version="stable" path="unique">`UNIQUE`</InternalLink> constraints, duplicate rows are deduplicated during import.

When <InternalLink path="molt-fetch-commands-and-flags#skip-pk-check">`--skip-pk-check`</InternalLink> is set, all tables are treated as if they lack a primary key, and are thus exported in a single unsharded batch. To avoid performance issues, use this flag with <InternalLink path="molt-fetch-commands-and-flags#table-filter">`--table-filter`</InternalLink> to target only tables **without** a primary key.

For example:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
molt fetch \
  --mode data-load \
  --table-filter 'nopktbl' \
  --skip-pk-check
```

Example log output when <InternalLink path="molt-fetch-commands-and-flags#skip-pk-check">`--skip-pk-check`</InternalLink> is enabled:

```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
{"level":"info","message":"sharding is skipped for table public.nopktbl - flag skip-pk-check is specified and thus no PK for source table is specified"}
```

#### Type mapping

If [`drop-on-target-and-recreate`](#handle-target-tables) 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 <InternalLink version="stable" path="data-types">types</InternalLink> that have the same <InternalLink version="stable" path="oid">`OID`</InternalLink>.

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

  | MySQL type                                          | CockroachDB type                                                                               | Notes                                                        |
  | --------------------------------------------------- | ---------------------------------------------------------------------------------------------- | ------------------------------------------------------------ |
  | `CHAR`, `CHARACTER`, `VARCHAR`, `NCHAR`, `NVARCHAR` | <InternalLink version="stable" path="string">`VARCHAR`</InternalLink>                          | Varying-length string; raises warning if BYTE semantics used |
  | `TINYTEXT`, `TEXT`, `MEDIUMTEXT`, `LONGTEXT`        | <InternalLink version="stable" path="string">`STRING`</InternalLink>                           | Unlimited-length string                                      |
  | `GEOMETRY`                                          | <InternalLink version="stable" path="architecture/glossary#geometry">`GEOMETRY`</InternalLink> | Spatial type (PostGIS-style)                                 |
  | `LINESTRING`                                        | <InternalLink version="stable" path="linestring">`LINESTRING`</InternalLink>                   | Spatial type (PostGIS-style)                                 |
  | `POINT`                                             | <InternalLink version="stable" path="point">`POINT`</InternalLink>                             | Spatial type (PostGIS-style)                                 |
  | `POLYGON`                                           | <InternalLink version="stable" path="polygon">`POLYGON`</InternalLink>                         | Spatial type (PostGIS-style)                                 |
  | `MULTIPOINT`                                        | <InternalLink version="stable" path="multipoint">`MULTIPOINT`</InternalLink>                   | Spatial type (PostGIS-style)                                 |
  | `MULTILINESTRING`                                   | <InternalLink version="stable" path="multilinestring">`MULTILINESTRING`</InternalLink>         | Spatial type (PostGIS-style)                                 |
  | `MULTIPOLYGON`                                      | <InternalLink version="stable" path="multipolygon">`MULTIPOLYGON`</InternalLink>               | Spatial type (PostGIS-style)                                 |
  | `GEOMETRYCOLLECTION`, `GEOMCOLLECTION`              | <InternalLink version="stable" path="geometrycollection">`GEOMETRYCOLLECTION`</InternalLink>   | Spatial type (PostGIS-style)                                 |
  | `JSON`                                              | <InternalLink version="stable" path="jsonb">`JSONB`</InternalLink>                             | CRDB's native JSON format                                    |
  | `TINYINT`, `INT1`                                   | <InternalLink version="stable" path="int">`INT2`</InternalLink>                                | 2-byte integer                                               |
  | `BLOB`                                              | <InternalLink version="stable" path="bytes">`BYTES`</InternalLink>                             | Binary data                                                  |
  | `SMALLINT`, `INT2`                                  | <InternalLink version="stable" path="int">`INT2`</InternalLink>                                | 2-byte integer                                               |
  | `MEDIUMINT`, `INT`, `INTEGER`, `INT4`               | <InternalLink version="stable" path="int">`INT4`</InternalLink>                                | 4-byte integer                                               |
  | `BIGINT`, `INT8`                                    | <InternalLink version="stable" path="int">`INT`</InternalLink>                                 | 8-byte integer                                               |
  | `FLOAT`                                             | <InternalLink version="stable" path="float">`FLOAT4`</InternalLink>                            | 32-bit float                                                 |
  | `DOUBLE`                                            | <InternalLink version="stable" path="float">`FLOAT`</InternalLink>                             | 64-bit float                                                 |
  | `DECIMAL`, `NUMERIC`, `REAL`                        | <InternalLink version="stable" path="decimal">`DECIMAL`</InternalLink>                         | Validates scale ≤ precision; warns if precision > 19         |
  | `BINARY`, `VARBINARY`                               | <InternalLink version="stable" path="bytes">`BYTES`</InternalLink>                             | Binary data                                                  |
  | `DATETIME`                                          | <InternalLink version="stable" path="timestamp">`TIMESTAMP`</InternalLink>                     | Date and time (no time zone)                                 |
  | `TIMESTAMP`                                         | <InternalLink version="stable" path="timestamp">`TIMESTAMPTZ`</InternalLink>                   | Date and time with time zone                                 |
  | `TIME`                                              | <InternalLink version="stable" path="time">`TIME`</InternalLink>                               | Time of day (no date)                                        |
  | `BIT`                                               | <InternalLink version="stable" path="bit">`VARBIT`</InternalLink>                              | Variable-length bit array                                    |
  | `DATE`                                              | <InternalLink version="stable" path="date">`DATE`</InternalLink>                               | Date only (no time)                                          |
  | `TINYBLOB`, `MEDIUMBLOB`, `LONGBLOB`                | <InternalLink version="stable" path="bytes">`BYTES`</InternalLink>                             | Binary data                                                  |
  | `BOOL`, `BOOLEAN`                                   | <InternalLink version="stable" path="bool">`BOOL`</InternalLink>                               | Boolean                                                      |

* The following Oracle types are mapped to CockroachDB types:

  | Oracle type(s)                        | CockroachDB type                                                                                                                                           | Notes                                                                   |
  | ------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------- |
  | `NCHAR`, `CHAR`, `CHARACTER`          | <InternalLink version="stable" path="string">`CHAR`</InternalLink>(n) or <InternalLink version="stable" path="string">`CHAR`</InternalLink>                | Fixed-length character; falls back to unbounded if length not specified |
  | `VARCHAR`, `VARCHAR2`, `NVARCHAR2`    | <InternalLink version="stable" path="string">`VARCHAR`</InternalLink>(n) or <InternalLink version="stable" path="string">`VARCHAR`</InternalLink>          | Varying-length string; raises warning if BYTE semantics used            |
  | `STRING`                              | <InternalLink version="stable" path="string">`STRING`</InternalLink>                                                                                       | Unlimited-length string                                                 |
  | `SMALLINT`                            | <InternalLink version="stable" path="int">`INT2`</InternalLink>                                                                                            | 2-byte integer                                                          |
  | `INTEGER`, `INT`, `SIMPLE_INTEGER`    | <InternalLink version="stable" path="int">`INT4`</InternalLink>                                                                                            | 4-byte integer                                                          |
  | `LONG`                                | <InternalLink version="stable" path="int">`INT8`</InternalLink>                                                                                            | 8-byte integer                                                          |
  | `FLOAT`, `BINARY_FLOAT`, `REAL`       | <InternalLink version="stable" path="float">`FLOAT4`</InternalLink>                                                                                        | 32-bit float                                                            |
  | `DOUBLE`, `BINARY_DOUBLE`             | <InternalLink version="stable" path="float">`FLOAT8`</InternalLink>                                                                                        | 64-bit float                                                            |
  | `DEC`, `NUMBER`, `DECIMAL`, `NUMERIC` | <InternalLink version="stable" path="decimal">`DECIMAL`</InternalLink>(p, s) or <InternalLink version="stable" path="decimal">`DECIMAL`</InternalLink>     | Validates scale ≤ precision; warns if precision > 19                    |
  | `DATE`                                | <InternalLink version="stable" path="date">`DATE`</InternalLink>                                                                                           | Date only (no time)                                                     |
  | `BLOB`, `RAW`, `LONG RAW`             | <InternalLink version="stable" path="bytes">`BYTES`</InternalLink>                                                                                         | Binary data                                                             |
  | `JSON`                                | <InternalLink version="stable" path="jsonb">`JSONB`</InternalLink>                                                                                         | CRDB's native JSON format                                               |
  | `CLOB`, `NCLOB`                       | <InternalLink version="stable" path="string">`STRING`</InternalLink>                                                                                       | Treated as large text                                                   |
  | `BOOLEAN`                             | <InternalLink version="stable" path="bool">`BOOL`</InternalLink>                                                                                           | Boolean                                                                 |
  | `TIMESTAMP`                           | <InternalLink version="stable" path="timestamp">`TIMESTAMP`</InternalLink> or <InternalLink version="stable" path="timestamp">`TIMESTAMPTZ`</InternalLink> | If `WITH TIME ZONE` → `TIMESTAMPTZ`, else `TIMESTAMP`                   |
  | `ROWID`, `UROWID`                     | <InternalLink version="stable" path="string">`STRING`</InternalLink>                                                                                       | Treated as opaque identifier                                            |
  | `SDO_GEOMETRY`                        | <InternalLink version="stable" path="architecture/glossary#geometry">`GEOMETRY`</InternalLink>                                                             | Spatial type (PostGIS-style)                                            |
  | `XMLTYPE`                             | <InternalLink version="stable" path="string">`STRING`</InternalLink>                                                                                       | Stored as text                                                          |

* 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 <InternalLink path="molt-fetch-commands-and-flags#type-map-file">`--type-map-file`</InternalLink> flag. The allowable custom mappings are valid CockroachDB aliases, casts, and the following mappings specific to MOLT Fetch and <InternalLink path="molt-verify">Verify</InternalLink>:

  * <InternalLink version="stable" path="timestamp">`TIMESTAMP`</InternalLink> \<> <InternalLink version="stable" path="timestamp">`TIMESTAMPTZ`</InternalLink>
  * <InternalLink version="stable" path="string">`VARCHAR`</InternalLink> \<> <InternalLink version="stable" path="uuid">`UUID`</InternalLink>
  * <InternalLink version="stable" path="bool">`BOOL`</InternalLink> \<> <InternalLink version="stable" path="int">`INT2`</InternalLink>
  * <InternalLink version="stable" path="bit">`VARBIT`</InternalLink> \<> <InternalLink version="stable" path="string">`TEXT`</InternalLink>
  * <InternalLink version="stable" path="bit">`VARBIT`</InternalLink> \<> <InternalLink version="stable" path="bool">`BOOL`</InternalLink>
  * <InternalLink version="stable" path="jsonb">`JSONB`</InternalLink> \<> <InternalLink version="stable" path="string">`TEXT`</InternalLink>
  * <InternalLink version="stable" path="inet">`INET`</InternalLink> \<> <InternalLink version="stable" path="string">`TEXT`</InternalLink>

<InternalLink path="molt-fetch-commands-and-flags#type-map-file">`--type-map-file`</InternalLink> 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:

```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
 [
  {
    "table": "public.t1",
    "column_type_map": [
      {
        "column": "*",
        "source_type": "int",
        "crdb_type": "INT2"
      },
      {
        "column": "name",
        "source_type": "varbit",
        "crdb_type": "string"
      }
    ]
  }
]
```

* `table` specifies the table that will use the custom type mappings in `column_type_map`. The value is written as `{schema}.{table}`.
* `column` specifies the column that will use the custom type mapping. If `*` is specified, then all columns in the `table` with the matching `source_type` are converted.
* `source_type` specifies the source type to be mapped.
* `crdb_type` specifies the target CockroachDB <InternalLink version="stable" path="data-types">type</InternalLink> to be mapped.

### Define transformations

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

* Map <InternalLink version="stable" path="computed-columns">computed columns</InternalLink> from source to target.
* Map partitioned tables to a single target table.
* Rename tables on the target database.
* Rename database schemas.

Transformation rules are defined in the JSON file indicated by the <InternalLink path="molt-fetch-commands-and-flags#transformations-file">`--transformations-file`</InternalLink> flag. For example:

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

#### Transformation rules example

The following JSON example defines three transformation rules: rule `1` [maps computed columns](#column-exclusions-and-computed-columns), rule `2` [renames tables](#table-renaming), and rule `3` [renames schemas](#schema-renaming).

```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
{
  "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"
      }
    },
    {
      "id": 3,
      "resource_specifier": {
        "schema": "previous_schema"
      },
      "schema_rename_opts": {
        "value": "new_schema"
      }
    }
  ]
}
```

#### Column exclusions and computed columns

* `resource_specifier`: Identifies which schemas and tables to transform.
  * `schema`: POSIX regex matching source schemas.
  * `table`: POSIX regex matching source tables.
* `column_exclusion_opts`: Exclude columns or map them as computed columns.
  * `column`: POSIX regex matching source columns to exclude.
  * `add_computed_def`: When `true`, map matching columns as <InternalLink version="stable" path="computed-columns">computed columns</InternalLink> on target tables using <InternalLink version="stable" path="alter-table#add-column">`ALTER TABLE ... ADD COLUMN`</InternalLink> and the source column definition. All matching columns must be computed columns on the source.
    <Danger>
      Columns matching `column` are **not** moved to CockroachDB if `add_computed_def` is `false` (default) or if matching columns are not computed columns.
    </Danger>

[Example rule `1`](#transformation-rules-example) maps all source `age` columns to <InternalLink version="stable" path="computed-columns">computed columns</InternalLink> on CockroachDB. This assumes that all matching `age` columns are defined as computed columns on the source:

```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
{
  "id": 1,
  "resource_specifier": {
    "schema": ".*",
    "table": ".*"
  },
  "column_exclusion_opts": {
    "add_computed_def": true,
    "column": "^age$"
  }
},
```

#### Table renaming

* `resource_specifier`: Identifies which schemas and tables to transform.
  * `schema`: POSIX regex matching source schemas.
  * `table`: POSIX regex matching source tables.
* `table_rename_opts`: Rename tables on the target.
  * `value`: Target table name. For a single matching source table, renames it to this value. For multiple matches (n-to-1), consolidates matching partitioned tables with the same table definition into a single table with this name.

    For n-to-1 mappings:

    * Use [`--use-copy`](#import-into-vs-copy-from) or [`--direct-copy`](#direct-copy) for data movement.
    * Manually create the target table. Do not use [`--table-handling drop-on-target-and-recreate`](#handle-target-tables).

[Example rule `2`](#transformation-rules-example) maps all table names with prefix `charges_part` to a single `charges` table on CockroachDB (an n-to-1 mapping). This assumes that all matching `charges_part.*` tables have the same table definition:

```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
{
  "id": 2,
  "resource_specifier": {
    "schema": "public",
    "table": "charges_part.*"
  },
  "table_rename_opts": {
    "value": "charges"
  }
},
```

#### Schema renaming

* `resource_specifier`: Identifies which schemas and tables to transform.
  * `schema`: POSIX regex matching source schemas.
  * `table`: POSIX regex matching source tables.
* `schema_rename_opts`: Rename database schemas on the target.
  * `value`: Target schema name. For example, `previous_schema.table1` becomes `new_schema.table1`.

[Example rule `3`](#transformation-rules-example) renames the database schema `previous_schema` to `new_schema` on CockroachDB:

```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
{
  "id": 3,
  "resource_specifier": {
    "schema": "previous_schema"
  },
  "schema_rename_opts": {
    "value": "new_schema"
  }
}
```

#### General notes

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 <InternalLink path="molt-fetch-commands-and-flags#logging">`--logging`</InternalLink> `debug` and look for `ALTER TABLE ... ADD COLUMN` statements with the `STORED` or `VIRTUAL` keywords in the log output:

```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
{"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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW CREATE TABLE computed;
```

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

## Continue MOLT Fetch after interruption

If MOLT Fetch fails while loading data into CockroachDB from intermediate files, it exits with an error message, fetch ID, and [continuation token](#list-active-continuation-tokens) 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.

Continuation is only possible under the following conditions:

* All data has been exported from the source database into intermediate files on [cloud](#bucket-path) or [local storage](#local-path).
* The *initial load* of source data into the target CockroachDB database is incomplete.

<Note>
  Only one fetch ID and set of continuation tokens, each token corresponding to a table, are active at any time. See [List active continuation tokens](#list-active-continuation-tokens).
</Note>

To retry all data starting from the continuation point, reissue the `molt fetch` command and include the <InternalLink path="molt-fetch-commands-and-flags#fetch-id">`--fetch-id`</InternalLink>.

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

To retry a specific table that failed, include both <InternalLink path="molt-fetch-commands-and-flags#fetch-id">`--fetch-id`</InternalLink> and <InternalLink path="molt-fetch-commands-and-flags#continuation-token">`--continuation-token`</InternalLink>. 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](#list-active-continuation-tokens).

<Note>
  This will retry only the table that corresponds to the continuation token. If the fetch task succeeds, there may still be source data that is not yet loaded into CockroachDB.
</Note>

```
--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 <InternalLink path="molt-fetch-commands-and-flags#fetch-id">`--fetch-id`</InternalLink> and <InternalLink path="molt-fetch-commands-and-flags#continuation-file-name">`--continuation-file-name`</InternalLink>. The latter flag specifies the filename of an intermediate file in [cloud or local storage](#define-intermediate-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
```

<Note>
  Continuation is not possible when using [direct copy](#direct-copy).
</Note>

### List active continuation tokens

To view all active continuation tokens, issue a `molt fetch tokens list` command along with <InternalLink path="molt-fetch-commands-and-flags#conn-string">`--conn-string`</InternalLink>, which specifies the <InternalLink version="stable" path="connection-parameters#connect-using-a-url">connection string</InternalLink> for the target CockroachDB database. For example:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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.
```

## Enable replication

A change data capture (CDC) cursor is written to the MOLT Fetch output as `cdc_cursor` at the beginning and end of the fetch task.

For MySQL:

```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
{"level":"info","type":"summary","fetch_id":"735a4fe0-c478-4de7-a342-cfa9738783dc","num_tables":1,"tables":["public.employees"],"skipped_unmigratable_tables":[],"cdc_cursor":"b7f9e0fa-2753-1e1f-5d9b-2402ac810003:3-21","net_duration_ms":4879.890041,"net_duration":"000h 00m 04s","time":"2024-03-18T12:37:02-04:00","message":"fetch complete"}
```

For Oracle:

```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
{"level":"info","type":"summary","fetch_id":"735a4fe0-c478-4de7-a342-cfa9738783dc","num_tables":3,"tables":["migration_schema.employees"],"skipped_unmigratable_tables":[],"cdc_cursor":"backfillFromSCN=26685444,scn=26685786","net_duration_ms":6752.847625,"net_duration":"000h 00m 06s","time":"2024-03-18T12:37:02-04:00","message":"fetch complete"}
```

This `cdc_cursor` value is also included in the output of a fetch task from a PostgreSQL source. However, in the case of a PostgreSQL source, you can instead enable replication with the <InternalLink path="molt-fetch-commands-and-flags#pglogical-replication-slot-name">`--pglogical-replication-slot-name`</InternalLink> and <InternalLink path="molt-fetch-commands-and-flags#pglogical-publication-and-slot-drop-and-recreate">`--pglogical-publication-and-slot-drop-and-recreate`</InternalLink> flags, which must be defined.

Use the `cdc_cursor` value as the checkpoint for MySQL or Oracle replication with MOLT Replicator. Use the <InternalLink path="molt-fetch-commands-and-flags#pglogical-replication-slot-name">`--pglogical-replication-slot-name`</InternalLink> value as the checkpoint for PostgreSQL replication with MOLT Replicator. Refer to <InternalLink path="molt-replicator#replication-checkpoints">Replication checkpoints</InternalLink> in the MOLT Replicator documentation.

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

## Docker usage

### Authentication

When using MOLT Fetch with [cloud storage](#bucket-path), it is necessary to specify volumes and environment variables, as described in the following sections for [Google Cloud Storage](#google-cloud-storage) and [Amazon S3](#amazon-s3).

No additional configuration is needed when running MOLT Fetch with a [local file server](#local-path) or in [direct copy mode](#direct-copy):

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
docker run -it cockroachdb/molt fetch ...
```

For more information on `docker run`, see the [Docker documentation](https://docs.docker.com/reference/cli/docker/container/run/).

#### Google Cloud Storage

If you are using [Google Cloud Storage](https://cloud.google.com/storage/docs/access-control) for [cloud storage](#bucket-path):

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

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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](https://cloud.google.com/sdk/docs/configurations) into the container. In addition to setting the `GOOGLE_APPLICATION_CREDENTIALS` environment variable, set `CLOUDSDK_CONFIG` to point to the configuration directory:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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](https://cloud.google.com/docs/authentication/application-default-credentials).

#### Amazon S3

If you are using [Amazon S3](https://docs.aws.amazon.com/AmazonS3/latest/userguide/security-iam.html) for [cloud storage](#bucket-path):

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:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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/migration_db?sslmode=disable'
  --target "postgres://root@host.docker.internal:26257/defaultdb?sslmode=disable"
  ```

* For Linux and Windows, use `172.17.0.1`. For example:

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

## Common uses

### Bulk data load

When migrating data to CockroachDB in a bulk load (without utilizing <InternalLink path="migration-considerations-replication">continuous replication</InternalLink> to minimize system downtime), run the `molt fetch` command with the required flags, as shown below:

Specify the source and target database connections. For connection string formats, refer to [Source and target databases](#specify-source-and-target-databases).

<Tabs>
  <Tab title="PostgreSQL">
    ```
    --source $SOURCE
    --target $TARGET
    ```

    Specify how to move data to CockroachDB. Use [cloud storage](#bucket-path) for intermediate file storage:

    ```
    --bucket-path 's3://bucket/path'
    ```

    Alternatively, use a [local file server](#local-path) for intermediate storage:

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

    Alternatively, use [direct copy](#direct-copy) to move data directly without intermediate storage:

    ```
    --direct-copy
    ```

    Optionally, filter the source data to migrate. By default, all schemas and tables are migrated. For details, refer to [Schema and table selection](#schema-and-table-selection).

    ```
    --schema-filter 'migration_schema'
    --table-filter '.*user.*'
    ```
  </Tab>

  <Tab title="MySQL">
    ```
    --source $SOURCE
    --target $TARGET
    ```

    Specify how to move data to CockroachDB. Use [cloud storage](#bucket-path) for intermediate file storage:

    ```
    --bucket-path 's3://bucket/path'
    ```

    Alternatively, use a [local file server](#local-path) for intermediate storage:

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

    Alternatively, use [direct copy](#direct-copy) to move data directly without intermediate storage:

    ```
    --direct-copy
    ```

    Optionally, filter the source data to migrate. By default, all schemas and tables are migrated. For details, refer to [Schema and table selection](#schema-and-table-selection).

    For MySQL sources, omit <InternalLink path="molt-fetch-commands-and-flags#schema-filter">`--schema-filter`</InternalLink> because MySQL tables belong directly to the database specified in the connection string, not to a separate schema. If needed, use <InternalLink path="molt-fetch-commands-and-flags#table-filter">`--table-filter`</InternalLink> to select specific tables:

    ```
    --table-filter '.*user.*'
    ```
  </Tab>

  <Tab title="Oracle">
    For Oracle Multitenant (CDB/PDB) sources, also include <InternalLink path="molt-fetch-commands-and-flags#source-cdb">`--source-cdb`</InternalLink> to specify the container database (CDB) connection string.

    ```
    --source $SOURCE
    --source-cdb $SOURCE_CDB
    --target $TARGET
    ```

    Specify how to move data to CockroachDB. Use [cloud storage](#bucket-path) for intermediate file storage:

    ```
    --bucket-path 's3://bucket/path'
    ```

    Alternatively, use a [local file server](#local-path) for intermediate storage:

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

    Alternatively, use [direct copy](#direct-copy) to move data directly without intermediate storage:

    ```
    --direct-copy
    ```

    Optionally, filter the source data to migrate. By default, all schemas and tables are migrated. For details, refer to [Schema and table selection](#schema-and-table-selection).

    For Oracle sources, <InternalLink path="molt-fetch-commands-and-flags#schema-filter">`--schema-filter`</InternalLink> is case-insensitive. You can use either lowercase or uppercase:

    ```
    --schema-filter 'migration_schema'
    --table-filter '.*user.*'
    ```
  </Tab>
</Tabs>

Specify how to handle target tables. By default, <InternalLink path="molt-fetch-commands-and-flags#table-handling">`--table-handling`</InternalLink> is set to `none`, which loads data without changing existing data in the tables. For details, refer to [Target table handling](#handle-target-tables):

```
--table-handling truncate-if-exists
```

When performing a bulk load without subsequent replication, use <InternalLink path="molt-fetch-commands-and-flags#ignore-replication-check">`--ignore-replication-check`</InternalLink> to skip querying for replication checkpoints (such as `pg_current_wal_insert_lsn()` on PostgreSQL, `gtid_executed` on MySQL, and `CURRENT_SCN` on Oracle). This is appropriate when:

* Performing a one-time data migration with no plan to replicate ongoing changes.
* Exporting data from a read replica where replication checkpoints are unavailable.

```
--ignore-replication-check
```

At minimum, the `molt fetch` command should include the source, target, data path, and <InternalLink path="molt-fetch-commands-and-flags#ignore-replication-check">`--ignore-replication-check`</InternalLink> flags:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
molt fetch \
--source $SOURCE \
--target $TARGET \
--bucket-path 's3://bucket/path' \
--ignore-replication-check
```

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

* <InternalLink path="migration-approach-classic-bulk-load">Classic Bulk Load Migration</InternalLink>
* <InternalLink path="migration-approach-phased-bulk-load">Phased Bulk Load Migration</InternalLink>

### Initial bulk load (before replication)

In a migration that utilizes <InternalLink path="migration-considerations-replication">continuous replication</InternalLink>, perform an initial data load before <InternalLink path="molt-replicator#forward-replication-after-initial-load">setting up ongoing replication with MOLT Replicator</InternalLink>. Run the `molt fetch` command without <InternalLink path="molt-fetch-commands-and-flags#ignore-replication-check">`--ignore-replication-check`</InternalLink>, as shown below:

The workflow is the same as [Bulk data load](#bulk-data-load), except:

* Exclude <InternalLink path="molt-fetch-commands-and-flags#ignore-replication-check">`--ignore-replication-check`</InternalLink>. MOLT Fetch will query and record replication checkpoints.
* After the data load completes, check the [CDC cursor](#enable-replication) in the output for the checkpoint value to use with MOLT Replicator.

<Tabs>
  <Tab title="PostgreSQL">
    - You must include <InternalLink path="molt-fetch-commands-and-flags#pglogical-replication-slot-name">`--pglogical-replication-slot-name`</InternalLink> and <InternalLink path="molt-fetch-commands-and-flags#pglogical-publication-and-slot-drop-and-recreate">`--pglogical-publication-and-slot-drop-and-recreate`</InternalLink> to automatically create the publication and replication slot during the data load.

    At minimum, the `molt fetch` command should include the source, target, and data path flags:

    ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    molt fetch \
    --source $SOURCE \
    --target $TARGET \
    --bucket-path 's3://bucket/path' \
    --pglogical-replication-slot-name molt_slot \
    --pglogical-publication-and-slot-drop-and-recreate
    ```

    The output will include a `cdc_cursor` value at the end of the fetch task:

    ```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    {"level":"info","type":"summary","fetch_id":"735a4fe0-c478-4de7-a342-cfa9738783dc","num_tables":1,"tables":["public.employees"],"skipped_unmigratable_tables":[],"cdc_cursor":"b7f9e0fa-2753-1e1f-5d9b-2402ac810003:3-21","net_duration_ms":4879.890041,"net_duration":"000h 00m 04s","time":"2024-03-18T12:37:02-04:00","message":"fetch complete"}
    ```
  </Tab>

  <Tab title="MySQL">
    At minimum, the `molt fetch` command should include the source, target, and data path flags:

    ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    molt fetch \
    --source $SOURCE \
    --target $TARGET \
    --bucket-path 's3://bucket/path'
    ```

    The output will include a `cdc_cursor` value at the end of the fetch task:

    ```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    {"level":"info","type":"summary","fetch_id":"735a4fe0-c478-4de7-a342-cfa9738783dc","num_tables":1,"tables":["public.employees"],"skipped_unmigratable_tables":[],"cdc_cursor":"b7f9e0fa-2753-1e1f-5d9b-2402ac810003:3-21","net_duration_ms":4879.890041,"net_duration":"000h 00m 04s","time":"2024-03-18T12:37:02-04:00","message":"fetch complete"}
    ```

    Use this `cdc_cursor` value when starting MOLT Replicator to ensure replication begins from the correct position.
  </Tab>

  <Tab title="Oracle">
    At minimum, the `molt fetch` command should include the source, target, and data path flags:

    ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    molt fetch \
    --source $SOURCE \
    --source-cdb $SOURCE_CDB \
    --target $TARGET \
    --bucket-path 's3://bucket/path'
    ```

    The output will include a `cdc_cursor` value at the end of the fetch task:

    ```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    {"level":"info","type":"summary","fetch_id":"735a4fe0-c478-4de7-a342-cfa9738783dc","num_tables":1,"tables":["public.employees"],"skipped_unmigratable_tables":[],"cdc_cursor":"b7f9e0fa-2753-1e1f-5d9b-2402ac810003:3-21","net_duration_ms":4879.890041,"net_duration":"000h 00m 04s","time":"2024-03-18T12:37:02-04:00","message":"fetch complete"}
    ```

    Use this `cdc_cursor` value when starting MOLT Replicator to ensure replication begins from the correct position.
  </Tab>
</Tabs>

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

* <InternalLink path="migration-approach-delta">Delta Migration</InternalLink>
* <InternalLink path="migration-approach-phased-delta-failback">Phased Delta Migration with Failback Replication</InternalLink>

## Known limitations

* Only tables with <InternalLink version="stable" path="primary-key">primary key</InternalLink> types of <InternalLink version="stable" path="int">`INT`</InternalLink>, <InternalLink version="stable" path="float">`FLOAT`</InternalLink>, or <InternalLink version="stable" path="uuid">`UUID`</InternalLink> can be sharded with <InternalLink path="molt-fetch-best-practices#configure-the-source-database-and-connection">`--export-concurrency`</InternalLink>.
* `GEOMETRY` and `GEOGRAPHY` types are not supported.

The following limitation is specific to PostgreSQL sources:

* `OID LOB` types in PostgreSQL are not supported, although similar types like `BYTEA` are supported.

The following limitations are specific to Oracle sources:

* Migrations must be performed from a single Oracle schema. You **must** include <InternalLink path="molt-fetch#select-data-to-migrate">`--schema-filter`</InternalLink> so that MOLT Fetch only loads data from the specified schema. Refer to <InternalLink path="molt-fetch#select-data-to-migrate">Schema and table filtering</InternalLink>.
  * Specifying <InternalLink path="molt-fetch#select-data-to-migrate">`--table-filter`</InternalLink> is also strongly recommended to ensure that only necessary tables are migrated from the Oracle schema.
* Oracle advises against `LONG RAW` columns and [recommends converting them to `BLOB`](https://www.orafaq.com/wiki/LONG_RAW#History). `LONG RAW` can only store binary values up to 2GB, and only one `LONG RAW` column per table is supported.

## See also

* <InternalLink path="molt-fetch-installation">MOLT Fetch Installation</InternalLink>
* <InternalLink path="molt-fetch-commands-and-flags">MOLT Fetch Commands and Flags</InternalLink>
* <InternalLink path="molt-fetch-monitoring">MOLT Fetch Metrics</InternalLink>
* <InternalLink path="molt-fetch-best-practices">MOLT Fetch Best Practices</InternalLink>
* <InternalLink path="molt-fetch-troubleshooting">MOLT Fetch Troubleshooting</InternalLink>
* <InternalLink path="migration-overview">Migration Overview</InternalLink>
* <InternalLink path="molt-replicator">MOLT Replicator</InternalLink>