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

upgrade-postgres-9.3-to-9.5.md

TL;DR

Install Postgres 9.5, and then:

sudo pg_dropcluster 9.5 main --stop
sudo pg_upgradecluster 9.3 main
sudo pg_dropcluster 9.3 main

---

Specifically for using the WAL-E enabled Docker image, make sure WAL-E is turned off:

sudo pkill backupcron

Install postgresql 9.5:

wget https://alioth.debian.org/scm/loggerhead/pkg-postgresql/postgresql-common/trunk/download/head:/apt.postgresql.org.s-20130224224205-px3qyst90b3xp8zj-1/apt.postgresql.org.sh
chmod a+x apt.postgresql.org.sh
sed -i -e 's/$CODENAME-pgdg main/$CODENAME-pgdg main 9.5/g' apt.postgresql.org.sh
sudo ./apt.postgresql.org.sh

sudo apt-get install postgresql-9.5
sudo apt-get install postgresql-client-9.5
sudo apt-get install postgresql-contrib-9.5

Once the installation finished, I used aptitude search to check which versions of postgres I have installed.

i   postgresql                                                        - object-relational SQL database (supported version)
i A postgresql-9.3                                                    - object-relational SQL database, version 9.3 server
i A postgresql-9.5                                                    - object-relational SQL database, version 9.5 server
i A postgresql-client-9.3                                             - front-end programs for PostgreSQL 9.3
i A postgresql-client-9.5                                             - front-end programs for PostgreSQL 9.5
i A postgresql-contrib-9.3                                            - additional facilities for PostgreSQL
i A postgresql-contrib-9.5                                            - additional facilities for PostgreSQL

Looks like we successfully installed PostgreSQL 9.5, but I still need to upgrade from 9.3 to 9.5.

Run pg_lsclusters, your 9.3 and 9.5 main clusters should be "online".

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

There already is a cluster "main" for 9.5 (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.3/main when 9.5/main also exists. The recommended procedure is to remove the 9.5 cluster with pg_dropcluster and then upgrade with pg_upgradecluster.

Stop the 9.5 cluster and drop it.

sudo pg_dropcluster 9.5 main --stop

Upgrade the 9.3 cluster to the latest version.

sudo pg_upgradecluster 9.3 main

Your 9.3 cluster should now be "down".

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

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

sudo pg_dropcluster 9.3 main

Now push an initial WAL-E base backup:

sudo -u postgres envdir /etc/wal-e.d/env wal-e backup-push

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.