Create a PostgreSQL service

Apply the following object on your namespace, as specified by its YAML description.

Example to create a PostgreSQL instance
apiVersion: vshn.appcat.vshn.io/v1
kind: VSHNPostgreSQL
metadata:
  name: pgsql-app1-prod (1)
  namespace: prod-app (2)
spec:
  parameters:
    service:
      majorVersion: "16" (3)
      pgSettings:
        timezone: Europe/Zurich (4)
    size: (5)
      plan: standard-2
    backup: (6)
      schedule: "30 23 * * *"
      retention: 12
  writeConnectionSecretToRef:
    name: postgres-creds (7)
1 Instance name
2 The namespace where the object will be created
3 PostgreSQL version. For supported versions, see our product docs
4 Specify custom PostgreSQL settings [optional]
5 Size of the PostgreSQL instance. See Plans and Sizing for more information.
6 Backup configuration, schedule standard cron: en.wikipedia.org/wiki/Cron, retention field specify how many backups should be kept
7 Secret where the connection details are provisioned. This secret shouldn’t exist before creation.
To get more information about all available configuration options, please see the API Reference

Changes to the claim spec take effect immediately on apply and bypass the maintenance window entirely.

Some changes (such as plan upgrades, replica count changes, resource sizing, or pinImageTag updates) can trigger pod restarts or short connection interruptions. Apply disruptive edits at a time that suits your workload.

StackGres

StackGres-specific features (not available in CNPG) are:

  • pgBouncer connection pooling

  • Envoy sidecar proxy for advanced monitoring

  • Dedicated Patroni resource allocation

To deploy your instance using StackGres, set spec.compositionRef.name:

apiVersion: vshn.appcat.vshn.io/v1
kind: VSHNPostgreSQL
metadata:
  name: pgsql-app1-prod
  namespace: prod-app
spec:
  compositionRef:
    name: vshnpostgres.vshn.appcat.vshn.io
  parameters:
    service:
      majorVersion: "16"
      pgBouncerSettings:
        pgbouncer:
          admin_users: postgres
  size:
    plan: standard-2
  writeConnectionSecretToRef:
    name: postgres-creds
Once set, the method of deployment cannot be changed afterwards. Similarly, existing instances cannot be switched to a different deployment method without migrating to a new instance.

Envoy Sidecar Proxy

By default, Envoy sidecar proxy is disabled on StackGres-based PostgreSQL instances. You can enable it by setting spec.parameters.service.enableEnvoy to true.

When enabled, Envoy provides an additional proxy layer that can be used for advanced monitoring and observability purposes.

apiVersion: vshn.appcat.vshn.io/v1
kind: VSHNPostgreSQL
metadata:
  name: pgsql-app1-prod
  namespace: prod-app
spec:
  compositionRef:
    name: vshnpostgres.vshn.appcat.vshn.io
  parameters:
    service:
      majorVersion: "16"
      enableEnvoy: true (1)
    size:
      plan: standard-2
  writeConnectionSecretToRef:
    name: postgres-creds
1 Enable Envoy sidecar proxy for advanced monitoring capabilities.

For more information about Envoy in StackGres, see the StackGres Envoy documentation.

Inspect your new PostgreSQL service

$  kubectl get vshnpostgresqls.vshn.appcat.vshn.io
NAME              SYNCED   READY   CONNECTION-SECRET   AGE
pgsql-app1-prod   True     True    postgres-creds      6m14s

When you see True in the READY column, it means the instance is provisioned and ready to use.

Find the connection details

The connection details are stored in a secret. You can retrieve them with the following command:

$ oc get secrets postgres-creds -o yaml

The output of the command above is a secret specification with the following structure:

apiVersion: v1
kind: Secret
metadata:
  name: postgres-creds
stringData:
  POSTGRESQL_DB: postgres (1)
  POSTGRESQL_HOST: my-postgres-example.my-cloud.com (2)
  POSTGRESQL_PASSWORD: my-secret (3)
  POSTGRESQL_PORT: 21699 (4)
  POSTGRESQL_URL: postgres://postgres:my-secret@my-postgres-example.my-cloud.com:21699/postgresql?sslmode=require (5)
  POSTGRESQL_USER: postgres (6)
  ca.crt: base64encoded(data) (7)
  tls.crt: base64encoded(data) (8)
  tls.key: base64encoded(data) (9)
1 Database name
2 Host to connect to
3 Password
4 Port
5 URL containing all necessary information to connect to the instance
6 Username
7 ca.crt to use when using sslmode=verify-full
8 tls.crt to use when connecting to instance
9 tls.key to use when connecting to instance
Not all fields apply to all providers