Migrate Your Database to CockroachDB

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

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

The following MOLT (Migrate Off Legacy Technology) tools simplify migration:

• Schema Conversion Tool.
• Support for AWS DMS.

Step 1. Convert your schema

To begin a new migration to CockroachDB, convert your database schema to an equivalent CockroachDB schema.

1. Use the source database's tooling to extract the data definition language (DDL) to a .sql file.

2. Upload the .sql file to the Schema Conversion Tool on the CockroachDB Cloud Console. The tool will convert the syntax, identify unimplemented features in the schema, and suggest edits according to CockroachDB best practices.

   Note:

   The Schema Conversion Tool accepts .sql files from PostgreSQL, MySQL, Oracle, and Microsoft SQL Server.

3. The Schema Conversion Tool automatically creates a new CockroachDB Serverless database with the converted schema. If you are migrating to a CockroachDB Self-Hosted database, you can export the converted schema from the Schema Conversion Tool and execute the statements in cockroach sql, or use a third-party schema migration tool such as Alembic, Flyway, or Liquibase.

4. Move your data to the new database.

Schema design best practices

Consider the following CockroachDB attributes and best practices:

• 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 auto-generating unique IDs 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.

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
  • New in v22.2: CockroachDB has support for user-defined functions.
• Triggers.
• Events.
• FULLTEXT functions and indexes.
  • Depending on your use case, you may be able to get by using trigram indexes to do fuzzy string matching and pattern matching.

• Drop primary key.

  Note:

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

• XML functions.

• Column-level privileges.

• XA syntax.

• Creating a database from a template.

• Dropping a single partition from a table.

• Foreign data wrappers.

• Advisory Lock Functions (although some functions are defined with no-op implementations).

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.

Step 2. Move your data to CockroachDB

To migrate data from another database to CockroachDB, use the source database's tooling to extract the data to a .sql file. Then use one of the following methods to migrate the data:

• Use COPY FROM to copy the data to your CockroachDB tables. This option behaves identically to the PostgreSQL syntax and is recommended if your tables must remain online and accessible during a migration or continuous bulk ingestion of data. However, it is slower than using IMPORT INTO because the statements are executed through a single node on the cluster.
• Use IMPORT INTO to migrate CSV or Avro data into pre-existing tables. This option is recommended if you can tolerate your tables being offline, such as during an initial migration to CockroachDB. It is faster than using COPY FROM because the statements are distributed across multiple nodes.
• Use AWS Database Migration Service (DMS) to migrate data from any database, such as PostgreSQL, MySQL, or Oracle, to CockroachDB. This option is recommended for near-zero downtime migrations of large data sets, either with ongoing replication or as a one-time migration of a snapshot.
  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

• Migrations Page
• Can a PostgreSQL or MySQL application be migrated to CockroachDB?
• PostgreSQL Compatibility
• Schema Design Overview
• Create a Database
• Create a User-defined Schema
• Back Up and Restore