How to change my region on CockroachDB Cloud?

Question

I'm currently in AWS us-west-2 and I'm trying to find a solution to change region to California — possibly Google Cloud — as I'm using CockroackDB Cloud

I've been searching for a while. I have a small database in production I wish to transfer that I can shut down for a few hours without a problem, but I want to keep the data inside of it. I simply want to change my "primary" region for performance reasons.

Is there any solution to do so? After looking at their documentation, it seems impossible to export an entire database to then import it again, or simply switch regions. I'm not sure what to do. I find the struggle really annoying, I don't think it should be that complicated to move data even when you can shut it down for a while.

For reference, I want to do that because my database takes >1000ms to respond for 1 record result and I want to reduce the distance between clients/server/database

Answer 1

I found a solution and applied it successfully. For the ones wondering how to pull this move for a relatively small project with production data, here's what I did:

• Export all the data from the tables into CSVs using Postico
• Flatten the DDL for all the tables so I can execute it at once in Postico
• Delete the cluster entirely
• Create a new cluster in the right region and access it through Postico
• Run all the DDL in there
• Import the CSV one by one by right-clicking on each table and inserting the data; you may want to check the order in which you do it as some foreign-key may crash the flow
• Add all the indexes afterward

In short, thank you Postico. You made my life easier today.