Migrate Your Database to CockroachDB

On this page Carat arrow pointing down

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:

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:

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:

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


Yes No
On this page

Yes No