Maintaining highly available and resilient PostgreSQL databases is crucial for business continuity in today’s cloud-native landscape. The Cloud Native PostgreSQL Operator provides robust capabilities for managing PostgreSQL clusters in Kubernetes environments, particularly in handling failover scenarios and implementing disaster recovery strategies.

In this blog post, we’ll explore the key features of the Cloud Native PostgreSQL Operator for managing failover and disaster recovery. We’ll discuss how it ensures high availability, implements automatic failover, and facilitates disaster recovery processes. Additionally, we’ll look at best practices for configuring and managing PostgreSQL clusters using this operator in Kubernetes environments.

Why to run Postgres on Kubernetes?

Running PostgreSQL on Kubernetes offers several advantages for modern, cloud-native applications:

1. Stateful Workload Readiness: Contrary to old beliefs, Kubernetes is now ready for stateful workloads like databases. A 2021 survey by the Data on Kubernetes Community revealed that 90% of respondents believe Kubernetes is suitable for stateful workloads, with 70% already running databases in production.
2. Immutable Application Containers: CloudNativePG leverages immutable application containers, enhancing deployment safety and repeatability. This approach aligns with microservice architecture principles and simplifies updates and patching.
3. Cloud-Native Benefits: Running PostgreSQL on Kubernetes embraces cloud-native principles, fostering a DevOps culture, enabling microservice architectures, and providing robust container orchestration.
4. Automated Management: Kubernetes operators like CloudNativePG extend Kubernetes controllers to manage complex applications like PostgreSQL, handling deployments, failovers, and other critical operations automatically.
5. Declarative Configuration: CloudNativePG allows for declarative configuration of PostgreSQL clusters, simplifying change management and enabling Infrastructure as Code practices.
6. Resource Optimization: Kubernetes provides efficient resource management, allowing for better utilization of infrastructure and easier scaling of database workloads.
7. High Availability and Disaster Recovery: Kubernetes facilitates the implementation of high availability architectures across availability zones and enables efficient disaster recovery strategies.
8. Streamlined Operations with Operators: Using operators like CloudNativePG automates all the tasks mentioned above, significantly reducing operational complexity. These operators act as PostgreSQL experts in code form, handling intricate database management tasks such as failovers, backups, and scaling with minimal human intervention. This not only increases reliability but also frees up DBAs and DevOps teams to focus on higher-value activities, ultimately leading to more robust and efficient database operations in Kubernetes environments.

By leveraging Kubernetes for PostgreSQL deployments, organizations can benefit from increased automation, improved scalability, and enhanced resilience for their database infrastructure, with operators like CloudNativePG further simplifying and optimizing these processes.

List of Postgres Operators

Kubernetes operators represent an innovative approach to managing applications within a Kubernetes environment by encapsulating operational knowledge and best practices. These extensions automate the deployment and maintenance of complex applications, such as databases, ensuring smooth operation in a Kubernetes setup.

The Cloud Native PostgreSQL Operator is a prime example of this concept, specifically designed to manage PostgreSQL clusters on Kubernetes. This operator automates various database management tasks, providing a seamless experience for users. Some key features include direct integration with the Kubernetes API server for high availability without relying on external tools, self-healing capabilities through automated failover and replica recreation, and planned switchover of the primary instance to maintain data integrity during maintenance or upgrades.

Additionally, the operator supports scalable architecture with the ability to manage multiple instances, declarative management of PostgreSQL configuration and roles, and compatibility with Local Persistent Volumes and separate volumes for WAL files. It also offers continuous backup solutions to object stores like AWS S3, Azure Blob Storage, and Google Cloud Storage, ensuring data safety and recoverability. Furthermore, the operator provides full recovery and point-in-time recovery options from existing backups, TLS support with client certificate authentication, rolling updates for PostgreSQL minor versions and operator upgrades, and support for synchronous replicas and HA physical replication slots. It also offers replica clusters for multi-cluster PostgreSQL deployments, connection pooling through PgBouncer, a native customizable Prometheus metrics exporter, and LDAP authentication support.

