Using a PostgreSQL service

The YAML code below creates two objects: a VSHNPostgreSQL instance and a pod accessing it to show information about the instance. The latter references the postgres-creds-connection secret that will contain the access keys required to connect to the instance.

apiVersion: v1
kind: Namespace
metadata:
  name: app1-prod
  labels:
    name: app1-prod
---
apiVersion: vshn.appcat.vshn.io/v1
kind: VSHNPostgreSQL
metadata:
  name: postgresql-prod
  namespace: app1-prod
spec:
  parameters:
    service:
      majorVersion: "16"
    backup:
      schedule: '0 22 * * *'
  writeConnectionSecretToRef:
    name: postgres-creds-connection (1)
---
apiVersion: v1
kind: Pod
metadata:
  name: postgres-client
  namespace: app1-prod
spec:
  containers:
  - name: postgres
    image: postgres:16
    resources:
      limits:
        memory: "128Mi"
        cpu: "500m"
    command:
      - /bin/sh
      - -c
    args:
      - PGPASSWORD=$POSTGRESQL_PASSWORD psql "sslmode=verify-ca sslrootcert=/etc/secret-volume/ca.crt host=$POSTGRESQL_HOST port=$POSTGRESQL_PORT dbname=$POSTGRESQL_DB" -U $POSTGRESQL_USER -l
    envFrom:
      - secretRef:
          name: postgres-creds-connection (2)
    volumeMounts:
    - name: secret-volume
      readOnly: true
      mountPath: "/etc/secret-volume"
  volumes:
  - name: secret-volume
    secret:
      defaultMode: 0600
      secretName: postgres-creds-connection (2)
  restartPolicy: OnFailure
1 The operator will create a secret with the access keys to connect to the instance.
2 The pod will use the secret to connect to the instance.

Debug your instance

You can easily check the status of your instance. PostgreSQL by VSHN is composed of many resources, responsible for creating the database cluster and bucket, setting up backups and uploads, and each of these components might present issues, for example in case of a typo.

Learn about the status of your instance by running the following command. If there are any issues with your instance, they will be mentioned in the status fields.

# This example shows objects created in the example above
$ kubectl -n app1-prod describe vshnpostgresqls.vshn.appcat.vshn.io postgresql-prod
status:
  certificateConditions:
  - lastTransitionTime: "2023-03-09T10:07:41Z"
    reason: Available
    status: "True"
    type: Ready
  - lastTransitionTime: "2023-03-09T10:07:30Z"
    reason: ReconcileSuccess
    status: "True"
    type: Synced
  conditions:
  - lastTransitionTime: "2023-03-09T10:07:24Z"
    reason: ReconcileSuccess
    status: "True"
    type: Synced
  - lastTransitionTime: "2023-03-09T10:07:24Z"
    reason: Composite resource claim is waiting for composite resource to become Ready
    status: "False"
    type: Ready
  instanceNamespace: app1-prod-dsft
  localCAConditions:
  - lastTransitionTime: "2023-03-09T10:07:41Z"
    reason: Available
    status: "True"
    type: Ready
  - lastTransitionTime: "2023-03-09T10:07:30Z"
    reason: ReconcileSuccess
    status: "True"
    type: Synced
  namespaceConditions:
  - lastTransitionTime: "2023-03-09T10:07:40Z"
    reason: Available
    status: "True"
    type: Ready
  - lastTransitionTime: "2023-03-09T10:07:30Z"
    reason: ReconcileSuccess
    status: "True"
    type: Synced
  networkPolicyConditions:
  - lastTransitionTime: "2023-03-09T10:07:46Z"
    reason: Available
    status: "True"
    type: Ready
  - lastTransitionTime: "2023-03-09T10:07:36Z"
    reason: ReconcileSuccess
    status: "True"
    type: Synced
  pgclusterConditions:
  - lastTransitionTime: "2023-03-09T10:07:34Z"
    reason: Creating
    status: "False"
    type: Ready
  - lastTransitionTime: "2023-03-09T10:07:44Z"
    reason: ReconcileSuccess
    status: "True"
    type: Synced
  pgconfigConditions:
  - lastTransitionTime: "2023-03-09T10:07:43Z"
    reason: Available
    status: "True"
    type: Ready
  - lastTransitionTime: "2023-03-09T10:07:32Z"
    reason: ReconcileSuccess
    status: "True"
    type: Synced
  profileConditions:
  - lastTransitionTime: "2023-03-09T10:07:41Z"
    reason: Available
    status: "True"
    type: Ready
  - lastTransitionTime: "2023-03-09T10:07:31Z"
    reason: ReconcileSuccess
    status: "True"
    type: Synced
  secretConditions:
  - lastTransitionTime: "2023-03-09T10:07:34Z"
    message: 'observe failed: cannot resolve resource references: cannot get referenced
      resource: secrets "postgresql-prod-secret" not found'
    reason: ReconcileError
    status: "False"
    type: Synced