Thread: Transfer from MySQL to PostgreSQL
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 28/3/06 10:49 am, "Andrea" <andrea.b73@email.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.
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
Attachment
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@sc-networks.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. +