Connect to a CloudNativePG (CNPG) PostgreSQL Database from an Application on Kubernetes

We will be connecting a PostgreSQL Database to the mealie application.

Step 1: Create a Secret for Database Credentials

We generate our password, store it as a secret, and use it in the PostgreSQL cluster.

We will create a Secret containing the app credentials which are the ones that are used by applications connecting to the PostgreSQL cluster, and correspond to the user (mealie) owning the database.

secret.yaml:

apiVersion: v1
kind: Secret
metadata:
  name: mealie-db-creds
  namespace: mealie
type: kubernetes.io/basic-auth
data:
  username: bWVhbGll
  password: c3Ryb25nLXBhc3N3b3Jk

I have simply done base64 encoding, but the best way would be to encrypt the secrets, store them in an external secret manager and rotate them regularly.

Apply the manifest:

k apply -f secret.yaml

convention over configuration paradigm:
Instead of creating our own secrets, alternatively, we can let the operator choose a default database name (app) and a default application user name (app), as well as randomly generate a secure password for both the superuser (postgres) and the application user (app) in PostgreSQL.

Step 2: Create Database Cluster

database.yaml:

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: mealie-db
  namespace: mealie

spec:
  description: "Postgres cluster for the mealie application"
  imageName: ghcr.io/cloudnative-pg/postgresql:18.3-system-trixie
  instances: 3

  bootstrap:
    initdb:
      database: mealie
      owner: mealie
      secret:
        name: mealie-db-creds

  storage:
    storageClass: local-path
    size: 1Gi

  resources:
    requests:
      memory: "512Mi"
      cpu: "1"
    limits:
      memory: "1Gi"
      cpu: "2"

Apply the manifest:

k apply -f database.yaml

Step 3: Understand Database Services

Applications work with the services created by CloudNativePG in the same Kubernetes cluster.

We will be using those services in our applications, and avoiding connecting directly to a specific PostgreSQL instance, as the latter can change during the cluster lifetime.

Get services:

k get services -n mealie

For our cluster, the important service is mealie-db-rw. This is the read/write endpoint that points to the primary instance of the cluster.

We will use the Kubernetes DNS service to point to a given server (the Kubernetes DNS service is required by the operator).

We can do that by using the name of the service (mealie-db-rw) if the application is deployed in the same namespace as the PostgreSQL cluster.

In case the PostgreSQL cluster resides in a different namespace, we can use the full qualifier: service-name.namespace-name.svc.cluster.local (mealie-db-rw.mealie.svc.cluster.local).

Step 4: Create a ConfigMap to Store Database Details

These database data will be set as environment variables inside the application cluster to let the application connect to the database.

configmap.yaml :

apiVersion: v1
data:
  DB_ENGINE: postgres
  POSTGRES_SERVER: mealie-db-rw
  POSTGRES_PORT: "5432"
  POSTGRES_DB: mealie
kind: ConfigMap
metadata:
  name: mealie-configmap
  namespace: mealie

Apply the config manifest:

k apply -f configmap.yaml

Step 5: Create a Secret to Store Database Sensitive Data

This Secret contains the username and password required for the application to access the database and will be set as environment variables inside the application container.

mealie-secret.yaml :

apiVersion: v1
data:
  POSTGRES_PASSWORD: c3Ryb25nLXBhc3N3b3Jk
  POSTGRES_USER: bWVhbGll
kind: Secret
metadata:
  name: mealie-container-env
  namespace: mealie

Apply:

k apply -f mealie-secret.yaml

Step 6: Configure and Apply Application Deployment

deployment.yaml:

apiVersion: apps/v1 
kind: Deployment 
metadata: 
  name: mealie 
  labels: 
    app: mealie 
spec: 
  replicas: 1 
  selector: 
    matchLabels: 
      app: mealie 
  template: 
    metadata: 
      labels: 
        app: mealie 
    spec: 
      containers: 
      - name: mealie 
        image: ghcr.io/mealie-recipes/mealie:latest 
        ports: 
        - containerPort: 9000
        envFrom:
        - configMapRef:
          name: mealie-configmap
        - secretRef:
          name: mealie-container-env

Apply the deployment:

k apply -f deployment.yaml

Step 7: Verify the Application Connectivity to the Database

## List database pods
k get pods -n mealie

## Exec into the primary database pod:
k exec -it mealie-db-1 -n mealie -- sh

## Start the PostgreSQL client:
psql

## List the available databases
\l

## Connect to the mealie database
\c mealie

## List the tables in the mealie database
\dt

Our mealie database has been created! Let's connect to it and list the tables.

We can see the several tables created by the mealie application in our mealie database when they were connected.

Now we will try to query some data from these tables.

Let's check the users of this application by querying the users table:

SELECT * FROM users;

We can see that there is 1 user which is a default user created by the application for first time login.

Step 8: Verify Database Updation

Let's verify whether the database is being updated while we are using the application.

First let's login with this default user. And then we will create a new user (trinaya).

Created a new user (trinaya):

Now, let's check whether the database has updated it's users table with this new user's data.

SELECT * FROM users;

The first query was before the new user was created and the second time it's after the new user was created.
We can clearly see how our database has been updated.

Yayy!! We have successfully connected our Mealie application to a PostgreSQL Database using CNPG operator!

Test it further by adding a few recipes!

Let's query from the tables recipes to list the recipes currently in our mealie application (there would be none because we haven't added any yet).

SELECT * FROM recipes;

Now let's add a recipe!

Let's verify whether our database has been updated.

There we go! Our Best Eggless Chocolate Cake recipe has been added to our Mealie Database! Wohoo!