Importing postgres data to mysql

Question

I have a requirement where I need to insert the postgres data into mysql. Suppose I have user table in postgres. I have user table also in mysql. I tried to do something like this:

gts = 'cd '+js_browse[0].js_path #gts prints correct folder name/usr/local/myfolder_name 
os.system(gts)
gts_home = 'export GTS_HOME='+js_browse[0].js_path  
os.system(gts_home)
tt=gts+'&& sh bin/admin.sh User --input-dir /tmp/import'
#inside temp/import i import store my postgres user table data
#bin is the folder inside myfolder_name

In mysql if I use the command it works perfectly fine:

cd /usr/local/myfolder_name
bin/admin.sh User -account=1 user=hamid -create'

I am unable to store data inside mysql this way. Any help shall be appreciated.

Is this a one time thing or do you need a script so that you can repeat the copy/transfer/import of data? I can only guess that admin.sh is a shell script that uses its arguments to perform certain operations such as create a user (which appears to be the case with your example). Are you hoping to alter admin.sh to do things with both the mysql and postgres databases? How does admin.sh determine which database to connect to? Are you able to successfully store the user data into temp/import? — ghbarratt, Apr 17 '12 at 13:49
I need this script to run every time i create a new user in postgres.yes my data is getting stored in temp/import . — Dar Hamid, Apr 17 '12 at 14:00
So what you really want to do at this point is to get the data collected in temp/import into the MySQL database, right? I am guessing that ideally you would do this through the same python script you are using to extract the data from the Postgres db. I think your options in that case would be something like [MySQLdb](http://stackoverflow.com/questions/372885/how-do-i-connect-to-a-mysql-database-in-python) or the [mysql command](http://stackoverflow.com/questions/6730111/how-to-execute-large-mysql-data-insert-script-file-using-command-line-in-windows) — ghbarratt, Apr 17 '12 at 14:15

score 1 · Answer 1 · answered Apr 17 '12 at 13:22

1

You don't really give us much information. And why would go from postgres to mysql?

But you can use one of these tools - I have seen people talk good about them

pg2mysql or pgs2sql

Hope it works out.

answered Apr 17 '12 at 13:22

Mads

724
3
10

The reason why i will go from postgres to mysql is that i have two applications one running on postgres and another on mysql.need to transfer data frm one to another. – Dar Hamid Apr 17 '12 at 13:25
I understand. But I would rather go from mysql to postgres. I don't have a big knowledge on either, but postgres seems to be newer, more reliable and somewhat faster. SO why not move from mysql to postgres? – Mads Apr 17 '12 at 13:32
I only need to fetch data from postgres table(e.g user table) and insert it into mysql table(here again user table).I don't need any conversion tool. – Dar Hamid Apr 17 '12 at 13:32
Oh, OK. If this is a one-time thing, then use a CSV. I know that postgres can easily export to csv and mysql can easily read it. – Mads Apr 17 '12 at 13:37
Could be - I don't know. Now you mention it I can read the question both ways. – Mads Apr 17 '12 at 13:48

score 1 · Answer 2 · answered Apr 17 '12 at 14:19

PostgreSQL provides possibility to dump data into the CSV format using COPY command.

The easiest path for you will be to spend time once to copy schema objects from PostgreSQL to MySQL, you can use pg_dump -s for this on the PostgreSQL side. IMHO, it will be the biggest challenge to properly move schemas.

And then you should import CSV-formatted data dumps into the MySQL, check this for reference. Scrolling down to the comments you'll find recipes for Windows also. Something like this should do the trick (adjust parameters accordingly):

LOAD DATA LOCAL INFILE C:\test.csv
INTO TABLE tbl_temp_data 
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

Importing postgres data to mysql

2 Answers2