after update 1.135.3 to 1.136.0 error of microservices worker error: PostgresError: multiple primary keys for table "geodata_places" are not allowed #20157

gedias · 2025-07-24T19:29:30Z

gedias
Jul 24, 2025

i received this error:
Initializing Immich v1.136.0
Detected CPU Cores: 4
Starting api worker
Starting microservices worker
[Nest] 7 - 07/24/2025, 7:24:24 PM LOG [Microservices:EventRepository] Initialized websocket server
[Nest] 18 - 07/24/2025, 7:24:25 PM LOG [Api:EventRepository] Initialized websocket server
[Nest] 7 - 07/24/2025, 7:24:26 PM LOG [Microservices:DatabaseRepository] targetLists=1, current=1 for clip_index of 112306 rows
[Nest] 7 - 07/24/2025, 7:24:26 PM LOG [Microservices:DatabaseRepository] targetLists=256, current=256 for face_index of 181495 rows
[Nest] 7 - 07/24/2025, 7:24:26 PM LOG [Microservices:DatabaseRepository] Running migrations, this may take a while
Query failed : {
durationMs: 5.861652999999933,
error: PostgresError: multiple primary keys for table "geodata_places" are not allowed
at ErrorResponse (/usr/src/app/server/node_modules/postgres/cjs/src/connection.js:794:26)
at handle (/usr/src/app/server/node_modules/postgres/cjs/src/connection.js:480:6)
at Socket.data (/usr/src/app/server/node_modules/postgres/cjs/src/connection.js:315:9)
at Socket.emit (node:events:518:28)
at addChunk (node:internal/streams/readable:561:12)
at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
at Readable.push (node:internal/streams/readable:392:5)
at TCP.onStreamRead (node:internal/stream_base_commons:189:23) {
severity_local: 'ERROR',
severity: 'ERROR',
code: '42P16',
file: 'index.c', line: '221',
routine: 'index_check_primary_key'
},
sql: 'ALTER TABLE "geodata_places" ADD CONSTRAINT "geodata_places_pkey" PRIMARY KEY ("id");',
params: []
}

alextran1502 · 2025-07-24T19:35:41Z

alextran1502
Jul 24, 2025
Maintainer

cc @zackpollard

1 reply

zackpollard Jul 24, 2025
Maintainer

🤔

gedias · 2025-07-24T19:43:57Z

gedias
Jul 24, 2025
Author

0 replies

gedias · 2025-07-24T20:03:42Z

gedias
Jul 24, 2025
Author

i execute ALTER TABLE geodata_places DROP CONSTRAINT geodata_places_pkey; apparently is continuing

0 replies

skatsubo · 2025-07-25T00:27:05Z

skatsubo
Jul 25, 2025
Collaborator

Indexes:
"geodata_places_pkey1" PRIMARY KEY, btree (id) WITH (fillfactor='100')

That's strange. geodata_places_pkey1 (with 1 at the end) is not defined anywhere in code. How did it appear?..

You can try renaming it and see if it helps:

ALTER TABLE geodata_places RENAME CONSTRAINT geodata_places_pkey1 TO geodata_places_pkey;

0 replies

sonyberry · 2025-07-25T00:43:33Z

sonyberry
Jul 25, 2025

Zip

0 replies

gedias · 2025-07-25T01:39:19Z

gedias
Jul 25, 2025
Author

I believe my database is having problems, and I'll have to reset it.
I ran a VACUUM FULL and it shows errors.
I strongly believe it's a coincidence that I changed the version and the database is having problems.
Thank you all for your help.
I'll try rescanning to see if the photos that already existed return to Immich.
Once again, thank you all so much.

0 replies

gedias · 2025-07-25T02:10:31Z

gedias
Jul 25, 2025
Author

I recovered a backup from yesterday, it gave the same error, however, when I ran the command that @skatsubo recommended it worked.

ALTER TABLE geodata_places RENAME CONSTRAINT geodata_places_pkey1 TO geodata_places_pkey;

0 replies

1317 · 2025-07-25T03:59:01Z

1317
Jul 25, 2025

I don't think this is a coincidence, as I encontered the same issue when upgrading immich from v1.134.0 to v1.136.0. The immich_server just keeps restarting with a interval around 5 seconds. The log from immich_server is attached below. My temporary solution is manually specifying the previous version, i.e. v1.135.3, and everything goes well.

