Postgresql upgrade from 9.3 to 9.6

Have been using Postgresql 9.3 for a while until recently after upgrading to PgAdmin4 1.3, I cannot import my backup due to compatibility issue, so I decided to upgrade Postgresql to the current version 9.6.

Below are my notes on steps:

• Create the file /etc/apt/sources.list.d/pgdg.list, and add a line for the repositorydeb http://apt.postgresql.org/pub/repos/apt/ {CodeName}-pgdg main ( lsb_release -c to get your Ubuntu code name, e.g. xenial for 16.04)

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

• Import the repository signing key, and update the package lists

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc |   sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-9.6

By doing the above, the installer would have installed the new version and initiated db files on /var/lib/postgresql/9.6

• Restore your previous pg_hba.conf and any postgresql.conf modifications (in my case only add the following line to allow same net logins)

Pg_hba.con

#2017.03.22 from my management console

host    all             all             samenet                 md5

postgresql.conf

 listen_addresses = ‘*’ # what IP address(es) to listen on;

# comma-separated list of addresses;

# defaults to ‘localhost’; use ‘*’ for all

# (change requires restart)

port = 5432                  # (change requires restart)

Note the installation would create following folders:

/etc/postgresql/x.x –  for actual config files

/usr/lib/postgresql/x.x –  for binary

/usr/share/postgresql/x.x – sample config files

/var/lib/postgresq/x.x  – actual data file

1. Make sure to rename /usr/lib/postgresql/x.x/bin/pg_ctl to something like pg_ctl.old. Doing this so that the init.d/postgresql won’t start the service
2. Start the new server (9.6)

/usr/lib/postgresql/9.6/bin/postgres -D /var/lib/postgresql/9.6/main -c config_file=/etc/postgresql/9.6/main/postgresql.conf

3. Restore from backup data

/usr/lib/postgresql/9.6/bin/psql -d postgres -f outputfile

The least downtime can be achieved by installing the new server in a different directory and running both the old and the new servers in parallel, on different ports. Then you can use something like:

pg_dumpall -p 5432 | psql -d postgres -p 5433

Use below to stop the old instance

/usr/lib/postgresql/9.3/bin/pg_ctl -D /var/lib/postgresql/9.3/main stop

Below are installation of Postgres 10, if you have pg_ctl not found issue

sudo apt install postgresql-10 postgresql-contrib postgresql-client

Also useful command to show what’s installed

apt list –installed | grep -i postgres