The lasted Postgresql 10 brings in a number of exciting new features: parallel queries, partitions, new sequence and logical replication. Below are steps to upgrade v9.6 to v10. It has similar steps as described in my previous post “Postgresql upgrade from 9.3 to 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

After the above, the installer would have installed the new version and initiated db files on /var/lib/postgresql/10 (it’s odd the name is not 10.0)

  • If your old version was running when the above was done, Postgres v10 would listen on the next port, e.g. 5433. Keeping the old version running is convenient as you can restore its data on the fly, refer to below for detail.
  • Restore from backup data
  • Or 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:

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

Pg_hba.con

#2017.10.26 scram-sha-256 is supported by v10 but not PgAdmin4 yet

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 = 5433                  # (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

  • Stop the new server(10) if it’s up and running

  • Restart the new server (10) – so the above changes are effected

  • Use below to stop the old instance

  • 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 old service
  • Then you can modify postgresql.conf to change port number back to 5432 and then restart the service
Postgresql upgrade from 9.6 to 10.0
Tagged on:         

Leave a Reply

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

26 + = 31

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