Re: Transfer from MySQL to PostgreSQL

On 28/3/06 10:49 am, "Andrea" <andrea(dot)b73(at)email(dot)it> wrote:

> I have both MySQL (5.0) and PostgreSQL (8.1) database servers on my PC.
>
> On MySQL I have a table (very simple, only 7 char fields) filled with
> about 35000 records. On PostgreSQL I have created the same table
> structure (same fields, names, indexes, etc...). I would like to
> transfer all records from MySQL to PostgreSQL.
> Which is the easiest and shortest way to do this??
>
> I know this question is related to a software extern to PostgreSQL,
> however, I hope someone can help me. Thank.

If you can get the data out of the MySQL table into a simple tab delimited
text file, then that is very easy to get it into PostgreSQL using the COPY
command

http://www.postgresql.org/docs/8.1/static/sql-copy.html

(I don't use MySQL though, so can't tell you how to get it into the file)

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

I have both MySQL (5.0) and PostgreSQL (8.1) database servers on my PC.

On MySQL I have a table (very simple, only 7 char fields) filled with
about 35000 records. On PostgreSQL I have created the same table
structure (same fields, names, indexes, etc...). I would like to
transfer all records from MySQL to PostgreSQL.
Which is the easiest and shortest way to do this??

I know this question is related to a software extern to PostgreSQL,
however, I hope someone can help me. Thank.

On Tue, Mar 28, 2006 at 11:49:16AM +0200, Andrea wrote:

> I have both MySQL (5.0) and PostgreSQL (8.1) database servers on my PC.
> On MySQL I have a table (very simple, only 7 char fields) filled with
> about 35000 records. On PostgreSQL I have created the same table
> structure (same fields, names, indexes, etc...). I would like to
> transfer all records from MySQL to PostgreSQL. Which is the easiest
> and shortest way to do this??

dump the database with insert statements (maybe newer versions of
mysqldump can also dump only one table's data), remove the stuff, that
is not needed with postgres, fix the table names and so on with an
editor of your choice and run the resulting file with psql.

--
cu

Use SELECT ...INTO OUTFILE and treat the data as a csv while importing
with COPY from the pgsql side.

from mysql docs: The SELECT ... INTO OUTFILE 'file_name' form of
SELECT writes the selected rows to a file. The file is created on the
server host, so you must have the FILE privilege to use this syntax.
file_name cannot be an existing file, which among other things
prevents files such as /etc/passwd and database tables from being
destroyed. As of MySQL 5.0.19, the character_set_filesystem system
variable controls the interpretation of the filename.

Avoid the /etc/passwd line >:}

g.-

On 3/28/06, Christoph Frick <frick(at)sc-networks(dot)com> wrote:
> On Tue, Mar 28, 2006 at 11:49:16AM +0200, Andrea wrote:
>
> > I have both MySQL (5.0) and PostgreSQL (8.1) database servers on my PC.
> > On MySQL I have a table (very simple, only 7 char fields) filled with
> > about 35000 records. On PostgreSQL I have created the same table
> > structure (same fields, names, indexes, etc...). I would like to
> > transfer all records from MySQL to PostgreSQL. Which is the easiest
> > and shortest way to do this??
>
> dump the database with insert statements (maybe newer versions of
> mysqldump can also dump only one table's data), remove the stuff, that
> is not needed with postgres, fix the table names and so on with an
> editor of your choice and run the resulting file with psql.
>
> --
> cu
>
>
>

--
Guido Barosio
-----------------------

Guido Barosio wrote:
> Use SELECT ...INTO OUTFILE and treat the data as a csv while importing
> with COPY from the pgsql side.
>
>
> from mysql docs: The SELECT ... INTO OUTFILE 'file_name' form of
> SELECT writes the selected rows to a file. The file is created on the
> server host, so you must have the FILE privilege to use this syntax.
> file_name cannot be an existing file, which among other things
> prevents files such as /etc/passwd and database tables from being
> destroyed. As of MySQL 5.0.19, the character_set_filesystem system
> variable controls the interpretation of the filename.

I think you also have to be careful that NULL's are transfered cleanly
from one database to the other.

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +