How to upgrade PostgreSQL-server to 9.6

If you have followed my posts on how to install Jira, Confluence and Bitbucket in past, you probably have ended up installing Posgresql-server version 9.2. That version still is the latest version that Centos7 has on its official repository.

The problem is Atlassian dropped support for that version few months back but you still could install the updates. Last month when I updated to the latest version of Jira (7.11.0), I had an oops!! moment when server failed to start due to unsupported database server. Luckily I had a recent full back-up of the server and could recover easily.

I then needed to go through these steps to update the database server to the latest.

Note: The recommended pg_upgrade command which does an in-place update didn’t work for me. That was because of the way I have installed the older version in first place. So needed to use a dump and restore option which was still quick enough and did the trick.

Environment:

Current Server

• version: postgresql-server 9.2
• data directory: /var/lib/pgsql/data
• bin directory: /usr/bin/

New server:

• postgresql-server 9.6
• data directory: /var/lib/pgsql/9.6/data/
• bin directory: /usr/pgsql-9.6/bin

#stop any application that is using the database
sudo systemctl stop jira

# Backup old server

sudo su postgres
cd ~
pg_dumpall > pgsql-server-dump-20180721
exit

# in my installation the bin files have been copied to the root bin directory. This makes new version installation fail. New version uses its own directory for the bin and places links in main bin. 
#Note, this step will break your current database server. So if for any reason you need to rollback to the old version, you need to restore these files or create a link with the same names if you know what you are doing.

sudo systemctl stop postgresql
sudo mkdir -p /usr/pgsql-9.2/bin/
sudo mv \
/usr/bin/postgres \
/usr/bin/postmaster \
/usr/bin/postgresql-check-db-dir \
/usr/bin/postgresql-setup \
/usr/bin/psql \
/usr/bin/clusterdb \
/usr/bin/createdb \
/usr/bin/createlang \
/usr/bin/createuser \
/usr/bin/dropdb \
/usr/bin/droplang \
/usr/bin/dropuser \
/usr/bin/pg_basebackup \
/usr/bin/pg_config \
/usr/bin/pg_controldata \
/usr/bin/pg_ctl \
/usr/bin/pg_dump \
/usr/bin/pg_dumpall \
/usr/bin/pg_receivexlog \
/usr/bin/pg_resetxlog \
/usr/bin/pg_restore \
/usr/bin/reindexdb \
/usr/bin/vacuumdb \
/usr/pgsql-9.2/bin/


# Install new version from official repository

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

# initialise the database server
sudo /usr/pgsql-9.6/bin/postgresql96-setup initdb

# copy config files across from the old server
sudo su postgres
cd ~
mv 9.6/data/pg_hba.conf 9.6/data/pg_hba.conf-bkup
cp data/pg_hba.conf 9.6/data/
mv 9.6/data/postgresql.conf 9.6/data/postgresql.conf-bkup
cp data/postgresql.conf 9.6/data/postgresql.conf
exit
# start the new server
sudo systemctl start postgresql-9.6

# restore the server database
# this will include system database and users
sudo su postgres
cd ~
/usr/pgsql-9.6/bin/psql -d postgres -f pgsql-server-dump-20180721
exit

sudo systemctl restart postgresql-9.6

# disable the old server and enable the new one so the new server will start after a reboot
sudo systemctl disable postgresql
sudo systemctl enable postgresql-9.6

sudo reboot

Your applications should work straight away with the new version. Now you can go ahead with updating your applications if you needed.