Disaster Recovery Strategies for PostgreSQL Deployments on Kubernetes (Part 2)

-


In my previous post, I described how to use multi-cluster architecture provided by Crunchy PostgreSQL Operator to achieve disaster recovery and high availability on Kubernetes.

As mentioned in the previous post, both  Crunchy PostgreSQL Operator and Zalando PostgreSQL Operator support for multi-cluster deployments. In this post, I am going to describe how to use Zalando PostgreSQL Operator to deploy active/standby PostgreSQL clusters.

Multi-Cluster Architecture in Zalando PostgreSQL Operator

Below is the multi-cluster architecture in Zalando PostgreSQL Operator.

The basic mechanism is same as Crunchy PostgreSQL Operator. But you need to be aware of the following: 

  • At the moment, S3 is only allowed for deploying a standby cluster. This means if you want to setup a standby cluster, you must use S3 for the external storage. On the other hand, Crunchy PostgreSQL Operator allows you to use both S3 and GCS to deploy a standby cluster.
  • As mentioned in the documentation, it is recommended to deploy standby cluster with only one pod, because the PostgreSQL pods in the standby PostgreSQL cluster are only be allowed to read the WAL archive from the external storage. On the other hand, Crunchy PostgreSQL Operator allows the pods using cascading replication in the standby cluster.
  • After failover, the previous active cluster can't be recovered as a standby. You need to recreate a standby cluster.
  • Primary pod in the active cluster sends WALs to the external storage every archive_timeout. If you want to apply the changes on the standby cluster as quickly as possible, you may tune archive_timeout parameter. The default value is 1800s.

Next, let's verify the multi-cluster architecture in Zalando PostgreSQL Operator on Amazon EKS. 

Creating prerequisite resources

First you need to create the prerequisite resources. Assuming you have installed eksctl and AWS CLI.

Create EKS clusters

Create two EKS clusters in different regions.

$ eksctl create cluster --name k8s-cluster1  
--version 1.20 \
--region us-west-1 \
--nodegroup-name k8s-cluster1-workers \
--node-type t3.medium  \
--nodes 2 \
--nodes-min 1 \  
--nodes-max 3
$ eksctl create cluster --name k8s-cluster2 \
--version 1.20 \
--region us-west-2 \
--nodegroup-name k8s-cluster2-workers \ 
--node-type t3.medium \ 
--nodes 2 \
--nodes-min 1 \ 
--nodes-max 3

Create S3 bucket

Create a S3 bucket to store the backups and WALs. 

$ aws s3 mb s3://zalando-postgres-backup --region us-west-2

Create IAM role

In this example, we use kube2iam DaemonSet for accessing Amazon S3 on Kubernetes clusters with the IAM role.

Create an IAM role postgres-pod-role and attach AmazonS3FullAccess policy to the role.

Confirm Role ARN attached to each cluster worker node and specify them in pod-role-trust-policy.json.

$ eksctl get cluster k8s-cluster1 -o yaml --region us-west-1 | grep RoleArn
  RoleArn: arn:aws:iam::963139724110:role/eksctl-k8s-cluster1-cluster-ServiceRole-1L6RZB9M2BUUN 
$ eksctl get cluster k8s-cluster2 -o yaml --region us-west-2 | grep RoleArn
  RoleArn: arn:aws:iam::963139724110:role/eksctl-k8s-cluster2-cluster-ServiceRole-14X22DA5P09U9
$ cat pod-role-trust-policy.json 
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "Service": "ec2.amazonaws.com"
      },
      "Action": "sts:AssumeRole"
    },
    {
      "Effect": "Allow",
      "Principal": {
        "AWS": "arn:aws:iam::963139724110:role/eksctl-k8s-cluster1-nodegroup-k8s-NodeInstanceRole-8G9IQIJJ6YVG"
      },
      "Action": "sts:AssumeRole"
    },
    {
      "Effect": "Allow",
      "Principal": {
        "AWS": "arn:aws:iam::963139724110:role/eksctl-k8s-cluster2-nodegroup-k8s-NodeInstanceRole-1D53FRGX13NSG"
      },
      "Action": "sts:AssumeRole"
    }
  ]
}
$ aws iam create-role \
  --role-name postgres-pod-role \
  --assume-role-policy-document file://pod-role-trust-policy.json


$ aws iam attach-role-policy \
  --role-name postgres-pod-role \
  --policy-arn arn:aws:iam::aws:policy/AmazonS3FullAccess

Deploy active PostgreSQL cluster

First, let's deploy the active PostgreSQL cluster in EKS cluster1. We use kubectl config use-context command to set the default context.

1. Switch to cluster1

$ kubectl config use-context <cluster 1>

2. Create kube2iam DaemonSet. 

$ cat >> $HOME/kube2iam-sa.yaml <<EOF
apiVersion: v1
kind: ServiceAccount
metadata:
  name: kube2iam
  namespace: kube-system
