Someone successfully run db with cloudnative-pg Operator? #9060

RogerSik · 2024-04-24T14:24:29Z

RogerSik
Apr 24, 2024

Someone successfully run db with cloudnative-pg Operator?

Trying it with custom image but always is something else missing / not working.

---
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: immich-db
  namespace: immich
  labels:
    app: immich-db
spec:
  instances: 1

  # special image
  # https://docs.pgvecto.rs/admin/kubernetes.html
  # https://github.com/tensorchord/cloudnative-pgvecto.rs
  imageName: ghcr.io/tensorchord/cloudnative-pgvecto.rs:14-v0.2.0

  postgresql:
    parameters:
      shared_buffers: 500MB # use 25% of resources.limits
    shared_preload_libraries:
      - 'vectors.so'

  bootstrap:
    initdb:
      database: app
      owner: app
      dataChecksums: true

      postInitApplicationSQL:
        - CREATE EXTENSION IF NOT EXISTS "vector";
        - CREATE EXTENSION IF NOT EXISTS "vectors";
        - CREATE EXTENSION IF NOT EXISTS "cube";
        - CREATE EXTENSION IF NOT EXISTS "earthdistance";

Currently immich-server stucks with:

[Nest] 7  - 04/24/2024, 3:41:31 PM     LOG [EventRepository] Initialized websocket server
Migration "AddCLIPEmbeddingIndex1700713994428" failed, error: operator class "vector_cos_ops" does not exist for access method "vectors"
/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:219
            throw new QueryFailedError_1.QueryFailedError(query, parameters, err);
                  ^

QueryFailedError: operator class "vector_cos_ops" does not exist for access method "vectors"

bo0tzz · 2024-04-24T15:00:29Z

bo0tzz
Apr 24, 2024
Maintainer

You're missing the search path sql noted in the cloudnative-pgvecto.rs README.

2 replies

RogerSik Apr 24, 2024
Author

You mean

        - ALTER SYSTEM SET search_path TO "$user", public, vectors;

same error with it :/

Migration "AddCLIPEmbeddingIndex1700713994428" failed, error: operator class "vector_cos_ops" does not exist for access method "vectors"
/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:219
            throw new QueryFailedError_1.QueryFailedError(query, parameters, err);

I somewhere after some time removed it because in https://docs.pgvecto.rs/admin/kubernetes.html it wasn'tt mention.

RS185734 May 14, 2024

It seems no one can make immich work with cloud-native PostgreSQL.

RS185734 · 2024-05-14T23:34:03Z

RS185734
May 14, 2024

Same Error

