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
  1. 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

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.

12 − 7 =

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