Automating checking for new versions of PostgreSQL

It is important to run the latest revision of the major branch of Postgres you
are using. While
the pgsql-announce mailing list is often touted as a canonical
way to become aware of new releases, a better method is to use
the check_postgres program, which will let you know if the version you are running needs to be
upgraded or not. An example usage:

Postgres version numbers come in three sections. The first two indicate the "major version"
of Postgres that you are running. The third number indicates the revision. You always want
to be using the highest revision available for your version. In the example above, Postgres
version 9.2 is being used, and it should be upgraded from revision 2 to revision 4. A change
in the revision number is known as a minor release; these are only done for important reasons,
such as security or important bug fixes. Read
the versioning policy page for more information.

When a new version of PostgreSQL is made, there are two general ways of communicating this
fact: the pgsql-announce mailing list, and
the versions.rss file on the postgresql.org
web site. While the mailing list is
low volume, it is not ideal as it contains posts about
conferences, and about other Postgres-related software. A better solution is to track
the versions.rss file. You could simply subscribe to it, but this will only tell you when the
file has been changed. The check_postgres program parses this file and compares the latest
revision to the version of Postgres that you are using.

To use it, simply call check_postgres and pass new_version_pg
as the action argument, as well as telling check_postgres which PostgreSQL
instance you are checking. For example, to check that the Postgres running on
your internal host "zuko" is up to date, just run:

check_postgres.pl --action=new_version_pg --dbhost=zuko

Here is what the default output looks like, for both a matching and
a non-matching version:

$ check_postgres.pl --action=new_version_pg --dbhost=appa
POSTGRES_NEW_VERSION_PG CRITICAL: Please upgrade to version 9.2.4 of Postgres. You are running 9.2.2$ check_postgres.pl --action=new_version_pg --dbhost=toph
POSTGRES_NEW_VERSION_PG OK: Version 9.2.4 is the latest for Postgres

Those examples are very Nagios specific, of course, as evidenced by those uppercase strings at the beginning
of the output. If you are using Nagios, it's a good idea to run this, perhaps once a day or more often. If
you are not using Nagios, you can make the output a little cleaner with the --simple argument:

$ check_postgres.pl --action=new_version_pg --dbhost=azula
Please upgrade to version 9.2.4 of Postgres. You are running 9.2.2$ check_postgres.pl --action=new_version_pg --dbhost=sokka
Version 9.2.4 is the latest for Postgres

One quick and simple trick is to make this into a cron job and add the --quiet argument, which
prevents all output if the check was "OK". In this way, cron will only send outout (e.g. mail
someone) when a new revision has been released. A cron example:

Once this alarm goes off, you should strive to update your clusters as soon as possible.
If you are using a packaging system, then it may be as simple as relying on it to
do the right thing, e.g. "yum update postgresql". If you are installing from source,
you will need the new tarball and can simply "make install" onto the existing
Postgres, and then restart it. Always check the release notes for the new revision - once in a blue
moon the update requires some other action, such as a reindex of certain types of indexes.

2 comments:

Well, if they have a stable, parseable page describing their versions, it would be easy enough. As far as I can tell, they do not. They may even have their own method of upgrade detection/announcement/push-installation. I will defer to someone from EDB on that.