I
Immich•10mo ago
دورايمون

Database error after upgrade

Glad that immich survived 2 upgrades in the past without issue, but today after upgrading my instance I got database container restarting loop reading this in log: 
2024-08-10 10:23:15
PostgreSQL Database directory appears to contain a database; Skipping initialization
2024-08-10 10:23:15
2024-08-10 10:23:15
2024-08-10 10:23:15.790 GMT [1] FATAL: invalid value for parameter "search_path": ""$user","
2024-08-10 10:23:15
2024-08-10 10:23:15.790 GMT [1] DETAIL: List syntax is invalid.
2024-08-10 10:23:15
PostgreSQL Database directory appears to contain a database; Skipping initialization
2024-08-10 10:23:15
2024-08-10 10:23:15
2024-08-10 10:23:15.790 GMT [1] FATAL: invalid value for parameter "search_path": ""$user","
2024-08-10 10:23:15
2024-08-10 10:23:15.790 GMT [1] DETAIL: List syntax is invalid.
5 Replies
Immich
Immich•10mo ago
:wave: Hey @دورايمون, Thanks for reaching out to us. Please follow the recommended actions below; 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 compose ps docs - Reverse Proxy: https://immich.app/docs/administration/reverse-proxy Checklist 1. :ballot_box_with_check: I have verified I'm on the latest release(note that mobile app releases may take some time). 2. :ballot_box_with_check: I have read applicable release notes. 3. :ballot_box_with_check: I have reviewed the FAQs for known issues. 4. :ballot_box_with_check: I have reviewed Github for known issues. 5. :ballot_box_with_check: I have tried accessing Immich via local ip (without a custom reverse proxy). 6. :blue_square: I have uploaded the relevant logs, docker compose, and .env files, making sure to use code formatting. 7. :ballot_box_with_check: I have 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) If this ticket can be closed you can use the /close command, and re-open it later if needed.
دورايمون
دورايمونOP•10mo ago
This is the compose for database: 
{
  "services": {
    "Immich-database": {
      "container_name": "Immich-database",
      "image": "tensorchord/pgvecto-rs:pg14-v0.2.0",
      "environment": [
        "POSTGRES_PASSWORD=xxx",
        "POSTGRES_USER=postgres",
        "POSTGRES_DB=immich",
        "PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/lib/postgresql/14/bin",
        "GOSU_VERSION=1.16",
        "LANG=en_US.utf8",
        "PG_MAJOR=14",
        "PG_VERSION=14.10-1.pgdg120+1",
        "PGDATA=/var/lib/postgresql/data"
      ],
      "labels": {
        "cosmos-auto-update": "true",
        "cosmos-force-network-mode": "cosmos-Immich-default",
        "cosmos-force-network-secured": "true",
        "cosmos-icon": "https://azukaar.github.io/cosmos-servapps-official/servapps/Immich/icon.png",
        "cosmos-persistent-env": "POSTGRES_PASSWORD, POSTGRES_USER, POSTGRES_DB",
        "cosmos-stack": "Immich",
        "cosmos-stack-main": "Immich"
      },
      "ports": [],
      "volumes": [
        {
          "Type": "volume",
          "Source": "Immich-pgdata",
          "Target": "/var/lib/postgresql/data"
        }
      ],
      "networks": {
        "Immich": {},
        "cosmos-Immich-default": {}
      },
      "routes": null,
      "restart": "always",
      "devices": null,
      "expose": [],
      "depends_on": [],
      "command": "postgres -c shared_preload_libraries=vectors.so -c search_path=\"$user\", public, vectors",
      "entrypoint": "docker-entrypoint.sh",
      "user": "postgres",
      "hostname": "6b7cfbcebbaa",
      "network_mode": "cosmos-Immich-default",
      "stop_signal": "SIGINT",
      "healthcheck": {
        "test": null,
        "interval": 0,
        "timeout": 0,
        "retries": 0,
        "start_period": 0
      }
    }
  }
}
{
  "services": {
    "Immich-database": {
      "container_name": "Immich-database",
      "image": "tensorchord/pgvecto-rs:pg14-v0.2.0",
      "environment": [
        "POSTGRES_PASSWORD=xxx",
        "POSTGRES_USER=postgres",
        "POSTGRES_DB=immich",
        "PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/lib/postgresql/14/bin",
        "GOSU_VERSION=1.16",
        "LANG=en_US.utf8",
        "PG_MAJOR=14",
        "PG_VERSION=14.10-1.pgdg120+1",
        "PGDATA=/var/lib/postgresql/data"
      ],
      "labels": {
        "cosmos-auto-update": "true",
        "cosmos-force-network-mode": "cosmos-Immich-default",
        "cosmos-force-network-secured": "true",
        "cosmos-icon": "https://azukaar.github.io/cosmos-servapps-official/servapps/Immich/icon.png",
        "cosmos-persistent-env": "POSTGRES_PASSWORD, POSTGRES_USER, POSTGRES_DB",
        "cosmos-stack": "Immich",
        "cosmos-stack-main": "Immich"
      },
      "ports": [],
      "volumes": [
        {
          "Type": "volume",
          "Source": "Immich-pgdata",
          "Target": "/var/lib/postgresql/data"
        }
      ],
      "networks": {
        "Immich": {},
        "cosmos-Immich-default": {}
      },
      "routes": null,
      "restart": "always",
      "devices": null,
      "expose": [],
      "depends_on": [],
      "command": "postgres -c shared_preload_libraries=vectors.so -c search_path=\"$user\", public, vectors",
      "entrypoint": "docker-entrypoint.sh",
      "user": "postgres",
      "hostname": "6b7cfbcebbaa",
      "network_mode": "cosmos-Immich-default",
      "stop_signal": "SIGINT",
      "healthcheck": {
        "test": null,
        "interval": 0,
        "timeout": 0,
        "retries": 0,
        "start_period": 0
      }
    }
  }
}
I use cosmos, so don't mind the labels Any help is highly appreciated 🤞 I tried re-pulling the images, restarting the server, recreating the stack Sadly I can't even exec a bash to db docker as its restarting...
bo0tzz
bo0tzz•10mo ago
"command": "postgres -c shared_preload_libraries=vectors.so -c search_path=\"$user\", public, vectors", It's complaining about this being wrong The command line we use is 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"]
دورايمون
دورايمونOP•10mo ago
Okay, I will try it now, probably the compose from cosmos repo is not updated Thanks for help @bo0tzz 
2024-08-10 10:54:04
PostgreSQL Database directory appears to contain a database; Skipping initialization
2024-08-10 10:54:04
2024-08-10 10:54:04
2024-08-10 10:54:04.043 GMT [1] FATAL: invalid value for parameter "search_path": ""\$user","
2024-08-10 10:54:04
2024-08-10 10:54:04.043 GMT [1] DETAIL: List syntax is invalid.
2024-08-10 10:54:04
PostgreSQL Database directory appears to contain a database; Skipping initialization
2024-08-10 10:54:04
2024-08-10 10:54:04
2024-08-10 10:54:04.043 GMT [1] FATAL: invalid value for parameter "search_path": ""\$user","
2024-08-10 10:54:04
2024-08-10 10:54:04.043 GMT [1] DETAIL: List syntax is invalid.
I don't fully understand the diference between cosmos-compose and stock docker-compose, but I guess it should be the same.. however it doesn't seem to pass the argumants correctly Ok so I asked chatgpt, and it solved it To address this issue, the goal is to ensure that PostgreSQL interprets the search_path parameter correctly without it being misinterpreted by the shell or Docker. Revised Command: json Copy code 
"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"
"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"
Explanation: Double Escaping: Use \" to properly escape the double quotes (") around $user so that Docker passes them correctly to PostgreSQL. Escaping the $: Use \$user to ensure that $user is recognized as a variable by PostgreSQL after Docker processes the command. No Spaces: Removed spaces around the commas in search_path to prevent issues with parsing. This format should correctly pass the search_path value to PostgreSQL without errors.
Zeus
Zeus•10mo ago
Wow that’s ugly. Glad it worked though

Did you find this page helpful?