By leveraging the Cloud Native PostgreSQL Operator, organizations can streamline their database management processes on Kubernetes, reducing manual intervention and ensuring high availability, scalability, and security in their PostgreSQL deployments. This operator showcases how Kubernetes operators can significantly enhance application management within a cloud-native ecosystem.

Here are the most popular PostgreSQL operators:

1. CloudNativePG (formerly known as Cloud Native PostgreSQL Operator)
2. Crunchy Data Postgres Operator (first released in 2017)
3. Zalando Postgres Operator (first released in 2017)
4. StackGres (released in 2020)
5. Percona Operator for PostgreSQL (released in 2021)
6. Kubegres (released in 2021)
7. Patroni (for HA PostgreSQL solutions using Python.)

Understanding Failover in PostgreSQL

Primary-Replica Architecture

In a PostgreSQL cluster, the primary-replica (formerly master-slave) architecture consists of:

• Primary Node: Handles all write operations and read operations
• Replica Nodes: Maintain synchronized copies of the primary node’s data

Automatic Failover Process

When the primary node becomes unavailable, the operator initiates the following process:

1. Detection: Continuous health monitoring identifies primary node failure
2. Election: A replica is selected to become the new primary
3. Promotion: The chosen replica is promoted to primary status
4. Reconfiguration: Other replicas are reconfigured to follow the new primary
5. Service Updates: Kubernetes services are updated to point to the new primary

Implementing Disaster Recovery

Backup Strategies

The operator supports multiple backup approaches:

1. Volume Snapshots

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: postgresql-cluster
spec:
  instances: 3
  backup:
    volumeSnapshot:
      className: csi-hostpath-snapclass
      enabled: true
      snapshotOwnerReference: true

2. Barman Integration

spec:
  backup:
    barmanObjectStore:
      destinationPath: 's3://backup-bucket/postgres'
      endpointURL: 'https://s3.amazonaws.com'
      s3Credentials:
        accessKeyId:
          name: aws-creds
          key: ACCESS\_KEY\_ID
        secretAccessKey:
          name: aws-creds
          key: ACCESS\_SECRET\_KEY

Disaster Recovery Procedures

1. Point-in-Time Recovery (PITR)
   • Enables recovery to any specific point in time
   • Uses WAL (Write-Ahead Logging) archives
   • Minimizes data loss
2. Cross-Region Recovery
   • Maintains backup copies in different geographical regions
   • Enables recovery in case of regional failures

Demo

This section provides a step-by-step guide to setting up a CloudNative PostgreSQL cluster, testing failover, and performing disaster recovery.

1. Installation

Method 1: Direct Installation

kubectl apply --server-side -f \
https://raw.githubusercontent.com/cloudnative-pg/cloudnative-pg/main/releases/cnpg-1.24.0.yaml

Method 2: Helm Installation

helm repo add cnpg https://cloudnative-pg.github.io/charts
helm upgrade --install cnpg \
  --namespace cnpg-system \
  --create-namespace \
  cnpg/cloudnative-pg

Verify the Installation

kubectl get deployment -n cnpg-system cnpg-controller-manager

Install CloudNativePG Plugin

CloudNativePG provides a plugin for kubectl to manage a cluster in Kubernetes. You can install the cnpg plugin using a variety of methods.

Via the installation script

curl -sSfL \
  https://github.com/cloudnative-pg/cloudnative-pg/raw/main/hack/install-cnpg-plugin.sh | \
  sudo sh -s -- -b /usr/local/bin

If you already have Krew installed, you can simply run:

kubectl krew install cnpg

2. Create S3 Credentials Secret

First, create an S3 bucket and an IAM user with S3 access. Then, create a Kubernetes secret with the IAM credentials:

kubectl create secret generic s3-creds \
  --from-literal=ACCESS_KEY_ID=your_access_key_id \
  --from-literal=ACCESS_SECRET_KEY=your_secret_access_key

3. Create PostgreSQL Cluster

