PostgreSQL 16 Update

Table of Contents
Overview
GreenArrow High Availability Cluster
Upgrading Postgres 9.5 to 16
Upgrading Postgres 8.3 to 16
- Upgrade Procedure
- Rollback Procedure

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.

Upgrading Postgres 9.5 to 16

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:

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.
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.
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.
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:

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.
Stop PostgreSQL:
```
hvmail_postgres_manager stop
```

Upgrade PostgreSQL:

greenarrow update upgrade_postgres_95_to_16 --upgrade

Start PostgreSQL:
```
hvmail_postgres_manager start
```

Slow Upgrade Mode

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

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.
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.
Stop PostgreSQL:
```
hvmail_postgres_manager stop
```
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.
Start PostgreSQL:
```
hvmail_postgres_manager start
```
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:

You used the Slow Upgrade Mode which completed successfully.
You have not upgraded GreenArrow again since the upgrade to PostgreSQL 16.
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:

Stop PostgreSQL:
```
hvmail_postgres_manager stop
```

Switch back to PostgreSQL 9.5:

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

Start PostgreSQL:
```
hvmail_postgres_manager start
```
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.
Let us know about the issue you encountered, so we can try to fix it.

Upgrading Postgres 8.3 to 16

Upgrade Procedure

This procedure will require downtime. The amount of downtime depends on the size of your Postgres 8.3 database and the speed of your disks. If you have less than 50GB of data in Postgres 8.3, this procedure might take an hour or less. More than that and it’s likely to take several hours.

We recommend you run this procedure in a “screen” or “tmux” session in order to reduce the risk of network interruptions resulting in a procedure failure.

If at any point you run into an unusual situation that isn’t accounted for in this document, or if you run into any trouble, please contact GreenArrow technical support.

Verify that GreenArrow is currently using PostgreSQL 8.3 by examining the PG_VERSION file. It should contain just the string 8.3:
```
# cat /var/hvmail/postgres/default/data/PG_VERSION
8.3
```
Determine that you have enough disk space for this procedure and where you will put your temporary Postgres 8.3 backup:

Disk space is a major consideration for upgrading Postgres using this procedure because it requires you to store 3x copies of your Postgres database:
1. Original PostgreSQL 8.3 data - retained for roll-back
2. Backup - the database dump
3. New PostgreSQL 16 data
To calculate your maximum disk space requirements for the upgrade, run this command first to find out the size of your existing 8.3 database:
```
du -sh /var/hvmail/postgres/8.3/data
df -h /var/hvmail/postgres/8.3/data
```
If you do not have three times the disk space of your PostgreSQL 8.3 database, you will need to either add more disk space or use a different method that is outside the scope of this procedure. Please contact GreenArrow technical support if you need advice about how to proceed due to insufficient disk space.

Disk read/write performance can impact the overall time it takes to run this upgrade procedure. If the server has a separate disk that has at least 1x the free space of the original 8.3 database, then we recommend saving the Backup (the database dump) to that other disk.

The procedure below assumes you’ll use the path /var/backup for your temporary Postgres 8.3 backup. If you need to use a different path, adjust the procedure accordingly.
Upgrade to the latest version of GreenArrow.

Check for any non-default tablespaces:

/var/hvmail/postgres/8.3/bin/psql -U postgres greenarrow <<'EOF'
    SELECT * FROM pg_tablespace;
EOF

Here’s what the default looks like:

spcname    | spcowner | spclocation | spcacl
-----------+----------+-------------+--------
pg_default |       10 |             |
pg_global  |       10 |             |
(2 rows)

If any non-default tablespaces exist, GreenArrow technical support for consultation on how to proceed.

Set your locale to en_US.UTF-8:
```
export LANG=en_US.UTF-8
```
Verify whether PostgreSQL 16 data should be symlinked to an alternate filesystem (if /var/hvmail/postgres/8.3/ is on another filesystem, that is a good indication that 16 should be, too).
```
ls -ld /var/hvmail/postgres/8.3/data
```
If the above file is a symbolic link, create a symbolic link to a distinct path on the same filesystem, for example:
```
ln -s /TARGET_PATH/postgres-16 /var/hvmail/postgres/16/data
```

Initialize the new Postgres 16 database:

mkdir -p /var/hvmail/postgres/16/data
chown hvpostgres:hvpostgres /var/hvmail/postgres/16/data
setuidgid hvpostgres /var/hvmail/postgres/16/bin/initdb -E UTF8 -D /var/hvmail/postgres/16/data --auth password --username postgres --pwfile /var/hvmail/control/postgres.admin_pass

Stop GreenArrow:

