Migrations Page

The Migrations page on the CockroachDB Cloud Console features a Schema Conversion Tool that helps you:

• Convert a schema from a PostgreSQL, MySQL, Oracle, or Microsoft SQL Server database for use with CockroachDB.
• Create a new database that uses the converted schema. You specify the target database and database owner when finalizing the schema.

To view this page, click Migrations in the left navigation of the CockroachDB Cloud Console. The Migrations tab is selected.

Upload a SQL dump

The upload box for the Schema Conversion Tool is displayed at the top of the Migrations page.

The Schema Conversion Tool expects to analyze a SQL dump file containing data definition statements that create a database schema. The exact steps depend on the dialect from which you are migrating.

Note:

This feature is in preview. This feature is subject to change. To share feedback and/or issues, contact Support.

To generate an appropriate file, run the pg_dump utility and specify the -s or --schema-only options to extract only the schema of a PostgreSQL database to a .sql file.

To generate an appropriate file, run the mysqldump utility and specify the -d or --no-data options to extract only the schema of the MySQL database to a .sql file.

To generate an appropriate file, run the expdp utility to extract only the schema of the Oracle database to a .sql file.

To generate an appropriate file, use either SQL Server Management Studio or the equivalent mssql-scripter utility to extract only the schema of the SQL Server database to a .sql file.

The dump file must be smaller than 4 MB. INSERT and COPY statements will be ignored in schema conversion. To begin your database migration:

1. Click the upload box and select a .sql file, or drop a .sql file directly into the box.
2. Select your Dialect from the pulldown menu and configure the following conversion defaults:
INT type conversion: On CockroachDB, INT is an alias for INT8, which creates 64-bit signed integers. On PostgreSQL, INT defaults to INT4. For details, see Differences from other databases.
Casing of Identifiers: Select Keep case sensitivity to enclose identifiers in double-quotes, and Make case insensitive to convert identifiers to lowercase. For details on how CockroachDB handles identifiers, see Identifiers.
AUTO_INCREMENT Conversion Option: We do not recommend using a sequence to define a primary key column. For details, see Differences from other databases. To understand the differences between the UUID and unique_rowid() options, see the SQL FAQs.
Enum Preferences: On CockroachDB, ENUMS are a standalone type. On MySQL, they are part of column definitions. You can select to either deduplicate the ENUM definitions or create a separate type for each column.
GENERATED AS IDENTITY Conversion Option: We do not recommend using a sequence to define a primary key column. For details, see Differences from other databases. To understand the differences between the UUID and unique_rowid() options, see the SQL FAQs.

IDENTITY Conversion Option: We do not recommend using a sequence to define a primary key column. For details, see Differences from other databases. To understand the differences between the UUID and unique_rowid() options, see the SQL FAQs.
3. Click Migrate and wait for the schema to be analyzed. A loading screen is displayed. Depending on the size and complexity of the SQL dump, analyzing the schema can require up to several minutes.
4. When analysis is complete, review the Summary Report and edit, add, or remove SQL statements in the Statements list.

Migrations table

If you have attempted at least one migration, the Migrations table is displayed with the following details:

Column	Description
Migration Name	The filename of the .sql file that was uploaded.
Status	The status of the migration: READY FOR REVIEW, READY TO FINALIZE, or FINALIZED. You can finalize migrations with READY TO FINALIZE status.
Date Imported	The timestamp when the SQL dump was uploaded.
Last Updated	The timestamp when the SQL statements were updated.
Errors	The number of SQL errors preventing a migration from attaining READY TO FINALIZE status.

To view the Summary Report or Statements list for a migration, click the migration name.

Summary Report

The Summary Report displays the results of the schema analysis:

• The number of Statements Total in the uploaded .sql file that were analyzed.
• The number of Errors in SQL statements that are blocking finalization. Errors are further categorized and counted on the Statement Status graph.
• The number of Incidental Errors in SQL statements that are caused by errors in other SQL statements.

The number of Incompatible Statements that could not be converted because they have no equivalent syntax on CockroachDB.

The number of Compatibility Notes regarding differences in SQL syntax. Although these statements do not block finalization, you should update them before finalization.
• The number of Suggestions regarding CockroachDB best practices.

To review and update the schema, click View Statements or the Statements tab to open the Statements list.

To finalize the schema and create a new database for migration, click Finalize Schema. The schema must have zero errors.

Statement Status

The Statement Status graph displays the number of successful statements (green), the number of failed statements (red), and the number of incidental errors (orange):

