Unable to connect to database

[mystic tangle]

After upgrading, my immich-server has an error saying “Unable to connect to database”. Some Googling suggested that this might be a database corruption, so I attempted to restore from backup using the instructions online. These instructions don’t seem to change anything, however, because the docker start immich_postgres line seems not to do any database initialisation, since it says a database was already found. So I run the instructions again, this time including deleting the postgres directory. When I do this, however, the restore seems not to actually restore anything, since then Immich does indeed start, but it gives me the welcome page asking me to set up my first user. Assistance fixing the problem would be great, but separately. this is the second upgrade in a row that has somehow broken my Immich instance, and I am getting really confused and frustrated :\.

[maiden sun]

:wave: Hey @mystic tangle,

Thanks for reaching out to us. Please carefully read this message and follow the recommended actions. 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 ps -a docs
• Reverse Proxy: https://immich.app/docs/administration/reverse-proxy
• Code Formatting https://support.discord.com/hc/en-us/articles/210298617-Markdown-Text-101-Chat-Formatting-Bold-Italic-Underline#h_01GY0DAKGXDEHE263BCAYEGFJA

Checklist

I have...

1. :ballot_box_with_check: verified I'm on the latest release(note that mobile app releases may take some time).
2. :ballot_box_with_check: read applicable release notes.
3. :ballot_box_with_check: reviewed the FAQs for known issues.
4. :ballot_box_with_check: reviewed Github for known issues.
5. :ballot_box_with_check: tried accessing Immich via local ip (without a custom reverse proxy).
6. :blue_square: uploaded the relevant information (see below).
7. :ballot_box_with_check: 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)

Information

In order to be able to effectively help you, we need you to provide clear information to show what the problem is. The exact details needed vary per case, but here is a list of things to consider:

• Your docker-compose.yml and .env files.
• Logs from all the containers and their status (see above).
• All the troubleshooting steps you've tried so far.
• Any recent changes you've made to Immich or your system.
• Details about your system (both software/OS and hardware).
• Details about your storage (filesystems, type of disks, output of commands like fdisk -l and df -h).
• The version of the Immich server, mobile app, and other relevant pieces.
• Any other information that you think might be relevant.

Please paste files and logs with proper code formatting, and especially avoid blurry screenshots.
Without the right information we can't work out what the problem is. Help us help you ;)

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

[mystic tangle]

What files should I attach?

[burnt sundial]

So before you perform the database restore, you should either change the path of DB_DATA_LOCATION or remove the old folder there

Did you put the database file on a network drive by chance?

[mystic tangle]

Yes, so that was my second attempt, where then Immich started, but it didn’t look like anything actually got restored

[mystic tangle]

Nope, so presumably less complicated

[burnt sundial]

Where did you put the database?

[burnt sundial]

Can you use a new location, then run the restore command and post the result here?

[mystic tangle]

Is using a new location equivalent to deleting the old directory?

[burnt sundial]

Yes

Please put the database on the same OS drive as your container/VM

[mystic tangle]

Yeah, already is

[mystic tangle]

OK, so once I’ve done that, what result do you need to see? docker compose logs?

[burnt sundial]

Have you run the import command and what is the output?

[mystic tangle]

Oh, the import command itself is supposed to have an output? I don’t recall seeing any when I ran it earlier

This is the line with gunzip piping to sed?

[burnt sundial]

Can you provide the command you use? it is helpful to troubleshoot when I can see more information

[mystic tangle]

Oh, yeah, sure

[burnt sundial]

and are you doing this on Windows?

[mystic tangle]

Yeah, on Windows

I copied that line from the instructions, unless I copied it wrong 😅

[mystic tangle]

gunzip /absolute/path/to/file.sql.gz |sed "s/SELECT pg_catalog.set_config('search_path', '', false);/SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true);/g" | docker exec -i immich_postgres psql --username=postgres

(Obviously I put the actual correct path)

[strong anchor]

That command should generate a lot of output

Does windows even have gunzip ..?

[mystic tangle]

I’m running on Cygwin, so it does have gunzip

[mystic tangle]

It didn’t, though, so I guess this was the failure point

Is there a way for me to debug what’s going on?

[strong anchor]

You should probably try again and provide lots of screenshots. Also try running the individual piped commmands and see where the error is

[mystic tangle]

So I’ve split up the piped command (hopefully I did it correct), and on the last step, I get this:

$ docker exec -i immich_postgres psql --username=postgres immich-db-backup-1732932000009.sql
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432"
failed: FATAL:  database "immich-db-backup-1732932000009.sql" does not exist

Oh, wait, I see, I should probably feed it in by stdin

Give me a sec

OK, now I am getting a ton of output, which looks promising

Does that mean the piped command doesn’t work, or is this a Windows problem or something?

In any case, splitting up the command worked: Everything has been properly restored :)

Thanks!