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.

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

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

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

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

But before committing potential suicide ...

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

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

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;

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

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;

Before committing potential suicide...

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