emielregis Comments - Answer Overflow

emielregis

•Created by emielregis on 4/14/2025 in #help-desk-support

Postgres errors when restoring

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

38 replies

IImmich

•Created by emielregis on 4/14/2025 in #help-desk-support

Postgres errors when restoring

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.

38 replies

IImmich

•Created by emielregis on 4/14/2025 in #help-desk-support

Postgres errors when restoring

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

38 replies

IImmich

•Created by emielregis on 4/14/2025 in #help-desk-support

Postgres errors when restoring

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');

38 replies

IImmich

•Created by emielregis on 4/14/2025 in #help-desk-support

Postgres errors when restoring

It's clearly these two lines in the dump that's causing the issues. I'll continue investigating this tomorrow.

38 replies

IImmich

•Created by emielregis on 4/14/2025 in #help-desk-support

Postgres errors when restoring

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.

38 replies

IImmich

•Created by emielregis on 4/14/2025 in #help-desk-support

Postgres errors when restoring

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.

38 replies

IImmich

•Created by emielregis on 4/14/2025 in #help-desk-support

Postgres errors when restoring

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.

38 replies

IImmich

•Created by emielregis on 4/14/2025 in #help-desk-support

Postgres errors when restoring

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.

38 replies

IImmich

•Created by emielregis on 4/14/2025 in #help-desk-support

Postgres errors when restoring

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) } } }

38 replies

IImmich

•Created by emielregis on 4/14/2025 in #help-desk-support

Postgres errors when restoring

The same one, somehow.

38 replies

IImmich

•Created by emielregis on 4/14/2025 in #help-desk-support

Postgres errors when restoring

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

38 replies

IImmich

•Created by emielregis on 4/14/2025 in #help-desk-support

Postgres errors when restoring

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"

38 replies

IImmich

•Created by emielregis on 4/14/2025 in #help-desk-support

Postgres errors when restoring

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.

38 replies

IImmich

•Created by emielregis on 4/14/2025 in #help-desk-support

Postgres errors when restoring

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)

38 replies

IImmich

•Created by emielregis on 4/14/2025 in #help-desk-support

Postgres errors when restoring

.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

38 replies

IImmich

•Created by emielregis on 4/14/2025 in #help-desk-support

Postgres errors when restoring

  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:

38 replies

IImmich

•Created by emielregis on 4/14/2025 in #help-desk-support

Postgres errors when restoring

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

38 replies

IImmich

•Created by razormy on 2/11/2025 in #help-desk-support

iPhone to Immich: One-Way Sync Help

*Make sure you delete photos from your phone's built-in gallery app, and not Immich. Deleting from Immich will delete the copy on your server.

7 replies

IImmich

•Created by Kordek on 2/3/2025 in #help-desk-support

2 step verification

Among the many options, the easiest may be Google Oauth. You can then sign in using your google account, which you may set up with 2-factor authentication.

6 replies

Gaming

Programming