immich [BUG] Postgres database failed to start after 1.63.0 update

The bug

I updated my docker containers this morning to the latest versions and Immich fails to full start (get a 500 error). There are errors in the logs that it cannot connect to the database. So I look at the database logs and see the below.

2023-06-24 09:52:01.690 UTC [2058] CONTEXT: parallel worker 2023-06-24 09:52:01.690 UTC [2058] STATEMENT: ALTER TABLE "assets" ADD CONSTRAINT "UQ_4ed4f8052685ff5b1e7ca1058ba" UNIQUE ("originalPath") 2023-06-24 09:52:01.694 UTC [1] LOG: background worker "parallel worker" (PID 2059) exited with exit code 1 2023-06-24 09:52:04.051 UTC [2060] ERROR: could not create unique index "UQ_4ed4f8052685ff5b1e7ca1058ba" 2023-06-24 09:52:04.051 UTC [2060] DETAIL: Key ("originalPath")=(upload/b31e780c-fef6-448f-8560-a4fe2ad6b473/2021/2021-10-13/FullSizeRender+2.jpg) is duplicated. 2023-06-24 09:52:04.051 UTC [2060] STATEMENT: ALTER TABLE "assets" ADD CONSTRAINT "UQ_4ed4f8052685ff5b1e7ca1058ba" UNIQUE ("originalPath") 2023-06-24 09:52:04.051 UTC [2061] FATAL: terminating background worker "parallel worker" due to administrator command 2023-06-24 09:52:04.051 UTC [2061] STATEMENT: ALTER TABLE "assets" ADD CONSTRAINT "UQ_4ed4f8052685ff5b1e7ca1058ba" UNIQUE ("originalPath") 2023-06-24 09:52:04.054 UTC [1] LOG: background worker "parallel worker" (PID 2061) exited with exit code 1 2023-06-24 09:52:04.741 UTC [2063] ERROR: could not create unique index "UQ_4ed4f8052685ff5b1e7ca1058ba" 2023-06-24 09:52:04.741 UTC [2063] DETAIL: Key ("originalPath")=(upload/b31e780c-fef6-448f-8560-a4fe2ad6b473/2022/2022-03-03/FullSizeRender.jpg) is duplicated. 2023-06-24 09:52:04.741 UTC [2063] STATEMENT: ALTER TABLE "assets" ADD CONSTRAINT "UQ_4ed4f8052685ff5b1e7ca1058ba" UNIQUE ("originalPath") 2023-06-24 09:52:04.741 UTC [2062] ERROR: could not create unique index "UQ_4ed4f8052685ff5b1e7ca1058ba" 2023-06-24 09:52:04.741 UTC [2062] DETAIL: Key ("originalPath")=(upload/b31e780c-fef6-448f-8560-a4fe2ad6b473/2022/2022-03-03/FullSizeRender.jpg) is duplicated. 2023-06-24 09:52:04.741 UTC [2062] CONTEXT: parallel worker 2023-06-24 09:52:04.741 UTC [2062] STATEMENT: ALTER TABLE "assets" ADD CONSTRAINT "UQ_4ed4f8052685ff5b1e7ca1058ba" UNIQUE ("originalPath") 2023-06-24 09:52:04.743 UTC [1] LOG: background worker "parallel worker" (PID 2063) exited with exit code 1 2023-06-24 09:52:07.141 UTC [2065] ERROR: could not create unique index "UQ_4ed4f8052685ff5b1e7ca1058ba" 2023-06-24 09:52:07.141 UTC [2065] DETAIL: Key ("originalPath")=(upload/b31e780c-fef6-448f-8560-a4fe2ad6b473/2021/2021-10-13/FullSizeRender.jpg) is duplicated. 2023-06-24 09:52:07.141 UTC [2065] STATEMENT: ALTER TABLE "assets" ADD CONSTRAINT "UQ_4ed4f8052685ff5b1e7ca1058ba" UNIQUE ("originalPath") 2023-06-24 09:52:07.141 UTC [2066] FATAL: terminating background worker "parallel worker" due to administrator command 2023-06-24 09:52:07.141 UTC [2066] STATEMENT: ALTER TABLE "assets" ADD CONSTRAINT "UQ_4ed4f8052685ff5b1e7ca1058ba" UNIQUE ("originalPath") 2023-06-24 09:52:07.145 UTC [1] LOG: background worker "parallel worker" (PID 2066) exited with exit code 1 2023-06-24 09:52:07.799 UTC [2067] ERROR: could not create unique index "UQ_4ed4f8052685ff5b1e7ca1058ba" 2023-06-24 09:52:07.799 UTC [2067] DETAIL: Key ("originalPath")=(upload/b31e780c-fef6-448f-8560-a4fe2ad6b473/2022/2022-08-03/FullSizeRender.jpg) is duplicated. 2023-06-24 09:52:07.799 UTC [2067] STATEMENT: ALTER TABLE "assets" ADD CONSTRAINT "UQ_4ed4f8052685ff5b1e7ca1058ba" UNIQUE ("originalPath")