2025-07-25T03:44:52.979940702Z Initializing Immich v1.136.0
2025-07-25T03:44:52.991937351Z Detected CPU Cores: 4
2025-07-25T03:44:57.138691703Z Starting api worker
2025-07-25T03:44:57.143885317Z Starting microservices worker
2025-07-25T03:44:59.463731263Z [Nest] 7  - 07/25/2025, 11:44:59 AM     LOG [Microservices:EventRepository] Initialized websocket server
2025-07-25T03:44:59.601917797Z [Nest] 7  - 07/25/2025, 11:44:59 AM     LOG [Microservices:DatabaseRepository] targetLists=1, current=1 for clip_index of 18833 rows
2025-07-25T03:44:59.605240584Z [Nest] 7  - 07/25/2025, 11:44:59 AM     LOG [Microservices:DatabaseRepository] targetLists=1, current=1 for face_index of 6285 rows
2025-07-25T03:44:59.606333516Z [Nest] 7  - 07/25/2025, 11:44:59 AM     LOG [Microservices:DatabaseRepository] Running migrations, this may take a while
2025-07-25T03:44:59.742880028Z [Nest] 18  - 07/25/2025, 11:44:59 AM     LOG [Api:EventRepository] Initialized websocket server
2025-07-25T03:45:00.239260583Z Query failed : {
2025-07-25T03:45:00.239287451Z   durationMs: 4.8117059999995035,
2025-07-25T03:45:00.239290456Z   error: PostgresError: multiple primary keys for table "geodata_places" are not allowed
2025-07-25T03:45:00.239292646Z       at ErrorResponse (/usr/src/app/server/node_modules/postgres/cjs/src/connection.js:794:26)
2025-07-25T03:45:00.239294713Z       at handle (/usr/src/app/server/node_modules/postgres/cjs/src/connection.js:480:6)
2025-07-25T03:45:00.239296663Z       at Socket.data (/usr/src/app/server/node_modules/postgres/cjs/src/connection.js:315:9)
2025-07-25T03:45:00.239298617Z       at Socket.emit (node:events:518:28)
2025-07-25T03:45:00.239300518Z       at addChunk (node:internal/streams/readable:561:12)
2025-07-25T03:45:00.239302540Z       at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
2025-07-25T03:45:00.239304583Z       at Readable.push (node:internal/streams/readable:392:5)
2025-07-25T03:45:00.239306537Z       at TCP.onStreamRead (node:internal/stream_base_commons:189:23)
2025-07-25T03:45:00.239308482Z       at TCP.callbackTrampoline (node:internal/async_hooks:130:17) {
2025-07-25T03:45:00.239310427Z     severity_local: 'ERROR',
2025-07-25T03:45:00.239312270Z     severity: 'ERROR',
2025-07-25T03:45:00.239314136Z     code: '42P16',
2025-07-25T03:45:00.239316003Z     file: 'index.c',
2025-07-25T03:45:00.239317905Z     line: '221',
2025-07-25T03:45:00.239319806Z     routine: 'index_check_primary_key'
2025-07-25T03:45:00.239321697Z   },
2025-07-25T03:45:00.239323562Z   sql: 'ALTER TABLE "geodata_places" ADD CONSTRAINT "geodata_places_pkey" PRIMARY KEY ("id");',
2025-07-25T03:45:00.239340649Z   params: []
2025-07-25T03:45:00.239342964Z }
2025-07-25T03:45:00.256207255Z [Nest] 7  - 07/25/2025, 11:45:00 AM     LOG [Microservices:DatabaseRepository] Migration "1750676477029-AlbumAssetUpdateId" succeeded
2025-07-25T03:45:00.256955973Z [Nest] 7  - 07/25/2025, 11:45:00 AM   ERROR [Microservices:DatabaseRepository] Kysely migrations failed: PostgresError: multiple primary keys for table "geodata_places" are not allowed
2025-07-25T03:45:00.257418409Z [Nest] 7  - 07/25/2025, 11:45:00 AM     LOG [Microservices:DatabaseRepository] Migration "1750694237564-AlbumAssetAuditTable" succeeded
2025-07-25T03:45:00.257432340Z [Nest] 7  - 07/25/2025, 11:45:00 AM     LOG [Microservices:DatabaseRepository] Migration "1750780093818-AddAlbumToAssetDeleteTrigger" succeeded
2025-07-25T03:45:00.257737015Z [Nest] 7  - 07/25/2025, 11:45:00 AM     LOG [Microservices:DatabaseRepository] Migration "1751035357937-MemorySyncChanges" succeeded
2025-07-25T03:45:00.257752775Z [Nest] 7  - 07/25/2025, 11:45:00 AM     LOG [Microservices:DatabaseRepository] Migration "1751304834247-StackSyncChanges" succeeded
2025-07-25T03:45:00.257756133Z [Nest] 7  - 07/25/2025, 11:45:00 AM     LOG [Microservices:DatabaseRepository] Migration "1751924596408-AddOverrides" succeeded
2025-07-25T03:45:00.257758572Z [Nest] 7  - 07/25/2025, 11:45:00 AM     LOG [Microservices:DatabaseRepository] Migration "1752004072340-UpdateIndexOverrides" succeeded
2025-07-25T03:45:00.257760887Z [Nest] 7  - 07/25/2025, 11:45:00 AM     LOG [Microservices:DatabaseRepository] Migration "1752152941084-PeopleAuditTable" succeeded
2025-07-25T03:45:00.257763171Z [Nest] 7  - 07/25/2025, 11:45:00 AM    WARN [Microservices:DatabaseRepository] Migration "1752161055253-RenameGeodataPKConstraint" failed
2025-07-25T03:45:00.258756920Z PostgresError: multiple primary keys for table "geodata_places" are not allowed
2025-07-25T03:45:00.258775719Z     at ErrorResponse (/usr/src/app/server/node_modules/postgres/cjs/src/connection.js:794:26)
2025-07-25T03:45:00.258778133Z     at handle (/usr/src/app/server/node_modules/postgres/cjs/src/connection.js:480:6)
2025-07-25T03:45:00.258780101Z     at Socket.data (/usr/src/app/server/node_modules/postgres/cjs/src/connection.js:315:9)
2025-07-25T03:45:00.258782054Z     at Socket.emit (node:events:518:28)
2025-07-25T03:45:00.258783984Z     at addChunk (node:internal/streams/readable:561:12)
2025-07-25T03:45:00.258785906Z     at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
2025-07-25T03:45:00.258797523Z     at Readable.push (node:internal/streams/readable:392:5)
2025-07-25T03:45:00.258799791Z     at TCP.onStreamRead (node:internal/stream_base_commons:189:23)
2025-07-25T03:45:00.258801721Z     at TCP.callbackTrampoline (node:internal/async_hooks:130:17) {
2025-07-25T03:45:00.258803683Z   severity_local: 'ERROR',
2025-07-25T03:45:00.258805556Z   severity: 'ERROR',
2025-07-25T03:45:00.258807474Z   code: '42P16',
2025-07-25T03:45:00.258809336Z   file: 'index.c',
2025-07-25T03:45:00.258811242Z   line: '221',
2025-07-25T03:45:00.258813171Z   routine: 'index_check_primary_key'
2025-07-25T03:45:00.258815131Z }
2025-07-25T03:45:00.260695705Z microservices worker error: PostgresError: multiple primary keys for table "geodata_places" are not allowed, stack: PostgresError: multiple primary keys for table "geodata_places" are not allowed
2025-07-25T03:45:00.260717047Z     at ErrorResponse (/usr/src/app/server/node_modules/postgres/cjs/src/connection.js:794:26)
2025-07-25T03:45:00.260720071Z     at handle (/usr/src/app/server/node_modules/postgres/cjs/src/connection.js:480:6)
2025-07-25T03:45:00.260722120Z     at Socket.data (/usr/src/app/server/node_modules/postgres/cjs/src/connection.js:315:9)
2025-07-25T03:45:00.260724231Z     at Socket.emit (node:events:518:28)
2025-07-25T03:45:00.260726291Z     at addChunk (node:internal/streams/readable:561:12)
2025-07-25T03:45:00.260728239Z     at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
2025-07-25T03:45:00.260730150Z     at Readable.push (node:internal/streams/readable:392:5)
2025-07-25T03:45:00.260732081Z     at TCP.onStreamRead (node:internal/stream_base_commons:189:23)
2025-07-25T03:45:00.260734093Z     at TCP.callbackTrampoline (node:internal/async_hooks:130:17)
2025-07-25T03:45:00.273288440Z microservices worker exited with code 1
2025-07-25T03:45:00.273314286Z Killing api process

