Menu

Upgrade PostgreSQL 9.4 to 9.5

In order to upgrade to qTest Manager 8.4.4 (or later), you must upgrade PostgreSQL from 9.4 to 9.5. Here are the instructions to perform this upgrade based on your server:

CentOS 7 (non-Docker)

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 (non-Docker)

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.

Windows

1. Download and Install

Download PostgreSQL 9.5 here.

After downloading, double click on the downloaded file and follow the installation wizard to install it.

When installation completes, verify that the PostgreSQL service is running by following below steps:

  • Open command prompt and type services.msc to open Services window 
  • On Services window, look for the service named postgresql-x64-9.5 and make sure its Status is Running and its Startup Type is set to Automatic. You should also notice that there are 2 postgres services running, one is PostgreSQL 9.4 and another one is PostgreSQL 9.5, as below screenshot

postgres-upgrade.png

2. Stop PostgreSQL services

On Services windows, perform following steps to stop both PostgreSQL services

  • Right clicking on a service named postgresql-x64-9.4 and select Stop in the popup menu
  • Right clicking on a service named postgresql-x64-9.5 and select Stop in the popup menu

stop-postgresql-services.png

3. Create a new Windows user

Create a new user on your PC named postgres. This is a Windows user, not a database user. Add it to your PC’s Administrators group.

create-postgres-windows-user.png

4. Grant permissions 

Go to Windows Explorer, give the Windows postgres account Full Control permission on C:\Program Files\PostgreSQL\ and all children.

grant-permission-to-postgres-windows-user.png

5. Edit pg_hba.conf files

Edit the pg_hba.conf files for both servers. They are in C:\Program Files\PostgreSQL\[version]\data. If they are stock, comment out ALL lines, then add the below lines at the bottom.

host all all 127.0.0.1/32 trust
host all all ::1/128 trust

6. Execute upgrade

Open a command prompt window in administrator mode. Run this command: RUNAS /USER:postgres “CMD.EXE”

runas-command.png

If being asked, enter the password of the postgres Windows user.

On the command prompt console, change directory to the folder C:\Program Files\PostgreSQL\9.5\bin and type below command, in one line.

C:\Program Files\PostgreSQL\9.5\bin>pg_upgrade.exe --old-datadir "C:\Program Files\PostgreSQL\9.4\data" --new-datadir "C:\Program Files\PostgreSQL\9.5\data" --old-bindir "C:\Program Files\PostgreSQL\9.4\bin" --new-bindir "C:\Program Files\PostgreSQL\9.5\bin"

Tip: you can copy above command and paste to the command prompt window then press Enter.

The upgrade process will start to execute, this will take a while to complete if you have a lot of data. Wait until this is done before continuing.

7. Update PostgreSQL port

Edit the postgresql.conf file of the new PostgreSQL 9.5 install at C:\Program Files\PostgreSQL\9.5\data\postgresql.conf and change the listening port to 5432.

NOTE: if your qTest and/or qTest Sessions are connecting to PostgreSQL on a port which is different than the default 5432, e.g. 9999, make sure you change the value to 9999

update-postgresql-9-5-port.png

8. Start service

In Services window, start PostgreSQL 9.5 service

start-postgres-9.5-service.png

9. Verify

Access to your qTest and/or qTest Sessions site to verify the application is working and the data are being shown.

If you created a postgres Windows account above, remove it.

Once you have verified that everything works properly, you can optionally uninstall the old PostgreSQL 9.4.

Subscribe To Our Blog
Powered by Zendesk