Installing Crunchy Postgres Operator v5 on EKS

-

In my previous post I described how to deploy Crunchy Postgres Operator v4 on Kubernetes and use it to achieve disaster recovery and high availability. The new major version, v5 was released last year and the installation methods have significantly changed. 

Crunchy Postgres Operator (PGO) v5 allows you to deploy PGO and your PostgreSQL clusters through Kustomize or Helm and manage your PostgreSQL clusters using kubectl command instead of pgo used in v4. This post describes how to install PGO v5 and create PostgreSQL cluster on Amazon EKS using Kustomize.

Prerequisites

Before you start, make sure you have installed the following tools:

  • AWS CLI
  • eksctl
  • kubectl
  • An access key ID and secret access key
  • An Amazon S3 bucket (In this tutorial we create a bucket named my-postgres-bucket and specify the bucket name in the PostgresCluster YAML file)

Creating an Amazon EKS cluster

Create a 3 nodes Amazon EKS cluster with the latest Kubernetes version 1.21 in region us-west-2. 

$ eksctl create cluster \
    --name my-cluster \
    --version 1.21 \
    --region us-west-2 \
    --nodes 3

Deploying PGO

This section describes how to deploy PGO using Kustomize.
First, download the postgres-operator-examples repository to your local working environment:
$ git clone https://github.com/CrunchyData/postgres-operator-examples.git
$ cd postgres-operator-examples/

Deploy PGO using the following command:

$ kubectl apply -k kustomize/install

PGO pod will be deployed in the namespace postgres-operator.

$ kubectl get pods -n postgres-operator
NAME                   READY   STATUS    RESTARTS   AGE
pgo-59c4f987b6-9pqxv   1/1     Running   0          15s

Creating a PostgreSQL Cluster

Once the PGO pod is ready, you can start deploying the PostgreSQL cluster.
In this tutorial, we deploy a PostgreSQL cluster with the following configurations:
  • PostgreSQL cluster with 1 master and 2 replicas
  • Synchronous replication
  • Automated failover
  • WAL archiving
  • Storing backup and WAL files in persistent volume and S3
Copy the example YAML files for Postgres cluster that uses S3 for backups. 
$ cp -r kustomize/s3 kustomize/my-postgres
Edit postgres.yaml file:
$ cat kustomize/my-postgres/postgres.yaml
apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: my-postgres
spec:
  image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres:centos8-14.2-0
  postgresVersion: 14
  instances:
    - name: pg-1
      replicas: 3
      dataVolumeClaimSpec:
        accessModes:
        - "ReadWriteOnce"
        resources:
          requests:
            storage: 10Gi
  backups:
    pgbackrest:
      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbackrest:centos8-2.36-1
      configuration:
      - secret:
          name: pgo-s3-creds
      global:
        repo2-path: /pgbackrest/postgres-operator/my-postgres/repo2
      repos:
      - name: repo1
        volume:
          volumeClaimSpec:
            accessModes:
            - "ReadWriteOnce"
            resources:
              requests:
                storage: 10Gi
      - name: repo2
        s3:
          bucket: "my-postgres-bucket"
          endpoint: "s3.us-west-2.amazonaws.com"
          region: "us-west-2"
  patroni:
    dynamicConfiguration:
      synchronous_mode: true
  users:
    - name: postgres
    - name: testuser
      databases:
        - testdb
  • spec.instances.replicas: the number of replicas
  • spec.backups.pgbackrest.repos.name: repository to store backup and WAL files. Allow to specify multiple repositories using repoN format.
  • spec.backups.pgbackrest.repos.s3: S3 bucket
  • spec.instances.dataVolumeClaimSpec: PVC for PostgreSQL data
  • spec.backups.pgbackrest.repos.volume.volumeClaimSpec: PVC for backup and WAL files
  • spec.patroni.dynamicConfiguration.synchronous_mode: enable/disable synchronous replication
  • spec.users: PostgreSQL users and databases which the user can access. postgres user is created as a superuser.

