Migrating a PostgreSQL database to CockroachDB Serverless

pg_dump-to-cockroachdb.md

When restoring a PostgreSQL database from a dump, the process is to simply feed PostgreSQL the dump file, whose SQL statements it executes.

CockroachDB seems to take a different approach in order to restore the database more efficiently. Unfortunately, it lacks support for the types of things that often occur in a PostgreSQL dump, such as sequences and computed indexes. Fortunately, these things can usually be removed from the database dump before importing, then executed as DDL after importing.

Also, it's not easy for a new CockroachDB user to follow the import process as documented, so I wanted to make it easy.

1. Move all DDL statements related to SEQUENCEs, INDEXes and VIEWs into a separate SQL file. You'll need this later.
2. Ensure client_min_messages is set to debug5 in the PostgreSQL dump file.
3. Add your billing address and credit card details to CockroachDB if you haven't already. (Cockroach will prevent imports from external sources until you do.)
4. Start a local HTTP server so your database dump can be downloaded by CockroachDB Serverless via HTTP, e.g.

   python -m SimpleHTTPServer 3000

5. Use ngrok to expose your local server to the internet, e.g.

   ngrok http 3000

6. Connect to CockroachDB via the CLI, e.g.

   cockroach sql --url "your-connection-string"

7. Import your database dump, which was modified in step 1, e.g.

   IMPORT PGDUMP 'https://somerandomcode.ngrok.io/dump.sql' WITH ignore_unsupported_statements;

8. Assuming this succeeded, run all the SQL commands you extracted in step 1.

If step 7 failed, try to find help on Slack.

If step 8 failed, please let me know here (in addition to getting help elsewhere).