> ## 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. # Phased Delta Migration with Failback Replication from Oracle 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 {children} ; }; A *Phased Delta Migration with Failback Replication* involves migrating data to CockroachDB in several phases. Data can be sliced per tenant, per service, per region, or per table to suit the needs of the migration. **For each given migration phase**, you use MOLT Fetch to perform an initial bulk load of the data, you use MOLT Replicator to update the target database via forward replication and to activate failback replication, and then you cut over application traffic to CockroachDB after schema finalization and data verification. This process is repeated for each phase of data. * Data is migrated to the target in phases. * This approach utilizes continuous replication. * Rollback is achieved via failback replication. This approach is comparable to the Delta Migration, but dividing the data into multiple phases allows each downtime window to be shorter, and it allows each phase of the migration to be less complex. Depending on how you divide the data, it also may allow your downtime windows to affect only a subset of users. For example, dividing the data per region could mean that, when migrating the data from Region A, application usage in Region B may remain unaffected. This approach may increase overall migration complexity: its duration is longer, you will need to do the work of partitioning the data, and you will have a longer period when you run both the source and the target database concurrently. Failback replication keeps the source database up to date with changes that occur in the target database once the target database begins receiving write traffic. Failback replication ensures that, if something goes wrong during the migration process, traffic can easily be returned to the source database without data loss. Like forward replication, in this approach, failback replication is run on a per-phase basis. It can persist indefinitely, until you're comfortable maintaining the target database as your sole data store. This approach is best for databases that are too large to migrate all at once, and in business cases where downtime must be minimal. It's also suitable for risk-averse situations in which a safe rollback path must be ensured. It can only be performed if your team can handle the complexity of this approach, and if your source database can easily be divided into the phases you need. This page describes an example scenario. While the commands provided can be copy-and-pasted, they may need to be altered or reconsidered to suit the needs of your specific environment. Phased Delta Migration flow

