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
  • 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. – user1240 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. – user1240 Apr 27 '12 at 18:07

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.

  • 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

Use following command to get the backup data.

pg_dump --inserts -f filename.dump your_database

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

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

  • 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
  • Small correction to the answer given by karthik-ananth above, regarding the command: <!-- language-all: lang-none --> pg_dump -d filename.dump Since the -d is used for something else, this should be: pg_dump --inserts [-d db_name] >> filename.dump – wkoot Jun 23 '14 at 14:01

Use MySQL Workbench

The following steps are based on MySQL Workbench 6.1

Source & Target

Open MySQL Workbench and start the Migration Wizard
Database > Migration Wizard...
Set up connection to your source database (PostgreSQL)
Set up connection to your target database (MySQL)
Select the schemas to migrate

Object Migration

Select the source objects to migrate
Review and manual editing proposed migration
Target creation options
Create schemas
Create target results

Data Migration

Data transfer setup
Bulk data transfer

See also How to migrate postgresql databases to mysql using the mysql workbench migration-wizard

  • 1
    Welcome Jonathon! When posting a link, try to summarize or provide an explanation of the content in your answer, otherwise it is a lot less useful if the website disappears. – LowlyDBA Jan 7 '15 at 21:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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