This is an internal documentation. There is a good chance you’re looking for something else. See Disclaimer.

Manage Postgres versions

The databases downloaded with tco expect a compatible version of the local Postgres stack; if the stack is incompatible, tco will complain about the pg_restore version installed on the system. pg_restore should follow the same version as psql.

Install PPA

Important

Do not blindly run the first command apt install postgresql shown on the pages linked below, as that will install the default postgres version from the standard repository.

Follow the instructions under PostgreSQL Apt Repository at the right link for you:

Upgrade Postgres (including cluster)

As of May 2026, Ubuntu 24.04 comes with Postgres 16 (official repository), which is incompatible with the databases downloaded with tco. This section walks through upgrading Postgres locally to version 18 via the PPA.

  1. Install a higher PostgreSQL version (e.g., 18).

sudo apt install postgresql-18

  1. Upgrade the cluster directly.

sudo pg_upgradecluster 16 main

The command above handles everything: it creates a new PostgreSQL 18 cluster, migrates all your data from the 16 cluster, and reassigns the port (the old cluster moves to a higher port, the new one takes 5432). This means there will be no data loss and also no need to use pg_createcluster manually.

The command should also restart and re-enable the postgres systemd service (it is possible to check it with systemctl status postgresql).

  1. Verify the upgrade

pg_lsclusters

Output example:

Ver Cluster Port Status                Owner    Data directory
16  main    5433 down                  postgres /var/lib/postgresql/16/main
18  main    5432 online                postgres /var/lib/postgresql/18/main

  1. Once satisfied, remove the old cluster and the previous packages.

sudo pg_dropcluster 16 main
sudo apt remove postgresql-16 postgresql-client-16

Downgrade Postgres

This section shows how to perform a downgrade. The example present in the code snippets depict a downgrade from Postgres 18 (PPA) to 17 (PPA). However, the same concepts and process apply to any other versions. Additionally, this section can be helpful to troubleshoot misconfigured setups.

Important

Postgres does not support downgrades, therefore the Postgres cluster will have to be deleted, together with all its databases. A new cluster will have to be created.

  1. Install a lower PostgreSQL version (e.g., 17).

sudo apt install postgresql-17

  1. Display all versions.

dpkg -l | grep postgresql

Output example:

rc  postgresql-16                                 16.13-0ubuntu0.24.04.1                   amd64        The World's Most Advanced Open Source Relational Database
ii  postgresql-17                                 17.9-1.pgdg24.04+1                       amd64        The World's Most Advanced Open Source Relational Database
ii  postgresql-18                                 18.3-1.pgdg24.04+1                       amd64        The World's Most Advanced Open Source Relational Database
ii  postgresql-18-jit                             18.3-1.pgdg24.04+1                       amd64        LLVM JIT support for PostgreSQL 18
ii  postgresql-client-17                          17.9-1.pgdg24.04+1                       amd64        front-end programs for PostgreSQL 17
ii  postgresql-client-18                          18.3-1.pgdg24.04+1                       amd64        front-end programs for PostgreSQL 18
ii  postgresql-client-common                      290.pgdg24.04+1                          all          manager for multiple PostgreSQL client versions
ii  postgresql-common                             290.pgdg24.04+1                          all          PostgreSQL database-cluster manager

  1. Remove the higher Postgres version package (e.g., 18).

sudo apt remove postgresql-18 postgresql-client-18

  1. pg_restore version check.

Even after the new postgres version is installed and the old one is deleted, pg_restore --version will still show the old version:

pg_restore --version

Expected output of pg_restore still bound to version 18:

Error: PostgreSQL version 18 is not installed

  1. Enable and start the Postgres v17 service.

sudo systemctl restart postgresql
sudo systemctl enable postgresql

  1. Even after restarting the postgres service (version 17), the right version of pg_restore might still not be used.

pg_restore --version

Output example still bound to version 18:

Error: PostgreSQL version 18 is not installed

  1. The installed psql version should be right, though.

psql --version

Output:

psql (PostgreSQL) 17.9 (Ubuntu 17.9-1.pgdg24.04+1)

  1. Create a cluster that works with version 17 (this part also includes troubleshooting substeps).

sudo pg_createcluster 17 main

The status will be “down”. Now, let us start it:

sudo pg_ctlcluster 17 main start

Let’s inspect the clusters.

pg_lsclusters

As it can be noticed in the example output below, the newly started cluster uses port 5433. The old cluster version is still present:

Ver Cluster Port Status                Owner    Data directory              Log file
17  main    5433 online                postgres /var/lib/postgresql/17/main /var/log/postgresql/postgresql-17-main.log
18  main    5432 down,binaries_missing postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log

You can drop the cluster (which will delete its databases):

sudo pg_dropcluster 18 main

At this point, the right version of pg_restore should already be in use.

pg_restore --version

The version is now indeed the right one:

pg_restore (PostgreSQL) 17.9 (Ubuntu 17.9-1.pgdg24.04+1)

Let’s now first restart the cluster and then display the information for all clusters.

sudo pg_ctlcluster 18 main restart
pg_lsclusters

The new postgres cluster still uses port 5433, as shown in the output:

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

This is not ideal, as our setup expects the port to be 5432. It is possible to manually set the correct port with sudoedit /etc/postgresql/17/main/postgresql.conf, where you are supposed to change the port value from 5433 to 5432. After saving, you can restart the cluster with sudo pg_ctlcluster 17 main restart, and enter the following command to show the clusters:

pg_lsclusters

Now the output will correctly show port 5432 being used:

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

  1. Create databases, users, roles

Since a new cluster was created, you will need to repeat steps 2. Setup Postgres for use with Ansible and 3. Setup Postgres for local development from Setup tco.