## Example scenario You have a moderately-sized (500GB) database that provides the data store for a web application. You want to migrate the entirety of this database to a new CockroachDB cluster. You will divide this migration into four geographic regions (A, B, C, and D). The application runs on a Kubernetes cluster with an NGINX Ingress Controller. Your business could not accommodate major performance issues that could arise after the migration. Therefore, you want to enable failback replication so that you can easily return to using your original database with minimal interruption. **Estimated system downtime:** 3-5 minutes per region. ## Before the migration * Install the MOLT (Migrate Off Legacy Technology) tools. * Review the MOLT Fetch and MOLT Replicator documentation. * Develop a migration plan and prepare for the migration. * **Recommended:** Perform a dry run of this full set of instructions in a development environment that closely resembles your production environment. This can help you get a realistic sense of the time and complexity it requires. * Understand the prerequisites and limitations of the MOLT tools: ### Prerequisites #### Oracle Instant Client Install Oracle Instant Client on the machine that will run `molt` and `replicator`. If using the MOLT Replicator binary (instead of Docker), the Oracle Instant Client libraries must be accessible at `/usr/lib`. * On macOS ARM machines, download the [Oracle Instant Client](https://www.oracle.com/database/technologies/instant-client/macos-arm64-downloads.html#ic_osx_inst). After installation, you should have a new directory at `/Users/$USER/Downloads/instantclient_23_3` containing `.dylib` files. Set the `LD_LIBRARY_PATH` environment variable to this directory: ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} export LD_LIBRARY_PATH=/Users/$USER/Downloads/instantclient_23_3 ``` * On Linux machines, install the Oracle Instant Client dependencies and set the `LD_LIBRARY_PATH` to the client library path: ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} sudo apt-get install -yqq --no-install-recommends libaio1t64 sudo ln -s /usr/lib/x86_64-linux-gnu/libaio.so.1t64 /usr/lib/x86_64-linux-gnu/libaio.so.1 unzip -d /tmp /tmp/instantclient-basiclite-linux-amd64.zip sudo mv /tmp/instantclient_21_13/* /usr/lib export LD_LIBRARY_PATH=/usr/lib ``` You can also download Oracle Instant Client directly from the Oracle site for [Linux ARM64](https://www.oracle.com/database/technologies/instant-client/linux-amd64-downloads.html) or [Linux x86-64](https://www.oracle.com/ca-en/database/technologies/instant-client/linux-x86-64-downloads.html). ### Limitations #### MOLT Fetch limitations * Only tables with primary key types of `INT`, `FLOAT`, or `UUID` can be sharded with `--export-concurrency`. * `GEOMETRY` and `GEOGRAPHY` types are not supported. * Migrations must be performed from a single Oracle schema. You **must** include `--schema-filter` so that MOLT Fetch only loads data from the specified schema. Refer to Schema and table filtering. * Specifying `--table-filter` 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. #### MOLT Replicator limitations * Replication modes require connection to the primary instance (PostgreSQL primary, MySQL primary/master, or Oracle primary). MOLT cannot obtain replication checkpoints or transaction metadata from replicas. * Running DDL on the source or target while replication is in progress can cause replication failures. * `TRUNCATE` operations on the source are not captured. Only `INSERT`, `UPDATE`, `UPSERT`, and `DELETE` events are replicated. * Changes to virtual columns are not replicated automatically. To migrate these columns, you must define them explicitly with transformation rules. * Replication will not work for tables or column names exceeding 30 characters. This is a [limitation of Oracle LogMiner](https://docs.oracle.com/en/database/oracle/oracle-database/21/sutil/oracle-logminer-utility.html#GUID-7594F0D7-0ACD-46E6-BD61-2751136ECDB4). * The following data types are not supported for replication: * User-defined types (UDTs) * Nested tables * `VARRAY` * `LONGBLOB`/`CLOB` columns (over 4000 characters) * If your Oracle workload executes `UPDATE` statements that modify only LOB columns, these `UPDATE` statements are not supported by Oracle LogMiner and will not be replicated. * If you are using Oracle 11 and execute `UPDATE` statements on `XMLTYPE` or LOB columns, those changes are not supported by Oracle LogMiner and will be excluded from ongoing replication. * If you are migrating LOB columns from Oracle 12c, use [AWS DMS Binary Reader](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.Oracle.html#CHAP_Source.Oracle.CDC) instead of LogMiner. Oracle LogMiner does not support LOB replication in 12c. #### MOLT Verify limitations * MOLT Verify compares 20,000 rows at a time by default, and row values can change between batches, potentially resulting in temporary inconsistencies in data. To configure the row batch size, use the `--row_batch_size` flag. * MOLT Verify checks for collation mismatches on primary key columns. This may cause validation to fail when a `STRING` is used as a primary key and the source and target databases are using different collations. * MOLT Verify might give an error in case of schema changes on either the source or target database. * Geospatial types cannot yet be compared. ## Step 1: Prepare the source database In this step, you will: * [Create a dedicated migration user on your source database](#create-migration-user-on-source-database). * [Configure the source database for replication](#configure-source-database-for-replication). ### Create migration user on source database Create a dedicated migration user (for example, `MIGRATION_USER`) on the source database. This user is responsible for reading data from source tables during the migration. You will pass this username in the [source connection string](#source-connection-string). ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE USER MIGRATION_USER IDENTIFIED BY 'password'; ``` When migrating from Oracle Multitenant (PDB/CDB), this should be a [common user](https://docs.oracle.com/database/121/ADMQS/GUID-DA54EBE5-43EF-4B09-B8CC-FAABA335FBB8.htm). Prefix the username with `C##` (e.g., `C##MIGRATION_USER`). Grant the user privileges to connect, read metadata, and `SELECT` and `FLASHBACK` the tables you plan to migrate. The tables should all reside in a single schema (for example, `migration_schema`). For details, refer to [Schema and table filtering](#schema-and-table-filtering). #### Oracle Multitenant (PDB/CDB) user privileges Connect to the Oracle CDB as a DBA and grant the following: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} -- Basic access GRANT CONNECT TO C##MIGRATION_USER; GRANT CREATE SESSION TO C##MIGRATION_USER; -- General metadata access GRANT EXECUTE_CATALOG_ROLE TO C##MIGRATION_USER; GRANT SELECT_CATALOG_ROLE TO C##MIGRATION_USER; -- Access to necessary V$ views GRANT SELECT ON V_$LOG TO C##MIGRATION_USER; GRANT SELECT ON V_$LOGFILE TO C##MIGRATION_USER; GRANT SELECT ON V_$LOGMNR_CONTENTS TO C##MIGRATION_USER; GRANT SELECT ON V_$ARCHIVED_LOG TO C##MIGRATION_USER; GRANT SELECT ON V_$DATABASE TO C##MIGRATION_USER; GRANT SELECT ON V_$LOG_HISTORY TO C##MIGRATION_USER; -- Direct grants to specific DBA views GRANT SELECT ON ALL_USERS TO C##MIGRATION_USER; GRANT SELECT ON DBA_USERS TO C##MIGRATION_USER; GRANT SELECT ON DBA_OBJECTS TO C##MIGRATION_USER; GRANT SELECT ON DBA_SYNONYMS TO C##MIGRATION_USER; GRANT SELECT ON DBA_TABLES TO C##MIGRATION_USER; ``` Connect to the Oracle PDB (not the CDB) as a DBA and grant the following: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} -- Allow C##MIGRATION_USER to connect to the PDB and see active transaction metadata GRANT CONNECT TO C##MIGRATION_USER; GRANT CREATE SESSION TO C##MIGRATION_USER; -- General metadata access GRANT SELECT_CATALOG_ROLE TO C##MIGRATION_USER; -- Access to necessary V$ views GRANT SELECT ON V_$SESSION TO C##MIGRATION_USER; GRANT SELECT ON V_$TRANSACTION TO C##MIGRATION_USER; -- Grant these two for every table to migrate in the migration_schema GRANT SELECT, FLASHBACK ON migration_schema.tbl TO C##MIGRATION_USER; ``` #### Single-tenant Oracle user privileges Connect to the Oracle database as a DBA and grant the following: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} -- Basic access GRANT CONNECT TO MIGRATION_USER; GRANT CREATE SESSION TO MIGRATION_USER; -- General metadata access GRANT SELECT_CATALOG_ROLE TO MIGRATION_USER; GRANT EXECUTE_CATALOG_ROLE TO MIGRATION_USER; -- Access to necessary V$ views GRANT SELECT ON V_$DATABASE TO MIGRATION_USER; GRANT SELECT ON V_$SESSION TO MIGRATION_USER; GRANT SELECT ON V_$TRANSACTION TO MIGRATION_USER; -- Direct grants to specific DBA views GRANT SELECT ON ALL_USERS TO MIGRATION_USER; GRANT SELECT ON DBA_USERS TO MIGRATION_USER; GRANT SELECT ON DBA_OBJECTS TO MIGRATION_USER; GRANT SELECT ON DBA_SYNONYMS TO MIGRATION_USER; GRANT SELECT ON DBA_TABLES TO MIGRATION_USER; -- Grant these two for every table to migrate in the migration_schema GRANT SELECT, FLASHBACK ON migration_schema.tbl TO MIGRATION_USER; ``` ### Configure source database for replication Connect to the primary instance (PostgreSQL primary, MySQL primary/master, or Oracle primary), **not** a replica. Replicas cannot provide the necessary replication checkpoints and transaction metadata required for ongoing replication. #### Enable ARCHIVELOG and FORCE LOGGING Enable `ARCHIVELOG` mode for LogMiner to access archived redo logs: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} -- Check current log mode SELECT log_mode FROM v$database; -- Enable ARCHIVELOG (requires database restart) SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN; -- Verify ARCHIVELOG is enabled SELECT log_mode FROM v$database; -- Expected: ARCHIVELOG ``` Enable supplemental logging for primary keys: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} -- Enable minimal supplemental logging for primary keys ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; -- Verify supplemental logging status SELECT supplemental_log_data_min, supplemental_log_data_pk FROM v$database; -- Expected: -- SUPPLEMENTAL_LOG_DATA_MIN: IMPLICIT (or YES) -- SUPPLEMENTAL_LOG_DATA_PK: YES ``` Enable `FORCE LOGGING` to ensure all changes are logged: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER DATABASE FORCE LOGGING; -- Verify FORCE LOGGING is enabled SELECT force_logging FROM v$database; -- Expected: YES ``` #### Create source sentinel table Create a checkpoint table called `REPLICATOR_SENTINEL` in the Oracle schema you will migrate: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE TABLE migration_schema."REPLICATOR_SENTINEL" ( keycol NUMBER PRIMARY KEY, lastSCN NUMBER ); ``` Grant privileges to modify the checkpoint table. In Oracle Multitenant, grant the privileges on the PDB: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} GRANT SELECT, INSERT, UPDATE ON migration_schema."REPLICATOR_SENTINEL" TO C##MIGRATION_USER; ``` #### Grant LogMiner privileges Grant LogMiner privileges. In Oracle Multitenant, grant the permissions on the CDB: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} -- Access to necessary V$ views GRANT SELECT ON V_$LOG TO C##MIGRATION_USER; GRANT SELECT ON V_$LOGFILE TO C##MIGRATION_USER; GRANT SELECT ON V_$LOGMNR_CONTENTS TO C##MIGRATION_USER; GRANT SELECT ON V_$ARCHIVED_LOG TO C##MIGRATION_USER; GRANT SELECT ON V_$LOG_HISTORY TO C##MIGRATION_USER; -- SYS-prefixed views (for full dictionary access) GRANT SELECT ON SYS.V_$LOGMNR_DICTIONARY TO C##MIGRATION_USER; GRANT SELECT ON SYS.V_$LOGMNR_LOGS TO C##MIGRATION_USER; GRANT SELECT ON SYS.V_$LOGMNR_PARAMETERS TO C##MIGRATION_USER; GRANT SELECT ON SYS.V_$LOGMNR_SESSION TO C##MIGRATION_USER; -- Access to LogMiner views and controls GRANT LOGMINING TO C##MIGRATION_USER; GRANT EXECUTE ON DBMS_LOGMNR TO C##MIGRATION_USER; ``` The user must: * Query [redo logs from LogMiner](#verify-logminer-privileges). * Retrieve active transaction information to determine the starting point for ongoing replication. * Update the internal [`REPLICATOR_SENTINEL` table](#create-source-sentinel-table) created on the Oracle source schema by the DBA. #### Verify LogMiner privileges Query the locations of redo files in the Oracle database: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SELECT l.GROUP#, lf.MEMBER, l.FIRST_CHANGE# AS START_SCN, l.NEXT_CHANGE# AS END_SCN FROM V$LOG l JOIN V$LOGFILE lf ON l.GROUP# = lf.GROUP#; ``` ``` GROUP# MEMBER START_SCN END_SCN _________ _________________________________________ ____________ ______________________ 3 /opt/oracle/oradata/ORCLCDB/redo03.log 1232896 9295429630892703743 2 /opt/oracle/oradata/ORCLCDB/redo02.log 1155042 1232896 1 /opt/oracle/oradata/ORCLCDB/redo01.log 1141934 1155042 3 rows selected. ``` Get the current snapshot System Change Number (SCN): ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SELECT CURRENT_SCN FROM V$DATABASE; ``` ``` CURRENT_SCN ----------- 2358840 1 row selected. ``` Add the redo log files to LogMiner, using the redo log file paths you queried: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} EXEC DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/opt/oracle/oradata/ORCLCDB/redo01.log', OPTIONS => DBMS_LOGMNR.NEW); EXEC DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/opt/oracle/oradata/ORCLCDB/redo02.log', OPTIONS => DBMS_LOGMNR.ADDFILE); EXEC DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/opt/oracle/oradata/ORCLCDB/redo03.log', OPTIONS => DBMS_LOGMNR.ADDFILE); ``` Start LogMiner, specifying the SCN you queried: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} EXEC DBMS_LOGMNR.START_LOGMNR( STARTSCN => 2358840, OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG ); ``` ``` PL/SQL procedure successfully completed. ``` If you receive `ORA-01435: user does not exist`, the Oracle user lacks sufficient LogMiner privileges. Refer to [Grant LogMiner privileges](#grant-logminer-privileges). ## Step 2: Prepare the target database ### Define the target tables Convert the source table definitions into CockroachDB-compatible equivalents. CockroachDB supports the PostgreSQL wire protocol and is largely compatible with PostgreSQL syntax. * The source and target table definitions must **match**. Review Type mapping to understand which source types can be mapped to CockroachDB types. For example, an Oracle source table defined as `CREATE TABLE migration_schema.tbl (pk INT PRIMARY KEY);` must have a corresponding schema and table in CockroachDB: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE SCHEMA migration_schema; CREATE TABLE migration_schema.tbl (pk INT PRIMARY KEY); ``` * MOLT Fetch can automatically define matching CockroachDB tables using the `drop-on-target-and-recreate` option. * If you define the target tables manually, review how MOLT Fetch handles type mismatches. You can use the [MOLT Schema Conversion Tool](#schema-conversion-tool) to create matching table definitions. * By default, table and column names are case-insensitive in MOLT Fetch. If using the `--case-sensitive` flag, schema, table, and column names must match Oracle's default uppercase identifiers. Use quoted names on the target to preserve case. For example, the following CockroachDB SQL statement will error: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE TABLE co.stores (... store_id ...); ``` It should be written as: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE TABLE "CO"."STORES" (... "STORE_ID" ...); ``` When using `--case-sensitive`, quote all identifiers and match the case exactly (for example, use `"CO"."STORES"` and `"STORE_ID"`). * Every table **must** have an explicit primary key. Avoid using sequential keys. To learn more about the performance issues that can result from their use, refer to the guidance on indexing with sequential keys. If a sequential key is necessary in your CockroachDB table, you must create it manually, after using MOLT Fetch to load and replicate the data. * Review Transformations to understand how computed columns and partitioned tables can be mapped to the target, and how target tables can be renamed. * By default on CockroachDB, `INT` is an alias for `INT8`, which creates 64-bit signed integers. PostgreSQL and MySQL default to 32-bit integers. Depending on your source database or application requirements, you may need to change the integer size to `4`. For more information, refer to Considerations for 64-bit signed integers. #### Schema Conversion Tool The MOLT Schema Conversion Tool (SCT) converts source table definitions to CockroachDB-compatible syntax. It requires a free CockroachDB Cloud account. 1. Upload a source `.sql` file to convert the syntax and identify unimplemented features and syntax incompatibilities in the table definitions. 2. Import the converted table definitions to a CockroachDB cluster: * When migrating to CockroachDB Cloud, the Schema Conversion Tool automatically applies the converted table definitions to a new Cloud database. * When migrating to a self-hosted CockroachDB cluster, export a converted DDL file and pipe the data definition language (DDL) directly into `cockroach sql`. #### Drop constraints and indexes 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](#table-handling-mode)) Do **not** drop `PRIMARY KEY` constraints. You can [recreate the constraints and indexes after loading the data](#add-constraints-and-indexes). ### Create the SQL user Create a SQL user in the CockroachDB cluster that has the necessary privileges. To create a user `crdb_user` in the default database (you will pass this username in the [target connection string](#target-connection-string)): ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE USER crdb_user WITH PASSWORD 'password'; ``` Grant database-level privileges for schema creation within the target database: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} GRANT ALL ON DATABASE defaultdb TO crdb_user; ``` Grant permission to modify cluster settings: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} GRANT SYSTEM MODIFYCLUSTERSETTING TO crdb_user; ``` Grant usage on schemas being migrated: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} GRANT USAGE ON SCHEMA migration_schema TO crdb_user; GRANT USAGE ON SCHEMA public TO crdb_user; ``` Grant user privileges to create tables in the `migration_schema` schema and internal MOLT tables like `_molt_fetch_exceptions` in the `public` CockroachDB schema: Ensure that you are connected to the target database. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} GRANT CREATE ON SCHEMA migration_schema TO crdb_user; GRANT CREATE ON SCHEMA public TO crdb_user; ``` If you manually defined the target tables (which means that [`drop-on-target-and-recreate`](#table-handling-mode) will not be used), grant the following privileges on the schema: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA migration_schema TO crdb_user; ALTER DEFAULT PRIVILEGES IN SCHEMA migration_schema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO crdb_user; ``` Grant the same privileges for internal MOLT tables in the `public` CockroachDB schema: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO crdb_user; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO crdb_user; ``` If you will be running Fetch with [`drop-on-target-and-recreate`](#table-handling-mode), and the target schema has pre-existing tables that were created by a different user, you may need to change table ownership to allow the migration user to drop those tables. Make the following alteration for each table: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER TABLE migration_schema.table1 OWNER TO crdb_user; ``` Depending on the MOLT Fetch data load mode you will use, grant the necessary privileges to run either [`IMPORT INTO`](#import-into-privileges) or [`COPY FROM`](#copy-from-privileges) on the target tables: #### `IMPORT INTO` privileges Grant `SELECT`, `INSERT`, and `DROP` (required because the table is taken offline during the `IMPORT INTO`) privileges on all tables being migrated: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} GRANT SELECT, INSERT, DROP ON ALL TABLES IN SCHEMA migration_schema TO crdb_user; ``` If you plan to use [cloud storage with implicit authentication](#cloud-storage-authentication) for data load, grant the `EXTERNALIOIMPLICITACCESS` system-level privilege: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} GRANT SYSTEM EXTERNALIOIMPLICITACCESS TO crdb_user; ``` #### `COPY FROM` privileges Grant `admin` privileges to the user: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} GRANT admin TO crdb_user; ``` #### Set session variable Ensure that the `statement_timeout` session variable is set to `0s` for the user: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER ROLE crdb_user SET statement_timeout = '0s'; ``` #### Replication privileges Grant permissions to create the staging schema for replication: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER USER crdb_user CREATEDB; ``` ### Configure GC TTL Before starting the [initial data load](#run-molt-fetch), configure the garbage collection (GC) TTL on the source CockroachDB cluster to ensure that historical data remains available when replication begins. The GC TTL must be long enough to cover the full duration of the data load. Increase the GC TTL before starting the data load. For example, to set the GC TTL to 24 hours: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER DATABASE defaultdb CONFIGURE ZONE USING gc.ttlseconds = 86400; ``` The GC TTL duration must be higher than your expected time for the initial data load. Once replication has started successfully (which automatically protects its own data range), you can restore the GC TTL to its original value. For example, to restore to 5 minutes: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ALTER DATABASE defaultdb CONFIGURE ZONE USING gc.ttlseconds = 300; ``` For details, refer to Protect Changefeed Data from Garbage Collection. ## Migrate each phase Steps 3-12 are run for each phase of the data migration. When migrating the first phase, you will run through these steps for Region A. You will repeat these steps for the other regions during each subsequent migration phase. ## Step 3: Load data into CockroachDB In this step, you will: * [Configure MOLT Fetch with the flags needed for your migration](#configure-molt-fetch). * [Run MOLT Fetch](#run-molt-fetch). * [Understand how to continue a load after an interruption](#continue-molt-fetch-after-an-interruption). ### Configure MOLT Fetch The MOLT Fetch documentation includes detailed information about how to configure MOLT Fetch, and how to monitor MOLT Fetch metrics. When you run `molt fetch`, you can configure the following options for data load: * Specify source and target databases: Specify URL‑encoded source and target connections. * Select data to migrate: Specify schema and table names to migrate. **Important for a phased migration.** * Define intermediate file storage: Export data to cloud storage or a local file server. * Define fetch mode: Specifies whether data will only be loaded into/from intermediate storage. * Shard tables: Divide larger tables into multiple shards during data export. * Data load mode: Choose between `IMPORT INTO` and `COPY FROM`. * Table handling mode: Determine how existing target tables are initialized before load. * Define data transformations: Define any row-level transformations to apply to the data before it reaches the target. * Monitor fetch metrics: Configure metrics collection during initial data load. Read through the documentation to understand how to configure your `molt fetch` command and its flags. Follow best practices, especially those related to security. At minimum, the `molt fetch` command should include the source, target, data path, and `--ignore-replication-check` flags. For a phased migration, you may also choose to include `--schema-filter` or `--table-filter` flags: ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} molt fetch \ --source $SOURCE \ --target $TARGET \ --table-filter '.*user.*' \ --bucket-path 's3://bucket/path' \ --ignore-replication-check ``` However, depending on the needs of your migration, you may have many more flags set, and you may need to prepare some accompanying .json files. ### Run MOLT Fetch Perform the initial load of the source data. 1. Issue the MOLT Fetch command to move the source data to CockroachDB. This example command passes the source and target connection strings [as environment variables](#secure-connections), writes [intermediate files](#intermediate-file-storage) to S3 storage, and uses the `truncate-if-exists` [table handling mode](#table-handling-mode) to truncate the target tables before loading data. It also limits the migration to a single schema and filters three specific tables to migrate. The data load mode defaults to `IMPORT INTO`. The command assumes an Oracle Multitenant (CDB/PDB) source. `--source-cdb` specifies the container database (CDB) connection string. ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} molt fetch \ --source $SOURCE \ --source-cdb $SOURCE_CDB \ --target $TARGET \ --schema-filter 'migration_schema' \ --table-filter 'employees|payments|orders' \ --bucket-path 's3://migration/data/cockroach' \ --table-handling truncate-if-exists ``` 2. Check the output to observe `fetch` progress. A `starting fetch` message indicates that the task has started: ```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} {"level":"info","type":"summary","num_tables":3,"cdc_cursor":"backfillFromSCN=26685444,scn=26685786","time":"2025-02-10T14:28:11-05:00","message":"starting fetch"} ``` `data extraction` messages are written for each table that is exported to the location in `--bucket-path`: ```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} {"level":"info","table":"migration_schema.employees","time":"2025-02-10T14:28:11-05:00","message":"data extraction phase starting"} ``` ```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} {"level":"info","table":"migration_schema.employees","type":"summary","num_rows":200000,"export_duration_ms":1000,"export_duration":"000h 00m 01s","time":"2025-02-10T14:28:12-05:00","message":"data extraction from source complete"} ``` `data import` messages are written for each table that is loaded into CockroachDB: ```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} {"level":"info","table":"migration_schema.employees","time":"2025-02-10T14:28:12-05:00","message":"starting data import on target"} ``` ```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} {"level":"info","table":"migration_schema.employees","type":"summary","net_duration_ms":1899.748333,"net_duration":"000h 00m 01s","import_duration_ms":1160.523875,"import_duration":"000h 00m 01s","export_duration_ms":1000,"export_duration":"000h 00m 01s","num_rows":200000,"cdc_cursor":"backfillFromSCN=26685444,scn=26685786","time":"2025-02-10T14:28:13-05:00","message":"data import on target for table complete"} ``` A `fetch complete` message is written when the fetch task succeeds: ```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} {"level":"info","type":"summary","fetch_id":"f5cb422f-4bb4-4bbd-b2ae-08c4d00d1e7c","num_tables":3,"tables":["migration_schema.employees","migration_schema.payments","migration_schema.payments"],"skipped_unmigratable_tables":[],"cdc_cursor":"backfillFromSCN=26685444,scn=26685786","net_duration_ms":6752.847625,"net_duration":"000h 00m 06s","time":"2024-03-18T12:30:37-04:00","message":"fetch complete"} ``` This message shows the appropriate values for the `--backfillFromSCN` and `--scn` flags to use when [starting Replicator](#start-replicator): ``` --backfillFromSCN 26685444 --scn 26685786 ``` ### Continue MOLT Fetch after an interruption If MOLT Fetch fails while loading data into CockroachDB from intermediate files, it exits with an error message, fetch ID, and *continuation token* for each table that failed to load on the target database. ```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} {"level":"info","table":"public.employees","file_name":"shard_01_part_00000001.csv.gz","message":"creating or updating token for duplicate key value violates unique constraint \"employees_pkey\"; Key (id)=(1) already exists."} {"level":"info","table":"public.employees","continuation_token":"a1b2c3d4-e5f6-7890-abcd-ef1234567890","message":"created continuation token"} {"level":"info","fetch_id":"f5cb422f-4bb4-4bbd-b2ae-08c4d00d1e7c","message":"continue from this fetch ID"} {"level":"error","message":"Error: error from fetching table for public.employees: error importing data: duplicate key value violates unique constraint \"employees_pkey\" (SQLSTATE 23505)"} ``` 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 or local storage. * The *initial load* of source data into the target CockroachDB database is incomplete. * The load uses [`IMPORT INTO` rather than `COPY FROM`](#data-load-mode). 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. The following command reattempts the data load starting from a specific continuation file, but you can also use individual continuation tokens to reattempt the data load for individual tables. The command assumes an Oracle Multitenant (CDB/PDB) source. `--source-cdb` specifies the container database (CDB) connection string. ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} molt fetch \ --source $SOURCE \ --source-cdb $SOURCE_CDB \ --target $TARGET \ --schema-filter 'migration_schema' \ --table-filter 'employees|payments|orders' \ --bucket-path 's3://migration/data/cockroach' \ --table-handling truncate-if-exists \ --ignore-replication-check \ --fetch-id f5cb422f-4bb4-4bbd-b2ae-08c4d00d1e7c \ --continuation-file-name shard_01_part_00000001.csv.gz ``` ## Step 4: Verify the initial data load In this step, you will use MOLT Verify to confirm that the source and target data is consistent. This ensures that the data load was successful. Use MOLT Verify's `--schema-filter` or `--table-filter` to select only the tables that are relevant for the given phase. ### Run MOLT Verify 1. Run the MOLT Verify command, specifying the source and target [connection strings](#connection-strings) and the tables to validate. ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} molt verify \ --source $SOURCE \ --target $TARGET \ --table-filter 'employees|payments|orders' ``` With Oracle Multitenant deployments, while `--source-cdb` is required for `fetch`, it is **not** necessary for `verify`. 2. Check the output to observe `verify` progress. A `verification in progress` indicates that the task has started: ```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} {"level":"info","time":"2025-02-10T15:35:04-05:00","message":"verification in progress"} ``` `starting verify` messages are written for each specified table: ```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} {"level":"info","time":"2025-02-10T15:35:04-05:00","message":"starting verify on public.employees, shard 1/1"} ``` A `finished row verification` message is written after each table is compared. If `num_success` equals `num_truth_rows` and the error counters (`num_missing`, `num_mismatch`, `num_extraneous`, and `num_column_mismatch`) are all `0`, the table verified successfully. Any non-zero values in the error counters indicate data discrepancies that need investigation. For details on each field, refer to the MOLT Verify page. ```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} {"level":"info","type":"summary","table_schema":"public","table_name":"employees","num_truth_rows":200004,"num_success":200004,"num_conditional_success":0,"num_missing":0,"num_mismatch":0,"num_extraneous":0,"num_live_retry":0,"num_column_mismatch":0,"time":"2025-02-10T15:35:05-05:00","message":"finished row verification on public.employees (shard 1/1)"} ``` A `verification complete` message is written when the verify task succeeds: ```json theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} {"level":"info","net_duration_ms":699.804875,"net_duration":"000h 00m 00s","time":"2025-02-10T15:35:05-05:00","message":"verification complete"} ``` ## Step 5: Finalize the target schema ### Add constraints and indexes Add any constraints or indexes that you previously [removed from the CockroachDB schema](#drop-constraints-and-indexes) to facilitate data load. If you used the `--table-handling drop-on-target-and-recreate` option for data load, only `PRIMARY KEY` and `NOT NULL` constraints are preserved. You **must** manually recreate all other constraints and indexes. For the appropriate SQL syntax, refer to `ALTER TABLE ... ADD CONSTRAINT` and `CREATE INDEX`. Review the best practices for creating secondary indexes on CockroachDB. ## Step 6: Begin forward replication In this step, you will: * [Configure MOLT Replicator with the flags needed for your migration](#configure-molt-replicator-forward-replication). * [Start MOLT Replicator](#start-molt-replicator-forward-replication). * [Understand how to continue replication after an interruption](#continue-molt-replicator-after-an-interruption-forward-replication). ### Configure MOLT Replicator (forward replication) When you run `replicator`, you can configure the following options for replication: * [Replication connection strings](#replication-connection-strings): Specify URL-encoded source and target database connections. * [Replicator flags](#replicator-flags): Specify required and optional flags to configure replicator behavior. * [Tuning parameters](#tuning-parameters): Optimize replication performance and resource usage. * [Replicator metrics](#replicator-metrics): Monitor replication progress and performance. #### Replication connection strings MOLT Replicator uses `--sourceConn` and `--targetConn` to specify the source and target database connections. `--sourceConn` specifies the connection string of the source database: ``` --sourceConn 'oracle://{username}:{password}@{host}:{port}/{service_name}' ``` For Oracle Multitenant databases, also specify `--sourcePDBConn` with the PDB connection string: ``` --sourcePDBConn 'oracle://{username}:{password}@{host}:{port}/{pdb_service_name}' ``` `--targetConn` specifies the target CockroachDB connection string: ``` --targetConn 'postgresql://{username}:{password}@{host}:{port}/{database}' ``` Follow best practices for securing connection strings. Refer to [Secure connections](#secure-connections). #### Replicator flags Configure the following MOLT Replicator flags for continuous replication. For details on all available flags, refer to Replicator Flags. | Flag | Description | | ------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `--sourceSchema` | **Required.** Oracle user that owns the tables to replicate. Oracle capitalizes identifiers by default, so use uppercase (for example, `MIGRATION_USER`). | | `--targetSchema` | **Required.** Target schema name on CockroachDB where tables will be replicated. Schema name must be fully qualified in the format `database.schema`. | | `--scn` | **Required.** Snapshot System Change Number (SCN) for the initial changefeed starting point. | | `--backfillFromSCN` | **Required.** SCN of the earliest active transaction at the time of the snapshot. Ensures no transactions are skipped. | | `--stagingSchema` | **Required.** Staging schema name on CockroachDB for replication metadata and checkpoints. Schema name must be fully qualified in the format `database.schema`. | | `--stagingCreateSchema` | Automatically create the staging schema if it does not exist. Include this flag when starting replication for the first time. | | `--metricsAddr` | Enable Prometheus metrics at a specified `{host}:{port}`. Metrics are served at `http://{host}:{port}/_/varz`. | | `--userscript` | Path to a userscript that enables data filtering, routing, or transformations. For examples, refer to Userscript Cookbook. | You can find the SCN values in the message `replication-only mode should include the following replicator flags` after the [initial data load](#start-fetch) completes. #### Tuning parameters Configure the following `replicator` flags to optimize replication throughput and resource usage. Test different combinations in a pre-production environment to find the optimal balance of stability and performance for your workload. The following parameters apply to PostgreSQL, Oracle, and CockroachDB (failback) sources. | Flag | Description | | --------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `--parallelism` | Control the maximum number of concurrent target transactions. Higher values increase throughput but require more target connections. Start with a conservative value and increase based on target database capacity. | | `--flushSize` | Balance throughput and latency. Controls how many mutations are batched into each query to the target. Increase for higher throughput at the cost of higher latency. | | `--targetApplyQueueSize` | Control memory usage during operation. Increase to allow higher throughput at the expense of memory; decrease to apply backpressure and limit memory consumption. | | `--targetMaxPoolSize` | Set larger than `--parallelism` by a safety factor to avoid exhausting target pool connections. Replicator enforces setting parallelism to 80% of this value. | | `--collapseMutations` | Reduce the number of queries to the target by combining multiple mutations on the same primary key within each batch. Disable only if exact mutation order matters more than end state. | | `--enableParallelApplies` | Improve apply throughput for independent tables and table groups that share foreign key dependencies. Increases memory and target connection usage, so ensure you increase `--targetMaxPoolSize` or reduce `--parallelism`. | | `--flushPeriod` | Set to the maximum allowable time between flushes (for example, `10s` if data must be applied within 10 seconds). Works with `--flushSize` to control when buffered mutations are committed to the target. | | `--quiescentPeriod` | Lower this value if constraint violations resolve quickly on your workload to make retries more frequent and reduce latency. Do not lower if constraint violations take time to resolve. | | `--scanSize` | Applies to failback (`replicator start`) scenarios **only**. Balance memory usage and throughput. Increase to read more rows at once from the CockroachDB staging cluster for higher throughput, at the cost of memory pressure. Decrease to reduce memory pressure and increase stability. | #### Replicator metrics MOLT Replicator metrics are not enabled by default. Enable Replicator metrics by specifying the `--metricsAddr` flag with a port (or `host:port`) when you start Replicator. This exposes Replicator metrics at `http://{host}:{port}/_/varz`. For example, the following flag exposes metrics on port `30005`: ``` --metricsAddr :30005 ``` For guidelines on using and interpreting replication metrics, refer to Replicator Metrics. ### Start MOLT Replicator (forward replication) With initial load complete, start replication of ongoing changes on the source to CockroachDB using MOLT Replicator. MOLT Fetch captures a consistent point-in-time checkpoint at the start of the data load (shown as `cdc_cursor` in the fetch output). Starting replication from this checkpoint ensures that all changes made during and after the data load are replicated to CockroachDB, preventing data loss or duplication. The following steps use the checkpoint values from the fetch output to start replication at the correct position. Run the `replicator` command, specifying the backfill and starting SCN from the [checkpoint recorded during data load](#run-molt-fetch). Use `--stagingSchema` to specify a unique name for the staging database, and include `--stagingCreateSchema` to have MOLT Replicator automatically create the staging database. If you [filtered tables during the initial load](#schema-and-table-filtering), write a userscript to filter tables on replication and specify the path with `--userscript`. ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} replicator oraclelogminer \ --sourceConn $SOURCE \ --sourcePDBConn $SOURCE_PDB \ --targetConn $TARGET \ --sourceSchema MIGRATION_USER \ --targetSchema defaultdb.migration_schema \ --backfillFromSCN 26685444 \ --scn 26685786 \ --stagingSchema defaultdb._replicator \ --stagingCreateSchema \ --metricsAddr :30005 \ --userscript table_filter.ts \ -v ``` When filtering out tables in a schema with a userscript, replication performance may decrease because filtered tables are still included in LogMiner queries and processed before being discarded. #### Check that replication is working Verify that Replicator is processing changes successfully. To do so, check the MOLT Replicator logs. Since you enabled debug logging with `-v`, you should see connection and row processing messages: When transactions are read from the Oracle source, you should see registered transaction IDs (XIDs): ``` DEBUG [2025-07-03T15:55:12-05:00] registered xid 0f001f0040060000 DEBUG [2025-07-03T15:55:12-05:00] registered xid 0b001f00bb090000 ``` When rows are successfully replicated, you should see debug output like the following: ``` DEBUG [2025-07-03T15:55:12-05:00] upserted rows conflicts=0 duration=2.620009ms proposed=13 target="\"molt_movies\".\"USERS\".\"CUSTOMER_CONTACT\"" upserted=13 DEBUG [2025-07-03T15:55:12-05:00] upserted rows conflicts=0 duration=2.212807ms proposed=16 target="\"molt_movies\".\"USERS\".\"CUSTOMER_DEVICE\"" upserted=16 ``` These messages confirm successful replication. You can disable verbose logging after verifying the connection. ### Continue MOLT Replicator after an interruption (forward replication) Run the MOLT Replicator `oraclelogminer` command using the same `--stagingSchema` value from your [initial replication command](#start-molt-replicator-forward-replication). Replicator will automatically find the correct restart SCN (System Change Number) from the `_oracle_checkpoint` table in the staging schema. The restart point is determined by the non-committed row with the smallest `startscn` column value. ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} replicator oraclelogminer \ --sourceConn $SOURCE \ --sourcePDBConn $SOURCE_PDB \ --sourceSchema MIGRATION_USER \ --targetSchema defaultdb.migration_schema \ --targetConn $TARGET \ --stagingSchema defaultdb._replicator \ --metricsAddr :30005 \ --userscript table_filter.ts \ -v ``` When filtering out tables in a schema with a userscript, replication performance may decrease because filtered tables are still included in LogMiner queries and processed before being discarded. Replication resumes from the last checkpoint without performing a fresh load. Monitor the metrics endpoint at `http://localhost:30005/_/varz` to track replication progress. ## Step 7: Stop application traffic Once the inital data load has been verified and the target schema has been finalized, it's time to begin the cutover process. First, stop application traffic to the source for this particular region. If the Kubernetes cluster that deploys the application has pre-region deployments (for example, `app-us`, `app-eu`, `app-apac`), you can scale down only the deployment for that region. ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} kubectl scale deploy/app-eu --replicas=0 ``` Or this can be handled by the NGINX Ingress Controller, by including the following to your NGINX configuration, ensuring that the conditional statement is suitable for your deployment: ```yml theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} apiVersion: networking.k8s.io/v1 kind: Ingress metadata: name: app annotations: nginx.ingress.kubernetes.io/server-snippet: | if ($http_x_region = "eu") { return 503; } spec: ingressClassName: nginx rules: - host: api.example.com http: paths: - path: / pathType: Prefix backend: service: name: app port: number: 80 ``` Application downtime begins now, for users in the given region. It is strongly recommended that you perform a dry run of this migration in a test environment. This will allow you to practice using the MOLT tools in real time, and it will give you an accurate sense of how long application downtime might last. ## Step 8: Stop forward replication Before you can cut over traffic to the target, the changes to the source database need to finish being written to the target. Once the source is no longer receiving write traffic, MOLT Replicator will take some seconds to finish replicating the final changes. This is known as *drainage*. 1. Wait for replication to drain, which means that all transactions that occurred on the source database have been fully processed and replicated. There are several ways to determine that replication has fully drained: * When replication is caught up, you will not see new `upserted rows` logs. * If you set up the replication metrics endpoint with `--metricsAddr` in the preceding steps, metrics are available at: ``` http://{host}:{port}/_/varz ``` Use the following Prometheus alert expression to observe when the combined rate of upserts and deletes is `0` for each schema: ``` sum by (schema) (rate(apply_upserts_total[$__rate_interval]) + rate(apply_deletes_total[$__rate_interval])) ``` * You can also check Prometheus metrics associated with replication lag, including `source_commit_to_apply_lag_seconds`. 2. Cancel replication by entering `ctrl-c` to issue a `SIGTERM` signal. This returns an exit code `0`. ## Step 9: Verify the replicated data Repeat [Step 4](#step-4-verify-the-initial-data-load) to verify the updated data. ## Step 10: Begin failback replication In this step, you will: * [Prepare both databases for failback replication](#prepare-your-source-and-target-databases-for-failback-replication) * [Configure MOLT Replicator with the flags needed for your migration](#configure-molt-replicator-failback-replication). * [Start MOLT Replicator](#start-molt-replicator-failback-replication). * [Understand how to continue replication after an interruption](#continue-molt-replicator-after-an-interruption-failback-replication). ### Prepare your source and target databases for failback replication #### Prepare the CockroachDB cluster For details on enabling CockroachDB changefeeds, refer to Create and Configure Changefeeds. If you are migrating to a CockroachDB self-hosted cluster, enable rangefeeds on the cluster: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SET CLUSTER SETTING kv.rangefeed.enabled = true; ``` Use the following optional settings to increase changefeed throughput. The following settings can impact source cluster performance and stability, especially SQL foreground latency during writes. For details, refer to Advanced Changefeed Configuration. To lower changefeed emission latency, but increase SQL foreground latency: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SET CLUSTER SETTING kv.rangefeed.closed_timestamp_refresh_interval = '250ms'; ``` To lower the closed timestamp lag duration: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SET CLUSTER SETTING kv.closed_timestamp.target_duration = '1s'; ``` To improve catchup speeds but increase cluster CPU usage: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SET CLUSTER SETTING kv.rangefeed.concurrent_catchup_iterators = 64; ``` #### Grant target database user permissions You should have already created a migration user on the target database (your **original source database**) with the necessary privileges. Refer to [Create migration user on source database](#create-migration-user-on-source-database). For failback replication, grant the user additional privileges to write data back to the target database: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} -- Grant INSERT, UPDATE, and FLASHBACK on tables to fail back to GRANT SELECT, INSERT, UPDATE, FLASHBACK ON migration_schema.employees TO MIGRATION_USER; GRANT SELECT, INSERT, UPDATE, FLASHBACK ON migration_schema.payments TO MIGRATION_USER; GRANT SELECT, INSERT, UPDATE, FLASHBACK ON migration_schema.orders TO MIGRATION_USER; ``` #### Create a CockroachDB changefeed On the target cluster, create a CockroachDB changefeed to send changes to MOLT Replicator. 1. Get the current logical timestamp from CockroachDB, after [ensuring that forward replication has fully drained](#step-8-stop-forward-replication): ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SELECT cluster_logical_timestamp(); ``` ``` cluster_logical_timestamp ---------------------------------- 1759246920563173000.0000000000 ``` 2. Create the CockroachDB changefeed pointing to the MOLT Replicator webhook endpoint. Use `cursor` to specify the logical timestamp from the preceding step. For details on the webhook sink URI, refer to Webhook sink. Explicitly set a default `10s` `webhook_client_timeout` value in the `CREATE CHANGEFEED` statement. This value ensures that the webhook can report failures in inconsistent networking situations and make crash loops more visible. The webhook URL path specifies the schema name on the target Oracle database. Oracle capitalizes identifiers by default. For example, `/MIGRATION_SCHEMA` routes changes to the `MIGRATION_SCHEMA` schema. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE CHANGEFEED FOR TABLE employees, payments, orders \ INTO 'webhook-https://replicator-host:30004/MIGRATION_SCHEMA?client_cert={base64_encoded_cert}&client_key={base64_encoded_key}&ca_cert={base64_encoded_ca}' \ WITH updated, resolved = '250ms', min_checkpoint_frequency = '250ms', initial_scan = 'no', cursor = '1759246920563173000.0000000000', webhook_sink_config = '{"Flush":{"Bytes":1048576,"Frequency":"1s"}}', webhook_client_timeout = '10s'; ``` The output shows the job ID: ``` job_id ----------------------- 1101234051444375553 ``` Ensure that only **one** changefeed points to MOLT Replicator at a time to avoid mixing streams of incoming data. 3. Monitor the changefeed status, specifying the job ID: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SHOW CHANGEFEED JOB 1101234051444375553; ``` ``` job_id | ... | status | running_status | ... ----------------------+-----+---------+-------------------------------------------+---- 1101234051444375553 | ... | running | running: resolved=1759246920563173000,0 | ... ``` To confirm the changefeed is active and replicating changes to the target database, check that `status` is `running` and `running_status` shows `running: resolved={timestamp}`. `running: resolved` may be reported even if data isn't being sent properly. This typically indicates incorrect host/port configuration or network connectivity issues. 4. Verify that Replicator is reporting incoming HTTP requests from the changefeed. To do so, check the MOLT Replicator logs. Since you enabled debug logging with `-v`, you should see periodic HTTP request successes: ``` DEBUG [2025-08-25T11:52:47-05:00] httpRequest="&{0x14000b068c0 45 200 3 9.770958ms false false}" DEBUG [2025-08-25T11:52:48-05:00] httpRequest="&{0x14000d1a000 45 200 3 13.438125ms false false}" ``` These debug messages confirm successful changefeed connections to MOLT Replicator. You can disable verbose logging after verifying the connection. ### Configure MOLT Replicator (failback replication) When you run `replicator`, you can configure the following options for replication: * [Connection strings](#connection-strings): Specify URL‑encoded source and target connections. * [TLS certificate and key](#tls-certificate-and-key): Configure secure TLS connections. * [Replicator flags](#replicator-flags): Specify required and optional flags to configure replicator behavior. * [Tuning parameters](#tuning-parameters): Optimize failback performance and resource usage. * [Replicator metrics](#replicator-metrics): Monitor failback replication performance. #### Replication connection strings MOLT Replicator uses `--sourceConn` and `--targetConn` to specify the source and target database connections. For MOLT Replicator, the source is always the **replication** source, while the target is always the **replication** target. This is distinct from the **migration** source and target. In the case of this example migration, the new CockroachDB cluster is the migration target, but because failback replication moves data from the migration target back to the migration source, the **replication** target is the original source database. In essence, the `--sourceConn` and `--targetConn` strings should be reversed for failback replication. `--sourceConn` specifies the connection string of the CockroachDB cluster: ``` --sourceConn 'postgresql://{username}:{password}@{host}:{port}/{database}' ``` `--targetConn` specifies the original source database: ``` --targetConn 'oracle://{username}:{password}@{host}:{port}/{service_name}' ``` Follow best practices for securing connection strings. Refer to [Secure connections](#secure-connections). ##### Secure connections * 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 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: ```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. #### TLS certificate and key Always use **secure TLS connections** for failback replication to protect data in transit. Do **not** use insecure configurations in production: avoid the `--disableAuthentication` and `--tlsSelfSigned` Replicator flags and `insecure_tls_skip_verify=true` query parameter in the changefeed webhook URI. Generate self-signed TLS certificates or certificates from an external CA. Ensure the TLS server certificate and key are accessible on the MOLT Replicator host machine via a relative or absolute file path. When you [start failback with Replicator](#start-replicator), specify the paths with `--tlsCertificate` and `--tlsPrivateKey`. For example: ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} replicator start \ ... \ --tlsCertificate ./certs/server.crt \ --tlsPrivateKey ./certs/server.key ``` The client certificates defined in the changefeed webhook URI must correspond to the server certificates specified in the `replicator` command. This ensures proper TLS handshake between the changefeed and MOLT Replicator. To include client certificates in the changefeed webhook URL, encode them with `base64` and then URL-encode the output with `jq`: ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} base64 -i ./client.crt | jq -R -r '@uri' base64 -i ./client.key | jq -R -r '@uri' base64 -i ./ca.crt | jq -R -r '@uri' ``` When you [create the changefeed](#create-a-cockroachdb-changefeed), pass the encoded certificates in the changefeed URL, where `client_cert`, `client_key`, and `ca_cert` are webhook sink parameters: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE CHANGEFEED FOR TABLE table1, table2 INTO 'webhook-https://host:port/database/schema?client_cert={base64_and_url_encoded_cert}&client_key={base64_and_url_encoded_key}&ca_cert={base64_and_url_encoded_ca}' WITH ...; ``` For additional details on the webhook sink URI, refer to Webhook sink. #### Replicator flags | Flag | Description | | -------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `--stagingSchema` | **Required.** Staging schema name on CockroachDB for the changefeed checkpoint table. Schema name must be fully qualified in the format `database.schema`. | | `--bindAddr` | **Required.** Network address to bind the webhook sink for the changefeed. For example, `:30004`. | | `--tlsCertificate` | Path to the server TLS certificate for the webhook sink. Refer to [TLS certificate and key](#tls-certificate-and-key). | | `--tlsPrivateKey` | Path to the server TLS private key for the webhook sink. Refer to [TLS certificate and key](#tls-certificate-and-key).Q | | `--metricsAddr` | Enable Prometheus metrics at a specified `{host}:{port}`. Metrics are served at `http://{host}:{port}/_/varz`. | | `--userscript` | Path to a userscript that enables data filtering, routing, or transformations. For examples, refer to Userscript Cookbook. | * The staging schema is first created during initial replication setup with `--stagingCreateSchema`. * When configuring a [secure changefeed](#tls-certificate-and-key) for failback, you **must** include `--tlsCertificate` and `--tlsPrivateKey`, which specify the paths to the server certificate and private key for the webhook sink connection. ### Tuning parameters Configure the following `replicator` flags to optimize replication throughput and resource usage. Test different combinations in a pre-production environment to find the optimal balance of stability and performance for your workload. The following parameters apply to PostgreSQL, Oracle, and CockroachDB (failback) sources. | Flag | Description | | --------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `--parallelism` | Control the maximum number of concurrent target transactions. Higher values increase throughput but require more target connections. Start with a conservative value and increase based on target database capacity. | | `--flushSize` | Balance throughput and latency. Controls how many mutations are batched into each query to the target. Increase for higher throughput at the cost of higher latency. | | `--targetApplyQueueSize` | Control memory usage during operation. Increase to allow higher throughput at the expense of memory; decrease to apply backpressure and limit memory consumption. | | `--targetMaxPoolSize` | Set larger than `--parallelism` by a safety factor to avoid exhausting target pool connections. Replicator enforces setting parallelism to 80% of this value. | | `--collapseMutations` | Reduce the number of queries to the target by combining multiple mutations on the same primary key within each batch. Disable only if exact mutation order matters more than end state. | | `--enableParallelApplies` | Improve apply throughput for independent tables and table groups that share foreign key dependencies. Increases memory and target connection usage, so ensure you increase `--targetMaxPoolSize` or reduce `--parallelism`. | | `--flushPeriod` | Set to the maximum allowable time between flushes (for example, `10s` if data must be applied within 10 seconds). Works with `--flushSize` to control when buffered mutations are committed to the target. | | `--quiescentPeriod` | Lower this value if constraint violations resolve quickly on your workload to make retries more frequent and reduce latency. Do not lower if constraint violations take time to resolve. | | `--scanSize` | Applies to failback (`replicator start`) scenarios **only**. Balance memory usage and throughput. Increase to read more rows at once from the CockroachDB staging cluster for higher throughput, at the cost of memory pressure. Decrease to reduce memory pressure and increase stability. | #### Replicator metrics MOLT Replicator metrics are not enabled by default. Enable Replicator metrics by specifying the `--metricsAddr` flag with a port (or `host:port`) when you start Replicator. This exposes Replicator metrics at `http://{host}:{port}/_/varz`. For example, the following flag exposes metrics on port `30005`: ``` --metricsAddr :30005 ``` For guidelines on using and interpreting replication metrics, refer to Replicator Metrics. ### Start MOLT Replicator (failback replication) With initial load complete, start replication of ongoing changes on the source to CockroachDB using MOLT Replicator. MOLT Fetch captures a consistent point-in-time checkpoint at the start of the data load (shown as `cdc_cursor` in the fetch output). Starting replication from this checkpoint ensures that all changes made during and after the data load are replicated to CockroachDB, preventing data loss or duplication. The following steps use the checkpoint values from the fetch output to start replication at the correct position. Run the MOLT Replicator `start` command to begin failback replication from CockroachDB to your source database. In this example, `--metricsAddr :30005` enables a Prometheus endpoint for monitoring replication metrics, and `--bindAddr :30004` sets up the webhook endpoint for the changefeed. `--stagingSchema` specifies the staging database name (`defaultdb._replicator` in this example) used for replication checkpoints and metadata. This staging database was created during [initial forward replication](#step-6-begin-forward-replication) when you first ran MOLT Replicator with `--stagingCreateSchema`. ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} replicator start \ --targetConn $TARGET \ --stagingConn $STAGING \ --stagingSchema defaultdb._replicator \ --metricsAddr :30005 \ --bindAddr :30004 \ --tlsCertificate ./certs/server.crt \ --tlsPrivateKey ./certs/server.key \ -v ``` ### Continue MOLT Replicator after an interruption (failback replication) Run the MOLT Replicator `oraclelogminer` command using the same `--stagingSchema` value from your [initial replication command](#start-molt-replicator-failback-replication). Replicator will automatically find the correct restart SCN (System Change Number) from the `_oracle_checkpoint` table in the staging schema. The restart point is determined by the non-committed row with the smallest `startscn` column value. ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} replicator oraclelogminer \ --sourceConn $SOURCE \ --sourcePDBConn $SOURCE_PDB \ --sourceSchema MIGRATION_USER \ --targetSchema defaultdb.migration_schema \ --targetConn $TARGET \ --stagingSchema defaultdb._replicator \ --metricsAddr :30005 \ --userscript table_filter.ts \ -v ``` When filtering out tables in a schema with a userscript, replication performance may decrease because filtered tables are still included in LogMiner queries and processed before being discarded. Replication resumes from the last checkpoint without performing a fresh load. Monitor the metrics endpoint at `http://localhost:30005/_/varz` to track replication progress. ## Step 11: Cut over application traffic With the target cluster verified and finalized, it's time to resume application traffic for the current migration phase. ### Modify application code In the application back end, update the application to route traffic for this migration phase to the CockroachDB cluster. A simple example: ```yml theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} env: - name: DATABASE_URL_US_EAST value: postgres://root@cockroachdb.us-east:26257/defaultdb?sslmode=verify-full - name: DATABASE_URL_US_WEST value: postgres://legacy-db.us-west:5432/defaultdb # Still on source ``` In your application code, route database connections based on the user's region: ```python theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} def get_db_connection(user_region): if user_region == "us-east": return os.getenv("DATABASE_URL_US_EAST") # CockroachDB else: return os.getenv("DATABASE_URL_US_WEST") # Source database ``` ### Resume application traffic If you halted traffic by scaling down a regional Kubernetes deployment, scale it back up. ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} kubectl scale deploy/app-eu --replicas=3 ``` Or if this was handled by the NGINX Controller, remove the 503 block that was written in step 3: ```yml theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} apiVersion: networking.k8s.io/v1 kind: Ingress metadata: name: app # annotations: # nginx.ingress.kubernetes.io/server-snippet: | # if ($http_x_region = "eu") { # return 503; # } spec: ingressClassName: nginx rules: - host: api.example.com http: paths: - path: / pathType: Prefix backend: service: name: app port: number: 80 ``` This ends downtime for the current migration phase. ## Step 12: Stop failback replication After traffic has been cut over to the target, you can maintain failback replication indefinitely. Once you decide that you want to use the CockroachDB cluster as your sole data store going forward, you can end failback replication with the following steps. 1. Wait for replication to drain, which means that all transactions that occurred on the source database have been fully processed and replicated. There are several ways to determine that replication has fully drained: * When replication is caught up, you will not see new `upserted rows` logs. * If you set up the replication metrics endpoint with `--metricsAddr` in the preceding steps, metrics are available at: ``` http://{host}:{port}/_/varz ``` Use the following Prometheus alert expression to observe when the combined rate of upserts and deletes is `0` for each schema: ``` sum by (schema) (rate(apply_upserts_total[$__rate_interval]) + rate(apply_deletes_total[$__rate_interval])) ``` * You can also check Prometheus metrics associated with replication lag, including `source_commit_to_apply_lag_seconds`. 2. Cancel replication by entering `ctrl-c` to issue a `SIGTERM` signal. This returns an exit code `0`. ## Repeat for each phase During the next scheduled migration phase, [return to step 3](#step-3-load-data-into-cockroachdb) to migrate the next phase of data. Repeat steps 3-12 for each phase of data, until every region's data has been migrated and all application traffic has been cut over to the target. ## See also * Migration Overview * Migration Considerations * Phased Bulk Load Migration * MOLT Fetch * MOLT Verify * MOLT Fetch Troubleshooting for Oracle * MOLT Replicator Troubleshooting for Oracle