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

Hi, I am using timescaledb for Thingsboard's ts data. Will this script work too?

@skangmy yes, however after the cluster upgrades you need manually upgrade the timescaledb extension by executing the next command:

   psql -U user_name -d database_name -h host -W -X -c 'ALTER EXTENSION timescaledb UPDATE;'

where:

user_name - is your DB user name(default is postgres).
database_name - is ThingsBoard db name (default is thingsboard)
host - the host address where DB installed(if it is local setup, should be 127.0.0.1).

@ShvaykaD I got the following error when running pg_upgradecluster

FATAL:  extension "timescaledb" must be preloaded
HINT:  Please preload the timescaledb library via shared_preload_libraries.

This can be done by editing the config file at: /etc/postgresql/11/main/postgresql.conf
and adding 'timescaledb' to the list in the shared_preload_libraries config.
        # Modify postgresql.conf:
        shared_preload_libraries = 'timescaledb'

Another way to do this, if not preloading other libraries, is with the command:
        echo "shared_preload_libraries = 'timescaledb'" >> /etc/postgresql/11/main/postgresql.conf

(Will require a database restart.)

If you REALLY know what you are doing and would like to load the library without preloading, you can disable this check with:
        SET timescaledb.allow_install_without_preload = 'on';
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
connection to server was lost
Error during cluster dumping, removing new cluster

But the thing is /etc/postgresql/11/main/postgresql.conf does not exist until the cluster is upgraded.

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