Mysql – “relation does not exist” trying to import thesql dump into postgres

importMySQLpostgresql

environment:

ubuntu 10.04
mysql server 5.1.69 
postgres 9.2

Here's the sequence of steps:

  1. created a new postgres database, myDatabase
  2. executed this command: mysqldump -u root -p --compatible=postgresql myDatabase > mydump.sql
  3. executed this command: psql -h localhost -d myDatabase -U postgres
  4. At postgres prompt, executed these commands:
    SET standard_conforming_strings = 'off';    
    SET backslash_quote = 'on';     
    \i mydump.sql;

At this point, the process began, and after getting tons of

HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
psql:mydump.sql:196: WARNING:  nonstandard use of escape in a string literal
LINE 1: ...012053',50.18,50.36,49.4,49.69,59592,184824),(115,'\"2012060...

I get 

psql:mydump.sql:196: ERROR:  relation "pricedata" does not exist
LINE 1: INSERT INTO "pricedata" VALUES (113,'19981102',0.6275,0.6275...

I checked the mydump.sql file, and it has a CREATE TABLE "pricedata", but obviously this didn't have the desired effect on the postgres db.

I'm not sure what I'm supposed to do to get this to work. Any assistance would be greatly appreciated.

Best Answer

This won't work. Don't even try feeding mysqldump output directly into psql. You'll need to dump schema and data separately, convert the schema either by hand or with a tool, load the converted schema into PostgreSQL then load the dumped data.

mysqldump's compatibility flags are moderately useful for dumping data but pretty useless for dumping schema definitions.

The "relation does not exist" error will be a follow-up error caused by a prior one. Try running with psql --set ON_ERROR_STOP=1 .... .

Related Question