Postgres Update in Docker
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
- Data of new database is at
- 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
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.
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!