bug: DB connection issue on Kubernetes

[SyntaxJuggler]

Describe the bug

When deploying Logto using the provided Kubernetes deployment YAML, the application consistently crashes with a timeout error when attempting to connect to the PostgreSQL database. However, manual connections to both the main database and the Logto database work fine without any issues.

Expected behavior

Logto should start successfully and connect to the PostgreSQL database without timing out when deployed using the provided Kubernetes deployment configuration.

How to reproduce?

Set up PostgreSQL:

1. Deploy PostgreSQL in the logto namespace.
2. Ensure that the database logto is created and accessible.
3. The PostgreSQL service should be accessible at logto-postgresql.logto.svc.cluster.local on port 5432.

Apply the following Kubernetes deployment YAML for Logto:

apiVersion: apps/v1
kind: Deployment
metadata:
  name: logto
  namespace: logto
spec:
  selector:
    matchLabels:
      app: logto
  template:
    metadata:
      labels:
        app: logto
    spec:
      volumes:
        - name: connectors
          emptyDir: {}
      initContainers:
        - image: ghcr.io/logto-io/logto
          name: init
          command:
            - /bin/sh
          args:
            - "-c"
            - "npm run cli connector add -- --official"
          volumeMounts:
            - name: connectors
              mountPath: /etc/logto/packages/core/connectors
      containers:
        - image: ghcr.io/logto-io/logto
          name: logto
          volumeMounts:
            - name: connectors
              mountPath: /etc/logto/packages/core/connectors
          env:
            - name: TRUST_PROXY_HEADER
              value: "1"
            - name: DB_URL
              value: "postgresql://postgres:[email protected]:5432/logto"
            # - name: PORT
            #   value: "3001"
            # - name: ADMIN_PORT
            #   value: "3002"
            - name: ENDPOINT
              value: "https://domain.com"
            - name: ADMIN_ENDPOINT
              value: "https://admin.domain.com"
          resources:
            requests:
              cpu: "250m"
              memory: "512Mi"
            limits:
              cpu: "500m"
              memory: "1Gi"

And also deploy from the docker-compose example ultimately causes the same error

apiVersion: apps/v1
kind: Deployment
metadata:
  name: logto
  namespace: logto
spec:
  replicas: 1
  selector:
    matchLabels:
      app: logto
  template:
    metadata:
      labels:
        app: logto
    spec:
      containers:
        - name: logto
          image: svhd/logto
          command:
            - sh
            - -c
            - "npm run cli db seed -- --swe && npm start"
          ports:
            - containerPort: 3001
            - containerPort: 3002
          env:
            - name: TRUST_PROXY_HEADER
              value: "1"
            - name: DB_URL
              value: "postgres://logto:[email protected]:5432/logto"
            # - name: PORT
            #   value: "3001"
            # - name: ADMIN_PORT
            #   value: "3002"
            - name: ENDPOINT
              value: "https://domain.com"
            - name: ADMIN_ENDPOINT
              value: "https://admin.domain.com"
          resources:
            requests:
              cpu: "250m"
              memory: "512Mi"
            limits:
              cpu: "500m"
              memory: "1Gi"

The following error message appears:

Defaulted container "logto" out of: logto, init (init)

> start
> cd packages/core && NODE_ENV=production node .

(node:17) ExperimentalWarning: Importing JSON modules is an experimental feature and might change at any time
(Use `node --trace-warnings ...` to show where the warning was created)
index    error Error while initializing app:
index    error Error: timeout expired
    at Timeout._onTimeout (/etc/logto/node_modules/.pnpm/[email protected]/node_modules/pg/lib/client.js:106:28)
    at listOnTimeout (node:internal/timers:581:17)
    at process.processTimers (node:internal/timers:519:7)
npm notice
npm notice New minor version of npm available! 10.8.2 -> 10.9.0
npm notice Changelog: https://github.com/npm/cli/releases/tag/v10.9.0
npm notice To update run: npm install -g [email protected]
npm notice

PS C:\Users\pozit\Nextcloud\Kubernetes\Logto> kubectl logs logto-6f57b6c998-dgb2n -n logto

> cli
> logto db seed --swe

- Create tables
info ✔ Create tables
info ✔ [default] Generated config oidc.privateKeys
info ✔ [default] Generated config oidc.cookieKeys
info ✔ [default] Seed OIDC config
info ✔ [admin] Generated config oidc.privateKeys
info ✔ [admin] Generated config oidc.cookieKeys
info ✔ [admin] Seed OIDC config
info ✔ Cloud Service Application successfully created for: default
info ✔ Created machine-to-machine applications for Management API proxy
info ✔ Initialized tenant organization template
info ✔ Assigned the proxy roles to the applications
info ✔ Created tenant organizations
info ✔ Seed data
npm notice
npm notice New minor version of npm available! 10.8.2 -> 10.9.0
npm notice Changelog: https://github.com/npm/cli/releases/tag/v10.9.0
npm notice To update run: npm install -g [email protected]
npm notice

> start
> cd packages/core && NODE_ENV=production node .