3 replies

1317 Jul 25, 2025

Indexes:
"geodata_places_pkey1" PRIMARY KEY, btree (id) WITH (fillfactor='100')

That's strange. geodata_places_pkey1 (with 1 at the end) is not defined anywhere in code. How did it appear?..

You can try renaming it and see if it helps:
ALTER TABLE geodata_places RENAME CONSTRAINT geodata_places_pkey1 TO geodata_places_pkey;

Well, actually I find the operation from @skatsubo works for me too. Hope the steps may help anyone with the same problem:

Go into the container and database by docker exec -it immich_postgres bash and then psql -U postgres -d immich (as defined in .env)
Type \d+ geodata_places; to check the table geodata_places, which should rerturn as follows in my case:

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 |             |              | 
Indexes:
    "geodata_places_tmp_pkey1" PRIMARY KEY, btree (id) WITH (fillfactor='100')        ** <- this casues the problem **
    "IDX_geodata_gist_earthcoord" btree (ll_to_earth_public(latitude, longitude))
    "idx_geodata_gist_earthcoord_fba1934e_c03d_4dca_96cb_198960b081f" gist (ll_to_earth_public(latitude, longitude)) WITH (fillfactor='100')
    "idx_geodata_places_admin2_name_5b85dfcb_81be_4e37_b92b_6fec8312" gin (f_unaccent("admin2Name"::text) gin_trgm_ops)
    "idx_geodata_places_country_code_37f118bc_0e2f_42c5_83f3_cef2f50" gin (f_unaccent("admin1Name"::text) gin_trgm_ops)
    "idx_geodata_places_country_code_c0900dca_75ae_4494_b430_10b4db8" gin (f_unaccent(name::text) gin_trgm_ops)
