Can't restore database from backup - v1.102.3 [Stuck on Admin Registration page]

[conorlap]

I recently upgraded to v1.102.3 and undertook the steps to remove pgdata and add location etc:

  database:
    container_name: immich_postgres
    image: registry.hub.docker.com/tensorchord/pgvecto-rs:pg14-v0.2.0@sha256:90724186f0a3517cf6914295b5ab410db9ce23190a2d9d0b9dd6463e3fa298f0
    environment:
      POSTGRES_PASSWORD: ${DB_PASSWORD}
      POSTGRES_USER: ${DB_USERNAME}
      POSTGRES_DB: ${DB_DATABASE_NAME}
    volumes:
#remove:  - pgdata:/var/lib/postgresql/data  
+      - ${DB_DATA_LOCATION}:/var/lib/postgresql/data
    restart: always

volumes:
#remove: - pgdata:
  model-cache:

.env file
[...]
 DB_HOSTNAME=immich_postgres
 DB_USERNAME=postgres
 DB_DATABASE_NAME=immich
+DB_DATA_LOCATION=/srv/appdata/immich_db/

I had already setup immich_db_dumper and had plenty of backups. However when I go to restore the backup, when I start Immich it just brings me to the Admin Registration page "Since you are the first user on the system, you will be assigned as the Admin and are responsible for administrative tasks, and additional users will be created by you."

I have restored the backup by undertaking the following:

1. Stop all immich containers
2. Delete db files created in /srv/appdata/immich_db/
3. docker start immich_postgres
4. gunzip < /srv/appdata/immich_db_backup/last/immich-20240412-000000.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
5. start all immich containers

Can anyone suggest how to restore?

[drvenabili]

Hi @conorlap , did you fix it? I have the same issue.

Thanks!

[conorlap]

Unfortunately not, I've given up to be honest! I may come back to Immich at some point and start from scratch but I'm quite disappointed that the backup solution that I implemented based on Immich guides didn't even work!

[drvenabili]

Ah too bad, thanks. Yes I feel the same way... I'll look more into it and ping you here if I find a fix.

[mmomjian]

Please share the exact docker compose content you used for the db_dumper. Also, what logs do you get when trying to restore the database?

[drvenabili]

This fixes it for me: #9034 (comment)

For reference the dumper part of the docker-compose is pasted below. It's pretty standard. Logs are fine (i.e. the command creates and fills the DB), no error thrown.

  backup:
    container_name: immich_db_dumper
    image: prodrigestivill/postgres-backup-local
    env_file:
      - .env
    environment:
      POSTGRES_HOST: database
      POSTGRES_DB: ${DB_DATABASE_NAME}
      POSTGRES_USER: ${DB_USERNAME}
      POSTGRES_PASSWORD: ${DB_PASSWORD}
      SCHEDULE: "@daily"
      BACKUP_DIR: /db_dumps
      BACKUP_KEEP_DAYS: 7
    volumes:
      - /mnt/user/CommunityApplicationsAppdataBackup/immich_db_backup/:/db_dumps
    depends_on:
      - database

[wolfmarco]

I had the same issue. My current workaround is to copy the DB DUMP into the container and run the commands inside the container.
I attached my used backup/restore scripts. It's actually designed for TrueNAS Scale (kubernetes containers).
So you have to translate these command to docker commands.
But maybe it will help.

immich-backup-restore.zip

[bhugh]

I'm having the same issue.

The restore went well by every indication. There were logs listing various tables etc being updated in the DB, and nothing that looked like an error.

The database directory looks to be about the right size (2.6G) but when I join the postgres container and run some database queries (for example, some of them found here: https://immich.app/docs/guides/database-queries/ ) they all return nothing - zero rows.

So by all indications the DB is completely empty - at least, any tables with relevant information are empty.

All the databases and tables look to be there - just all are empty. This is checking after restoring the DB and then running immich the first time. So (as a guess) for some reason Immich didn't realize there was anything in the database and so initialized everything again.

[bhugh]

OK, I think the problem with restoring the DB files is as simple as, the database name (immich) needs to be specified in the psql command. At least - after making this change I was able to import the DB with no apparent errors, and then start immich and log in.

This is the line I successfully used - note the addition of -d immich

gunzip < "/mnt/ServerDataV_6TB/immich_setup_databases_2024-05-21/db_dumps/daily/immich-20240516-000000.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 -U postgres -d immich    # Restore Backup

Now I am having different problems - errors trying to view photos & various pages. I believe this is due to mismatches between the Immich version of my saved DBs and the current version, but we'll see. I have been traveling and so haven't had time to keep up with recent Immich upgrades - then some of the DB backups failed so I'm using one a few days older than the very latest. Altogether I'm not really sure which Immich version I was running on that day.

[drvenabili]

This fixed it for me. Thanks, @bhugh !

[conorlap]

Awesome thank you so much @bhugh !!!!! - this fixed it for me too at long last ❤️

[kvin101]

Thanks @bhugh
I lost all hope and thought of starting from zero again.
you saved me.
Thank you very much 🙏🏻.

[mmomjian]

Glad it worked! For future reference, the issue here is using an outdated version of the backup script. Note the addition of POSTGRES_CLUSTER here: https://immich.app/docs/administration/backup-and-restore/