What a mostly pleasant experience it was to upgrade to PostgreSQL 9.5 on Debian unstable. It was available in the repository just one day after the upstream release.
After an apt-get upgrade I am notified that the postgresql-9.4 package is now obsolete and postgresql-9.5 can be installed. Fine, done that.
Then I can run su postgres -c "pg_dumpall >/var/lib/postgresql/dump.sql" to backup my current databases. The Debian alternatives system ensures that the 9.4 version of pg_dumpall is used.
I then copy settings from /etc/postgresql/9.4/main/pg_hba.conf to 9.5, using Emacs ediff, and Tramp sudo.
Now the old version can be removed, along with its data and config files:
systemctl stop postgresql
apt-get remove --purge postgresql-9.4 postgresql-client-9.4Starting up the new version systemctl start postgresql, every thing works fine. And finally su postgres -c "psql -d postgres -f /var/lib/postgresql/dump.sql" loads up my previous data.
Now a quick poke around with psql verifies that everything is already there (good, because the old databases are already deleted).
One problem occurs that leaves me scratching my head. My Python app using Psycopg can’t connect to the PostgreSQL server, saying it’s not runnning. Why is this so?
Well it turns out that the listen port in /etc/postgresql/9.5/main/postgresql.conf is set to 5433, not its well-known value 5432. This not only affects the TCP server, but also the filename of the UNIX domain socket.
Presumably this port is auto-configured by the Debian package so that two parallel installed PostgreSQL servers can run simultaneously. But nonetheless I fell into the trap.