This document describes the version upgrade policies for MySQL and PostgreSQL databases.
PostgreSQL and MySQL major and minor versioning policies
PostgreSQL and MySQL have similar versioning policies with versions being in the form of X.Y.Z (for example, 9.2.4 or 5.5.31).
Both databases have these types of releases:
- Major database version releases
- Minor database version releases
Major database version releases
Here the first or second part of the version number, X and/or Y, is increased. Both databases typically only change the first number when extremely significant changes or features are introduced. For example, the PostgreSQL move from 8.4 to 9.0 occurred when they introduced streaming replication that allowed for hot standby database replicas. That was a Big Deal because before that the PostgreSQL built-in replication did not allow for connecting to, and reading from, standby servers.
Major version releases are not considered “binary compatible” from one release to the next. That means that you can't simply install the new 9.2 binaries on a running 9.1 server, restart the database, and be good. This is because major version releases often include new features, changed features, on-disk/in-memory data format changes, schema / architecture changes, and other backwards incompatibilities. This means that, to perform a major version upgrade, you need to either:
- Perform a dump of your current database and restore it on a fresh installation running the new version, which can be very time consuming for large databases.
- Perform an often complicated, and sometimes time consuming, in-place upgrade using special tools provided by the PostgreSQL (pg_upgrade) and MySQL (mysql_upgrade) teams. (Replicated setups only add to the fun here.)
See Database Version Upgrade Policies for more details about upgrading to a major database version on Engine Yard.
If you would like assistance converting data from one database to another or are interested in performing an in-place upgrade, please contact Engine Yard Professional Services.
Minor database version releases
Both databases often do minor version releases. In this case the third part of the version number, Z, is increased. For example, MySQL at one point went from 5.5.31 to 5.5.32. Minor version releases are limited to security and bug fixes that don't require major schema / architectural changes to the database server and client applications or protocols.
Minor version releases are binary compatible upgrades such that if you want to upgrade from PostgreSQL 9.2.3 to 9.2.4 all you need to do is install the 9.2.4 binaries over the 9.2.3 binaries and restart your database server.
This table provides other resources related to database versioning.
|For more information about...||See...|
|PostgreSQL versioning||PostgreSQL versioning policy|
|MySQL versioning||Choosing MySQL version|
|Engine Yard database versioning||Database Version Upgrade Policies|