• OK represents a successful statement.
• Unimplemented Feature represents a statement that uses an unimplemented feature.
• Statement Error represents a statement that failed for a reason other than a missing user, unimplemented feature, or incompatible syntax.
• Not Executed represents a statement that was not executed by the tool, such as an INSERT or COPY statement.
• Missing User represents a statement that references a nonexistent user.

Incompatible represents a statement that could not be converted because it has no equivalent syntax on CockroachDB.
• Incidental Error represents a statement that failed because another SQL statement encountered one of the preceding error types.

Suggestions

The Suggestions graph displays the number of each suggestion type:

• Sequences represents a statement that uses a sequence to define a primary key column. Using a sequence for a primary key column is not recommended.
• Default INT Size represents a statement that was added to change the integer size to 4. By default, CockroachDB uses INT8. If you don't want to change the integer size, you can remove this statement in the Statements list.
• Missing Primary Key represents a statement that does not define an explicit primary key for a table. Defining an explicit primary key on every table is recommended.

Statements list

The Statements list displays the result of analyzing each statement in the .sql file that you uploaded. The numbers from the Summary Report are displayed above the list of statements.

To finalize the schema and create a new database for migration, click Finalize Schema. The schema must have zero errors.

If the Finalize Schema button is disabled, use the Statements list to update the schema. Navigate the list by scrolling or by clicking the arrows and Scroll to Top button on the bottom-right.

By default, the Statements list displays both successful and failed statements. To view only the statements that failed, check Collapse successful statements.

Statements are displayed as follows:

• A statement that succeeded is displayed without further detail.
• A statement that failed is displayed with [error] and a message with error details. If the failure was due to an incidental error, the message also states: This error may automatically resolve once an earlier statement no longer errors.
A statement that failed due to incompatible syntax is displayed with [incompat], a message with syntax details, and an Acknowledge checkbox.

A statement that has a SQL compatibility issue is displayed with [compat note], a message with syntax details, and an Acknowledge checkbox.

Note:

Some statements with compatibility issues are automatically removed during conversion. If a statement was removed from the schema, this is stated in the statement's [incompat] or [compat note] message.

• A statement that has a suggestion is displayed with [suggestion], a message with suggestion details, and an Acknowledge checkbox.

To edit a statement, click the Edit button or the statement itself and enter your changes. Your changes are saved when you click outside the statement, or when you click the Save button. Click Cancel to discard your changes.

To remove or add a statement, click the ellipsis above the statement and then click Delete statement, Add statement above, or Add statement below.

Update the schema

Respond to errors and suggestions according to the following guidelines:

Type	Solution	Required for finalization
Unimplemented feature	The feature does not yet exist on CockroachDB. Implement a workaround by editing the statement and adding statements. Otherwise, remove the statement from the schema. If a link to a tracking issue is included, click the link for further context. For more information about unimplemented features, see Migrate Your Database to CockroachDB.	✓
Statement error	Edit the statement to fix the error. Otherwise, remove the statement from the schema.	✓
Not executed	Remove the statement from the schema. You can include it when moving data to the new database.	✓
Missing user	Click the Add User button next to the error message. You must be a member of the admin role. This adds the missing user to the cluster.	✓
Incidental error	Resolve the error in the earlier failed statement that caused the incidental error.	✓
Incompatible statement (non-PostgreSQL dialects)	There is no equivalent syntax on CockroachDB. Implement a workaround by replacing the statement. Otherwise, remove the statement from the schema. Then check Acknowledge.	✓
Compatibility note (non-PostgreSQL dialects)	Edit the statement to match the CockroachDB syntax. Then optionally check Acknowledge.	✗
Suggestion	Review and take any relevant actions indicated by the message. Then optionally check Acknowledge.	✗

After updating the schema, you must retry the migration to update the Summary Report. This is necessary in order to verify that the schema has zero errors and can be finalized.

To export the current schema, click Export SQL File at the top of the Statements list.

Retry the migration

To analyze a schema that you have updated, click Retry Migration at the top of the Statements list. This updates the Summary Report.

This is necessary in order to verify that the schema has zero errors and can be finalized.

Finalize the schema

You can finalize the schema when the number of errors is zero. This value is displayed on the Migrations table, Summary Report, and Statements list.

To finalize the schema, click Finalize Schema when viewing the Summary Report or Statements list. A modal will open:

1. In the Success tab, click Next.

2. In the Create Schema tab, name the new database and select a user to own the database. Optionally click Download SQL export to download your schema file. This is useful for migrating your database to a different cluster. Then click Finalize to create the new database.

See also

• Migrate Your Database to CockroachDB