The OS that Immich Server is running on

UnRaid

Version of Immich Server

v1.6.0

Version of Immich Mobile App

latest

Platform with the issue

[X] Server
[ ] Web
[ ] Mobile

Your docker-compose.yml content

version: "3.8"
services:
  immich-server:
    image: altran1502/immich-server:release
    entrypoint: ["/bin/sh", "./start-server.sh"]
    volumes:
      - ${UPLOAD_LOCATION}:/usr/src/app/upload
    env_file:
      - .env
    environment:
      - NODE_ENV=production
    depends_on:
      - redis
      - database
    restart: always

  immich-microservices:
    image: altran1502/immich-server:release
    entrypoint: ["/bin/sh", "./start-microservices.sh"]
    volumes:
      - ${UPLOAD_LOCATION}:/usr/src/app/upload
    env_file:
      - .env
    environment:
      - NODE_ENV=production
    depends_on:
      - redis
      - database
    restart: always

  immich-machine-learning:
    image: altran1502/immich-machine-learning:release
    entrypoint: ["/bin/sh", "./entrypoint.sh"]
    volumes:
      - ${UPLOAD_LOCATION}:/usr/src/app/upload
    env_file:
      - .env
    environment:
      - NODE_ENV=production
    depends_on:
      - database
    restart: always

  immich-web:
    image: altran1502/immich-web:release
    entrypoint: ["/bin/sh", "./entrypoint.sh"]
    env_file:
      - .env
    environment:
      # Rename these values for svelte public interface
      - PUBLIC_IMMICH_SERVER_URL=${IMMICH_SERVER_URL}
    restart: always

  typesense:
    container_name: immich_typesense
    image: typesense/typesense:0.24.0
    environment:
      - TYPESENSE_API_KEY=${TYPESENSE_API_KEY}
      - TYPESENSE_DATA_DIR=/data
    logging:
      driver: none
    volumes:
      - tsdata:/data
    restart: always

  redis:
    container_name: immich_redis
    image: redis:6.2
    restart: always

  database:
    container_name: immich_postgres
    image: postgres:14
    env_file:
      - .env
    environment:
      POSTGRES_PASSWORD: ${DB_PASSWORD}
      POSTGRES_USER: ${DB_USERNAME}
      POSTGRES_DB: ${DB_DATABASE_NAME}
      PG_DATA: /var/lib/postgresql/data
    volumes:
      - pgdata:/var/lib/postgresql/data
    restart: always

  immich-proxy:
    container_name: immich_proxy
    image: altran1502/immich-proxy:release
    environment:
      # Make sure these values get passed through from the env file
      - IMMICH_SERVER_URL
      - IMMICH_WEB_URL
    ports:
      - 2283:8080
    logging:
      driver: none
    depends_on:
      - immich-server
    restart: always

volumes:
  pgdata:
  tsdata:

Your .env content

###################################################################################
# Database
###################################################################################

DB_HOSTNAME=immich_postgres
DB_USERNAME=postgres
DB_PASSWORD=postgres
DB_DATABASE_NAME=immich

# Optional Database settings:
# DB_PORT=5432

###################################################################################
# Redis
###################################################################################

REDIS_HOSTNAME=immich_redis

# Optional Redis settings:
# REDIS_PORT=6379
# REDIS_DBINDEX=0
# REDIS_PASSWORD=
# REDIS_SOCKET=

###################################################################################
# Upload File Location
#
# This is the location where uploaded files are stored.
###################################################################################

UPLOAD_LOCATION=/mnt/user/Photo Upload

