Migrations to Cockroach Cloud
Part 1: Schema conversion tool enables PostgreSQL migrations to Cloud
Very neat announcement from our leader Nate Stewart on how customers can migrate existing PostgreSQL backend applications to our Cockroach Cloud offering using MOLT. In this blog, we will walk through the first phase of a database migration which is loading a schema file into the Schema Conversion Tool assistant (SCT) which converts a postgreSQL dump file to Cockroach SQL dialect and applies the SQL commands to a database/schema in the cloud. Afterwards, data is then uploaded and application modified to work with CockroachDB and I will followup with that process. So lets get started using the DVD rental example.
Lets break down the steps for the a schema migration
bring your schema file extracted from a pg_dump, for this example I am using the above DVD sample sql file with schema DDL
upload to the migration UI accessed from the Cockroach Cloud Console Page
So taking the schema file from the following good example, I drop into the above window and the conversion tool begins to work parsing the SQL file and converting to operating cockroach SQL commands. In a short few minutes, we get this report of the conversion.
pretty neat and it gets better as I can now walk through the errors and recommendations fixing them one by one and retry the migration tasks to reduce them a few at a time..for instance, I was able to add a user which did not exist in order to support the grants
corrected all items which are not supported such as functions, domains etc
next retry reduce errors to 36 and continued to work through the issues
after 5 minutes, I was able to remove all errors, triggers and corrected function call syntax issues.
I choose to Finalize my schema to dvdrental database owned by postges user
and it worked!!
~~ Next step is to load data which in the example is a fairly simple process. I have all the .dat data files in CSV format
By simply connecting to the cloud database, I run the COPY command to load all the tables
and now I have my database migrated to the cloud! this is as easy as making a PBJ. Up Next, I will use DMS for the data migration of a new project.
