Installing PostgreSQL

Introduction

You may have noticed that Plural sometimes deploys with the Postgres application pre-installed. This is because it is a dependency for the Plural Console, which requires a Postgres database to store state about your Plural installation.

Some applications may also have a Postgres database as a dependency, in which case a separate database would be deployed for that application. These databases can be accessed directly using the plural proxy CLI command which you can learn about here.

To fulfill these requests for a database, Plural uses the Zalando Postgres Operator. This allows us to create Postgres databases on-demand when required by an application.

Using the Postgres Operator

While we use the operator automatically to create databases for applications, you can invoke the operator yourself to manually spin up a database for your own use cases.

To do this, place the following YAML into any application directory (we recommend /postgres/helm/postgres/templates):

apiVersion: acid.zalan.do/v1
kind: postgresql
metadata:
  annotations:
    meta.helm.sh/release-name: plural
    meta.helm.sh/release-namespace: plural
  labels:
    app: postgres
    app.kubernetes.io/instance: plural
    app.kubernetes.io/managed-by: Helm
    app.kubernetes.io/name: plural
    app.kubernetes.io/version: 0.9.16-rc5
    db: plural
    helm.sh/chart: plural-0.9.37
  name: plural-plural
  namespace: plural
spec:
  clone:
    cluster: plural-clone
  databases:
    plural: plural
  numberOfInstances: 2
  postgresql:
    parameters:
      max_connections: "101"
    version: "13"
  resources:
    limits:
      cpu: "2"
      memory: 1Gi
    requests:
      cpu: 400m
      memory: 1Gi
  sidecars:
  - env:
    - name: DATA_SOURCE_URI
      value: 127.0.0.1:5432/plural?sslmode=disable
    - name: DATA_SOURCE_USER
      valueFrom:
        secretKeyRef:
          key: username
          name: postgres.plural-plural.credentials.postgresql.acid.zalan.do
    - name: DATA_SOURCE_PASS
      valueFrom:
        secretKeyRef:
          key: password
          name: postgres.plural-plural.credentials.postgresql.acid.zalan.do
    image: gcr.io/pluralsh/postgres-exporter:0.8.0
    livenessProbe:
      failureThreshold: 6
      httpGet:
        path: /
        port: http-metrics
        scheme: HTTP
      initialDelaySeconds: 5
      periodSeconds: 10
      successThreshold: 1
      timeoutSeconds: 5
    name: exporter
    ports:
    - containerPort: 9187
      name: http-metrics
      protocol: TCP
    readinessProbe:
      failureThreshold: 6
      httpGet:
        path: /
        port: http-metrics
        scheme: HTTP
      initialDelaySeconds: 5
      periodSeconds: 10
      successThreshold: 1
      timeoutSeconds: 5
  teamId: plural
  tolerations:
  - effect: NoSchedule
    key: plural.sh/pluralReserved
    operator: Exists
  users:
    plural:
    - superuser
    - createdb
  volume:
    size: 75Gi

Now run plural deploy --commit "deploy postgres" and the Postgres Operator will pick up your request and create the database for you.

Accessing the Database manually

In order to access your database directly, you'll need to get the generated password. This is located in a Kubernetes secret within the Postgres namespace.

To find the password secret, make sure that you have kubectl configured to point at the relevant cluster and run the following command:

kubectl get secrets -n postgres

The relevant secret should follow the naming convention dbuser.dbname.acid.zalando. To decrypt the secret, run the following command:

kubectl get secrets/{SECRET_NAME} --template={{.data.password}} | base64 -d

Now with the username in the name of this secret and the decrypted password, you can now access your database.

Using the Database with other Plural Applications

A common use case for spinning up a new Postgres database is using it with another Plural application, for example, Superset. To connect any application to the Postgres database on the same Kubernetes cluster, use the following address:

{POSTGRES_POD_NAME}.postgres:5432

To get the Pod name for your Postgres database, run kubectl get pods -n postgres in the relevant Kubernetes cluster.