###################################################################################
# Typesense
###################################################################################
TYPESENSE_API_KEY=
# TYPESENSE_ENABLED=false

###################################################################################
# JWT SECRET
#
# This JWT_SECRET is used to sign the authentication keys for user login
# You should set it to a long randomly generated value
# You can use this command to generate one: openssl rand -base64 128
###################################################################################

JWT_SECRET=bc752726-8372-11ed-a1eb-0242ac120002

###################################################################################
# Reverse Geocoding
#
# Reverse geocoding is done locally which has a small impact on memory usage
# This memory usage can be altered by changing the REVERSE_GEOCODING_PRECISION variable
# This ranges from 0-3 with 3 being the most precise
# 3 - Cities > 500 population: ~200MB RAM
# 2 - Cities > 1000 population: ~150MB RAM
# 1 - Cities > 5000 population: ~80MB RAM
# 0 - Cities > 15000 population: ~40MB RAM
####################################################################################

# DISABLE_REVERSE_GEOCODING=false
# REVERSE_GEOCODING_PRECISION=3

####################################################################################
# WEB - Optional
#
# Custom message on the login page, should be written in HTML form.
# For example:
# PUBLIC_LOGIN_PAGE_MESSAGE="This is a demo instance of Immich.<br><br>Email: <i>[email protected]</i><br>Password: <i>demo</i>"
####################################################################################

PUBLIC_LOGIN_PAGE_MESSAGE=

####################################################################################
# Alternative Service Addresses - Optional
#
# This is an advanced feature for users who may be running their immich services on different hosts.
# It will not change which address or port that services bind to within their containers, but it will change where other services look for their peers.
# Note: immich-microservices is bound to 3002, but no references are made
####################################################################################

IMMICH_WEB_URL=http://immich-web:3000
IMMICH_SERVER_URL=http://immich-server:3001
IMMICH_MACHINE_LEARNING_URL=http://immich-machine-learning:3003

Reproduction steps

1. update immich
2. start container

Additional information

No response

Jun 24 '23 09:06 brent7320

I am also seeing some issues with the database after upgrading, looks like a bad migration?

[Nest] 8  - 06/24/2023, 10:16:37 AM   ERROR [TypeOrmModule] Unable to connect to the database. Retrying (1)...
QueryFailedError: column "isReadOnly" of relation "assets" already exists
    at PostgresQueryRunner.query (/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:211:19)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async ImportAsset1686584273471.up (/usr/src/app/dist/infra/migrations/1686584273471-ImportAsset.js:9:9)
    at async MigrationExecutor.executePendingMigrations (/usr/src/app/node_modules/typeorm/migration/MigrationExecutor.js:225:17)
    at async DataSource.runMigrations (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:260:35)
    at async DataSource.initialize (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:148:17)

Jun 24 '23 10:06 Foritus

Same problem here.

2023-06-24 10:44:00.139 UTC [17987] STATEMENT:  ALTER TABLE "assets" ADD CONSTRAINT "UQ_4ed4f8052685ff5b1e7ca1058ba" UNIQUE ("originalPath")
2023-06-24 10:44:00.172 UTC [17988] ERROR:  could not create unique index "UQ_4ed4f8052685ff5b1e7ca1058ba"
2023-06-24 10:44:00.172 UTC [17988] DETAIL:  Key ("originalPath")=(upload/library/eedbf3c9-f353-45fe-87c6-9d9e9aa22540/2023/2023-03-02/FullSizeRender+1.jpg) is duplicated.

Jun 24 '23 10:06 firasdib

Ok, here is the fix

Leave everything running
Attach to the Postgres database container with the following command (assuming you are using the stock .env values for the database)

docker exec -it immich_postgres bash

Connect to the immich's database

psql -U postgres -d immich

Check for files that have a duplicate original path

select a."originalPath", count(a.id)
from assets a
group by a."originalPath"
having count(a.id) > 1;

The results are the duplicated original paths that must be removed.

Here, you can go find those files and pull them aside so we can reupload them after removing them from the database.
Now we will delete the bad database entries

delete from assets a where a."originalPath" = '<the-path-from-query-of-step-4>';

Example from user @firasdib who has the path upload/library/eedbf3c9-f353-45fe-87c6-9d9e9aa22540/2023/2023-03-02/FullSizeRender+1.jpg reported as duplicated

