CODEWITHSUNDEEP
cockroachdb
Loiselle
Loiselle

Reputation: 482

Import Postgres dump into a table with CockroachDB

After executing a pg_dump from PostgeSQL, I attempted to import the .sql file into CockroachDB, but received the following errors:

ERROR:  unknown variable: "STATEMENT_TIMEOUT"
ERROR:  unknown variable: "LOCK_TIMEOUT"
ERROR:  unknown variable: "IDLE_IN_TRANSACTION_SESSION_TIMEOUT"
SET
SET
ERROR:  unknown variable: "CHECK_FUNCTION_BODIES"
SET
ERROR:  unknown variable: "ROW_SECURITY"
SET
ERROR:  unknown variable: "DEFAULT_TABLESPACE"
ERROR:  unknown variable: "DEFAULT_WITH_OIDS"
CREATE TABLE
ERROR:  syntax error at or near "OWNER"

Any guidance?

Upvotes: 1

Views: 848

Answers (1)

Loiselle
Loiselle

Reputation: 482

CockroachDB has special support for using psql, which supports the COPY command (which is faster than batched INSERT statements).

You'll need to do two things:

  1. Clean up the SQL file
  2. Import it into CockroachDB (which sounds like you tried, but I'll include the steps here for anyone else who needs them):

Clean up the SQL File

After generating the .sql file, you need to perform a few editing steps before importing it:

  1. Remove all statements from the file besides the CREATE TABLE and COPY statements.

  2. Manually add the table's PRIMARY KEY constraint to the CREATE TABLE statement.

    This has to be done manually because PostgreSQL attempts to add the primary key after creating the table, but CockroachDB requires the primary key be defined upon table creation.

  3. Review any other constraints to ensure they're properly listed on the table.
  4. Remove any unsupported elements, such as arrays.

Import Data

After reformatting the file, you can import it through psql:

$ psql -p [port] -h [node host] -d [database] -U [user] < [file name].sql

For reference, CockroachDB uses these defaults:

  • [port]: 26257
  • [user]: root

Upvotes: 3

Related Questions