Create a file named cluster.yaml with the following content:

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: example
spec:
  backup:
    barmanObjectStore:
      destinationPath: 's3://your-bucket-name/retail-master-db'
      s3Credentials:
        accessKeyId:
          name: s3-creds
          key: ACCESS_KEY_ID
        secretAccessKey:
          name: s3-creds
          key: ACCESS_SECRET_KEY
  instances: 2
  imageName: ghcr.io/clevyr/cloudnativepg-timescale:16-ts2
  postgresql:
    shared_preload_libraries:
      - timescaledb
  bootstrap:
    initdb:
      postInitTemplateSQL:
        - CREATE EXTENSION IF NOT EXISTS timescaledb;
  storage:
    size: 20Gi

Apply the configuration to create cluster:

kubectl apply -f cluster.yaml

Verify the cluster status:

kubectl cnpg status example

4. Getting Access

Deploying a cluster is one thing, actually accessing it is entirely another. CloudNativePG creates three services for every cluster, named after the cluster name. In our case, these are:

kubectl get service

• example-rw: Always points to the Primary node
• example-ro: Points to only Replica nodes (round-robin)
• example-r: Points to any node in the cluster (round-robin)

5. Insert Data

Create a PostgreSQL client pod:

kubectl run pgclient --image=postgres:13 --command -- sleep infinity

Connect to the database:

kubectl exec -ti example-1 -- psql app

Create a table and insert data:

CREATE TABLE stocks_real_time (
  time TIMESTAMPTZ NOT NULL,
  symbol TEXT NOT NULL,
  price DOUBLE PRECISION NULL,
  day_volume INT NULL
);

SELECT create_hypertable('stocks_real_time', by_range('time'));
CREATE INDEX ix_symbol_time ON stocks_real_time (symbol, time DESC);
GRANT ALL PRIVILEGES ON TABLE stocks_real_time TO app;

INSERT INTO stocks_real_time (time, symbol, price, day_volume)
VALUES
  (NOW(), 'AAPL', 150.50, 1000000),
  (NOW(), 'GOOGL', 2800.75, 500000),
  (NOW(), 'MSFT', 300.25, 750000);

6. Failover Test

Force a backup:

kubectl cnpg backup example

Initiate failover by deleting the primary pod:

kubectl delete pod example-1

Monitor the cluster status:

kubectl cnpg status example

Key observations during failover:

1. Initial status: “Switchover in progress”
2. After approximately 2 minutes 15 seconds: “Waiting for instances to become active”
3. After approximately 3 minutes 30 seconds: Complete failover with new primary

Verify data integrity after failover through service:

Retrieve the database password:

kubectl get secret example-app -o \
  jsonpath="{.data.password}" | base64 --decode

Connect to the database using the password:

kubectl exec -it pgclient -- psql -h example-rw -U app

Execute the following SQL queries:

# Confirm the count matches the number of rows inserted earlier. It will show 3
SELECT COUNT(*) FROM stocks_real_time;

#Insert new data to test write capability after failover:
INSERT INTO stocks_real_time (time, symbol, price, day_volume)
VALUES (NOW(), 'NFLX', 500.75, 300000);


SELECT * FROM stocks_real_time ORDER BY time DESC LIMIT 1;

Check read-only service:

kubectl exec -it pgclient -- psql -h example-ro -U app

Once connected, execute:

SELECT COUNT(*) FROM stocks_real_time;

Review logs of both pods:

kubectl logs example-1
kubectl logs example-2

Examine the logs for relevant failover information.

Perform a final cluster status check:

kubectl cnpg status example

Confirm both instances are running and roles are as expected.

7. Backup and Restore Test

First, check the current status of your cluster:

kubectl cnpg status example

Note the current state, number of instances, and any important details.

Promote the example-1 node to Primary:

kubectl cnpg promote example example-1

Monitor the promotion process, which typically takes about 3 minutes to complete.

Check the updated status of your cluster, then create a new backup:

kubectl cnpg backup example –backup-name=example-backup-1

Verify the backup status:

kubectl get backups
NAME               AGE   CLUSTER   METHOD              PHASE       ERROR
example-backup-1   38m   example   barmanObjectStore   completed

Delete the Original Cluster then prepare for the recovery test:

kubectl delete cluster example

There are two methods available to perform a Cluster Recovery bootstrap from another cluster. For further details, please refer to the documentation. There are two ways to achieve this result in CloudNativePG:

