Mysql to Postgresql

2011/2/22 Adarsh Sharma <adarsh.sharma@orkash.com>:

Dear all,

Today I need to back up a mysql database and restore in Postgresql database
but I don't know how to achieve this accurately.

Have a look at: "mysqldump --compatible=postgresql" command:
<http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_compatible&gt;

Anyway, most of the times you will need a more manual migration, with
human intervention (custom scripts) and migrating the data through
something like CSV (SELECT... INTO OUTFILE).

--
Jaime Crespo
MySQL & Java Instructor
Software Developer
Warp Networks
<http://warp.es&gt;

On Tuesday 22 February 2011 10:21:01 Adarsh Sharma wrote:

Hi,

Today I need to back up a mysql database and restore in Postgresql
database but I don't know how to achieve this accurately.

Can anyone kindly describe me the way to do this.

Have a look here:
http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#MySQL

HTH, Jens

Take a look at the Navicat for PostgreSQL.

--
Thanks & Regards

Dhaval Jaiswal
Cell: +91 80953.978.43

________________________________

From: pgsql-general-owner@postgresql.org on behalf of Adarsh Sharma
Sent: Tue 2/22/2011 2:51 PM
To: pgsql-general@postgresql.org
Cc: mysql@lists.mysql.com
Subject: [GENERAL] Mysql to Postgresql

Dear all,

Today I need to back up a mysql database and restore in Postgresql
database but I don't know how to achieve this accurately.

Can anyone kindly describe me the way to do this.

Thanks & best Regards,

Adarsh Sharma

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material.
Any review, re-transmission, dissemination or other use of or taking of any action in reliance upon,this information by persons or entities other than the intended recipient is prohibited.
If you received this in error, please contact the sender and delete the material from your computer.
Microland takes all reasonable steps to ensure that its electronic communications are free from viruses.
However, given Internet accessibility, the Company cannot accept liability for any virus introduced by this e-mail or any attachment and you are advised to use up-to-date virus checking software.

On 02/22/11 1:25 AM, Jaime Crespo Rincón wrote:

2011/2/22 Adarsh Sharma<adarsh.sharma@orkash.com>:

Dear all,

Today I need to back up a mysql database and restore in Postgresql database
but I don't know how to achieve this accurately.

Have a look at: "mysqldump --compatible=postgresql" command:
<http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_compatible&gt;

Anyway, most of the times you will need a more manual migration, with
human intervention (custom scripts) and migrating the data through
something like CSV (SELECT... INTO OUTFILE).

if your tables aren't too huge, one method is via a perl script that
uses DBI to connect to both mysql and pgsql, and fetches a table from
one and loads it into the other. the catch-22 is, its fairly hard to
do this efficiently if the tables won't fit in memory

there are also various "ETL" (Extract, Translate, Load) tools that do
this sort of thing with varying levels of performance and automation,
some free, some commercial.

Please take a look at this article:
http://securfox.wordpress.com/2010/12/12/converting-mysql-to-postgresql/

I think also, that there are a tool that can do this easly,
Regards,

On Tue, Feb 22, 2011 at 9:21 AM, Adarsh Sharma <adarsh.sharma@orkash.com>wrote:

Dear all,

Today I need to back up a mysql database and restore in Postgresql database
but I don't know how to achieve this accurately.

Can anyone kindly describe me the way to do this.

Thanks & best Regards,

Adarsh Sharma

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Ali MEZGANI
Network Engineering/Security
http://securfox.wordpress.com/

On 2/22/2011 9:33 PM, John R Pierce wrote:

On 02/22/11 1:25 AM, Jaime Crespo Rincón wrote:

2011/2/22 Adarsh Sharma<adarsh.sharma@orkash.com>:

Dear all,

Today I need to back up a mysql database and restore in Postgresql
database
but I don't know how to achieve this accurately.

Have a look at: "mysqldump --compatible=postgresql" command:
<http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_compatible&gt;

Anyway, most of the times you will need a more manual migration, with
human intervention (custom scripts) and migrating the data through
something like CSV (SELECT... INTO OUTFILE).

if your tables aren't too huge, one method is via a perl script that
uses DBI to connect to both mysql and pgsql, and fetches a table from
one and loads it into the other. the catch-22 is, its fairly hard to do
this efficiently if the tables won't fit in memory

there are also various "ETL" (Extract, Translate, Load) tools that do
this sort of thing with varying levels of performance and automation,
some free, some commercial.

Actually, in mysql, you can set:

$db->{"mysql_use_result"} = 1;

Which causes mysql to not load the entire result set into memory, it
might be a bit slower because it has to make more round trips to the
server, but it uses very little ram. (I use this in my own perl mysql
to pg script)

-Andy

2011/2/22 Adarsh Sharma <adarsh(dot)sharma(at)orkash(dot)com>:

Dear all,

Today I need to back up a mysql database and restore in Postgresql database
but I don't know how to achieve this accurately.

In addition to other suggestions, you could also use open source Tungsten
Replicator which has real-time MySQL to PostgreSQL replication capabilities.
Though, if you only need to do it once, this might be overwhelming in your
case. On the other hand, if you need to migrate out with minimum downtime
and Q&A you might want to check it out.

We've done a webinar a week back which recording you can watch from
http://www.continuent.com/news/webinars - search for "Move Your Data In
Real-time From MySQL To PostgreSQL And Greenplum".

In a nutshell, it looks like this:
(1) Manual work to prepare the empty schema on PG
(2) New MySQL instance with Row Replication enabled
(3) Dump your MySQL backup to this MySQL with Row Replication
(4) Tunsgten Replicator will extract all incoming Row Change Events of each
transaction and apply them to PG one by one, taking care of DBMS specific
conversions under the hood (there are quite a bit).

Hope this helps,

Sincerely,
Linas