Hello!

This tutorial includes detailed steps on how to upgrade from old 9.2 postgresql server to new 9.6 postgresql server, with data migration. But you can also use this article if you want to install fresh 9.6 version - you just need to skip migration steps. You can also check this link for more information.

1. install PGDG RPM file for new postgresql server

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

(for the list of all options, check this link: https://yum.postgresql.org/repopackages.php)

You can check list of available packages (should list both versions: postgresql-server and postgresql96-server)

sudo yum list postgresql*

2. install new postgresl server

sudo yum install postgresql96-server

3. init db

sudo /usr/pgsql-9.6/bin/postgresql96-setup initdb

-->Upgrade steps (skip if required)

4. make sure both servers stopped

sudo systemctl stop postgresql

# and in case you have new service started:
# sudo systemctl stop postgresql-9.6

5. do the hack (for upgrade command to run)

sudo mv /usr/bin/pg_ctl{,-orig}
echo '#!/bin/bash'|sudo tee -a /usr/bin/pg_ctl
echo '"$0"-orig "${@/unix_socket_directory/unix_socket_directories}"' |sudo tee -a /usr/bin/pg_ctl
sudo chmod +x /usr/bin/pg_ctl

6. run upgrade

sudo su - postgres
/usr/pgsql-9.6/bin/pg_upgrade  -v -b /usr/bin/ -B /usr/pgsql-9.6/bin/ -d /var/lib/pgsql/data/ -D /var/lib/pgsql/9.6/data/
logout

7. undo the hack

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

8. remove old postgresql-server

sudo yum remove postgresql-server postgresql postgresql-libs

You can also remove old data directory, but make sure everything works well in new postgresql version before doing that:

sudo rm -rf /var/lib/pgsql/data/

<--End of Upgrade steps

9. disable old postgres from base repo

sudo vi /etc/yum.repos.d/CentOS-Base.repo

# append these lines to [base] and [updates] sections:

[base]
..
exclude=postgresql*

[updates]
..
exclude=postgresql*

10. update repo and check packages

sudo yum update
sudo yum list postgresql*
# should list postgresql96-server, but not postgresql server!

11. enable and start service

sudo systemctl enable postgresql-9.6.service
sudo systemctl start postgresql-9.6.service

12. export new path

echo 'export PATH=$PATH:/usr/pgsql-9.6/bin'|sudo tee -a /etc/profile.d/centos9.6.sh

13. login/logout and new path should be available. Now you can run psql command:

psql --version
psql (PostgreSQL) 9.6.11