Обсуждение: mysql to pgsql

I'm trying to make the switch from mysql to pgsql, but I'm having trouble
figuring out how to transfer my databases over!  I tried doing a dump in
mysql and then importing that into pgsql, but I got *tons* of errors.. I
searched the list archives and docs, but I haven't found anything that
talks about this..

Does anyone know how I can move my databases over?

Thanks,
--Bob

Hya

1. Dump data from Mysql
        mysqldump > x
2. Open x
        joe x (or vi x)
                Look tables create data, fix types (such int(11) -> integer,
etc)
                Remove all Mysql comments, unfortunately pg, cant support
comments,
                        they begins with # if i not mistake
                Quote some data if need - tablenames, field names etc such
(create table "order" etc)
                Remove Mysql quotes if any.
                Look encoding - such \" (i suggest pg and mysql encoding is
same, so only check it)
        (all this is done with search replace)
2. Save file
3. Create new db in postgre,
4. psql the_db_name < x
5. Look errors. If any return to point 2

thats all.

Second "easy" way is to use database migrate utility under windows, with two
ODBC configured to two servers. If you do so, dump pg database after this,
check types, then drop it and import again (this is because ODBC substitute
wrong types eg int -> float for example)

After migration, dont forget to grant rights on the new database, because
they will be not transfered.

if you hava a problem, just tell me.
nmmm
--------------------------------------------------------------
The reboots are for hardware upgrades!
"http://www.nmmm.nu; <nmmm@nmmm.nu>


----- Original Message -----
From: Bob Zoller <bob@helpermonkey.org>
To: <pgsql-admin@postgreSQL.org>
Sent: ÷åòâúâòúê, Ôåâðóàðè 17, 2000 11:19
Subject: [ADMIN] mysql to pgsql


> I'm trying to make the switch from mysql to pgsql, but I'm having trouble
> figuring out how to transfer my databases over!  I tried doing a dump in
> mysql and then importing that into pgsql, but I got *tons* of errors.. I
> searched the list archives and docs, but I haven't found anything that
> talks about this..
>
> Does anyone know how I can move my databases over?
>
> Thanks,
> --Bob
>
>
> ************

One correction:

On Fri, Feb 18, 2000 at 11:23:49AM +0200, Nikolay Mijaylov wrote:
> Hya
>
>                 Remove all Mysql comments, unfortunately pg, cant support
> comments,
>                         they begins with # if i not mistake

pgsql supports SQL standard comments. A comment is introduced by the
token --, and ends at the next newline.  I have many sql script files
that look like:

create table testit ( -- this is a test table
f1 int, -- this is a test integer
f2 real, -- this is a real, or float
f3 text -- this is a variable length text element
);

Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

On Don, 17 Feb 2000, Bob Zoller wrote:
>I'm trying to make the switch from mysql to pgsql, but I'm having trouble
>figuring out how to transfer my databases over!  I tried doing a dump in
>mysql and then importing that into pgsql, but I got *tons* of errors.. I
>searched the list archives and docs, but I haven't found anything that
>talks about this..
For testing purposes, I did it the other way round for a small database (15
tables) with a perl script using DBI/DBD. It worked very well and you don't
have to deal with different formats of ASCII dumps.

Bye...

    Dirk