immich-app/immich
1
0
Fork 0
mirror of https://github.com/immich-app/immich.git synced 2026-07-25 14:00:45 +03:00
Code Issues 783 Packages Projects Releases 22 Wiki Activity

Restoring a database dump through the documentation command didn't work #4886

New Issue
Closed
opened 2026-02-05 10:57:23 +03:00 by OVERLORD · 11 comments
OVERLORD commented 2026-02-05 10:57:23 +03:00
Owner
Copy Link

Originally created by @BernardoGiordano on GitHub (Dec 7, 2024).

The bug

Today I migrated my immich database (30k photos) from a RPI4 to my new unRAID NAS. It's already the second time I'm upgrading machine, so I already had familiarity with the database backup and restore procedure.
Note: I've been using Immich since 1.78 and I went through all the updates periodically. I backed up my last database dump after upgrading to 1.122.1.

Issue is that the database restore command in the docs didn't work. I followed the documentation carefully, also making sure that the skip migrations variable was set, the postgres appdata folder was completely wiped and so on.

Basically, running gunzip < "/path/to/backup/dump.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 --username=postgres didn't do anything (I had no console output, the command took very few seconds to complete).

Luckily, I still had the old command I run when I migrated the first time in my bash history, which was: gunzip < "/path/to/backup/dump.sql.gz" | docker exec -i immich_postgres psql -U postgres -d immich and it worked perfectly (console output was showing that I was altering table and copying rows). Everything went fine after that and I'm enjoying my new hardware with the same library. Maybe you can consider adding this command in the docs or replacing the other one completely? If I hadn't already done this procedure once following the old docs at the time, I would have been in trouble now.

The OS that Immich Server is running on

unRAID

Version of Immich Server

v1.122.1

Version of Immich Mobile App

v1.121.0

Platform with the issue

  • Server
  • Web
  • Mobile

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: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:eaba718fecd1196d88533de7ba49bf903ad33664a92debb24660a922ecd9cac8
    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="$${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

# 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=/mnt/user/photo-library
# The location where your database files are stored
DB_DATA_LOCATION=/mnt/user/appdata/immich-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=REDACTED

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

Reproduction steps

  1. Follow the restore procedure carefully
  2. The gunzip ... command won't output anything to console.

Relevant log output

No response

Additional information

No response

Originally created by @BernardoGiordano on GitHub (Dec 7, 2024). ### The bug Today I migrated my immich database (30k photos) from a RPI4 to my new unRAID NAS. It's already the second time I'm upgrading machine, so I already had familiarity with the database backup and restore procedure. Note: I've been using Immich since 1.78 and I went through all the updates periodically. I backed up my last database dump after upgrading to 1.122.1. Issue is that the database restore command in the docs didn't work. I followed the documentation carefully, also making sure that the skip migrations variable was set, the postgres appdata folder was completely wiped and so on. Basically, running `gunzip < "/path/to/backup/dump.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 --username=postgres` didn't do anything (I had no console output, the command took very few seconds to complete). Luckily, I still had the old command I run when I migrated the first time in my bash history, which was: `gunzip < "/path/to/backup/dump.sql.gz" | docker exec -i immich_postgres psql -U postgres -d immich` and it worked perfectly (console output was showing that I was altering table and copying rows). Everything went fine after that and I'm enjoying my new hardware with the same library. Maybe you can consider adding this command in the docs or replacing the other one completely? If I hadn't already done this procedure once following the old docs at the time, I would have been in trouble now. ### The OS that Immich Server is running on unRAID ### Version of Immich Server v1.122.1 ### Version of Immich Mobile App v1.121.0 ### Platform with the issue - [X] Server - [ ] Web - [ ] Mobile ### Your docker-compose.yml content ```YAML # # 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: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:eaba718fecd1196d88533de7ba49bf903ad33664a92debb24660a922ecd9cac8 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="$${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 ```Shell # 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=/mnt/user/photo-library # The location where your database files are stored DB_DATA_LOCATION=/mnt/user/appdata/immich-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=REDACTED # The values below this line do not need to be changed ################################################################################### DB_USERNAME=postgres DB_DATABASE_NAME=immich ``` ### Reproduction steps 1. Follow the [restore procedure](https://immich.app/docs/administration/backup-and-restore) carefully 2. The `gunzip ...` command won't output anything to console. ### Relevant log output _No response_ ### Additional information _No response_
OVERLORD commented 2026-02-05 10:57:26 +03:00
Author
Owner
Copy Link

