Upgrading a neglected Redmine: PostgreSQL 9.6 → 15 and Redmine 5.0 → 6.1 in Docker

by

While checking my homelab for PHP2Shell, I took a closer look at my Redmine install. It isn’t PHP (Redmine is Ruby on Rails), but what I found was bad enough: sameersbn/redmine:5.0.2 from 2022 and sameersbn/postgresql:9.6, a database version that went end of life in 2021. Redmine 5.x is end of life too. Time to fix it properly, and to write it down for next time.

The plan

  1. Move the database from PostgreSQL 9.6 to a current version, while keeping Redmine on 5.0.2.
  2. Then upgrade Redmine itself.

Two big changes at once means debugging two things at once if something breaks, so they’re separate steps.

Step by step or straight to 6.1?

Redmine’s database migrations are cumulative: when 6.1 starts, it runs every migration between your version and 6.1 in order. Going 5.0 → 5.1 → 6.0 → 6.1 runs the same migrations, just in more steps. Stepwise upgrades mainly help to find out which version breaks a plugin or theme. I had neither:

ls /data/redmine/plugins /data/redmine/themes
# both empty

So: straight to 6.1.

Part 1: migrating the database

You can’t just point a new PostgreSQL major version at an old data directory. The safe way is dump and restore, with the old data left untouched as a fallback.

Reference counts first

Before touching anything, I recorded a few row counts to compare after the restore:

docker compose exec -T postgresql \
  psql -h 127.0.0.1 -U redmine -d redmine_production -c \
  "select (select count(*) from projects) projects, (select count(*) from issues) issues,
          (select count(*) from users) users, (select count(*) from attachments) attachments,
          (select count(*) from journals) journals;"

Dump, stop, new database, restore

docker compose stop redmine
docker compose exec -T postgresql \
  pg_dump -h 127.0.0.1 -U redmine -Fc redmine_production > /root/redmine_pg96.dump
docker compose down
cp compose.yaml compose.yaml.bak

The database service in compose.yaml becomes the official PostgreSQL image, with a new data directory outside Redmine’s data volume (the old one sat inside it, which also meant Redmine’s own backup could pick up database files):

  postgresql:
    image: postgres:15
    environment:
      - POSTGRES_USER=redmine
      - POSTGRES_PASSWORD=<same as DB_PASS>
      - POSTGRES_DB=redmine_production
    volumes:
      - /data/redmine-postgresql15:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U redmine -d redmine_production"]
      interval: 10s
      timeout: 5s
      retries: 5
    restart: always

And Redmine waits for a healthy database:

    depends_on:
      postgresql:
        condition: service_healthy

Watch the syntax: condition needs the map form. Leaving the old list dash in front (- postgresql:) gets you depends_on.0 must be a string.

docker compose up -d postgresql      # wait for (healthy)
docker compose exec -T postgresql \
  pg_restore -U redmine -d redmine_production --no-owner --no-privileges \
  < /root/redmine_pg96.dump

One error is expected here: schema “public” already exists. The new database ships with it, and the old dump tries to create it again. Harmless. After that, the same count query on the new database returned identical numbers, and Redmine 5.0.2 started fine on the new PostgreSQL.

Part 2: Redmine 5.0.2 → 6.1

With the database sorted, the Redmine upgrade itself was the easy part. I pinned the minor line instead of an exact version or latest:

sed -i 's|sameersbn/redmine:5.0.2|sameersbn/redmine:6.1|' compose.yaml
docker compose pull redmine
docker compose up -d
docker compose logs -f redmine

The image detects the new version, clears the cache, runs the migrations and starts puma, nginx and cron. With a small database it took well under a minute.

The catch: pg_dump version mismatch, and why I ended up on PostgreSQL 15

I first migrated to PostgreSQL 16. Everything ran, but Redmine’s built-in backup failed:

docker compose run --rm redmine app:backup:create
...
pg_dump: error: aborting because of server version mismatch
pg_dump: detail: server version: 16.x; pg_dump version: 15.x

pg_dump refuses to dump a server newer than itself, and the Redmine 6.1 image ships the PostgreSQL 15 client (the 5.0.2 image even had 14). With REDMINE_BACKUP_SCHEDULE=daily set, that would have meant a silently failing backup every night.

The fix: move the database once more, to PostgreSQL 15. That’s also the robust choice, because a future image with a newer pg_dump can still back up an older server. One detail: going down a version, use a plain SQL dump. Older pg_restore versions often can’t read custom-format dumps written by a newer pg_dump.

docker compose stop redmine
docker compose exec -T postgresql \
  pg_dump -U redmine --no-owner --no-privileges redmine_production > /root/redmine_pg16_plain.sql
docker compose down
# compose: postgres:16 -> postgres:15, new data dir
docker compose up -d postgresql
docker compose exec -T postgresql psql -U redmine -d redmine_production \
  < /root/redmine_pg16_plain.sql

Counts compared again, and the built-in backup now runs through:

docker compose run --rm redmine app:backup:create
...
Dumping PostgreSQL database redmine_production...
Dumping files...
Creating backup archive: ..._redmine_backup.tar...

Note that it’s docker compose run --rm, not exec: app:backup:create is a command of the image’s entrypoint, not a program inside the container.

Small fixes along the way

  • REDMINE_SECRET_TOKEN: without it, the image generates a new session token on every start and logs everyone out. Generate one with openssl rand -hex 64 and set it in the environment.
  • Time zone: I had TZ=Europe/Zürich. The time zone database has no entry with an umlaut, so it was silently ignored. It’s Europe/Zurich.
  • version: “2” at the top of the compose file is obsolete and only produces warnings. Removed.

Takeaways

  • Database and application upgrades are separate steps.
  • Verify with counts, not just “it starts”.
  • Match your PostgreSQL server to the client in your backup tooling. A newer server than the bundled pg_dump breaks backups silently.
  • Test the backup right after an upgrade, don’t wait for the nightly run.
  • Keep the old data until the new setup has run for a few days.

Leave a comment