QueryFailedError: operator class "vector_cos_ops" does not exist for access method "vectors"
    at PostgresQueryRunner.query (/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:219:19)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async AddCLIPEmbeddingIndex1700713994428.up (/usr/src/app/dist/migrations/1700713994428-AddCLIPEmbeddingIndex.js:13:9)
    at async MigrationExecutor.executePendingMigrations (/usr/src/app/node_modules/typeorm/migration/MigrationExecutor.js:225:17)
    at async DataSource.runMigrations (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:265:35)
    at async DatabaseRepository.runMigrations (/usr/src/app/dist/repositories/database.repository.js:179:9)
    at async /usr/src/app/dist/services/database.service.js:52:13
    at async /usr/src/app/dist/repositories/database.repository.js:187:23 {
  query: '\n' +
    '      CREATE INDEX IF NOT EXISTS clip_index ON smart_search\n' +
    '      USING hnsw (embedding vector_cosine_ops)\n' +
    '      WITH (ef_construction = 300, m = 16)',
  parameters: undefined,
  driverError: error: operator class "vector_cos_ops" does not exist for access method "vectors"
      at /usr/src/app/node_modules/pg/lib/client.js:526:17
      at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
      at async PostgresQueryRunner.query (/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:184:25)
      at async AddCLIPEmbeddingIndex1700713994428.up (/usr/src/app/dist/migrations/1700713994428-AddCLIPEmbeddingIndex.js:13:9)
      at async MigrationExecutor.executePendingMigrations (/usr/src/app/node_modules/typeorm/migration/MigrationExecutor.js:225:17)
      at async DataSource.runMigrations (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:265:35)
      at async DatabaseRepository.runMigrations (/usr/src/app/dist/repositories/database.repository.js:179:9)
      at async /usr/src/app/dist/services/database.service.js:52:13
      at async /usr/src/app/dist/repositories/database.repository.js:187:23 {

Use below

---
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: pg-immich-cluster
  namespace: cnpg-system
spec:
  imageName: ghcr.io/tensorchord/cloudnative-pgvecto.rs:16.2
  postgresql:
    shared_preload_libraries:
      - "vectors.so"
  instances: 1
  storage:
    pvcTemplate:
      accessModes:
        - ReadWriteOnce
      resources:
        requests:
          storage: 20Gi
      storageClassName: manual-immich
  monitoring:
    enablePodMonitor: true
  bootstrap:
    initdb:
      database: postgresql-immich-pgsql
      owner: postgresql-immich-pgsql
      secret:
        name: post-init-immich-pgsql-secret
      postInitApplicationSQL:
        - ALTER SYSTEM SET search_path TO "$user", public, vectors;
        - CREATE EXTENSION IF NOT EXISTS "vector";
        - CREATE EXTENSION IF NOT EXISTS "vectors";
        - CREATE EXTENSION IF NOT EXISTS "cube";
        - CREATE EXTENSION IF NOT EXISTS "earthdistance";

1 reply

dgsardina May 29, 2024

I got the same error, even tried adding the following to portInitApplicationSQL. And tried with many tags of the image without success, using either v0.2.0 and 0.2.1 of pgvecto...
@RS185734 Did you get it working?

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: immich-pg
  namespace: immich
spec:
  instances: 1
  imageName: ghcr.io/tensorchord/cloudnative-pgvecto.rs:16.2-v0.2.0

  superuserSecret:
    name: immich-pg-superuser
  enableSuperuserAccess: true

  startDelay: 30
  stopDelay: 100
  primaryUpdateStrategy: unsupervised
  primaryUpdateMethod: restart ##@@ Cannot use switchover with 1 instance

  postgresql:
    shared_preload_libraries:
      - "vectors.so"
    enableAlterSystem: true

  storage:
    size: 8Gi

  monitoring:
    enablePodMonitor: false

  bootstrap:
    initdb:
      database: immich
      owner: immich
      secret:
        name: immich-pg-user
      dataChecksums: true
      postInitApplicationSQL:
        - ALTER SYSTEM SET search_path TO "$user", public, vectors;
        - CREATE EXTENSION IF NOT EXISTS "vector";
        - CREATE EXTENSION IF NOT EXISTS "vectors";
        - CREATE EXTENSION IF NOT EXISTS "cube";
        - CREATE EXTENSION IF NOT EXISTS "earthdistance";
        - ALTER SCHEMA vectors OWNER TO "immich";
        - GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA vectors TO "immich";
        - GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "immich";

dgsardina · 2024-06-06T10:53:18Z

dgsardina
Jun 6, 2024

@RS185734 I made it work... The trick was NOT to create extension 'vector' on postInitApplicationSQL as all the commands here are executed with superuser permissions. The extension is then created by the migrations on the regular app (or immich) user.

apiVersion: postgresql.cnpg.io/v1                                                                                                                                                                                                                                                       
kind: Cluster
metadata:
  name: immich-pg
  namespace: immich
spec:
  instances: 1
  imageName: ghcr.io/tensorchord/cloudnative-pgvecto.rs:16.3-v0.2.1
  superuserSecret:
    name: immich-pg-superuser
  enableSuperuserAccess: true
  startDelay: 30
  stopDelay: 100
  primaryUpdateStrategy: unsupervised
  primaryUpdateMethod: restart ##@@ Cannot use switchover with 1 instance
  postgresql:
    shared_preload_libraries:
      - "vectors.so"
    enableAlterSystem: true
  storage:
    size: 8Gi
  monitoring:
    enablePodMonitor: false
  bootstrap:
    initdb:
      database: immich
      owner: immich
      secret:
        name: immich-pg-user
      dataChecksums: true
      postInitApplicationSQL:
        - ALTER SYSTEM SET search_path TO "$user", public, vectors;
        - SET search_path TO "$user", public, vectors;
        - CREATE EXTENSION IF NOT EXISTS "vectors";
        - CREATE EXTENSION IF NOT EXISTS "cube";
        - CREATE EXTENSION IF NOT EXISTS "earthdistance";
        - ALTER SCHEMA vectors OWNER TO "immich";
        - GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA vectors TO "immich";
        - GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "immich";
        # - CREATE EXTENSION IF NOT EXISTS "vector";
        # - ALTER ROLE "immich" WITH "superuser";

1 reply

RogerSik Jun 7, 2024
Author

This works. Thanks for sharing!

RS185734 · 2024-06-11T12:28:06Z

RS185734
Jun 11, 2024

Thanks, @dgsardina For the manifests,

For someone who is importing PostgreSQL from the immich chart

use below

      postInitApplicationSQL:
        - ALTER SYSTEM SET search_path TO "$user", public, vectors;
        - SET search_path TO "$user", public, vectors;
        - CREATE EXTENSION IF NOT EXISTS "vectors";
        - ALTER SCHEMA vectors OWNER TO "postgresql_immich_pgsql";
        - GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA vectors TO "postgresql_immich_pgsql";
        - GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "postgresql_immich_pgsql";
        - CREATE EXTENSION IF NOT EXISTS cube WITH SCHEMA pg_catalog;
        - CREATE EXTENSION IF NOT EXISTS earthdistance WITH SCHEMA pg_catalog;

0 replies

bdsoha · 2024-06-25T09:41:26Z

bdsoha
Jun 25, 2024

@RS185734 I was not able to backup the native bitnami/postgresql that comes with the chart and restore it on the new cnpg cluster instance.

Any suggestions?

0 replies

kriegalex · 2025-02-12T05:44:13Z

kriegalex
Feb 12, 2025

On my side, I checked the Immich documentation, and it says:

Typically Immich expects superuser permission in the database, which you can grant by running ALTER USER WITH SUPERUSER; at the psql console. If you prefer not to grant superuser permissions, follow the instructions in the next section.

So, I added the line to grant superuser rights. To be safe, I left the two lines required by pgvecto.rs that I already had. My instance is now working. Previously, it complained about permissions issues while creating the vectors extension.

From the solution above, I see that I was probably missing the ALTER and GRANT requests, but this setup is more in line with the official documentation. This database should not be reachable outside the cluster, at least it doesn't matter in a homelab environment, I guess.

My Cluster bootstrap section (CloudNativePG) :

# Database configuration
  bootstrap:
    initdb:
      database: immich
      owner: immich
      secret:
        name: immich-db-secret
      postInitSQL:
        - ALTER USER immich WITH SUPERUSER;
      postInitApplicationSQL:
        - ALTER SYSTEM SET search_path TO "$user", public, vectors;
        - CREATE EXTENSION IF NOT EXISTS "vectors";

I leave my error message here, so others can find this discussion thread:

[Nest] 7 - 02/12/2025, 4:39:14 AM FATAL [Microservices:DatabaseService] Failed to activate pgvecto.rs extension.
Please ensure the Postgres instance has pgvecto.rs installed.

If the Postgres instance already has pgvecto.rs installed, Immich may not have the necessary permissions to activate it.
In this case, please run 'CREATE EXTENSION IF NOT EXISTS vectors' manually as a superuser.
See https://immich.app/docs/guides/database-queries for how to query the database.

Alternatively, if your Postgres instance has pgvector, you may use this instead by setting the environment variable 'DB_VECTOR_EXTENSION=pgvector'.
Note that switching between the two extensions after a successful startup is not supported.
The exception is if your version of Immich prior to upgrading was 1.90.2 or earlier.
In this case, you may set either extension now, but you will not be able to switch to the other extension following a successful startup.
microservices worker error: QueryFailedError: permission denied to create extension "vectors", stack: QueryFailedError: permission denied to create extension "vectors"
at PostgresQueryRunner.query (/usr/src/app/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:219:19)
at process.processTicksAndRejections (node:internal/process/task_queues:105:5)
at async DataSource.query (/usr/src/app/node_modules/typeorm/data-source/DataSource.js:350:20)
at async DatabaseRepository.createExtension (/usr/src/app/dist/repositories/database.repository.js:85:9)
at async DatabaseService.createExtension (/usr/src/app/dist/services/database.service.js:121:13)
at async /usr/src/app/dist/services/database.service.js:82:17
at async /usr/src/app/dist/repositories/database.repository.js:211:23
microservices worker exited with code 1
Killing api process

0 replies

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Someone successfully run db with cloudnative-pg Operator? #9060

{{title}}

Replies: 6 comments 4 replies

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

Select a reply

Someone successfully run db with cloudnative-pg Operator? #9060

Replies: 6 comments · 4 replies

bo0tzz Apr 24, 2024 Maintainer

RogerSik Apr 24, 2024 Author

RogerSik Jun 7, 2024 Author

Replies: 6 comments 4 replies

bo0tzz
Apr 24, 2024
Maintainer

RogerSik Apr 24, 2024
Author

RogerSik Jun 7, 2024
Author