• Using a recovery object store, that is a backup of another cluster created by Barman Cloud and defined via the barmanObjectStore option in the externalClusters section (recommended)
• Using an existing Backup object in the same namespace (this was the only option available before version 1.8.0).

Method 1: Recovery from an Object Store

You can recover from a backup created by Barman Cloud and stored on supported object storage. Once you have defined the external cluster, including all the required configurations in the barmanObjectStore section, you must reference it in the .spec.recovery.source option.

Create a file named example-object-restored.yaml with the following content:

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: example-object-restored
spec:
  instances: 2
  imageName: ghcr.io/clevyr/cloudnativepg-timescale:16-ts2
  postgresql:
    shared_preload_libraries:
      - timescaledb
  storage:
    size: 1Gi
  bootstrap:
    recovery:
      source: example
  externalClusters:
    - name: example
      barmanObjectStore:
        destinationPath: 's3://your-bucket-name'
        s3Credentials:
          accessKeyId:
            name: s3-creds
            key: ACCESS_KEY_ID
          secretAccessKey:
            name: s3-creds
            key: ACCESS_SECRET_KEY

Apply the restored cluster configuration:

kubectl apply -f example-object-restored.yaml

Monitor the restored cluster status:

kubectl cnpg status example-object-restored

Retrieve the database password:

kubectl get secret example-object-restored-app \
  -o jsonpath="{.data.password}" | base64 --decode

Connect to the restored database:

kubectl exec -it pgclient -- psql -h example-object-restored-rw -U app

Verify the restored data by executing the following SQL queries:

# it should show 4
SELECT COUNT(*) FROM stocks_real_time;
SELECT * FROM stocks_real_time;

The successful execution of these steps to recover from an object store confirms the effectiveness of the backup and restore process.

Delete the example-object-restored Cluster then prepare for the backup object restore test:

kubectl delete cluster example-object-restored

Method 2: Recovery from Backup Object

In case a Backup resource is already available in the namespace in which the cluster should be created, you can specify its name through .spec.bootstrap.recovery.backup.name

Create a file named example-restored.yaml:

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: example-restored
spec:
  instances: 2
  imageName: ghcr.io/clevyr/cloudnativepg-timescale:16-ts2
  postgresql:
    shared_preload_libraries:
      - timescaledb
  storage:
    size: 1Gi
  bootstrap:
    recovery:
      backup:
        name: example-backup-1

Apply the restored cluster configuration:

kubectl apply -f example-restored.yaml

Monitor the restored cluster status:

kubectl cnpg status example-restored

Retrieve the database password:

kubectl get secret example-restored-app \
  -o jsonpath="{.data.password}" | base64 --decode

Connect to the restored database:

kubectl exec -it pgclient -- psql -h example-restored-rw -U app

Verify the restored data by executing the following SQL queries:

SELECT COUNT(*) FROM stocks_real_time;
SELECT * FROM stocks_real_time;

The successful execution of these steps confirms the effectiveness of the backup and restore process.

Kubernetes Events and Logs

1. Failover Events

Monitor events using:

# Watch cluster events
kubectl get events --watch | grep postgresql

# Get specific cluster events
kubectl describe cluster example | grep -A 10 Events

• Key events to monitor:
  • Primary selection process
  • Replica promotion events
  • Connection switching events
  • Replication status changes

2. Backup Status

Monitor backup progress:

# Check backup status
kubectl get backups

# Get detailed backup info
kubectl describe backup example-backup-1

• Key metrics:
  • Backup duration
  • Backup size
  • Compression ratio
  • Success/failure status

3. Recovery Process

Monitor recovery status:

# Watch recovery progress
kubectl cnpg status example-restored

# Check recovery logs
kubectl logs example-restored-1 \-c postgres

• Important recovery indicators:
  • WAL replay progress
  • Timeline switches
  • Recovery target status

Conclusion

