upgrade PostgreSQL 9.5 to 9.6 on Ubuntu

I just upgraded PostgreSQL 9.5 to 9.6 on Ubuntu and thought I'd share my findings, as there are a couple of OS/package-specific nuances of which to be aware.

(I didn't want to have to dump and restore data manually, so several of the other answers here were not viable.)

In short, the process consists of installing the new version of PostgreSQL alongside the old version (e.g., 9.5 and 9.6), and then running the pg_upgrade binary, which is explained in (some) detail at https://www.postgresql.org/docs/9.6/static/pgupgrade.html .

The only "tricky" aspect of pg_upgrade is that failure to pass the correct value for an argument, or failure to be logged-in as the correct user or cd to the correct location before executing a command, may lead to cryptic error messages.

On Ubuntu (and probably Debian), provided you are using the "official" repo, deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main, and provided you haven't changed the default filesystem paths or runtime options, the following procedure should do the job.

Install the new version (note that we specify the 9.6, explicitly):

sudo apt install postgresql-9.6

Once installation succeeds, both versions will be running side-by-side, but on different ports. The installation output mentions this, at the bottom, but it's easy to overlook:

Creating new cluster 9.6/main ...
  config /etc/postgresql/9.6/main
  data   /var/lib/postgresql/9.6/main
  locale en_US.UTF-8
  socket /var/run/postgresql
  port   5433

Stop both server instances (this will stop both at the same time):

sudo systemctl stop postgresql

Switch to the dedicated PostgreSQL system user:

su postgres

Move into his home directory (failure to do this will cause errors):

cd ~

pg_upgrade requires the following inputs (pg_upgrade --help tells us this):

When you run pg_upgrade, you must provide the following information:
  the data directory for the old cluster  (-d DATADIR)
  the data directory for the new cluster  (-D DATADIR)
  the "bin" directory for the old version (-b BINDIR)
  the "bin" directory for the new version (-B BINDIR)

These inputs may be specified with "long names", to make them easier to visualize:

  -b, --old-bindir=BINDIR       old cluster executable directory
  -B, --new-bindir=BINDIR       new cluster executable directory
  -d, --old-datadir=DATADIR     old cluster data directory
  -D, --new-datadir=DATADIR     new cluster data directory

We must also pass the --new-options switch, because failure to do so results in the following:

connection to database failed: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/lib/postgresql/.s.PGSQL.50432"?

This occurs because the default configuration options are applied in the absence of this switch, which results in incorrect connection options being used, hence the socket error.

Execute the pg_upgrade command from the new PostgreSQL version:

/usr/lib/postgresql/9.6/bin/pg_upgrade --old-bindir=/usr/lib/postgresql/9.5/bin --new-bindir=/usr/lib/postgresql/9.6/bin --old-datadir=/var/lib/postgresql/9.5/main --new-datadir=/var/lib/postgresql/9.6/main --old-options=-cconfig_file=/etc/postgresql/9.5/main/postgresql.conf --new-options=-cconfig_file=/etc/postgresql/9.6/main/postgresql.conf

Logout of the dedicated system user account:

exit

The upgrade is now complete, but, the new instance will bind to port 5433 (the standard default is 5432), so keep this in mind if attempting to test the new instance before "cutting-over" to it.

Start the server as normal (again, this will start both the old and new instances):

systemctl start postgresql

If you want to make the new version the default, you will need to edit the effective configuration file, e.g., /etc/postgresql/9.6/main/postgresql.conf, and ensure that the port is defined as such:

port = 5432

If you do this, either change the old version's port number to 5433 at the same time (before starting the services), or, simply remove the old version (this will not remove your actual database content; you would need to use apt --purge remove postgresql-9.5 for that to happen):

apt remove postgresql-9.5

The above command will stop all instances, so you'll need to start the new instance one last time with:

systemctl start postgresql

As a final point of note, don't forget to consider pg_upgrade's good advice:

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
    ./analyze_new_cluster.sh

Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh