Subject: MySQL DB to PostgresSQL DB

From: "Jose Apablaza" <[email-masked]>
Date: Tue, 16 May 2006 00:19:03 +0200

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

Thanks in advance.

Jose

---

From: Jeff MacDonald <[email-masked]>
Date: Mon, 15 May 2006 19:26:15 -0400

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

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.

> - If is posible, How do I need to build the DB in MySQL?, in order to
> have success in the exportation.

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.

> Jose 

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

---

From: "Jose Apablaza" <[email-masked]>
Date: Tue, 16 May 2006 10:45:05 +0200

Hello,

> but I'll be nice and give you a few ideas on how to get your questions

Thank a lot for your answers Jeff,

> depends on what exactly mysql is doing for you now.

I have a web page on ActionApps (MySQL) and the idea is move it to OpenACS
(PostgreSQL)

-- 
José Apablaza
Telecommunications Engineer
Skype: pruebagp

---

From: Joe <[email-masked]>
Date: Thu, 18 May 2006 10:23:12 -0400

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