Database on Kubernetes

Kubernetes has become the de facto standard for container orchestration, offering powerful capabilities

for running stateful applications like databases. In this lesson, we'll explore how to deploy, configure,

and manage PostgreSQL databases on Kubernetes, taking advantage of Kubernetes' native capabilities

and ecosystem of operators and tools.

Running databases on Kubernetes brings both challenges and significant benefits. While databases have

traditionally been deployed on dedicated servers, the cloud-native approach using Kubernetes provides

advantages in terms of automation, scalability, and operational efficiency. We'll examine best practices

for running production-grade PostgreSQL deployments in Kubernetes environments.

StatefulSets

Kubernetes workload API objects that manage stateful applications with

persistent storage, stable network identities, and ordered deployment and scaling.

Persistent Volumes

Storage resources provisioned by administrators or dynamically by Storage

Classes, providing persistent data storage independent of Pod lifecycle.

Kubernetes Operators

Application-specific controllers that extend Kubernetes functionality

to create, configure, and manage instances of complex applications like PostgreSQL.

Cloud Native Database Patterns

Design and operational patterns for running databases in dynamic, auto-scaling

cloud environments with high availability and disaster recovery capabilities.

K8s Basics

Deployment Options

Database Operators

Management

Before diving into database deployments on Kubernetes, it's important to understand the key Kubernetes

concepts that are relevant for stateful applications like PostgreSQL.

Kubernetes Architecture Overview

Kubernetes is a container orchestration platform that automates the deployment, scaling, and

management of containerized applications. The key components of a Kubernetes cluster include:

Control Plane

API Server: The central management point

Scheduler: Assigns Pods to Nodes

Controller Manager: Manages various controllers

etcd: Distributed key-value store for cluster state

Node Components

Kubelet: Ensures containers run in a Pod

Container Runtime: Runs containers (e.g., Docker)

kube-proxy: Network proxy and load balancer

Kubernetes organizes applications into

, which are the smallest deployable units

containing one or more containers. Pods are typically managed by higher-level controllers like

Deployments, StatefulSets, or DaemonSets.

Database Deployment Challenges

Traditional Kubernetes Deployments are designed for stateless applications. Databases require

stateful workloads with stable network identities and persistent storage, making them more complex

to deploy on Kubernetes.

StatefulSets: The Foundation for Databases

StatefulSets are specialized workload API objects designed for applications that require stable

network identities and persistent storage. This makes them ideal for databases like PostgreSQL.

Key Features

Stable Network Identity:

Predictable Pod names and DNS hostnames

Ordered Deployment:

Sequential Pod creation, scaling, and deletion

Persistent Storage:

Each Pod can claim its own persistent volume

Pod Identity:

Each Pod maintains its identity across rescheduling

Common Use Cases

Primary-replica database configurations

Distributed databases with sharding

Message brokers and queues

Any application requiring stable, unique network identities

The example above shows a basic StatefulSet configuration for PostgreSQL. Note how it includes a

volumeClaimTemplate

that dynamically provisions persistent storage for each Pod,

ensuring data persistence even if Pods are rescheduled to different nodes.

Persistent Storage Options

Databases require reliable persistent storage. Kubernetes offers several options for providing

persistent storage to applications.

Persistent Volumes and Claims

The Kubernetes persistent storage model separates storage provisioning from usage:

Persistent Volume (PV):

A piece of storage in the cluster provisioned by an administrator or dynamically by a StorageClass

Persistent Volume Claim (PVC):

A request for storage by a user, which can be fulfilled by a PV

When using StatefulSets, PVCs are typically created using volumeClaimTemplates, which

automatically create a PVC for each Pod in the StatefulSet.

Storage Classes and Provisioners

StorageClasses provide a way to describe "classes" of storage offered in a cluster. Different

classes may map to quality-of-service levels, backup policies, or arbitrary policies determined

by cluster administrators.

Each StorageClass contains:

Provisioner:

Determines what volume plugin to use for provisioning PVs

Parameters:

Configuration for the provisioner

ReclaimPolicy:

What happens to a PV when its claim is deleted

VolumeBindingMode:

Controls when volume binding and dynamic provisioning occur

For databases, it's important to choose a StorageClass that provides:

Low latency and high IOPS (for transaction processing)

Data durability and reliability

Backup and snapshot capabilities

Volume expansion (to accommodate growing databases)

Storage Performance Considerations

Database performance is heavily dependent on storage performance. Consider these factors when

choosing storage for PostgreSQL on Kubernetes:

I/O Performance

High IOPS capability for transaction processing

Throughput:

Sufficient for analytical workloads

Low latency for fast transaction commits

Random I/O:

Ability to handle random access patterns

Durability and Reliability

Replication:

Storage-level replication for durability

Backup/Snapshot:

Support for consistent backups

Consistency:

Strong consistency guarantees

Fast recovery in case of node failures

Cloud Provider Storage Recommendations

gp3 or io2 EBS volumes for better performance control

SSD Persistent Disks or Extreme Persistent Disks

Premium SSD or Ultra Disk Storage

Production Recommendation

For production PostgreSQL deployments, always benchmark storage performance before

deployment and continuously monitor I/O metrics in production. The storage subsystem

is often the performance bottleneck for database workloads.

Networking and Service Discovery

Kubernetes provides several networking abstractions that are important for PostgreSQL deployments,

especially for primary-replica setups and client connectivity.

Kubernetes Services provide stable network endpoints for Pods, enabling:

ClusterIP:

Internal access within the cluster

Expose on each node's IP at a static port

LoadBalancer:

External load balancer (cloud providers)

ExternalName:

CNAME record in DNS

Headless Services

