search

immich-app / immich

High performance self-hosted photo and video management solution.
https://immich.app
GNU Affero General Public License v3.0
50.56k stars 2.68k forks source link

[BUG] Docker Compose stack cannot connect to external database (PostgreSQL). #2950

Closed perfectra1n closed 1 year ago

perfectra1n commented 1 year ago

The bug

Hey there,

Just wanted to say that I love Immich. It's great all around. I'm assuming that there is an issue with how the app recovers from a network hiccup or something of the sort. It tries to create a unique index and is unable to. The entire DB is 252MB if you would like a copy of it, just let me know where to put it. I get the following error over and over again:

[Nest] 1  - 06/25/2023, 7:39:56 PM   ERROR [TypeOrmModule] Unable to connect to the database. Retrying (8)...
QueryFailedError: could not create unique index "UQ_4ed4f8052685ff5b1e7ca1058ba"
    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:10: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)
[Nest] 1  - 06/25/2023, 7:39:59 PM   ERROR [TypeOrmModule] Unable to connect to the database. Retrying (9)...
QueryFailedError: could not create unique index "UQ_4ed4f8052685ff5b1e7ca1058ba"
    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:10: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)
[Nest] 1  - 06/25/2023, 7:39:59 PM   ERROR [ExceptionHandler] could not create unique index "UQ_4ed4f8052685ff5b1e7ca1058ba"
QueryFailedError: could not create unique index "UQ_4ed4f8052685ff5b1e7ca1058ba"
    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:10: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)

When I completely remove the DB and create a new one, the application launches just fine. I'm not quite sure why there's already a unique index for my immichdb, but it's the only app that is using that database. I'm only running one instance of the database.

I'm not the best when it comes to understanding how databases work, but if you need anything more from me please let me know.

The OS that Immich Server is running on

Ubuntu 20.04

Version of Immich Server

v1.63.0

Version of Immich Mobile App

v1.63.0

Platform with the issue

Your docker-compose.yml content

version: "3.8"

services:
  immich-server:
    container_name: immich_server
    image: ghcr.io/immich-app/immich-server:${IMMICH_VERSION:-release}
    command: [ "start.sh", "immich" ]
    volumes:
      - ${UPLOAD_LOCATION}:/usr/src/app/upload
    env_file:
      - ../stack.env
    depends_on:
      - redis
      - typesense
    restart: always

  immich-microservices:
    container_name: immich_microservices
    image: ghcr.io/immich-app/immich-server:${IMMICH_VERSION:-release}
    command: [ "start.sh", "microservices" ]
    volumes:
      - ${UPLOAD_LOCATION}:/usr/src/app/upload
    env_file:
      - ../stack.env
    depends_on:
      - redis
      - typesense
    restart: always

  immich-machine-learning:
    container_name: immich_machine_learning
    image: ghcr.io/immich-app/immich-machine-learning:${IMMICH_VERSION:-release}
    volumes:
      - model-cache:/cache
    env_file:
      - ../stack.env
    restart: always

  immich-web:
    container_name: immich_web
    image: ghcr.io/immich-app/immich-web:${IMMICH_VERSION:-release}
    env_file:
      - ../stack.env
    restart: always

  typesense:
    container_name: immich_typesense
    image: typesense/typesense:0.24.1@sha256:9bcff2b829f12074426ca044b56160ca9d777a0c488303469143dd9f8259d4dd
    environment:
      - TYPESENSE_API_KEY=${TYPESENSE_API_KEY}
      - TYPESENSE_DATA_DIR=/data
    logging:
      driver: none
    volumes:
      - /mnt/nfs_docker_volumes/ubuntuserver/immich/tsdata:/data
    restart: always

  redis:
    container_name: immich_redis
    image: redis:6.2-alpine@sha256:70a7a5b641117670beae0d80658430853896b5ef269ccf00d1827427e3263fa3
    restart: always

  immich-proxy:
    container_name: immich_proxy
    image: ghcr.io/immich-app/immich-proxy:${IMMICH_VERSION:-release}
    environment:
      # Make sure these values get passed through from the env file
      - IMMICH_SERVER_URL
      - IMMICH_WEB_URL
    ports:
      - 2283:8080
    depends_on:
      - immich-server
    restart: always

volumes:
  model-cache:

Your .env content

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

DB_HOSTNAME=mainpostgres.something.network
DB_USERNAME=immichuser
DB_PASSWORD=immichpassword
DB_DATABASE_NAME=immichdb

# Optional Database settings:
# DB_PORT=5432

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

REDIS_HOSTNAME=immich_redis

# Optional Redis settings:

# Note: these parameters are not automatically passed to the Redis Container
# to do so, please edit the docker-compose.yml file as well. Redis is not configured
# via environment variables, only redis.conf or the command line

# 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/nfs_docker_volumes/server/immich/upload

###################################################################################
# Log message level - [simple|verbose]
###################################################################################

LOG_LEVEL=simple

###################################################################################
# Typesense
###################################################################################
# TYPESENSE_ENABLED=false
TYPESENSE_API_KEY=ed01861e-68a1-43e6-8714-something
# TYPESENSE_HOST: typesense
# TYPESENSE_PORT: 8108
# TYPESENSE_PROTOCOL: http

###################################################################################
# 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="Immich Instance"

####################################################################################
# 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

####################################################################################
# Alternative API's External Address - Optional
#
# This is an advanced feature used to control the public server endpoint returned to clients during Well-known discovery.
# You should only use this if you want mobile apps to access the immich API over a custom URL. Do not include trailing slash.
# NOTE: At this time, the web app will not be affected by this setting and will continue to use the relative path: /api
# Examples: http://localhost:3001, http://immich-api.example.com, etc
####################################################################################

IMMICH_API_URL_EXTERNAL=http://immich.domain.com

###################################################################################
# Immich Version - Optional
#
# This allows all immich docker images to be pinned to a specific version. By default,
# the version is "release" but could be a specific version, like "v1.59.0".
###################################################################################

#IMMICH_VERSION=

Reproduction steps

1. Create Immich instance
2. Run for an undetermined amount of time.
3. Have a network stroke(?)
4. Maybe reboot Immich a few times
5. Database will become "unreachable" as a result of being unable to create a unique index.
6. Profit.

Additional information

No response

bo0tzz commented 1 year ago

See https://github.com/immich-app/immich/issues/2931#issuecomment-1605396092

perfectra1n commented 1 year ago

LOL I looked two days ago and was like "I'll post this issue this weekend" and lo and behold a resolution has been found. Thanks :)

Dropped the queries / steps I had to use in #2931 (comment).