imagegenius
Unable to initialize reverse geocoding: PostgresError: relation "geodata_places_tmp" already exists
Hey oh, 1st time posting here so not sure how to format everything.
Anyhoo, this morning I realized that my Immich docker was using a lot of ram ressource and when looking at the logs I saw that it was looping like that this: " Detected CPU Cores: 8 Starting microservices worker [Nest] 4710 - 05/23/2025, 8:08:26 AM LOG [Microservices:EventRepository] Initialized websocket server [Nest] 4710 - 05/23/2025, 8:08:26 AM LOG [Microservices:DatabaseRepository] Running migrations, this may take a while [Nest] 4710 - 05/23/2025, 8:08:26 AM LOG [Microservices:MetadataService] Bootstrapping metadata service [Nest] 4710 - 05/23/2025, 8:08:26 AM LOG [Microservices:MetadataService] Initializing metadata service [Nest] 4710 - 05/23/2025, 8:08:26 AM LOG [Microservices:MapRepository] Initializing metadata repository Query failed : { durationMs: 2.0961520000000746, error: PostgresError: relation "geodata_places_tmp" already exists at ErrorResponse (/app/immich/server/node_modules/postgres/cjs/src/connection.js:790:26) at handle (/app/immich/server/node_modules/postgres/cjs/src/connection.js:476:6) at Socket.data (/app/immich/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: '42P07', file: 'heap.c', line: '1149', routine: 'heap_create_with_catalog' }, sql: 'CREATE TABLE geodata_places_tmp\n' + ' (\n' + ' LIKE geodata_places INCLUDING ALL EXCLUDING INDEXES\n' + ' )', params: [] } [Nest] 4710 - 05/23/2025, 8:08:26 AM ERROR [Microservices:MetadataService] Unable to initialize reverse geocoding: PostgresError: relation "geodata_places_tmp" already exists PostgresError: relation "geodata_places_tmp" already exists at ErrorResponse (/app/immich/server/node_modules/postgres/cjs/src/connection.js:790:26) at handle (/app/immich/server/node_modules/postgres/cjs/src/connection.js:476:6) at Socket.data (/app/immich/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) Error: Metadata service init failed at MetadataService.init (/app/immich/server/dist/services/metadata.service.js:93:19) at process.processTicksAndRejections (node:internal/process/task_queues:105:5) at async MetadataService.onBootstrap (/app/immich/server/dist/services/metadata.service.js:72:9) at async EventRepository.onEvent (/app/immich/server/dist/repositories/event.repository.js:126:13) at async MicroservicesModule.onModuleInit (/app/immich/server/dist/app.module.js:83:9) at async callModuleInitHook (/app/immich/server/node_modules/@nestjs/core/hooks/on-module-init.hook.js:51:9) at async NestApplication.callInitHook (/app/immich/server/node_modules/@nestjs/core/nest-application-context.js:242:13) at async NestApplication.init (/app/immich/server/node_modules/@nestjs/core/nest-application.js:100:9) at async NestApplication.listen (/app/immich/server/node_modules/@nestjs/core/nest-application.js:170:13) at async bootstrap (/app/immich/server/dist/workers/microservices.js:25:5) microservices worker error: Error: Metadata service init failed, stack: Error: Metadata service init failed at MetadataService.init (/app/immich/server/dist/services/metadata.service.js:93:19) at process.processTicksAndRejections (node:internal/process/task_queues:105:5) at async MetadataService.onBootstrap (/app/immich/server/dist/services/metadata.service.js:72:9) at async EventRepository.onEvent (/app/immich/server/dist/repositories/event.repository.js:126:13) at async MicroservicesModule.onModuleInit (/app/immich/server/dist/app.module.js:83:9) at async callModuleInitHook (/app/immich/server/node_modules/@nestjs/core/hooks/on-module-init.hook.js:51:9) at async NestApplication.callInitHook (/app/immich/server/node_modules/@nestjs/core/nest-application-context.js:242:13) at async NestApplication.init (/app/immich/server/node_modules/@nestjs/core/nest-application.js:100:9) at async NestApplication.listen (/app/immich/server/node_modules/@nestjs/core/nest-application.js:170:13) at async bootstrap (/app/immich/server/dist/workers/microservices.js:25:5) microservices worker exited with code 1 Detected CPU Cores: 8 Starting microservices worker [Nest] 4735 - 05/23/2025, 8:08:30 AM LOG [Microservices:EventRepository] Initialized websocket server [Nest] 4735 - 05/23/2025, 8:08:30 AM LOG [Microservices:DatabaseRepository] Running migrations, this may take a while [Nest] 4735 - 05/23/2025, 8:08:30 AM LOG [Microservices:MetadataService] Bootstrapping metadata service [Nest] 4735 - 05/23/2025, 8:08:30 AM LOG [Microservices:MetadataService] Initializing metadata service [Nest] 4735 - 05/23/2025, 8:08:30 AM LOG [Microservices:MapRepository] Initializing metadata repository Query failed : { durationMs: 2.1993249999995896, error: PostgresError: relation "geodata_places_tmp" already exists at ErrorResponse (/app/immich/server/node_modules/postgres/cjs/src/connection.js:790:26) at handle (/app/immich/server/node_modules/postgres/cjs/src/connection.js:476:6) at Socket.data (/app/immich/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: '42P07', file: 'heap.c', line: '1149', routine: 'heap_create_with_catalog' }, sql: 'CREATE TABLE geodata_places_tmp\n' + ' (\n' + ' LIKE geodata_places INCLUDING ALL EXCLUDING INDEXES\n' + ' )', params: [] } [Nest] 4735 - 05/23/2025, 8:08:31 AM ERROR [Microservices:MetadataService] Unable to initialize reverse geocoding: PostgresError: relation "geodata_places_tmp" already exists PostgresError: relation "geodata_places_tmp" already exists at ErrorResponse (/app/immich/server/node_modules/postgres/cjs/src/connection.js:790:26) at handle (/app/immich/server/node_modules/postgres/cjs/src/connection.js:476:6) at Socket.data (/app/immich/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) Error: Metadata service init failed at MetadataService.init (/app/immich/server/dist/services/metadata.service.js:93:19) at process.processTicksAndRejections (node:internal/process/task_queues:105:5) at async MetadataService.onBootstrap (/app/immich/server/dist/services/metadata.service.js:72:9) at async EventRepository.onEvent (/app/immich/server/dist/repositories/event.repository.js:126:13) at async MicroservicesModule.onModuleInit (/app/immich/server/dist/app.module.js:83:9) at async callModuleInitHook (/app/immich/server/node_modules/@nestjs/core/hooks/on-module-init.hook.js:51:9) at async NestApplication.callInitHook (/app/immich/server/node_modules/@nestjs/core/nest-application-context.js:242:13) at async NestApplication.init (/app/immich/server/node_modules/@nestjs/core/nest-application.js:100:9) at async NestApplication.listen (/app/immich/server/node_modules/@nestjs/core/nest-application.js:170:13) at async bootstrap (/app/immich/server/dist/workers/microservices.js:25:5) microservices worker error: Error: Metadata service init failed, stack: Error: Metadata service init failed at MetadataService.init (/app/immich/server/dist/services/metadata.service.js:93:19) at process.processTicksAndRejections (node:internal/process/task_queues:105:5) at async MetadataService.onBootstrap (/app/immich/server/dist/services/metadata.service.js:72:9) at async EventRepository.onEvent (/app/immich/server/dist/repositories/event.repository.js:126:13) at async MicroservicesModule.onModuleInit (/app/immich/server/dist/app.module.js:83:9) at async callModuleInitHook (/app/immich/server/node_modules/@nestjs/core/hooks/on-module-init.hook.js:51:9) at async NestApplication.callInitHook (/app/immich/server/node_modules/@nestjs/core/nest-application-context.js:242:13) at async NestApplication.init (/app/immich/server/node_modules/@nestjs/core/nest-application.js:100:9) at async NestApplication.listen (/app/immich/server/node_modules/@nestjs/core/nest-application.js:170:13) at async bootstrap (/app/immich/server/dist/workers/microservices.js:25:5) microservices worker exited with code 1 "
I tried to do the last update, restart the PostGreSQL_Immich, but in the end Immich keep looping on that. Any idea how to fix that ? Thanks
No, I don't find the settings that I need to change in the edit page of the docker and this issue was also happening before the update
I don't find those entry in the docker setting page of immich : use "SERVER_PORT" or "MACHINE_LEARNING_PORT" instead of "IMMICH_PORT" and use "SERVER_HOST" or "MACHINE_LEARNING_HOST" instead of "IMMICH_HOST"
I don't find those entry in the docker setting page of immich : use "SERVER_PORT" or "MACHINE_LEARNING_PORT" instead of "IMMICH_PORT" and use "SERVER_HOST" or "MACHINE_LEARNING_HOST" instead of "IMMICH_HOST"
It's fine to not have set them. They exist for those who want to customize their setup
So what should I do ?
Restore a working backup
Those are the container settings that I have
docker run -d --name='immich' --net='bridge' --pids-limit 2048 -e TZ="Europe/Paris" -e HOST_OS="Unraid" -e HOST_HOSTNAME="Proto-Serv" -e HOST_CONTAINERNAME="immich" -e 'DB_HOSTNAME'='192.168.0.38' -e 'DB_USERNAME'='postgres' -e 'DB_PASSWORD'='postgres' -e 'DB_DATABASE_NAME'='immich' -e 'REDIS_HOSTNAME'='localhost' -e 'DB_PORT'='5433' -e 'REDIS_PORT'='6379' -e 'REDIS_PASSWORD'='' -e 'MACHINE_LEARNING_HOST'='0.0.0.0' -e 'MACHINE_LEARNING_PORT'='3003' -e 'MACHINE_LEARNING_WORKERS'='0' -e 'MACHINE_LEARNING_WORKER_TIMEOUT'='120' -e 'DOCKER_MODS'='imagegenius/mods:universal-redis' -e 'PUID'='99' -e 'PGID'='100' -e 'UMASK'='022' -l net.unraid.docker.managed=dockerman -l net.unraid.docker.webui='http://[IP]:[PORT:8080]' -l net.unraid.docker.icon='https://raw.githubusercontent.com/imagegenius/templates/main/unraid/img/immich.png' -p '8081:8080/tcp' -v '/mnt/user/Immich/':'/photos':'rw' -v '/mnt/user/Pictures/Backup GPhoto/':'/libraries':'rw' -v '/mnt/user/appdata/immich':'/config':'rw' 'ghcr.io/imagegenius/immich' 091b17e8c330b61e77d1fc7cf85baad8eaa09c11a6e58cdb4d8a88545a2935a0
Those are the container settings that I have
docker run -d --name='immich' --net='bridge' --pids-limit 2048 -e TZ="Europe/Paris" -e HOST_OS="Unraid" -e HOST_HOSTNAME="Proto-Serv" -e HOST_CONTAINERNAME="immich" -e 'DB_HOSTNAME'='192.168.0.38' -e 'DB_USERNAME'='postgres' -e 'DB_PASSWORD'='postgres' -e 'DB_DATABASE_NAME'='immich' -e 'REDIS_HOSTNAME'='localhost' -e 'DB_PORT'='5433' -e 'REDIS_PORT'='6379' -e 'REDIS_PASSWORD'='' -e 'MACHINE_LEARNING_HOST'='0.0.0.0' -e 'MACHINE_LEARNING_PORT'='3003' -e 'MACHINE_LEARNING_WORKERS'='0' -e 'MACHINE_LEARNING_WORKER_TIMEOUT'='120' -e 'DOCKER_MODS'='imagegenius/mods:universal-redis' -e 'PUID'='99' -e 'PGID'='100' -e 'UMASK'='022' -l net.unraid.docker.managed=dockerman -l net.unraid.docker.webui='http://[IP]:[PORT:8080]' -l net.unraid.docker.icon='https://raw.githubusercontent.com/imagegenius/templates/main/unraid/img/immich.png' -p '8081:8080/tcp' -v '/mnt/user/Immich/':'/photos':'rw' -v '/mnt/user/Pictures/Backup GPhoto/':'/libraries':'rw' -v '/mnt/user/appdata/immich':'/config':'rw' 'ghcr.io/imagegenius/immich' 091b17e8c330b61e77d1fc7cf85baad8eaa09c11a6e58cdb4d8a88545a2935a0
The settings are fine
I think this is actually an Immich bug. Basically the server crashed while importing geodata and there's now a temporary table left behind in the DB that Immich isn't handling correctly. You can run the SQL query DROP TABLE geodata_places_tmp; to fix it.
OK so I have seen this issue on reddit and other, but I have no idea how to run it, do I run it via the console of the the PostGreSQL or the console of immich
OK so I have seen this issue on reddit and other, but I have no idea how to run it, do I run it via the console of the the PostGreSQL or the console of immich
Option 1. Console / GUI of Posgresql.
Option 2. From terminal / SSH on the host by connecting to Postgresql in container.
https://immich.app/docs/guides/database-queries/
Run
docker exec -it immich_postgres psql --dbname=<DB_DATABASE_NAME> --username=<DB_USERNAME>to connect to the database via the container directly. (Replace <DB_DATABASE_NAME> and <DB_USERNAME> with the values from your .env file).
You may need to replace immich_postgres with your container name as well.
Option 3. Upgrade to v1.136.0 where geodata_places_tmp is fixed.
Or, preferably, wait for v1.136.1 which fixes several new bugs discovered in v.1.136.0.
