I took the dive into Docker Containers on Linux. I’ve been meaning to do this for quite some time, but really my specialty of ColdFusion and Microsoft SQL Server really doesn’t require much in the way of containers.

However, my current project is to convert our internal wiki from WikiPedia to Wiki.js. During this process, I’ve learned about Docker and Docker Compose. I’m currently running Docker 20.10.12 and Docker compose V2. So I use “docker compose” rather than “docker-compose”. This is being run on Amazon Linux 2, on-premise. These steps should work for any RHEL-based Linux distro and beyond.

During this process, I somehow ended up running PostgreSQL 11 when the latest version is 14. I already had data in the database, so I thought it’d just be best to learn how to upgrade PostgreSQL, which is not as simple as changing the version number in the docker-compose.yml config file.

Thank you to José Postiga with Better Programming for getting started with the “How to Upgrade Your PostgreSQL Version Using Docker” blog post. I was able to accomplish the upgrade with some modifications.

To upgrade PostgreSQL from 11 to 14 (other versions seem to require the same steps), these general steps must be accomplished:

1. Create a temporary folder for the database backup and share it with your PostgreSQL container instance
2. Backup the database
3. Shutdown the database container
4. Remove the database data
5. Change the PostgreSQL version
6. Change password encryption
7. Start the database container
8. Restore the database

Backup Database

Consider taking a backup or a snapshot of your server/instance before moving any further.

cd {docker compose project folder}
docker compose down
sudo mkdir /dbbackup
sudo chmod 777 /dbbackup
vim docker-compose.yml

At this time, all containers are spun down. Add /dbbackup as a shared volume to your docker config for your PostgreSQL service. Example “- /dbbackup:/dbbackup”. Save and exit vim.

Next, we will spin up only the PostgreSQL container and enter its bash. Then we will backup the entire database.

docker compose up {database service name} -d
docker compose exec {database service name} bash
pg_dumpall -U {postgres_user} > /dbbackup/dump.sql
exit

We are now back in the host’s user’s bash. We’ll want to shut down PostgreSQL and remove the database data. Warning: this will delete your active database data until it is restored. Backups are advised!

docker compose down
docker volume ls
docker volume rm {associated persitant database volume}
vim docker-compose.yml

While in vim, change the PostgreSQL version number (example: image: postgres:14). Save and exit from vim.

Change Encryption to SCRAM-SHA-256

Note: Skip this scram-sha-256 change if you run Wiki.js v2. It does not support it.

If you are using the POSTGRES_PASSWORD_FILE environment variable, and use a plain text password (ex: openssl rand -base64 32) or MD5, PostgreSQL will not let you login. You will get “FATAL: password authentication failed for user “{user}””, “DETAIL: User “wiki” does not have a valid SCRAM secret.”, and “Connection matched pg_hba.conf line 100: “host all all all scram-sha-256″”.

I replaced the password with a SCRAM-SHA-265 encrypted password. I used a generated password from Lastpass, but you can use anything (the longer the better). Then used a PostgreSQL password generator on GitHub.

curl -sL https://github.com/supercaracal/scram-sha-256/releases/download/v1.0.0/scram-sha-256_1.0.0_linux_amd64.tar.gz | tar zx -C /tmp
/tmp/scram-sha-256
rm /tmp/scram-sha-256

Once the hash is created, copy the generated output into your password file defined as the POSTGRES_PASSWORD_FILE environment variable.

Now, the user and password are only created and not updated. So we will need to update it in psql as well.

docker compose exec db bash
psql -U {user}
ALTER ROLE jkatz PASSWORD '{generated scram-sha-256 hash}';
SELECT
    rolname, rolpassword ~ '^SCRAM-SHA-256\$' AS has_upgraded
FROM pg_authid
WHERE rolcanlogin;
\q
exit

The above query should return true next to your user, confirming it’s a valid SCRAM-SHA-256 hash.

Thanks to Jonathan S. Katz for his helpful post How to Upgrade Your PostgreSQL Passwords to SCRAM.

Change Encryption to MD5

Note: Skip this step if you want to use SCRAM-SHA-256 (recommended)

Now, however, Wiki.js 2 and possibly your client do not support passing SCRAM-SHA-256 hashes, as I found out the hard way. Therefore we need to update postgresql.conf to go back and accept MD5 passwords. First, we’ll need to enable IPv4 forwarding on the host to download a text editor inside the container. Then we’ll need to install a text editor.

sudo sysctl -w net.ipv4.ip_forward=1
docker compose exec db bash
apt-get update
apt-get install vim
vim /var/lib/postgresql/data/postgresql.conf

Verify password encryption is set to md5 from last query’s result.

Find the “password_encryption” property and uncomment it. Set it to “md5”. Example:

password_ecryption = md5

Save and exit. Next we’ll want to reload the config.

psql -U {user}
SELECT pg_reload_conf();
SHOW password_encryption;
\q
exit

Restore Database

Next, we’ll want to start the container back up, and restore the database. In most cases the default database will be “postgres”.

docker compose up {database service name} -d
docker compose exec {database service name} bash
psql -U {postgres_user} -d {default database} -f /dbbackup/dump.sql
exit

If everything goes alright, spin up the remainder of your containers. For the first time, don’t detach the services (don’t use -d) so you can see the console output for errors.

docker compose up -d

Once everything is verified, remove the temp volume from your docker-compose.yml config and delete the directory (/dbbackup). Remember to stop your containers first.

If you have any suggestions to improve this post, please comment. I’d love to hear from you!

#4%e5%ae%ae%e5%b3%b6%e6%a4%bf%e3%81%ae%e5%a0%b4%e5%90%88, #docker, #docker-compose, #postgresql, #upgrade