Thursday, May 14, 2020

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
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
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
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

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


Anonymous Anonymous said...

Thank you for your post. I have to upgrade postgresql 9.2 to 9.6 over a CenOS 7 server and first time I tried to do this a year ago (I've had health problems for almost a year), I found myself with the same problem: installation threw symlinks errors. As this was supposed to be performed over one of the main production servers I was afraid these errors would affect other software installed on the server. I was thinking in this approach but wasn't sure to take over it. Now I see it's not a bad decission.

04 February, 2021 15:15  

Post a comment

<< Home