@bo0tzz commented on GitHub (Dec 7, 2024):

The only difference between the two commands is the sed command in the middle. Do you get any output to the console for just gunzip < "/path/to/backup/dump.sql.gz" or for gunzip < "/path/to/backup/dump.sql.gz" | sed "s/SELECT pg_catalog.set_config('search_path', '', false);/SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true);/g"?

@bo0tzz commented on GitHub (Dec 7, 2024): The only difference between the two commands is the `sed` command in the middle. Do you get any output to the console for just `gunzip < "/path/to/backup/dump.sql.gz"` or for `gunzip < "/path/to/backup/dump.sql.gz" | sed "s/SELECT pg_catalog.set_config('search_path', '', false);/SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true);/g"`?
OVERLORD commented 2026-02-05 10:57:26 +03:00
Author
Owner
Copy Link

@BernardoGiordano commented on GitHub (Dec 7, 2024):

No output for the entire command with the sed pipe.

@BernardoGiordano commented on GitHub (Dec 7, 2024): No output for the entire command with the `sed` pipe.
OVERLORD commented 2026-02-05 10:57:28 +03:00
Author
Owner
Copy Link

@bo0tzz commented on GitHub (Dec 7, 2024):

And just to check the obvious, you do have sed available on your system?

@bo0tzz commented on GitHub (Dec 7, 2024): And just to check the obvious, you do have `sed` available on your system?
OVERLORD commented 2026-02-05 10:57:29 +03:00
Author
Owner
Copy Link

@mmomjian commented on GitHub (Dec 7, 2024):

Please post console output of exactly what you see for each of these commands. Restoring the DB without sed will break the reverse geocoding feature.

@mmomjian commented on GitHub (Dec 7, 2024): Please post console output of exactly what you see for each of these commands. Restoring the DB without sed will break the reverse geocoding feature.
OVERLORD commented 2026-02-05 10:57:29 +03:00
Author
Owner
Copy Link

@BernardoGiordano commented on GitHub (Dec 7, 2024):

And just to check the obvious, you do have sed available on your system?

I do (I would have got a console error otherwise).

Please post console output of exactly what you see for each of these commands. Restoring the DB without sed will break the reverse geocoding feature.

I have no way to repeat the database import now, but I can show you the output of the sed command before it is piped into postgres. diffing the original dump and the dump after it has been processed with sed gives me this patch:

root@Tower:/mnt/user/photo-library# diff dump-20241207.sql dump-after-sed.sql
58c58
< SELECT pg_catalog.set_config('search_path', '', false);
---
> SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true);
82c82
< SELECT pg_catalog.set_config('search_path', '', false);
---
> SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true);
109c109
< SELECT pg_catalog.set_config('search_path', '', false);
---
> SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true);
143c143
< SELECT pg_catalog.set_config('search_path', '', false);
---
> SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true);
165c165
< SELECT pg_catalog.set_config('search_path', '', false);
---
> SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true);
185c185
< SELECT pg_catalog.set_config('search_path', '', false);
---
> SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true);
424376c424376
< SELECT pg_catalog.set_config('search_path', '', false);
---
> SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true);
424399c424399
< SELECT pg_catalog.set_config('search_path', '', false);
---
> SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true);
@BernardoGiordano commented on GitHub (Dec 7, 2024): > And just to check the obvious, you do have `sed` available on your system? I do (I would have got a console error otherwise). > Please post console output of exactly what you see for each of these commands. Restoring the DB without sed will break the reverse geocoding feature. I have no way to repeat the database import now, but I can show you the output of the `sed` command before it is piped into postgres. `diff`ing the original dump and the dump after it has been processed with `sed` gives me this patch: ``` root@Tower:/mnt/user/photo-library# diff dump-20241207.sql dump-after-sed.sql 58c58 < SELECT pg_catalog.set_config('search_path', '', false); --- > SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true); 82c82 < SELECT pg_catalog.set_config('search_path', '', false); --- > SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true); 109c109 < SELECT pg_catalog.set_config('search_path', '', false); --- > SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true); 143c143 < SELECT pg_catalog.set_config('search_path', '', false); --- > SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true); 165c165 < SELECT pg_catalog.set_config('search_path', '', false); --- > SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true); 185c185 < SELECT pg_catalog.set_config('search_path', '', false); --- > SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true); 424376c424376 < SELECT pg_catalog.set_config('search_path', '', false); --- > SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true); 424399c424399 < SELECT pg_catalog.set_config('search_path', '', false); --- > SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true); ```
OVERLORD commented 2026-02-05 10:57:30 +03:00
Author
Owner
Copy Link

