Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

Looking to convert all my MySQL DB's to postgresql, any experiences with mysql2pgsql?

by taint (Chaplain)
on Nov 27, 2013 at 02:47 UTC ( #1064524=perlquestion: print w/ replies, xml ) Need Help??
taint has asked for the wisdom of the Perl Monks concerning the following question:

Greetings, Monks.

I've been meaning to use PostgreSQL exclusively for some time now. But most of my current databases are MySQL. After a bit of research, it looks like the Perl script mysql2pgsql might just be the ticket. But before committing potential suicide, I thought it best to inquire here, and hopefully get input from others who might have used this.

Thank you for all your consideration.

--Chris

#!/usr/bin/perl -Tw
use Perl::Always or die;
my $perl_version = (5.12.5);
print $perl_version;

Comment on Looking to convert all my MySQL DB's to postgresql, any experiences with mysql2pgsql?
Re: Looking to convert all my MySQL DB's to postgresql, any experiences with mysql2pgsql? (backup)
by Anonymous Monk on Nov 27, 2013 at 02:56 UTC

    ... But before committing potential suicide ...

    step 1) avert suicide

    step 1a) backup

    step 1b) restore from backup to verify it worked

    step 1c) try it on 10 record dump :)

      I knew should have listed that in the OP. But it just seemed so obvious. :P

      --Chris

      #!/usr/bin/perl -Tw
      use Perl::Always or die;
      my $perl_version = (5.12.5);
      print $perl_version;
Re: Looking to convert all my MySQL DB's to postgresql, any experiences with mysql2pgsql?
by boftx (Chaplain) on Nov 27, 2013 at 03:07 UTC

    But before committing potential suicide ...

    Anyone who has ever watched "M*A*S*H" knows that suicide is painless.

    It helps to remember that the primary goal is to drain the swamp even when you are hip-deep in alligators.
      LOL. Yep. I've seen all of them, and some several times over. :)
      Right you are, boftx!

      --Chris

      #!/usr/bin/perl -Tw
      use Perl::Always or die;
      my $perl_version = (5.12.5);
      print $perl_version;
      > Anyone who has ever watched "M*A*S*H" knows that suicide is painless.

      xD

      Mike Altman is the son of the original film’s director, Robert Altman, and was 14 years old when he wrote the song’s lyrics. During an appearance on The Tonight Show Starring Johnny Carson in the 1980s, Robert Altman said that his son had earned more than a million US dollars for having co-written the song while he only made US$70,000 for having directed the movie.

      Cheers Rolf

      ( addicted to the Perl Programming Language)

Re: Looking to convert all my MySQL DB's to postgresql, any experiences with mysql2pgsql?
by Anonymous Monk on Nov 27, 2013 at 07:01 UTC
    My advice is: do the schema conversion by hand, figure out the correct options for mysqldump to get postgres-compatible output, and pipe it to psql (within a single transaction).

    Leave the inter-table constraints out of the schema and add them only after you have the data in.

    Of course, the schema conversion part is a tad difficult if you're not familiar with postgres's data types.

      Of course, the schema conversion part is a tad difficult if you're not familiar with postgres's data types.

      sqlt / http://sqlfairy.sourceforge.net/ *magically* converts from schemas to schemas

      sqlt -f MySQL -t PostgreSQL < in.mysql > out.postgresql sqlt --from MySQL --to PostgreSQL < in.mysql > out.postgresql sqlt --parser MySQL --producer PostgreSQL < in.mysql > out.postgresql
        LOL
        Funny you bring that Module up. After posting this topic, I went on a hunt to see if there might be something better, and landed on exactly that same Module.

        Thanks :)

        --Chris

        #!/usr/bin/perl -Tw
        use Perl::Always or die;
        my $perl_version = (5.12.5);
        print $perl_version;
      Good advice.
      Thanks for the words of caution.

      --Chris

      #!/usr/bin/perl -Tw
      use Perl::Always or die;
      my $perl_version = (5.12.5);
      print $perl_version;
