Thread: Database Conversion

Hello, All,

I have a new faculty member who has a large database that is in
MySQL.  We don't support MySQL so the database needs to be ported to
PostgreSQL.  Her GA, who know MySQL, says that he has a query that he
will run that will put the data into postgres.  I thought that the
data would have to be output to a text file and then copied into
postgres.  I don't know MySQL.  I've done a conversion from Oracle
and this is how I did it.  Is he correct that he can put the data
into a postgres database by running a MySQL query?  It doesn't sound
possible to me.

Carol

Στις Thursday 04 September 2008 16:24:34 ο/η Carol Walter έγραψε:
> Hello, All,
>
> I have a new faculty member who has a large database that is in
> MySQL.  We don't support MySQL so the database needs to be ported to
> PostgreSQL.  Her GA, who know MySQL, says that he has a query that he
> will run that will put the data into postgres.  I thought that the
> data would have to be output to a text file and then copied into
> postgres.  I don't know MySQL.  I've done a conversion from Oracle
> and this is how I did it.  Is he correct that he can put the data
> into a postgres database by running a MySQL query?  It doesn't sound
> possible to me.
>

We recently did a conversion from MS Access (i dont know details) to pgsql 8.3.3.
The MS Access-aware guy just declared the correct postgresql ODBC settings,
i adjusted the pgsql backend to accept connections from the MS workstation,
then performed an EXPORT from MS Access to the pgsql datasource
and thats all.
Of course all i got was the exact MS Access tables, which then were useful
to populate my new designed pgsql tables.

One caveat here, most commonly, is the design of the DB.
The lower end you get mysql->sql server->access->COBOL, etc...
the greater chance you need a re-engineering of the schema.

> Carol
>



--
Achilleas Mantzios

Στις Thursday 04 September 2008 16:24:34 ο/η Carol Walter έγραψε:
> Hello, All,
>
> I have a new faculty member who has a large database that is in
> MySQL.  We don't support MySQL so the database needs to be ported to
> PostgreSQL.  Her GA, who know MySQL, says that he has a query that he
> will run that will put the data into postgres.  I thought that the
> data would have to be output to a text file and then copied into
> postgres.  I don't know MySQL.  I've done a conversion from Oracle
> and this is how I did it.  Is he correct that he can put the data
> into a postgres database by running a MySQL query?  It doesn't sound
> possible to me.

If his query is like:

SELECT 'INSERT INTO PostgreSqlTable(...) VALUES(''||somevalue...||'')' FROM mysqltable ....

then it is possible

>
> Carol
>



--
Achilleas Mantzios

> I have a new faculty member who has a large database that is in
> MySQL.  We don't support MySQL so the database needs to be ported to
> PostgreSQL.  Her GA, who know MySQL, says that he has a query that he
> will run that will put the data into postgres.  I thought that the
> data would have to be output to a text file and then copied into
> postgres.  I don't know MySQL.  I've done a conversion from Oracle
> and this is how I did it.  Is he correct that he can put the data
> into a postgres database by running a MySQL query?  It doesn't sound
> possible to me.

I don't think mysql has anything that exports data into postgresql. Unless
he is talking about the likes of DTS/SSIS or perl DBI, or other tools. Or
the tables are simple and he thinks he can ingeniously craft queries and
run them through pipes eventually to psql. DDL will be more difficult.


Regards,
Ben

Well, my database server lives on a Solaris 10 box.  I'm running
PosgreSQL 8.2.3.  The database that is being converted from MySQL is
currently on a Windows machine.  So far it seems that every solution
involves an interim step or two.  I think he was alluding to just
running a query.

Carol
On Sep 4, 2008, at 10:33 AM, Ben Kim wrote:

