> ## 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 Best Practices

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

This page describes best practices for using <InternalLink path="molt-fetch">MOLT Fetch</InternalLink> to ensure reliable, secure, and performant data migration to CockroachDB.

## Test and validate

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

## Configure the source database and connection

* To prevent connections from terminating prematurely during the <InternalLink path="molt-fetch#data-export-phase">data export phase</InternalLink>, 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 (<InternalLink path="molt-fetch-commands-and-flags#global-flags">`--export-concurrency`</InternalLink>) multiplied by the number of tables (<InternalLink path="molt-fetch-commands-and-flags#global-flags">`--table-concurrency`</InternalLink>) being exported concurrently.

    <Note>
      With the default numerical range sharding, 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. PostgreSQL users can enable <InternalLink path="molt-fetch-commands-and-flags#global-flags">`--use-stats-based-sharding`</InternalLink> to use statistics-based sharding for tables with primary keys of any data type. For details, refer to <InternalLink path="molt-fetch#shard-tables-for-concurrent-export">Table sharding</InternalLink>.
    </Note>

  * **Maximum lifetime of a connection.**

* If a PostgreSQL database is set as a <InternalLink path="molt-fetch">source</InternalLink>, ensure that [`idle_in_transaction_session_timeout`](https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-IN-TRANSACTION-SESSION-TIMEOUT) on PostgreSQL is either disabled or set to a value longer than the duration of the <InternalLink path="molt-fetch#data-export-phase">data export phase</InternalLink>. 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 <InternalLink path="molt-fetch-monitoring#metrics">`molt_fetch_table_export_duration_ms`</InternalLink> for all exported tables.

## Memory requirements

* MOLT Fetch buffers data in memory before flushing to files or the target database, regardless of which <InternalLink path="molt-fetch">data path</InternalLink> you use. To prevent memory outages during <InternalLink path="molt-fetch#data-export-phase">data export</InternalLink>, estimate the amount of memory used to export tables:

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

  If you are exporting multiple tables concurrently (i.e., <InternalLink path="molt-fetch-commands-and-flags#global-flags">`--table-concurrency`</InternalLink> is set higher than `1`), estimate the memory requirement using the table with the largest average row size. For details on how concurrency and sharding interact, refer to <InternalLink path="molt-fetch">Table sharding</InternalLink>.

  Insufficient memory can cause degraded throughput, process crashes, or migration failures. To prevent memory issues:

  * Allocate enough memory for MOLT Fetch, based on the preceding formula.
  * Adjust <InternalLink path="molt-fetch-commands-and-flags#global-flags">`--row-batch-size`</InternalLink>, <InternalLink path="molt-fetch-commands-and-flags#global-flags">`--export-concurrency`</InternalLink>, or <InternalLink path="molt-fetch-commands-and-flags#global-flags">`--table-concurrency`</InternalLink> if memory is constrained.
  * Monitor memory utilization during the migration.

## Optimize performance

* To optimize data load performance, drop all non-`PRIMARY KEY` <InternalLink version="stable" path="alter-table#drop-constraint">constraints</InternalLink> and <InternalLink version="stable" path="drop-index">indexes</InternalLink> on the target CockroachDB database before migrating:

  * <InternalLink version="stable" path="foreign-key">`FOREIGN KEY`</InternalLink>
  * <InternalLink version="stable" path="unique">`UNIQUE`</InternalLink>
  * <InternalLink version="stable" path="schema-design-indexes">Secondary indexes</InternalLink>
  * <InternalLink version="stable" path="check">`CHECK`</InternalLink>
  * <InternalLink version="stable" path="default-value">`DEFAULT`</InternalLink>
  * <InternalLink version="stable" path="not-null">`NOT NULL`</InternalLink> (you do not need to drop this constraint when using `drop-on-target-and-recreate` for <InternalLink path="molt-fetch#handle-target-tables">table handling</InternalLink>)

  <Danger>
    Do **not** drop <InternalLink version="stable" path="primary-key">`PRIMARY KEY`</InternalLink> constraints.
  </Danger>

  You can recreate <InternalLink version="stable" path="alter-table#add-constraint">constraints</InternalLink> and <InternalLink version="stable" path="create-index">indexes</InternalLink> after loading the data.

