Postgres minor version upgrade

Question

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.

Answer 1

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

Answer 2

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

Answer 3

yum update  postgresql-version