Migrate Data from Postgres to MySQL

if you ever come to the need to migrate Data from Postgres to MySQL, you should stay away from using dumps. It is a hell lot easier to use export/import to/from CSV Data, because you do not need to synchronize column-names and it is special-char-proof.

Postgres syntax for exporting:

COPY tablename [ ( column [, ...] ) ]
    TO { 'filename' | STDOUT }
    [ [ WITH ] 
          [ BINARY ]
          [ OIDS ]
          [ DELIMITER [ AS ] 'delimiter' ]
          [ NULL [ AS ] 'null string' ]
          [ CSV [ HEADER ]
                [ QUOTE [ AS ] 'quote' ] 
                [ ESCAPE [ AS ] 'escape' ]
                [ FORCE QUOTE column [, ...] ]

so you end up like:
COPY article(title,text) TO '/tmp/pg_articles.csv';

and import it on MySQL-side with
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

like
LOAD DATA INFILE '/tmp/pg_articles.csv' INTO mysqlarticles (headline, articleText);

3 comments to Migrate Data from Postgres to MySQL

  • Dear Uwe,

    MANY thanks for the tip. I didn’t finish the transfer, but it looks much smoother then pg_dump & sed.

    I think this page should have more delicious bookmarks. I was the first – I’m proud I found your post ;-)

    Artur

    PS
    There’s no your name on the About page :-)
    I found it on LinkedIn.

    PPS
    Please remove “useless” and “noone” from the underline ;-)

  • Carlos

    That’s a great tip.

    How can we use it if PostgreSQL table has some null values to keep those nulls and not 0 in MySQL? I have tried but it keeps saving null numeric fields as 0…

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>