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.