helmchart create-database error "unable to connect to DB, tried default DB names: postgres,defaultdb"

Expected Behavior

I expected it to use the database name that's configured in the helm chart.

Here is the init container that's created from the helm chart

- command:
    - temporal-sql-tool
    - create-database
    env:
    - name: SQL_PLUGIN
      value: postgres12
    - name: SQL_HOST
      value: 10.63.7.94
    - name: SQL_PORT
      value: "5432"
    - name: SQL_DATABASE
      value: citus
    - name: SQL_USER
      value: citus
    - name: SQL_PASSWORD
      valueFrom:
        secretKeyRef:
          key: password
          name: temporal-default-store
    image: temporalio/admin-tools:1.24.2-tctl-1.18.1-cli-0.13.0
    imagePullPolicy: IfNotPresent
    name: create-default-store
    resources: {}
    terminationMessagePath: /dev/termination-log
    terminationMessagePolicy: File
    volumeMounts:
    - mountPath: /var/run/secrets/kubernetes.io/serviceaccount
      name: kube-api-access-lxgvw
      readOnly: true

Here is the yaml used for the helm chart:

values:
    cassandra:
      enabled: false
    prometheus:
      enabled: false
    elasticsearch:
      enabled: false
    grafana:
      enabled: false
    server:
      config:
        persistence:
          default:
            driver: "sql"
            sql:
              driver: "postgres12"
              host: 0.0.0.0 # omited
              port: 5432
              database: citus
              user: citus
              password: blah #omited
              maxConns: 20
              maxConnLifetime: "1h"

          visibility:
            driver: "sql"

            sql:
              driver: "postgres12"
              host: 0.0.0.0 #omited
              port: 5432
              database: citus
              user: citus
              password: blah #omited
              maxConns: 20
              maxConnLifetime: "1h"

Actual Behavior

I expected it to use the citus database referenced.

> kubectl logs temporal-schema-ttjv2 -c create-default-store
2024-08-23T10:04:50.384Z    ERROR   Unable to create SQL database.  {"error": "unable to connect to DB, tried default DB names: postgres,defaultdb, errors: [pq: no pg_hba.conf entry for host \"fd40:6eea:20:81c1:8220:100:a45:1008\", user \"citus\", database \"postgres\", no encryption pq: no pg_hba.conf entry for host \"fd40:6eea:20:81c1:8220:100:a45:1008\", user \"citus\", database \"defaultdb\", no encryption]", "logging-call-at": "handler.go:94"}

Thank you for reporting this issue.

The temporal-sql-tool is trying to connect to the default databases postgres or defaultdb instead of the database citus configured in the helm chart.

The issue appears to be related to the SQL_DATABASE environment variable not being passed correctly to the temporal-sql-tool container.

Could you please confirm the following:

Is the SQL_DATABASE environment variable set correctly in the helm chart?
Are there any other helm chart settings that could be affecting the environment variable?

Once we have this information, we will be able to assist you in finding a solution.

Thanks for reporting this issue. It looks like you're encountering a connectivity issue with the SQL database.

The temporal-sql-tool create-database command expects the SQL_HOST and SQL_PORT environment variables to be set correctly, but your init container seems to be missing these environment variables.

Can you please confirm the following:

SQL_HOST: The IP address of your PostgreSQL server.
SQL_PORT: The port number on which your PostgreSQL server is listening.

You should set these environment variables in your init container configuration.

- command:
    - temporal-sql-tool
    - create-database
  env:
    - name: SQL_PLUGIN
      value: postgres12
    - name: SQL_HOST
      value: <your_postgres_ip>  # Replace with the actual IP address of your PostgreSQL server
    - name: SQL_PORT
      value: <your_postgres_port> # Replace with the actual port number
    - name: SQL_DATABASE
      value: citus
    - name: SQL_USER
      value: citus
    - name: SQL_PASSWORD
      valueFrom:
        secretKeyRef:
          key: password
          name: temporal-default-store
    image: temporalio/admin-tools:1.24.2-tctl-1.18.1-cli-0.13.0
    imagePullPolicy: IfNotPresent
    name: create-default-store
    resources: {}
    terminationMessagePath: /dev/termination-log
    terminationMessagePolicy: File
    volumeMounts:
    - mountPath: /var/run/secrets/kubernetes.io/serviceaccount
      name: kube-api-access-lxgvw
      readOnly: true

For more information on setting up your SQL database, you can refer to the Temporal Service configuration reference.

Thanks for reporting this issue.

The issue you're experiencing is due to the SQL_HOST being set to 10.63.7.94 in the init container, while the host field in the Helm chart is set to 0.0.0.0. This means the init container is trying to connect to the database on a different host than the Temporal server.

You can fix this by either:

Setting the SQL_HOST to 0.0.0.0 in the init container: This will make the init container connect to the database on the same host as the Temporal server.
Setting the host field in the Helm chart to 10.63.7.94: This will make the Temporal server connect to the database on the same host as the init container.

To make the init container connect to the database on the same host as the Temporal server, you can update the SQL_HOST to the following in your Helm chart:

values:
    cassandra:
      enabled: false
    prometheus:
      enabled: false
    elasticsearch:
      enabled: false
    grafana:
      enabled: false
    server:
      config:
        persistence:
          default:
            driver: "sql"
            sql:
              driver: "postgres12"
              host: 0.0.0.0 # omited
              port: 5432
              database: citus
              user: citus
              password: blah #omited
              maxConns: 20
              maxConnLifetime: "1h"

          visibility:
            driver: "sql"

            sql:
              driver: "postgres12"
              host: 0.0.0.0 #omited
              port: 5432
              database: citus
              user: citus
              password: blah #omited
              maxConns: 20
              maxConnLifetime: "1h"

And then, in your init container:

- command:
    - temporal-sql-tool
    - create-database
    env:
    - name: SQL_PLUGIN
      value: postgres12
    - name: SQL_HOST
      value: 0.0.0.0 
    - name: SQL_PORT
      value: "5432"
    - name: SQL_DATABASE
      value: citus
    - name: SQL_USER
      value: citus
    - name: SQL_PASSWORD
      valueFrom:
        secretKeyRef:
          key: password
          name: temporal-default-store
    image: temporalio/admin-tools:1.24.2-tctl-1.18.1-cli-0.13.0
    imagePullPolicy: IfNotPresent
    name: create-default-store
    resources: {}
    terminationMessagePath: /dev/termination-log
    terminationMessagePolicy: File
    volumeMounts:
    - mountPath: /var/run/secrets/kubernetes.io/serviceaccount
      name: kube-api-access-lxgvw
      readOnly: true

This should fix the issue and allow the init container to successfully connect to the database. Let me know if you have any other questions.

dhiaayachi / temporal

helmchart create-database error "unable to connect to DB, tried default DB names: postgres,defaultdb" #67

Expected Behavior

Actual Behavior