Upgrading Inventree Postgres to 17

Due to me moving, I haven’t really played with electronics lately. However, that was about to change. So I started InvenTree, my electronics inventory handling tool, only to be greeted by an error. The darn thing was not working because it upgraded to 1.2.x which requires Postgres 14 at minimum and I had Postgres 13. Well, I figured to just simply upgrade the database. Let the yak shaving begin!

Before I started with anything I pinned my Inventree to 1.1.12 (last version to use Postgres 13) and made backup of my docker containers and data. Then, the first stop was Inventree’s migration instructions. Instructions are clearly written and I am almost positive they worked before. Unfortunately, since I procrastinated, they worked no longer. I tried a few variations but each ended in transaction_timeout issue.

pg_restore: error: could not execute query: ERROR:  unrecognized configuration parameter "transaction_timeout"

After investigating a bit, I found out that my migration from 13 to 14 might be slightly complicated by the fact Inventree dumped database using binaries for Postgres 17. Again, this wouldn’t have been an issue if I haven’t been lazy with my upgrades. However, even when I tried to go directly to 17, I ended up with the seeminly empty database. Yes, database wasn’t really empty, but does it really matter if nothing appears in UI?

So I decided to go with a different route - export followed by import. It just makes sense it would work. But no, restoration always resulted in issue with key duplication

psycopg.errors.UniqueViolation: duplicate key value violates unique constraint "common_inventreesetting_key_key"

After messing with it for a while to no avail, I decided to do the most straightforward thing possible. Why not just dump and restore the database manually without involving Inventree? Procedure that ended up working for me was as follows:

1. At first, I just dumped database while running Inventree 1.1.12 on top of Postgres 13.

docker exec -it inventree-db pg_dump -U pguser inventree > inventree_backup.sql

2. With that out of way, I stopped the containers.

docker compose down

3. Delete old database copy so we have it clean. Remember to do the backup beforehand.

rm -rf data/pgdb/

4. Edit compose.yaml and bump Postgres to 17

5. Bring up containers and let them create new database (once it is done, use d to "detach).

docker compose up

6. Stop Inventree services, in my case inventree-server and inventree-worker.

docker compose stop inventree-server inventree-worker

7. Drop the database and recreate it empty.

docker exec -it inventree-db psql -U pguser -d postgres -c "DROP DATABASE inventree;"
docker exec -it inventree-db psql -U pguser -d postgres -c "CREATE DATABASE inventree;"

8. Restore from backup

docker exec -i inventree-db psql -U pguser inventree < ./inventree_backup.sql

9. Start Inventree services back up.

docker compose start inventree-server inventree-worker

10. Now upgrade 1.2.x version, in my case by setting INVENTREE_TAG=stable (in .env file)

Since we kept the same version of Inventree for steps 1-9, we avoided the need to schema update. Inventree was not aware of any change and Postgres could simply upgrade its data to the version 17. Once we were at version 17, Inventree’s automatic upgrade scripts knew how to handle version bump from 1.1.12 to 1.2.3 as version 17 is supported by both.

Now, onto getting some soldering done.