QueryFailedError: relation "migrations" already exists | Immich | Page 1

ruby trout Apr 16, 2024, 6:47 PM

#

I want to migrate from docker to k8s (on TrueNas via Truecharts). I took a backup as described in the docs. But after restoring I got an error while starting the immich pod. Any Idea?

green thistle Apr 16, 2024, 6:52 PM

#

The immich server initializes the db on its first startup, so you have to first start the db, restore your backup and then start the other containers.

#

Otherwise, the backup will conflict with the newly initialized objects in the db.

ruby trout Apr 16, 2024, 6:56 PM

#

I'm pretty sure I done this that way. But I will do it again 🙂

loud brook Apr 16, 2024, 6:57 PM

#

You can post the commands you used here if you need some help. Most of them are outlined here: https://immich.app/docs/administration/backup-and-restore/

ruby trout Apr 16, 2024, 6:59 PM

#

I could not use pg_dumpall because of k8s. So I just dumped only the immich database. But this should work as well, right?

loud brook Apr 16, 2024, 6:59 PM

#

that's ok

#

for the restore make sure you specify the target database --dbname=immich

ruby trout Apr 16, 2024, 7:00 PM

#

Yes.

ruby trout Apr 16, 2024, 7:17 PM

#

Will I break anything, when I only restore the database, but not the images (i will do this later)?

loud brook Apr 16, 2024, 7:18 PM

#

it shouldn't break anything

ruby trout Apr 16, 2024, 7:18 PM

#

nice thx

loud brook Apr 16, 2024, 7:18 PM

#

I mean, don't delete the backup lol

#

but it should be fine

ruby trout Apr 16, 2024, 7:31 PM

#

Does not work. Same error again 😦

loud brook Apr 16, 2024, 7:37 PM

#

please send over the exact commands you are running

ruby trout Apr 16, 2024, 7:40 PM

#

postgres@red bolt-cnpg-main-1:~/data$ gunzip < immich.sql.gz | sed "s/SELECT pg_catalog.set_config('search_path', '', false);/SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true);/g" | psql --username=postgres --dbname immich

#

for backup

docker exec -t immich_postgres pg_dump --clean --if-exists --username=postgres immich | gzip > "immich.sql.gz"

loud brook Apr 16, 2024, 7:40 PM

#

Ok, let's start over, reset everything and before you run the gunzip you should shell into the database using psql immich and run \d+

ruby trout Apr 16, 2024, 7:43 PM

#

sure.. just a sec

loud brook Apr 16, 2024, 7:43 PM

#

let me know what that shows

ruby trout Apr 16, 2024, 7:47 PM

#

loud brook Apr 16, 2024, 7:47 PM

#

and to be safe can you do \l+

ruby trout Apr 16, 2024, 7:47 PM

#

loud brook Apr 16, 2024, 7:47 PM

#

Ok. now try the gunzip

#

then show me those \d+ and \l+ commands again

ruby trout Apr 16, 2024, 7:49 PM

#

loud brook Apr 16, 2024, 7:50 PM

#

That looks fine. At this point you should be able to start up Immich

ruby trout Apr 16, 2024, 7:52 PM

#

#

nope 😄

loud brook Apr 16, 2024, 7:53 PM

#

If you restart Immich one more time, does it still log that error?

#

and/or does Immich actually work?

#

I don't think migrations is not the first table to be created, so I wonder if there's a first run script that is trying to create it for some reason

#

actually it looks like it's a crash loop isn't it

ruby trout Apr 16, 2024, 7:56 PM

#

No Endpoint, so no service & no webgui

#

Yes. k8s restarts the pod every n seconds

loud brook Apr 16, 2024, 7:57 PM

#

I think it might be a permissions issue.. let's try doing a re restore but use the command: psql --dbname=immich --username=immich

#

This might be because you are not using pg_dumpall

ruby trout Apr 16, 2024, 7:58 PM

#

ok. lets do it again 😄

loud brook Apr 16, 2024, 7:58 PM

#

Sorry! Tricky situation. The restore commands are based on pg_dumpall which contains more permissions

#

pg_dump is just rows and tables I think

ruby trout Apr 16, 2024, 7:59 PM

#

Yes used pg_dumpall before. It works, but is more tricky because I need to reset the password. But I ended up with the exact same error

#