StatefulSets typically use headless services (clusterIP: None) to provide:

DNS records for each Pod (

Direct access to specific Pods by hostname

Critical for database replication configurations

For PostgreSQL high-availability setups, you would typically have:

A headless service for stable network identity of all Pods in the StatefulSet

A service targeting the primary instance for write operations

Optionally, a service targeting replica instances for read operations

Best Practice

Use a combination of services and Pod labels to direct write traffic to the primary and read

traffic to replicas. This separation allows for effective read scaling while maintaining write

consistency.

1# Example StatefulSet for a simple PostgreSQL deployment
2apiVersion: apps/v1
3kind: StatefulSet
4metadata:
5  name: postgres
6spec:
7  serviceName: "postgres"
8  replicas: 1
9  selector:
10    matchLabels:
11      app: postgres
12  template:
13    metadata:
14      labels:
15        app: postgres
16    spec:
17      containers:
18      - name: postgres
19        image: postgres:14
20        ports:
21        - containerPort: 5432
22          name: postgres
23        env:
24        - name: POSTGRES_PASSWORD
25          valueFrom:
26            secretKeyRef:
27              name: postgres-secrets
28              key: postgres-password
29        volumeMounts:
30        - name: postgres-data
31          mountPath: /var/lib/postgresql/data
32  volumeClaimTemplates:
33  - metadata:
34      name: postgres-data
35    spec:
36      accessModes: ["ReadWriteOnce"]
37      storageClassName: "standard"
38      resources:
39        requests:
40          storage: 10Gi

1# Example PersistentVolumeClaim
2apiVersion: v1
3kind: PersistentVolumeClaim
4metadata:
5  name: postgres-data
6spec:
7  accessModes:
8    - ReadWriteOnce
9  storageClassName: standard
10  resources:
11    requests:
12      storage: 10Gi

1# Example StorageClass for AWS EBS volumes
2apiVersion: storage.k8s.io/v1
3kind: StorageClass
4metadata:
5  name: fast
6provisioner: kubernetes.io/aws-ebs
7parameters:
8  type: gp2
9  fsType: ext4
10reclaimPolicy: Retain
11allowVolumeExpansion: true
12volumeBindingMode: WaitForFirstConsumer

1# Headless service for StatefulSet
2apiVersion: v1
3kind: Service
4metadata:
5  name: postgres
6  labels:
7    app: postgres
8spec:
9  ports:
10  - port: 5432
11    name: postgres
12  clusterIP: None
13  selector:
14    app: postgres
15---
16# Service for client access (primary node)
17apiVersion: v1
18kind: Service
19metadata:
20  name: postgres-primary
21  labels:
22    app: postgres
23spec:
24  type: ClusterIP
25  ports:
26  - port: 5432
27    targetPort: 5432
28    name: postgres
29  selector:
30    app: postgres
31    role: primary

There are several approaches to deploying PostgreSQL on Kubernetes, ranging from simple standalone

deployments to sophisticated high-availability configurations with automated failover. Let's explore

the different deployment options and their characteristics.

Standalone PostgreSQL Deployment

A standalone deployment is the simplest approach, consisting of a single PostgreSQL instance.

This configuration is suitable for development, testing, and non-critical workloads.

Advantages

Simple to deploy and manage

Minimal resource requirements

Easy to backup and restore

Straightforward configuration

Limitations

No high availability

Single point of failure

Limited scalability for read operations

Downtime during maintenance

The example above includes health checks (readiness and liveness probes) to ensure

Kubernetes can monitor the health of the PostgreSQL instance. This is particularly important

for databases to prevent unnecessary restarts and potential data corruption.

Production Consideration

While a standalone deployment is simple, it's generally not recommended for production

workloads that require high availability. However, it can be suitable for applications with

low-value data or where data loss is acceptable in rare cases.

Primary-Replica Replication

A primary-replica (formerly known as master-slave) setup uses PostgreSQL's native streaming

replication to maintain one or more read replicas of the primary database. This provides both

read scalability and improved data durability.

Advantages

Horizontal read scaling

Improved data durability

Load distribution for read operations

Basis for high-availability setups

Challenges

Manual failover process by default

Complexity in configuration

Need for careful monitoring

Potential for replication lag

Setting up a primary-replica deployment in Kubernetes involves:

Creating a StatefulSet with multiple replicas

Using init containers or configuration scripts to determine Pod roles (primary or replica)

Configuring PostgreSQL streaming replication between Pods

Setting up separate services for primary (write) and replica (read) access

Primary Configuration

The primary PostgreSQL server needs to be configured to allow streaming replication:

These settings enable Write-Ahead Log (WAL) shipping to replica servers and configure

authentication for replication connections.

Replica Configuration

Replica servers need a special configuration to connect to the primary and

operate in standby mode:

Note: In PostgreSQL 12 and later, recovery.conf is deprecated, and these settings are

placed in postgresql.auto.conf with a standby.signal file to indicate standby mode.

Kubernetes Implementation

Implementing this in Kubernetes typically requires scripts to:

Determine if a Pod should be primary or replica based on its ordinal index

Initialize empty data directories appropriately

Configure replication settings dynamically

Handle failover scenarios

These scripts are usually included as init containers or as part of the main

container's startup script. Due to this complexity, many organizations prefer

to use PostgreSQL operators that handle these scenarios automatically.

Implementation Complexity

Setting up primary-replica replication manually in Kubernetes involves significant complexity.

For production environments, it's often better to use a specialized PostgreSQL operator

or a managed database service that integrates with Kubernetes.

High-Availability Clusters

High-availability PostgreSQL clusters extend the primary-replica configuration with automatic

failover capabilities. These setups typically include additional components for cluster management,

health monitoring, and automated recovery.

Key Components

Distributed Consensus:

etcd, ZooKeeper, or Consul

Failover Mechanism:

Automated primary election

Health Monitoring:

Continuous health checks

Connection Routing:

Dynamic endpoint management

HA Solutions for PostgreSQL

HA solution using etcd, Consul, or ZooKeeper

Cloud native PostgreSQL HA

PostgreSQL Operator:

Crunchy Data, Zalando, etc.

HA PostgreSQL cluster using Patroni

A high-availability setup adds several components to the basic primary-replica configuration:

Patroni: A Popular HA Solution

Patroni is a template for PostgreSQL HA that manages and automates PostgreSQL deployments. It

integrates with distributed configuration stores like etcd, Consul, or ZooKeeper to maintain

cluster state and coordinate failovers.

Key features include:

Automated failover and recovery

Dynamic configuration management

REST API for cluster management

Flexible deployment options

Integration with service discovery mechanisms

Production Recommendation

For production PostgreSQL deployments on Kubernetes that require high availability,

using a specialized PostgreSQL operator is highly recommended. These operators integrate

solutions like Patroni with Kubernetes-native capabilities to provide a robust,

automated HA solution.

Cloud Provider Database Services

As an alternative to self-managed PostgreSQL on Kubernetes, most cloud providers offer

managed PostgreSQL services that can integrate with your Kubernetes cluster. These services

offload database management tasks to the cloud provider while still allowing your applications

to access the database.

Advantages

Reduced operational burden

Automated backups and patching

Built-in high availability

Performance optimization

Integrated monitoring and metrics

Considerations

Higher cost than self-managed

Potential vendor lock-in

Limited customization options

Networking integration complexity

Multi-region scenarios

Popular cloud-managed PostgreSQL offerings include:

Amazon RDS for PostgreSQL

Amazon Aurora PostgreSQL

Google Cloud

Cloud SQL for PostgreSQL

AlloyDB for PostgreSQL

Microsoft Azure

Azure Database for PostgreSQL

Azure Cosmos DB for PostgreSQL

Best Practice

When deciding between self-managed PostgreSQL on Kubernetes and a managed service,

consider total cost of ownership, including operational overhead, performance requirements,

compliance needs, and team expertise. For many organizations, managed services provide

better value despite higher direct costs.

1# Simple standalone PostgreSQL deployment
2apiVersion: apps/v1
3kind: StatefulSet
4metadata:
5  name: postgres
6spec:
7  serviceName: "postgres"
8  replicas: 1
9  selector:
10    matchLabels:
11      app: postgres
12  template:
13    metadata:
14      labels:
15        app: postgres
16    spec:
17      containers:
18      - name: postgres
19        image: postgres:14
20        ports:
21        - containerPort: 5432
22          name: postgres
23        env:
24        - name: POSTGRES_PASSWORD
25          valueFrom:
26            secretKeyRef:
27              name: postgres-secrets
28              key: postgres-password
29        - name: POSTGRES_DB
30          value: "mydb"
31        - name: PGDATA
32          value: "/var/lib/postgresql/data/pgdata"
33        volumeMounts:
34        - name: postgres-data
35          mountPath: /var/lib/postgresql/data
36        resources:
37          requests:
38            memory: "1Gi"
39            cpu: "500m"
40          limits:
41            memory: "2Gi"
42            cpu: "1000m"
43        readinessProbe:
44          exec:
45            command:
46            - /bin/sh
47            - -c
48            - pg_isready -U postgres
49          initialDelaySeconds: 5
50          periodSeconds: 10
51        livenessProbe:
52          exec:
53            command:
54            - /bin/sh
55            - -c
56            - pg_isready -U postgres
57          initialDelaySeconds: 30
58          periodSeconds: 15
59  volumeClaimTemplates:
60  - metadata:
61      name: postgres-data
62    spec:
63      accessModes: ["ReadWriteOnce"]
64      storageClassName: "standard"
65      resources:
66        requests:
67          storage: 10Gi

1# postgresql.conf settings for primary
2wal_level = replica
3max_wal_senders = 10
4max_replication_slots = 10
5wal_keep_size = 1GB
6hot_standby = on
7
8# pg_hba.conf entry to allow replicas to connect
9host replication replicator 0.0.0.0/0 md5

1# postgresql.conf settings for replica
2hot_standby = on
3primary_conninfo = 'host=postgres-0.postgres user=replicator password=replicator_password'
4
5# recovery.conf (or postgresql.auto.conf in PostgreSQL 12+)
6primary_slot_name = 'replica_1'
7restore_command = 'cp /var/lib/postgresql/archive/%f %p'
8recovery_target_timeline = 'latest'
9standby_mode = 'on'

Kubernetes operators extend the platform's capabilities to handle stateful applications like

databases. PostgreSQL operators automate complex database management tasks, providing a more

cloud-native approach to running databases on Kubernetes.

Understanding Kubernetes Operators

Kubernetes operators are application-specific controllers that extend the Kubernetes API to

create, configure, and manage complex applications. They encode operational knowledge and

automate tasks that would otherwise require manual intervention.

How Operators Work

Extend Kubernetes with Custom Resource Definitions (CRDs)

Implement custom controllers to watch and respond to changes

Encode domain-specific operational knowledge

Automate common tasks like provisioning, scaling, and failover

Provide consistent management interfaces

Operator Components

Custom Resources:

Database-specific Kubernetes objects

Controller:

Watches and reconciles resource state

Control Loop:

Continuously maintains desired state

Operator SDK:

Framework for building operators

Operational Logic:

Embedded domain expertise

For PostgreSQL specifically, operators handle complex tasks such as:

Automating deployment of primary and replica instances

Managing high availability and failover

Handling backups and point-in-time recovery

Simplifying configuration management

Automating minor and major version upgrades

Providing monitoring and metrics integration

Why Use Operators?

Database operations are complex and error-prone when performed manually. Operators encapsulate

operational expertise, reducing the risk of human error and providing consistent, repeatable

operations across environments. This is especially valuable for PostgreSQL, which requires

careful management for high availability and data integrity.

Popular PostgreSQL Operators

Several PostgreSQL operators are available for Kubernetes, each with its own features,

strengths, and community support. Let's compare some of the most widely used options.

Zalando Postgres Operator

The Zalando Postgres Operator is one of the most mature PostgreSQL operators, developed

by Zalando SE, a large European e-commerce company. It manages PostgreSQL clusters based

on Patroni and Spilo.

Key Features

Deployment of PostgreSQL clusters with HA

Automated failover via Patroni

Point-in-time recovery

Rolling updates with minimal downtime

Database resource management

Consistent backups

Production PostgreSQL deployments

Environments requiring high availability

Multi-tenant database deployments

Organizations with mature Kubernetes adoption

The Zalando operator is known for its robustness and is used in production by many

organizations. It's a good choice for those seeking a mature, well-tested solution.

Crunchy PostgreSQL Operator

The Crunchy PostgreSQL Operator (PGO) is developed by Crunchy Data, a company specializing

in PostgreSQL products and services. It provides a feature-rich PostgreSQL-as-a-Service

platform for Kubernetes.

Key Features

High-availability PostgreSQL clusters

Backup and disaster recovery

Monitoring with Prometheus and Grafana

Comprehensive scaling options

TLS support for all connections

Integration with pgBackRest

Enterprise PostgreSQL deployments

Environments with strict security requirements

Systems needing advanced monitoring

Deployments requiring detailed backup strategies

Crunchy PGO is known for its comprehensive feature set and enterprise-grade capabilities.

It's particularly well-suited for organizations that require robust backup and recovery

mechanisms or have complex security requirements.

CloudNativePG Operator

CloudNativePG is a newer, CNCF Sandbox project designed as a Kubernetes-native PostgreSQL

operator. It focuses on simplicity, declarative configuration, and cloud-native principles.

Key Features

Declarative PostgreSQL cluster management

Automated failover and healing

Continuous backup with retention policies

Point-in-time recovery

Fully customizable SSL certificates

Kubernetes-native monitoring

Cloud-native applications

Kubernetes-first organizations

GitOps workflows

Development and production environments

CloudNativePG is rapidly gaining popularity due to its focus on Kubernetes-native design

and simplicity. It's a good choice for organizations heavily invested in Kubernetes and

cloud-native principles.

Operator Comparison

When selecting a PostgreSQL operator, consider these factors based on your requirements:

Crunchy PGO

CloudNativePG

Community Support

Strong (Commercial)

Growing (CNCF)

HA Architecture

Patroni-based

Custom (with Patroni)

Backup Solution

WAL-E, Custom

pgBackRest

Monitoring Integration

Prometheus

Prometheus, Grafana

Prometheus

Upgrade Handling

Configuration Flexibility

Enterprise Features

Selection Guidance

All three operators are solid choices, but each excels in different areas. If you need

a proven solution with extensive community usage, consider Zalando. For enterprise

features and support, Crunchy PGO might be the best fit. If you prefer a modern,

Kubernetes-native approach with a clean design, CloudNativePG is an excellent option.

Operator Deployment and Management

Deploying and managing a PostgreSQL operator involves several steps, from initial installation

to ongoing operations. Here's a general workflow for adopting a PostgreSQL operator in your

Kubernetes environment.

Deployment Workflow

Operator Installation:

Install the operator using Helm, kubectl, or a custom

installation process provided by the operator.

Custom Resource Definitions:

Ensure CRDs are properly installed and configured.

Operator Configuration:

Configure global settings according to your requirements.

PostgreSQL Cluster Definition:

Create a YAML definition for your PostgreSQL clusters.

Cluster Deployment:

Apply the cluster definition to create the PostgreSQL instances.

Verification:

Check that the operator has created the required resources and the database is accessible.

Ongoing Management:

Use the operator's custom resources to manage the database lifecycle.

Common Management Tasks

Scaling the number of replicas

Updating PostgreSQL configuration

Performing version upgrades

Creating and restoring backups

Monitoring cluster health

Managing database users and permissions

GitOps Integration

PostgreSQL operators work well with GitOps tools like:

This allows for declarative, version-controlled database management.

Operator Upgrade Considerations

When upgrading PostgreSQL operators, carefully review the release notes and upgrade

documentation. Operator upgrades may require changes to CRDs or other resources, and

some upgrades might not be backward compatible. Always test operator upgrades in a

non-production environment first.

1# Example Zalando PostgreSQL cluster definition
2apiVersion: "acid.zalan.do/v1"
3kind: postgresql
4metadata:
5  name: acid-minimal-cluster
6spec:
7  teamId: "acid"
8  volume:
9    size: 1Gi
10  numberOfInstances: 2
11  users:
12    # database owner
13    zalando:
14    - superuser
15    - createdb
16    # role for application foo
17    foo_user: []
18  databases:
19    foo: zalando
20  postgresql:
21    version: "14"
22    parameters:
23      shared_buffers: "32MB"
24      max_connections: "10"
25      log_statement: "all"

1# Example Crunchy PGO cluster definition
2apiVersion: postgres-operator.crunchydata.com/v1beta1
3kind: PostgresCluster
4metadata:
5  name: hippo
6spec:
7  image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres:ubi8-14.5-0
8  postgresVersion: 14
9  instances:
10    - name: instance1
11      replicas: 2
12      dataVolumeClaimSpec:
13        accessModes:
14        - "ReadWriteOnce"
15        resources:
16          requests:
17            storage: 1Gi
18  backups:
19    pgbackrest:
20      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbackrest:ubi8-2.38-0
21      repos:
22      - name: repo1
23        volume:
24          volumeClaimSpec:
25            accessModes:
26            - "ReadWriteOnce"
27            resources:
28              requests:
29                storage: 1Gi

1# Example CloudNativePG cluster definition
2apiVersion: postgresql.cnpg.io/v1
3kind: Cluster
4metadata:
5  name: postgresql-cluster
6spec:
7  instances: 3
8  primaryUpdateStrategy: unsupervised
9
10  # PostgreSQL configuration
11  postgresql:
12    parameters:
13      shared_buffers: 256MB
14      pg_stat_statements.max: '10000'
15      pg_stat_statements.track: all
16
17  # Storage configuration
18  storage:
19    size: 1Gi
20    storageClass: standard
21
22  # Backup configuration
23  backup:
24    barmanObjectStore:
25      destinationPath: s3://postgresql-backup/
26      endpointURL: https://minio-service:9000/
27      s3Credentials:
28        accessKeyId:
29          name: minio-creds
30          key: access-key
31        secretAccessKey:
32          name: minio-creds
33          key: secret-key

1# Example deployment for Zalando PostgreSQL Operator using Helm
2
3# Add the Helm repository
4helm repo add postgres-operator-charts https://opensource.zalando.com/postgres-operator/charts/postgres-operator
5
6# Update the repository
7helm repo update
8
9# Install the operator
10helm install postgres-operator postgres-operator-charts/postgres-operator
11
12# Install the required UI (optional)
13helm install postgres-operator-ui postgres-operator-charts/postgres-operator-ui
14
15# Create a PostgreSQL cluster
16kubectl apply -f postgres-cluster.yaml
17
18# Check the status
19kubectl get postgresql
20
21# Connect to the database
22kubectl exec -it <pod-name> -- psql -U postgres

Successfully running PostgreSQL in Kubernetes requires attention to several operational aspects,

from backup and recovery to monitoring and scaling. This section covers key management practices

for PostgreSQL on Kubernetes.

Backup and Recovery Strategies

Robust backup and recovery mechanisms are essential for any production database deployment.

Kubernetes introduces unique considerations for backing up PostgreSQL databases.

Backup Methods

Logical Backups:

pg_dump for schema and data

Physical Backups:

pg_basebackup for full cluster copy

WAL Archiving:

Continuous transaction logging

Volume Snapshots:

Storage-level snapshots (cloud providers)

Purpose-built Tools:

pgBackRest, Barman, WAL-G

Key Considerations

Consistency:

Ensure backups are crash-consistent

Scheduling:

Automate regular backups

Use durable storage for backups

Regularly verify backup integrity

Encrypt backups at rest and in transit

Operator-Managed Backups

PostgreSQL operators typically include built-in backup functionality:

Zalando Operator

S3-compatible storage integration

WAL-E for archiving

Configurable backup schedules

Retention policies

Crunchy PGO

pgBackRest integration

Multiple backup repositories

Full, differential, and incremental backups

Point-in-time recovery

CloudNativePG

Barman integration

S3 and Azure Blob storage support

Backup retention policies

WAL archiving

These built-in backup mechanisms are often the simplest and most reliable option for

Kubernetes-hosted PostgreSQL databases. They handle complexities such as coordination

with the database for consistent backups and proper storage of WAL files.

Recovery Procedures

Recovery procedures depend on the backup method and the nature of the failure:

Recovery Scenarios

Pod Failure:

Automatic recreation by StatefulSet

Node Failure:

Pod rescheduling with persistent storage

Data Corruption:

Restore from backup

Cluster Failure:

Recreate cluster and restore

Disaster Recovery:

Cross-region restore

Point-in-Time Recovery (PITR)

Combines base backup with WAL files

Allows recovery to any point in time

Critical for recovering from logical errors

Requires continuous WAL archiving

Supported by most PostgreSQL operators

Most operators provide custom resources or commands for initiating recovery operations.

These typically handle the complex process of restoring the base backup, configuring

recovery parameters, and applying the necessary WAL files.

Backup Best Practices

To ensure reliable backups in a Kubernetes environment:

Multiple Backup Methods:

Implement both logical and physical backups

for different recovery scenarios.

Off-Cluster Storage:

Store backups outside the Kubernetes cluster

to protect against cluster-wide failures.

Backup Verification:

Regularly test backups by performing recovery

in a separate environment.

Monitoring:

Monitor backup processes and alert on failures.

Automation:

Automate backup procedures using operators or custom

CronJobs.

Documentation:

Maintain clear documentation of backup and recovery

procedures.

Disaster Recovery:

Include database backups in your broader

disaster recovery planning.

Resource Considerations

Backup operations can be resource-intensive. Consider:

Scheduling backups during low-traffic periods

Setting appropriate resource limits for backup Jobs

Using incremental backups where possible

Monitoring database performance during backups

Security Note

Database backups often contain sensitive information. Ensure your backup storage

is properly secured, backups are encrypted, and access to backup repositories is

tightly controlled with proper authentication and authorization.

Monitoring and Observability

Effective monitoring is crucial for maintaining healthy PostgreSQL deployments in Kubernetes.

A comprehensive monitoring strategy includes database-specific metrics, Kubernetes resource

monitoring, and integrated alerting.

Key Metrics to Monitor

Database Metrics:

Connections, queries, transactions, WAL

System Metrics:

CPU, memory, disk I/O, network

Kubernetes Metrics:

Pod health, resource usage, events

Storage Metrics:

Usage, IOPS, latency

Replication Metrics:

Lag, status, conflicts

Monitoring Tools

Prometheus:

Metrics collection and storage

Visualization and dashboards

pg_stat_statements:

Query performance tracking

postgres_exporter:

PostgreSQL metrics for Prometheus

Kube-state-metrics:

Kubernetes object metrics

PostgreSQL Prometheus Monitoring

A common approach is to use the PostgreSQL Prometheus exporter to collect database metrics:

Standard Metrics

Database size and growth

Connection counts and states

Transaction rates (commits/rollbacks)

Query execution statistics

Lock information

Buffer usage

Custom Metrics

Specific table/index sizes

Bloat detection

Cache hit ratios

Vacuum and analyze stats

Replication lag

Application-specific metrics

Many PostgreSQL operators include built-in Prometheus exporters or integration,

simplifying the monitoring setup process.

Grafana Dashboards

Grafana dashboards provide visualization for PostgreSQL metrics. Several community-maintained

dashboards are available for PostgreSQL monitoring:

PostgreSQL Overview

General metrics dashboard with:

Database activity

Connection stats

Transaction rates

Cache hit ratios

Replication status

PostgreSQL Details

In-depth metrics for:

Query performance

Index usage

Table statistics

I/O performance

Lock analysis

When setting up dashboards, consider creating views for different audiences:

Executive Dashboard:

High-level health and performance

DBA Dashboard:

Detailed technical metrics for database administrators

Developer Dashboard:

Query performance and application-specific metrics

Operations Dashboard:

Infrastructure and resource metrics

For Kubernetes-specific monitoring, include dashboards for StatefulSet status,

persistent volume claims, and pod resource usage alongside the PostgreSQL metrics.

Alerting and SLOs

Effective alerting is critical for proactive PostgreSQL management. Define meaningful

alerts based on Service Level Objectives (SLOs) and key performance indicators:

Critical Alerts

Database availability issues

Replication lag exceeding threshold

Disk space critically low

Connection pool saturation

Transaction ID wraparound risk

Warning Alerts

Query time exceeding thresholds

High lock contention

Increasing WAL generation rate

CPU/Memory pressure

Backup failures

When defining alerts, balance between actionability and noise. Focus on alerts that:

Require immediate attention

Indicate potential service impact

Have clear remediation actions

Provide sufficient context for troubleshooting

Monitoring Integration

For a complete observability solution, integrate PostgreSQL monitoring with your broader

Kubernetes monitoring. This includes correlating database events with application logs,

Kubernetes events, and infrastructure metrics to provide a comprehensive view of system

health and performance.

Scaling and Performance Optimization

Kubernetes provides several mechanisms for scaling PostgreSQL databases to handle increased

load. However, database scaling requires careful planning and differs from scaling stateless

applications.

Vertical Scaling

Increasing resources for individual PostgreSQL instances:

Adjust CPU and memory requests/limits

Increase storage capacity

Optimize PostgreSQL for available resources

Consider node sizing and placement

Horizontal Scaling

Distributing load across multiple PostgreSQL instances:

Read replicas for query distribution

Connection pooling

Sharding for write distribution

Multi-master solutions (limited options)

Read Scaling with Replicas

One of the most common scaling approaches for PostgreSQL is to distribute read queries

across multiple replicas:

Implementation Approach

Scale the StatefulSet replicas

Configure streaming replication

Create a read-only service targeting replicas

Direct read queries to the replica service

Considerations

Replication lag impacts data consistency

Application must distinguish read/write operations

Load balancing between replicas

Monitoring replica performance

To implement this effectively, each Pod in the StatefulSet needs to be labeled

appropriately (primary or replica) based on its role. Most PostgreSQL operators

handle this labeling automatically.

Connection Pooling

Connection pooling is essential for scaling PostgreSQL, as each connection consumes

resources. Popular connection poolers include PgBouncer and Pgpool-II:

Lightweight connection pooling

Session, transaction, and statement pooling modes

Low overhead

Simple configuration

Connection pooling

Load balancing

Query caching

More complex configuration

Many PostgreSQL operators include optional connection pooling, which simplifies

deployment and ensures proper integration with the PostgreSQL cluster.

Resource Optimization

Optimizing PostgreSQL for Kubernetes involves proper resource configuration both

at the Kubernetes and PostgreSQL levels:

Kubernetes Resource Settings

Resource Requests:

Guaranteed minimum resources

Resource Limits:

Maximum allowed resources

QoS Class:

Aim for Guaranteed QoS

Pod Topology:

Anti-affinity for high availability

Pod Disruption Budgets:

Protect from disruptions

PostgreSQL Configuration

shared_buffers:

25-40% of available memory

effective_cache_size:

~75% of available memory

Based on concurrent connections and complex queries

maintenance_work_mem:

For maintenance operations

max_connections:

Appropriate for available resources

It's important to align PostgreSQL's configuration with the resources allocated

in Kubernetes. Most operators provide mechanisms to customize PostgreSQL configuration

based on the available resources.

Performance Tuning Workflow

Benchmark Baseline:

Establish current performance metrics

Identify Bottlenecks:

Use monitoring to find constraints

Optimize Queries:

Improve slow or inefficient queries

Tune Configuration:

Adjust PostgreSQL settings

Resource Scaling:

Increase resources if needed

Add Replicas:

Scale horizontally for read operations

Re-benchmark:

Measure improvements and iterate

Scaling Limitations

Remember that PostgreSQL has inherent scaling limitations as a traditional relational

database. While read scaling is relatively straightforward, write scaling is more

challenging without database sharding or specialized solutions. For applications with

extreme scaling requirements, consider distributed SQL options or purpose-built

database systems that are designed for horizontal scalability.

Common Challenges and Solutions

Running PostgreSQL on Kubernetes presents several unique challenges. Let's explore common

issues and their solutions to ensure a robust and reliable database deployment.

Stateful Application Challenges

PostgreSQL, as a stateful application, presents specific challenges in Kubernetes:

Data Persistence

Challenge:

Ensuring data survives Pod restarts and node failures.

Use PersistentVolumes with appropriate storage classes

Implement regular backups to external storage

Choose storage with data replication capabilities

Configure WAL archiving for point-in-time recovery

Network Identity

Challenge:

Maintaining stable network identity for primary-replica setups.

Use StatefulSets for stable Pod naming and DNS records

Implement proper headless services

Use separate services for primary and replica access

Configure proper service discovery mechanisms

Scaling Complexity

Challenge:

PostgreSQL doesn't natively scale horizontally for writes.

Focus on vertical scaling for write performance

Implement read replicas for read scaling

Use connection pooling to maximize connection efficiency

Consider database sharding for extreme scale

Resource Management

Challenge:

Databases have specific resource requirements that differ from stateless applications.

Set appropriate resource requests and limits

Use node affinity to place database Pods on appropriate nodes

Implement pod disruption budgets for high availability

Monitor resource usage and performance closely

High Availability Challenges

Achieving high availability for PostgreSQL in Kubernetes requires addressing

several specific challenges:

Failover Management

Challenge:

Handling primary node failures and promoting replicas reliably.

Use PostgreSQL operators with automated failover

Implement distributed consensus (e.g., etcd) for leader election

Configure proper health checks for quick failure detection

Test failover scenarios regularly

Client Connectivity

Challenge:

Ensuring applications can connect to the current primary after failover.

Use Kubernetes Services with appropriate selectors

Implement connection poolers that handle primary changes

Configure applications with retry logic and connection resilience

Use leader election services or proxies

Split Brain Prevention

Challenge:

Preventing multiple nodes from acting as primary simultaneously.

Use distributed consensus for leader election

Implement proper fencing mechanisms

Configure strict majority-based decision making

Use tools like Patroni with proper watchdog configurations

Data Consistency

Challenge:

Ensuring data consistency during failures and network partitions.

Configure synchronous replication for critical workloads

Implement proper transaction isolation levels

Use WAL archiving for recovery point objectives

Configure appropriate replication slot management

Operator Advantage

PostgreSQL operators significantly simplify high-availability challenges by implementing

industry best practices and automated management. They typically include built-in

solutions for failover, leader election, client routing, and split-brain prevention.

Upgrade and Maintenance Challenges

Upgrading PostgreSQL in Kubernetes environments presents unique challenges:

Version Upgrades

Challenge:

PostgreSQL major version upgrades require careful planning and execution.

Use operators with upgrade automation capabilities

Implement blue-green deployment strategies

Perform thorough testing in non-production environments

Maintain comprehensive backups before upgrades

Configuration Updates

Challenge:

Updating PostgreSQL configuration without service interruption.

Use ConfigMaps and update them with minimal disruption

Classify parameters by reload requirements (requires restart vs. reload)

Implement rolling updates for parameters requiring restarts

Use operators that handle configuration changes efficiently

Schema Changes

Challenge:

Implementing schema changes with minimal downtime.

Use zero-downtime migration techniques

Implement backward-compatible schema changes

Utilize PostgreSQL's transactional DDL capabilities

Consider tools like Liquibase or Flyway for schema management

Maintenance Operations

Challenge:

Performing routine maintenance without impacting service availability.

Schedule maintenance during low-traffic periods

Use read replicas to offload reporting and maintenance operations

Implement automatic VACUUM and ANALYZE

Use connection pooling to minimize impact on active connections

For all maintenance operations, thorough testing and having a reliable rollback plan

are essential. Always test procedures in non-production environments and ensure

comprehensive backups are available before making significant changes.

Best Practice

When running PostgreSQL in Kubernetes, document all operational procedures thoroughly,

including regular operations, maintenance tasks, and emergency procedures. This

documentation should cover both database-specific procedures and Kubernetes-related

operations, creating a comprehensive operational runbook for your database deployment.

1# Example: PITR with CloudNativePG operator
2apiVersion: postgresql.cnpg.io/v1
3kind: Cluster
4metadata:
5  name: cluster-restore-pitr
6spec:
7  instances: 3
8  storage:
9    size: 1Gi
10  bootstrap:
11    recovery:
12      source: cluster-source
13      recoveryTarget:
14        targetTime: "2023-03-01 12:00:00 UTC"
15  # Other configurations...

1# Example postgres-exporter deployment
2apiVersion: apps/v1
3kind: Deployment
4metadata:
5  name: postgres-exporter
6  labels:
7    app: postgres-exporter
8spec:
9  replicas: 1
10  selector:
11    matchLabels:
12      app: postgres-exporter
13  template:
14    metadata:
15      labels:
16        app: postgres-exporter
17      annotations:
18        prometheus.io/scrape: "true"
19        prometheus.io/port: "9187"
20    spec:
21      containers:
22      - name: postgres-exporter
23        image: quay.io/prometheuscommunity/postgres-exporter:v0.10.1
24        ports:
25        - containerPort: 9187
26          name: metrics
27        env:
28        - name: DATA_SOURCE_URI
29          value: "postgres-primary.default.svc.cluster.local:5432/postgres?sslmode=disable"
30        - name: DATA_SOURCE_USER
31          valueFrom:
32            secretKeyRef:
33              name: postgres-secret
34              key: username
35        - name: DATA_SOURCE_PASS
36          valueFrom:
37            secretKeyRef:
38              name: postgres-secret
39              key: password
40        - name: PG_EXPORTER_EXTEND_QUERY_PATH
41          value: "/etc/postgres_exporter/queries.yaml"
42        volumeMounts:
43        - name: custom-queries
44          mountPath: /etc/postgres_exporter
45        resources:
46          limits:
47            memory: "128Mi"
48            cpu: "100m"
49      volumes:
50      - name: custom-queries
51        configMap:
52          name: postgres-exporter-queries

1# Example Prometheus alerting rules for PostgreSQL
2groups:
3- name: PostgreSQLAlerts
4  rules:
5  - alert: PostgreSQLHighConnectionCount
6    expr: sum(pg_stat_activity_count) by (datname) > 100
7    for: 5m
8    labels:
9      severity: warning
10    annotations:
11      summary: "High PostgreSQL connection count"
12      description: "Database {{ $labels.datname }} has {{ $value }} connections"
13
14  - alert: PostgreSQLReplicationLag
15    expr: pg_replication_lag > 300
16    for: 1m
17    labels:
18      severity: critical
19    annotations:
20      summary: "PostgreSQL replication lag"
21      description: "Replication lag is {{ $value }} seconds"
22
23  - alert: PostgreSQLDiskSpaceLow
24    expr: (node_filesystem_avail_bytes{mountpoint="/var/lib/postgresql/data"} / node_filesystem_size_bytes{mountpoint="/var/lib/postgresql/data"}) * 100 < 10
25    for: 5m
26    labels:
27      severity: critical
28    annotations:
29      summary: "PostgreSQL disk space low"
30      description: "PostgreSQL data directory has less than 10% free space"

1# Example read-only service for PostgreSQL replicas
2apiVersion: v1
3kind: Service
4metadata:
5  name: postgres-replica
6  labels:
7    app: postgres
8    role: replica
9spec:
10  type: ClusterIP
11  ports:
12  - port: 5432
13    targetPort: 5432
14    name: postgres
15  selector:
16    app: postgres
17    role: replica  # Label selector targets replica Pods

1# Example PgBouncer Deployment
2apiVersion: apps/v1
3kind: Deployment
4metadata:
5  name: pgbouncer
6spec:
7  replicas: 2
8  selector:
9    matchLabels:
10      app: pgbouncer
11  template:
12    metadata:
13      labels:
14        app: pgbouncer
15    spec:
16      containers:
17      - name: pgbouncer
18        image: edoburu/pgbouncer:1.16.0
19        ports:
20        - containerPort: 5432
21        env:
22        - name: DB_USER
23          value: postgres
24        - name: DB_PASSWORD
25          valueFrom:
26            secretKeyRef:
27              name: postgres-secrets
28              key: postgres-password
29        - name: DB_HOST
30          value: postgres-primary
31        - name: DB_NAME
32          value: postgres
33        - name: POOL_MODE
34          value: transaction
35        - name: MAX_CLIENT_CONN
36          value: "1000"
37        - name: DEFAULT_POOL_SIZE
38          value: "20"

1# Example PostgreSQL resource configuration
2apiVersion: apps/v1
3kind: StatefulSet
4metadata:
5  name: postgres
6spec:
7  # ... other configuration ...
8  template:
9    spec:
10      containers:
11      - name: postgres
12        # ... other configuration ...
13        resources:
14          requests:
15            memory: "2Gi"
16            cpu: "1000m"
17          limits:
18            memory: "2Gi"
19            cpu: "1000m"
20        env:
21        - name: POSTGRES_PASSWORD
22          valueFrom:
23            secretKeyRef:
24              name: postgres-secrets
25              key: postgres-password
26        - name: POSTGRES_DB
27          value: "mydb"
28        # PostgreSQL configuration
29        - name: POSTGRES_INITDB_ARGS
30          value: "--data-checksums"
31        - name: POSTGRES_INITDB_WALDIR
32          value: "/var/lib/postgresql/wal"
33        # Custom configuration via ConfigMap
34        volumeMounts:
35        - name: postgres-config
36          mountPath: /etc/postgresql/postgresql.conf
37          subPath: postgresql.conf
38      volumes:
39      - name: postgres-config
40        configMap:
41          name: postgres-config

Running PostgreSQL on Kubernetes offers powerful capabilities for automation, scalability, and

operational efficiency. While it presents unique challenges compared to traditional deployments,

the benefits of a cloud-native approach make it increasingly popular for modern applications.

Key Takeaways

Use StatefulSets for stable network identity and ordered operations

Choose appropriate storage classes for persistent data

Consider PostgreSQL operators for simplified management

Implement comprehensive monitoring and alerting

Plan for backup, recovery, and high availability

Address scaling requirements through read replicas and connection pooling

Decision Factors

Self-Managed vs. Managed:

Evaluate operational overhead vs. control

Operator Selection:

Choose based on feature set and maturity

Storage Options:

Balance performance, reliability, and cost

High Availability:

Define RTO/RPO requirements

Resource Allocation:

Size appropriately for workload

As database workloads continue to move to Kubernetes, the ecosystem of tools and best practices

continues to evolve. Stay informed about new capabilities in PostgreSQL operators, Kubernetes

storage options, and cloud-native database technologies to ensure your database deployments

remain efficient, reliable, and scalable.

Further Learning

Kubernetes: Run a Replicated Stateful Application

Zalando Postgres Operator Documentation

Crunchy PostgreSQL Operator Documentation

CloudNativePG Documentation

Patroni: High Availability for PostgreSQL