Thread: Convert database

-----Original Message-----
From: DenBaguse MasRodjie [mailto:roji@mail15.com]
Sent: 03 October 2003 10:32
To: pgsql-odbc@postgresql.org
Subject: [ODBC] Convert database

How to convert from MySQL to PostgreSQL.

I tried using DBTools, but to slowly. Converting 1 row taken around 0.25 second!!!!

Because i have database over 100MB on MySQL. Maybe over 3 days non-stop to converting.

Is there another way? I'm tried to using my2pg and mysql2pgsql. But error occured.

I want to migrate to PostgreSQL 'cause i have a problem on MySQL.'

MySQL doesn't support sub select on stable version (4.0.14) and too slow sub select query on alpha version (4.1.0).

Is PostgreSQL support that features? How fast it can execute?

 

 

 

Thank's

 

 

Roji

> How to convert from MySQL to PostgreSQL.
> I tried using DBTools, but to slowly. Converting 1 row taken around 0.25
> second!!!! Because i have database over 100MB on MySQL. Maybe over 3
> days non-stop to converting. Is there another way? I'm tried to using
> my2pg and mysql2pgsql. But error occured. I want to migrate to
> PostgreSQL 'cause i have a problem on MySQL.' MySQL doesn't support sub
> select on stable version (4.0.14) and too slow sub select query on alpha
> version (4.1.0). Is PostgreSQL support that features? How fast it can
> execute?

Sub-selects rock on postgresql. I use them extensively. In fact I would
never use MySQL, and that is just one of the reasons.

Anyway, to import, if you are handy without a GUI, there may be a way to
do it quickly. This is what I do when I migrate SQL data from one source
to another. (It have done this quite often between MSSQL, PostgreSQL, and
Oracle...Like I said, I wouldn't use MySQL)

Dump out the schema from the database if you don't already have it as a
SQL script.

Separate the table definitions from the index/trigger definitions.

Create the tables in PostgreSQL usinhg the SQL schema. Make any changes to
the data types as nessisary.

Using what ever tool exists in your current SQL environment, dump out a
tab-delimited file for each table.

Using postgreSQL's "COPY" syntax, copy the data to the table from the dump
file.

Hint, you have PHP and you know how to use it, it is trivial to open a
MySQL and a PostgreSQL connection at the same time, query all records from
MySQL database and "COPY" them to PostgreSQL.

I have two databases, one Oracle and one PostgreSQL. The PostgreSQL works
on a snapshot of the Oracle database. The snapshot is updated every week.
The table has 26 million records in it. I don't remember how long it
takes, but it isn't an unreasonable amount of time.