postgresql,

How to list, remove and upgrade PostgreSQL cluster

Aug 11, 2021 · 1 min read · Post a comment

As part of the PostgreSQL series, in today’s tutorial, we are going to see on how can we list, remove and upgrade clusters, the easy way.

Prerequisites

  • PostgreSQL
  • sudo privileges

Solution

Step 1. First things first, let’s see how many clusters we got installed on our machine.

pg_lsclusters

Output:

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

As we can see, we got two clusters, 10 and 11.

Step 2. Let’s say we don’t need cluster 11. Stop and remove PostgreSQL cluster 11.

pg_dropcluster 11 main --stop

Note(s): If you want only to stop the cluster, use the command: pg_ctlcluster stop 11 main. pg_ctlcluster command supports start/restart/reload operations as well.

Step 4. Now, if we want to do an upgrade, always do a backup first.

export BCKP_DATE="$(date +%Y_%m_%d-%H:%M)"
pg_dumpall -f <backup_dir>/postgres_10_${BCKP_DATE}.sql

Step 5. Upgrade cluster 10 to 11.

pg_upgradecluster 10 main

Step 6. Cluster 10 should be down, so verify it.

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

Step 7. Now, we have a fresh new PostgreSQL cluster 11. Remove any PostgreSQL 10 packages and package dependencies.

sudo apt-get purge postgresql-10 postgresql-client-10

Note(s): You can list all package dependencies using the command: dpkg --list | grep postgresql-10.

Conclusion

Always do backups before doing major PostgreSQL upgrades. Feel free to leave a comment below and if you find this tutorial useful, follow our official channel on Telegram.