Unable to deploy backstage on GKE cluster with CloudSQL Postgres database

Question

I am currently trying to deploy backstage to a GKE cluster using a postgres database in CloudSQL. I have deployed a sidecar to access the cloudsql database in my deployment and I have a deployment for the docker container. The backend deployment is unable to deploy because of the following error:

{"level":"info","message":"Performing database migration","plugin":"catalog","service":"backstage","type":"plugin"}
Backend failed to start up KnexTimeoutError: Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?
    at Client_PG.acquireConnection (/app/node_modules/knex/lib/client.js:307:26)
    at async Runner.ensureConnection (/app/node_modules/knex/lib/execution/runner.js:287:28)
    at async Runner.run (/app/node_modules/knex/lib/execution/runner.js:30:19)
    at async listCompleted (/app/node_modules/knex/lib/migrations/migrate/migration-list-resolver.js:12:3)
    at async Promise.all (index 1)
    at async Migrator.latest (/app/node_modules/knex/lib/migrations/migrate/Migrator.js:63:29)
    at async applyDatabaseMigrations (/app/node_modules/@backstage/plugin-catalog-backend/dist/index.cjs.js:2020:3)
    at async CatalogBuilder.build (/app/node_modules/@backstage/plugin-catalog-backend/dist/index.cjs.js:4095:7)
    at async createPlugin$4 (/app/packages/backend/dist/index.cjs.js:84:40)
    at async main (/app/packages/backend/dist/index.cjs.js:276:29) {
  sql: undefined,
  bindings: undefined
}

This is my deployment:

apiVersion: apps/v1
kind: Deployment
metadata:
  name: backstage-deployment
  namespace: backstage
spec:
  replicas: 1
  selector:
    matchLabels:
      app: backstage
  template:
    metadata:
      labels:
        app: backstage
    spec:
      serviceAccountName: backstage-sa
      containers:
        - name: backstage
          image: us-central1-docker.pkg.dev/px-mike-project-hje/backstage/backstage
          imagePullPolicy: Always
          ports:
            - name: backstage
              containerPort: 7007
          env:
          - name: POSTGRES_USER
            valueFrom:
              secretKeyRef:
                name: pg-db-ref
                key: username
          - name: POSTGRES_PASSWORD
            valueFrom:
              secretKeyRef:
                name: pg-db-ref
                key: password
          - name: POSTGRES_HOST
            valueFrom:
              secretKeyRef:
                name: pg-db-ref
                key: endpoint

        - name: cloud-sql-proxy
          image: gcr.io/cloudsql-docker/gce-proxy:1.28.0
          command:
            - "/cloud_sql_proxy"
            - "-ip_address_types=PRIVATE"
            - "-log_debug_stdout"
            - "-instances=px-mike-project-hje:us-central1:pg-database=tcp:5432"
          securityContext:
            runAsNonRoot: true
          resources:
            requests:
              memory: "2Gi"
              cpu: "1"

Here is my app-config for my database:

  database:
    client: pg
    connection:
      host: ${POSTGRES_HOST}
      port: 5432
      user: ${POSTGRES_USER}
      password: ${POSTGRES_PASSWORD}
    database: pg-database
    ensureExists: false
    pluginDivisionMode: schema
    knexConfig:
      pool:
        min: 15
        max: 30
        acquireTimeoutMillis: 60000
        idleTimeoutMillis: 60000
      acquireConnectionTimeout: 10000
    plugin: 
      # catalog:
        # connection:
        #   database: pg-database
      auth:
        client: better-sqlite3
        connection: ':memory:'

I've tried to run the docker image locally and was successful. I am stuck running the deployment with the cloudsql postgres database successfully.

Answer 1

First, a cloud sql side car proxy is needed to access the database via gke cluster. Follow this tutorial and add to the deployment with this link: https://cloud.google.com/sql/docs/postgres/connect-kubernetes-engine

Second, Backstage uses knex for database management so the databases with in the cloudsql postgres database will be created via Backstage. Change the app config to the following (can change to env variables from deployment):

  database:
    client: pg
    connection:
      host: localhost #${POSTGRES_HOST}
      port: 5432
      user: postgres # ${POSTGRES_USER}
      password: password # ${POSTGRES_PASSWORD}

The rest of the database configuration should be deleted.