This page summarizes the steps of migrating a database to CockroachDB:
- Test and update your schema to work with CockroachDB.
- Move your data into CockroachDB.
- Test and update your application.
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:
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 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.
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:
- Avro
- ESRI Shapefiles (
.shp
) (usingshp2pgsql
) - OpenStreetMap data files (
.pbf
) (usingosm2pgsql
) - GeoPackage data files (
.gpkg
) (usingogr2ogr
) - GeoJSON data files (
.geojson
) (usingogr2ogr
)
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.