The Cloud Native PostgreSQL Operator significantly simplifies the management of PostgreSQL clusters in Kubernetes environments. By following these practices for failover and disaster recovery, organizations can maintain highly available database systems that recover quickly from failures while minimizing data loss. Remember to regularly test your failover and disaster recovery procedures to ensure they work as expected when needed. Continuous monitoring and proactive maintenance are key to maintaining a robust PostgreSQL infrastructure.

Everything fails, all the time. ~ Werner Vogels, CTO, Amazon Web services

Editoral: And if you are looking for looking for a distributed, scalable, reliable, and durable storage for your PostgreSQL cluster in Kubernetes or any other Kubernetes storage need, simplyblock is the solution you’re looking for.

The post How to Build Scalable and Reliable PostgreSQL Systems on Kubernetes appeared first on simplyblock.

Therefore, a Postgres Operator is a critical component of the database setup. In addition to the “simply” management of the Postgres cluster, it often also provides additional integration with external tools like pgpool II or pgbouncer (for connection pooling), pgbackrest or Barman (for backup and restore), as well as Postgres extension management.

Critical components should be chosen wisely. Oftentimes, it’s hard to exchange one tool for another later on. Additionally, depending on your requirements, you may need a tool with commercial support available, while others prefer the vast open source ecosystem. If both, even better.

But how do you choose your Postgres Operator of choice? Let’s start with a quick introduction of the most common options.

Stolon

Stolon is one of the oldest operators available. Originally released in November 2015 it even predates the term Kubernetes Operator .

The project is well known and has over 4.5k stars on GitHub. However the age shows. Many features aren’t cloud-native, it doesn’t support CRDs (custom resource definition), hence configuration isn’t according to Kubernetes. All changes are handled through a command line tool. Apart from that, the last release 0.17.0 is from September 2021, and there isn’t really any new activity on the repository. You can read that as “extremely stable” or “almost abandoned”. I guess both views on the matter are correct. Anyhow, from my point of view, the lack of activity is concerning for a tool that’s potentially being used for 5-10 years, especially in a fast-paced world like the cloud-native one.

Personally, I don’t recommend using it for new setups. I still wanted to mention it though since it deserves it. It did and does a great job for many people. Still, I left it out of the comparison table further below.

CloudNativePG

CloudNativePG is the new kid on the block, or so you might think. Its official first commit happened in March 2022. However, CloudNativePG was originally developed and sponsored by EDB (EnterpriseDB), one of the oldest companies built around PostgreSQL.

As the name suggests, CloudNativePG is designed to bring the full cloud-native feeling across. Everything is defined and configured using CRDs. No matter if you need to create a new Postgres cluster, want to define backup schedules, configure automatic failover, or scale up and down. It’s all there. Fully integrated into your typical Kubernetes way of doing things. Including a plugin for kubectl.

On GitHub , CloudNativePG skyrocketed in terms of stargazers and collected over 3.6k stars over the course of its lifetime. And while it’s not officially a CNCF (Cloud Native Computing Foundation) project, the CNCF stands pretty strongly behind it. And not to forget, the feature set is on par with older projects. No need to hide here.

All in all, CloudNativePG is a strong choice for a new setup. Fully in line with your Kubernetes workflows, feature rich, and a strong and active community.

Editor’s note: Btw, we had Jimmy Angelakos as a guest on the Cloud Commute podcast , and he talks about CloudNativePG. You should listen in.

Crunchy Postgres Operator (PGO)

PGO, or the Postgres Operator from Crunchy Data , has been around since March 2017 and is a very beloved choice by a good chunk of people. On GitHub, PGO has over 3.7k stars, an active community, quick response times to issues, and overall a pretty stable timeline activity.

Postgres resources are defined and managed through CRDs, as are Postgres users. Likewise, PGO provides integration with a vast tooling ecosystem, such as patroni (for automatic failover), pgbackrest (for backup management), pgbouncer (connection pooling), and more.

A good number of common extensions are provided out of the box, however, adding additional extensions is a little bit more complicated.

Overall, Crunchy PGO is a solid, production-proven option, also into the future. While not as fresh and hip as CloudNativePG anymore, it checks all the necessary marks, and it does so for many people for many years.

OnGres StackGres