hvmail_init stop
hvmail_postgres_manager stop
svc -d /service/*

Bring Postgres 8.3 back online:
```
svc -u /service/hvmail-postgres
```
Remove an hstore operator that is present on some (but not all) PostgreSQL 8.3 installations, but is incompatible with 16:
```
/var/hvmail/postgres/8.3/bin/psql -U postgres greenarrow <<'EOF'
    DROP OPERATOR IF EXISTS => (text, text)
EOF
```

Take a backup of the Postgres 8.3 database:

mkdir -p /var/backup
chmod 700 /var/backup
/var/hvmail/postgres/8.3/bin/pg_dumpall -U postgres | \
    pigz -c > /var/backup/greenarrow-database.sql.gz

Stop Postgres 8.3:
```
svc -d /service/hvmail-postgres
```

Set the default PostgreSQL version to 16:

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

Mark PostgreSQL as initialized:
```
touch /var/hvmail/postgres/16/data/ok
```
Bring Postgres 16 online and confirm it stays up for at least 10 seconds:
```
svc -u /service/hvmail-postgres
sleep 10
svstat /service/hvmail-postgres
```
If Postgres 16 is failing to start or restarting, check the logs to examine what is happening:
```
tail -F /var/hvmail/log/postgres/current | tai64nlocal
```

Restore your Postgres 8.3 data into your new Postgres 16 database:

unpigz -c /var/backup/greenarrow-database.sql.gz | \
    /var/hvmail/postgres/16/bin/psql -f - postgres postgres 2>&1 | \
    tee /tmp/restore-to-16

Check the restoral for any errors:

grep ERROR /tmp/restore-to-16

Some errors are expected. You can ignore any of the following:

could not find function "pg_freespacemap_pages" in file "/var/hvmail/postgres/16/lib/pg_freespacemap.so"
could not find function "pg_freespacemap_relations" in file "/var/hvmail/postgres/16/lib/pg_freespacemap.so"
extension "plpgsql" already exists
function pg_freespacemap_pages() does not exist
function pg_freespacemap_relations() does not exist
function public.pg_freespacemap_pages() does not exist
function public.pg_freespacemap_relations() does not exist
relation "pg_freespacemap_pages" does not exist
relation "pg_freespacemap_relations" does not exist
relation "public.pg_freespacemap_pages" does not exist
relation "public.pg_freespacemap_relations" does not exist
role "postgres" already exists

Remove old Postgres 8.3 specific functions, and install replacement Postgres 16 extensions:

/var/hvmail/postgres/16/bin/psql -U postgres greenarrow <<'EOF'
    DROP FUNCTION pgstattuple(IN relname text,
    OUT table_len BIGINT,               -- physical table length in bytes
    OUT tuple_count BIGINT,             -- number of live tuples
    OUT tuple_len BIGINT,               -- total tuples length in bytes
    OUT tuple_percent FLOAT,            -- live tuples in %
    OUT dead_tuple_count BIGINT,        -- number of dead tuples
    OUT dead_tuple_len BIGINT,          -- total dead tuples length in bytes
    OUT dead_tuple_percent FLOAT,       -- dead tuples in %
    OUT free_space BIGINT,              -- free space in bytes
    OUT free_percent FLOAT);

    DROP FUNCTION pgstattuple(IN reloid oid,
    OUT table_len BIGINT,               -- physical table length in bytes
    OUT tuple_count BIGINT,             -- number of live tuples
    OUT tuple_len BIGINT,               -- total tuples length in bytes
    OUT tuple_percent FLOAT,            -- live tuples in %
    OUT dead_tuple_count BIGINT,        -- number of dead tuples
    OUT dead_tuple_len BIGINT,          -- total dead tuples length in bytes
    OUT dead_tuple_percent FLOAT,       -- dead tuples in %
    OUT free_space BIGINT,              -- free space in bytes
    OUT free_percent FLOAT);

    DROP FUNCTION pgstatindex(IN relname text,
    OUT version int4,
    OUT tree_level int4,
    OUT index_size int4,
    OUT root_block_no int4,
    OUT internal_pages int4,
    OUT leaf_pages int4,
    OUT empty_pages int4,
    OUT deleted_pages int4,
    OUT avg_leaf_density float8,
    OUT leaf_fragmentation float8);

    DROP FUNCTION pg_relpages(text);

    DROP EXTENSION pgstattuple;
    DROP EXTENSION pg_freespacemap;

    CREATE EXTENSION pgstattuple;
    CREATE EXTENSION pg_freespacemap;
EOF

Grant the greenarrow user access to pg_largeobject:

/var/hvmail/postgres/16/bin/psql -U postgres greenarrow <<'EOF'
    GRANT ALL ON pg_largeobject TO greenarrow;
    UPDATE pg_largeobject_metadata
        SET lomowner = (
            SELECT usesysid
            FROM pg_user
            WHERE usename = 'greenarrow'
            LIMIT 1
        )
        WHERE oid IN ( SELECT data FROM s_images );
EOF

Copy your pg_hba.conf customizations from Postgres 8.3 to 16:

cat /var/hvmail/postgres/8.3/data/pg_hba.conf > \
    /var/hvmail/postgres/16/data/pg_hba.conf

Bring GreenArrow back online:

hvmail_postgres_manager start
hvmail_init start
svc -u /service/*

Run greenarrow update and observe its successful completion:
```
greenarrow update
```

Rollback Procedure

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

You used the above “Upgrading Postgres 8.3 to 16” procedure (which may or may not have completed successfully).
You have not upgraded GreenArrow again since the upgrade to PostgreSQL 16.
You have not yet deleted the old PostgreSQL 8.3 data directory (/var/hvmail/postgres/8.3/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:

Stop PostgreSQL:
```
hvmail_postgres_manager stop
```

Switch back to PostgreSQL 8.3:

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

Start PostgreSQL:
```
hvmail_postgres_manager start
```
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.
Let us know about the issue you encountered, so we can try to fix it.