Upgrading PostgreSQL service from 9.x or 10.x to 11 version on Ubuntu server

upgrade-postgresql-service-ubuntu.md

TL;DR

Create a backup:

sudo -Hiu postgres pg_dumpall > mybackup.sql

Install Postgres 11, and then:

sudo pg_dropcluster 11 main --stop
sudo pg_upgradecluster 9.x main
sudo pg_dropcluster 9.x main

(x is a sub-version number)

---

Install PostgreSQL 11:

# install **wget** if not already installed:
sudo apt install -y wget

# import the repository signing key:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# add repository contents to your system:
RELEASE=$(lsb_release -cs)
echo "deb http://apt.postgresql.org/pub/repos/apt/ ${RELEASE}"-pgdg main | sudo tee  /etc/apt/sources.list.d/pgdg.list

# install and launch the postgresql service:
sudo apt update
sudo apt -y install postgresql-11

Use dpkg -l | grep postgresql to check which versions of postgres are installed:

ii  postgresql-11                                 object-relational SQL database, version 11 server
ii  postgresql-9.6                                object-relational SQL database, version 9.6 server
ii  postgresql-client-11                          front-end programs for PostgreSQL 11
ii  postgresql-client-9.6                         front-end programs for PostgreSQL 9.6
ii  postgresql-client-common                      manager for multiple PostgreSQL client versions
ii  postgresql-common                             PostgreSQL database-cluster manager
ii  postgresql-contrib-9.6                        additional facilities for PostgreSQL

In my case, I will be performing the upgrade from 9.6 to 11 version, so at the next steps, I will replace the 9.x version with the actual 9.6 version.

Note: guide can be also used to upgrade PostgreSQL 10 version. Upgrade steps fully the same.

Run pg_lsclusters, your 9.6 and 11 main clusters should be "online".

pg_lsclusters 
Ver Cluster Port Status Owner    Data directory               Log file
9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
 11 main    5433 online postgres /var/lib/postgresql/11/main  /var/log/postgresql/postgresql-11-main.log

There already is a cluster "main" for 11 (since this is created by default on package installation). This is done so that a fresh installation works out of the box without the need to create a cluster first, but of course it clashes when you try to upgrade 9.6/main when 11/main also exists. The recommended procedure is to remove the 11 cluster with pg_dropcluster and then upgrade the 9.6/main with pg_upgradecluster.

Stop the 11 cluster and drop it.

sudo pg_dropcluster 11 main --stop

Upgrade the 9.6 cluster to the latest version.

sudo pg_upgradecluster 9.6 main

Your 9.6 cluster should now be "down".

pg_lsclusters 
Ver Cluster  Port Status Owner    Data directory               Log file
9.6 main     5433 down   postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
11  main     5432 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log

Check that the upgraded cluster works, then remove the 9.6 cluster.

sudo pg_dropcluster 9.6 main

Lastly, please make sure that in the postgresql.conf for new cluster cluster_name parameter is equals to '11/main', otherwise replace the existing value with '11/main':

sudo nano /etc/postgresql/11/main/postgresql.conf

change cluster_name = '9.6/main' to cluster_name = '11/main'.

@ShvaykaD I managed to upgrade the db with some 'hack' in pg_upgradecluster script. I have to explicitly include this -o shared_preload_libraries=timescaledb when the script is creating new cluster.

Although during the process, there are a lot of error messages like the following but the cluster was created successfully.

pg_restore: [archiver (db)] could not execute query: ERROR:  operation not supported on chunk tables
    Command was: ALTER TABLE _timescaledb_internal._hyper_1_11_chunk ENABLE TRIGGER ALL;


pg_restore: [archiver (db)] Error from TOC entry 4100; 0 18391 TABLE DATA _hyper_1_12_chunk postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  operation not supported on chunk tables
    Command was: ALTER TABLE _timescaledb_internal._hyper_1_12_chunk DISABLE TRIGGER ALL;


pg_restore: [archiver (db)] could not execute query: ERROR:  operation not supported on chunk tables
    Command was: ALTER TABLE _timescaledb_internal._hyper_1_12_chunk ENABLE TRIGGER ALL;


pg_restore: [archiver (db)] Error from TOC entry 4101; 0 18403 TABLE DATA _hyper_1_13_chunk postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  operation not supported on chunk tables
    Command was: ALTER TABLE _timescaledb_internal._hyper_1_13_chunk DISABLE TRIGGER ALL;

Then, I managed to upgrade to Thingsboard version 2.5.1 without any issue.

Is it possible that there is a proper way to upgrade postgres with timescaledb?

UPDATE: I found this upgrade instruction https://docs.timescale.com/latest/using-timescaledb/update-db. Have not tried it yet

Hi, is there a recommendation for larger databases? pg_upgradecluster returns after about 4 hours:

pg_restore: [archiver (db)] error returned by PQputCopyData: server closed the connection unexpectedly

I did not lose connection to the server. Looking up similar posts online say that it's due to timeouts.

UPDATE: It turns out my server was running out of space. I monitored available space with df -h and came to this conclusion. Increasing the server size solved my issue. pg_upgrade_cluster makes a copy of your db, so I would suggest checking you have enough space to accommodate that.