PostgreSQL

---

PostgreSQL is an excellent open source relational database platform that provides plenty speed, reliability, and a robust set of features. I've deployed my instance with a primary and a single read replica. We'll use the Bitnami Helm chart as our installation.

Installation

As with many complex Helm charts, I like to grab the values.yaml file to adjust the deployment settings. We can do that with the following commands:

helm show values oci://registry-1.docker.io/bitnamicharts/postgresql > values.yaml

Now we can start editing the file to our needs. Below is some examples where I have customized the settings. For example, I use Longhorn for block storage. You will obviously need to adjust to your needs.

values.yaml

global:
...
  storageClass: "longhorn"
  postgresql:
    ## @param global.postgresql.auth.postgresPassword Password for the "postgres" admin user (overrides `auth.postgresPassword`)
    ## @param global.postgresql.auth.username Name for a custom user to create (overrides `auth.username`)
    ## @param global.postgresql.auth.password Password for the custom user to create (overrides `auth.password`)
    ## @param global.postgresql.auth.database Name for a custom database to create (overrides `auth.database`)
    ## @param global.postgresql.auth.existingSecret Name of existing secret to use for PostgreSQL credentials (overrides `auth.existingSecret`).
    ## @param global.postgresql.auth.secretKeys.adminPasswordKey Name of key in existing secret to use for PostgreSQL credentials (overrides `auth.secretKeys.adminPasswordKey`). Only used when `global.postgresql.auth.existingSecret` is set.
    ## @param global.postgresql.auth.secretKeys.userPasswordKey Name of key in existing secret to use for PostgreSQL credentials (overrides `auth.secretKeys.userPasswordKey`). Only used when `global.postgresql.auth.existingSecret` is set.
    ## @param global.postgresql.auth.secretKeys.replicationPasswordKey Name of key in existing secret to use for PostgreSQL credentials (overrides `auth.secretKeys.replicationPasswordKey`). Only used when `global.postgresql.auth.existingSecret` is set.
    ##
    auth:
      postgresPassword: "YourSuperSecretPassword"
      username: "dbuser"
      password: "dbuser"
      database: "initial"
      existingSecret: ""
      secretKeys:
        adminPasswordKey: ""
        userPasswordKey: ""
        replicationPasswordKey: ""

...
architecture: replication #standalone
...
primary:
  ...
  ## PostgreSQL Primary resource requests and limits
  ## ref: https://kubernetes.io/docs/concepts/configuration/manage-compute-resources-container/  
  ## @param primary.resourcesPreset Set container resources according to one common preset (allowed values: none, nano, small, medium, large, xlarge, 2xlarge). This is ignored if primary.resources is set (primary.resources is recommended for production).
  ## More information: https://github.com/bitnami/charts/blob/main/bitnami/common/templates/resources.tpl#L15
  ##
  resourcesPreset: "large"
...
readReplicas:
  ...
  replicaCount: 1
  ...
  ## PostgreSQL read only resource requests and limits
  ## ref: https://kubernetes.io/docs/concepts/configuration/manage-compute-resources-container/
  ## @param readReplicas.resourcesPreset Set container resources according to one common preset (allowed values: none, nano, small, medium, large, xlarge, 2xlarge). This is ignored if readReplicas.resources is set (readReplicas.resources is recommended for production).
  ## More information: https://github.com/bitnami/charts/blob/main/bitnami/common/templates/resources.tpl#L15
  ##

resourcesPreset: "large" ...

Deploy the Chart

Now that we've adjusted our values.yaml file we can deploy the chart. I like to keep these as bash scripts so I can easily redeploy the chart as I make configuration changes

build-postgres.sh

#!/bin/bash

helm upgrade --install --namespace database \
             postgres oci://registry-1.docker.io/bitnamicharts/postgresqll \
             -f values.yaml

Creating Databases for Applications

Now you can simply create new databases as needed for all of your Postgres enabled application. The process if straightforward: First, using Adminer, or any other Postgres client of your liking execute the following (after making the appropriate changes)

CREATE DATABASE "db_name";
CREATE ROLE "role_name" LOGIN PASSWORD 'your_secure_password';
GRANT ALL PRIVILEGES ON DATABASE "db_name" TO role_name;
ALTER DATABASE "db_name" OWNER TO role_name;

This will create a database and role for your application and give that role ownership of the database which should allow the consuming application everything it'll need to function.