This feature is in preview. This feature is subject to change. To share feedback and/or issues, contact Support.
The Migrations page on the CockroachDB Cloud Console features a Schema Conversion Tool that helps you:
- Convert a schema from a PostgreSQL database for use with CockroachDB.
- Create a new database that uses the converted schema.
On the Migrations page, a migration refers to converting a schema for use with CockroachDB and creating a new database that uses the schema. It does not include moving data to the new database. For details on all steps required to complete a database migration, see Migrate Your Database to CockroachDB.
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 PostgreSQL dump file containing data definition statements that create a database schema. To generate an appropriate file, run the
pg_dump utility and specify the
--schema-only options to extract only the schema of a PostgreSQL database to a
The dump file must be less than 4 MB.
COPY statements will be ignored in schema conversion.
To begin a database migration:
- Click the upload box and select a
.sqlfile, or drop a
.sqlfile directly into the box.
- 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.
- When analysis is complete, review the Summary Report and edit, add, or remove SQL statements in the Statements list.
If you have attempted at least one migration, the Migrations table is displayed with the following details:
|Migration Name||The filename of the
|Status||The status of the migration:
|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
The Summary Report displays the results of the schema analysis:
- The number of statements total in the uploaded
.sqlfile that were analyzed.
- The number of errors in SQL statements that are blocking finalization. Errors are further categorized 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 suggestions that were made regarding differences from other databases.
To resolve errors and review suggestions, 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 number of errors must be zero.
The Statement Status graph displays the number of successful statements (green), the number of errors (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 or unimplemented feature.
- Not Executed represents a statement that was not executed by the tool.
- Missing User represents a statement that references a nonexistent user.
- Incidental Error represents a statement that failed because another SQL statement encountered one of the preceding error types.
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.
For more details on why these suggestions are made, see Differences from other databases.
The Statements list displays the result of analyzing each statement in the
.sql file that you uploaded. The number of Statements Total, Errors, Incidental Errors, and Suggestions are displayed above the list of statements.
To finalize the schema and create a new database for migration, click Finalize Schema. The number of Errors must be zero.
Otherwise, use the Statements list to review and resolve errors. Navigate the list by scrolling or by clicking the arrows and Scroll to Top button on the bottom-right.
Incidental errors do not block finalization. This is because they are caused by errors in other SQL statements, and will likely disappear as you resolve those errors.
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 with 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:
|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
|Incidental error||Resolve the earlier failed statement that caused the incidental error.|
|Suggestion||Review and take any actions relevant to the suggestion. Then check Acknowledge.|
If you have made changes to any statements, retry the migration to update the number of Statements Total, Errors, Incidental Errors, and Suggestions.
To export the current schema, click Export SQL File at the top of the Statements list.
Retry the migration
To analyze a schema that has been updated in the Statements list, click Retry Migration at the top of the list.
This is necessary in order to update the number of Errors and enable finalization.
Finalize the schema
To finalize the schema, click Finalize Schema when viewing the Summary Report or Statements list. A modal will open:
In the Success tab, click Next.
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.
In the Move Data tab, click Done!.