Today I had to upgrade my PostgreSQL major version (14.6 → 15.1). I already knew (thanks to the Arch Wiki) how to upgrade major versions on Arch-based distros, but I have never done it on Alpine Linux before. To save all of you some troubleshooting and as a future reference for me, I will lay out the process here. This guide kind of assumes that you are familiar with upgrading PostgreSQL on Arch-based distros.

Getting started

Install the newer version of PostgreSQL, in my case, 15:

$ sudo apk add postgresql15

Stop the PostgreSQL service:

$ sudo rc-service postgresql stop

Make sure to change to the newer PostgreSQL version (in my case 15):

$ pg_versions set-default 15

Make yourself root user for convenience, and cd into the old version of the PostgreSQL directory:

$ sudo su
$ cd /var/lib/postgresql/14

Now, you follow similar instructions as shown on the Arch Wiki:

$ mv data olddata
$ cd /var/lib/postgresql/15
$ mkdir data tmp
$ chown postgres:postgres data tmp

Change to the postgres user and cd into the tmp folder:

$ su postgres
$ cd tmp

Initialize the new cluster:

$ initdb -D /var/lib/postgresql/15/data --locale=en_US.UTF-8 --encoding=UTF8 --data-checksums

Upgrade the cluster (note that the old PostgreSQL binaries are not located /opt/pgsql-PG_VERFSION/bin, in comparison with Arch):

$ pg_upgrade -b /usr/libexec/postgresql14/ -B /usr/libexec/postgresql15/ -d /var/lib/postgresql/14/olddata/ -D /var/lib/postgresql/15/data/

NOTE

In my case, I got the following two errors when running the pg_upgrade command. If you do too, follow these steps:

Error 1

old cluster does not use data checksums but the new one does
Failure, exiting

To fix this, recreate the tmp and data directories in the new PostgreSQL version folder, and redo the initdb command in the following way:

$ initdb -D /var/lib/postgresql/15/data --locale=en_US.UTF-8 --encoding=UTF8 # Note the removed --data-checksums

Error 2

lc_collate values for database "template1" do not match:  old "C", new "en_US.UTF-8"
Failure, exiting

To fix this, recreate the tmp and data directories in the new PostgreSQL version folder, and redo the initdb command in the following way:

$ initdb -D /var/lib/postgresql/15/data --locale=C --encoding=UTF8 # Note that --locale has changed

If all is well, you can now start the PostgreSQL service again, and delete the old PostgreSQL version folder. Furthermore, you can remove the old PostgreSQL version:

$ sudo rc-service postgresql start
$ sudo rm -rf /var/lib/postgresql/14/
$ sudo apk del postgresql14

That’s it.

Cheers!

- Pim