Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Among other problems, MySQL doesn't order trable creations according to constraints between them. Which means that if there's a dependecy A => B, not necessarily MySQL is going to create A before B. This is troublesome for Postgres. –  Andras Gyomrey Sep 2 '13 at 1:15

1 Answer 1

up vote 4 down vote accepted

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 .... .

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.