Find solution
How to migrate data from mySQL to PostgreSQL?

Convert a MySQL schema to PostgreSQL

  • 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?

  • 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.

vogon_poet at Ask.Metafilter.Com Visit the source

Was this solution helpful to you?

Other answers

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.

fatbird

There's also a http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html#option_mysqldump_compatible 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...)

bitdamaged

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 ";

nightwood

Never used either database, but is it possible to dump the schema in ANSi standard SQL (or some other common dialect)?

SemiSalt

flabdablet

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.

vogon_poet

http://stackoverflow.com/questions/5417386/import-mysql-dump-to-postgresql-database

rachelpapers

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?

vogon_poet

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.

flabdablet

Load more

Related Q & A:

Just Added Q & A:

Find solution

For every problem there is a solution! Proved by Solucija.

  • Got an issue and looking for advice?

  • Ask Solucija to search every corner of the Web for help.

  • Get workable solutions and helpful tips in a moment.

Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.