But lets do it again 🙂

loud brook Apr 16, 2024, 8:00 PM

#

surprised pg_dumpall had the same error.. hmm. I hope this helps

ruby trout Apr 16, 2024, 8:01 PM

#

Could this happend because my old immich version was not up2date but the newone is?

loud brook Apr 16, 2024, 8:03 PM

#

How much not up to date? It could yes

ruby trout Apr 16, 2024, 8:04 PM

#

Can I check this in my db dump?

#

Because my old docker instance is offline

ruby trout Apr 16, 2024, 8:06 PM

#

loud brook I think it might be a permissions issue.. let's try doing a re restore but use t...

Some kind of policy does not allow access from cli for user immich. (Yes the password is correct)

loud brook Apr 16, 2024, 8:08 PM

#

It’s not obviously stored in the DB. can you check the DB dump for any lines containing the word OWNER?

#

Also are any errors printed when you do the restore? Can you post the output of the restore command from the first way of doing it as username=postgres

ruby trout Apr 16, 2024, 8:10 PM

#

#

This looks wrong, right? This should be owned by user immich

#

because docker uses the postgres user, but truecharts uses an extra user?

loud brook Apr 16, 2024, 8:12 PM

#

Docker does not use the Postgres user, it uses an Immich user

#

Either way, your previous backup is quite odd

#

I recommend doing the restore, like it worked before, then run all those lines but change it to immich

ruby trout Apr 16, 2024, 8:13 PM

#

loud brook Also are any errors printed when you do the restore? Can you post the output of ...

I've not piped it. It's gone, but has no errors printed

ruby trout Apr 16, 2024, 8:13 PM

#

loud brook I recommend doing the restore, like it worked before, then run all those lines b...

Yes sir...

loud brook Apr 16, 2024, 8:15 PM

#

Actually, a few of those should stay as Postgres.. give me a few minutes to check which ones

ruby trout Apr 16, 2024, 8:15 PM

#

ok

loud brook Apr 16, 2024, 8:16 PM

#

Are you using this DB for anything else?

ruby trout Apr 16, 2024, 8:16 PM

#

no

loud brook Apr 16, 2024, 8:16 PM

#

Then it should be fine to own all them to immich

ruby trout Apr 16, 2024, 8:19 PM

#

uhhh, new error

lets restart postgres...

loud brook Apr 16, 2024, 8:22 PM

#

https://immich.app/docs/administration/postgres-standalone/

#

Bottom of this page

ruby trout Apr 16, 2024, 8:26 PM

#

Problems with k8s... it takes some time..

#

YES! Thank you so much! ❤️

loud brook Apr 16, 2024, 8:33 PM

#

Great! Really weird issue. Not sure how that happened

#

Please keep a record of the tables you owned to immich. In case there is an issue down the line you will know what to change back

green thistle Apr 16, 2024, 9:55 PM

#

loud brook https://immich.app/docs/administration/postgres-standalone/

Maybe it’d be simpler to just set the schema owner there after all

loud brook Apr 16, 2024, 9:58 PM

#

green thistle Maybe it’d be simpler to just set the schema owner there after all

It would be, my goal originally was to allow for multiple instances of Immich in one DB

#

I think this is the only thing that would conflict if we changed the owner

#

I'm honestly surprised that ALTER DEFAULT seemed to not work - baffles me (though it wasn't run in this case)

green thistle Apr 16, 2024, 10:00 PM

#

Yeah, I have no clue why that doesn’t work. I’d be interested to see if 0.2.1 behaves any differently here

#

We could update the default to that if so

loud brook Apr 16, 2024, 10:04 PM

#

Wait, I think I'm dumb. Schemas in postgres are a subset of databases, right?

#

For example if I do \dn+ in the postgres database I only see the public schema, but in the immich DB I see the vectors schema

#

So maybe we can just to alter owner...... I actually have two instances of Immich in my database so I will do some testing

#

I just did ALTER SCHEMA vectors OWNER TO... and ALTER TABLE pg_vector_index_stat OWNER TO... and both Immich instances are running fine

#

If we alter the schema owner first, the table owner will inherit, yes?

ruby trout Apr 16, 2024, 10:14 PM

#

I have nearly no knowledge about pg :/ So I can't help you there

#QueryFailedError: relation "migrations" already exists