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

Mobile skin and plugin for Roundcube webmail

The "Melanie2" mobile skin and plugin seems to make Roundcube work on phones. And it is easier to install than it would seem when only briefly looking at the github instructions.

These are the commands I needed on a Debian 10 ("Buster") machine where roundcube 1.3.10 was installed. (the base install was done from the Debian repositories: apt install roundcube roundcube-sqlite3. Instead of sqlite3, it is also possible to use PostgreSQL or MySQL with the roundcube-pgsql or roundcube-mysql packages).

For the mobile stuff:

Create a directory to store the extra stuff and make upgrades easier:

mkdir -p /opt/roundcube-stuff
cd /opt/roundcube-stuff/

Get the files from Github:

git clone https://github.com/messagerie-melanie2/roundcube_skin_melanie2_larry_mobile
git clone https://github.com/messagerie-melanie2/roundcube_jquery_mobile
git clone https://github.com/messagerie-melanie2/roundcube_mobile

Instead of renaming and copying the directories, create symlinks and copy them to roundcube's skins and plugins folders:

ln -si $(pwd)/roundcube_skin_melanie2_larry_mobile/ melanie2_larry_mobile
ln -si $(pwd)/roundcube_jquery_mobile/              jquery_mobile
ln -si $(pwd)/roundcube_mobile                      mobile

cp -vd /opt/roundcube-stuff/melanie2_larry_mobile   /var/lib/roundcube/skins/
cp -vd /opt/roundcube-stuff/jquery_mobile           /var/lib/roundcube/plugins/
cp -vd /opt/roundcube-stuff/mobile                  /var/lib/roundcube/plugins/

Finally, add 'mobile' to the $config['plugins'] array in /etc/roundcube/config.inc.php. If doing it by hand is too much work, copy/pasting this should work:

echo 'array_push( $config["plugins"], "mobile" );' | tee -a /etc/roundcube/config.inc.php
#or:
## echo '$config["plugins"][] = "mobile";' | tee -a /etc/roundcube/config.inc.php