Newer PostgreSQL versions introduce useful functionalities, so it is a great idea to keep our database engines up-to date. In addition if the version of the database engine stays very old, in the future it can be difficult to “catch up” and upgrade the data to the new version.
This is the official documentation of the pg_upgrade process: https://www.postgresql.org/docs/current/pgupgrade.html
To upgrade the PostgreSQL database engine version
Check the version of the PostgreSQL server
- In pgAdmin4 open a Query Tool window
- Execute:
SELECT version();
Create a backup of the old server
- Open pgAdmin4 and right-click the local database server
- Click the folder icon to select the backup file location
- Select the postgres role, and click the Backup button
- The confirmation appears in the lower right corner
- Wait until the process completes
Get the PostgreSQL data directory
To get the name of the current PostgreSQL data directory
On macOS
On macOS the location of the server installation is /Library/PostgreSQL/MY_SERVER_VERSION/
The binary (executable) files are in the “bin” directory, the data files are in the “data” directory
Remove deprecated libraries
Some old versions of the PostgreSQL server contain deprecated libraries. As those are not part of the new installation anymore, pg_upgrade will stop with an error message.
Upgrading from version 15
Version 15 of the PostgreSQL server contained the adminpack library which is not part of later versions. Before we shut down the old server we will need to remove the library:
- In pgAdmin4 right-click the postgres database and select Query Tool
- Execute the following command
DROP EXTENSION adminpack CASCADE;
Stop the old PostgreSQL server
- Find the the PostgreSQL command line utility
locate pg_ctl
- If the message appears “WARNING: The locate database (/var/db/locate.database) does not exist.”, execute the following command:
sudo launchctl load -w /System/Library/LaunchDaemons/com.apple.locate.plist
- There is no indication when the “locate” database build completes, so try to execute the “locate” command after a few minutes to see if the database is available.
- Switch to the root directory to avoid directory access issues, and execute the found
pg_ctl command to shut down the PostgreSQL server.
The -D option specifies the location of the data directory:
cd /
sudo -u postgres /Library/PostgreSQL/15/bin/pg_ctl -D /Library/PostgreSQL/15/data stop
- After a few seconds the server stops
Install the new version of the server
- Download the latest version of the PostgreSQL installer from https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
- Start the installer,
- Follow the prompts
- IMPORTANT, use the recommended, version specific installation directory names
- Create a password for the database superuser called “postgres”
- If the old version of the PostgresSQL server is running, a new port number will be recommended
- If no PostgreSQL instance is running, the standard 5432 port number is populated:
- Keep the Default locale setting
- The summary page shows the location of the components
- Click Next to start the installation
- If you don’t want to install other components, uncheck the Stack Builder launch.
Configure the environment
As the upgrade process will connect to the old and new instance multiple times, save the access credentials in the PostgreSQL password file in the user’s root directory at ~/.pgpass
Create the password file
Official documentation is at https://www.postgresql.org/docs/current/libpq-pgpass.html
- Open or create the password file from the command line with
code ~/.pgpass
- Specify the database admin credentials in the file:
# EXAMPLE: hostname:port:database:username:password
localhost:*:*:postgres:MY_ADMIN_PASSWORD
- Set the file access permissions to disallow access by world, otherwise the file will be ignored by the process:
chmod 0600 ~/.pgpass
If the error message during upgrade is
connection to server on socket “/private/tmp/.s.PGSQL.50432” failed: fe_sendauth: no password supplied
could not connect to source postmaster started with the command:
Enable connection to local servers without a password. Update the config files for the old and new version to trust the local connection.
- Edit the old server’s config file
vi /Library/PostgreSQL/15/data/pg_hba.conf
- In the line of the local connection change the method from “scram-sha-256” to “trust”
local all all trust
- Edit the new server’s config file
vi /Library/PostgreSQL/17/data/pg_hba.conf
- In the line of the local connection change the method from “scram-sha-256” to “trust”
local all all trust
Stop both PostgreSQL servers
Even though we have stopped the old server, the installation of the new version started the new.
- Stop the new server with
sudo -u postgres /Library/PostgreSQL/17/bin/pg_ctl -D /Library/PostgreSQL/17/data stop
Start the upgrade
pg_upgrade needs write access to the current directory, so let’s create a working directory for the process, othrewise we get the error message:
You must have read and write access in the current directory.
Also, as we will run the pg_upgrade comamnd as the user “postgres” we need to start the process in a directory it has access to, otherwise we get the error message:
could not determine current directory
To satisfy both requirements, we need to be in a directory where the postgres user can be and have write access. We will use the temp directory:
cd /tmp
- To upgrade the PosgreSQL server from version 15 to 17 execute the command as the postgres user:
sudo -su postgres
- When asked, enter your macOS password to allow the user switching:
- Switch to the /tmp directory and start the upgrade process
cd /tmp
/Library/PostgreSQL/17/bin/pg_upgrade --old-datadir /Library/PostgreSQL/15/data --new-datadir /Library/PostgreSQL/17/data --old-bindir /Library/PostgreSQL/15/bin --new-bindir /Library/PostgreSQL/17/bin
After successful upgrade
Start the new server
Open the terminal and execute the command below. Use the new server’s version number in the command
sudo -u postgres /Library/PostgreSQL/17/bin/pg_ctl -D /Library/PostgreSQL/17/data start
Check the version of the new PostgreSQL server
- In pgAdmin4 open a Query Tool window
- Execute:
SELECT version();
Rebuild the optimizer statistics
As the optimizer statistics were not transferred to the new server environment, after starting the new server execte the command from the terminal
sudo -su postgres /Library/PostgreSQL/17/bin/vacuumdb --all --analyze-in-stages
To delete the old cluster
Once you have validated the new cluster, you can delete the data files of the old cluster. In the /tmp directory, where we have executed the pg_upgrade comand, the process created a customized script. Execute from the command line
cd /tmp
./delete_old_cluster.sh
Troubleshooting
If the old version of the PostgreSQL server used extra libraries, we need to install those for the new version too:
Checking for presence of required libraries fatal
Your installation references loadable libraries that are missing from the
new installation. You can add these libraries to the new installation,
or remove the functions using them from the old installation. A list of
problem libraries is in the file:
/Library/PostgreSQL/17/data/pg_upgrade_output.d/20241002T161901.823/loadable_libraries.txt
Open the text file and install the referenced libraries. In my case the message was:
could not load library “$libdir/adminpack”: ERROR: could not access file “$libdir/adminpack”: No such file or directory
In database: postgres
If the missing library is the adminpack, see Remove deprecated libraries above.
To install the missing libraries
- Start the Application Stack Builder utility from the Launchpad
- Select the new PostgreSQL server version
- Select the missing library
