Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

How can I convert database from PostgreSQL to MySQL?

I have the postgresql dump file already and I try to use pg2mysql script, it converted but when uploading the file it produces this error:

ERROR 1064 (42000) at line 4: You have an error in your SQL syntax;
  check the manual that corresponds to your MySQL server version for the right
  syntax to use near 'bigserial NOT NULL,
    created timestamp NOT NULL,
    sql0 varchar(250) DEFAUL' at line 2
share|improve this question
1  
Script out the objects. Script out the data. Create the objects in MySQL and then insert the data. For the datatypes that don't match up exactly, use the closest match you can find. – Randolph West Apr 27 '12 at 4:30
I am not good in database thing so I need to find easiest way for me. I have the postgresql dump file already and I try to use pg2mysql script, it converted but when uploading the file it doesn't work. It gives me error of this pastebin.ca/2141111. Looks like the format issue. – James Apr 27 '12 at 9:33
Your error is related to data types, which I pointed out in the beginning. You need to manually convert those. – Randolph West Apr 27 '12 at 18:07

2 Answers

up vote 1 down vote

Once you've exported the SQL script, you need to convert the data types. There is no generic and automatic way of doing this, since MySQL and PostgreSQL have different sets of features. (PostgreSQL tends to be more standards-compliant than MySQL and may also support more features in general, e.g. CTE, indices on functions, ...)

The closest type to PostgreSQL's BIGSERIAL in the error you get is probably BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE in MySQL.

share|improve this answer
2  
I agree, the syntax problems are the least thing to worry about. The missing features in MySQL will present a much bigger challenge once the schema is created and the application is running – a_horse_with_no_name Apr 27 '12 at 11:33
Seems like plenty of enormous web properties are able to get by without PostgreSQL and use MySQL. What MySQL may lack in SQL-level features, it makes up for with larger community support and far more flexible replication. Different applications/businesses require different features. Don't assume. – Aaron Brown Apr 27 '12 at 23:24
1  
@AaronBrown We don't really know the requirements from the question. I'd say it's generally easier to move from MySQL to PostgreSQL than the other way around: it's easy to fix MySQL's GROUP BY behaviour, but somewhat harder to implement a CTE, an index on expressions or window functions in MySQL. Of course, you're right, this only matters if those features are used. It depends entirely on the application indeed. – Bruno Apr 28 '12 at 0:01
Hi Bruno, thanks for your respond. This is quite challenging for me because I really have no skills in database especially this conversion matter. – James Apr 30 '12 at 1:22
@James, this really depends on the application. Are you just moving data across and writing a new application, or are you expecting to port your application to PostgreSQL too? Does that application make use of an RDBMS abstraction layer, like some ORM systems do? – Bruno May 1 '12 at 12:12
up vote 1 down vote

Use following command to get the backup data. 

pg_dump -d filename.dump

This commands generates the sql script that can be used to restore schema as well as data to other sql databases. The -d options tell it to use Inserts instead of default copy commands.

Here is the manual - http://www.postgresql.org/docs/current/interactive/app-pgdump.html

share|improve this answer
Thanks Karthik. I have only postgresql dump file that was sent on me and I use the pg2mysql script but it doesn't work when importing to mysql. I would appreciate your help guys to show me the steps in converting the right way. Thanks – James Apr 27 '12 at 9:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.