Migrate Your Database to CockroachDB

On this page Carat arrow pointing down
Warning:
CockroachDB v21.2 is no longer supported as of May 16, 2023. For more details, refer to the Release Support Policy.

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 PostgreSQL, MySQL, Oracle, or Microsoft SQL Server, 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:

  • Stored procedures and functions
  • Triggers
  • Events
  • User-defined functions (UDF)
  • FULLTEXT functions and indexes
  • Drop primary key*
  • XML Functions
  • Column-level privileges
  • XA syntax
  • Creating a database from a template
  • Dropping a single partition from a table

* Each table must have a primary key associated with it. You can, however, drop and add a primary key constraint within a single transaction.

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:

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
On this page

Yes No