Ephes Blog

Miscellaneous things. Mostly Weeknotes and links I stumbled upon.

Date -

Upgrading Postgres

, Jochen

I am mainly writing this article to remember how to do this next time 😅 (it is 14 or 15 to 16 here, but it should also work with newer versions too). This writeup is for Debian / Ubuntu and is based on this article I found as the first search result on how to upgrade postgres. First, make sure that the packages on your system are up to date and that you have the version of postgres you want to upgrade installed (usually running on port 5433).

apt-get update
apt dist-upgrade

# Show installed postgres versions
apt list --installed | rg postgresql

Apply any configuration changes you made to the running version to the version you want to upgrade. Then you can stop your running postgres version and run pg_upgrade.

systemctl stop postgresql.service
su - postgres

# Check if clusters are compatible
/usr/lib/postgresql/16/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/15/main \
  --new-datadir=/var/lib/postgresql/16/main \
  --old-bindir=/usr/lib/postgresql/15/bin \
  --new-bindir=/usr/lib/postgresql/16/bin \
  --old-options '-c config_file=/etc/postgresql/15/main/postgresql.conf' \
  --new-options '-c config_file=/etc/postgresql/16/main/postgresql.conf' \
  --check

# Run without --check to upgrade your cluster

Swap the ports of the old and new versions and restart the postgres service.

# change "port = 5434" to "port = 5432"
vim /etc/postgresql/16/main/postgresql.conf

# change "port = 5432" to "port = 5433"
vim /etc/postgresql/15/main/postgresql.conf

systemctl start postgresql.service

Make sure you are running the new postgres version.

su - postgres
psql -c "SELECT version();"

Remove the old Postgres Version

apt-get remove postgresql-15 postgresql-server-dev-15
rm -rf /etc/postgresql/15/
su - postgres
./delete_old_cluster.sh

Postgres on a Mac with Homebrew

I use Macs running Homebrew for development. And I like to keep my postgres databases in the project repository to make it easy to remove the database by just deleting a directory. And to start the right database along with all the other needed services using a Procfile. Upgrading postgres there works the same way. First install the postgres version you want to upgrade to and create a new data directory.

brew install postgresql@16
/opt/homebrew/Cellar/postgresql@16/16.0/bin/initdb -D databases/postgres_new

Then upgrade the database with pg_upgrade and remove the old data directory.

/opt/homebrew/Cellar/postgresql@16/16.0/bin/pg_upgrade \
 --old-datadir databases/postgres \
--new-datadir databases/postgres_new \
--old-bindir /opt/homebrew/Cellar/postgresql@14/14.9/bin \
--new-bindir /opt/homebrew/Cellar/postgresql@16/16.0/bin \
--check

# run pg_upgrade without --check to actually upgrade the database

rm -r databases/postgres
mv databases/postgres_new databases/postgres