PostgreSQL 16 Update

• Table of Contents
• Overview
• GreenArrow High Availability Cluster
• Upgrades
  • Upgrade Preparation
  • Fast Upgrade Mode
  • Slow Upgrade Mode
• Rollback from an error during the upgrade
• Rollback from a successful upgrade
  • Fast Upgrade Mode Rollback
  • Slow Upgrade Mode Rollback

Overview

GreenArrow v4.304.0 adds support for PostgreSQL 16. New installations of GreenArrow made since that release use PostgreSQL 16 automatically. For older GreenArrow installations, upgrading to PostgreSQL 16 is optional but recommended because of performance enhancements.

The upgrade and downgrade procedures on this page target experienced Linux system administrators. If you would like us to upgrade PostgreSQL for you, please get in touch with GreenArrow technical support.

GreenArrow High Availability Cluster

This procedure should not be run on a GreenArrow High Availability Cluster. Please contact GreenArrow technical support if you wish to upgrade your High Availability Cluster to Postgres 16.

Upgrades

Upgrade Preparation

Before upgrading PostgreSQL, you must complete the following preparation work. The preparation work will not create any downtime unless it reveals an issue that would block the upgrade:

1. Ensure you’re running a GreenArrow release that’s new enough (v4.304.0 or later) to come with the PostgreSQL 9.5 to 16 upgrade tool:

   greenarrow update upgrade_postgres_95_to_16 --help
   

   If you’re running an earlier release, then you’ll need to update GreenArrow before proceeding with the remainder of this procedure.

2. Verify that GreenArrow is currently using PostgreSQL 9.5 by examining the PG_VERSION file. It should contain just the string 9.5:

   # cat /var/hvmail/postgres/default/data/PG_VERSION
   9.5
   

   If you’re running an older PostgreSQL release, then GreenArrow technical support will need to upgrade you to PostgreSQL 9.5 before upgrading to PostgreSQL 16.

3. Optional, but strongly recommended: Take a backup of GreenArrow, so that you can restore from that backup if you encounter PostgreSQL 16 issues. We have tested GreenArrow with PostgreSQL 16 and don’t anticipate any issues, but running into a problem that requires rolling back to PostgreSQL 9.5 is possible.

4. Select an upgrade mode:

   1. Fast (recommended) mode: The upgrade tool hard links PostgreSQL 9.5’s files to PostgreSQL 16. This mode typically takes a few minutes to complete and requires minimal additional disk space but has the downside of only being reversible by restoring from backups.
   2. Slow (alternative) mode: The upgrade tool copies PostgreSQL 9.5’s data to PostgreSQL 16. This allows you the option of quickly reverting to PostgreSQL 9.5 if something goes wrong with the upgrade but has the following downsides:
      1. Since there will be two copies of PostgreSQL’s data files, PostgreSQL disk usage will approximately double until you later delete 9.5’s data files. You can estimate the amount of additional disk space required by checking the current usage of the /var/hvmail/postgres/9.5/data/ directory.
      2. Any external tablespaces that are configured will also double their disk usage.
      3. This upgrade path takes significantly longer (typically 10-20 times as long as Fast Upgrade Mode) because of the time required to copy files.

   The difference in upgrade time is primarily due to how long it takes to run the greenarrow update upgrade_postgres_95_to_16 command. For example, on a system with 4 CPU cores, 8GB of RAM, SSD storage, and a 99GB /var/hvmail/postgres/9.5/data/ directory, the command ran in:

   • 25 seconds when run in Fast Upgrade Mode
   • 6 minutes, 49 seconds when run in Slow Upgrade Mode

Fast Upgrade Mode

If you selected Fast Upgrade Mode, then complete the following sequence:

1. Ensure that /var/hvmail/postgres/9.5/data/ and /var/hvmail/postgres/16/ are on the same filesystem.

   df -h /var/hvmail/postgres/9.5/data/ /var/hvmail/postgres/16/
   

   If they’re not on the same filesystem, then you’ll need to either:

   1. Move /var/hvmail/postgres/16 to the correct filesystem. For example:

      mv /var/hvmail/postgres/16 /media/disk1/postgres16
      ln -s /media/disk1/postgres16 /var/hvmail/postgres/16
      

   2. Use Slow Upgrade Mode instead.

2. Stop PostgreSQL:

   hvmail_postgres_manager stop
   

3. Upgrade PostgreSQL:

   greenarrow update upgrade_postgres_95_to_16 --upgrade
   

4. Start PostgreSQL:

   hvmail_postgres_manager start
   

Slow Upgrade Mode

If you selected Slow Upgrade Mode, then complete the following sequence:

1. Ensure that the filesystem that the /var/hvmail/postgres/16 directory is on has enough disk space to hold a copy of PostgreSQL 9.5’s data, with some margin to spare:

   du -hs /var/hvmail/postgres/9.5/data/
   df -h /var/hvmail/postgres/16/
   

   If the filesystem doesn’t have enough disk space, then you’ll need to either:

   1. Expand the filesystem.

   2. Move /var/hvmail/postgres/16 to a filesystem that does have enough space. For example:

      mv /var/hvmail/postgres/16 /media/disk1/postgres16
      ln -s /media/disk1/postgres16 /var/hvmail/postgres/16
      

   3. Use the Fast Upgrade Mode instead.

