immich-app/immich
1
0
Fork 0
mirror of https://github.com/immich-app/immich.git synced 2026-03-01 11:20:12 +03:00
Code Issues 783 Packages Projects Releases 12 Wiki Activity

[BUG] Postgres database failed to start after 1.63.0 update #993

New Issue
Closed
opened 2026-02-04 23:48:52 +03:00 by OVERLORD · 16 comments
OVERLORD commented 2026-02-04 23:48:52 +03:00
Owner
Copy Link

Originally created by @brent7320 on GitHub (Jun 24, 2023).

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

  • 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>demo@demo.de</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

Originally created by @brent7320 on GitHub (Jun 24, 2023). ### 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 ```YAML 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 ```Shell ################################################################################### # 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>demo@demo.de</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 ```bash 1. update immich 2. start container ``` ### Additional information _No response_
OVERLORD added the 🗄️server label 2026-02-04 23:48:52 +03:00
OVERLORD commented 2026-02-04 23:48:57 +03:00
Author
Owner
Copy Link

@Foritus commented on GitHub (Jun 24, 2023):

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)
@Foritus commented on GitHub (Jun 24, 2023): 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) ```
OVERLORD commented 2026-02-04 23:49:02 +03:00
Author
Owner
Copy Link

@firasdib commented on GitHub (Jun 24, 2023):

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.
@firasdib commented on GitHub (Jun 24, 2023): 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. ```
OVERLORD commented 2026-02-04 23:49:07 +03:00
Author
Owner
Copy Link

@alextran1502 commented on GitHub (Jun 24, 2023):

Ok, here is the fix

  1. Leave everything running
  2. 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
  1. Connect to the immich's database
psql -U postgres -d immich
  1. 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.

  1. Here, you can go find those files and pull them aside so we can reupload them after removing them from the database.

  2. 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';
  1. Now, can you restart the Immich stack, it should work.
  2. Reupload those files
@alextran1502 commented on GitHub (Jun 24, 2023): Ok, here is the fix 1. Leave everything running 2. Attach to the Postgres database container with the following command (assuming you are using the stock `.env` values for the database) ```bash docker exec -it immich_postgres bash ``` 3. Connect to the immich's database ```bash psql -U postgres -d immich ``` 4. Check for files that have a duplicate original path ```bash 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. 5. Here, you can go find those files and pull them aside so we can reupload them after removing them from the database. 6. Now we will delete the bad database entries ```bash 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 ```bash delete from assets a where a."originalPath" = 'upload/library/eedbf3c9-f353-45fe-87c6-9d9e9aa22540/2023/2023-03-02/FullSizeRender+1.jpg'; ``` 7. Now, can you restart the Immich stack, it should work. 8. Reupload those files
OVERLORD commented 2026-02-04 23:49:10 +03:00
Author
Owner
Copy Link

@Pheggas commented on GitHub (Jun 24, 2023):

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?

@Pheggas commented on GitHub (Jun 24, 2023): 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?
OVERLORD commented 2026-02-04 23:49:14 +03:00
Author
Owner
Copy Link

@alextran1502 commented on GitHub (Jun 24, 2023):

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

@alextran1502 commented on GitHub (Jun 24, 2023): @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.
OVERLORD commented 2026-02-04 23:49:18 +03:00
Author
Owner
Copy Link

@firasdib commented on GitHub (Jun 24, 2023):

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.

@firasdib commented on GitHub (Jun 24, 2023): 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.
OVERLORD commented 2026-02-04 23:49:26 +03:00
Author
Owner
Copy Link

@brent7320 commented on GitHub (Jun 24, 2023):

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

@brent7320 commented on GitHub (Jun 24, 2023): This resolved it for me as well, thank you for the quick response!
OVERLORD commented 2026-02-04 23:49:29 +03:00
Author
Owner
Copy Link

@alextran1502 commented on GitHub (Jun 24, 2023):

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.

@alextran1502 commented on GitHub (Jun 24, 2023): > 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.
OVERLORD commented 2026-02-04 23:49:37 +03:00
Author
Owner
Copy Link

@muddyland commented on GitHub (Jun 24, 2023):

Worked for me in K8s as well. Thanks!

@muddyland commented on GitHub (Jun 24, 2023): Worked for me in K8s as well. Thanks!
OVERLORD commented 2026-02-04 23:49:42 +03:00
Author
Owner
Copy Link

@romeolazar commented on GitHub (Jun 24, 2023):

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
@romeolazar commented on GitHub (Jun 24, 2023): 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 ```
OVERLORD commented 2026-02-04 23:49:45 +03:00
Author
Owner
Copy Link

@Pheggas commented on GitHub (Jun 24, 2023):

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

@Pheggas commented on GitHub (Jun 24, 2023): @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.
OVERLORD commented 2026-02-04 23:49:55 +03:00
Author
Owner
Copy Link

@romeolazar commented on GitHub (Jun 24, 2023):

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

@romeolazar commented on GitHub (Jun 24, 2023): > @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.
OVERLORD commented 2026-02-04 23:50:00 +03:00
Author
Owner
Copy Link

@SiskoUrso commented on GitHub (Jun 25, 2023):

@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

@SiskoUrso commented on GitHub (Jun 25, 2023): @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
OVERLORD commented 2026-02-04 23:50:04 +03:00
Author
Owner
Copy Link

@bo0tzz commented on GitHub (Jun 25, 2023):

Closing this issue as a fix has been provided.

@bo0tzz commented on GitHub (Jun 25, 2023): Closing this issue as a fix has been provided.
OVERLORD commented 2026-02-04 23:50:10 +03:00
Author
Owner
Copy Link

@perfectra1n commented on GitHub (Jun 26, 2023):

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

  1. 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;
  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.

@perfectra1n commented on GitHub (Jun 26, 2023): This is the query that worked for me after I individually pulled aside the issue files. 1. Pull the files aside that are found in the first query: ```sql select a."originalPath", count(a.id) from assets a group by a."originalPath" having count(a.id) > 1; ``` 2. Then delete from `assets` where those were found (**destructive query, so please make sure you pulled those files aside**): ```sql 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.
OVERLORD commented 2026-02-04 23:50:17 +03:00
Author
Owner
Copy Link

@viger90 commented on GitHub (Apr 14, 2024):

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?

@viger90 commented on GitHub (Apr 14, 2024): 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?
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
accessibility
changelog:enhancement
changelog:security
changelog:skip
changelog:translation
cli
date-time
dependencies
documentation
external-library
format
good first issue
mobile-beta
mobile-beta
mobile-beta
needs-answer
nice to have
pull-request
Mirrored from GitHub Pull Request
 sharing
tech-debt
📱mobile
🖥️web
🗄️server
🧠machine-learning
No Label 🗄️server
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
OVERLORD
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: immich-app/immich#993
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?