delete from assets a where a."originalPath" = 'upload/library/eedbf3c9-f353-45fe-87c6-9d9e9aa22540/2023/2023-03-02/FullSizeRender+1.jpg';

Now, can you restart the Immich stack, it should work.
Reupload those files

Jun 24 '23 11:06 alextran1502

It's not always the case. My instance works fine after the update. Will there be hotfix version in order to prevent other users having same issue?

Jun 24 '23 11:06 Pheggas

@Pheggas this only happens in some instances and not all. There is no good way to hotfix this besides manually performing the steps outlined above.

Jun 24 '23 11:06 alextran1502

I can confirm the steps outlined by @alextran1502 fixed the issue for me. Hopefully it's a one time thing, once the unique index is in place I assume it will deal with future problems.

Jun 24 '23 11:06 firasdib

This resolved it for me as well, thank you for the quick response!

Jun 24 '23 11:06 brent7320

I can confirm the steps outlined by @alextran1502 fixed the issue for me. Hopefully it's a one time thing, once the unique index is in place I assume it will deal with future problems.

Yes, this is a one-time thing.

Jun 24 '23 12:06 alextran1502

Worked for me in K8s as well. Thanks!

Jun 24 '23 18:06 muddyland

Hi guys,

When I run psql -U postgres -d immich I get:

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "postgres" does not exist

The file is there:

root@7f87e1cffabf:/# ls -alh /var/run/postgresql/
total 0
drwxrwsr-t. 1 postgres postgres 27 Jun 24 18:49 .
drwxr-xr-x. 1 root     root     24 Jun 14 21:22 ..
srwxrwxrwx. 1 postgres postgres  0 Jun 24 18:25 .s.PGSQL.5432

Jun 24 '23 18:06 romeolazar

@romeolazar you probably edited POSTGRES_USER env variable or better to say DB_USERNAME in .env file, right? the -U flag specifying user that you want to log in as. It writes user postgres does not exist which means the username is different than what Alex entered in your case.

Jun 24 '23 19:06 Pheggas

@romeolazar you probably edited POSTGRES_USER env variable or better to say DB_USERNAME in .env file, right? the -U flag specifying user that you want to log in as. It writes user postgres does not exist which means the username is different than what Alex entered in your case.

Thank you. Indeed, I was not paying attention.

Jun 24 '23 19:06 romeolazar

@alextran1502 thanks for the fix, that worked for me. Issue happened last night but was too tired to write up a report, was coming to do it and happy to see there was already a fix.

Thanks again

Jun 24 '23 23:06 SiskoUrso

Closing this issue as a fix has been provided.

Jun 25 '23 19:06 bo0tzz

This is the query that worked for me after I individually pulled aside the issue files.

Pull the files aside that are found in the first query:

select a."originalPath", count(a.id)
from assets a
group by a."originalPath"
having count(a.id) > 1;

Then delete from assets where those were found (destructive query, so please make sure you pulled those files aside):

DELETE FROM assets a
WHERE a."originalPath" IN (
  SELECT "originalPath"
  FROM (
    SELECT a1."originalPath"
    FROM assets a1
    GROUP BY a1."originalPath"
    HAVING COUNT(a1.id) > 1
  ) as subquery
);

I had something like 50+ entries so it took some time to pull the files aside, but I wrote that second query to be a little bit faster/quicker than having to do each one manually.

Jun 25 '23 21:06 perfectra1n

Hello!

Firts of all i am a new guy about it (like a noob). I would like to try the immich, but i have the same problem: Error: getaddrinfo ENOTFOUND IMMICH at GetAddrInfoReqWrap.onlookupall [as oncomplete] (node:dns:118:26) [Nest] 7 - 04/14/2024, 4:49:00 PM ERROR [TypeOrmModule] Unable to connect to the database. Retrying (6)...

I found this topic, and i stuck it because i don't know what is my "originalPath". How can i find it? Where can i chek it?

Apr 14 '24 16:04 viger90

immich immich copied to clipboard

[BUG] Postgres database failed to start after 1.63.0 update

The bug

The OS that Immich Server is running on

Version of Immich Server

Version of Immich Mobile App

Platform with the issue

Your docker-compose.yml content

Your .env content

Reproduction steps

Additional information

immich
immich copied to clipboard