How to convert PostgreSQL to MySQL

Question

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

• user1240: 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.
• James: 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.
• user1240: Your error is related to data types, which I pointed out in the beginning. You need to manually convert those.

Answer 1

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.

• user1822: 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
• Aaron Brown: 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.
• Bruno: @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.
• James: Hi Bruno, thanks for your respond. This is quite challenging for me because I really have no skills in database especially this conversion matter.
• Bruno: @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?

Answer 2

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

• James: 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
• wkoot: 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

Answer 3

My open-source tool SQLpipe can transfer the result of a select query from one database to another. PostgreSQL and MySQL are both supported systems.

This can solve your problem if you're OK transferring data one table at a time. Other tools will be a better fit if you need to transfer an entire database at once, however.

Here is a guide on using SQLpipe to transfer data from PostgreSQL to MySQL. A quick summary:

1. Download SQLpipe. It is an executable program with no dependencies.
2. Grant permission to run SQLpipe with chmod +x sqlpipe, if required by your operating system.
3. Transfer data with a single CLI command, like this:

sqlpipe transfer \
 --source-ds-type "postgresql" \
 --source-hostname "your-postgresql-hostname" \
 --source-port 5432 \
 --source-db-name "your-postgresql-db-name" \
 --source-username "your-postgresql-username" \
 --source-password "your-postgresql-password"
 --target-ds-type "mysql" \
 --target-hostname "your-mysql-hostname" \
 --target-port 3306 \
 --target-db-name "your-mysql-db-name" \
 --target-username "your-mysql-username" \
 --target-password "your-mysql-password" \
 --target-table "name-of-table-to-insert-into" \
 --overwrite \
 --query "select * from public.users"

Answer 4

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

• LowlyDBA - John M: 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.

Answer 5

Maybe is worth trying this little hack. I couldn ́t do it tru MYSQL WorkBench. I had import tables containing 40 columns o more, first by exporting 10 lines from each postgres table to CSV then opening it in Excel with DATA -> MYSQL FOR Excel ( Must install this addon and excel must be open as administrator to work).

Export this table to MYSQL, then truncate it. After that run command similar as described on previous answer for a single CSV file :

COPY table TO 'c:\\TMP\\table.csv' WITH (FORMAT CSV, HEADER);

Then run this on MYSQL workbench (for local cpu file - my.cnf must have local_infile=1 ) :

LOAD DATA LOCAL INFILE 'C:\\tmp\\table.csv' INTO TABLE db.table_name FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;

Answer 6

If you happen to have a .sql file specific dialect and want to convert it to be able to run it on another db, you can use https://www.jooq.org/translate/