Can't run immich because PSQL broke itself

Help
1

Trying to run immich, and psql seems to break itself:

$ journalctl -xeu postgresql
Oct 04 09:20:15 host postgres[234150]: [234150] STATEMENT:  SELECT idx_status FROM pg_vector_index_stat WHERE indexname = $1
Oct 04 09:20:21 host postgres[234236]: [234236] ERROR:  pgvecto.rs: The extension is upgraded so all index files are outdated.
Oct 04 09:20:21 host postgres[234236]:         ADVICE: Delete all index files. Please read `https://docs.pgvecto.rs/admin/upgrading.html`
O

Based on Immich 1.95.1 Upgrade - Postgresql Errors · Issue #7327 · immich-app/immich · GitHub and psql’s own docs (!) I’m supposed to run SELECT pgvectors_upgrade();

$ sudo psql -U postgres -c 'SELECT pgvectors_upgrade();'
psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "postgres"

$ sudo -u postgres psql -c 'SELECT pgvectors_upgrade();'
ERROR:  function pgvectors_upgrade() does not exist
LINE 1: SELECT pgvectors_upgrade();
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

My immich-related config is simply

  services = {
    immich = {
      enable = true;
      host = "0.0.0.0";
      openFirewall = true;
    };
  };
2

So I was running it against the wrong db, but still this doesn’t fix anything.

$ sudo -u postgres psql -d immich -c 'SELECT pgvectors_upgrade();'
 pgvectors_upgrade 
-------------------
 
(1 row)

$ journalctl -xeu postgresql
Oct 04 09:50:12 host postgres[229934]: [229934] LOG:  checkpoint starting: time
Oct 04 09:50:13 host postgres[229934]: [229934] LOG:  checkpoint complete: wrote 4 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.401 s, sync=0.005 s, total=0.416 s; sync files=3, longest=0.005 s, average=0.002 s; distance=7 kB, estimate=7 kB; lsn=0/2CF6D858, redo lsn=0/2CF6D820
Oct 04 09:50:15 host postgres[295453]: [295453] ERROR:  pgvecto.rs: The extension is upgraded so all index files are outdated.
Oct 04 09:50:15 host postgres[295453]:         ADVICE: Delete all index files. Please read `https://docs.pgvecto.rs/admin/upgrading.html`
Oct 04 09:50:15 host postgres[295453]: [295453] STATEMENT:  SELECT idx_status FROM pg_vector_index_stat WHERE indexname = $1
3

Turns out I needed to restart the psql service, then run the below, then restart it again

$ sudo -u postgres psql -d immich                                 
psql (16.4)
Type "help" for help.

immich=# SELECT
        I.relname AS indexname
    FROM pg_index X JOIN
         pg_class I ON I.oid = X.indexrelid JOIN
         pg_am A ON A.oid = I.relam
    WHERE A.amname = 'vectors';
 indexname  
------------
 clip_index
 face_index
(2 rows)

immich=# REINDEX INDEX clip_index;
REINDEX
immich=# REINDEX INDEX face_index;
REINDEX
2 Likes