Thread: MySQL DB to PostgresSQL DB

greetings,

I don't know if pgsql-www is the right place for your questions or not,
but I'll be nice and give you a few ideas on how to get your questions
answered.. :)

On Tue, 2006-05-16 at 00:19 +0200, Jose Apablaza wrote:
> Dear all,
>
> This is my first post and I will try to be short and clear in my
> questions and I hope you can help me with that.
>
> I have a MySQL DB and I want to export to PostgreSQL DB.
>
> My questions are;
>
> - Is posible to export MySQL DB to PostgreSQL DB?

directly? without modification? not sure about that. at the very least
many of the field types are different in pg vs. other databases like
mysql. you'll need to run a script or do a search-and-replace to resolve
that.

> - Does PostgreSQL DB has tools to import MySQL DB?
> - If is posible, How do I need to build the DB in MySQL?, in order to
> have success in the exportation.
> - Do someone did it before?, exporting MySQL DB to PostgreSQL DB?

many people have converted from mysql to postgresql.

> - What kind of risk do we can to have in this process?

depends on what exactly mysql is doing for you now.

> - How long can take this process?

depends on the size of the database and skill at this sort of thing.
also, if you are using mysql_* calls from (for example) php, you will
need to convert that code. personally, I recommend PEAR::DB (assuming
php is being used).. that way once you have the database imported to pg,
you change the connect string and you are done. info about PEAR::DB is
here: http://pear.php.net/DB ... you might also try
http://pear.php.net/MDB2 , but I do not have personal experience with
that module.

I did a google search:
http://www.google.com/search?hl=en&q=mysql+to+postgresql&btnG=Google
+Search

and found a number of links that might help you in your quest. if none
of those links are helpful, please let me know privately.

another recommendation I would make after using google and reading up on
a few things is to ask about this process on irc.. #postgresql on
irc.freenode.net is an *excellent* resource.

>
> Thanks in advance.
>
> Jose
>

regards,
J
--
Jeff MacDonald
Zoid Technologies, http://zoidtechnologies.com/

Jose Apablaza wrote:
> - Is posible to export MySQL DB to PostgreSQL DB?
> - Does PostgreSQL DB has tools to import MySQL DB?
> - If is posible, How do I need to build the DB in MySQL?, in order to 
> have success in the exportation.
> - Do someone did it before?, exporting MySQL DB to PostgreSQL DB?
> - What kind of risk do we can to have in this process?
> - How long can take this process?

Yes, it's doable, but it's not as straighforward as
   mysqldump -someflags mydbname | psql -someotherflags pgdbname

I suggest you start by checking the resources/articles in the MySQL 
section of http://www.postgresql.org/docs/techdocs.3 (which I'm glad to 
say is much better organized and comprehensive than when I had to do 
it).  FWIW, I converted using CSV as the intermediate format, and the 
dates and timestamps were the trickiest, which required the use of 
"staging" tables (with a textual representation of the columns).  An 
interesting side effect was discovering data inconsistencies in the 
MySQL database since as part of the conversion I implemented foreign key 
constraints under PostgreSQL (which were missing in the former).

Joe