restored all trash items | Immich | Page 1

cunning cedarBOT May 29, 2024, 5:39 PM

#

:wave: Hey @noble crystal,

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 immich .

References

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

Checklist

:ballot_box_with_check: I have verified I'm on the latest release(note that mobile app releases may take some time).
:ballot_box_with_check: I have read applicable release notes.
:ballot_box_with_check: I have reviewed the FAQs for known issues.
:ballot_box_with_check: I have reviewed Github for known issues.
:blue_square: I have tried accessing Immich via local ip (without a custom reverse proxy).
:ballot_box_with_check: I have uploaded the relevant logs, docker compose, and .env files using the buttons below or the /upload command.
: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.

noble crystal May 29, 2024, 5:40 PM

#

I extracted the backup and I have immich_2024_05_29_04_00_02.sql Not sure how to do the next steps.

half geyser May 29, 2024, 5:41 PM

#

So just to confirm, are you trying to get back items that were trashed before today?

#

Or items that you went through and trashed today

summer pawn May 29, 2024, 5:41 PM

#

Items that got restored from the trash

#

But should still get trashed

noble crystal May 29, 2024, 5:42 PM

#

Hopefully I haven't trashed anything today and I have a backup from midnight.

half geyser May 29, 2024, 5:42 PM

#

summer pawn Items that got restored from the trash

I understand that, I want to know when they were trashed

noble crystal May 29, 2024, 5:42 PM

#

I want to restore my trash from yesterday.

half geyser May 29, 2024, 5:42 PM

#

Alright cool

#

I have to go now but that backup should contain everything you need to do that

#

I'll help out later if nobody else gets round to it before then 🙂

noble crystal May 29, 2024, 5:43 PM

#

ok thanks

#

in the meantime I can continue adding pictures?

#

it's already kinda too late anyway since I added so many stuff since this morning.

half geyser May 29, 2024, 5:44 PM

#

noble crystal in the meantime I can continue adding pictures?

Yea that'll be fine

noble crystal May 29, 2024, 5:44 PM

#

good thanks

unreal vortex May 29, 2024, 5:53 PM

#

It seems like you can just get a list of asset IDs from your backup and then re-trash them

noble crystal May 29, 2024, 5:53 PM

#

what's the best way of doing that?

unreal vortex May 29, 2024, 5:53 PM

#

Do you have any idea roughly how many assets we are talking about? Like a few hundred, thousand?

noble crystal May 29, 2024, 5:53 PM

#

few hundreds I'd say

#

maximum a thousand but I think so

unreal vortex May 29, 2024, 5:55 PM

#

I think the easiest is probably to load the backup into a temporary database then do something like select id from assets where deletedAt is not null

noble crystal May 29, 2024, 5:57 PM

#

what application should I use? Ideally foss on windows which is my main system

unreal vortex May 29, 2024, 5:58 PM

#

Do you want to just send me the backup SQL file and I can do it for you?

noble crystal May 29, 2024, 6:00 PM

#

is it safe to do?

#

is there personal data in this db?

summer pawn May 29, 2024, 6:01 PM

#

There is exif data in there, so potentially locations, and hashes of your images (basically very low res thumbnails)

spare granite May 29, 2024, 6:01 PM

#

Also names of people that you've added, and the names and emails of users

summer pawn May 29, 2024, 6:01 PM

#

True

noble crystal May 29, 2024, 6:02 PM

#

oh

#

I'd prefer to try first I think

#

Thanks for suggesting though

unreal vortex May 29, 2024, 6:03 PM

#

No worries. I obviously wouldn't do anything with it, but if you feel more comfortable not sharing it that is totally fine. Just a bit technical so it might take some time to figure out / walk you through it.

noble crystal May 29, 2024, 6:03 PM

#

Yeah I'd prefer not to if I can 😛

spare granite May 29, 2024, 6:04 PM

#

You'll need to restore that backup "as usual" but into a separate postgres (not the one you're using for Immich)

#

Then we can query it to get the trash

noble crystal May 29, 2024, 6:05 PM

#

can I do this with pgadmin?

unreal vortex May 29, 2024, 6:08 PM

#

Maybe?

#

How big is your sql file?

noble crystal May 29, 2024, 6:08 PM

#

60mb

