Postgresql upgrade on CentOS

Our CentOS 7.5 machine had PostgreSQL version 9.2.24. But the machine doing nightly backups had Debian 9, and PostgreSQL 9.6.10. This turned out to be a problem. pg_restore 9.2 cannot restore backups which were made by 9.6, giving this cryptic error:

pg_restore: [archiver] unsupported version (1.13) in file header

So the better long-term solution seemed to be to upgrade PostgreSQL on CentOS. The instructions in "CentOS: Upgrade PostgreSQL from 9.2 to 9.6" were very helpful. There were just a few details I needed to do differently.

yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
yum install postgresql96-server

This gave a list of

failed to link /usr/bin/psql -> /etc/alternatives/pgsql-psql: /usr/bin/psql exists and it is not a symlink
failed to link /usr/bin/clusterdb -> /etc/alternatives/pgsql-clusterdb: /usr/bin/clusterdb exists and it is not a symlink
...etc.

It can be fixed at the end.

systemctl stop postgresql

Hack pg_ctl

echo '#!/bin/bash' > /usr/bin/pg_ctl
echo '"$0"-orig "${@/unix_socket_directory/unix_socket_directories}"' >> /usr/bin/pg_ctl
chmod +x /usr/bin/pg_ctl

su postgres ## Exit MC first, or it hangs!

Our databases are too big to live in /var/lib/pgsql, so we have them on a separate disk mounted in /mnt/postgres

mkdir -p /mnt/postgres/9.6/data

/usr/pgsql-9.6/bin/initdb -D /mnt/postgres/9.6/data

Now, pg_upgrade may fail if the locale and or charset of the new cluster is not the same as that of the database to be upgraded.
In that case, you may have an old initdb.log in /var/lib/pgsql which would show how the original database was initialised:
Mine had this:

The database cluster will be initialized with locale “en_US.UTF-8”.
The default database encoding has accordingly been set to “UTF8”.

But my current locale had various other settings like

LC_TIME=en_DK.UTF-8
LC_COLLATE=de_CH.UTF-8
etc.

So to make pg_upgrade happy, I had to
delete /mnt/postgres/9.6/data
run initdb again with the correct locale :

LC_ALL=en_US.UTF-8 /usr/pgsql-9.6/bin/initdb -D /mnt/postgres/9.6/data

(maybe the --no-locale option would also have worked or using the correct --locale= or --lc-*= options. See man initdb)

/usr/pgsql-9.6/bin/pg_upgrade --verbose --old-datadir /mnt/postgres/data/ --new-datadir /mnt/postgres/9.6/data/ --old-bindir /usr/bin/ --new-bindir /usr/pgsql-9.6/bin/

mcedit /etc/systemd/system/postgresql-9.6.service

.include /lib/systemd/system/postgresql-9.6.service
[Service]
Environment=PGDATA=/mnt/postgres/9.6/data

systemctl daemon-reload
systemctl start postgresql-9.6
systemctl status postgresql-9.6
systemctl enable postgresql-9.6
systemctl disable postgresql.service

mv -f /usr/bin/pg_ctl{-orig,}

mkdir -p /usr/pgsql-9.2/bin/
for f in /usr/pgsql-9.6/bin/*; do b=$(basename "$f"); mv -v /usr/bin/$b /usr/pgsql-9.2/bin/ ; done

ln -si /etc/alternatives/pgsql-psql /usr/bin/psql
ln -si /etc/alternatives/pgsql-clusterdb /usr/bin/clusterdb
ln -si /etc/alternatives/pgsql-createdb /usr/bin/createdb
ln -si /etc/alternatives/pgsql-createlang /usr/bin/createlang
ln -si /etc/alternatives/pgsql-createuser /usr/bin/createuser
ln -si /etc/alternatives/pgsql-dropdb /usr/bin/dropdb
ln -si /etc/alternatives/pgsql-droplang /usr/bin/droplang
ln -si /etc/alternatives/pgsql-dropuser /usr/bin/dropuser
ln -si /etc/alternatives/pgsql-pg_basebackup /usr/bin/pg_basebackup
ln -si /etc/alternatives/pgsql-pg_dump /usr/bin/pg_dump
ln -si /etc/alternatives/pgsql-pg_dumpall /usr/bin/pg_dumpall
ln -si /etc/alternatives/pgsql-pg_restore /usr/bin/pg_restore
ln -si /etc/alternatives/pgsql-reindexdb /usr/bin/reindexdb
ln -si /etc/alternatives/pgsql-vacuumdb /usr/bin/vacuumdb