This page summarizes the steps of migrating a database to CockroachDB:
- Convert your schema to work with CockroachDB.
- Move your data into CockroachDB.
- Test and update your application.
The following MOLT (Migrate Off Legacy Technology) tools simplify migration:
- Schema Conversion Tool.
- Support for AWS DMS.
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. Convert your schema
To begin a new migration to CockroachDB, convert your database schema to an equivalent CockroachDB schema.
- Use the source database's tooling to extract the data definition language (DDL) to a
.sql
file. 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.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.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 forINT8
, which creates 64-bit signed integers. Depending on your source database or application requirements, you may need to change the integer size to4
. 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.
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 usingIMPORT 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 usingCOPY 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:
- The state of your data is what you expect post-migration.
- Performance is sufficient for your application's workloads. Follow the SQL Performance Best Practices and implement transaction retry logic.