473,289 Members | 1,840 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

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!!

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

http://archives.postgresql.org

Nov 12 '05 #1
5 14174
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
by: OneSolution | last post by:
I need to decide on which database system to use for our company. What is the popular opinion on PostGres vs. MySql? Thanks, Santosh
6
by: Th3L0rD | last post by:
Hi, I'm porting some php code from mysql to postgres but I canàt find something equivalent to mysql_select_db(). I use this function to switch from a DB on machine 1 to a db on machine 2. Is there...
5
by: Phil Powell | last post by:
I've read some online resources that utilize various MySQL command-line actions to migrate data from Access to MySQL. The situation is this: a group of co-workers of mine will be using an Access...
0
by: Rob Young | last post by:
Postgres to Mysql I've administrated a few Postgres database servers in the past, but have just started working with mysql, as I've volunteered to admin the mysql server run by my school's comp...
8
by: wlcna | last post by:
mysql v4.0.16: I had been using mysql with innodb and thought that was fine, until i used it for something requiring a few - perhaps slightly involved - joins, and have now seen the performance...
1
by: Fabio Benavides Murillo | last post by:
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...
3
by: Gaetano Mendola | last post by:
I found this article: http://www.serverwatch.com/news/article.php/10824_1126981_Ext that is clear out dated, it's anyway a good comparison with mysql. Do you know if someone did the TPC-C...
3
by: warwick.poole | last post by:
I am interested in finding out about Enterprise scale Postgres installations and clustering, especially on Linux. Essentially I would like to know the possibility that Postgres can store the...
3
by: fjm67 | last post by:
I am new to PHP but not so new to Postgres. If someone can either direct me to some howto or even provide me with an example, I would be grateful. I would like to know if it is possible to...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.