Striim offers a managed service and a self-hosted platform that you can use to do the following:
- Migrate data to CockroachDB from an existing, publicly hosted database containing application data, such as PostgreSQL, MySQL, Oracle, or Microsoft SQL Server.
As of this writing, Striim supports the following database sources:
- HP NonStop SQL/MX
- MariaDB
- MariaDB Galera Cluster
- MySQL
- Oracle
- PostgreSQL
- SQL Server
- Sybase
- Teradata
This page describes the Striim functionality at a high level. For detailed information, refer to the Striim documentation.
Before you begin
Complete the following items before using Striim:
Ensure you have a secure, publicly available CockroachDB cluster running the latest v23.1 production release, and have created a SQL user that you can use to configure your Striim target.
Manually create all schema objects in the target CockroachDB cluster. Although Striim offers a feature called Auto Schema Conversion, we recommend converting and importing your schema before running Striim to ensure that the data populates successfully.
- If you are migrating from PostgreSQL, MySQL, Oracle, or Microsoft SQL Server, use the Schema Conversion Tool to convert and export your schema. Ensure that any schema changes are also reflected on your tables.
Note:All tables must have an explicitly defined primary key. For more guidance, see the Migration Overview.
Migrate and replicate data to CockroachDB
You can use Striim to migrate tables from a source database to CockroachDB. This can comprise an initial load that copies the selected schemas and their data from the source database to CockroachDB, followed by continuous replication of ongoing changes using Striim change data capture (CDC).
Initial load
To perform the initial load, create a Striim application and configure the source database using one of the Initial Load sources. Configure CockroachDB as a PostgreSQL target. For information about where to find the CockroachDB connection parameters, see Connect to a CockroachDB Cluster. Do the following before deploying the application:
Specify the Connection URL in JDBC format while appending the
reWriteBatchedInserts=true
property, and without specifying the username. For example:jdbc:postgresql://{host}:{port}/{database}?password={password}&sslmode=verify-full&reWriteBatchedInserts=true
After creating the target, export the application and add the field
_h_ConnectionRetryCode: '40001'
to the TQL file. For example:CREATE OR REPLACE TARGET cockroach USING Global.DatabaseWriter ( ConnectionRetryPolicy: 'retryInterval=30, maxRetries=3', ParallelThreads: '5', CheckPointTable: 'CHKPOINT', BatchPolicy: 'EventCount:128,Interval:60', Password_encrypted: 'true', StatementCacheSize: '4', CDDLAction: 'Process', Password: 'xxxxx', Tables: 'public.test_table_large,public.test_table_large', CommitPolicy: 'EventCount:128,Interval:60', DatabaseProviderType: 'Postgres', PreserveSourceTransactionBoundary: 'false', ConnectionURL: 'jdbc:postgresql://{host}:{port}/{database}?password={password}&sslmode=verify-full&reWriteBatchedInserts=true', Username: 'root', _h_ConnectionRetryCode: '40001', adapterName: 'DatabaseWriter' )
Then import the modified TQL file to create a new application.
To minimize downtime for your migration, configure a separate continuous replication application before you deploy the initial load application. Once the initial load is complete, deploy the continuous replication application.
Deploy this application to perform the initial load of data to CockroachDB. Remember that you should have already created the schema objects on CockroachDB.
Continuous replication
To perform continuous replication of ongoing changes, create another Striim application and configure the source database using one of the CDC sources. Configure CockroachDB as a PostgreSQL target. For information about where to find the CockroachDB connection parameters, see Connect to a CockroachDB Cluster. Do the following before deploying the application:
Configure the CDC reader as described in the Striim documentation.
Set up your source database for continuous replication as described in the Striim for BigQuery documentation.
Repeat the guidance for creating the initial load application:
- When configuring CockroachDB as a target, specify the Connection URL in JDBC format while appending the
reWriteBatchedInserts=true
property, and without specifying the username. - After creating the target, export the application and add the field
_h_ConnectionRetryCode: '40001'
to the TQL file. Then import the modified TQL file to create a new application.
- When configuring CockroachDB as a target, specify the Connection URL in JDBC format while appending the
Deploy this application once the initial load application has finished running.