Re: Looking to convert all my MySQL DB's to postgresql, any experiences with mysql2pgsql?
by Tux (Monsignor) on Nov 27, 2013 at 07:23 UTC

    I deal with Oracle, PostgreSQL, MySQL, SQLite, and Unify almost daily, and I always convert the data between databases using CSV exports and imports. The huge benefit is that if something is weird or incompatible, you can edit the CSV data and retry. CSV data *is* portable, but make sure the encoding is the same on both ends (preferable UTF-8).

    Things to remember when converting schema's are incompatabilities between the different databases, like date formats, dealing with NULL, default BLOB (length) limits, triggers and field defaults.

    Things might turn nasty when using database-specific stuff. Issues that are hard to convert are e.g. postgres' bigserial to Oracle. I don't know how modern mysql supports serial and bigserial.

    Another thing to try before you die is to test *EVERY* SQL statement you use. Not all ANSI SQL variations are 100% ANSI, nor are their syntaxes exactly the same. A nasty conversion from Oracle/mysql to postgres are substrings in indexes or views. Their syntax is way different:

    Oracle/mysql: create index idx_foo_blah on foo (blah (30)); Postgres: create index idx_foo_blah on foo ((substring (blah for +30)));

    Oracle does store NULL in varchar and varchar2 type fields when you store the empty string. DBD::mysql always strips spaces from char fields last time I tried. You should be aware of those tiny differences when moving data from one database to another and what tools you use for that. The quotation in all databases differ too much to even start mentioning.

    Take extra care with triggers and stored procedures and (nesting) transactions.


    Enjoy, Have FUN! H.Merijn
      I enjoyed reading that Tux, thank you. I'm in the "thinking about it" stage of converting MySQL=>PostgreSQL (and possibly apache2=>Nginx).

      Was wondering though if;

      SELECT * FROM users WHERE clue > 0; Works with all SQL variations?

      /me *chuckles*

        I have not yet seen a SQL dialect that does not support that syntax (unless "clue" is a subquery with joins or aggregates).

        Hell, even DBD::CSV supports aggregates when using SQL::Statement, so a folder of .csv files suddenly makes a real database :).


        Enjoy, Have FUN! H.Merijn
      Take extra care with triggers and stored procedures and (nesting) transactions.

      ...I think those are rarities in the MySQL world

      (Besides, anyone who has invested in an RDBMS enough to use stored procedures probably has no interest in nor intent on switching to another one.)

      Thanks Tux. I really appreciate all the time you put into the reply -- alot to chew on.

      One thing that really struck me tho;
      "I don't know how modern mysql supports serial and bigserial."
      This, coming from someone who opens with:
      "deal with Oracle, PostgreSQL, MySQL, SQLite, and Unify almost daily" ;)
      Sorry, I couldn't resist. ;)

      Seriously. +'s to you. A great reply, and one I'll be relying on during the conversion.

      --Chris

      #!/usr/bin/perl -Tw
      use Perl::Always or die;
      my $perl_version = (5.12.5);
      print $perl_version;

        I disgust the quoting mechanisms used in mysql, so my "dealing with MySQL" usually can be summarized in making a CSV dump of a MySQL database using perl scripts written ages ago, and then loading that data into PostgreSQL database with same schema so I can continue without being annoyed all the time.

        e.g. So far *only* mysql is - in the default configuration - *refusing* to accept valid ANSI SQL statements like select count (*) from foo;. That space between count and (*) is only seen as valid when one adds to the global config:

        [mysqld] sql_mode=IGNORE_SPACE

        So much for ANSI conformance. And that illness is still true in MariaDB 5.5.33:

        MariaDB [test]> create table foo (c_foo integer); Query OK, 0 rows affected (0.46 sec) MariaDB [test]> select count (*) from foo; ERROR 1064 (42000): You have an error in your SQL syntax; check the ma +nual that corresponds to your MariaDB server version for the right sy +ntax to use near '*) from foo' at line 1 MariaDB [test]> select count(*) from foo; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) MariaDB [test]>

        HATE!

        IMHO there is no ideal database. Every database has its annoyances. It is still software after all, but Oracle and MySQL really make me curse much more than all the others combined.


        Enjoy, Have FUN! H.Merijn
Re: Looking to convert all my MySQL DB's to postgresql, any experiences with mysql2pgsql?
by pvaldes (Chaplain) on Nov 27, 2013 at 14:54 UTC

    Before committing potential suicide...

    pg_dumpall -c --attribute-inserts > copy_of_mycluster_of-databases-$(date +%F_%H\:%M).sql

    if you have oids copy also this objects with the option --oids

    To restore simply load the file with something like psql -f copy_of_mycluster_of-databases-*.sql postgres

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1064524]
Approved by GrandFather
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (14)
As of 2014-09-19 16:58 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (143 votes), past polls