08. Upgrade PostgreSQL 9.4 to 9.5 on CentOS/Ubuntu (non-Docker)

Follow this article if you want to upgrade PostgreSQL database engine from version 9.4 to version 9.5. on CentOS 7 or Ubuntu.

CentOS 7

1. Open Terminal

2. Make sure you will do all the installation steps as system user by executing following command:

root@ip-[your-ip-address]~# sudo su

 

3. Install PostgreSQL 9.5:

root@ip-[your-ip-address]~# yum update -y
root@ip-[your-ip-address]~# yum install https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-7-x86_64/pgdg-centos95-9.5-3.noarch.rpm
root@ip-[your-ip-address]~# yum install postgresql95-server postgresql95 -y
root@ip-[your-ip-address]~# /usr/pgsql-9.5/bin/postgresql95-setup initdb

 

4. Configure PostgreSQL 9.5

Edit the /var/lib/pgsql/9.5/data/postgresql.conf, look for a line as below and change it to:

port = 5433 # we want to make sure the port is not same as the one taken by PostgreSQL 9.4

Change the listen_addresses setting to an asterisk to listen to all available IP addresses:

listen_addresses = '*'

Save the file when you're done.

 

5. Initialize PostgreSQL 9.5

root@ip-[your-ip-address]~# /usr/pgsql-9.5/bin/postgresql95-setup initdb

 

6. Start and enable PostgreSQL service

root@ip-[your-ip-address]~# systemctl start postgresql-9.5.service
root@ip-[your-ip-address]~# systemctl enable postgresql-9.5.service

 

7. Verify that PostgreSQL service started successfully

Execute below command, if terminal shows a result containing Active: active (running) then you have successfully installed PostgreSQL 9.5.

root@ip-[your-ip-address]~# systemctl status postgres-9.5
postgresql-9.5.service - PostgreSQL 9.5 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-9.5.service; enabled; vendor preset: disabled)
   Active: active (running) since Tue 2017-06-13 10:47:25 UTC; 31min ago
Main PID: 2962 (postgres)
   CGroup: /system.slice/postgresql-9.5.service
           ├─2962 /usr/pgsql-9.5/bin/postgres -D /var/lib/pgsql/9.5/data
           ├─2963 postgres: logger process   
           ├─2965 postgres: checkpointer process   
           ├─2966 postgres: writer process   
           ├─2967 postgres: wal writer process   
           ├─2968 postgres: autovacuum launcher process   
           └─2969 postgres: stats collector process   

Jun 13 10:47:24 ip-10-0-10-234 systemd[1]: Starting PostgreSQL 9.5 database server...
Jun 13 10:47:24 ip-10-0-10-234 pg_ctl[2957]: < 2017-06-13 10:47:24.443 UTC >LOG:  redirecting log output to logging collector process
Jun 13 10:47:24 ip-10-0-10-234 pg_ctl[2957]: < 2017-06-13 10:47:24.443 UTC >HINT:  Future log output will appear in directory "pg_log".
Jun 13 10:47:25 ip-10-0-10-234 systemd[1]: Started PostgreSQL 9.5 database server.

Now we are ready to perform the upgrade.

 

8.  Upgrade PostgreSQL 9.4 to 9.5

Stop all PostgreSQL instances

root@ip-[your-ip-address]~# service postgresql-9.4 stop
root@ip-[your-ip-address]~# service postgresql-9.5 stop

 

Execute the upgrade and copy configuration files from PostgreSQL 9.4 to PostgreSQL 9.5:

root@ip-[your-ip-address]~# cd /tmp
root@ip-[your-ip-address] tmp]# sudo -u postgres /usr/pgsql-9.5/bin/pg_upgrade --old-bindir=/usr/pgsql-9.4/bin/ --new-bindir=/usr/pgsql-9.5/bin/ --old-datadir=/var/lib/pgsql/9.4/data/ --new-datadir=/var/lib/pgsql/9.5/data/
root@ip-[your-ip-address] tmp]# cp /var/lib/pgsql/9.4/data/postgresql.conf /var/lib/pgsql/9.5/data/postgresql.conf
root@ip-[your-ip-address] tmp]# cp /var/lib/pgsql/9.4/data/pg_hba.conf /var/lib/pgsql/9.5/data/pg_hba.conf

 

