PostgreSQL

On this page you can learn

• how to set up a centralized PostgreSQL instance in Gimlet that your applications can share
• and how to provision a logical database for your applicaton inside this shared PostgreSQL instance

Reasons to centralize

Having a centralized PostgreSQL that your applications share means more constraints typically, but you can also delegate the operational responsibilities to the maintainer of the shared instance.

In a small company that maintainer may also be you, but using a centralized instance instead of multiple small ones will ease your maintenance burden.

Operational responsibilities that you can optimize by using a shared instance:

• backups
• monitoring, alerting, availability
• cost

Running a database is an operations heavy task. Providing high-availability and performing backups reliably on a regular cadence is something you should not take lightly. If you can outsource it to your cloud provider, we recommend to consider it.

Use an Existing PostgreSQL

Gimlet allows you to specify the details of an existing PostgreSQL instance. This can be a cloud managed instance or something that you manage outside of Gimlet.

Working with existing PostgreSQL instances are the same as working with a containerized PostgreSQL instances running on Kubernetes. Without the maintenance part naturally. Jump to provision the application credentials.

Deploy a Containerized PostgreSQL on Kubernetes

In this section you will deploy a PostgreSQL version 16 instance that will serve as a centralized instance that your applications can use.

You put these manifests to the gitops-<environment-name>-infra repository by convention.

The manifests configure PostgreSQL in a standalone configuration without replicas.

# helm-repositories/bitnami.yaml
---
apiVersion: source.toolkit.fluxcd.io/v1
kind: HelmRepository
metadata:
  name: bitnami
  namespace: infrastructure
spec:
  interval: 60m
  url: https://charts.bitnami.com/bitnami

# helm-releases/postgresql.yaml
---
apiVersion: helm.toolkit.fluxcd.io/v2
kind: HelmRelease
metadata:
  name: redis
  namespace: infrastructure
spec:
  interval: 60m
  releaseName: postgresql
  chart:
    spec:
      chart: postgresql
      version: 15.5.31
      sourceRef:
        kind: HelmRepository
        name: bitnami
      interval: 10m
  values:
    auth:
      existingSecret: postgresql-credentials
    architecture: standalone
    tls:
      enabled: true
      autoGenerated: true
    persistence:
      enabled: true
      size: 8Gi
    persistentVolumeClaimRetentionPolicy:
      enabled: true
      whenScaled: Retain
      whenDeleted: Retain

# manifests/postgresql-credentials.yaml
---
apiVersion: v1
kind: Secret
metadata:
  name: postgresql-credentials
  namespace: infrastructure
type: Opaque
stringData:
  postgres-password: super-secret-password

Verify PostgreSQL

$ kubectl exec -it postgresql-0 -- bash
Defaulted container "postgresql" out of: postgresql, copy-certs (init)
$ I have no name!@postgresql-0:/$ psql -U postgres
Password for user postgres: 
psql (16.4)
Type "help" for help.

postgres=#

Provision Application Credentials

Your application needs a dedicated schema and credentials.

To provision the logical databse with the required credentials, you are going to add manifests to your application's Gimlet manifest.

Terraform workflow to provision logical database with credentials

The bellow addition to your application's Gimlet manifest uses a Terraform module to provision a database and user in the centralized PostgreSQL instance.

Upon deployment, the Terraform module is executed and the database is provisioned in a couple of minutes. Your application will eventually settle in running state.

# .gimlet/staging.yaml
app: myapp
env: staging
namespace: my-team
chart:
  repository: https://chart.onechart.dev
  name: onechart
  version: 0.32.0
values:
  replicas: 1
  image:
    repository: myapp
    tag: 1.1.0
  ingress:
    host: myapp.staging.mycompany.com
    tlsEnabled: true
+dependencies:
+- name: myapp-postgresql
+  namespace: infrastructure
+  kind: terraform
+  spec:
+    module:
+      url: https://github.com/gimlet-io/tfmodules?sha=df0316bfc4d5163edce1ffcf0ed0d667cc263bd0&path=civo/logical-database
+    values:
+      database: myapp
+      user: myapp
+      host: postgresql.infrastructure.svc.cluster.local
+    sealedSecrets:
+      password: <sealed-password-value>
+    existing-secret: postgresql-credentials

Verify PostgreSQL with pgAdmin

The last step is to set the PostgreSQL connection parameters in your application.

The best practice is to use environment variables to configure your application. You can set them in the deployment configuration, and you can also use secrets.

• Connection URL: postgresql.infrastructure.svc.cluster.local
• Password: the one you set earlier

Backups

Add the following configuration to helm-releases/postgresql.yaml to enable daily backups of PostgreSQL.

...
spec:
  interval: 60m
  releaseName: postgresql
  chart:
    spec:
      chart: postgresql
      version: 15.5.31
      sourceRef:
        kind: HelmRepository
        name: bitnami
      interval: 10mpo
  values:
+   backup:
+     enabled: true
+     storage:
+       size: 20Gi   
...

This backup will be placed to a different volume than your PostgreSQL data volume. So you are protected from

• data cossuption due to appl misbehavior
• and data loss due to PostgreSQL data volume failure.