This is a new post based on my previous “Postgresql upgrade from 9.6 to 10.0“, with necessary updates and corrections.
The latest Postgresql 12 (12.1) further improved performance on parallel queries, partitions, automatic (but overridable) inlining of common table expressions (CTEs) . It’s in my view that it’s having more and more “Enterprise” features that are only available in those commercial databases such as Oracle and SQL server.
By following the following steps, you can easily upgrade your Postgresql:
- 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-12
- After the above, the installer would have installed the new version and initiated db files on /var/lib/postgresql/12 . If your old version was running when the above was done, Postgres v12 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. If the old instance is still running, make sure you specify -p 5433 as otherwise the restore will be using port 5432 which is to restore to your PG10 instance
/usr/lib/postgresql/12/bin/psql -p 5433 -f outputfile
- Alternatively 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 below to transfer data:
sudo su postgres#use v10 pg_dumpallcd /usr/lib/postgresql/10/binpg_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
#2019.11.26 added to allow samenet access
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/12/bin/postgres
./pg_ctl -D /var/lib/postgresql/12/main stop
- Restart the new server (12) – so the above changes are effected
/usr/lib/postgresql/12/bin/postgres -D /var/lib/postgresql/12/main -c config_file=/etc/postgresql/12/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.
- On my Ubuntu 18.04, it appears the above doesn’t work anymore so have to use systemctl stop postgresql@10-main.service to stop the service and then systemctl mask postgresql@10-main.service to mask the service
- Then you can modify postgresql.conf to change port number back to 5432 and then restart the service