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)

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

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)

  1. Restore from backup data

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:

Use below to stop the old instance

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

Postgresql upgrade from 9.3 to 9.6
Tagged on:                 

One thought on “Postgresql upgrade from 9.3 to 9.6

Leave a Reply

Your email address will not be published. Required fields are marked *

− 1 = 5

This site uses Akismet to reduce spam. Learn how your comment data is processed.