MOLT Fetch Best Practices

​

Test and validate

​

Configure the source database and connection

• To prevent connections from terminating prematurely during the data export phase, set the following to high values on the source database:
  • Maximum allowed number of connections. MOLT Fetch can export data across multiple connections. The number of connections it will create is the number of shards (--export-concurrency) multiplied by the number of tables (--table-concurrency) being exported concurrently.
    With the default numerical range sharding, only tables with primary key types of INT, FLOAT, or UUID can be sharded. PostgreSQL users can enable --use-stats-based-sharding to use statistics-based sharding for tables with primary keys of any data type. For details, refer to Table sharding.
  • Maximum lifetime of a connection.
• If a PostgreSQL database is set as a source, ensure that idle_in_transaction_session_timeout on PostgreSQL is either disabled or set to a value longer than the duration of the data export phase. Otherwise, the connection will be prematurely terminated. To estimate the time needed to export the PostgreSQL tables, you can perform a dry run and sum the value of molt_fetch_table_export_duration_ms for all exported tables.

​

Memory requirements

• MOLT Fetch buffers data in memory before flushing to files or the target database, regardless of which data path you use. To prevent memory outages during data export, 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., --table-concurrency 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 Table sharding. 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 --row-batch-size, --export-concurrency, or --table-concurrency if memory is constrained.
  • Monitor memory utilization during the migration.

​

Optimize performance

• To optimize data load performance, drop all non-PRIMARY KEY constraints and indexes on the target CockroachDB database before migrating:
  • FOREIGN KEY
  • UNIQUE
  • Secondary indexes
  • CHECK
  • DEFAULT
  • NOT NULL (you do not need to drop this constraint when using drop-on-target-and-recreate for table handling)
  Do not drop PRIMARY KEY constraints.
  You can recreate constraints and indexes after loading the data.
• For PostgreSQL sources using --use-stats-based-sharding, 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, filter and export the largest table in its own molt fetch task. Repeat this for each of the largest tables. Then export the remaining tables in another task.
• 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 metric 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 Table sharding.

​

Docker performance

​

Import and continuation handling

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

​

Security

​

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 in the MOLT_SOURCE and MOLT_TARGET environment variables, which MOLT Fetch and MOLT Verify read in place of --source and --target. For Oracle multitenant sources, MOLT_SOURCE_CDB replaces --source-cdb. For example:
    export MOLT_SOURCE="postgres://migration_user:a%2452%26@localhost:5432/migration_db?sslmode=verify-full"
    export MOLT_TARGET="postgres://root@localhost:26257/defaultdb?sslmode=verify-full"
    
    Afterward, omit --source and --target from MOLT commands. Unlike connection strings passed as flags, the environment variables do not appear in the process list. If you set both a flag and its environment variable, the flag takes precedence.
  • 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
  
  MySQL source connection strings use the MySQL names for TLS parameters: ssl-mode (DISABLED, PREFERRED, REQUIRED, VERIFY_CA, or VERIFY_IDENTITY), ssl-ca, ssl-cert, and ssl-key. MOLT rejects the PostgreSQL names (sslmode, sslrootcert, sslcert, and sslkey) in a MySQL connection string. For example, a MySQL connection string with TLS certificates:
  mysql://migration_user@db.example.com:3306/appdb?ssl-mode=VERIFY_IDENTITY&ssl-ca=/etc/migration_db/certs/ca.pem&ssl-cert=/etc/migration_db/certs/client.crt&ssl-key=/etc/migration_db/certs/client.key
  
• URL-encode connection strings for the source database and CockroachDB so special characters in passwords are handled correctly.
  • Given a password a$52&, pass it to the molt escape-password command with single quotes:
    molt escape-password --password 'a$52&'
    
    Use the encoded password in your connection string. For example:
    postgres://migration_user:a%2452%26@localhost:5432/migration_db
    
• Remove sslmode=disable from production connection strings.

​

Connect to Oracle over TLS (TCPS)

/path/to/wallet/
├── cwallet.sso     # auto-login wallet (credentials and trusted certificates)
├── sqlnet.ora      # points the Oracle client at this directory
└── tnsnames.ora    # defines the TCPS connection alias

MY_ORACLE_TLS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCPS)(HOST = {host})(PORT = 2484))
    (CONNECT_DATA = (SERVICE_NAME = {service_name}))
  )

WALLET_LOCATION =
  (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /path/to/wallet)))
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 1.2

• A TNS alias is required. The oracle://{host}:{port}/{service_name} URL form uses plain-TCP EZCONNECT and fails against a TCPS listener with ORA-12537: TNS:connection closed. Always connect via the @{alias} defined in tnsnames.ora.
• Auto-login wallets only. The Oracle client driver has no wallet-password option, so use an auto-login wallet (cwallet.sso). Because the wallet contains credentials, store it in a directory that only the user running the tool can read.
• Oracle Instant Client basic variant required. TLS requires the full basic client; basiclite may be missing the required TLS libraries.

molt fetch \
--source 'oracle://{username}:{password}@MY_ORACLE_TLS' \
--source-oracle-wallet-location '/path/to/wallet' \
--target 'postgres://{username}@{host}:{port}/{database}' \
...

​

Cloud storage security

​

See also

• MOLT Fetch
• Migration Overview
• MOLT Replicator
• MOLT Verify