2. Check if there are any PostgreSQL 9.5 tablespaces with a custom location by running the following command and searching the output for any tablespaces that have a “Location” specified:

   psql -U greenarrow -c "\db"
   

   Here’s example output that contains a single custom tablespace location (/media/disk2/data2):

                List of tablespaces
       Name    |   Owner    |      Location
   ------------+------------+--------------------
    data2      | greenarrow | /media/disk2/data2
    pg_default | postgres   |
    pg_global  | postgres   |
   (3 rows)
   

   If there are any custom tablespace locations, ensure that the filesystem that each custom location is on has enough disk space to hold a copy of PostgreSQL 9.5’s data, with some margin to spare. For example:

   du -hs /media/disk2/data2/
   df -h /media/disk2/data2/
   

   If the filesystem doesn’t have enough disk space, then you’ll need to either:

   1. Expand the filesystem.

   2. Use the Fast Upgrade Mode instead.

3. Stop PostgreSQL:

   hvmail_postgres_manager stop
   

4. Upgrade PostgreSQL:

   greenarrow update upgrade_postgres_95_to_16 --upgrade --no-link
   

   This command is expected to take multiple minutes to complete in most cases. You can optionally monitor progress by monitoring the /var/hvmail/var/tmp/pg_upgrade_to_16.log file.

5. Start PostgreSQL:

   hvmail_postgres_manager start
   

6. Optional, but strongly recommended: Come back and delete the old PostgreSQL 9.5 data directory (/var/hvmail/postgres/9.5/data/) to free up disk space after you’ve verified that there are no issues with PostgreSQL 16. If you identified any custom tablespace locations in step 2 and aren’t confident about how to delete just the PostgreSQL 9.5 data directory from them, contact GreenArrow technical support for assistance.

Rollback from an error during the upgrade

This procedure can corrupt data if it is run after a successful upgrade.

If you encountered an error while running greenarrow update upgrade_postgres_95_to_16, and you wish to rollback to Postgres 9.5, you will need to run the following commands:

rm -f /var/hvmail/var/postgres16.migration-status
[ -f /var/hvmail/postgres/9.5/data/pg_hba.conf.migration ] && mv /var/hvmail/postgres/9.5/data/pg_hba.conf{.migration,}
[ -f /var/hvmail/postgres/9.5/data/global/pg_control.old ] && mv /var/hvmail/postgres/9.5/data/global/pg_control{.old,}
chown hvpostgres.hvpostgres /var/hvmail/postgres/9.5/data/pg_hba.conf /var/hvmail/postgres/9.5/data/global/pg_control
rm -f /var/hvmail/postgres/default
ln -s /var/hvmail/postgres/9.5 /var/hvmail/postgres/default
rm -rf /var/hvmail/postgres/16/data

You should now be able to start Postgres using the hvmail_postgres_manager start command and resume using GreenArrow with Postgres 9.5.

Rollback from a successful upgrade

If you encounter a mild issue with PostgreSQL 16, such as non-critical function breaking, we recommend contacting GreenArrow technical support to request a fix.

For a severe issue, such as one that renders GreenArrow inoperable, two methods are available to roll back GreenArrow to the state it was in before the upgrade. If you roll back, you will lose any data saved after upgrading to PostgreSQL 16 but before the rollback to 9.5.

Fast Upgrade Mode Rollback

If you used the Fast Upgrade Mode, and it completed successfully, to revert to Postgres 9.5 you will need to restore from a backup taken before the PostgreSQL 16 upgrade.

Slow Upgrade Mode Rollback

If you meet all of the following prerequisites, you can rapidly rollback to PostgreSQL 9.5:

1. You used the Slow Upgrade Mode which completed successfully.

2. You have not upgraded GreenArrow again since the upgrade to PostgreSQL 16.

3. You have not yet deleted the old PostgreSQL 9.5 data directory (/var/hvmail/postgres/9.5/data/).

If you do not meet the above prerequisites, restore from a backup taken before the PostgreSQL 16 upgrade.

If you do meet the above prerequisites, then:

1. Stop PostgreSQL:

   hvmail_postgres_manager stop
   

2. Switch back to PostgreSQL 9.5:

   rm -f /var/hvmail/postgres/default
   ln -s /var/hvmail/postgres/9.5 /var/hvmail/postgres/default
   

3. Start PostgreSQL:

   hvmail_postgres_manager start
   

4. If you’re confident that you aren’t going to switch back to PostgreSQL 16 without running through the upgrade process again, then delete the PostgreSQL 16 data directory (/var/hvmail/postgres/16/data/) to free up disk space.

5. Let us know about the issue you encountered, so we can try to fix it.