1

What is the recommended method to perform a minor version upgrade of postgres on linux (Centos)? I am looking at upgrading from 9.5.4 to 9.5.5.

  • 1
    what operating system? – Evan Carroll Oct 30 '17 at 17:31
2

Just do a dist-upgrade or the like on Linux, or install the newer versions on Windows/OSX. Minor versions ensure total compatibility with the data directory (heap, and index).

Stands to reason though, you'll have to stop the database during the upgrade.

From the official docs on versioning

Minor releases are numbered by increasing the last part of the version number. Beginning with version 10, this is the second part of the version number, e.g. 10.0 to 10.1; for older versions this is the third part of the version number, e.g. 9.5.3 to 9.5.4. The PostgreSQL team only adds bug fixes to minor releases. All users should upgrade to the most recent minor release as soon as possible. While upgrades always have some risk, PostgreSQL minor releases fix only frequently-encountered, security, and data corruption bugs to reduce the risk of upgrading. The community considers not upgrading to be riskier than upgrading.

So essentially, PostgreSQL is so conservative that minor-version number upgrades are only issued when there is more risk to not-upgrading than upgrading. I've been working with PostgreSQL for about 15 years. I don't do any additional backups nor take any special precautions when doing minor upgrades. Not saying you can't -- even the docs say they come with "some risk."

| improve this answer | |
  • Just to be safe can I do a side-side upgrade? Install 9.5.5 and then point the db to it? – sharadov Oct 30 '17 at 18:14
  • 2
    you can, but that is in my opinion a total waste of time. =) – Evan Carroll Oct 30 '17 at 18:14
  • @sharadov It's not a bad idea: then you can prewarm your new version before directing traffic there. If you're worried about something breaking, you can switch your users in batches (probably not necessary given the conservative release policy, but couldn't hurt!). – DylanYoung Nov 23 '18 at 16:17
  • @sharadov since you had concerns, if you haven't already, I would recommend implementing a comprehensive backup strategy: occasional full backups and frequent incremental/differential backups, spread across at least two locations. While a minor upgrade is not dangerous in the grand scheme of things, they are on a long list of "things that can go wrong". IMHO these rules apply from huge corporations to tiny startups, and bring a lot of comfort. – moodboom Dec 27 '18 at 13:22
4

In short, a minor upgrade of a Postgres installation (also called cluster) is rather simple:

  • stop the cluster
  • replace the Postgres binaries with the new version (the details may differ here, depending on OS and flavour)
  • restart the cluster

For a production system and a DBA that tries to be more careful, there are two more things:

  • a backup could be done before the upgrade.
  • reading the release notes is a must before executing the upgrade.

    There are sometimes cases, that even a minor upgrade requires extra steps. Sometimes it's rebuilding some indexes, sometimes more weird stuff, etc. More often than not, you don't have to do anything extra, but reading these notes is essential as when some extra work is needed, it is to prevent some bug from not being fixed or to invert some corruption, etc.

    If you want to upgrade (example) from 9.5.4 to 9.5.9, you need to read all the intermediate release notes, too (9.5.5, 9.5.6, 9.5.7, 9.5.8, 9.5.9). For the specific 9.5.5 notes, see below.

So the steps would be:

  • reading the release notes and altering the procedure accordingly
  • stop all connections and prevent new ones
  • take a backup (either of the whole cluster with with pg_basebackup or of each database with pg_dump)
  • stop the cluster
  • replace the Postgres binaries with the new version
  • restart the cluster

If there is replication involved, you'll have to consider that, too:

For a production system and a DBA that tries to be more careful, a backup could be done before the upgrade:

  • stop all connections and prevent new ones
  • wait for all replicas to catch up
    and take backup
  • stop all clusters (master and replicas)
  • replace the Postgres binaries with the new version (in each cluster)
  • restart all clusters

A variation in this case is the backing up can be skipped, if we keep one or more replicas stopped and with the old binaries. If for any reason, we need to go back to the old minor version, we can promote that replica to master.


For the specific 9.5.5 release for example, the release notes say:

E.5. Release 9.5.5

Release date: 2016-10-27

This release contains a variety of fixes from 9.5.4. For information about new features in the 9.5 major release, see Section E.10.

E.5.1. Migration to Version 9.5.5

A dump/restore is not required for those running 9.5.X.

However, if your installation has been affected by the bug described in the first changelog entry below, then after updating you may need to take action to repair corrupted free space maps.

Also, if you are upgrading from a version earlier than 9.5.2, see Section E.8.

So, we need to read this first changelog, too:

E.5.2. Changes

Fix WAL-logging of truncation of relation free space maps and visibility maps (Pavan Deolasee, Heikki Linnakangas)

It was possible for these files to not be correctly restored during crash recovery, or to be written incorrectly on a standby server. Bogus entries in a free space map could lead to attempts to access pages that have been truncated away from the relation itself, typically producing errors like "could not read block XXX: read only 0 of 8192 bytes". Checksum failures in the visibility map are also possible, if checksumming is enabled.

Procedures for determining whether there is a problem and repairing it if so are discussed at https://wiki.postgresql.org/wiki/Free_Space_Map_Problems.

So, dump/restore is not required for moving from 9.5.4 to 9.5.5 and you only need to check for this specific issue. Check your error logs and you are good to go ;)

| improve this answer | |
  • Since this is a prod server, I was planning to install 9.5.5 on the same server and then just point the database to it, so that I can revert back should we run into issues. – sharadov Oct 30 '17 at 18:42
  • 1
    I'm not sure what exactly you mean with "point the database to it". – ypercubeᵀᴹ Oct 30 '17 at 18:44
  • We use an environment file, so we just need to point the env variable to the new ver. – sharadov Oct 30 '17 at 18:49
  • 1
    Has there ever been a case where a dump restore was required in the change logs for a minor upgrade version? I think they just put that there for boilerplate. If a dump restore was required, it'd be a major version upgrade.. right? – Evan Carroll Oct 30 '17 at 19:18
  • 1
    @EvanCarrollQWERHJKL correct, I can't find a case when it was required for a minor version. Edited out. – ypercubeᵀᴹ Oct 30 '17 at 19:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.