I recently wanted to upgrade a postgres database to the latest version.

This is the workflow to achieve this with the following prerequisites:

• Data of old database is inside new-data-folder
• Data of new database is at old-data-folder
• Both folders are inside the same directory
• Upgrading from 11 to 14

Your setup will surely differ, so just change the paths, download links and postgres version accordingly when using this.

podman pull docker.io/postgres:14

podman run -ti -v new-data-folder:/var/lib/postgresql/data -v old-data-folder:/var/lib/postgresql/old docker.io/postgres:14 /bin/bash

apt install wget bzip2 gcc make libreadline-dev zlib1g-dev procfs


wget -O - https://ftp.postgresql.org/pub/source/v11.4/postgresql-11.4.tar.bz2 \| tar jxf - |

cd postgresql-11.4

./configure
make install

# binaries for 11 are now installed to /usr/local/pgsql/bin/postgres 
    
# make sure to undo this again when finished
chown -R postgres /var/lib/postgresql/

su - postgres -c "/usr/lib/postgresql/14/bin/initdb -A trust"
su - postgres -c "/usr/lib/postgresql/14/bin/pg_ctl -D /var/lib/postgresql/data -l logfile start" 

ps -aux # take note of the ID of postgres $process_id
kill $process_id

# start the upgrade
su postgres
pg_upgrade -b /usr/local/pgsql/bin -d /var/lib/postgresql/old -D /var/lib/postgresql/data
exit

# start server of new postgres version
su - postgres -c "/usr/lib/postgresql/14/bin/pg_ctl -D /var/lib/postgresql/data -l logfile start"
su postgres

# upgrade the extensions using the just started postgres server
psql -f update_extensions.sql
exit
exit

The upgrade is done. If you had custom access set up is now time to copy over the pg_hba.conf from old-data-folder to new-data-folder

Make sure to chown your data on the host back to the user that is actually running the database. E.g: chown -R postgres:postgres new-data-folder

Finally start the postgres14 container with the new data folder and you should be good to go. Big shoutout to the postgres contributors for making the update process so straight forward.

Final thoughts

When I have a problem to solve I often just scan webpages until I find a solution that fits my exact issue.

Many of you reading this are probably the same so I kept the writing short on this post.

Please let me know in the comments what you think of this approach.

Thanks and happy hacking!