Updating PostgreSQL

Stephan Burns | Feb 8, 2025

Upgrading Between Major Versions

Upgrading between major postgres versions isn’t as simple as changing the version number in your compose file. You topically have to do a manual migration between versions. The following steps are designed to be a safe way to migrate your existing data. As always whenever updating or modifying any system I recommend you TAKE A FULL BACKUP FIRST!!! Even though these instructions make several copies of your data along the way to prevent data loss it’s still important to make sure your data is safe before doing anything.

These steps work with any dockerized version of postgres but are specifically designed to assist in migrations of homelab2.0 services.

  1. Add a persistent backup directory. Add a temporary directory to store your postgres database dump file. This is an example of where to look to add the line. Don’t copy paste this. I don’t know what your docker-compose looks like.
db:
volumes:
    - database:/var/lib/postgresql/data
    - ./backup:/backup # <<<<<<<< This line
  1. Populate some variables
CONTAINER_NAME='db' # The name in the docker compose for your database. Probably db or postgresql
DB_USERNAME='db_username' # The username your application uses to login to the postgres database.
DB_TABLE='db_table' # The table your application uses. Probably the name of the application.
  1. Run the backup job. a. We first stop everything. We don’t want the application to be writing to the database while we are backing it up. b. Start just the database container. c. Wait for the database to initialize. d. Create the backup storing it in the /backup directory we created before. e. Shut the database down.
sudo docker compose down
sudo docker compose up -d $CONTAINER_NAME
sleep 2
sudo docker compose exec $CONTAINER_NAME /bin/bash -c "pg_dumpall -U $DB_USERNAME > /backup/backup.sql"
sudo docker compose down
  1. Update version number in docker compose file.
  2. Backup database files on NFS host. This code block assumes your database folder is on a Linux host and is named postgres This will rename that folder to be postgres.bak and create a new folder with matching permissions ans ownership. This process allows a fresh database to be created by postgres in the next step.
USER=$(ls -la . | grep postgres | awk '{print $3}')
GROUP=$(ls -la . | grep postgres | awk '{print $4}')
mv postgres postgres.bak
mkdir postgres
chmod 700 postgres
chown $USER:$GROUP postgres
  1. Start up the database, copy the database data into the new database, and start up the service.
sudo docker compose up -d $CONTAINER_NAME
sudo docker compose exec $CONTAINER_NAME /bin/bash -c "psql -U $DB_USERNAME -f /backup/backup.sql -d $DB_TABLE"
sudo docker compose up -d
  1. Deleting the extra copies DANGER!!! Double check that the application works as intended before running these steps. You cannot go back if something is messed up.

On the NFS host.

rm -rf postgres.bak

Remove the backup folder from your docker compose file.

rm -rf backup

Update PostgreSQL passwords to use SCRAM

There is a great article out there for this already. https://www.crunchydata.com/blog/how-to-upgrade-postgresql-passwords-to-scram#step-3-determine-who-needs-to-upgrade

The following are simplified instructions. I highly recommend if you have never done this before to read the original article. They go into great detail on what is actually happening here.

  1. Enable SCRAM
nano pg_hba.conf

Scroll all the way to the bottom. You will see a line that looks like this. Make sure it says scram like in the below example.

host all all all md5

host all all all scram-sha-256
  1. Change the encryption method to use scram. Make sure it’s set to scram.
nano postgresql.conf
  1. Restart the database container
sudo docker compose restart

password_encryption = scram-sha-256     # scram-sha-256 or md5
  1. Find all user accounts that aren’t using scram. They will have an f after their name if they aren’t using scram and a t if they are.
SELECT
    rolname, rolpassword ~ '^SCRAM-SHA-256\$' AS has_upgraded
FROM pg_authid
WHERE rolcanlogin;
  1. Update their password. For the sake of this quick copy paste example I will assume you have the original password somewhere. Your going to exec into the container and set the password on each user account that isn’t using scram.
sudo docker compose exec POSTGRESQL_CONTAINER /bin/bash -c "psql -d POSTGRESQL_DATABASE -U POSTGRESQL_USERNAME -c '\password'"