By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,374 Members | 2,014 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,374 IT Pros & Developers. It's quick & easy.

migrate from postgres to mysql

P: n/a
> Hello

I have a problem, I want to migrate the data of a postgres' database to a
mysql's database, this because i need to move to a windows.

I tried making a postgres' backup using pg_dump, but this file doesn't have the insert in Transac-sql.

Any help is welcome

txs in advance!!

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Fabio Benavides Murillo wrote:
Hello

I have a problem, I want to migrate the data of a postgres' database to a
mysql's database, this because i need to move to a windows.

I tried making a postgres' backup using pg_dump, but this file doesn't


have
the insert in Transac-sql.

Any help is welcome

txs in advance!!


You can run PostgreSQL on windows.

-Robby

--
Robby Russell, | Sr. Administrator / Lead Programmer
Command Prompt, Inc. | http://www.commandprompt.com
rr******@commandprompt.com | Telephone: (503) 222.2783
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #2

P: n/a
On Thu, 2003-10-02 at 08:49, Fabio Benavides Murillo wrote:
Hello

I have a problem, I want to migrate the data of a postgres' database to a
mysql's database, this because i need to move to a windows.
You might also want to explore running PostgreSQL on windows. Currently
you can use Cygwin to stably run the current version of Pg. There is
also a native windows port on the way, if it's for development uses you
might want to test the beta.
I tried making a postgres' backup using pg_dump, but this file doesn't

have
the insert in Transac-sql.


Transact-SQL is actually Sybase's and MS SQL Server's dialect of SQL.
Perhaps you're referring to the use of COPY instead of INSERT
statements? If so see the help for pg_dump as you can have data dumped
as individual inserts (the -d and -D switches).

You'll probably have to do some editing of the dump by hand as well; to
change sequences to MySQL's auto_increment, remove any schema usages,
etc. I'm unaware of any automated tools as not many people migrate in
that direction ;).

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #3

P: n/a
Well, I've not done it, but you could do a

pg_dump -s dbname >schema.sql
pg_dump -d -a dbname >data.sql

to get just the schema in one file and the data in inserts in another
file. Then you could use a perl script driven by SQL::Translator (check
http://www.cpan.org) to translate the schema from Pg to MySQL. Create
the schema in MySQL, then load via the inserts.

In theory, should be easy; reality on the other hand...

Good luck,
Scott

On Thu, 2003-10-02 at 16:17, Arguile wrote:
On Thu, 2003-10-02 at 08:49, Fabio Benavides Murillo wrote:
Hello

I have a problem, I want to migrate the data of a postgres' database to a
mysql's database, this because i need to move to a windows.
You might also want to explore running PostgreSQL on windows. Currently
you can use Cygwin to stably run the current version of Pg. There is
also a native windows port on the way, if it's for development uses you
might want to test the beta.
I tried making a postgres' backup using pg_dump, but this file doesn't

have
the insert in Transac-sql.


Transact-SQL is actually Sybase's and MS SQL Server's dialect of SQL.
Perhaps you're referring to the use of COPY instead of INSERT
statements? If so see the help for pg_dump as you can have data dumped
as individual inserts (the -d and -D switches).

You'll probably have to do some editing of the dump by hand as well; to
change sequences to MySQL's auto_increment, remove any schema usages,
etc. I'm unaware of any automated tools as not many people migrate in
that direction ;).

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

--
------------------------------------------------------------------------
Scott Cain, Ph. D. ca**@cshl.org
GMOD Coordinator (http://www.gmod.org/) 216-392-3087
Cold Spring Harbor Laboratory
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #4

P: n/a
In article <10*********************@localhost.localdomain>,
Scott Cain <ca**@cshl.org> writes:
Well, I've not done it, but you could do a
pg_dump -s dbname >schema.sql
pg_dump -d -a dbname >data.sql to get just the schema in one file and the data in inserts in another
file. Then you could use a perl script driven by SQL::Translator (check
http://www.cpan.org) to translate the schema from Pg to MySQL. Create
the schema in MySQL, then load via the inserts.


I'd replace the second call of pg_dump by a "COPY mytbl TO 'mytbl.txt'"
for each table in the DB and import it into MySQL with "LOAD DATA
[LOCAL] INFILE". This would be much faster than INSERTing.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #5

P: n/a
Arguile schrieb:

You might also want to explore running PostgreSQL on windows. Currently
you can use Cygwin to stably run the current version of Pg. There is
also a native windows port on the way, if it's for development uses you
might want to test the beta.

I have searched the website but I cannot find any 7.4 beta binaries for
windows...
I thought the windows version was postponed until 7.5?
Thomas

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.