Enabling high availability for PostgreSQL with Kubernetes StatefulSets

Question

Pulumi · Accepted Answer

To enable high availability (HA) for PostgreSQL on Kubernetes, we will use a `StatefulSet`. A `StatefulSet` is a Kubernetes workload API object used to manage stateful applications. It manages the deployment and scaling of a set of Pods and provides guarantees about the ordering and uniqueness of these Pods.

When you run PostgreSQL in Kubernetes with HA, you want to ensure that your database can withstand node failures, network partitions, and other potential issues that can occur in a distributed system. To achieve HA, we can set up a primary-replica configuration with one primary that handles writes and replicas that can take over if the primary fails. The replicas also handle read operations, allowing you to scale your read throughput.

To provide a reliable failover mechanism, we will include a solution such as Patroni, which is a template for PostgreSQL HA. Patroni works with Kubernetes to handle leader elections and the failover process. It uses `StatefulSets` to ensure that there is stable networking and storage for your PostgreSQL Pods.

Here's an example of how you can define a `StatefulSet` for a PostgreSQL cluster with Patroni in Kubernetes using Pulumi. The code will create a PostgreSQL HA setup with one primary and two replicas.

Before proceeding with the following Pulumi program, make sure you have:
1. A Kubernetes cluster running and accessible using `kubectl`.
2. Pulumi CLI [installed](https://www.pulumi.com/docs/get-started/install/) and [configured](https://www.pulumi.com/docs/intro/cloud-providers/kubernetes/setup/) for your Kubernetes cluster.

Now, let's dive into the Pulumi code:

```typescript
import * as k8s from "@pulumi/kubernetes";

// Define the PostgreSQL StatefulSet with Patroni for high availability.
// We're using a StatefulSet instead of a Deployment because PostgreSQL requires stable network identifiers and storage.
const postgresStatefulSet = new k8s.apps.v1.StatefulSet("postgres-statefulset", {
    metadata: {
        name: "postgres-ha",
        labels: {
            app: "postgres-ha",
        },
    },
    spec: {
        serviceName: "postgres",
        replicas: 3, // We define 3 replicas which include one primary and two standby nodes for failover.
        selector: {
            matchLabels: {
                app: "postgres-ha",
            },
        },
        template: {
            metadata: {
                labels: {
                    app: "postgres-ha",
                },
            },
            spec: {
                terminationGracePeriodSeconds: 10,
                containers: [
                    {
                        name: "postgres",
                        image: "postgres:12", // Using the official PostgreSQL Docker image.
                        ports: [
                            {
                                name: "postgres",
                                containerPort: 5432,
                            },
                        ],
                        env: [
                            {
                                name: "POSTGRES_PASSWORD",
                                value: "your-database-password", // Replace with a secure password for your PostgreSQL.
                            },
                            // Additional environment variables for Patroni configuration go here.
                        ],
                        // ...additional container configuration...
                    },
                    // Define the Patroni sidecar container here.
                    // Patroni will manage the PostgreSQL instances to ensure HA.
                ],
                // ...other pod specification...
            },
        },
        // We define a volume claim template with access modes and resources to ensure each replica has its own persistent storage.
        volumeClaimTemplates: [
            {
                metadata: {
                    name: "pgdata",
                },
                spec: {
                    accessModes: ["ReadWriteOnce"],
                    resources: {
                        requests: {
                            storage: "10Gi",
                        },
                    },
                },
            },
        ],
    },
});

// Export the name of the StatefulSet
export const statefulSetName = postgresStatefulSet.metadata.name;
```

This code defines a `StatefulSet` that will manage our PostgreSQL pods. It includes:
- A label selector to identify the pods that belong to the `StatefulSet`.
- Defining 3 replicas: the primary node and two replicas for failover.
- Container configuration for PostgreSQL, including the image to use and environment variables for configuration.
- Volume claim templates to provide each instance with stable and persistent storage.

The storage request size and the password should be adapted according to your needs. The Patroni sidecar requires additional containers and configurations that are not explicitly defined in this example but are pivotal for the HA setup. You can follow the installation and configuration instructions from [Patroni's documentation](https://patroni.readthedocs.io/en/latest/) for details on setting up the sidecar container and its environment variables.

Once you've defined your resources in your Pulumi program, you will run `pulumi up` to create the infrastructure.

Keep in mind that this configuration is the starting point for creating a highly available PostgreSQL cluster with Kubernetes. A production-grade HA setup will require further customizations such as setting up proper networking, storage, backup solutions, and disaster recovery strategies.

If you have questions regarding the implementation details or how to add certain specific configurations, feel free to ask, and we can iteratively improve this Pulumi program.