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

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)));

[download]

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
  • Comment on Re: Looking to convert all my MySQL DB's to postgresql, any experiences with mysql2pgsql?
  • Download Code

Replies are listed 'Best First'.
Re^2: Looking to convert all my MySQL DB's to postgresql, any experiences with mysql2pgsql?
by barrd (Canon) on Nov 27, 2013 at 11:34 UTC
    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*

[reply]
[d/l]

      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
[reply]
[d/l]
Re^2: Looking to convert all my MySQL DB's to postgresql, any experiences with mysql2pgsql?
by taint (Chaplain) on Nov 27, 2013 at 14:43 UTC
    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;
[reply]

      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

      [download]

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

      [download]

      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
[reply]
[d/l]
[select]

        LOL. Couldn't agree more (I did say I was migrating to Postgres, didn't I?) :)

        Which is not to say PostgreSQL is perfect. It's just (like you) makes me curse less, and I find myself harboring a lot less anxiety throughout the day, as a result. :)
        Seems to handle large amounts data more efficiently than MySQL (others?) too.

        Thanks for the response Tux.

        --Chris

        #!/usr/bin/perl -Tw
use Perl::Always or die;
my $perl_version = (5.12.5);
print $perl_version;
[reply]
Re^2: Looking to convert all my MySQL DB's to postgresql, any experiences with mysql2pgsql?
by Anonymous Monk on Nov 27, 2013 at 14:11 UTC
    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.)

[reply]

In Section Seekers of Perl Wisdom