How to upgrade PostgreSQL 12 to version 14

Docs
1

Errors or typos? Topics missing? Hard to read? Let us know.

Create a data backup

Optional but strongly advised: backup all existing data.

sudo -u postgres pg_dumpall > backup.sql

Perform the upgrade

  1. Update and install packages

    sudo apt-get update
sudo apt-get install postgresql-14 postgresql-server-dev-14

  2. Halt the old server

    sudo systemctl stop postgresql.service

  3. Migrate 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'

  4. Reconfigure server ports

    sudo vim /etc/postgresql/14/main/postgresql.conf  # Set port to 5432
sudo vim /etc/postgresql/12/main/postgresql.conf  # Set port to 5433

  5. Activate new server and verify version

    sudo systemctl start postgresql.service
sudo -u postgres psql -c "SELECT version();"

  6. Execute new cluster script

    sudo -u postgres ./analyze_new_cluster.sh

Reset user passwords

PostgreSQL v14 defaults to scram-sha-256 for password hashing. Redefine all existing passwords.

sudo -u postgres psql
postgres=# \password $USER

Or, modify /etc/postgresql/14/main/pg_hba.conf to switch back to md5. But be cautious, as future versions may not support MD5.

Remove old version

  1. Remove old packages

    sudo apt-get remove postgresql-12 postgresql-server-dev-12

  2. Delete configuration and data

    sudo rm -rf /etc/postgresql/12/
sudo -u postgres ./delete_old_cluster.sh
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