* For PostgreSQL sources using <InternalLink path="molt-fetch-commands-and-flags#global-flags">`--use-stats-based-sharding`</InternalLink>, run [`ANALYZE`](https://www.postgresql.org/docs/current/sql-analyze.html) on source tables before migration to ensure optimal shard distribution. This is especially important for large tables where even distribution can significantly improve export performance.

* If a table in the source database is much larger than the other tables, <InternalLink path="molt-fetch#schema-and-table-selection">filter and export the largest table</InternalLink> in its own `molt fetch` task. Repeat this for each of the largest tables. Then export the remaining tables in another task.

* Ensure that the machine running MOLT Fetch is equipped 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` <InternalLink path="molt-fetch-monitoring#metrics">metric</InternalLink> for decreases in the number of rows being processed. You can use the [sample Grafana dashboard](https://molt.cockroachdb.com/molt/cli/grafana_dashboard.json) to view metrics. For details on optimizing export performance through sharding, refer to <InternalLink path="molt-fetch">Table sharding</InternalLink>.

### Docker performance

MOLT Fetch is 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.

## Import and continuation handling

* When using <InternalLink path="molt-fetch">`IMPORT INTO`</InternalLink> during the <InternalLink path="molt-fetch#data-import-phase">data import phase</InternalLink> 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, <InternalLink version="stable" path="import-into#view-and-control-import-jobs">manually resume or cancel the job</InternalLink>. Then resume `molt fetch` using <InternalLink path="molt-fetch">continuation</InternalLink>, or restart the task from the beginning.

## Security

Cockroach Labs strongly recommends the following security practices.

### Connection security

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

  * Avoid plaintext connection strings.

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

    ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    export SOURCE="postgres://migration_user:a%2452%26@localhost:5432/migration_db?sslmode=verify-full"
    export TARGET="postgres://root@localhost:26257/defaultdb?sslmode=verify-full"
    ```

    Afterward, reference the environment variables in MOLT commands:

    ```
    --source $SOURCE
    --target $TARGET
    ```

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

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

  For example, a PostgreSQL connection string with TLS certificates:

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

* URL-encode connection strings for the source database and <InternalLink version="stable" path="connect-to-the-database">CockroachDB</InternalLink> so special characters in passwords are handled correctly.

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

    ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    molt escape-password --password 'a$52&'
    ```

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

    ```
    postgres://migration_user:a%2452%26@localhost:5432/migration_db
    ```

* Remove `sslmode=disable` from production connection strings.

<Note>
  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 <InternalLink path="molt-fetch-commands-and-flags#allow-tls-mode-disable">`--allow-tls-mode-disable`</InternalLink> flag. Do this **only** when testing, or if a secure SSL/TLS connection to the source or target database is not possible.
</Note>

### Cloud storage security

Ensure that access control is properly configured for [Amazon S3](#amazon-s3), [Google Cloud Storage](#google-cloud-storage), or [Azure Blob Storage](#azure-blob-storage).

<Tabs>
  <Tab title="S3">
    ##### Amazon S3

    * Set the following environment variables in the terminal running `molt fetch`:

      ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
      export AWS_REGION='us-east-1'
      export AWS_SECRET_ACCESS_KEY='key'
      export AWS_ACCESS_KEY_ID='id'
      ```

      * To run `molt fetch` in a containerized environment (e.g., Docker), pass the required environment variables using `-e`. If your authentication method relies on local credential files, you may also need to volume map the host path to the appropriate location inside the container using `-v`. For example:

        ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
        docker run \
          -e AWS_ACCESS_KEY_ID='your-access-key' \
          -e AWS_SECRET_ACCESS_KEY='your-secret-key' \
          -v ~/.aws:/root/.aws \
          -it \
          cockroachdb/molt fetch \
          --bucket-path 's3://migration/data/cockroach' ...
        ```

    * Alternatively, set `--use-implicit-auth` to use <InternalLink version="stable" path="cloud-storage-authentication">implicit authentication</InternalLink>. When using assume role authentication, specify the service account with `--assume-role`. For example:

      ```
      --bucket-path 's3://migration/data/cockroach'
      --assume-role 'arn:aws:iam::123456789012:role/MyMigrationRole'
      --use-implicit-auth
      ```

    * Set `--import-region` to specify an `AWS_REGION` (e.g., `--import-region 'ap-south-1'`).

    * Ensure the S3 bucket is created and accessible by authorized roles and users only.
  </Tab>

  <Tab title="GCS">
    ##### Google Cloud Storage

    * Authenticate your local environment with [Application Default Credentials](https://cloud.google.com/docs/authentication/application-default-credentials):

      Using `gcloud`:

      ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
      gcloud init
      gcloud auth application-default login
      ```

      Using the environment variable:

      ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
      export GOOGLE_APPLICATION_CREDENTIALS={path_to_cred_json}
      ```

      * To run `molt fetch` in a containerized environment (e.g., Docker), pass the required environment variables using `-e`. If your authentication method relies on local credential files, you may also need to volume map the host path to the appropriate location inside the container using `-v`. For example:

        ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
        docker run \
          -e GOOGLE_APPLICATION_CREDENTIALS='/root/.config/gcloud/application_default_credentials.json' \
          -v ~/.config/gcloud:/root/.config/gcloud \
          -it \
          cockroachdb/molt fetch \
          --bucket-path 'gs://migration/data/cockroach' ...
        ```

    * Alternatively, set `--use-implicit-auth` to use <InternalLink version="stable" path="cloud-storage-authentication">implicit authentication</InternalLink>. When using assume role authentication, specify the service account with `--assume-role`. For example:

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

    * Ensure the Google Cloud Storage bucket is created and accessible by authorized roles and users only.
  </Tab>

  <Tab title="Azure">
    ##### Azure Blob Storage

    * Set the following environment variables in the terminal running `molt fetch`:

      ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
      export AZURE_ACCOUNT_NAME='account'
      export AZURE_ACCOUNT_KEY='key'
      ```

      You can also speicfy client and tenant credentials as environment variables:

      ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
      export AZURE_CLIENT_SECRET='secret'
      export AZURE_TENANT_ID='id'
      ```

      * To run `molt fetch` in a containerized environment (e.g., Docker), pass the required environment variables using `-e`. If your authentication method relies on local credential files, you may also need to volume map the host path to the appropriate location inside the container using `-v`. For example:

        ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
        docker run \
          -e AZURE_ACCOUNT_NAME='account' \
          -e AZURE_ACCOUNT_KEY='key' \
          -e AZURE_CLIENT_SECRET='secret' \
          -e AZURE_TENANT_ID='id' \
          -v ~/.azure:/root/.azure \
          -it \
          cockroachdb/molt fetch \
          --bucket-path 'azure-blob://migration/data/cockroach' ...
        ```

    * Alternatively, set `--use-implicit-auth` to use implicit authentication: For example:

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

      This mode supports Azure [managed identities](https://learn.microsoft.com/en-us/entra/identity/managed-identities-azure-resources/overview) and [workload identities](https://learn.microsoft.com/en-us/entra/workload-id/workload-identities-overview).

    * Ensure the Azure Blob Storage container is created and accessible by authorized roles and users only.
  </Tab>
</Tabs>

## See also

* <InternalLink path="molt-fetch">MOLT Fetch</InternalLink>
* <InternalLink path="migration-overview">Migration Overview</InternalLink>
* <InternalLink path="molt-replicator">MOLT Replicator</InternalLink>
* <InternalLink path="molt-verify">MOLT Verify</InternalLink>