Database restore failed: psql error, role "immich" does not exist

[changty6]

Hi everyone!

I set up Immich on a Linux Mint Debian Edition server and it is running smoothly. I wanted to do some backup and restore test before I completely moved from Google Photo.
The docker compose file, .env file and postgres directory are in ~/Immich, and I set UPLOAD_LOCATION to ~/Immich-lib. The postgres database was backed up using its own automatic backup, and the assets folder were backed up to an external HDD using Restic.
I have another Fedora computer, and I fresh installed Immich there, set up the same directory structure, and copied the assets folder to that computer. Then I followed the instruction to do the manual restore then I got the error at the last step:

$ gunzip < ../Immich-lib/backups/immich-db-backup-1742004000007.sql.gz | sed "s/SELECT pg_catalog.set_config('search_path', '', false);/SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true);/g" | docker exec -i immich_postgres psql --dbname=postgres --username=immich
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "immich" does not exist

The docker-compose.yml and .env files are set the same.

The OS that Immich Server is running on

Linux Mint Debian Edition 6.1 - Original
Fedora 41 - Target

Version of Immich Server

v1.129.0

• Server
• Web
• Mobile

Your docker-compose.yml content

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: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, 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:148bb5411c184abd288d9aaed139c98123eeb8824c5d3fce03cf721db58066d8
    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:739cdd626151ff1f796dc95a6591b55a714f341c737e27f045019ceabf8e8c52
    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="$${POSTGRES_DB}" --username="$${POSTGRES_USER}" || exit 1;
        Chksum="$$(psql --dbname="$${POSTGRES_DB}" --username="$${POSTGRES_USER}" --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

UPLOAD_LOCATION=../Immich-lib
DB_DATA_LOCATION=./postgres
IMMICH_VERSION=release
DB_PASSWORD=mypasswd
DB_USERNAME=postgres
DB_DATABASE_NAME=immich

Reproduction steps

cd ~/Immich
docker compose pull
docker compose create
docker start immich_postgres 
sleep 10
gunzip < "../Immich-lib/backups/immich-db-backup-1742004000007.sql.gz" | sed "s/SELECT pg_catalog.set_config('search_path', '', false);/SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true);/g" | docker exec -i immich_postgres psql --dbname=postgres --username=immich

Then I got the error
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: role "immich" does not exist

Thanks!

[danieldietzler]

You mixed up the database name and user name.

[changty6]

Solved! Thanks so much! Such a simple mistake...
Now I can log in to my account, but half of the images are showing "Error Loading Image", while others are loading fine. Should I give it more time?

[danieldietzler]

Sounds like it cannot find some thumbnails. I'd make sure those still exist on the disk and in the exact same location

[changty6]

Everything is fine after regenerating the thumbnails. Thanks very much!

[Liziloo]

OMG I'm glad you made this mistake, because I did it too, even after double checking.