Convert a MySQL schema to PostgreSQL

May 16, 2015 12:26 PM

I have a MySQL schema dump. I need to convert it to PostgreSQL. The data is not important right now; I just need to deal with the schema.

It actually looks like the easiest way to do this is just to manually find-and-replace all of MySQL's nonstandard datatypes, etc. with the equivalent Postgres syntax. But I'm no database guru, and I can't tell just by looking when something is valid Postgres syntax and when it isn't. It all looks like reasonable SQL, which is frustrating.

Is there some kind of easy side-by-side comparison that shows how to do equivalent things in MySQL and Postgres? Or, even better, some kind of automated translator?

Worst case, I bet I could use some kind of tool to migrate an (empty) database from a running MySQL server to a running pg server. But ideally I wouldn't have to do that (I don't even have MySQL installed).

Help?

posted by vogon_poet to Computers & Internet (12 answers total)

https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#MySQL
posted by flabdablet at 12:51 PM on May 16, 2015

Response by poster: The problem is that most of those assume you have an actual database with rows in it on a MySQL server -- either to migrate directly, or to do the dump in some particular format. I just have the schema file, and no data to migrate, so it seems like there must be a better solution, or at least a guide for how to make the changes manually.
posted by vogon_poet at 1:09 PM on May 16, 2015

By mysql schema file, do you mean the SQL text to created the schema (as from mysqldump)? But if you look at the PHP converter linked to in flabdablet's link above (the one that asks you to do the mysqldump in xml), the 'convert_field_data' seems to have the info you're looking for. A bit of it is (so mysql 'auto_increment' becomes 'serial' and so on.)

if ($attrs['Extra'] == "auto_increment") {
$fieldStr .= "serial ";
} elseif (substr( $attrs['Type'], 0, 3 ) == "int") {
$fieldStr .= "integer ";
} elseif (substr( $attrs['Type'], 0, 6 ) == "bigint") {
$fieldStr .= "bigint ";
} elseif (substr( $attrs['Type'], 0, 6 ) == "double") {
$fieldStr .= "money ";
} elseif (substr( $attrs['Type'], 0, 7 ) == "tinyint") {
$fieldStr .= "smallint ";
} elseif (substr( $attrs['Type'], 0, 8 ) == "smallint") {
$fieldStr .= "smallint ";
} elseif (substr( $attrs['Type'], 0, 5 ) == "float") {
$fieldStr .= "real ";
} elseif (substr( $attrs['Type'], 0, 7 ) == "decimal") {
$fieldStr .= "decimal ";
} elseif (substr( $attrs['Type'], 0, 4 ) == "blob") {
$fieldStr .= "bytea ";
} elseif (substr( $attrs['Type'], 0, 7 ) == "varchar") {
$fieldStr .= "text ";

posted by nightwood at 1:23 PM on May 16, 2015 [1 favorite]

http://stackoverflow.com/questions/5417386/import-mysql-dump-to-postgresql-database
posted by rachelpapers at 1:55 PM on May 16, 2015

This may sound crazy, but one option is to take a language like Python with a good ORM like SQLAlchemy, and write a script to connect to database A and database B, read the schema from A and create it in B, and then copy the data over.

How type X in MySQL maps to type Y in Postgres is exactly the sort of thing that ORMs are supposed to hide from you, and you can use this to your advantage. When you look for db migration tools, they basically do exactly this, just with a nice interface.

If the created schema is less than ideal in Postgres, you can just update it there afterwards.
posted by fatbird at 3:43 PM on May 16, 2015 [2 favorites]

Never used either database, but is it possible to dump the schema in ANSi standard SQL (or some other common dialect)?
posted by SemiSalt at 4:08 PM on May 16, 2015 [1 favorite]

Response by poster: The issue is that somebody else gave me the dump in a nonstandard format, and ideally I would be able to get it into postgres without installing and configuring MySQL.

Ultimately, I ended up changing everything by hand (manageable because there's no data) and it seems to work.

I converted VARCHAR to TEXT, and TINYINT to SMALLINT. Another issue that tripped me up was a slightly different syntax for creating UNIQUE constraints, and the fact that when you do ALTER TABLE...ADD KEY in MySQL (neither primary nor foreign), it's really equivalent to a postgres CREATE INDEX.

The problem is resolved for me. However, if anyone stumbles upon this who knows a better solution, please post it. What I did by hand was not very sophisticated, so perhaps there is a program that automates it and deals with weird edge cases?
posted by vogon_poet at 4:28 PM on May 16, 2015

There's also a compatibility mode for mysql dumps that can output the mysql data as postgres formatted.

(Note I know you don't want to install MySQL but I'm sticking it here for any future browser folks - also MySQL is pretty easy to install and run these days...)
posted by bitdamaged at 5:19 PM on May 16, 2015 [2 favorites]

somebody else gave me the dump in a nonstandard format

Knowing specifically what that format looks like, and preferably how the dump was generated, would certainly make it easier to provide more helpful answers.
posted by flabdablet at 4:59 AM on May 17, 2015

Best answer: Every time I've heard of someone doing something like this, they end up doing it by hand or else writing a custom convertor script. There is no meaningful standard SQL.
posted by Nelson at 6:51 AM on May 17, 2015

Seconding mysql2postgresql (mentioned in flabdablet's link). Also pgloader.io.
posted by Brian Puccio at 8:20 AM on May 18, 2015

Response by poster: For any future person reading this thread: everyone here gave good suggestions that could possibly help you.

But if you have my exact problem (somebody just gave you a schema dump in MySQL syntax with backticks around identifiers, and you don't have the original DB), and there aren't too many tables, it's faster and easier to just change things by hand until Postgres accepts the file.
posted by vogon_poet at 8:45 AM on May 18, 2015