Register your credentials to access AWS S3. Please note, you must specify the correct repository name in repoN format.

$ cp kustomize/my-postgres/s3.conf{.example,}
$ vi kustomize/my-postgres/s3.conf
[global]
repo2-s3-key=<YOUR_AWS_S3_KEY>
repo2-s3-key-secret=<YOUR_AWS_S3_KEY_SECRET>
Next, you can deploy a PostgreSQL cluster by running the following command: 
$ kubectl apply -k kustomize/my-postgres
You can use the command below to check the status of PostgreSQL pods.
$ kubectl get pods -n postgres-operator --selector=postgres-operator.crunchydata.com/instance-set \
  -L postgres-operator.crunchydata.com/role
NAME                      READY   STATUS    RESTARTS   AGE    ROLE
my-postgres-pg-1-7wm4-0   3/3     Running   0          6m8s   replica
my-postgres-pg-1-l957-0   3/3     Running   0          6m8s   replica
my-postgres-pg-1-wbx9-0   3/3     Running   0          6m8s   master

Connecting to PostgreSQL using psql

Once all of the pods is up and running, you can connect to the master pod and verify the replication status.
$ PG_CLUSTER_MASTER_POD=$(kubectl get pod -n postgres-operator -o name -l postgres-operator.crunchydata.com/cluster=my-postgres,postgres-operator.crunchydata.com/role=master)
$ kubectl -n postgres-operator port-forward "${PG_CLUSTER_MASTER_POD}" 5432:5432 &
Connect to master pod using the user postgres.
$ PG_CLUSTER_USER_SECRET_NAME=my-postgres-pguser-postgres
$ PGPASSWORD=$(kubectl get secrets -n postgres-operator "${PG_CLUSTER_USER_SECRET_NAME}" -o go-template='{{.data.password | base64decode}}') \
PGUSER=$(kubectl get secrets -n postgres-operator "${PG_CLUSTER_USER_SECRET_NAME}" -o go-template='{{.data.user | base64decode}}') \
psql -h localhost
Handling connection for 5432
psql (14.2)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-ECDSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# select application_name, state, sync_state from pg_stat_replication;
    application_name     |   state   | sync_state 
-------------------------+-----------+------------
 my-postgres-pg-1-l957-0 | streaming | sync
 my-postgres-pg-1-7wm4-0 | streaming | async
(2 rows)
We can see the PostgreSQL cluster with synchronous replication is configured correctly.

Verify automatic failover

PGO uses Patroni to achieve high availability. If a failure occurs in the master pod, pgo will trigger a failover and automatically heal the failed pod.

Let's verify automatic failover. 

First, let's check the current status.

$ kubectl get pods -n postgres-operator --selector=postgres-operator.crunchydata.com/instance-set \
  -L postgres-operator.crunchydata.com/role
NAME                      READY   STATUS    RESTARTS   AGE   ROLE
my-postgres-pg-1-7wm4-0   3/3     Running   0          49m   replica
my-postgres-pg-1-l957-0   3/3     Running   0          49m   replica
my-postgres-pg-1-wbx9-0   3/3     Running   0          49m   master
Next, delete the master pod using the following command:
$ PG_CLUSTER_MASTER_POD=$(kubectl get pod -n postgres-operator -o name -l postgres-operator.crunchydata.com/cluster=my-postgres,postgres-operator.crunchydata.com/role=master)
$ kubectl delete ${PG_CLUSTER_MASTER_POD} -n postgres-operator
Once the failure is detected, one of the replicas is promoted as master and the old master is restarted as a replica.
$ kubectl get pods -n postgres-operator --selector=postgres-operator.crunchydata.com/instance-set \
  -L postgres-operator.crunchydata.com/role
NAME                      READY   STATUS    RESTARTS   AGE   ROLE 
my-postgres-pg-1-7wm4-0   3/3     Running   0          51m   replica
my-postgres-pg-1-l957-0   3/3     Running   0          51m   master
my-postgres-pg-1-wbx9-0   3/3     Running   0          54s   replica

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: Authen...
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