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

In reply to Re: Looking to convert all my MySQL DB's to postgresql, any experiences with mysql2pgsql? by Tux
in thread Looking to convert all my MySQL DB's to postgresql, any experiences with mysql2pgsql? by taint

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.