Migrate Your Database to CockroachDB

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.

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:

• Unimplemented features.
• Differences from other databases.

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 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.

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) (using shp2pgsql)
• OpenStreetMap data files (.pbf) (using osm2pgsql)
• GeoPackage data files (.gpkg) (using ogr2ogr)
• GeoJSON data files (.geojson) (using ogr2ogr)

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
• Create a Database
• Create a User-defined Schema
• Back Up and Restore