MySQL 迁移postgresql_从MySQL迁移到PostgreSQL

We are currently using MySQL for a product we are building, and are keen to move to PostgreSQL as soon as possible, primarily for licensing reasons.

Has anyone else done such a move? Our database is the lifeblood of the application and will eventually be storing TBs of data, so I'm keen to hear about experiences of performance improvements/losses, major hurdles in converting SQL and stored procedures, etc.

Edit: Just to clarify to those who have asked why we don't like MySQL's licensing. We are developing a commercial product which (currently) depends on MySQL as a database back-end. Their license states we need to pay them a percentage of our list price per installation, and not a flat fee. As a startup, this is less than appealing.

解决方案

Steve, I had to migrate my old application the way around, that is PgSQL->MySQL. I must say, you should consider yourself lucky ;-)

Common gotchas are:

SQL is actually pretty close to language standard, so you may suffer from MySQL's dialect you already know

MySQL quietly truncates varchars that exceed max length, whereas Pg complains - quick workaround is to have these columns as 'text' instead of 'varchar' and use triggers to truncate long lines

double quotes are used instead of reverse apostrophes

boolean fields are compared using IS and IS NOT operators, however MySQL-compatible INT(1) with = and <> is still possible

there is no REPLACE, use DELETE/INSERT combo

Pg is pretty strict on enforcing foreign keys integrity, so don't forget to use ON DELETE CASCADE on references

if you use PHP with PDO, remember to pass a parameter to lastInsertId() method - it should be sequence name, which is created usually this way: [tablename]_[primarykeyname]_seq

I hope that helps at least a bit. Have lots of fun playing with Postgres!