Immich App postgres Database does not migrate from 24.04 to 24.10

candyman · April 2, 2025, 7:07pm

Hi all, trying to update from 24.04.2.5 to 24.10 and having one major issue - my Immich database doesn’t survive the migration. Upon starting 24.10, the updated Immich app spins up as if it’s a brand new instance with an empty db. On the 24.04 app, I’m using ixVolumes for the postgres data and backup storage; host paths for everything else. All of my data on the host paths has been preserved so no issue there.

From 24.04

Name:
immich
App Version:
1.119.1
Chart Version:
4.0.23
Last Updated:
N/A
Source:
immich.appgithub.com/truenas/charts/tree/master/community/immichgithub.com/immich-app/immich
Developer:
N/A
Catalog:
TrueNAS
Train:
community

I was able to dump the postgres db from the kubernetes container on 24.04 but it fails to restore on 24.10 docker postgres due to a data issue I believe. I’ve started reaching out for support from the Immich community regarding this issue to see if a manual migration is possible.

For my manual migration attempts I’m following these instructions which are based on immich’s recommended backup + restore procedures.

My restore begins working but fails trying to copy Smart Search data

COPY 0
ERROR:  pgvecto.rs: Bad literal.
INFORMATION: hint = Bad sequence.
CONTEXT:  COPY smart_search, line 25928, column embedding: "[-0.012000048, -0.026440227, -0.020461267, -0.012008893, 0.0068139997, 0.002615924, 0.017675625, 0.0..."

I would appreciate any guidance or pointers for figuring out why TrueNAS is unable to migrate this postgres db automatically for me. TrueNAS reports successful migration in app_migrations.log.

$ sudo cat /var/log/app_migrations.log
[2025/04/03 11:51:30] (DEBUG) app_migrations.migrate():250 - Migration details for 'system-update--2025-04-03_15:42:47' backup on 'apps_vms' pool
[2025/04/03 11:51:30] (DEBUG) app_migrations.migrate():253 - 'immich' app migrated successfully

netdata permissions look good in .ix-apps on 24.10 - uid is 999. Permissions container reports

2025-04-03 15:51:17.684375+00:00=== Applying configuration on volume with identifier [pgvecto_postgres_data] ===
2025-04-03 15:51:17.684622+00:00Current Ownership and Permissions on [/mnt/permission/pgvecto_postgres_data]:
2025-04-03 15:51:17.684655+00:00Ownership: wanted [999:999], got [0:0].
2025-04-03 15:51:17.684680+00:00Permissions: wanted [None], got [0755].
2025-04-03 15:51:17.684721+00:00---
2025-04-03 15:51:17.684745+00:00Ownership is incorrect. Fixing...
2025-04-03 15:51:17.684769+00:00Changing ownership to 999:999 on: [/mnt/permission/pgvecto_postgres_data]
2025-04-03 15:51:17.684793+00:00Ownership after changes:
2025-04-03 15:51:17.684817+00:00Ownership: [999:999]
2025-04-03 15:51:17.684851+00:00Skipping permissions check, chmod is falsy
2025-04-03 15:51:17.684875+00:00Time taken: 0.42ms
2025-04-03 15:51:17.684899+00:00=== Finished applying configuration on volume with identifier [pgvecto_postgres_data] ==

These are what my datasets look like in 24.04 Vs 24.10

24.04

Interestingly ix-apps is not actually mounted on the system but appears here in the dataset list.

24.10

My next attempt at salvaging this will be using the Heavy scripts to try and update immich on 24.04. Trying anything to get my db in a state where I can just restore it manually on 24.10.

Thanks in advance for any assistance or direction! I’m really stumped here

candyman · April 13, 2025, 6:57am

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:

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