Immich App postgres Database does not migrate from 24.04 to 24.10

Apps and Virtualization
2

RESOLUTION

I was finally able to fix this issue with a lot of trial and error. I manually migrated my immich database to Immich 1.131.3 on 24.10. Currently rebuilding smart search data and hoping everything works; it looks good so far!

If anyone runs into this in the future as more people upgrade, good luck! I still have no idea why TrueNAS was unable to migrate my Immich App. I’m guessing my db was in a bad state or something. So here are the additional snags I hit and the rough steps I took to get my immich DB into a state where manual migration succeeded.

First, I copied my immich database to a test/migration DB on 24.04 in pgadmin. Within this migration database, I truncated the smart_search table. Once I did this, I was able to dump and restore this migration database to the immich docker postgres container on 24.10. No more “Bad literal” errors!

More work was required though. When I started the server, I encountered this issue with a missing geodata_places table from the DB.
relation "geodata_places" does not exist

So back in the test DB on pgadmin, I can see this table wasn’t restored from the old db. So I try to dump the working geodata_places table and restore to the migration DB. Unfortunately, another error arises due a deprecated column:
type "public.earth" does not exist

Thankfully running the SQL from this discussion on the migration DB fixed my issue. I was able to dump and restore the geodata_places into the migration db.

drop extension if exists earthdistance cascade;
drop extension if exists cube;

create extension cube;
create extension earthdistance;
create table geodata_places( id integer not null primary key, name varchar(200) not null, longitude double precision not null, latitude double precision not null, "countryCode" char(2) not null, "admin1Code" varchar(20), "admin2Code" varchar(80), "modificationDate" date not null, "admin1Name" varchar, "admin2Name" varchar, "alternateNames" varchar, "earthCoord" earth generated always as (ll_to_earth(latitude, longitude)) stored);

From there I dumped the migration db as plaintext, upgraded to 24.10, and restored onto the docker postgres roughly using the guides I linked above. Finally, after all that, I was able to get immich up and running with my users and library seemingly in tact!

You may be able to get the TrueNAS migration to work by getting the DB into a good state before running the 24.10 upgrade but YMMV. I haven’t tried it, I’m just looking to move on after this project.

TLDR:

  1. On 24.04, copy old immich db to a WIP db
  2. Truncate smart_search table on the WIP db
  3. Apply geodata_places fix to the WIP db
  4. Dump geodata_places from old db and restore to WIP db
  5. Dump WIP db, upgrade to 24.10 and restore to new db