Immich•3w ago

Postgres errors when restoring

I am testing my backups and am seeing a new error. The restore itself seems to be working, but I'm unsure what is leading to this error. Error message (appears twice as postgres is being restored):

ERROR:  called `Result::unwrap()` on an `Err` value: 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) } } }

ERROR:  called `Result::unwrap()` on an `Err` value: 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) } } }

Command used to backup: docker exec -t immich_postgres pg_dumpall -c -U postgres > /path/to/database-backup/immich-database.sql Command used to restore:

cat /path/to/database-backup/immich-database.sql | 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=postgres

Relevant history: - This installation has been up for a very long time - about two years. - It went through the pgvector to pgvecto.rs breaking change, etc. - I restored from a backup a few months back and re-initialized the db to use data checksums. There were no errors at the time. No changes have been made to the compose file since.

20 Replies

Immich•3w ago

:wave: Hey @emielregis, Thanks for reaching out to us. Please carefully read this message and follow the recommended actions. This will help us be more effective in our support effort and leave more time for building Immich :immich:. References - Container Logs: docker compose logs docs - Container Status: docker ps -a docs - Reverse Proxy: https://immich.app/docs/administration/reverse-proxy - Code Formatting https://support.discord.com/hc/en-us/articles/210298617-Markdown-Text-101-Chat-Formatting-Bold-Italic-Underline#h_01GY0DAKGXDEHE263BCAYEGFJA Checklist I have... 1. :ballot_box_with_check: verified I'm on the latest release(note that mobile app releases may take some time). 2. :ballot_box_with_check: read applicable release notes. 3. :ballot_box_with_check: reviewed the FAQs for known issues. 4. :ballot_box_with_check: reviewed Github for known issues. 5. :ballot_box_with_check: tried accessing Immich via local ip (without a custom reverse proxy). 6. :ballot_box_with_check: uploaded the relevant information (see below). 7. :ballot_box_with_check: tried an incognito window, disabled extensions, cleared mobile app cache, logged out and back in, different browsers, etc. as applicable (an item can be marked as "complete" by reacting with the appropriate number) Information In order to be able to effectively help you, we need you to provide clear information to show what the problem is. The exact details needed vary per case, but here is a list of things to consider: - Your docker-compose.yml and .env files. - Logs from all the containers and their status (see above). - All the troubleshooting steps you've tried so far. - Any recent changes you've made to Immich or your system. - Details about your system (both software/OS and hardware). - Details about your storage (filesystems, type of disks, output of commands like fdisk -l and df -h). - The version of the Immich server, mobile app, and other relevant pieces. - Any other information that you think might be relevant. Please paste files and logs with proper code formatting, and especially avoid blurry screenshots. Without the right information we can't work out what the problem is. Help us help you ;) If this ticket can be closed you can use the /close command, and re-open it later if needed.

emielregisOP•3w ago

Docker Compose file:

services:
  immich-server:
    container_name: immich_server
    image: ghcr.io/immich-app/immich-server:${IMMICH_VERSION:-release}
    volumes:
      - ${UPLOAD_LOCATION}:/usr/src/app/upload
#      - ${THUMBS_LOCATION}:/usr/src/app/upload/thumbs
#      - ${PROFILE_LOCATION}:/usr/src/app/upload/profile
    env_file:
      - .env
    ports:
      - 2283:2283
    depends_on:
      - redis
      - database
    restart: always

  immich-machine-learning:
    container_name: immich_machine_learning
    #image: ghcr.io/immich-app/immich-machine-learning:${IMMICH_VERSION:-release}
    image: ghcr.io/immich-app/immich-machine-learning:${IMMICH_VERSION:-release}-openvino
    extends:
      file: hwaccel.ml.yml
      service: openvino
    volumes:
      - model-cache:/cache
    env_file:
      - .env
    restart: always
    #ports:
    #  - 3003:3003
    healthcheck:
      interval: 5m

services:
  immich-server:
    container_name: immich_server
    image: ghcr.io/immich-app/immich-server:${IMMICH_VERSION:-release}
    volumes:
      - ${UPLOAD_LOCATION}:/usr/src/app/upload
