Issues restoring from backup

[candid shell]

Hello, im attempting to restore a new docker instance from a Truenas Scale backup per the docs and am running into some errors. Below are the list of commands and responses as part of the process

[magic glade]

:wave: Hey @candid shell,

Thanks for reaching out to us. Please follow the recommended actions below; this will help us be more effective in our support effort and leave more time for building Immich .

References

• Container Logs: docker compose logs docs
• Container Status: docker compose ps docs
• Reverse Proxy: https://immich.app/docs/administration/reverse-proxy

Checklist

1. :ballot_box_with_check: I have verified I'm on the latest release(note that mobile app releases may take some time).
2. :ballot_box_with_check: I have read applicable release notes.
3. :ballot_box_with_check: I have reviewed the FAQs for known issues.
4. :ballot_box_with_check: I have reviewed Github for known issues.
5. :ballot_box_with_check: I have tried accessing Immich via local ip (without a custom reverse proxy).
6. :ballot_box_with_check: I have uploaded the relevant logs, docker compose, and .env files, making sure to use code formatting.
7. :ballot_box_with_check: I have tried an incognito window, disabled extensions, cleared mobile app cache, logged out and back in, different browsers, etc. as applicable

(an item can be marked as "complete" by reacting with the appropriate number)

If this ticket can be closed you can use the /close command, and re-open it later if needed.

[magic glade]

Successfully submitted, a tag has been added to inform contributors. :white_check_mark:

[candid shell]

I cant seem to copy any text in files that are open within Truenas scales jailmaker that docker is running in so i attached some screenshots

[brazen smelt]

You made the backup with a mismatched postgres version. Probably using the database dumper with a old config

You should change the immich postgres version to 16 and try again

[candid shell]

ok upgrading to 16 got me a lot further in the restoration process but now i am getting this error:

COPY 38
COPY 39
COPY 0
ERROR:  pgvecto.rs: Bad literal.
INFORMATION: hint = Bad sequence.
CONTEXT:  COPY smart_search, line 29251, column embedding: "[0.0003730172, 0.028498271, -0.021713013, 0.048647813, -0.00309021, -0.029211964, -0.013904419, 0.04..."```

Starting the server reveals some errors as well

I attempted the restore with the sed fragment per the docs just in case to no avail

Should i be adding DB_SKIP_MIGRATIONS=true to all containers?

[brazen smelt]

Please post the new compose and the exact sequence of events you are taking when restoring on the command line

[candid shell]

ok here you go

[brazen smelt]

@deft quiver Any thoughts?

[brazen smelt]

Might be worth trying a restore into pg16-vectors 0.2.0 (you’re using 0.2.1)

[deft quiver]

When I see “bad literal” it usually means the embedding needs quotes. Could be something like that, or perhaps a string encoding issue

[candid shell]

i tried using pg16-v0.2.0 and got the same error sadly

[candid shell]

Forgive my ignorance but I don't fully understand your comment. Are you saying that a portion of the restore command should be in quotes?

[brazen smelt]

No, this would be an issue within the database dump itself

Was the backup made on the same system / OS as the restore?

[candid shell]

The backup comes from my immich instance running directly on Truenas. I'm restoring to a docker instance running in a jailmaker on the same machine

[deft quiver]

Did the backup go through windows or macos at any point?

[candid shell]

Nope!

[brazen smelt]

Can you post the offending line from the dump with 5-10 lines of context before and after?

You’ve been fully resetting the database before each restore attempt?

Post the full way you made the backup as well

Is making another backup an option?

[candid shell]

Is this what youre asking for?

COPY 3
COPY 27290
COPY 60019
COPY 31788
COPY 0
COPY 32201
COPY 4831
COPY 31788
COPY 27290
COPY 206877
COPY 1
COPY 0
COPY 0
COPY 171
COPY 0
COPY 4274
COPY 2
COPY 925
COPY 13
COPY 38
COPY 39
COPY 0
ERROR:  pgvecto.rs: Bad literal.
INFORMATION: hint = Bad sequence.
CONTEXT:  COPY smart_search, line 29251, column embedding: "[0.0003730172, 0.028498271, -0.021713013, 0.048647813, -0.00309021, -0.029211964, -0.013904419, 0.04..."```

Looks like there is this error too:

SET
SET
SET
ERROR:  cannot drop the currently open database
ERROR:  current user cannot be dropped
ERROR:  role "immich" already exists
ALTER ROLE
SET
SET
SET
SET```

[candid shell]

Per the docs ive been removing everything from the db directory which should be removing the db? Is there something else i should be doing as part of this?

[candid shell]

This is the way i made the backup:
root@kyllonas[~]# k3s kubectl exec -t immich-postgres-7b5f576c66-89vdj -n ix-immich -- pg_dumpall --clean --if-exists --username=immich | gzip > "/mnt/nvme-storage/backups/immich/postgres_dump_2.sql.gz"

[brazen smelt]

This error is totally normal

[brazen smelt]

No, we’d like to see the lines from the dump.sql.gz file

[brazen smelt]

That should be fine if you’re stopping the container before / restarting after

[candid shell]

ok, i think this is what you are asking for

[candid shell]

Ive been running docker compose down -v at the start of every attempt

I created a new dump and got the same error:

INFORMATION: hint = Bad sequence.
CONTEXT:  COPY smart_search, line 29112, column embedding: "[-0.0005469388, -0.018609032, 0.008381439, 0.035322923, 0.008746837, -0.018083166, 0.03473919, 0.057..."```

[brazen smelt]

I see what looks like a new line in place of a UUID

[candid shell]

oh yeah i see that

[brazen smelt]

#8554 seems like it could be sort of related

[magic glade]

[Discussion] (immich-app/immich#8554)

[candid shell]

It does. From what i gather it sounds like i should run these commands in my original instance then dump and restore? https://github.com/immich-app/immich/discussions/7431#discussioncomment-8593807

Or is there a way to modify the dump

[brazen smelt]

I would make a filesystem level backup of the pgdata while the DB is stopped before making any edits to the DB

Mert might have some thoughts on what to do next

[candid shell]

I created a backup of the pgData dir and ran those commands in my truenas instance and got some reference errors for the embedding column not existing in the asset_faces table

i backed up the db after running those commands and the restoration seems to have succeeded

Gonna fire things up and cross my fingers

[brazen smelt]

Excellent , hope that works

[candid shell]

no errors in the logs and the ui at least attempts to redirect me to my oauth provider

[candid shell]

All the oauth settings are contained in the db? No other kind of configs I would need to bring over?

[brazen smelt]

not unless you had setup a config file, which isn't the defualt

[candid shell]

It's up and running successfully. Thank you so much for the help!