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.


  • Intro
  • Steps
  • Running pg_upgrade with --check
  • Checksums
  • Errors with extensions
  • Having a cluster install user
  • Errors with extensions
  • Locale provider mismatch
  • Encodings mismatch
  • Final initdb command 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 installs PostgreSQL in versioned directories. I had separate directories for versions 14 and 15. 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: initdb Documentation

To get started, check which version of pg_upgrade is active.

$ which pg_upgrade

If the old version is active, make sure to use pg_upgrade from the new PostgreSQL 15 version.


  1. Download and install This is a regular Mac OS app with a .dmg.
  2. Choose “Replace” during installation. You are replacing the Mac OS app, not modifying the current PostgreSQL cluster.
  3. Open 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_14, and 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 pg_ctl program, -D and a path to the data directory.

pg_ctl stop \
-D "/Users/andy/Library/Application\ Support/Postgres/var-14/"

While running “Initialize” from 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.

/Users/andy/Library/Application\ Support/Postgres/var-15

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

Running pg_upgrade with --check

Run 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 pg_upgrade besides --check.

The full command with the version 15 pg_upgrade and all 5 arguments is below.

/Applications/ \
--check \
--old-datadir "/Users/andy/Library/Application Support/Postgres/var-14" \
--old-bindir "/Applications/" \
--new-datadir "/Users/andy/Library/Application Support/Postgres/var-15" \
--new-bindir "/Applications/"


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/ \
--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 (pg_stat_statements, and auto_explain).

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/ 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 initdb command.

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 no-locale to initdb, and running it directly on the new data directory.

Encodings mismatch

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.

pg_upgrade Documentation

Final initdb command with flags

The final initdb command invocation is as follows.

/Applications/ \
--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, pg_upgrade with --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 pg_upgrade without --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 VACUUM, ANALYZE, etc.

pg_upgrade prints out a vacuumdb command to run which was helpful.

/Applications/ \
-U postgres \
--all \

Closing Thoughts

The 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, users, documentation authors, or anyone else using pg_upgrade.

If you have any feedback, suggestions, or corrections, please leave a comment or contact me here.

Thanks for reading!