Backup and Restore - automated Dump probably incompatible

[chr-ftw]

I followed this guide to migrate to another hardware.
https://immich.app/docs/administration/backup-and-restore/

I was unable to import data base dumps that have been created automatically by using "immich_db_dumper" container.
Many errors like this during import:

...
ERROR:  relation "smart_search" already exists
...
ERROR:  multiple primary keys for table "audit" are not allowed
...

Issue was resovled by manually creating a database dump, so I am good.

I don't know if thats a general issue or just on me.

[aviv926]

Did you follow the recovery instructions? It looks like you run immich before doing the restore. Can be?

[chr-ftw]

Fresh install - starting a compose (portainer stack) and doing nothing else. I deleted persistent volumes before (docker volume rm ... ).
What do you consider "not running immich" ? Not even starting the containers ? ... I would not even know how to deploy the stack without starting it.

I was able to reproduce the issue:

  backup:
    container_name: immich_db_dumper
    image: prodrigestivill/postgres-backup-local
    env_file:
      - stack.env
    environment:
      POSTGRES_HOST: database
      POSTGRES_DB: ${DB_DATABASE_NAME}
      POSTGRES_USER: ${DB_USERNAME}
      POSTGRES_PASSWORD: ${DB_PASSWORD}
      SCHEDULE: "@hourly"
      BACKUP_DIR: /db_dumps
    volumes:
      - ${DB_DUMP_LOCATION}:/db_dumps
    depends_on:
      - database

and

gunzip < immich-20240402-190001.sql.gz | docker exec -i immich_postgres psql -U postgres -d immich

errors like

ALTER SCHEMA
ERROR:  schema "vectors" already exists
...
ERROR:  function "f_concat_ws" already exists with same argument types
...
ERROR:  relation "albums_assets_assets" already exists
...
ERROR:  insert or update on table "albums" violates foreign key constraint "FK_b22c53f35ef20c28c21637c85f4"
DETAIL:  Key (ownerId)=(b235105d-8a25-4e97-b641-693f7be19f1e) is not present in table "users".

What does work for me is this:

  backup:
    container_name: immich_db_dumper
    image: prodrigestivill/postgres-backup-local
    env_file:
      - stack.env
    environment:
      POSTGRES_HOST: database
      POSTGRES_DB: ${DB_DATABASE_NAME}
      POSTGRES_USER: ${DB_USERNAME}
      POSTGRES_PASSWORD: ${DB_PASSWORD}
      BACKUP_SUFFIX: ".out"
      BACKUP_DIR: /db_dumps
      POSTGRES_EXTRA_OPTS: "--clean"
      SCHEDULE: "@hourly" #"@daily"
      POSTGRES_CLUSTER: TRUE
      BACKUP_KEEP_DAYS: 7
      BACKUP_KEEP_WEEKS: 4
      BACKUP_KEEP_MONTHS: 6
      #HEALTHCHECK_PORT=8080
    volumes:
      - ${DB_DUMP_LOCATION}:/db_dumps
    depends_on:
      - database

cat immich-latest.out | docker exec -i immich_postgres psql -U postgres -d immich

no erros on database restore

I'm not an expert, I just work my way around and read the docs ... I guess it's maybe still my fault but maybe someone else should test restoring database dumps as well.

[aviv926]

Well that's interesting, if the create option is not available in portainer it's something that should be added to the documentation.

[mertalev]

You can start only the Postgres container and not any other container. The important thing is just that the server doesn't talk to postgres until after the restore is done.

[chr-ftw]

You can start only the Postgres container and not any other container. The important thing is just that the server doesn't talk to postgres until after the restore is done.

Also as @aviv926 quoted in his comment it is clear not to start the container before importing.

So I understand what you write but I don see how that is possible in portainer. It "deploys" the stack aka. compose file and immediately starts all containers. Only after that I have control to stop each container.

Initially I assumed it's due to the behavior of prodrigestivill/postgres-backup-local.
As expained here it uses pg_dump with "-Z1" as default argument. Whereas immich backup and restore doku uses pg_dumpall and "--clean" as argument.

I see that's an user issue and not the responsibility of immich, but I also think many enjoy the convenience of portainer (and maybe never tested a db restore in portainer).

The only thing I am confused about now: After a fresh install with portainer, is importing a db-backup that was created using "--clean" a save alternative? I assume yes as everything that is now overwritten should not have been there in first place.
If so, I'd suggest updating the doc and include "POSTGRES_EXTRA_OPTS: "-Z1 --clean" to the given docker compose file.

[aviv926]

I don't think it's a problem, since the documentation also uses the -c value as you mentioned.
I suppose it would be helpful to add what you suggested to the documentation.
@martabal do you have any thoughts on this?

[cpuks]

I think current instructions provided are wrong as without -Z1 in POSTGRES_EXTRA_OPTS it does produce sql dump WITHOUT compression, so file extension should be .sql without .gz - therefore restore process is not using gunzip command anymore.
There are two options:

1. add variable BACKUP_SUFFIX=.sql
2. add variable -Z1 inside POSTGRES_EXTRA_OPTS (so it'll look like POSTGRES_EXTRA_OPTS=-Z1 --clean --if-exists)
   Otherwise, users are misled that it's a simple copy&paste job to restore while gunzip < immich_db_dump.sql.gz will produce errors...

EDIT

Even manual backup solution mismatch as after pg_dumpall there's gzip pipe so it's not apple to apple if there won't be -Z1 in POSTGRES_EXTRA_OPTS, manual backup produces sql dump then gzip it to provide dump.sql.gz file while docker solution produces uncompressed sql dump with .gz extension and causes error during restore process.

[chr-ftw]

it appears --clean or -c is relevant
It is present in the documentation, but not in the docker container

this also works for me:

 backup:
    container_name: immich_db_dumper
    image: prodrigestivill/postgres-backup-local
    env_file:
      - stack.env
    environment:
      POSTGRES_HOST: database
      POSTGRES_DB: ${DB_DATABASE_NAME}
      POSTGRES_USER: ${DB_USERNAME}
      POSTGRES_PASSWORD: ${DB_PASSWORD}
      SCHEDULE: "@daily" #"* * * * *" #"@hourly"
      BACKUP_DIR: /db_dumps
      POSTGRES_EXTRA_OPTS: "-Z1 --clean"
    volumes:
      - ${DB_DUMP_LOCATION}:/db_dumps
    depends_on:
      - database