This guide is mainly based on Arch's wiki, with some additions:
- I couldn't upgrade the database because the upgrade tool needs the
postgis-2.4.sofile - The created cluster could not be used to upgrade from the old data because the locale was set to 'C' (needed to add
--locale=en_US.UTF-8) - The upgrade tool checks if the the new postgresql installation has a compatible postgis version. That means we need postgis 2.4 for postgresql 11. Unfortunately, we cannot install it over the package manager, instead we need to compile it from source.
# Stop currently running server
systemctl stop postgresql.service
# Install new packages
sudo pacman -S postgresql postgresql-libs postgresql-old-upgrade
# Move data to olddata
sudo mv /var/lib/postgres/data /var/lib/postgres/olddata
sudo mkdir /var/lib/postgres/data /var/lib/postgres/tmp
sudo chown postgres:postgres /var/lib/postgres/data /var/lib/postgres/tmp
# Copy postgis-2.4.so to the upgrade tool's lib folder (you may need to downgrade to postgis-2.4 first)
pacman -U /var/cache/pacman/pkg/postgis-2.4.4-5-x86_64.pkg.tar.xz
sudo cp /usr/lib/postgresql/postgis-2.4.so /opt/pgsql-10/lib
# Go back to postgis 2.5
sudo pacman -S postgis
# We also need postgis 2.4 for postgresql 11, otherwise the upgrade will fail
cd /tmp
wget https://download.osgeo.org/postgis/source/postgis-2.4.5.tar.gz
tar xvzf postgis-2.4.5.tar.gz
cd postgis-2.4.5
./configure
make
# Instead of installing everything: https://superuser.com/a/1267640
sudo make install
# Switch to postgres user, create new database and upgrade the old data
sudo su postgres
initdb --locale=en_US.UTF-8 -D '/var/lib/postgres/data'
cd /var/lib/postgres/tmp
pg_upgrade -b /opt/pgsql-10/bin -B /usr/bin -d /var/lib/postgres/olddata -D /var/lib/postgres/data
# Start server
systemctl start postgresql.service
# Finally, update the postgis version:
sudo -u postgres psql -d mydatabase -c 'ALTER EXTENSION postgis UPDATE TO "2.5.0";'
A lifesaver, thanks! I've used it to upgrade from Postgres 11 to 12.