Upgrade PostgreSQL 14 to 16

Overview

Upgrade Current Database

I plan to use pg_upgrade to upgrade my current database.

Pre-Upgrade Checks

The following upgrade steps were skipped:

Optionally move the old cluster is not needed as the database is stored in the standard locations.
For source installs, build the new version is not needed as the binaries were installed.
Install the new PostgreSQL binaries was done above.
Initialize the new PostgreSQL cluster was done as part of the APT command.
Copy custom full-text search files is not needed as I have none.

Examine Extensions

The advice is to Install extension shared object files. I used the following command to discover what extensions have been installed:

postgres=# \dx
                        List of installed extensions
   Name    | Version |   Schema   |               Description               
-----------+---------+------------+-----------------------------------------
 adminpack | 2.1     | pg_catalog | administrative functions for PostgreSQL
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

Adjust authentication

I located the pga_hba.conf file as follows through psql:

postgres=# \dconfig hba_file
  List of configuration parameters
Parameter |                Value                
-----------+-------------------------------------
hba_file  | /etc/postgresql/14/main/pg_hba.conf
(1 row)

The rules appear to be correct:

postgres@coogee:~$ grep -vE "(^#|^$)" /etc/postgresql/14/main/pg_hba.conf
  local   all             postgres                                peer
  local   all             all                                     peer
  host    all             all             127.0.0.1/32            scram-sha-256
  host    all             all             ::1/128                 scram-sha-256
  local   replication     all                                     peer
  host    replication     all             127.0.0.1/32            scram-sha-256
  host    replication     all             ::1/128                 scram-sha-256

Get Config Settings

The current data directory is found as follows through psql:

postgres=# \dconfig data_directory
        List of configuration parameters
   Parameter    |            Value            
----------------+-----------------------------
 data_directory | /var/lib/postgresql/14/main
(1 row)

I also recorded the location of the configuration file (through psql):

postgres=# \dconfig config_file
           List of configuration parameters
  Parameter  |                  Value                  
-------------+-----------------------------------------
 config_file | /etc/postgresql/14/main/postgresql.conf
(1 row)

Stop both servers

I attempt to stop the current server as follows:

/usr/lib/postgresql/14/bin/pg_ctl -D /var/lib/postgresql/14/main stop

The output is:

waiting for server to shut down.... done
server stopped

The new server is shut down as follows:

/usr/lib/postgresql/16/bin/pg_ctl -D /var/lib/postgresql/16/main stop

The result is:

waiting for server to shut down.... done
server stopped

Configure Upgrade

I chose to configure the upgrade using environment variables as follows (using the values retrieved above):

# Configure upgrade using environment variables
export PGBINOLD=/usr/lib/postgresql/14/bin
export PGBINNEW=/usr/lib/postgresql/16/bin
export PGDATAOLD=/etc/postgresql/14/main
export PGDATANEW=/etc/postgresql/16/main
export PGPORTOLD=5433
export PGPORTNEW=5434
export PGUSER=postgres
# Ensure new binaries are used
export PATH=${PGBINNEW}:${PATH}
command -v pg_upgrade

The output is:

/usr/lib/postgresql/16/bin/pg_upgrade

Do Upgrade

I ran the following command as the postgres user:

pg_upgrade --jobs 6

The output is captured in upgrade.log.

The following warning was issued:

WARNING:  user-defined tablespace locations should not be inside the data directory, i.e. /var/lib/postgresql/14/main

And the following post-installation steps were recommended:

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
    /usr/lib/postgresql/16/bin/vacuumdb --all --analyze-in-stages
Could not create a script to delete the old cluster's data files
because user-defined tablespaces or the new cluster's data directory
exist in the old cluster directory.  The old cluster's contents must
be deleted manually.

References

pg_upgrade