#      - ${THUMBS_LOCATION}:/usr/src/app/upload/thumbs
#      - ${PROFILE_LOCATION}:/usr/src/app/upload/profile
    env_file:
      - .env
    ports:
      - 2283:2283
    depends_on:
      - redis
      - database
    restart: always

  immich-machine-learning:
    container_name: immich_machine_learning
    #image: ghcr.io/immich-app/immich-machine-learning:${IMMICH_VERSION:-release}
    image: ghcr.io/immich-app/immich-machine-learning:${IMMICH_VERSION:-release}-openvino
    extends:
      file: hwaccel.ml.yml
      service: openvino
    volumes:
      - model-cache:/cache
    env_file:
      - .env
    restart: always
    #ports:
    #  - 3003:3003
    healthcheck:
      interval: 5m

  redis:
    container_name: immich_redis
    command: redis-server --loglevel warning
    image: redis:6.2-alpine@sha256:70a7a5b641117670beae0d80658430853896b5ef269ccf00d1827427e3263fa3
    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:
      - ${DATABASE_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:

  redis:
    container_name: immich_redis
    command: redis-server --loglevel warning
    image: redis:6.2-alpine@sha256:70a7a5b641117670beae0d80658430853896b5ef269ccf00d1827427e3263fa3
    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:
      - ${DATABASE_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:

.env file:

# The location where your uploaded files are stored
UPLOAD_LOCATION=/media/media-backup/backup-test-staging-area/immich
DATABASE_LOCATION=/media/media-backup/backup-test-staging-area/postgres
# The Immich version to use. You can pin this to a specific version like "v1.71.0"
IMMICH_VERSION=release
# Connection secrets for postgres and typesense. You should change these to random passwords
DB_PASSWORD=meedie-db-pass

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

REDIS_HOSTNAME=immich_redis
LOG_LEVEL=warn
#MACHINE_LEARNING_PRELOAD__CLIP="ViT-B-32__openai"
MACHINE_LEARNING_PRELOAD__CLIP="ViT-B-16-SigLIP__webli"
MACHINE_LEARNING_PRELOAD__FACIAL_RECOGNITION="buffalo_l"
MACHINE_LEARNING_MODEL_TTL=0

# The location where your uploaded files are stored
UPLOAD_LOCATION=/media/media-backup/backup-test-staging-area/immich
DATABASE_LOCATION=/media/media-backup/backup-test-staging-area/postgres
# The Immich version to use. You can pin this to a specific version like "v1.71.0"
IMMICH_VERSION=release
# Connection secrets for postgres and typesense. You should change these to random passwords
DB_PASSWORD=meedie-db-pass

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

REDIS_HOSTNAME=immich_redis
LOG_LEVEL=warn
#MACHINE_LEARNING_PRELOAD__CLIP="ViT-B-32__openai"
MACHINE_LEARNING_PRELOAD__CLIP="ViT-B-16-SigLIP__webli"
MACHINE_LEARNING_PRELOAD__FACIAL_RECOGNITION="buffalo_l"
MACHINE_LEARNING_MODEL_TTL=0

Zeus•3w ago

What database are you on and what kind of mount / device is the database on? Can you run these? https://immich.app/docs/FAQ/#how-can-i-verify-the-integrity-of-my-database I think I’ve seen this though https://github.com/immich-app/immich/issues/11949

emielregisOP•3w ago

Postgres version is docker.io/tensorchord/pgvecto-rs:pg14-v0.2.0 The database and backup both live in local btrfs filesystems. There are no checksum errors.

docker exec -it immich_postgres psql --dbname=postgres --username=postgres --command="SELECT datname, checksum_failures, checksum_last_failure FROM pg_stat_database WHERE datname IS NOT NULL"

  datname  | checksum_failures | checksum_last_failure 
-----------+-------------------+-----------------------
 template0 |                 0 | 
 template1 |                 0 | 
 immich    |                 0 | 
 postgres  |                 0 | 
(4 rows)

docker exec -it immich_postgres psql --dbname=postgres --username=postgres --command="SELECT datname, checksum_failures, checksum_last_failure FROM pg_stat_database WHERE datname IS NOT NULL"

  datname  | checksum_failures | checksum_last_failure 
-----------+-------------------+-----------------------
 template0 |                 0 | 
 template1 |                 0 | 
 immich    |                 0 | 
 postgres  |                 0 | 
(4 rows)

Yeah, I saw this. There wasn't a resolution on the GitHub thread. Again, the restore seems to have worked, but I'm concerned there may be issues down the line that I may be missing at the moment.

Immich•3w ago

Successfully submitted, a tag has been added to inform contributors. :white_check_mark:

emielregisOP•3w ago

Mert suggested replacing \r\r\n with \n but it did not get rid of the error message either. Here is the command I tried:

cat /media/media-backup/backup-test-staging-area/immich/database-backup/immich-database.sql | sed "s/SELECT pg_catalog.set_config('search_path', '', false);/SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true);/g" | sed "s.\\r\\r\\n.\\n.g"  | docker exec -i immich_postgres psql --dbname=postgres --username=postgres

cat /media/media-backup/backup-test-staging-area/immich/database-backup/immich-database.sql | sed "s/SELECT pg_catalog.set_config('search_path', '', false);/SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true);/g" | sed "s.\\r\\r\\n.\\n.g"  | docker exec -i immich_postgres psql --dbname=postgres --username=postgres

Note the additional pipe to sed: sed "s.\\r\\r\\n.\\n.g"

Zeus•3w ago

I think your sed is broken

$ echo '\r\r\n' | sed "s;\\r\\r\\n;\\n;g"
\r\r\n
$ echo '\r\r\n' | sed 's;\\r\\r\\n;\\n;g'
\n

$ echo '\r\r\n' | sed "s;\\r\\r\\n;\\n;g"
\r\r\n
$ echo '\r\r\n' | sed 's;\\r\\r\\n;\\n;g'
\n

if you use double quotes I think you would have to quadruple escape the backslash because both bash and sed will remove a layer

emielregisOP•3w ago

Sorry, my bad; I did catch that. I was aware that you have to use single quotes with sed if using sed 's...g' syntax. I did use a broken 'sed' the first time, and copied the wrong command from my bash history here. Sorry about the confusion. Anyway, I used the command you suggested above and still got the error.

cat /media/media-backup/backup-test-staging-area/immich/database-backup/immich-database.sql | sed "s/SELECT pg_catalog.set_config('search_path', '', false);/SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true);/g" | sed 's;\\r\\r\\n;\\n;g'  | docker exec -i immich_postgres psql --dbname=postgres --username=postgres

cat /media/media-backup/backup-test-staging-area/immich/database-backup/immich-database.sql | sed "s/SELECT pg_catalog.set_config('search_path', '', false);/SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true);/g" | sed 's;\\r\\r\\n;\\n;g'  | docker exec -i immich_postgres psql --dbname=postgres --username=postgres

Zeus•3w ago

What’s the new error?

emielregisOP•3w ago

The same one, somehow.

Zeus•3w ago

Your sed is still broken then

emielregisOP•3w ago

ERROR:  called `Result::unwrap()` on an `Err` value: 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) } } }

