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

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

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. 

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>

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>

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

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