postgresql,

How to list, remove and upgrade PostgreSQL cluster

Aug 11, 2021 · 1 min read · Post a comment
How to list, remove and upgrade PostgreSQL cluster

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

Solution

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

pg_lsclusters

Sample 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 11 cluster:

pg_dropcluster 11 main --stop

Note: 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: You can list all package dependendies 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.