MOLT Fetch Best Practices

This page describes best practices for using 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 , 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 () multiplied by the number of tables () being exported concurrently.

    With the default numerical range sharding, only tables with types of , , or can be sharded. PostgreSQL users can enable to use statistics-based sharding for tables with primary keys of any data type. For details, refer to .

  • Maximum lifetime of a connection.

• If a PostgreSQL database is set as a , ensure that idle_in_transaction_session_timeout on PostgreSQL is either disabled or set to a value longer than the duration of the . 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 for all exported tables.

Memory requirements

• MOLT Fetch buffers data in memory before flushing to files or the target database, regardless of which you use. To prevent memory outages during , 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., 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 .

  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 , , or if memory is constrained.
  • Monitor memory utilization during the migration.

Optimize performance

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

  • (you do not need to drop this constraint when using drop-on-target-and-recreate for )
  Do not drop constraints.

  You can recreate and after loading the data.

• For PostgreSQL sources using , run ANALYZE 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, 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 for decreases in the number of rows being processed. You can use the sample Grafana dashboard to view metrics. For details on optimizing export performance through sharding, refer to .

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 during the 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, . Then resume molt fetch using , 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:

    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://[e-mail]: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 so special characters in passwords are handled correctly.

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

    molt escape-password --password 'a$52&'
    

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

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

• Remove sslmode=disable from production connection strings.

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 flag. Do this only when testing, or if a secure SSL/TLS connection to the source or target database is not possible.

Cloud storage security

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

S3

Amazon S3

• Set the following environment variables in the terminal running molt fetch:

  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:

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

GCS

Google Cloud Storage

• Authenticate your local environment with Application Default Credentials:

  Using gcloud:

  gcloud init
  gcloud auth application-default login
  

  Using the environment variable:

  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:

    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 . 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 '[e-mail]'
  

• Ensure the Google Cloud Storage bucket is created and accessible by authorized roles and users only.

Azure

Azure Blob Storage

• Set the following environment variables in the terminal running molt fetch:

  export AZURE_ACCOUNT_NAME='account'
  export AZURE_ACCOUNT_KEY='key'
  

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

  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:

    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 and workload identities.

• Ensure the Azure Blob Storage container is created and accessible by authorized roles and users only.

See also