Provision PostgreSQL Databases on Kubernetes with CloudNativePG (CNPG)

We will be deploying a PostgreSQL cluster using the CloudNativePG (CNPG) operator.

CloudNativePG is an open-source, community-driven Kubernetes operator designed to manage PostgreSQL workloads natively within Kubernetes.

Let's begin.

Step 1: Install the CloudNativePG Operator

We can find the installation guide in the official documentation.
Install the latest operator manifest using:

kubectl apply --server-side -f \
  https://raw.githubusercontent.com/cloudnative-pg/cloudnative-pg/release-1.28/releases/cnpg-1.28.1.yaml

Once installed, a new namespace called cnpg-system will be created.
This namespace contains the operator components and Custom Resource Definitions (CRDs).

Step 2: Deploy a PostgreSQL Cluster

We will create a namespace and a cluster resource with the below manifest.
This cluster resource is a custom resource that has been enabled when the operator was installed.

database.yaml :

apiVersion: v1
kind: Namespace
metadata:
  name: database
  labels:
    name: database
---
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: demo-cluster
  namespace: database
spec:
  instances: 3
  storage:
    size: 1Gi

Apply the manifest:

k apply -f database.yaml

Check the pods in the database namespace:

k get pods -n database

Initially we can see there is an init pod running. This pod prepares the database to be deployed, provisions some storage for it and bootstraps the database.

Once the init pod process finishes, we can see the first instance of the cluster being provisioned. When the first instance starts running, the second instance will be created which is then joined to cluster and similarly the third instance will be provisioned and joined to the cluster.

Step 3: View Cluster Status using the CNPG CLI plugin

Now that we have the operator installed, we can use the CNPG kubectl plugin to interact with our clusters.

k get cluster -n database
k cnpg status demo-cluster -n database

CNPG gives the status of our cluster and shows the overview of our instances.

Step 4: Connect to the database pod

## List the pods:
k get pods -n database

## Exec into a pod:
k exec -it <POD_NAME> -n database -- sh

## Start the PostgreSQL client:
psql

After running psql in the pod, we will enter into the interface for our database.

## List the available databases
\l

We can see that, an app database is created automatically.

Connect to the app database:

## Connect to the app database
\c app

## List the tables in this app database
\dt

Now, create a sample table in this app database:

-- Create a simple users table
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);

Insert a few records into the table:

-- Insert a couple of records
INSERT INTO users (name, email) VALUES
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com');

Test the table by querying some data like:

-- Select all users
SELECT * FROM users;

We've successfully deployed PostgreSQL on Kubernetes !!

Next, we’ll explore how to connect this database to an application and understand how it works in real time.