This tutorial shows how to upgrade PostgreSQL from 9.4 (openSUSE 42.2) to 9.6 (openSUSE 42.3)

1. Stop postgresql and disable service

   systemctl stop postgresql.service
   systemctl disable postgresql.service

2. Install PostgreSQL 9.6 (9.4 should still be installed after the update)

   zypper -n install postgresql96 postgresql96-server postgresql96-contrib

3. Move current cluster and create new data dir with user postgres

   mv /var/lib/pgsql/data/ /var/lib/pgsql/data94/
   su postgres -c "mkdir /var/lib/pgsql/data"
   

4. Enable service, start it to create new database cluster at data folder, and stop service again to prepare the following upgrade

   systemctl enable postgresql.service
   systemctl start postgresql.service
   systemctl stop postgresql.service

5. Run pg_upgrade where -b is old bin dir, -B is new bin dir, -d is old data dir, and -D new data dir. This must run as user postgres ( su - postgres )

   su - postgres -c "pg_upgrade -b /usr/lib/postgresql94/bin/ -B /usr/lib/postgresql96/bin/ -d /var/lib/pgsql/data94/ -D /var/lib/pgsql/data"

6. Copy the pg_hba.conf from old data dir to the new one (if you changed something at postgresql.conf you could copy that to the new data folder too). After that, start the postgresql service.

   cp /var/lib/pgsql/data94/pg_hba.conf /var/lib/pgsql/data
   systemctl start postgresql.service

7. Try to login to postgresql, Version 9.6.x should be printed, leave with \q

    # psql -U postgres
   psql (9.6.4)
   Type "help" for help.
   
   postgres=# \q

8. If you don't need the old cluster data, run this:

   /var/lib/pgsql/delete_old_cluster.sh