Access method: heap

Type ALTER TABLE geodata_places RENAME CONSTRAINT geodata_places_tmp_pkey1 TO geodata_places_pkey; to fix (the same geodata_places_tmp_pkey1 as prevoius step). It should return ALTER TABLE.
Type \q and exit to exit the database and container, respectively.
Upgrade immich as usual and immich_server should run normally.

skatsubo Jul 25, 2025
Collaborator

Glad it worked for you. Thanks for the guide, now we can point folks here.

Ahrimdon Jul 28, 2025

Fix worked like a charm! Thank you :)

diy97 · 2025-07-25T08:09:05Z

diy97
Jul 25, 2025

I also have this issue, and rolled back to V1.35.3

0 replies

vladiceanu · 2025-07-25T09:15:25Z

vladiceanu
Jul 25, 2025

Similar issue on upgrade to 1.136.0.

PostgresError: multiple primary keys for table "geodata_places" are not allowed
at ErrorResponse (/usr/src/app/server/node_modules/postgres/cjs/src/connection.js:794:26)
at handle (/usr/src/app/server/node_modules/postgres/cjs/src/connection.js:480:6)
at Socket.data (/usr/src/app/server/node_modules/postgres/cjs/src/connection.js:315:9)
at Socket.emit (node:events:518:28)
at addChunk (node:internal/streams/readable:561:12)
at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
at Readable.push (node:internal/streams/readable:392:5)
at TCP.onStreamRead (node:internal/stream_base_commons:189:23) {
severity_local: 'ERROR',
severity: 'ERROR',
code: '42P16',
file: 'index.c',
line: '221',
routine: 'index_check_primary_key'
}
microservices worker error: PostgresError: multiple primary keys for table "geodata_places" are not allowed, stack: PostgresError: multiple primary keys for table "geodata_places" are not allowed
at ErrorResponse (/usr/src/app/server/node_modules/postgres/cjs/src/connection.js:794:26)
at handle (/usr/src/app/server/node_modules/postgres/cjs/src/connection.js:480:6)
at Socket.data (/usr/src/app/server/node_modules/postgres/cjs/src/connection.js:315:9)
at Socket.emit (node:events:518:28)
at addChunk (node:internal/streams/readable:561:12)
at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
at Readable.push (node:internal/streams/readable:392:5)
at TCP.onStreamRead (node:internal/stream_base_commons:189:23)

