Switching from MySQL to PostgreSQL - tips, tricks and gotchas?

Question

I am contemplating a switch from MySQL to PostgreSQL.

What are your tips, tricks and gotchas for working with PostgreSQL?

What should a MySQLer look out for?

See also: How different is PostgreSQL to MySQL?
See also: Migrate from MySQL to PostgreSQL

Note - I don't think this is a duplicate. In particular the type of answers are quite diffferent and the responses here have much more implementation detail, which is what I was looking for

Answer 1

See: http://stackoverflow.com/questions/724867/how-different-is-postgresql-to-mysql

Answer 2

You could try PostgreSQL gotchas that contains the most common issues. Generally, the PostgreSQL documentation is pretty good too, so keep that under your pillow as well.

Also, Converting from MySQL to PostgreSQL on the pgsql wiki.

Answer 3

Before converting, set your MySQL to ANSI-strictness by starting the server with: --transaction-isolation=SERIALIZABLE --sql-mode=ANSI

Make sure you're not using MyIsam tables.

MySQL allows a lot of conversions it shouldn't; pg will require a cast.

Your stored procs, functions, and triggers will have to be re-written. pg gives you a choice of languages for these, but you have to install the languages; it's not as user friendly as MySQL.

pg will only allow in a select list columns that are in a group by or are aggregates; MySQL will cheat by selecting the first value in the group if you do this.

MySQL adds a bunch of extensions: the not-equal operator can be != as in C, it allows '&&' as a synonym for 'and', '||' for 'or' etc. In particular, pg uses '||' to mean string catenation.

Basically, pg is pretty strictly ANSI, MySQL isn't. I'd strongly suggest getting your MySQL to as strict an ANSI compliance as possible before converting to pg, then checking for any warnings when you run your applications.

Answer 4

Just went through this myself, well I still am...

• Case sensitive text
• Lack of INSERT IGNORE and REPLACE
• Explicit casting needed almost everywhere
• No backticks
• LOAD DATA INFILE (COPY is close, but not close enough)
• Change autoincrement to SERIAL
• Although bad form in MySQL, in Postgres, an INNER JOIN without an ON clause can't happen, use CROSS JOIN or the like
• COUNT(*) can be crazy slow
• Databases are encoded with character sets, not tables
• You can have multiple databases, with multiple schemas (MySQL really has just one database and multiple schema)
• Partitioning is different
• MySQL interval vs. Postgres interval (for time intervals)
• Implicit column renaming, Postgres requires AS
• Cannot update multiple tables at the same time in Postgres
• Postgres functions are powerful. So there is no CALL proc();; rewrite proc() as a function and SELECT proc();.

Answer 5

It is going to be a massive task as you'll have to test your entire code-base - every single query, anywhere, for

• Syntax
• Correct behaviour (i.e. returns the same results)
• Performance - e.g. are there any performance regressions / improvements, and can you handle them?
• Error handling - they do not behave the same under error conditions, maybe your code was relying on specific error codes

Operationally you will need to look at:

• Backup/restore
• Disc space utilisation
• Memory utilisation
• One-off data migration - could be a big / time consuming task
• Rollback plan for if it fails
• Monitoring - how are you monitoring your MySQL, and can those methods be adapted
• (If relevant) - replication

You will definitely have to do major amounts of performance testing before considering such a move.

These costs make moving to a different database too expensive for most nontrivial apps. Consider the benefits VERY carefully against the vast, vast costs of doing all of the above.

I would be surprised if it takes you less than three months, in a nontrivial application, during which time you won't be able to continue regular development.