>
>> I have a new faculty member who has a large database that is in
>> MySQL.  We don't support MySQL so the database needs to be ported to
>> PostgreSQL.  Her GA, who know MySQL, says that he has a query that he
>> will run that will put the data into postgres.  I thought that the
>> data would have to be output to a text file and then copied into
>> postgres.  I don't know MySQL.  I've done a conversion from Oracle
>> and this is how I did it.  Is he correct that he can put the data
>> into a postgres database by running a MySQL query?  It doesn't sound
>> possible to me.
>
> I don't think mysql has anything that exports data into postgresql.
> Unless he is talking about the likes of DTS/SSIS or perl DBI, or
> other tools. Or the tables are simple and he thinks he can
> ingeniously craft queries and run them through pipes eventually to
> psql. DDL will be more difficult.
>
>
> Regards,
> Ben
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

>
> Hello, All,
>
> I have a new faculty member who has a large database that is
> in MySQL.  We don't support MySQL so the database needs to be
> ported to PostgreSQL.  Her GA, who know MySQL, says that he
> has a query that he will run that will put the data into
> postgres.  I thought that the data would have to be output to
> a text file and then copied into postgres.  I don't know
> MySQL.  I've done a conversion from Oracle and this is how I
> did it.  Is he correct that he can put the data into a
> postgres database by running a MySQL query?  It doesn't sound
> possible to me.
>
> Carol
>

You could possibly do it in a single operation using MS Access if you
have an ODBC connection to each database.  If however the dataset is
large, I wouldn't recommend it.  I have a number of MySQL and PostgreSQL
dbs and I either dump sql and then import or use PHP scripts when moving
between the two.

Nick

On Thu, Sep 4, 2008 at 9:22 AM, Hajek, Nick <Nick.Hajek@vishay.com> wrote:
>
>>
>> Hello, All,
>>
>> I have a new faculty member who has a large database that is
>> in MySQL.  We don't support MySQL so the database needs to be
>> ported to PostgreSQL.  Her GA, who know MySQL, says that he
>> has a query that he will run that will put the data into
>> postgres.  I thought that the data would have to be output to
>> a text file and then copied into postgres.  I don't know
>> MySQL.  I've done a conversion from Oracle and this is how I
>> did it.  Is he correct that he can put the data into a
>> postgres database by running a MySQL query?  It doesn't sound
>> possible to me.
>>
>> Carol
>>
>
> You could possibly do it in a single operation using MS Access if you
> have an ODBC connection to each database.  If however the dataset is
> large, I wouldn't recommend it.  I have a number of MySQL and PostgreSQL
> dbs and I either dump sql and then import or use PHP scripts when moving
> between the two.

Also, if you can spare the afternoon to learn it, sed is an awesome
tool for ETL.  I've used it for migrating stuff from oracle to pgsql
and mangling input data to work.

According to the GA, the database has 1,000,000 rows.  I hesitate to
pass that along because I don't know what that means.  It strikes me
as an odd way to talk about a relational database.  Normally, a
relational database has more than one table, so how is a "database"
having a million rows relevant.  There are other databases in other
research projects, that have main tables that has well more than a
million records, so I don't know if this is considered large or not.

I thank you, all, for your answers.

Carol

On Sep 4, 2008, at 12:27 PM, Scott Marlowe wrote:

> On Thu, Sep 4, 2008 at 9:22 AM, Hajek, Nick <Nick.Hajek@vishay.com>
> wrote:
>>
>>>
>>> Hello, All,
>>>
>>> I have a new faculty member who has a large database that is
>>> in MySQL.  We don't support MySQL so the database needs to be
>>> ported to PostgreSQL.  Her GA, who know MySQL, says that he
>>> has a query that he will run that will put the data into
>>> postgres.  I thought that the data would have to be output to
>>> a text file and then copied into postgres.  I don't know
>>> MySQL.  I've done a conversion from Oracle and this is how I
>>> did it.  Is he correct that he can put the data into a
>>> postgres database by running a MySQL query?  It doesn't sound
>>> possible to me.
>>>
>>> Carol
>>>
>>
>> You could possibly do it in a single operation using MS Access if you
>> have an ODBC connection to each database.  If however the dataset is
>> large, I wouldn't recommend it.  I have a number of MySQL and
>> PostgreSQL
>> dbs and I either dump sql and then import or use PHP scripts when
>> moving
>> between the two.
>
> Also, if you can spare the afternoon to learn it, sed is an awesome
> tool for ETL.  I've used it for migrating stuff from oracle to pgsql
> and mangling input data to work.
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

