PostgreSQL 15 shipped in late 2022 (See PostgreSQL 15 Release Notes), including interesting new features like SQL
MERGE. I wanted to give them a try.
- Errors with extensions
- Having a cluster install user
- Errors with extensions
- Locale provider mismatch
- Encodings mismatch
initdbcommand with flags
- Closing Thoughts
On my Mac, I was running PostgreSQL 14.3 and had some significant databases I wanted to preserve but have them running on 15.
To perform the upgrade, I used
pg_upgrade which ships with PostgreSQL. To initialize, install, and manage the cluster on Mac OS, I use Postgres.app.
Postgres.app installs PostgreSQL in versioned directories. I had separate directories for versions 14 and 15.
Postgres.app has an “Initialize” button that creates a new cluster. I imagine this runs
initdb behind the scenes.
During my upgrade, I ended up running
initdb manually so that I could directly set specific flags. See:
To get started, check which version of
pg_upgrade is active.
$ which pg_upgrade /Applications/Postgres.app/Contents/Versions/14/bin/pg_upgrade
If the old version is active, make sure to use
pg_upgrade from the new PostgreSQL 15 version.
- Download and install Postgres.app. This is a regular Mac OS app with a
- Choose “Replace” during installation. You are replacing the Mac OS app, not modifying the current PostgreSQL cluster.
- Open Postgres.app. Choose the “+” icon. Version 15 is now available. Click the plus button to create a new cluster. In PostgreSQL the collection of databases is a “cluster.”
To make the versions easier to keep track of, I renamed both clusters,
my_db_15. Both ran on the same port so I ran one at a time.
Stop any running copies of 14. I use the
-D and a path to the data directory.
pg_ctl stop \ -D "/Users/andy/Library/Application\ Support/Postgres/var-14/"
While running “Initialize” from Postgres.app is possible, I had inconsistencies flagged by
pg_upgrade between the cluster versions.
In the end, I did not initialize the cluster from the app, but ran
initdb with various flags.
The data directory for version 15 is below.
Stop both versions before performing the upgrade. Stop the version 15 cluster if it’s running.
pg_ctl stop \ -D /Users/andy/Library/Application\ Support/Postgres/var-15
pg_upgrade with the
--check option to perform a dry run. Provide the data directory and the binaries directory for both clusters.
This means there are 4 arguments to
The full command with the version 15
pg_upgrade and all 5 arguments is below.
/Applications/Postgres.app/Contents/Versions/15/bin/pg_upgrade \ --check \ --old-datadir "/Users/andy/Library/Application Support/Postgres/var-14" \ --old-bindir "/Applications/Postgres.app/Contents/Versions/14/bin" \ --new-datadir "/Users/andy/Library/Application Support/Postgres/var-15" \ --new-bindir "/Applications/Postgres.app/Contents/Versions/15/bin"
One issue I ran into was with checksums.
The error was
"the old cluster does not use data checksums but the new one does".
What I did was to disable checksums for the new 15 cluster. I don’t fully understand the implications of this, but since this is for my local installation, I’m not worried about it for now.
To disable checksums I ran the following command.
/Applications/Postgres.app/Contents/Versions/15/bin/pg_checksums \ --disable \ -D "/Users/andy/Library/Application Support/Postgres/var-15"
Errors with extensions
The next error I had was with
pg_cron, which is an extension I’d compiled for PostgreSQL 14.
FATAL: could not access file "pg_cron": No such file or directory
I’d set up citusdata/pg_cron in 14, so I’ll need to set that up in 15 as well.
I confirmed it wasn’t listed in available extensions, from a
psql prompt in 15. I disabled it initially to make the upgrade easier, but was able to recompile it for 15.
The other items in
shared_preload_libraries for me are items that ship with PostgreSQL (
SELECT * FROM pg_extension;
I needed to modify my path to make sure the version 15 directory was active.
export PATH=$(which pg_config):$PATH
I followed the normal build from source instructions for citusdata/pg_cron.
Once that completed, I added it to the new
postgresql.conf config file.
vim "/Users/andy/Library/Application\ Support/Postgres/var-15/postgresql.conf" # edit shared_preload_libraries shared_preload_libraries = 'pg_cron' # (requires restart)
Now I can start PostgreSQL 15 again.
/Applications/Postgres.app/Contents/Versions/15/bin/pg_ctl start \ -D "/Users/andy/Library/Application Support/Postgres/var-15"
Having a cluster install user
I needed to create a
postgres superuser for the upgrade.
"FATAL: role "postgres" does not exist".
I solved this by adding
--username postgres to the
Locale provider mismatch
The next error was a mismatch in the locale providers between the versions.
"locale providers for database "template1" do not match: old 'libc', new 'icu'".
I solved this by adding
initdb, and running it directly on the new data directory.
The next issue was a mismatch in the encodings between the clusters.
"encodings for database "template1" do not match: old 'UTF8', new 'SQL_ASCII'"
I solved this by adding
--encoding UTF8 to the
initdb command below.
I added these flags
--lc-collate "en_US.UTF-8" and
--lc-ctype "en_US.UTF-8" based on the
pg_upgrade documentation in an attempt to have them match between clusters.
initdb command with flags
initdb command invocation is as follows.
/Applications/Postgres.app/Contents/Versions/15/bin/initdb \ --no-locale \ --encoding UTF8 \ --username postgres \ --lc-collate "en_US.UTF-8" \ --lc-ctype "en_US.UTF-8" \ -D "/Users/andy/Library/Application Support/Postgres/var-15"
After getting the
initdb configured with the flags listed above,
--check passed 100% of the checks. 🎉
The very satisfying output is below.
Performing Consistency Checks ----------------------------- Checking cluster versions ok Checking database user is the install user ok Checking database connection settings ok Checking for prepared transactions ok Checking for system-defined composite types in user tables ok Checking for reg* data types in user tables ok Checking for contrib/isn with bigint-passing mismatch ok Checking for presence of required libraries ok Checking database user is the install user ok Checking for prepared transactions ok Checking for new cluster tablespace directories ok *Clusters are compatible*
Once the checks completed, I ran
--check, so it ran for real, and it ran without errors.
Following the recommendations, I performed database maintenance operations on the new cluster. In general these are things like
pg_upgrade prints out a
vacuumdb command to run which was helpful.
/Applications/Postgres.app/Contents/Versions/15/bin/vacuumdb \ -U postgres \ --all \ --analyze-in-stages
pg_upgrade helped me upgrade the cluster. I had a new PostgreSQL 15 cluster, running with the data directory from the previous major version.
Being able to perform a non-destructive upgrade while leaving the old version intact is a nice design. Using
--check to perform a dry run first and work out issues was great.
While I did run into minor issues, I solved each one with help from Google and the upgrade was successful. Performing this upgrade on a large database would be more daunting, but this experience in local development was a way to get some practice.
Hopefully my upgrade experience is useful to PostgreSQL developers, Postgres.app users, documentation authors, or anyone else using
If you have any feedback, suggestions, or corrections, please leave a comment or contact me here.
Thanks for reading!