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)
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-10
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
-
/usr/lib/postgresql/10/bin/psql -d postgres -f outputfile
- 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:
#switch to postgres sudo su postgres #use v10 pg_dumpall cd /usr/lib/postgresql/10/bin pg_dumpall -p 5432 | psql -d postgres -p 5433
- 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
cd /usr/lib/postgresql/10/bin/postgres ./pg_ctl -D /var/lib/postgresql/10/main stop
- Restart the new server (10) – so the above changes are effected
/usr/lib/postgresql/10/bin/postgres -D /var/lib/postgresql/10/main -c config_file=/etc/postgresql/10/main/postgresql.conf or sudo service postgresql stop sudo service postgresql start
- Use below to stop the old instance
/usr/lib/postgresql/9.6/bin/pg_ctl -D /var/lib/postgresql/9.6/main stop
- 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