For me, the error wasn't clear about which key, had to find out myself by running

immich=# \d+ geodata_places;
                            [......]
Indexes:
    "geodata_places_tmp_pkey" PRIMARY KEY, btree (id) WITH (fillfactor='100')
    [......]

Then run the ALTER TABLE and restart the immich-server container

ALTER TABLE geodata_places RENAME CONSTRAINT geodata_places_tmp_pkey TO geodata_places_pkey;

After that it worked fine ✅

1 reply

rpmc994 Jul 30, 2025

Thank you so much, just been having this same issue and altering the CONSTRAINT fixed it

skatsubo · 2025-07-25T11:02:28Z

skatsubo
Jul 25, 2025
Collaborator

Interesting. Something weird is happening. There is zoo of auto-generated index names instead of the expected geodata_places_pkey.
So far:

geodata_places_pkey1
geodata_places_tmp_pkey
geodata_places_tmp_pkey1

Related/duplicate issue:

DB migration failing: multiple primary keys for table "geodata_places" are not allowed #20167

(Partial?) fix is ready:

fix: geodata_places pkey migration failing for certain upgrade paths #20177

7 replies

skatsubo Jul 31, 2025
Collaborator

Thank you! When can we expect the fix to appear in the image?

1.137.0 was just released

phumNewen Jul 31, 2025

Just updated. Still facing the problem

Ahrimdon Jul 31, 2025

Just updated. Still facing the problem

#20157 (reply in thread)

skatsubo Jul 31, 2025
Collaborator

@phumNewen

Which version are you updating from?
Could you post logs and \d geodata_places from the database?

I just tested 1.132.3 -> 1.137.0 successfully.
On 1.132.3 it was:

immich=# \d geodata_places
...
Indexes:
    "geodata_places_tmp_pkey1" PRIMARY KEY, btree (id) WITH (fillfactor='100')
...

phumNewen Aug 1, 2025

Just updated. Still facing the problem

#20157 (reply in thread)

That worked. Thanks a lot to everyone!

JBGoodnuf · 2025-07-31T21:47:14Z

JBGoodnuf
Jul 31, 2025

I am experiencing this same error with Immich after updating in TrueNAS Scale... how would I go about implementing this fix in TrueNAS?

2 replies

skatsubo Jul 31, 2025
Collaborator

I am experiencing this same error with Immich after updating in TrueNAS Scale

1.137.0 with the fix was just released.

JBGoodnuf Aug 1, 2025

Thanks @skatsubo that did the trick!

Uh oh!

after update 1.135.3 to 1.136.0 error of microservices worker error: PostgresError: multiple primary keys for table "geodata_places" are not allowed #20157

Uh oh!

Replies: 12 comments · 14 replies

Uh oh!

alextran1502 Jul 24, 2025 Maintainer

Uh oh!

zackpollard Jul 24, 2025 Maintainer

Uh oh!

gedias Jul 24, 2025 Author

Uh oh!

gedias Jul 24, 2025 Author

Uh oh!

skatsubo Jul 25, 2025 Collaborator

Uh oh!

Uh oh!

gedias Jul 25, 2025 Author

Uh oh!

gedias Jul 25, 2025 Author

Uh oh!

Uh oh!

Uh oh!

Uh oh!

skatsubo Jul 25, 2025 Collaborator

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

skatsubo Jul 25, 2025 Collaborator

Uh oh!

skatsubo Jul 31, 2025 Collaborator

Uh oh!

Uh oh!

Uh oh!

skatsubo Jul 31, 2025 Collaborator

Uh oh!

Uh oh!

Uh oh!

skatsubo Jul 31, 2025 Collaborator

Uh oh!

Replies: 12 comments 14 replies

alextran1502
Jul 24, 2025
Maintainer

zackpollard Jul 24, 2025
Maintainer

gedias
Jul 24, 2025
Author

gedias
Jul 24, 2025
Author

skatsubo
Jul 25, 2025
Collaborator

gedias
Jul 25, 2025
Author

gedias
Jul 25, 2025
Author

skatsubo Jul 25, 2025
Collaborator

skatsubo
Jul 25, 2025
Collaborator

skatsubo Jul 31, 2025
Collaborator

skatsubo Jul 31, 2025
Collaborator

skatsubo Jul 31, 2025
Collaborator