unreal vortex May 29, 2024, 6:09 PM

#

The file is basically a long list of sql statements to run to recreate the tables. You could manually copy / paste them into pgAdmin. 60mb is a lot of text though.

#

Best to just use the command line

noble crystal May 29, 2024, 6:10 PM

#

pgadmin isn't installed anymore

#

can I simply open the database in a windows app that would let me see the tables and that I can search with a term to list what was in the trash?

spare granite May 29, 2024, 6:11 PM

#

It's just a text file, so you can open it and search for "trash"

#

That should find you the definition of the trash table and then a long list of assets it contains

#

But it might be a bit of a search

unreal vortex May 29, 2024, 6:12 PM

#

"trash table" is the asset table where the asset has a deletedAt value

spare granite May 29, 2024, 6:12 PM

#

unreal vortex "trash table" is the asset table where the asset has a `deletedAt` value

Gdi I completely forgot about that >.<

#

So yeah, actually that won't really work

unreal vortex May 29, 2024, 6:13 PM

#

I'd just copy/paste the docker compose file, and then delete everything except the database section

#

then run docker compose up, and you will have a database container you can load the data into

noble crystal May 29, 2024, 6:14 PM

#

there's only 10 deletedAt results in vscode and I can see lot of null in the file. seems more like a binary file rather than a txt file

unreal vortex May 29, 2024, 6:14 PM

#

the database rows won't say deletedAt.

noble crystal May 29, 2024, 6:14 PM

#

I'm using truenas scale with kubernetes

unreal vortex May 29, 2024, 6:16 PM

#

do you even have docker installed on windows?

noble crystal May 29, 2024, 6:17 PM

#

nope

unreal vortex May 29, 2024, 6:43 PM

#

Well, you need to stand up a postgres database somewhere and then run all the sql commands from your backup on the database, using psql or similar.

noble crystal May 29, 2024, 11:55 PM

#

I was able to access my immich database on the running app

noble crystal May 30, 2024, 12:12 AM

#

I think that'll I take your offer @unreal vortex

#

I'm running out of time right now and I must fix other things

#

Can you please don't look at it apart of the trash stuff and then delete the file after?

unreal vortex May 30, 2024, 12:56 AM

#

noble crystal Can you please don't look at it apart of the trash stuff and then delete the fil...

Yup, I'll do exactly that.

#

Just send me a dm with a link or setting.

noble crystal May 30, 2024, 1:07 AM

#

thanks

noble crystal May 30, 2024, 1:32 AM

#

i sent you a pm!

unreal vortex May 30, 2024, 2:24 PM

#

I have found 164 trashed assets

unreal vortex May 30, 2024, 3:23 PM

#

If you run this on your database it should re-trash all the assets

📎 message.txt

noble crystal May 31, 2024, 1:05 PM

#

how can I run this in pgadmin?

#

it's weird because I recall at least 250+ trashed assets

spare granite May 31, 2024, 1:05 PM

#

There should be an SQL field you can just paste it into

noble crystal May 31, 2024, 1:07 PM

#

I'll have to look this up on Monday, I must leave for a work weekend starting now!

noble crystal Jun 5, 2024, 7:40 PM

#

Is this the right place?

unreal vortex Jun 5, 2024, 9:02 PM

#

yup

noble crystal Jun 13, 2024, 6:39 PM

#

Sorry it took so much time to get back. Life is crazy right now!

#

I can't paste the code you sent me in the SQL page, I must not be at the right place ```UPDATE
"assets"
SET
"deletedAt" = NOW()
WHERE
"id" IN (
'42952774-0f0f-4892-a843-0ddd04da643c',
'5d5ea906-c6fa-45c1-ba4d-f68dcd2edd3f',
[...]
);

#

I just found something called the query tool which seems to accept SQL commands.

#

It worked! Although not everything from my trash has been restored. There were many more stuff in there which is bugging me.

#

Could there be any other string than the deletedAt to find deleted content?

summer pawn Jun 13, 2024, 7:16 PM

#

All trashed assets will/would have had the deletedAt column set

noble crystal Jun 13, 2024, 7:53 PM

#

the only thing I can't think of is that I would have empty the recycle bin at some point and that I don't remember it.

#restored all trash items

References

Checklist