immich-app / immich

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

Database restore on v.1.112.1 throws error during execution #11949

Closed toennema closed 1 month ago

toennema commented 2 months ago

The bug

Doing the official backup & restore procedure described here: https://immich.app/docs/administration/backup-and-restore/, the command line gives the following error: ERROR: calledResult::unwrap()on anErrvalue: Error { inner: Error { inner: TomlError { message: "invalid table header\nexpected newline,#", original: Some("[indexing.hnsw]\r\r\nm = 16\r\r\nef_construction = 300"), keys: [], span: Some(15..16) } } }

The full log from the command line is attached. log.log

I don't know if this breaks anything but when restoring the a backup dump created & restored with v1.111.0 there is no error message. With v.112.1, also when using the backup file created with v.1.111.0, it gives this error. The same is true when following the backup & restore procedure under v1.112.1 when comming from an updated install from v1.111.0.

The OS that Immich Server is running on

Debian 12 Bookworm

Version of Immich Server

v.1.112.1

Version of Immich Mobile App

v1.112.1

Platform with the issue

Your docker-compose.yml content

#
# WARNING: Make sure to use the docker-compose.yml of the current release:
#
# https://github.com/immich-app/immich/releases/latest/download/docker-compose.yml
#
# The compose file on main may not be compatible with the latest release.
#

name: immich

services:
  immich-server:
    container_name: immich_server
    image: ghcr.io/immich-app/immich-server:${IMMICH_VERSION:-release}
    # 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
    ports:
      - 2283:3001
    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, openvino] to the image tag.
    # Example tag: ${IMMICH_VERSION:-release}-cuda
    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, openvino, openvino-wsl] for accelerated inference - use the `-wsl` version for WSL2 where applicable
    volumes:
      - model-cache:/cache
    env_file:
      - .env
    restart: always
    healthcheck:
      disable: false

  redis:
    container_name: immich_redis
    image: docker.io/redis:6.2-alpine@sha256:e3b17ba9479deec4b7d1eeec1548a253acc5374d68d3b27937fcfe4df8d18c7e
    healthcheck:
      test: redis-cli ping || exit 1
    restart: always

  database:
    container_name: immich_postgres
    image: docker.io/tensorchord/pgvecto-rs:pg14-v0.2.0@sha256:90724186f0a3517cf6914295b5ab410db9ce23190a2d9d0b9dd6463e3fa298f0
    environment:
      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
    healthcheck:
      test: pg_isready --dbname='${DB_DATABASE_NAME}' --username='${DB_USERNAME}' || exit 1; Chksum="$$(psql --dbname='${DB_DATABASE_NAME}' --username='${DB_USERNAME}' --tuples-only --no-align --command='SELECT COALESCE(SUM(checksum_failures), 0) FROM pg_stat_database')"; echo "checksum failure count is $$Chksum"; [ "$$Chksum" = '0' ] || exit 1
      interval: 5m
      start_interval: 30s
      start_period: 5m
    command: ["postgres", "-c", "shared_preload_libraries=vectors.so", "-c", 'search_path="$$user", public, vectors', "-c", "logging_collector=on", "-c", "max_wal_size=2GB", "-c", "shared_buffers=512MB", "-c", "wal_compression=on"]
    restart: always

volumes:
  model-cache:

Your .env content

# You can find documentation for all the supported env variables at https://immich.app/docs/install/environment-variables

# The location where your uploaded files are stored
UPLOAD_LOCATION=/media/pi/My Book/Immich/upload
# The location where your database files are stored
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/UTC

# The Immich version to use. You can pin this to a specific version like "v1.71.0"
IMMICH_VERSION=release

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

# The values below this line do not need to be changed
###################################################################################
DB_USERNAME=postgres
DB_DATABASE_NAME=immich

Reproduction steps

1. Restore backup from dump on v.1.112.1
2. CLI gives error message in line 369 (see log file)

Relevant log output

No response

Additional information

No response

mmomjian commented 2 months ago

This is a very strange bug but I don't see how it could be related to Immich at all - are you able to post the portion of the database dump that produces this error? Just to be clear, the dump was created and restored all on the same Linux machine?

bo0tzz commented 2 months ago

cc @mertalev, this smells a bit like a pgvecto.rs error to me?

toennema commented 2 months ago

This is a very strange bug but I don't see how it could be related to Immich at all - are you able to post the portion of the database dump that produces this error? Just to be clear, the dump was created and restored all on the same Linux machine?

Hello, yes. It is the same machine. But you are right, it seems to be a problem outside of Immich. The backup I had restored without error using v1.111.0 was a backup file from a couple of weeks ago, when this version was still the current one. I have uploaded some new assets after restoring this backup under v1.111.0, then did a new backup dump and restored it this morning (all using v1.111.0 without any update). This restore gave the same error message as before.

So this is clearly not related to v1.112.1. The restore seems ok. But I am still afraid that something is broken and that I have to start from 0 at some point if I continue to make incomplete or faulty backups. Do you think this error is problematic? Thank you.

toennema commented 2 months ago

Little update. I backed up the v.111.0 database which gave the error during restore, stopped all containers and purged them. Then redownloaded the current version and did a restore of this backup. The error message is now gone. I am still wondering so if I have lost/broken anything by doing the restore once with the error.

mertalev commented 2 months ago

There are some cases of this in #11801 too. The \r\r\n in the index creation command doesn't get parsed correctly. I'm not sure in what cases it ends up like that.