(node:45) ExperimentalWarning: Importing JSON modules is an experimental feature and might change at any time
(Use `node --trace-warnings ...` to show where the warning was created)
index    error Error while initializing app:
index    error Error: timeout expired
    at Timeout._onTimeout (/etc/logto/node_modules/.pnpm/[email protected]/node_modules/pg/lib/client.js:106:28)
    at listOnTimeout (node:internal/timers:581:17)
    at process.processTimers (node:internal/timers:519:7)

Context
Self-hosted, Logto version: Latest (as of the date of this report) Deployment method: Container (Docker image)

[SyntaxJuggler]

I was eventually able to get rid of this error by allocating more resources to the instance and trying to shorten the service path from logto-postgresql.logto.svc.cluster.local to logto-postgresql. I don't really understand what caused the error, I'm inclined to think it was the dns name. After these steps, the error no longer occurred.

[isaced]

I still encountered the problem by using a shorter service name: postgres and not limiting resource usage.

this is my docker-compose.yml:

version: "3.9"
services:
  app:
    depends_on:
      logto-postgres:
        condition: service_healthy
    image: svhd/logto:latest
    entrypoint: ["sh", "-c", "npm run cli db seed -- --swe && npm start"]
    ports:
      - 3001
      - 3002
    environment:
      - TRUST_PROXY_HEADER=1
      - DB_URL=postgresql://postgres:9blC9Ccpzho@logto-postgres:5432/logto
      - ENDPOINT
      - ADMIN_ENDPOINT

  logto-postgres:
    image: postgres:14-alpine
    user: postgres
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: 9blC9Ccpzho
    volumes:
      - postgres_data:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: 10s
      timeout: 5s
      retries: 5

volumes:
  postgres_data:

[SyntaxJuggler]

In my case, it's more about interacting with the database via Kubernetes, as it doesn't come with the underlying container, in which case there are almost always deployment issues.

I tried running your docker-compose and I got the error too, have you tried using the default docker-compose? I don't have any problems with it, maybe it's because of the parameters you added to your version.

# This compose file is for demonstration only, do not use in prod.
version: "3.9"
services:
  app:
    depends_on:
      postgres:
        condition: service_healthy
    image: svhd/logto:${TAG-latest}
    entrypoint: ["sh", "-c", "npm run cli db seed -- --swe && npm start"]
    ports:
      - 3001:3001
      - 3002:3002
    environment:
      - TRUST_PROXY_HEADER=1
      - DB_URL=postgres://postgres:p0stgr3s@postgres:5432/logto
      # Mandatory for GitPod to map host env to the container, thus GitPod can dynamically configure the public URL of Logto;
      # Or, you can leverage it for local testing.
      - ENDPOINT
      - ADMIN_ENDPOINT
  postgres:
    image: postgres:14-alpine
    user: postgres
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: p0stgr3s
    healthcheck:
      test: ["CMD-SHELL", "pg_isready"]
      interval: 10s
      timeout: 5s
      retries: 5

[isaced]

I tried the default docker-compose.yml, and the same issue exists.

[isaced]

It feels like a machine resource issue; the CPU usage spikes to 131% at the instant logto-app starts (on a 2C2G VPS). I tried killing all other applications or processes on the machine, and after doing so, it basically worked. (Though, it occasionally still fails.)

CONTAINER ID   NAME               CPU %     MEM USAGE / LIMIT     MEM %     NET I/O           BLOCK I/O         PIDS
3e430e3e8f27   logto-postgres-1   0.04%     19.89MiB / 1.638GiB   1.19%     918B / 0B         2.33MB / 43.5MB   7
1c9f5179fc69   logto-app-1        131.41%   73.29MiB / 1.638GiB   4.37%     3.49kB / 1.17kB   2.18MB / 0B       23

same #6652

[screw-hand]

The same error, but I am using docker-compose, and I updated entrypoint: ['sh', '-c', 'npm run cli db alteration deploy && npm run cli db seed -- --swe && npm start']), a new error occurred:

lobe-logto
lobe-logto     | > cli
lobe-logto     | > logto db alteration deploy
lobe-logto     |
lobe-logto     | fatal Unsupported action

I know that it may not be possible to use the shell interaction function in CI mode, but I don't know what version number to use as a parameter;
How can I find this version number?

[screw-hand]

re:#6748 (comment)

My problem has been solved; I hope this helps everyone!

logto:
    image: svhd/logto:latest
    container_name: lobe-logto
    ports:
      - '3001:3001'
      - '3002:3002'
    depends_on:
      postgresql:
        condition: service_healthy
    environment:
      - 'TRUST_PROXY_HEADER=1'
      - 'DB_URL=postgresql://postgres:uWNZugjBqixf8dxC@postgresql:5432/logto'
      - 'ENDPOINT=https://lobe-auth-api.example.com'
      - 'ADMIN_ENDPOINT=https://lobe-auth-ui.example.com'
-   entrypoint: ["sh", "-c", "npm run cli db seed -- --swe && npm start"]
+   entrypoint: ['sh', '-c', 'npm run cli db alteration deploy 1.22.0 && npm run cli db seed -- --swe && npm start']