Carol Walter wrote:
> Hello, All,
>
> I have a new faculty member who has a large database that is in
> MySQL.  We don't support MySQL so the database needs to be ported to
> PostgreSQL.  Her GA, who know MySQL, says that he has a query that he
> will run that will put the data into postgres.  I thought that the
> data would have to be output to a text file and then copied into
> postgres.  I don't know MySQL.  I've done a conversion from Oracle and
> this is how I did it.  Is he correct that he can put the data into a
> postgres database by running a MySQL query?  It doesn't sound possible
> to me.
>
> Carol
>
Why don't you crate a schema for him and let him try out his idea.
What's the downside?

The upside is that we all may learn something new.

HH

--
H. Hall
ReedyRiver Group LLC
http://www.reedyriver.com

That's a good idea.  I think I'll try that.

Carol

On Sep 4, 2008, at 1:18 PM, H. Hall wrote:

> Carol Walter wrote:
>> Hello, All,
>>
>> I have a new faculty member who has a large database that is in
>> MySQL.  We don't support MySQL so the database needs to be ported
>> to PostgreSQL.  Her GA, who know MySQL, says that he has a query
>> that he will run that will put the data into postgres.  I thought
>> that the data would have to be output to a text file and then
>> copied into postgres.  I don't know MySQL.  I've done a conversion
>> from Oracle and this is how I did it.  Is he correct that he can
>> put the data into a postgres database by running a MySQL query?
>> It doesn't sound possible to me.
>>
>> Carol
>>
> Why don't you crate a schema for him and let him try out his idea.
> What's the downside?
>
> The upside is that we all may learn something new.
>
> HH
>
> --
> H. Hall
> ReedyRiver Group LLC
> http://www.reedyriver.com
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

On Thu, 4 Sep 2008 09:24:34 -0400
Carol Walter <walterc@indiana.edu> wrote:

> Hello, All,
>
> I have a new faculty member who has a large database that is in
> MySQL.  We don't support MySQL so the database needs to be ported to
> PostgreSQL.  Her GA, who know MySQL, says that he has a query that he
> will run that will put the data into postgres.  I thought that the
> data would have to be output to a text file and then copied into
> postgres.  I don't know MySQL.  I've done a conversion from Oracle
> and this is how I did it.  Is he correct that he can put the data
> into a postgres database by running a MySQL query?  It doesn't sound
> possible to me.
>
> Carol
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
I'd use mysqldump to create 2 files: the first being the schema, which I'd tweak to suit ( implementing tablespaces for
example), and then a second dump, which exports the data in a format that is acceptable to postgres. 

That way, you end up with what you want in a simple, repeatable manner.

If it's mysql 5 then the stored procedures may need to be converted - I know there are tools out there, but haven't
usedthem and can't comment. 

Steve
--
Steve Holdoway <steve.holdoway@firetrust.com>

Also you should really look into updating your pg install to 8.2.9.
It takes only seconds / minutes to do.  Release notes:

http://www.postgresql.org/docs/8.2/static/release-8-2-4.html
http://www.postgresql.org/docs/8.2/static/release-8-2-5.html
http://www.postgresql.org/docs/8.2/static/release-8-2-6.html
http://www.postgresql.org/docs/8.2/static/release-8-2-7.html
http://www.postgresql.org/docs/8.2/static/release-8-2-8.html
http://www.postgresql.org/docs/8.2/static/release-8-2-9.html

If you skin through there you will see many security, performance, and
reliability fixes that have come out since 8.2.3 was released.

On Thu, Sep 4, 2008 at 2:04 PM, Carol Walter <walterc@indiana.edu> wrote:
> I'm using 8.2.3.  I don't know which version of MySQL the faculty member has