---
apiVersion: rbac.authorization.k8s.io/v1
kind: ClusterRole
metadata:
  name: kube2iam
rules:
  - apiGroups: [""]
    resources: ["namespaces","pods"]
    verbs: ["get","watch","list"]
---
apiVersion: rbac.authorization.k8s.io/v1
kind: ClusterRoleBinding
metadata:
  name: kube2iam
subjects:
- kind: ServiceAccount
  name: kube2iam
  namespace: kube-system
roleRef:
  kind: ClusterRole
  name: kube2iam
  apiGroup: rbac.authorization.k8s.io
---
EOF

$ cat >> $HOME/kube2iam.yaml <<EOF
apiVersion: apps/v1
kind: DaemonSet
metadata:
  name: kube2iam
  namespace: kube-system
  labels:
    app: kube2iam
spec:
  selector:
    matchLabels:
      name: kube2iam
  template:
    metadata:
      labels:
        name: kube2iam
    spec:
      serviceAccountName: kube2iam
      hostNetwork: true
      containers:
        - image: jtblin/kube2iam:latest
          name: kube2iam
          args:
            - "--base-role-arn=arn:aws:iam::963139724110:role/"
            - "--iptables=true"
            - "--host-ip=$(HOST_IP)"
            - "--node=$(NODE_NAME)"
            - "--host-interface=eni+"
          env:
            - name: HOST_IP
              valueFrom:
                fieldRef:
                  fieldPath: status.podIP
            - name: NODE_NAME
              valueFrom:
                fieldRef:
                  fieldPath: spec.nodeName
          ports:
            - containerPort: 8181
              hostPort: 8181
              name: http
          securityContext:
            privileged: true
EOF

$ kubectl apply -f $HOME/kube2iam-sa.yaml
$ kubectl apply -f $HOME/kube2iam.yaml

3. Clone the repository.

$ git clone https://github.com/zalando/postgres-operator.git
$ cd postgres-operator/manifests/

4. Create a ConfigMap to define environment variables used to access to Amazon S3.

$ cat >> my-custom-config.yaml <<EOF
apiVersion: v1
kind: ConfigMap
metadata:
  name: my-custom-config
  namespace: default
data:
  AWS_REGION: us-west-2
  WAL_S3_BUCKET: zalando-postgres-backup
EOF

5. Edit configmap.yaml

$ git diff configmap.yaml 
-  # kube_iam_role: ""
+  kube_iam_role: "postgres-pod-role"
...
-  # pod_environment_configmap: "default/my-custom-config"
+  pod_environment_configmap: "default/my-custom-config"

6. Edit the example manifest complete-postgres-manifest.yaml to deploy PostgreSQL pods. Here, we specify archive_timeout to 60s to send the WALs to S3 every 60 seconds.

$ vi complete-postgres-manifest.yaml 
... 
  postgresql:
    version: "13"
    parameters:  # Expert section
    ...
      archive_timeout: "60"

7. Deploy the active cluster. 

$ kubectl apply -f my-custom-config.yaml
$ kubectl apply -f configmap.yaml
$ kubectl apply -f operator-service-account-rbac.yaml
$ kubectl apply -f postgres-operator.yaml
$ kubectl apply -f api-service.yaml
$ kubectl apply -f complete-postgres-manifest.yaml
$ kubectl get pod
NAME                                 READY   STATUS    RESTARTS   AGE
acid-test-cluster-0                  1/1     Running   0          79s
acid-test-cluster-1                  1/1     Running   0          78s
postgres-operator-55b8549cff-lhcnq   1/1     Running   0          89s

Deploy standby PostgreSQL cluster

Next, let's deploy the standby PostgreSQL cluster in EKS cluster2.

1. Switch to cluster 2

$ kubectl config use-context <cluster 2>

2. Create kube2iam DaemonSet.

$ kubectl apply -f $HOME/kube2iam-sa.yaml
$ kubectl apply -f $HOME/kube2iam.yaml

3. Edit the example manifest standby-manifest.yaml.

$ git diff standby-manifest.yaml
...
standby:
-    s3_wal_path: "s3://path/to/bucket/containing/wal/of/source/cluster/"
+    s3_wal_path: "s3://zalando-postgres-backup/spilo/acid-test-cluster/wal/13/"

4. Deploy the standby cluster.

$ kubectl apply -f my-custom-config.yaml
$ kubectl apply -f configmap.yaml
$ kubectl apply -f operator-service-account-rbac.yaml
$ kubectl apply -f postgres-operator.yaml
$ kubectl apply -f api-service.yaml
$ kubectl apply -f standby-manifest.yaml 

$ kubectl get pod
NAME                                 READY   STATUS            RESTARTS   AGE
acid-standby-cluster-0               0/1     Running           0          28s
postgres-operator-55b8549cff-vcc7p   1/1     Running           0          51s

Verify replication across active and standby clusters

Switch to the active cluster and run several write queries. 

$ kubectl config use-context <cluster 1>

