immich-app/immich
1
0
Fork 0
mirror of https://github.com/immich-app/immich.git synced 2026-07-25 14:00:45 +03:00
Code Issues 783 Packages Projects Releases 22 Wiki Activity

PostgresError: could not create unique index "geodata_places_pkey" #7232

New Issue
Closed
opened 2026-02-05 12:53:04 +03:00 by OVERLORD · 2 comments
OVERLORD commented 2026-02-05 12:53:04 +03:00
Owner
Copy Link

Originally created by @dugravot6 on GitHub (Sep 15, 2025).

I have searched the existing issues, both open and closed, to make sure this is not a duplicate report.

  • Yes

The bug

Hi folks,
Upgrading from v1.135.3 to v1.137.0 or v1.140.1.
I encountered a problem similar to :

But not the same. Here is the error :

immich_postgres          | 2025-09-15 18:57:43.320 UTC [54] ERROR:  could not create unique index "geodata_places_pkey"                                    
immich_postgres          | 2025-09-15 18:57:43.320 UTC [54] DETAIL:  Key (id)=(2661633) is duplicated.                                                        
immich_postgres          | 2025-09-15 18:57:43.320 UTC [54] STATEMENT:  ALTER TABLE "geodata_places" ADD CONSTRAINT "geodata_places_pkey" PRIMARY KEY ("id"); 
immich_postgres          | 2025-09-15 18:57:43.321 UTC [51] ERROR:  could not create unique index "geodata_places_pkey"
immich_postgres          | 2025-09-15 18:57:43.321 UTC [51] DETAIL:  Key (id)=(2658440) is duplicated.
immich_postgres          | 2025-09-15 18:57:43.321 UTC [51] STATEMENT:  ALTER TABLE "geodata_places" ADD CONSTRAINT "geodata_places_pkey" PRIMARY KEY ("id"); 
immich_postgres          | 2025-09-15 18:57:43.325 UTC [1] LOG:  background worker "parallel worker" (PID 54) exited with exit code 1
immich_server            | Query failed : {
immich_server            |   durationMs: 22.676589000000604,
immich_server            |   error: PostgresError: could not create unique index "geodata_places_pkey"
immich_server            |       at ErrorResponse (/usr/src/app/server/node_modules/postgres/cjs/src/connection.js:794:26)
immich_server            |       at handle (/usr/src/app/server/node_modules/postgres/cjs/src/connection.js:480:6)
immich_server            |       at Socket.data (/usr/src/app/server/node_modules/postgres/cjs/src/connection.js:315:9)
immich_server            |       at Socket.emit (node:events:518:28)
immich_server            |       at addChunk (node:internal/streams/readable:561:12)
immich_server            |       at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
immich_server            |       at Readable.push (node:internal/streams/readable:392:5)
immich_server            |       at TCP.onStreamRead (node:internal/stream_base_commons:189:23) {
immich_server            |     severity_local: 'ERROR',
immich_server            |     severity: 'ERROR',
immich_server            |     code: '23505',
immich_server            |     detail: 'Key (id)=(2658440) is duplicated.',                                                                                   immich_server            |     schema_name: 'public',
immich_server            |     table_name: 'geodata_places',                                                                                                  immich_server            |     constraint_name: 'geodata_places_pkey',                                                                                        immich_server            |     file: 'tuplesort.c',
immich_server            |     line: '4297',
immich_server            |     routine: 'comparetup_index_btree'
immich_server            |   },
immich_server            |   sql: 'ALTER TABLE "geodata_places" ADD CONSTRAINT "geodata_places_pkey" PRIMARY KEY ("id");',

If i rollback to v1.135.3, immich server starts. So i a stuck with older version ...

Thanks for any help
Stephane.

The OS that Immich Server is running on

Debian/Docker

Version of Immich Server

v1.140.1

Version of Immich Mobile App

v1.142.0

Platform with the issue

  • Server
  • Web
  • Mobile

Device make and model

No response

Your docker-compose.yml content

services:
  immich-server:                                                                                                                                                  container_name: immich_server                                                                                                                                 image: ghcr.io/immich-app/immich-server:${IMMICH_VERSION:-release}
    networks:
      - my-network
    # extends:
    #   file: hwaccel.transcoding.yml
    #   service: cpu # set to one of [nvenc, quicksync, rkmpp, vaapi, vaapi-wsl] for accelerated transcoding
    volumes:
      # Do not edit the next line. If you want to change the media storage location on your system, edit the value of UPLOAD_LOCATION in the .env file
      - ${UPLOAD_LOCATION}:/usr/src/app/upload
      - /etc/localtime:/etc/localtime:ro
    env_file:                                                                                                                                                       - .env
    environment:
      PUID: 1000
      PGID: 1000
      VIRTUAL_HOST: photos.x.fr
      LETSENCRYPT_HOST: photos.x.fr
      LETSENCRYPT_EMAIL: stephane@x.fr
        #ports:
        #  - '2283:2283'
    depends_on:
      - redis
      - database
    restart: always
    healthcheck:
      disable: false

  immich-machine-learning:
    container_name: immich_machine_learning
    # For hardware acceleration, add one of -[armnn, cuda, rocm, openvino, rknn] to the image tag.
    # Example tag: ${IMMICH_VERSION:-release}-cuda
    networks:
      - my-network
    image: ghcr.io/immich-app/immich-machine-learning:${IMMICH_VERSION:-release}
    # extends: # uncomment this section for hardware acceleration - see https://immich.app/docs/features/ml-hardware-acceleration
    #   file: hwaccel.ml.yml
    #   service: cpu # set to one of [armnn, cuda, rocm, openvino, openvino-wsl, rknn] for accelerated inference - use the `-wsl` version for WSL2 where applicable                                                                                                                                                             volumes:                                                                                                                                                        - model-cache:/cache                                                                                                                                        env_file:                                                                                                                                                       - .env                                                                                                                                                      environment:                                                                                                                                                    PUID: 1000                                                                                                                                                    PGID: 1000                                                                                                                                                  restart: always                                                                                                                                               healthcheck:                                                                                                                                                    disable: false                                                                                                                                                                                                                                                                                                          redis:                                                                                                                                                          container_name: immich_redis                                                                                                                                  #image: docker.io/valkey/valkey:8-bookworm@sha256:42cba146593a5ea9a622002c1b7cba5da7be248650cbb64ecb9c6c33d29794b1                                            image: docker.io/valkey/valkey:8-bookworm@sha256:a137a2b60aca1a75130022d6bb96af423fefae4eb55faf395732db3544803280                                             environment:                                                                                                                                                    PUID: 1000                                                                                                                                                    PGID: 1000                                                                                                                                                  healthcheck:                                                                                                                                                    test: redis-cli ping || exit 1                                                                                                                              restart: always                                                                                                                                               networks:                                                                                                                                                       - my-network                                                                                                                                                                                                                                                                                                            database:                                                                                                                                                       container_name: immich_postgres                                                                                                                               #image: docker.io/tensorchord/pgvecto-rs:pg14-v0.2.0@sha256:739cdd626151ff1f796dc95a6591b55a714f341c737e27f045019ceabf8e8c52                                  image: ghcr.io/immich-app/postgres:14-vectorchord0.4.3-pgvectors0.2.0@sha256:32324a2f41df5de9efe1af166b7008c3f55646f8d0e00d9550c16c9822366b4a                 networks:                                                                                                                                                       - my-network                                                                                                                                                environment:                                                                                                                                                    PUID: 1000                                                                                                                                                    PGID: 1000                                                                                                                                                    POSTGRES_PASSWORD: ${DB_PASSWORD}                                                                                                                             POSTGRES_USER: ${DB_USERNAME}                                                                                                                                 POSTGRES_DB: ${DB_DATABASE_NAME}                                                                                                                              POSTGRES_INITDB_ARGS: '--data-checksums'                                                                                                                    volumes:                                                                                                                                                        # Do not edit the next line. If you want to change the database storage location on your system, edit the value of DB_DATA_LOCATION in the .env file          - ${DB_DATA_LOCATION}:/var/lib/postgresql/data                                                                                                              shm_size: 128mb                                                                                                                                               restart: always                                                                                                                                                                                                                                                                                                         volumes:                                                                                                                                                        model-cache:                                                                                                                                                                                                                                                                                                              networks:                                                                                                                                                       my-network:                                                                                                                                                     external: true

Your .env content

UPLOAD_LOCATION=./photos

# The location where your database files are stored. Network shares are not supported for the database
DB_DATA_LOCATION=./postgres

# To set a timezone, uncomment the next line and change Etc/UTC to a TZ identifier from this list: https://en.wikipedia.org/wiki/List_of_tz_database_time_zones#List
TZ=Etc/CET

# The Immich version to use. You can pin this to a specific version like "v1.71.0"
#IMMICH_VERSION=v1.135.3
IMMICH_VERSION=v1.137.0
#IMMICH_VERSION=v1.140.1
#IMMICH_VERSION=v1.137.3

# Connection secret for postgres. You should change it to a random password
# Please use only the characters `A-Za-z0-9`, without special characters or spaces
DB_PASSWORD=c

# The values below this line do not need to be changed
###################################################################################
DB_USERNAME=a
DB_DATABASE_NAME=b

Reproduction steps

...

Relevant log output

Additional information

No response

Originally created by @dugravot6 on GitHub (Sep 15, 2025). ### I have searched the existing issues, both open and closed, to make sure this is not a duplicate report. - [x] Yes ### The bug Hi folks, Upgrading from v1.135.3 to v1.137.0 or v1.140.1. I encountered a problem similar to : - https://github.com/immich-app/immich/discussions/20157 - https://github.com/immich-app/immich/issues/20167 But not the same. Here is the error : ``` immich_postgres | 2025-09-15 18:57:43.320 UTC [54] ERROR: could not create unique index "geodata_places_pkey" immich_postgres | 2025-09-15 18:57:43.320 UTC [54] DETAIL: Key (id)=(2661633) is duplicated. immich_postgres | 2025-09-15 18:57:43.320 UTC [54] STATEMENT: ALTER TABLE "geodata_places" ADD CONSTRAINT "geodata_places_pkey" PRIMARY KEY ("id"); immich_postgres | 2025-09-15 18:57:43.321 UTC [51] ERROR: could not create unique index "geodata_places_pkey" immich_postgres | 2025-09-15 18:57:43.321 UTC [51] DETAIL: Key (id)=(2658440) is duplicated. immich_postgres | 2025-09-15 18:57:43.321 UTC [51] STATEMENT: ALTER TABLE "geodata_places" ADD CONSTRAINT "geodata_places_pkey" PRIMARY KEY ("id"); immich_postgres | 2025-09-15 18:57:43.325 UTC [1] LOG: background worker "parallel worker" (PID 54) exited with exit code 1 immich_server | Query failed : { immich_server | durationMs: 22.676589000000604, immich_server | error: PostgresError: could not create unique index "geodata_places_pkey" immich_server | at ErrorResponse (/usr/src/app/server/node_modules/postgres/cjs/src/connection.js:794:26) immich_server | at handle (/usr/src/app/server/node_modules/postgres/cjs/src/connection.js:480:6) immich_server | at Socket.data (/usr/src/app/server/node_modules/postgres/cjs/src/connection.js:315:9) immich_server | at Socket.emit (node:events:518:28) immich_server | at addChunk (node:internal/streams/readable:561:12) immich_server | at readableAddChunkPushByteMode (node:internal/streams/readable:512:3) immich_server | at Readable.push (node:internal/streams/readable:392:5) immich_server | at TCP.onStreamRead (node:internal/stream_base_commons:189:23) { immich_server | severity_local: 'ERROR', immich_server | severity: 'ERROR', immich_server | code: '23505', immich_server | detail: 'Key (id)=(2658440) is duplicated.', immich_server | schema_name: 'public', immich_server | table_name: 'geodata_places', immich_server | constraint_name: 'geodata_places_pkey', immich_server | file: 'tuplesort.c', immich_server | line: '4297', immich_server | routine: 'comparetup_index_btree' immich_server | }, immich_server | sql: 'ALTER TABLE "geodata_places" ADD CONSTRAINT "geodata_places_pkey" PRIMARY KEY ("id");', ``` If i rollback to v1.135.3, immich server starts. So i a stuck with older version ... Thanks for any help Stephane. ### The OS that Immich Server is running on Debian/Docker ### Version of Immich Server v1.140.1 ### Version of Immich Mobile App v1.142.0 ### Platform with the issue - [x] Server - [x] Web - [ ] Mobile ### Device make and model _No response_ ### Your docker-compose.yml content ```YAML services: immich-server: container_name: immich_server image: ghcr.io/immich-app/immich-server:${IMMICH_VERSION:-release} networks: - my-network # extends: # file: hwaccel.transcoding.yml # service: cpu # set to one of [nvenc, quicksync, rkmpp, vaapi, vaapi-wsl] for accelerated transcoding volumes: # Do not edit the next line. If you want to change the media storage location on your system, edit the value of UPLOAD_LOCATION in the .env file - ${UPLOAD_LOCATION}:/usr/src/app/upload - /etc/localtime:/etc/localtime:ro env_file: - .env environment: PUID: 1000 PGID: 1000 VIRTUAL_HOST: photos.x.fr LETSENCRYPT_HOST: photos.x.fr LETSENCRYPT_EMAIL: stephane@x.fr #ports: # - '2283:2283' depends_on: - redis - database restart: always healthcheck: disable: false immich-machine-learning: container_name: immich_machine_learning # For hardware acceleration, add one of -[armnn, cuda, rocm, openvino, rknn] to the image tag. # Example tag: ${IMMICH_VERSION:-release}-cuda networks: - my-network image: ghcr.io/immich-app/immich-machine-learning:${IMMICH_VERSION:-release} # extends: # uncomment this section for hardware acceleration - see https://immich.app/docs/features/ml-hardware-acceleration # file: hwaccel.ml.yml # service: cpu # set to one of [armnn, cuda, rocm, openvino, openvino-wsl, rknn] for accelerated inference - use the `-wsl` version for WSL2 where applicable volumes: - model-cache:/cache env_file: - .env environment: PUID: 1000 PGID: 1000 restart: always healthcheck: disable: false redis: container_name: immich_redis #image: docker.io/valkey/valkey:8-bookworm@sha256:42cba146593a5ea9a622002c1b7cba5da7be248650cbb64ecb9c6c33d29794b1 image: docker.io/valkey/valkey:8-bookworm@sha256:a137a2b60aca1a75130022d6bb96af423fefae4eb55faf395732db3544803280 environment: PUID: 1000 PGID: 1000 healthcheck: test: redis-cli ping || exit 1 restart: always networks: - my-network database: container_name: immich_postgres #image: docker.io/tensorchord/pgvecto-rs:pg14-v0.2.0@sha256:739cdd626151ff1f796dc95a6591b55a714f341c737e27f045019ceabf8e8c52 image: ghcr.io/immich-app/postgres:14-vectorchord0.4.3-pgvectors0.2.0@sha256:32324a2f41df5de9efe1af166b7008c3f55646f8d0e00d9550c16c9822366b4a networks: - my-network environment: PUID: 1000 PGID: 1000 POSTGRES_PASSWORD: ${DB_PASSWORD} POSTGRES_USER: ${DB_USERNAME} POSTGRES_DB: ${DB_DATABASE_NAME} POSTGRES_INITDB_ARGS: '--data-checksums' volumes: # Do not edit the next line. If you want to change the database storage location on your system, edit the value of DB_DATA_LOCATION in the .env file - ${DB_DATA_LOCATION}:/var/lib/postgresql/data shm_size: 128mb restart: always volumes: model-cache: networks: my-network: external: true ``` ### Your .env content ```Shell UPLOAD_LOCATION=./photos # The location where your database files are stored. Network shares are not supported for the database DB_DATA_LOCATION=./postgres # To set a timezone, uncomment the next line and change Etc/UTC to a TZ identifier from this list: https://en.wikipedia.org/wiki/List_of_tz_database_time_zones#List TZ=Etc/CET # The Immich version to use. You can pin this to a specific version like "v1.71.0" #IMMICH_VERSION=v1.135.3 IMMICH_VERSION=v1.137.0 #IMMICH_VERSION=v1.140.1 #IMMICH_VERSION=v1.137.3 # Connection secret for postgres. You should change it to a random password # Please use only the characters `A-Za-z0-9`, without special characters or spaces DB_PASSWORD=c # The values below this line do not need to be changed ################################################################################### DB_USERNAME=a DB_DATABASE_NAME=b ``` ### Reproduction steps 1. 2. 3. ... ### Relevant log output ```shell ``` ### Additional information _No response_
OVERLORD commented 2026-02-05 12:53:05 +03:00
Author
Owner
Copy Link

@dugravot6 commented on GitHub (Sep 15, 2025):

BTW, there is no constraint to table :

immich=# \d+ geodata_places;
                                                  Table "public.geodata_places"
      Column      |          Type          | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
------------------+------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 id               | integer                |           | not null |         | plain    |             |              |
 name             | character varying(200) |           | not null |         | extended |             |              |
 longitude        | double precision       |           | not null |         | plain    |             |              |
 latitude         | double precision       |           | not null |         | plain    |             |              |
 countryCode      | character(2)           |           | not null |         | extended |             |              |
 admin1Code       | character varying(20)  |           |          |         | extended |             |              |
 admin2Code       | character varying(80)  |           |          |         | extended |             |              |
 modificationDate | date                   |           | not null |         | plain    |             |              |
 admin1Name       | character varying      |           |          |         | extended |             |              |
 admin2Name       | character varying      |           |          |         | extended |             |              |
 alternateNames   | character varying      |           |          |         | extended |             |              |
Access method: heap
@dugravot6 commented on GitHub (Sep 15, 2025): BTW, there is no constraint to table : ``` immich=# \d+ geodata_places; Table "public.geodata_places" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description ------------------+------------------------+-----------+----------+---------+----------+-------------+--------------+------------- id | integer | | not null | | plain | | | name | character varying(200) | | not null | | extended | | | longitude | double precision | | not null | | plain | | | latitude | double precision | | not null | | plain | | | countryCode | character(2) | | not null | | extended | | | admin1Code | character varying(20) | | | | extended | | | admin2Code | character varying(80) | | | | extended | | | modificationDate | date | | not null | | plain | | | admin1Name | character varying | | | | extended | | | admin2Name | character varying | | | | extended | | | alternateNames | character varying | | | | extended | | | Access method: heap ```
OVERLORD commented 2026-02-05 12:53:07 +03:00
Author
Owner
Copy Link

@dugravot6 commented on GitHub (Sep 15, 2025):

Just find how to remove duplicate keys :

DELETE FROM geodata_places a
USING geodata_places b
WHERE a.ctid < b.ctid
  AND a.id = b.id;

And migration is ok now.
There was 45000 duplicates !

@dugravot6 commented on GitHub (Sep 15, 2025): Just find how to remove duplicate keys : ``` DELETE FROM geodata_places a USING geodata_places b WHERE a.ctid < b.ctid AND a.id = b.id; ``` And migration is ok now. There was 45000 duplicates !
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
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#7232
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?