Start PostgreSQL 9.5

root@ip-[your-ip-address]~# service postgresql-9.5 start

Enable PostgreSQL 9.5 to start at system boot

root@ip-[your-ip-address]~# systemctl enable postgresql-9.5.service

Disable PostgreSQL 9.4 service

root@ip-[your-ip-address]~# systemctl disable postgresql-9.4.service

You have finished upgrading PostgreSQL 9.4 to PostgreSQL 9.5 on CentOS 7.

Ubuntu

1. Open Terminal

2. Make sure you will do all the installation steps as system user by executing following command:

root@ip-[your-ip-address]:/home/ubuntu# sudo su

 

3. Istall PostgreSQL 9.5:

Ubuntu 14

root@ip-[your-ip-address]:/home/ubuntu# sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
root@ip-[your-ip-address]:/home/ubuntu# sudo wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | sudo apt-key add -
root@ip-[your-ip-address]:/home/ubuntu# sudo apt-get update
root@ip-[your-ip-address]:/home/ubuntu# sudo apt-get install -y postgresql-9.5

Ubuntu 16

root@ip-[your-ip-address]:/home/ubuntu# sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
root@ip-[your-ip-address]:/home/ubuntu# sudo wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | sudo apt-key add -
root@ip-[your-ip-address]:/home/ubuntu# sudo apt-get update
root@ip-[your-ip-address]:/home/ubuntu# sudo apt-get install -y postgresql-9.5 

 

4. Verify that PostgreSQL instances are configured:

root@ip-[your-ip-address]:/home/ubuntu# sudo pg_lsclusters
9.4 main 5432 online postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log
9.5 main 5433 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log

 

5. Stop all PostgreSQL instances 

First you need to stop all the relevant database clusters. To stop all clusters, run:

root@ip-[your-ip-address]:/home/ubuntu# sudo /etc/init.d/postgresql stop

 

6. Upgrade PostgreSQL to 9.5

Execute below commands to upgrade PostgreSQL. Note that the second command needs to be entered in one line.

root@ip-[your-ip-address]:/home/ubuntu# cd /tmp
root@ip-[your-ip-address]:/home/ubuntu# sudo -H -u postgres /usr/lib/postgresql/9.5/bin/pg_upgrade -b /usr/lib/postgresql/9.4/bin -B /usr/lib/postgresql/9.5/bin -d /var/lib/postgresql/9.4/main -D /var/lib/postgresql/9.5/main -o ' -c config_file=/etc/postgresql/9.4/main/postgresql.conf' -O ' -c config_file=/etc/postgresql/9.5/main/postgresql.conf'

It will take a while to complete, depending on the size of your cluster or other factors.

7. Update PostgreSQL 9.5 listening port

Edit the /etc/postgresql/9.5/postgresql.conf, look for a line as below and change the port to 5432

port = 5432

8. Optionally, you might want to remove Postgres 9.4 cluster by following command

root@ip-[your-ip-address]:/home/ubuntu# sudo pg_dropcluster 9.4 main

9. Start PostgreSQL 9.5

root@ip-[your-ip-address]:/home/ubuntu# sudo pg_ctlcluster 9.5 main start

10. Verify that PostgreSQL 9.5 is running

Execute below command to verify PostgreSQL status. If you see the value of status column is online as the output, your have successfully upgraded PostgreSQL to 9.5.

root@ip-[your-ip-address]:/home/ubuntu# sudo pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.5 main    5432 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log
root@ip-[your-ip-address]:/home/ubuntu# 

You have finished upgrading PostgreSQL 9.4 to 9.5 on Ubuntu.