CODEWITHSUNDEEP
sqlpostgresqldatabase-migrationpg-dumpcockroachdb
Rishwan Riju
Rishwan Riju

Reputation: 31

Data migration from CockroachDB to PostgreSQL

I am trying to migrate my CockroachDB into PostgreSQL.

I have dumps of CockroachDB data in SQL format like "booking.sql".

I tried many ways ways to solve this problem:

  1. tried direct import of dump file using psql, but since the dump file was of CockroachDB it is showing some syntactical error
  2. my second plan was to restore the dump file back into CockroachDB system and try running pgdump from there. But I am not able to restore the database in CockroachDB.

ERROR: failed to open backup storage location: unsupported storage scheme: "" - refer to docs to find supported storage schemes

I tried doing again with import statement from CockroachDB, but that was of no use.

Upvotes: 1

Views: 3200

Answers (1)

Aria Shahdadi
Aria Shahdadi

Reputation: 301

for exporting data from cockroachDB there are some limitations. you can't export your data into SQL directly in new versions. the first way of exporting is using the cockroach dump command, but it's been deprecated from version 20.2 so if you are using a newer version, this won't work.

cockroach dump <database> <table> <table...> <flags>

sample:

cockroach dump startrek --insecure --user=maxroach > backup.sql

in new versions, you can export your data into CSV files using SQL commands like EXPORT

EXPORT DATABASE bank INTO 's3://{BUCKET NAME}/{PATH}?AWS_ACCESS_KEY_ID={KEYID}&AWS_SECRET_ACCESS_KEY={SECRET ACCESS KEY}' \ AS OF SYSTEM TIME '-10s';

to export into local nodes

EXPORT DATABASE bank INTO ('nodelocal://1/{PATH}');

the other alternative way of exporting is using database clients such as DBeaver. you can download and install DBeaver from https://dbeaver.io/download/. after adding the connection you can export the database from this path Right-click on db>tools>Backup

the fastest and easiest way of exporting is using a database tool like DBeaver. I hope this answer would have been helpful

Upvotes: 2

Related Questions