ERROR:  called `Result::unwrap()` on an `Err` value: 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) } } }

Zeus•3w ago

I recommend removing the docker exec and work with the raw database output until it’s working

emielregisOP•3w ago

Do you mean map the folder containing the dump to the docker container, then execute cat dump.sql | sed <stuff> | psql --dbname=postgres --username=postgres? I can try that. Did not work. I tried this from within the container

cat immich-database.sql | sed "s/SELECT pg_catalog.set_config('search_path', '', false);/SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true);/g" | sed 's;\\r\\r\\n;\\n;g' | psql --dbname=postgres --username=postgres

cat immich-database.sql | sed "s/SELECT pg_catalog.set_config('search_path', '', false);/SELECT pg_catalog.set_config('search_path', 'public, pg_catalog', true);/g" | sed 's;\\r\\r\\n;\\n;g' | psql --dbname=postgres --username=postgres

Same error. My bad. You probably meant to ask me to inspect the stream going to the docker exec command to figure out the correct sed. I'll give it a try.

Zeus•3w ago

It may be possible to edit the raw dump but I think this is risky

emielregisOP•3w ago

I am just testing my backups on a different Linux machine, so the real risk is minimal tbh. I think I'll edit the dump directly if needed. It's clearly these two lines in the dump that's causing the issues. I'll continue investigating this tomorrow.

cat immich-database.sql | grep indexing.hnsw -A 2

CREATE INDEX clip_index ON public.smart_search USING vectors (embedding vectors.vector_cos_ops) WITH (options='[indexing.hnsw]
m = 16
ef_construction = 300');
--
CREATE INDEX face_index ON public.face_search USING vectors (embedding vectors.vector_cos_ops) WITH (options='[indexing.hnsw]
m = 16
ef_construction = 300');

cat immich-database.sql | grep indexing.hnsw -A 2

CREATE INDEX clip_index ON public.smart_search USING vectors (embedding vectors.vector_cos_ops) WITH (options='[indexing.hnsw]
m = 16
ef_construction = 300');
--
CREATE INDEX face_index ON public.face_search USING vectors (embedding vectors.vector_cos_ops) WITH (options='[indexing.hnsw]
m = 16
ef_construction = 300');

Zeus•3w ago

Oh, it’s a true carriage return not a backslash followed by an r You probably need to remove one layer of backslashes from the single quote sed, then

emielregisOP•3w ago

It was indeed some shenanigans with carriage return and newline, though I'm not quite sure what exactly. Here is the command that worked.

cat immich-database.sql | dos2unix | 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=postgres

cat immich-database.sql | dos2unix | 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=postgres

Note the pipe to dos2unix

Zeus•3w ago

I don’t see why you would need dos2unix if this was all on Linux I’m concerned that may affect other rows As it will affect much more than just the target

emielregisOP•3w ago

You're right, using dos2unix is probably not a great workaround to fix just two instances of rogue carriage returns. Hopefully this bug can be tracked down and the root cause can be fixed eventually. The correct sed is sed 's/\r\r$//g' The restore command would be:

cat immich-database.sql | sed 's/\r\r$//g' | 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=postgres

cat immich-database.sql | sed 's/\r\r$//g' | 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=postgres

Gaming

Programming

Postgres errors when restoring

Did you find this page helpful?