How to create a PostgreSQL Cluster with StackGres on Kubernetes

This guide will walk you through setting up a PostgreSQL cluster using StackGres on Kubernetes cluster, breaking down each step to ensure clarity and ease of understanding. PostgreSQL, with its robustness and flexibility, stands out as a preferred choice for many. However, managing PostgreSQL in a Kubernetes environment can seem daunting, especially for beginners. Enter StackGres, a comprehensive solution designed to streamline this process.

StackGres is an innovative Kubernetes Operator for PostgreSQL, offering a full-stack PostgreSQL distribution specifically designed for Kubernetes environments. It simplifies deploying and managing production-ready Postgres clusters, eliminating the need for advanced Postgres expertise. StackGres provides a comprehensive solution that includes community PostgreSQL versions, over 150 Postgres extensions, high availability through Patroni, connection pooling, automated backups, integrated monitoring, and much more. The platform also features a fully-featured web console with Single Sign-On (SSO), support for PostgreSQL extensions like Babelfish (for SQL Server compatibility) and Timescale, as well as Citus for scaling out Postgres. Moreover, it offers the capability to self-host Supabase on StackGres.

To get started with StackGres, you need a Kubernetes environment pre-configured in kubectl. Installation involves deploying the StackGres operator using Kubernetes resource files provided for demonstration purposes. Once the operator is installed and ready, you can create your first StackGres cluster by defining a simple custom resource with your cluster’s configuration. This process enables the creation of a cluster with the latest PostgreSQL version, a specified number of nodes, and a defined disk size, all using the default storage class and StackGres’ default configuration settings.

Understanding the Basics

Before diving into the technicalities, let’s understand what we’re working with:

K3s: A lightweight Kubernetes distribution, perfect for edge, IoT, and CI/CD environments. It’s easy to install and runs almost anywhere.
PostgreSQL: A powerful, open-source object-relational database system with over 30 years of active development.
StackGres: An operator for Kubernetes, providing a full-stack PostgreSQL solution that simplifies deploying and managing PostgreSQL clusters.

Prerequisites

A working K3s cluster: If you’re new to K3s, start by installing it on your machine(Here is our detailed tutorial on how to install it). K3s simplifies the Kubernetes installation process, making it ideal for development and test environments.
kubectl CLI: This command-line tool lets you control Kubernetes clusters. It’s essential for deploying and managing applications on Kubernetes, including interacting with StackGres.

Step 1: Preparing Your K3s Environment

Ensure your K3s cluster is up and running. You can verify this by executing the following command:

kubectl cluster-info

This command should return information about your cluster, indicating it’s ready to go.

Step 2: Installing the StackGres Operator

StackGres operates within your Kubernetes cluster as an operator. To install it, you’ll deploy the StackGres operator using a provided YAML file. This file contains all the necessary Kubernetes resources for the operator.

Execute the following command to install the StackGres operator:

kubectl create -f https://stackgres.io/downloads/stackgres-k8s/stackgres/latest/stackgres-operator-demo.yml

This command fetches and applies a configuration from StackGres’s official site, setting up the operator in a new namespace called stackgres.

Step 3: Verifying the Operator Installation

After installation, it’s crucial to ensure that the StackGres operator is running. Use this command to check the operator’s status:

kubectl get pods -n stackgres -l group=stackgres.io

Look for the stackgres-operator and stackgres-restapi pods. If their status is Running, you’re all set.

Step 4: Creating Your First PostgreSQL Cluster

With StackGres, you define your PostgreSQL cluster using a custom resource. Here’s a simple example to create a single-instance PostgreSQL cluster:

apiVersion: stackgres.io/v1
kind: SGCluster
metadata:
  name: my-first-cluster
spec:
  instances: 1
  postgres:
    version: 'latest'
  pods:
    persistentVolume:
      size: '5Gi'

Save this configuration to a file (e.g., my-cluster.yml) and apply it using kubectl:

kubectl create -f my-cluster.yml

This command creates a new PostgreSQL cluster named my-first-cluster, running the latest PostgreSQL version and configured with a single instance and 5Gi of persistent storage.

Step 5: Monitoring Cluster Creation

Track the progress of your cluster’s deployment using:

kubectl get pods --watch

Wait for your cluster’s pod (e.g., my-first-cluster-0) to reach the Running state. This indicates that your PostgreSQL cluster is up and ready.

Step 6: Accessing Your PostgreSQL Cluster

Accessing your newly created PostgreSQL cluster is straightforward. StackGres provides a utility container for interacting with your database. Here’s how you can connect:

kubectl exec -ti "$(kubectl get pod --selector app=StackGresCluster,stackgres.io/cluster=true,role=master -o name)" -c postgres-util -- psql

This command opens a psql console connected to your PostgreSQL cluster, allowing you to execute SQL commands directly.

Step 7: Exposing the Service

Kubernetes services are typically only reachable from within the cluster by default. To access your PostgreSQL database from outside the Kubernetes cluster (such as from a browser on your VPS), you would need to expose it via a LoadBalancer or NodePort service. Given you’re on a VPS with a public IPv4 address, a LoadBalancer service could be more straightforward if your environment supports it; otherwise, NodePort can also be used.

If the StackGres Web Console is not already exposed via a NodePort, you will need to expose it. You can do this by creating or modifying a Kubernetes service of type NodePort for the Web Console. Here’s an example of how to define a service for the Web Console in a YAML file:

apiVersion: v1
kind: Service
metadata:
  name: stackgres-ui-nodeport
spec:
  type: NodePort
  ports:
    - port: 80
      targetPort: 8080
      nodePort: 30007
  selector:
    app: stackgres-ui

port is the port number on which the service will listen inside the cluster.
targetPort is the port on which the StackGres Web Console container is running.
nodePort is the port on which you’ll access the Web Console from outside the Kubernetes

Apply this service configuration using kubectl apply -f <filename>.yaml.

Convert the LoadBalancer Service to NodePort: You might need to edit the existing stackgres-restapi service to change its type to NodePort, or if you prefer keeping configurations immutable, delete and recreate it with the NodePort configuration.

Here’s how you can edit the service:

kubectl edit svc stackgres-restapi -n stackgres

In the editor, change type: LoadBalancer to type: NodePort, and optionally specify a nodePort value if you want a specific port; otherwise, Kubernetes will assign one randomly from the allowed range.

Access the Service: Once the service is exposed as a NodePort, you can access it using the IP address of any node in your cluster along with the assigned NodePort. If you didn’t specify a nodePort, you can find out which port was assigned by describing the service:

kubectl get svc stackgres-restapi -n stackgres

Look for the PORT(S) information, which will now include the NodePort assigned to your service.

To access the StackGres Web Console, open a web browser and navigate to your IP and the given port.

CHANGING THE UI PASSWORD

You can use the command below to change the password:

NEW_USER=admin
NEW_PASSWORD=password
kubectl create secret generic -n stackgres stackgres-restapi-admin  --dry-run=client -o json \
  --from-literal=k8sUsername="$NEW_USER" \
  --from-literal=password="$(echo -n "${NEW_USER}${NEW_PASSWORD}"| sha256sum | awk '{ print $1 }' )" > password.patch

kubectl patch secret -n stackgres stackgres-restapi-admin -p "$(cat password.patch)" && rm password.patch

Now that you have a running PostgreSQL cluster, explore further. StackGres offers extensive features such as automatic backups, high availability configurations, and detailed monitoring. Deploying a PostgreSQL cluster on a K3s environment using StackGres doesn’t have to be complicated. By following this step-by-step guide, even beginners can set up a