@BernardoGiordano commented on GitHub (Dec 7, 2024):

Please post console output of exactly what you see for each of these commands. Restoring the DB without sed will break the reverse geocoding feature.

Side note: geolocation feature works and also looking for locations in the UI works. What do you mean by break the reverse geocoding feature?

@BernardoGiordano commented on GitHub (Dec 7, 2024): > Please post console output of exactly what you see for each of these commands. Restoring the DB without sed will break the reverse geocoding feature. Side note: geolocation feature works and also looking for locations in the UI works. What do you mean by `break the reverse geocoding feature`?
OVERLORD commented 2026-02-05 10:57:31 +03:00
Author
Owner
Copy Link

@mmomjian commented on GitHub (Dec 7, 2024):

If you upload a new picture, does the location get correctly detected?

Closing as we can’t replicate / no logs. The sed command has been tested quite a bit and nothing here looks like an immich bug.

@mmomjian commented on GitHub (Dec 7, 2024): If you upload a new picture, does the location get correctly detected? Closing as we can’t replicate / no logs. The sed command has been tested quite a bit and nothing here looks like an immich bug.
OVERLORD commented 2026-02-05 10:57:32 +03:00
Author
Owner
Copy Link

@BernardoGiordano commented on GitHub (Dec 7, 2024):

If you upload a new picture, does the location get correctly detected?

Yes, location data still works fine.

@BernardoGiordano commented on GitHub (Dec 7, 2024): > If you upload a new picture, does the location get correctly detected? Yes, location data still works fine.
OVERLORD commented 2026-02-05 10:57:33 +03:00
Author
Owner
Copy Link

@mmomjian commented on GitHub (Dec 7, 2024):

It seems likely that the sed command actually did work, then.

@mmomjian commented on GitHub (Dec 7, 2024): It seems likely that the sed command actually did work, then.
OVERLORD commented 2026-02-05 10:57:34 +03:00
Author
Owner
Copy Link

@BernardoGiordano commented on GitHub (Dec 7, 2024):

I didn't need to run it when restoring my library though. That means that my database is still pre-sed, but location still works fine

@BernardoGiordano commented on GitHub (Dec 7, 2024): I didn't need to run it when restoring my library though. That means that my database is still pre-`sed`, but location still works fine
OVERLORD commented 2026-02-05 10:57:35 +03:00
Author
Owner
Copy Link

@DanWolfstone commented on GitHub (Nov 1, 2025):

Thank you, this helped me

@DanWolfstone commented on GitHub (Nov 1, 2025): Thank you, this helped me
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
accessibility
changelog:enhancement
changelog:security
changelog:skip
changelog:translation
cli
date-time
dependencies
documentation
external-library
format
good first issue
mobile-beta
mobile-beta
mobile-beta
needs-answer
nice to have
pull-request
Mirrored from GitHub Pull Request
 sharing
tech-debt
📱mobile
🖥️web
🗄️server
🧠machine-learning
No Label
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
OVERLORD
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: immich-app/immich#4886
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?