My Firefish instance was set up in November last year, and the database version it uses is naturally not the latest. Specifically, it’s PGroonga 12, an ancient version based on Postgres from five years ago. To avoid future compatibility and security issues, and for better performance, upgrading this database today is a must.

Upgrading a database running in Docker is quite different from upgrading other Docker containers. It’s not just a matter of pulling a new image and calling it a day. Since Postgres often has breaking changes with each major version, the persistent files generated by the old and new versions are not compatible. If you directly pull a new image and run it, the database won’t start. You must use certain methods to migrate the data.

Postgres has an official upgrade tool called pg_upgrade¹, but I won’t be using it this time because my database has been running for over a year, and it’s based on a version from five years ago. I’m not sure if this tool would work without issues. For this migration, the general idea is to export the SQL and then import it into the new version.

Deployment Overview Before Upgrade

Here is some configuration information you need to know before the upgrade:

The database part of the Docker Compose file is as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

db:
    restart: unless-stopped
    image: groonga/pgroonga:3.1.9-alpine-12-slim
    container_name: firefish_db
    networks:
      - calcnet
    env_file:
      - .config/docker.env
    volumes:
      - ./db:/var/lib/postgresql/data
    healthcheck:
      test: pg_isready --user="${POSTGRES_USER}" --dbname="${POSTGRES_DB}"
      interval: 5s
      timeout: 5s
      retries: 5

Exporting the Current Database

Before upgrading the database, first, take down the Firefish orchestration:

1

docker compose down

Start the database container alone and export the current database to SQL:

1
2

docker compose up -d db
docker exec -it firefish_db pg_dumpall -U example-firefish-user > backup.sql

Since Firefish’s database is relatively large, the export process may take a long time. For instance, my personal instance took over ten minutes.

Processing the Exported File

It might be due to changes in the authentication method in the new version of Postgres, but if you directly import the previously exported backup.sql file into the new database, it will change the new database’s Authentication Scheme, causing Firefish to fail to authenticate when connecting to the database later. To avoid this issue, you need to process the current backup.sql file, extracting only the firefish database portion instead of importing all the data. ²

1
2
3

#!/bin/bash
[ $# -lt 2 ] && { echo "Usage: $0 <postgresql dump> <dbname>"; exit 1; }
sed  "/connect.*$2/,\$!d" $1 | sed "/PostgreSQL database dump complete/,\$d"

Create a Shell script with the above content and save it as script.sh to process the current backup.sql:

1
2
3

nvim script.sh  # Or any editor you prefer
chmod +x script.sh
./script.sh backup.sql firefish >> upgrade.sql

If everything goes well, a file named upgrade.sql will appear in the current directory. You can open it to check and ensure it was exported correctly.

Importing Existing Data into the New Database

Modify docker-compose.yml:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

db:
    restart: unless-stopped
    image: groonga/pgroonga:3.2.2-alpine-16-slim  # Latest container
    container_name: firefish_db
    networks:
      - calcnet
    env_file:
      - .config/docker.env
    volumes:
      - ./database:/var/lib/postgresql/data # Note this change
    healthcheck:
      test: pg_isready --user="${POSTGRES_USER}" --dbname="${POSTGRES_DB}"
      interval: 5s
      timeout: 5s
      retries: 5

Notice that the database directory mapping configuration has changed from ./db:/var/lib/postgresql/data to ./database:/var/lib/postgresql/data. This is to give the new database a fresh start while preserving the old persistent data. Even if something goes wrong, you can always revert to the old database.

Pull and start the new container:

1
2

docker compose pull
docker compose up db -d

Import upgrade.sql into the new database:

1

cat upgrade.sql | docker exec -i firefish_db psql -U example-firefish-user -d firefish

Depending on the size of the database, the import process may also take a long time.

Finishing Up

Once the import is complete, start the entire orchestration:

1
2

docker compose stop db
docker compose up

It is recommended not to use the -d parameter for the first startup after the import. Start without any parameters to ensure there are no issues during the startup process, then restart with the -d parameter.

Finally, log in to the just-started Firefish instance and check for any data loss. If everything is fine, congratulations, you’re done! 🎉