Upgrading PostgreSQL 12 to version 14

Docs
#1

If you need to upgrade Postgresql from v12 to v14, you can follow the procedure given below.

You should turn off MAAS before performing this upgrade. It doesn’t matter whether it’s installed or not, but it shouldn’t be running during this procedure.

Back up your existing data

An optional but highly recommended first step is to back up your existing data, like this:

sudo -u postgres pg_dumpall > backup.sql

Upgrade the service

Next, you’ll want to upgrade the PostgreSQL service:

  1. Install new packages
sudo apt-get update
sudo apt-get install postgresql-14 postgresql-server-dev-14
  1. Stop the existing server
sudo systemctl stop postgresql.service
  1. Convert the configuration files
sudo -u postgres /usr/lib/postgresql/14/bin/pg_upgrade \
--old-datadir=/var/lib/postgresql/12/main \
--new-datadir=/var/lib/postgresql/14/main \
--old-bindir=/usr/lib/postgresql/12/bin \
--new-bindir=/usr/lib/postgresql/14/bin \
--old-options '-c config_file=/etc/postgresql/12/main/postgresql.conf' \
--new-options '-c config_file=/etc/postgresql/14/main/postgresql.conf'
  1. Move the new server to the default Postgresql port
# change 'port' to 5432
sudo vim /etc/postgresql/14/main/postgresql.conf

# change 'port' to 5433
sudo vim /etc/postgresql/12/main/postgresql.conf
  1. Start the new server and check the version
sudo systemctl start postgresql.service
sudo -u postgres psql -c "SELECT version();"
  1. Run the generated new cluster script
sudo -u postgres ./analyze_new_cluster.sh

Reset your passwords

Postgresql v14 uses scram-sha-256 as default password hash function. You must redefine all existing passwords

$ sudo -u postgres psql
postgres=# \password $USER
Enter new password for user "$USER": 
Enter it again:

Alternatively, you can edit /etc/postgresql/14/main/pg_hba.conf and revert this change, setting the hash function to md5 and restarting the server. Be aware that future versions of Postgresql might drop the MD5 support, so this is a temporary solution that carries some risk.

Clean up the old version

Finally, you should clean up the old version:

#uninstalls postgres packages
sudo apt-get remove postgresql-12 postgresql-server-dev-12

#removes the old postgresql directory
sudo rm -rf /etc/postgresql/12/

#delete the old cluster data
sudo -u postgres ./delete_old_cluster.sh
MAAS documentation
How to upgrade MAAS
How to do a fresh install of MAAS
#2

This procedure seems to be written without knowing about pg_upgradecluster

#3

I known about pg_upgradecluster, but as it assumes that the user runned pg_createcluster to create the DB (and we do not tell the user to run it in our docs), I wrote something more generic.

#4

Hi @billwear

What should be the procedure when upgrading a HA PostgreSQL (3 nodes) cluster?

#5

Another question I have is, what should I upgrade first
A. Ubuntu OS (focal -> jammy)
B. PostgreSQL (12 -> 14)

#6

hey, @gustavosr98,

Here’s my take at a procedure for Upgrading a HA MAAS Cluster (3 Nodes). Understand that this is provisional, so try it first, and let me know if it’s broken in some way:

  1. Backup Configuration: Before starting the upgrade process, it’s always a good practice to back up the configuration of your MAAS cluster. This ensures you can restore your settings in case something goes wrong.
  2. Review Release Notes: Check the release notes of the new MAAS version you’re planning to upgrade to. This will provide you with important information about any changes, requirements, or known issues.
  3. Upgrade Order:
  • In a HA cluster, it’s generally advisable to upgrade nodes one at a time rather than all at once. This minimizes the risk of downtime and potential issues.
  • Start with one node and complete the upgrade process on that node before moving on to the next.
  1. Stopping MAAS Services: You don’t necessarily need to stop MAAS services across the entire cluster during the upgrade. However, it’s a good practice to temporarily stop the services on the node you are upgrading to avoid potential conflicts. You can use commands like maas-region-controller stop and maas-rack-controller stop on the node you are upgrading.
  2. Upgrading a Node:
  • On the node you are upgrading, follow the installation instructions for the new MAAS version. This might involve updating packages, repositories, or using specific commands provided by MAAS.
  • Ensure that the upgrade process completes successfully on the first node before proceeding to the next.
  1. Node Synchronization:
  • During the upgrades, you can expect some temporary differences in version and configuration between the nodes.
  • However, these differences should not affect the overall operation of the cluster significantly if you are upgrading one node at a time.
  • After upgrading each node, ensure that it rejoins the cluster and synchronizes its configuration with the other nodes.
  1. Checking Quorum:
  • To ensure that your cluster maintains quorum, you can use tools or commands specific to your HA setup. This might involve tools like corosync-cfgtool, crm_mon, or other HA monitoring tools.
  • Check the status of the cluster to ensure that all nodes are back in quorum and operating correctly.
  1. Test Functionality: After all nodes have been upgraded and are back in quorum, thoroughly test the functionality of your HA MAAS cluster. Verify that it can provision and manage nodes as expected.
  2. Rollback Plan: Be prepared with a rollback plan in case the upgrade encounters critical issues. This plan should include steps to revert to the previous MAAS version and configuration.
  3. Documentation: Keep detailed records of the upgrade process, including any issues encountered and how they were resolved. This documentation can be valuable for future reference.
1 Like