PostgreSQL Upgrade

PostgreSQL_Upgrade.md

Inspired by this tip

Example of upgrading PostgreSQL from 9.3 to latest 9.5

Stop running application and services

Create a database backup

Stop any running postgres instance

sudo service postgresql stop

Install new postgres version 9.5

Infos: https://www.postgresql.org/download/linux/ubuntu/

Edit port for old postgresql

sudo vim /etc/postgresql/9.3/main/postgresql.conf
# search for "port", replace 5432 -> 5433

Install new Postgresql 9.5

sudo apt-cache search postgresql
sudo apt-get install postgresql-9.5 postgresql-contrib-9.5

Do you want to continue? Y

Answer to question: "Configuring postgresql-common. What do you want to do about modified configuration file createcluster.conf?"

• install the package maintainer's version

After this will start Postgresql 9.3 and 9.5 servers. Old one starts on port 5433 and new one on 5432 (default).

Test old / new versions are running

psql -p 5433
select version(); # => psql (9.5.3, server 9.3.13)

sudo su postgres ; psql
select version(); # => psql (9.5.3)

(exit postgres user bash)

Migration of data

At this point both postgres servers are running

sudo service --status-all

# [+] postgresql (should be running)

Double check postgres is listening on ports

netstat -tulnp | grep 543

Start tmux session on server (in case session hangs)

tmux

The Migration

cd /usr/lib/postgresql/9.5/bin/
sudo su postgres
./pg_dumpall -p 5433 -U postgres | ./psql -p 5432 -U postgres

Disable start of old Postgres server

Stop both running postgres servers

sudo service postgresql stop

Disable old Postgres 9.3 from starting

sudo vim /etc/postgresql/9.3/main/start.conf
# replace auto -> disabled

Start postgres servers

sudo service postgresql start

Only port 5432 should listen (Posgresql 9.5 running there)

netstat -tulnp | grep 543