Thread: migrate from postgres to mysql
> 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!! > > >
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 rrussell@commandprompt.com | Telephone: (503) 222.2783
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 ;).
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. cain@cshl.org GMOD Coordinator (http://www.gmod.org/) 216-392-3087 Cold Spring Harbor Laboratory
In article <1065126701.1431.8.camel@localhost.localdomain>, Scott Cain <cain@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.
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