$ kubectl exec acid-test-cluster-0 -it -- /bin/bash
# psql -U postgres
postgres=# create table t1 (id int);
postgres=# insert into t1 select generate_series(1,10); 

Switch to the standby cluster and verify data replication.

$ kubectl config use-context <cluster 2>

$ kubectl exec acid-standby-cluster-0 -it -- /bin/bash
# psql -U postgres
postgres=# select * from t1;
 id
----
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
(10 rows)

As you can see in the result above, replication settings have been configured correctly.

Promote standby PostgreSQL cluster

If the active cluster fails, you can manually promote the standby cluster to an active cluster. This means it will stop replicating changes from S3, and start to accept write/read queries.  

First, let's delete the active PostgreSQL cluster to simulate the failure.

$ kubectl config use-context <cluster 1>
$ kubectl delete postgresql acid-test-cluster

Then, promote the standby cluster.

$ kubectl config use-context <cluster 2>

$ kubectl exec acid-standby-cluster-0 -it -- /bin/bash
$ patronictl edit-config
(delete the standby_cluster section below)
 standby_cluster:
  create_replica_methods:
  - bootstrap_standby_with_wale
  - basebackup_fast_xlog
  restore_command: envdir "/run/etc/wal-e.d/env-standby" /scripts/restore_command.sh "%f" "%p"

Then, the new active cluster will start to send WALs to S3.

aws s3 ls --recursive s3://zalando-postgres-backup/spilo/acid-standby-cluster/wal/13/wal_005/
2021-07-04 22:37:14        135 spilo/acid-standby-cluster/wal/13/wal_005/00000003.history.lzo
2021-07-04 22:37:16    1530277 spilo/acid-standby-cluster/wal/13/wal_005/000000030000000000000015.lzo
2021-07-04 22:38:06        301 spilo/acid-standby-cluster/wal/13/wal_005/000000030000000000000016.00000028.backup.lzo
2021-07-04 22:38:06      76712 spilo/acid-standby-cluster/wal/13/wal_005/000000030000000000000016.lzo

Conclusion

I have described the multi-cluster architecture of Crunchy PostgreSQL Operator and Zalando PostgreSQL Operator. It enables rapid failure recovery to achieve disaster recovery and high availability. Multi-cluster architecture may be an effective option when considering PostgreSQL high availability and disaster recovery strategies on Kubernetes.

Comments

Post a Comment

Popular posts from this blog

Installing Pgpool-II on Debian/Ubuntu

-
First of all, many thanks to the PostgreSQL Global Development Group (PGDG) for creating Pgpool-II packages for Debian and Ubuntu. This post shows you step by step how to install Pgpool-II using official APT repository provided by PGDG. Prerequisites This blog assumes you have already installed two PostgreSQL 14 servers and setup streaming replication between the PostgreSQL servers.   For testing purpose,we set trust authentication in pg_hba.conf . In a production environment, Please follow the PostgreSQL and Pgpool-II documentation to configure proper authentication settings.  Install Pgpool-II To use the PostgreSQL apt repository, follow the steps below. Create the repository configuration file: # echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list Import the repository signing key: # wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - Update the package lists: #
Read more

Authentication in Pgpool-II

-
Image
    Pgpool-II is a proxy software for PostgreSQL cluster and it supports several authentication methods. How does Pgpool-II perform user authentication? In this post, I will introduce the authentication methods which Pgpool-II supports and how Pgpool-II authentication mechanism works. Authentication methods in Pgpool-II Pgpool-II supports several authentication methods: Trust Authentication MD5 Password Authentication   scram-sha-256 Authentication (Pgpool-II 4.0 or later) Certificate Authentication   PAM Authentication LDAP Authentication (from the next major release 4.2)  Starting with Pgpool-II 4.0, Pgpool-II supports scram-sha-256 authentication. scram-sha-256 authentication method is strongly recommended because it is the most secure password-based authentication method. How does Pgpool-II authentication mechanism work? Since Pgpool-II is a PostgreSQL proxy that works between clients and PostgreSQL servers, the authentication comprises two steps: Authentication between client
Read more

Query Load Balancing in Pgpool-II

-
Image
Pgpool-II is a PostgreSQL cluster management tool. The major features of Pgpool-II are: Connection pooling Query load balancing Automated failover Watchdog (High availability of Pgpool-II) Replication In memory query cache In this post, I will describe the query load balancing mechanism in Pgpool-II and the relevant configuration parameters. Why use query load balancing? Query load balancing can distribute database server workloads across multiple PostgreSQL servers. Nowadays, most database systems use multiple replicated database servers to achieve high availability for PostgreSQL. Pgpool-II takes the advantage of the replication feature in order to distribute the workloads across multiple PostgreSQL servers. If there is a PostgreSQL cluster with multiple PostgreSQL servers, Pgpool-II is able to distribute READ queries across those PostgreSQL servers. The benefit of READ queries load balancing is, improve the system's throughput reduce the load on each PostgreSQL server optimize
Read more