Migrate Your Database to CockroachDB

This page summarizes the steps of migrating a database to CockroachDB:

  1. Test and update your schema to work with CockroachDB.
  2. Move your data into CockroachDB.
  3. Test and update your application.
Note:

If you need to migrate data from a CockroachDB Serverless cluster to a CockroachDB Dedicated cluster, see Migrate data from Serverless to Dedicated.

Step 1. Test and update your schema

To begin a new migration to CockroachDB, extract the data definition language (DDL) of the source database. We strongly recommend migrating your database schema to a new CockroachDB database before migrating the data.

You will likely need to update your schema by converting the data definition statements to CockroachDB-compatible statements. This can be due to:

If you are migrating from a PostgreSQL database, use the CockroachDB MOLT (Migrate Off Legacy Technology) tools:

  • Use the Schema Conversion Tool on the CockroachDB Cloud Console to analyze your schema for SQL incompatibilities. The tool will identify and help you resolve errors in your schema, and then create a new CockroachDB database with the converted schema.
  • Move your data to the new database using AWS DMS.

Unimplemented features

CockroachDB supports the PostgreSQL wire protocol and is largely compatible with PostgreSQL syntax. However, the following PostgreSQL features do not yet exist in CockroachDB:

If your source database uses any of the preceding features, you may need to implement workarounds in your schema design, in your data manipulation language (DML) when moving data to the new database, or in your application code.

For more details on the CockroachDB SQL implementation, see SQL Feature Support.

Differences from other databases

Consider the following CockroachDB attributes and best practices:

  • When importing data, we recommend that you always have an explicit primary key defined on every table. For more information, see Primary key best practices.

  • Instead of using a sequence to define a primary key column, we recommend that you use multi-column primary keys or the UUID datatype for primary key columns. For more information, see CREATE SEQUENCE.

    • For performance reasons, we discourage indexing on sequential keys. If, however, you are working with a table that must be indexed on sequential keys, you should use hash-sharded indexes. Hash-sharded indexes distribute sequential traffic uniformly across ranges, eliminating single-range hot spots and improving write performance on sequentially-keyed indexes at a small cost to read performance.
  • By default on CockroachDB, INT is an alias for INT8, which creates 64-bit signed integers. Depending on your source database or application requirements, you may need to change the integer size to 4. For example, PostgreSQL defaults to 32-bit integers. For more information, see Considerations for 64-bit signed integers.

For additional considerations specific to other databases and data formats, see the corresponding documentation linked in Step 2. Move your data to CockroachDB.

Step 2. Move your data to CockroachDB

We recommend using AWS Database Migration Service (DMS) to migrate data from any database, such as PostgreSQL, MySQL, or Oracle, to CockroachDB.

Alternatively, use IMPORT to migrate CSV data.

You can also migrate data from the following data formats:

Tip:

For best practices for optimizing import performance in CockroachDB, see Import Performance Best Practices.

Step 3. Test and update your application

As the final step of migration, you will likely need to make changes to how your application interacts with the database. For example, refer to features that differ from PostgreSQL.

Unless you changed the integer size when migrating the schema, your application should also be written to handle 64-bit integers. For more information, see Considerations for 64-bit signed integers.

We strongly recommend testing your application against CockroachDB to ensure that:

  1. The state of your data is what you expect post-migration.
  2. Performance is sufficient for your application's workloads. Follow the SQL Performance Best Practices and implement transaction retry logic.

See also


Yes No

Yes No