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)
      pgBouncerSettings: (4)
        pgbouncer:
          admin_users: postgres
      pgSettings:
        timezone: Europe/Zurich (5)
    size: (6)
      plan: standard-4
    backup: (7)
      schedule: "30 23 * * *"
      retention: 12
  writeConnectionSecretToRef:
    name: postgres-creds (8)

1	Instance name
2	The namespace where the object will be created
3	PostgreSQL version. For supported versions, see our product docs
4	Specify additional configuration to the pgBouncer instance [optional]. See documentation
5	Specify custom PostgreSQL settings [optional]
6	Size of the PostgreSQL instance. See Plans and Sizing for more information.
7	Backup configuration, `schedule` standard cron: en.wikipedia.org/wiki/Cron, `retention` field specify how many backups should be kept
8	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

Envoy Sidecar Proxy

By default, Envoy sidecar proxy is disabled on 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:
  parameters:
    service:
      majorVersion: "16"
      enableEnvoy: true (1)
    size:
      plan: standard-4
  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.

CloudNativePG

Integration of this provider is under active development, it is not yet intended for use in production.

As an alternative to StackGres, the way PostgreSQL is currently deployed, you can choose to deploy your service using CloudNativePG (CNPG for short) instead. Deployments based on CNPG are currently only meant for evaluation purposes and therefore only support a limited amount of features compared to the StackGres based offering.

Unavailable features (at the moment) are: - Scaling - Backups - DB management

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

apiVersion: vshn.appcat.vshn.io/v1
kind: VSHNPostgreSQL
metadata:
  name: pgsql-app1-prod
  namespace: prod-app
spec:
  compositionRef:
    name: vshnpostgrescnpg.vshn.appcat.vshn.io

Once set, the method of deployment cannot be changed afterwards. Similarly, existing instances cannot be switched over to CNPG without migrating to a new instance.

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