StackGres by OnGres is also fairly new and tries to do a few things differently. While all resources can be managed through CRDs, they can also be managed through a CLI, and even a web interface. Up to the point that you can create a resource through a CRD, change a small property through the CLI, and scale the Postgres cluster using the webui. All management interfaces are completely interchangeable.

Same goes for extension management. StackGres has the biggest amount of supported Postgres extensions available, and it’s hard to find an extension which isn’t supported out of the box.

In terms of tooling integration, StackGres supports all the necessary tools to build a highly available, fault tolerant, automatically backed up and restored, scalable cluster.

In comparison to some of the other operators I really like the independent CRD types, giving a better overview of a specific resource instead of bundling all of the complexity of the Postgres and tooling ecosystem configuration in one major CRD with hundreds and thousands of lines of code.

StackGres is my personal favorite, even though it only accumulated around 900 starts on GitHub so far.

While still young, the team behind it is a group of veteran PostgreSQL folks. They just know what they do. If you prefer a bit of a bigger community, and less of a company driven project, you’ll be better off with CloudNativePG, but apart from that, StackGres is the way to go.

Editor’s note: We had Álvaro Hernández, the founder and CEO of OnGres , in our Cloud Commute podcast, talking about StackGres and why you should use it. Don’t miss the first hand information.

AppsCode KubeDB

KubeDB by AppsCode is different. In development since 2017, it’s well known in the community, but an all-in commercial product. That said, commercial support is great and loved.

Additionally, KubeDB isn’t just PostgreSQL. It supports Elasticsearch, MongoDB, Redis, Memcache, MySQL, and more. It’s a real database-in-a-box deployment solution.

All boxes are checked for KubeDB and there isn’t much to say about it other than, if you need a commercially supported operator for Postgres, look no further than KubeDB.

Zalando Postgres Operator

Last but not least, the Postgres Operator from Zalando . Yes, that Zalando, the one that sells shoes and clothes.

Zalando is a big Postgres user and started early with the cloud-native journey. Their operator has been around since 2017 and has a sizable fanbase. On GitHub the operator managed to collect over 4k stars, has a very active community, a stable release cadence, and is a great choice.

In terms of integrations with the tooling ecosystem, it provides less flexibility and is slightly opinionated towards how things are done. It was developed for Zalando’s own infrastructure first and foremost though.

Anyhow, the Zalando operator has been and still is a great choice. I actually used it myself for my previous startup and it just worked.

Which Postgres Kubernetes Operator should I Use?

You already know the answer, it depends. I know we all hate that answer, but it is true. As hinted at in the beginning, if you need commercial support certain options are already out of the scope.

It also depends if you already have another Postgres cluster with an operator running. If it works, is there really a reason to change it or introduce another one for the new cluster?

Anyhow, below is a quick comparison table of features and supported versions that I think are important.

Supported PostgreSQL Features

High Availability and Backup Features

Kubernetes Specific Features

How to choose your Postgres Operator?

The graph below shows the GitHub stars of the above Postgres Operators. What we see is a clear domination of Stolon, PGO, and Zalando’s PG Operator, with CloudNativePG rushing in from behind. StackGres, while around longer than CloudNativePG doesn’t have the community backing behind it, yet. But GitHub stars aren’t everything.

All of the above tools are great options, with the exception of Stolon, which isn’t a bad tool, but I’m concerned about the lack of activity. Make of it what you like.

Before closing, I want to quickly give some honorable mentions to two further tools.

Percona has an operator for PostgreSQL, but the community is very small right now. Let’s see if they manage to bring it on par with the other tools. If you use other Percona tools, it’s certainly worth giving it a look: Percona Operator for PostgreSQL .

The other one is the External PostgreSQL Server Operator by MoveToKube . It didn’t really fit the topic of this blog post as it’s less of a Postgres Operator but more of a database (in Postgres’ relational entity sense) and users management tool. Meaning, it uses CRDs to add, update, remove databases in external PG servers, as it does for Postgres users. Anyhow, this tool also works with services like Timescale Cloud, Amazon RDS, and many more. Worth mentioning and maybe you can make use of it in the future.

The post How to choose your Kubernetes Postgres Operator? appeared first on simplyblock.