[Bug]: Postgress lock, schema_version doesn't exist

[maxk001]

⚠️ This issue respects the following points: ⚠️

• This is a bug, not a question or a configuration issue.
• This issue is not already reported on Github (I've searched it).

Bug description

I have similar error as mentioned in this ticket, but for postgres provider so it may be wort a new ticket: ticket no 330

I tried using bolt, but I cannot as I need to do custom readiness probe cmd later to validate data in regular sql. Not doing it yet, so this extra sql is not a reason of current failure.

Steps to reproduce

1. Installed sftpgo using sagikazarmark's helm chart
2. Deployed postgress using cloudnative-pg operator.
3. Mounted all secrets from newly generates cluster secrets.

Expected behavior

Postgress is bootstraping the app

SFTPGo version

v2.5.4

Data provider

postgres

Installation method

Community Docker image

Configuration

          env:
            - name: SFTPGO_DATA_PROVIDER__DRIVER
              value: postgresql
            - name: SFTPGO_DATA_PROVIDER__NAME
              valueFrom:
                secretKeyRef:
                  name: sftpgo-postgres-app
                  key: dbname
            - name: SFTPGO_DATA_PROVIDER__HOST
              valueFrom:
                secretKeyRef:
                  name: sftpgo-postgres-app
                  key: host
            - name: SFTPGO_DATA_PROVIDER__PORT
              valueFrom:
                secretKeyRef:
                  name: sftpgo-postgres-app
                  key: port
            - name: SFTPGO_DATA_PROVIDER__USERNAME
              valueFrom:
                secretKeyRef:
                  name: sftpgo-postgres-app
                  key: username
            - name: SFTPGO_DATA_PROVIDER__PASSWORD
              valueFrom:
                secretKeyRef:
                  name: sftpgo-postgres-app
                  key: password

Relevant log output

{"level":"debug","time":"2024-05-14T13:27:16.216","sender":"dataprovider_postgresql","message":"postgres database handle created, connection string: \"host='sftpgo-postgres-rw' port='5432' dbname='app' user='app' password='[redacted]' sslmode=disable connect_timeout=10\", pool size: 0"}
{"level":"error","time":"2024-05-14T13:27:16.234","sender":"dataprovider_postgresql","message":"error preparing database query \"SELECT version from schema_version LIMIT 1\": ERROR: relation \"schema_version\" does not exist (SQLSTATE 42P01)"}
{"level":"info","time":"2024-05-14T13:27:16.234","sender":"dataprovider_postgresql","message":"creating initial database schema, version 23"}
{"level":"info","time":"2024-05-14T13:27:16.235","sender":"dataprovider_postgresql","message":"acquired database lock"}
{"level":"error","time":"2024-05-14T13:27:16.236","sender":"dataprovider_postgresql","message":"error preparing database query \"SELECT version from schema_version LIMIT 1\": ERROR: relation \"schema_version\" does not exist (SQLSTATE 42P01)"}

What are you using SFTPGo for?

Professional user, 1 person business, Medium business

Additional info

No response

[priitr-ent]

Have a look at https://stackoverflow.com/questions/67276391/why-am-i-getting-a-permission-denied-error-for-schema-public-on-pgadmin-4.
In our case it was postgres 15 related issue.

[drakkan]

Many of our commercial users use SFTPGo on k8s with postgres. We provide commercial support if required