{"id":840,"date":"2021-08-25T10:18:30","date_gmt":"2021-08-25T10:18:30","guid":{"rendered":"https:\/\/blog.ngocha.biz\/?p=840"},"modified":"2021-08-25T10:18:30","modified_gmt":"2021-08-25T10:18:30","slug":"deploy-postgresql-statefulset","status":"publish","type":"post","link":"https:\/\/blog.ngocha.biz\/?p=840","title":{"rendered":"How to Deploy PostgreSQL Statefulset Cluster on Kubernetes"},"content":{"rendered":"

This article aims to explain each of the components required to deploy PostgreSQL statefulset cluster on Kubernetes. We will also discuss the highly available PostgreSQL cluster setup using pgpool and repmgr.<\/p>\n

Towards the end of the article, we have added the guide to perform basic operations inside the PostgreSQL cluster using a client pod.<\/p>\n

As a beginner, creating components one by one while understanding the steps involved is a great way to learn about Kubernetes<\/a> and Postgres.<\/p>\n

Going step by step ensures that you can focus on understanding the \u2018why\u2019 while learning the \u2018how\u2019.<\/p>\n

Here is what you are going to learn in this comprehensive PostgreSQL deployment guide.<\/p>\n

PostgreSQL Cluster Kubernetes Manifests<\/strong><\/h2>\n

All the Kubernetes YAML manifests used in this guide are hosted on Github. Clone the repository for reference and implementation.<\/p>\n

git clone https:\/\/github.com\/scriptcamp\/kubernetes-postgresql.git<\/code><\/pre>\n
We have explained all the manifests required for PostgreSQL on Kubernetes. If you have trouble copying the manifests from the article, please clone and refer to the manifests directly.<\/p>\n
We have categorized the manifest into three folders named client<\/strong>, pgpool<\/strong> and  postgresql<\/strong> as shown below.<\/p>\n
\u251c\u2500\u2500 client\n\u2502   \u251c\u2500\u2500 client-pod.yaml\n\u2502   \u2514\u2500\u2500 psql-client.yaml\n\u251c\u2500\u2500 pgpool\n\u2502   \u251c\u2500\u2500 nginx.yaml\n\u2502   \u251c\u2500\u2500 pgpool-deployment.yaml\n\u2502   \u251c\u2500\u2500 pgpool-secret.yaml\n\u2502   \u251c\u2500\u2500 pgpool-svc-nodeport.yaml\n\u2502   \u2514\u2500\u2500 pgpool-svc.yaml\n\u2514\u2500\u2500 postgresql\n    \u251c\u2500\u2500 postgres-configmap.yaml\n    \u251c\u2500\u2500 postgres-headless-svc.yaml\n    \u251c\u2500\u2500 postgres-secrets.yaml\n    \u2514\u2500\u2500 postgres-statefulset.yaml<\/code><\/pre>\n
If you wish to deploy the components in one go, cd into each directory and execute the following. Start with postgresql<\/strong> directory.<\/p>\n
kubectl apply -f .<\/code><\/pre>\n
Bitnami PostgreSQL Docker Image<\/strong><\/h2>\n
This tutorial has used Bitnami docker images, this has been done intentionally. There are certain advantages you can get as a beginner by using a Bitnami image.<\/p>\n
    \n
  1. Bitnami images are shipped with necessary components pre-installed. This lets us maintain our focus can understanding and becoming familiar with Kubernetes side of things in depth.<\/li>\n
  2. Bitnami images are well tested and validated before being released. This helps us save time and overcome any problems we may face with newer versions or patches.<\/li>\n
  3. Bitnami images are very well documented, you’ll find a satisfactory explaination of each and every environment variable being used by the bitnami image. So a natural fit for beginners.<\/li>\n<\/ol>\n
    Above all, as a beginner – we should focus on understanding kubernetes components and avoid getting off our goal due to installing dozens of packages, finding documentation, etc.<\/p>\n
    Bitnami does just that for us.<\/p>\n
    High Level Architecture<\/h2>\n
    \"PostgreSQL<\/figure>\n
    Create a Namespace<\/h2>\n
    To deploy the PostgreSQL cluster, we will create a dedicated namespace named database.<\/p>\n
    kubectl create namespace database<\/code><\/pre>\n
    The manifest files do not have the namespace added to them. So we will add the namespace while deploying each component. If you don’t specify the namespace, it gets deployed in the default namespace.<\/p>\n
    Creating Postgres Server ConfigMaps<\/strong><\/h2>\n
    A ConfigMaps in Kubernetes lets us mount files on containers without the need to make changes to the Dockerfile or rebuilding the container image.<\/p>\n
    This feature is extremely helpful in cases where configurations have to be modified or created through files.<\/p>\n
    Postgres requires a script (pre-stop.sh<\/code>) to be run whenever it is about to be stopped. We will mount this script into the pod using config maps.<\/p>\n
    Save the following manifest as postgres-configmap.yaml<\/code><\/p>\n
    apiVersion: v1\nkind: ConfigMap\nmetadata:\n  name: postgres-configmap\ndata:\n  pre-stop.sh: |-\n    #!\/bin\/bash\n    set -o errexit\n    set -o pipefail\n    set -o nounset\n\n    # Debug section\n    exec 3>&1\n    exec 4>&2\n\n    # Load Libraries\n    . \/opt\/bitnami\/scripts\/liblog.sh\n    . \/opt\/bitnami\/scripts\/libpostgresql.sh\n    . \/opt\/bitnami\/scripts\/librepmgr.sh\n\n    # Auxiliary functions\n    is_new_primary_ready() {\n        return_value=1\n        currenty_primary_node=\"$(repmgr_get_primary_node)\"\n        currenty_primary_host=\"$(echo $currenty_primary_node | awk '{print $1}')\"\n\n        info \"$currenty_primary_host != $REPMGR_NODE_NETWORK_NAME\"\n        if [[ $(echo $currenty_primary_node | wc -w) -eq 2 ]] && [[ \"$currenty_primary_host\" != \"$REPMGR_NODE_NETWORK_NAME\" ]]; then\n            info \"New primary detected, leaving the cluster...\"\n            return_value=0\n        else\n            info \"Waiting for a new primary to be available...\"\n        fi\n        return $return_value\n    }\n\n    export MODULE=\"pre-stop-hook\"\n\n    if [[ \"${BITNAMI_DEBUG}\" == \"true\" ]]; then\n        info \"Bash debug is on\"\n    else\n        info \"Bash debug is off\"\n        exec 1>\/dev\/null\n        exec 2>\/dev\/null\n    fi\n\n    # Load PostgreSQL & repmgr environment variables\n    . \/opt\/bitnami\/scripts\/postgresql-env.sh\n\n    postgresql_enable_nss_wrapper\n\n    # Prepare env vars for managing roles\n    primary_node=\"$(repmgr_get_primary_node)\"\n    primary_host=\"$(echo $primary_node | awk '{print $1}')\"\n\n    # Stop postgresql for graceful exit.\n    postgresql_stop\n\n    if [[ \"$primary_host\" == \"$REPMGR_NODE_NETWORK_NAME\" ]]; then\n        info \"Primary node need to wait for a new primary node before leaving the cluster\"\n        retry_while is_new_primary_ready 10 5\n    else\n        info \"Standby node doesn't need to wait, leaving the cluster.\"\n    fi<\/code><\/pre>\n
    Create the configmap<\/p>\n
    kubectl apply -f postgres-configmap.yaml -n database<\/code><\/pre>\n
    It’s important to understand what the script is trying to do. Here is the overview:<\/p>\n
      \n
    1. The script first checks what type of component is getting stopped i.e. master or follower.<\/li>\n
    2. In case the master is getting stopped – the script delays the stoppage of the pod until a previous follower gets promoted to master.<\/li>\n
    3. This is done to ensure high availabiliy i.e. atleast one master with write capabilities should exist.<\/li>\n<\/ol>\n
      Confusing? Don’t worry. Just follow on. After reading the high availability section, the above points would become clear and make much more sense. <\/p>\n
      Be sure to go through the script and revisit the points.<\/p>\n
      Deploy PostgreSQL Services<\/strong><\/h2>\n
      Services in Kubernetes are the objects that pods use to communicate with each other. Services of type ClusterIP<\/code> are usually used for inter-pod communication.<\/p>\n
      There are two types of ClusterIP services<\/p>\n
        \n
      1. Headless Services<\/li>\n
      2. Services<\/li>\n<\/ol>\n
        Normal Kubernetes services act as load balancers and follow round-robin logic<\/strong> to distribute loads. Headless services don\u2019t act like load balancers. Also, normal services are assigned IPs by Kubernetes whereas Headless services are not.<\/p>\n
        In the case of Postgres servers, we require a headless service because it is a requirement for the PostgresSQL statefulset.<\/p>\n
        Save the following manifest as postgres-headless-svc.yaml<\/code>.<\/p>\n
        apiVersion: v1\nkind: Service\nmetadata:\n  name: postgres-headless-svc\nspec:\n  type: ClusterIP\n  clusterIP: None\n  ports:\n  - name: postgresql\n    port: 5432\n    targetPort: postgresql\n    protocol: TCP \n  selector:\n    app: postgres<\/code><\/pre>\n
        Create the service.<\/p>\n
        kubectl apply -f postgres-headless-svc.yaml -n database<\/code><\/pre>\n
        Create PostgresSQL Server Secrets<\/strong><\/h2>\n
        Secrets in Kubernetes are the objects used for supplying sensitive information to containers. They are like ConfigMaps with the difference that data is store in a base 64 encoded format.<\/p>\n
        For the security of our PostgreSQL cluster, it is wise to restrict access to the database with a password. We will use Secrets to mount our desired passwords to the containers.<\/p>\n
        Note:<\/strong> In production use cases, a secrets management solution like hashicorp vault will be used to store and retrive secrets. You can refer to hashicorp vault setup guide<\/a> on Kubernetes to understand more.<\/p><\/blockquote>\n
        Save the following manifest as postgres-secrets.yaml<\/code>. Please change the password with a secure password of your own.<\/p>\n
        apiVersion: v1\nkind: Secret\nmetadata:\n  name: postgres-secrets\ndata:\n  postgresql-password: \"V2JyVHBOM2c3cQ==\"\n  repmgr-password: \"c1ZwYm90R2d4Nw==\"<\/code><\/pre>\n
        Create the Secret.<\/p>\n
        kubectl apply -f postgres-secrets.yaml -n database<\/code><\/pre>\n
        When the cluster is initialized, it creates certain users like postgres<\/code> & repmgr<\/code> in our case. The above passwords are for these users.<\/p>\n
        PostgreSQL StatefulSet<\/strong><\/h2>\n
        While deploying the PostgreSQL on Kubernetes, what object type should be used and why? Deployments<\/strong><\/a> or StatefulSets?<\/strong> <\/p>\n
        The answer is StatefulSet. Let’s discuss!<\/p>\n
        StatefulSet is the Kubernetes object used to manage stateful applications. It\u2019s preferred over deployments for this use case as it provides guarantees about the ordering and uniqueness of these Pods i.e. the management of volumes is better with stateful sets.<\/p>\n
        This section is critical to get a deeper understanding of the deployment logic. So read with full concentration!<\/p>\n
        As a beginner, it is important to understand why we want to deploy a Statefulset and not Deployments. After all, our focus is on understanding the \u2018why\u2019 along with learning the \u2018how\u2019.<\/p>\n
        Why do We Need PostgreSQL<\/strong> Statefulset?<\/strong><\/h2>\n
        Postgres is going to be a stateful application i.e. it stores data (like tables, users) inside a volume. If the data is stored in pod ephemeral storage, then the data will get erased once the pod restarts.<\/p>\n
        Also, Postgres may have to be scaled to more than one pod in caseload increases.<\/p>\n
        All these operations have to be done in such a way that data consistency is maintained across all the pods like postgres-<\/code>0, postgres-1<\/code>, postgres-2<\/code>.<\/p>\n
        How can we achieve this in Kubernetes? Think and then read ahead!<\/p>\n
        Postgres implements continuous replication of data across all its pods. So when data is written on postgres-0<\/code> it gets replicated into postgres-1<\/code>. postgres-2<\/code> replicates data from postgres-3<\/code>. And so on\u2026<\/p>\n
        Postgres is able to do this continuous replication using an open-source tool called RepMgr<\/a> which comes built-in with Postgres docker image.<\/p>\n
        The thing to understand here is that postgres-1<\/code> needs to know where to look for postgres-0<\/code>. Otherwise, How will the replication happen?<\/p>\n
          \n
        1. How will it know from where to fetch data for the replication process?<\/li>\n
        2. How will postgres-1 know where to look for postgres-0?<\/li>\n
        3. How will postgres-2 know where to look for postgres-1?<\/li>\n<\/ol>\n
          Let\u2019s try to answer these questions now.<\/p>\n
          In case of deployments & stateful sets, pods are always assigned a unique name that can be used to look for the pods.<\/p>\n
          In the case of deployments,<\/strong> pods are always assigned a unique name but this unique name changes after the pod are deleted & recreated<\/strong>. So it\u2019s not useful to identify any pod.<\/p>\n
          Case of deployments: name of pod initially: postgres-bcr25qd41c-skxpe  <\/code><\/pre>\n
          In the case of the stateful set <\/strong>\u2013 each pod is assigned a unique name and this unique name stays with it even if the pod is deleted <\/strong>& recreated.<\/p>\n
          Case of statefulsets: name of pod initially: postgres-0 <\/code><\/pre>\n
          That\u2019s why we want to use a stateful set here i.e. so that we can reach any pod without any discrepancies.<\/p>\n
          Also, this unique ordering ensures that each pod is allocated the same underlying volume irrespective of pod restarts.<\/p>\n
          These concepts of Statefulset & deployments are not unique to Postgres deployments, if you explore \u2013 you\u2019ll find that many popular Kubernetes tools use stateful sets.<\/p>\n
          Some examples are Hashicorp’s Vault, Elasticsearch, and many more. All of them use Stateful Set and not deployments due to the same logic.<\/p>\n
          Deploy PostgreSQL StatefulSet<\/strong><\/h2>\n
          First, let\u2019s create the Statefulset. I have added an explanation for the Postgres Statefulset as well towards the end of this section.<\/p>\n
          Save the following manifest as postgres-statefulset.yaml<\/code><\/p>\n
          apiVersion: apps\/v1\nkind: StatefulSet\nmetadata:\n  name: postgres-sts\nspec:\n  serviceName: postgres-headless-svc\n  replicas: 3\n  selector:\n    matchLabels:\n      app: postgres\n  template:\n    metadata:\n      labels:\n        app: postgres\n    spec:\n      securityContext:\n        fsGroup: 1001\n      containers:\n        - name: postgresql\n          lifecycle:\n            preStop:\n              exec:\n                command:\n                  - \/pre-stop.sh\n          image: docker.io\/bitnami\/postgresql-repmgr:11.12.0-debian-10-r44\n          imagePullPolicy: \"IfNotPresent\"\n          securityContext:\n            runAsUser: 1001\n          # Auxiliary vars to populate environment variables\n          env:\n            - name: BITNAMI_DEBUG\n              value: \"false\"\n            # PostgreSQL configuration\n            - name: POSTGRESQL_VOLUME_DIR\n              value: \"\/bitnami\/postgresql\"\n            - name: PGDATA\n              value: \"\/bitnami\/postgresql\/data\"\n            - name: POSTGRES_USER\n              value: \"postgres\"\n            - name: POSTGRES_PASSWORD\n              valueFrom:\n                secretKeyRef:\n                  name: postgres-secrets\n                  key: postgresql-password\n            - name: POSTGRES_DB\n              value: \"postgres\"\n            - name: POSTGRESQL_LOG_HOSTNAME\n              value: \"true\"\n            - name: POSTGRESQL_LOG_CONNECTIONS\n              value: \"false\"\n            - name: POSTGRESQL_LOG_DISCONNECTIONS\n              value: \"false\"\n            - name: POSTGRESQL_PGAUDIT_LOG_CATALOG\n              value: \"off\"\n            - name: POSTGRESQL_CLIENT_MIN_MESSAGES\n              value: \"error\"\n            - name: POSTGRESQL_SHARED_PRELOAD_LIBRARIES\n              value: \"pgaudit, repmgr\"\n            - name: POSTGRESQL_ENABLE_TLS\n              value: \"no\"\n            # Repmgr configuration\n            - name: MY_POD_NAME\n              valueFrom:\n                fieldRef:\n                  fieldPath: metadata.name\n            - name: REPMGR_UPGRADE_EXTENSION\n              value: \"no\"\n            - name: REPMGR_PGHBA_TRUST_ALL\n              value: \"no\"\n            - name: REPMGR_MOUNTED_CONF_DIR\n              value: \"\/bitnami\/repmgr\/conf\"\n            - name: REPMGR_NAMESPACE\n              valueFrom:\n                fieldRef:\n                  fieldPath: metadata.namespace\n            - name: REPMGR_PARTNER_NODES\n              value: postgres-sts-0.postgres-headless-svc.$(REPMGR_NAMESPACE).svc.cluster.local,postgres-sts-1.postgres-headless-svc.$(REPMGR_NAMESPACE).svc.cluster.local,postgres-sts-2.postgres-headless-svc.$(REPMGR_NAMESPACE).svc.cluster.local\n            - name: REPMGR_PRIMARY_HOST\n              value: \"postgres-sts-0.postgres-headless-svc.$(REPMGR_NAMESPACE).svc.cluster.local\"\n            - name: REPMGR_NODE_NAME\n              value: \"$(MY_POD_NAME)\"\n            - name: REPMGR_NODE_NETWORK_NAME\n              value: \"$(MY_POD_NAME).postgres-headless-svc.$(REPMGR_NAMESPACE).svc.cluster.local\"\n            - name: REPMGR_LOG_LEVEL\n              value: \"NOTICE\"\n            - name: REPMGR_CONNECT_TIMEOUT\n              value: \"5\"\n            - name: REPMGR_RECONNECT_ATTEMPTS\n              value: \"3\"\n            - name: REPMGR_RECONNECT_INTERVAL\n              value: \"5\"\n            - name: REPMGR_USERNAME\n              value: \"repmgr\"\n            - name: REPMGR_PASSWORD\n              valueFrom:\n                secretKeyRef:\n                  name: postgres-secrets\n                  key: repmgr-password\n            - name: REPMGR_DATABASE\n              value: \"repmgr\"\n          ports:\n            - name: postgresql\n              containerPort: 5432\n              protocol: TCP\n          livenessProbe:\n            exec:\n              command:\n                - bash\n                - -ec\n                - 'PGPASSWORD=$POSTGRES_PASSWORD psql -w -U \"postgres\" -d \"postgres\"  -h 127.0.0.1 -c \"SELECT 1\"'\n            initialDelaySeconds: 30\n            periodSeconds: 10\n            timeoutSeconds: 5\n            successThreshold: 1\n            failureThreshold: 6\n          readinessProbe:\n            exec:\n              command:\n                - bash\n                - -ec\n                - 'PGPASSWORD=$POSTGRES_PASSWORD psql -w -U \"postgres\" -d \"postgres\"  -h 127.0.0.1 -c \"SELECT 1\"'\n            initialDelaySeconds: 5\n            periodSeconds: 10\n            timeoutSeconds: 5\n            successThreshold: 1\n            failureThreshold: 6\n          volumeMounts:\n            - name: data\n              mountPath: \/bitnami\/postgresql\n            - name: hooks-scripts\n              mountPath: \/pre-stop.sh\n              subPath: pre-stop.sh\n      volumes:\n        - name: hooks-scripts\n          configMap:\n            name: postgres-configmap\n            defaultMode: 0755\n  volumeClaimTemplates:\n    - metadata:\n        name: data\n      spec:\n        accessModes:\n          - \"ReadWriteOnce\"\n        resources:\n          requests:\n            storage: \"1Gi\"<\/code><\/pre>\n
          Create the Statefulset.<\/p>\n
          kubectl apply -f postgres-statefulset.yaml -n database<\/code><\/pre>\n
          The Statefulset YAML of the PostgreSQL server has components such as configmap mounts, security context, probes, etc. Let understand the key configurations.<\/p>\n
          Metadata as env vars:<\/strong> In Kubernetes, information like the name of pods, the namespace of the pods can be utilized as env var for the pod. <\/p>\n
          This is useful in cases where the env vars need to use pods metadata or some Kubernetes defined fields for the pods.<\/p>\n
          - name: MY_POD_NAME\n  valueFrom:\n    fieldRef:\n      fieldPath: metadata.name \n- name: REPMGR_PASSWORD\n  valueFrom:\n    secretKeyRef:\n      name: postgres-secrets <\/code><\/pre>\n
          Env vars injected through secrets:<\/strong> Sometimes the containers need to know the sensitive data in order to make use of it. <\/p>\n
          For e.g. in order to assign a password to the Postgres database, the required password must be supplied securely to the Postgres container.<\/p>\n
           - name: POSTGRES_PASSWORD\n   valueFrom:\n     secretKeyRef:\n       name: postgres-secrets\n       key: postgresql-password\n - name: REPMGR_PASSWORD\n   valueFrom:\n     secretKeyRef:\n       name: postgres-secrets\n       key: repmgr-password <\/code><\/pre>\n
          Probes:<\/strong> Probes ensure that the vault does not get stuck in a loop due to any bug and can be restarted automatically in case an unexpected error comes up.<\/p>\n
            livenessProbe:\n    exec:\n      command:\n        - bash\n        - -ec\n        - 'PGPASSWORD=$POSTGRES_PASSWORD psql -w -U \"postgres\" -d \"postgres\"  -h 127.0.0.1 -c \"SELECT 1\"'\n    initialDelaySeconds: 30\n    periodSeconds: 10\n    timeoutSeconds: 5\n    successThreshold: 1\n    failureThreshold: 6\n  readinessProbe:\n    exec:\n      command:\n        - bash\n        - -ec\n        - 'PGPASSWORD=$POSTGRES_PASSWORD psql -w -U \"postgres\" -d \"postgres\"  -h 127.0.0.1 -c \"SELECT 1\"'\n    initialDelaySeconds: 5\n    periodSeconds: 10\n    timeoutSeconds: 5\n    successThreshold: 1\n    failureThreshold: 6<\/code><\/pre>\n
          Basic principle of the above probes<\/strong><\/h3>\n
          Here the command used is just going to the “postgres<\/code>” database using the “postgres<\/code>” user and running the “SELECT 1” query. <\/p>\n
          If the Postgres process is running correctly, then the query will have a successful exit code. Otherwise not. <\/p>\n
          That’s how we can be sure that the probes can tell us if the process is running or the container needs to be restarted!<\/p>\n
          VolumeClaimTemplates: <\/strong>A template by which a stateful set can create volumes for replicas.<\/p>\n
            volumeClaimTemplates:\n    - metadata:\n        name: data\n      spec:\n        accessModes:\n          - \"ReadWriteOnce\"\n        resources:\n          requests:\n            storage: \"1Gi\"<\/code><\/pre>\n
          Let’s go through the important env vars for the Postgres process now.<\/p>\n
            \n
          1. POSTGRESQL_VOLUME_DIR<\/strong>: The directory where the Postgres process shall write its configuration files and data. This is the directory that we should mount with a PVC.<\/li>\n
          2. PGDATA<\/strong>: The directory inside the main Postgres directory where the data directory should be created.<\/li>\n
          3. POSTGRES_USER<\/strong>: The user that should be created automatically when the Postgres process starts.<\/li>\n
          4. POSTGRES_PASSWORD<\/strong>: The password for the user created by default.<\/li>\n
          5. POSTGRES_DB<\/strong>: The database which should be created when the Postgres process starts.<\/li>\n<\/ol>\n
            Let’s discuss the RepMgr<\/strong> now.<\/p>\n
            RepMgr Inside PostgresSQL Servers<\/strong><\/h2>\n
            RepMgr is an open-source tool shipped with Postgres which serves two purposes: Replication & Failover.<\/p>\n
              \n
            1. Replication<\/strong>: It replicates data from the primary server to all the replicas. This helps in reducing the load on the servers by distributing read & write queries.<\/li>\n
            2. Failover<\/strong>: It can handle failovers in the cluster i.e. it can promote a read-only server to a primary server when required.<\/li>\n<\/ol>\n
              You can read about it more here: https:\/\/repmgr.org\/<\/a><\/p>\n
              Let’s go through the important env vars for repmgr<\/strong> setup.<\/p>\n
                \n
              1. REPMGR_PARTNER_NODES<\/strong>: This expects a comma-separated list of all Postgres server addresses in the cluster. Including the primary server’s address.<\/li>\n
              2. REPMGR_PRIMARY_HOST<\/strong>: This expects the Postgres primary server’s address.<\/li>\n
              3. REPMGR_USERNAME<\/strong>: User to be created for the repmgr operations.<\/li>\n
              4. REPMGR_PASSWORD<\/strong>: Password to be created for the repmgr operations.<\/li>\n
              5. REPMGR_DATABASE<\/strong>: Database to be created for the repmgr operations.<\/li>\n<\/ol>\n
                Tip for using headless svc effectively<\/strong><\/h2>\n
                As you know, headless svc does not work as a load balancer and is used to address a group of pods together. There is another use case for headless services.<\/p>\n
                We can use it to get the address of individual pods. Let’s take an e.g. to understand this. We have three pods running as part of the Postgres Statefulset.<\/p>\n
                <\/p>\n\n\n\n\n\n\n
                Pod name<\/strong><\/td>\nPod Address<\/strong><\/td>\n<\/tr>\n
                postgres-sts-0<\/td>\n172.17.0.3<\/td>\n<\/tr>\n
                postgres-sts-1<\/td>\n172.17.0.8<\/td>\n<\/tr>\n
                postgres-sts-2<\/td>\n172.17.0.10<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
                <\/p>\n
                Pods and their addresses.<\/p>\n
                A headless svc  “postgres-headless-svc<\/code>” is pointed to these pods.<\/p>\n
                If you do a nslookup<\/code> from a pod running inside the same namespace of your cluster, you’ll be able to get the address of the above pods through the headless svc.<\/p>\n
                nslookup postgres-sts-0.postgres-headless-svc.database.svc.cluster.local\n\nServer:\t\t10.96.0.10\nAddress:\t10.96.0.10#53\n\nName:\tpostgres-sts-0.postgres-headless-svc.default.svc.cluster.local\nAddress: 172.17.0.3\n<\/code><\/pre>\n
                The above concept is used very commonly in Kubernetes, in fact, the env var REPMGR_PARTNER_NODES is using this.<\/p>\n
                Pg-Pool for Postgres<\/strong><\/h2>\n
                Pg pool is a middleware component that sits in front of the Postgres servers and acts as a gatekeeper to the cluster. <\/p>\n
                It mainly serves two purposes: Load balancing & Limiting the requests.<\/p>\n
                  \n
                1. Load Balancing:<\/strong> Pg pool takes connection requests and queries. It analyzes the query to decide where the query should be sent.<\/li>\n
                2. Read-only queries can be handled by read-replicas. Write operations can only be handled by the primary server. In this way, it loads balances the cluster.<\/li>\n
                3. Limits the requests:<\/strong> Like any other system, Postgres has a limit on no. of concurrent connections it can handle gracefully.<\/li>\n
                4. Pg-pool limits the no. of connections it takes up and queues up the remaining. Thus, gracefully handling the overload.<\/li>\n<\/ol>\n
                  Creating the pg-pool secrets<\/strong><\/h2>\n
                  Save the following manifest as pgpool-secret.yaml<\/code><\/p>\n
                  apiVersion: v1\nkind: Secret\nmetadata:\n  name: pgpool-secrets\ndata:\n  admin-password: \"SFRzaVZxYjdSZQ==\"<\/code><\/pre>\n
                  Create the secret.<\/p>\n
                  kubectl create -f pgpool-secret.yaml -n database<\/code><\/pre>\n
                  Creating the pg-pool service<\/strong><\/h2>\n
                  Save the following manifest as pgpool-svc.yaml<\/code><\/p>\n
                  apiVersion: v1\nkind: Service\nmetadata:\n  name: pgpool-svc\nspec:\n  type: ClusterIP\n  sessionAffinity: None\n  ports:\n    - name: postgresql\n      port: 5432\n      targetPort: postgresql\n      protocol: TCP\n      nodePort: null\n  selector:\n    app: pgpool<\/code><\/pre>\n
                  Create the service.<\/p>\n
                  kubectl apply -f pgpool-svc.yaml -n database<\/code><\/pre>\n
                  If you wish to access the cluster from outside the kubernetes cluster<\/a>, you need to deploy a NodePort service as well. <\/p>\n
                  Save the following manifest as pgpool-svc-nodeport.yaml<\/code><\/p>\n
                  apiVersion: v1\nkind: Service\nmetadata:\n  name: pgpool-svc-nodeport\nspec:\n  type: NodePort\n  sessionAffinity: None\n  ports:\n    - name: postgresql\n      port: 5432\n      nodePort: 32000\n      targetPort: postgresql\n      protocol: TCP\n  selector:\n    app: pgpool<\/code><\/pre>\n
                  Create the service.<\/p>\n
                  kubectl apply -f pgpool-svc-nodeport.yaml -n database\n<\/code><\/pre>\n
                  Deploy pgpool Deployment<\/strong><\/h2>\n
                  Save the following manifest as pgpool-deployment.yaml<\/code><\/p>\n
                  apiVersion: apps\/v1\nkind: Deployment\nmetadata:\n  name: pgpool-deployment\nspec:\n  replicas: 1\n  selector:\n    matchLabels: \n      app: pgpool\n  template:\n    metadata:\n      labels: \n        app: pgpool\n    spec:          \n      securityContext:\n        fsGroup: 1001\n      containers:\n        - name: pgpool\n          image: docker.io\/bitnami\/pgpool:4.2.3-debian-10-r38\n          imagePullPolicy: \"IfNotPresent\"\n          securityContext:\n            runAsUser: 1001\n          env:\n            - name: BITNAMI_DEBUG\n              value: \"false\"\n            - name: PGPOOL_BACKEND_NODES\n              value: 0:postgres-sts-0.postgres-headless-svc:5432,1:postgres-sts-1.postgres-headless-svc:5432,2:postgres-sts-2.postgres-headless-svc:5432\n            - name: PGPOOL_SR_CHECK_USER\n              value: \"repmgr\"\n            - name: PGPOOL_SR_CHECK_PASSWORD\n              valueFrom:\n                secretKeyRef:\n                  name: postgres-secrets\n                  key: repmgr-password\n            - name: PGPOOL_SR_CHECK_DATABASE\n              value: \"postgres\"\n            - name: PGPOOL_ENABLE_LDAP\n              value: \"no\"\n            - name: PGPOOL_POSTGRES_USERNAME\n              value: \"postgres\"\n            - name: PGPOOL_POSTGRES_PASSWORD\n              valueFrom:\n                secretKeyRef:\n                  name: postgres-secrets\n                  key: postgresql-password\n            - name: PGPOOL_ADMIN_USERNAME\n              value: \"admin\"\n            - name: PGPOOL_ADMIN_PASSWORD\n              valueFrom:\n                secretKeyRef:\n                  name: pgpool-secrets\n                  key: admin-password\n            - name: PGPOOL_ENABLE_LOAD_BALANCING\n              value: \"yes\"\n            - name: PGPOOL_ENABLE_LOG_CONNECTIONS\n              value: \"no\"\n            - name: PGPOOL_ENABLE_LOG_HOSTNAME\n              value: \"yes\"\n            - name: PGPOOL_ENABLE_LOG_PER_NODE_STATEMENT\n              value: \"no\"\n            - name: PGPOOL_CHILD_LIFE_TIME\n              value: \"\"\n            - name: PGPOOL_ENABLE_TLS\n              value: \"no\"\n          ports:\n            - name: postgresql\n              containerPort: 5432\n              protocol: TCP\n          livenessProbe:\n            exec:\n              command:\n                - \/opt\/bitnami\/scripts\/pgpool\/healthcheck.sh\n            initialDelaySeconds: 30\n            periodSeconds: 10\n            timeoutSeconds: 5\n            successThreshold: 1\n            failureThreshold: 5\n          readinessProbe:\n            exec:\n              command:\n                - bash\n                - -ec\n                - PGPASSWORD=${PGPOOL_POSTGRES_PASSWORD} psql -U \"postgres\" -d \"postgres\" -h \/opt\/bitnami\/pgpool\/tmp -tA -c \"SELECT 1\" >\/dev\/null\n            initialDelaySeconds: 5\n            periodSeconds: 5\n            timeoutSeconds: 5\n            successThreshold: 1\n            failureThreshold: 5<\/code><\/pre>\n
                  Create the deployment.<\/p>\n
                  kubectl apply -f pgpool-deployment.yaml -n database<\/code><\/pre>\n
                  PostgreSQL High Availability<\/strong><\/h2>\n
                  The failover mechanism provided by RepMgr and the Load balancing provided by Pg-pool ensure that the Postgres cluster remains up for a long time.<\/p>\n
                  The two mechanisms together ensure the high availability of the PostgreSQL cluster.<\/p>\n
                  With pod anti-affinity configurations, you can instruct Kubernetes to deploy pods in different nodes. This ensures you don’t have a single point of failure if a Kubernetes node goes down.<\/p>\n
                  Pod anti-affinity requires Kubernetes nodes to be labeled with appropriate labels. You can even dedicate few nodes in the cluster for databases by labeling them. With node affinity, all the PostgreSQL pods get assigned to nodes with specific labels.<\/p>\n
                  Connect to PostgreSQL Cluster From Client<\/strong><\/h2>\n
                  Let’s create a psql<\/strong> client pod. We will use it to connect & verify the cluster. Copy the manifest contents as psql-client.yaml<\/code><\/p>\n
                  ---\napiVersion: v1\nkind: Pod\nmetadata:\n  name: pg-client\nspec:\n  containers:\n    - image: bitnami\/postgresql:11.12.0-debian-10-r13\n      name: postgresql\n      env:\n      - name: ALLOW_EMPTY_PASSWORD\n        value: \"yes\"<\/code><\/pre>\n
                  Let’s deploy the client.<\/p>\n
                  kubectl apply -f psql-client.yaml -n database<\/code><\/pre>\n
                  Now let’s try to connect to the cluster.<\/p>\n
                  Copy the password.<\/p>\n
                  kubectl get secret postgres-secrets -n database -o jsonpath=\"{.data.postgresql-password}\" | base64 --decode<\/code><\/pre>\n
                  Exec & connect.<\/p>\n
                  kubectl exec -it pg-client -n database -- \/bin\/bash<\/code><\/pre>\n
                  We will use the password and the pg-pool service endpoint pgpool-svc<\/code> to connect to the cluster.<\/p>\n
                  PGPASSWORD=WbrTpN3g7q psql -h pgpool-svc -p 5432 -U postgres<\/code><\/pre>\n
                  For connecting from outside the cluster.<\/p>\n
                  PGPASSWORD=WbrTpN3g7q psql -h <IP ADDR> -p <NODEPORT ADDR of pgpool-svc-nodeport> -U postgres\n\n\/\/For minikube, IP ADDR can be found out by executing \"minikube ip\"<\/code><\/pre>\n
                  For example,<\/p>\n
                  PGPASSWORD=WbrTpN3g7q psql -h 34.138.59.54 -p 32000 -U postgres<\/code><\/pre>\n
                  Try some basic sample commands:<\/p>\n
                  create database db1; \\c db1; \/\/to connect to new database create table test (id int primary key not null, value text not null); insert into test values (1, ‘value1’); select * from test;<\/p>\n
                  Verifying the PostgreSQL<\/strong> Replication<\/strong><\/h2>\n
                  Command to verify if the replication is taking place.<\/p>\n
                  \/\/after connecting to the cluster using psql client.\nselect * from pg_stat_replication;<\/code><\/pre>\n
                  No. of entries you should see =  No. of replicas of Postgres running minus 1. <\/p>\n
                  Reason for “minus 1”<\/strong>: Data is being replicated from the master to the follower. It’s logically impossible for the Data to be replicated from the master to itself!<\/p>\n
                  Verify the failover by deleting the pods randomly and seeing if the cluster becomes unresponsive or not. I assure you, it will continue to work without issues!<\/p>\n
                  As an exercise, verify thoroughly! I always work on databases with the principle of – Trust but verify.<\/p>\n
                  Sample logs of follower pod when the primary pod goes down: Note how the messages inform the user about a failover taking place!<\/p>\n
                  NOTICE: promoting standby to primary\nDETAIL: promoting server \"postgres-sts-1\" (ID: 1001) using \"\/opt\/bitnami\/postgresql\/bin\/pg_ctl -o \"--config-file=\"\/opt\/bitnami\/postgresql\/conf\/postgresql.conf\" --external_pid_file=\"\/opt\/bitnami\/postgresql\/tmp\/postgresql.pid\" --hba_file=\"\/opt\/bitnami\/postgresql\/conf\/pg_hba.conf\"\" -w -D '\/bitnami\/postgresql\/data' promote\"\n2021-07-28 20:38:11.362 GMT [266] LOG:  received promote request\n2021-07-28 20:38:11.370 GMT [266] LOG:  redo done at 0\/8000028\n2021-07-28 20:38:11.370 GMT [266] LOG:  last completed transaction was at log time 2021-07-28 20:36:57.642182+00\n2021-07-28 20:38:11.698 GMT [266] LOG:  selected new timeline ID: 2\n2021-07-28 20:38:12.494 GMT [266] LOG:  archive recovery complete\nNOTICE: waiting up to 60 seconds (parameter \"promote_check_timeout\") for promotion to complete\nDEBUG: get_recovery_type(): SELECT pg_catalog.pg_is_in_recovery()\nINFO: standby promoted to primary after 0 second(s)\nDEBUG: setting node 1001 as primary and marking existing primary as failed\nDEBUG: begin_transaction()\nDEBUG: commit_transaction()\n2021-07-28 20:38:13.105 GMT [264] LOG:  database system is ready to accept connections\nNOTICE: STANDBY PROMOTE successful<\/code><\/pre>\n
                  Measuring PostgreSQL Performance<\/strong><\/h2>\n
                  As a beginner, let us discuss the things that should be kept in mind regarding Postgre’s performance and metrics.<\/p>\n
                    \n
                  1. Fine Tuning Configurations: <\/strong>Performance mainly depends on the configurations that have been set. For e.g. performance may take a hit at certain points in time due to increased workloads. Ensure that you have followed all the suggested performance optmizations.<\/a><\/li>\n
                  2. Optimized Queries: <\/strong>Another factor that impacts the performance is the types of queries that are dominant. If a lot of write operations are performed in a given time – then performance will go down. It also depends on the optimization of queries. The more optimized the queries – the better performance can be expected.<\/li>\n
                  3. Cluster Size:<\/strong> Resources also affect performance. Let’s say – your cluster is made up of 3 pods. For some use cases – it may be sufficient. For many, it will not be enough.<\/li>\n
                  4. Monitor your Database Metrics & Analyse Logs:<\/strong> Monitor the data based for CPU spikes, number of connections, disk space etc..If you have Prometheus setup<\/a>, you can use the PostgreSQL Prometheus exporter<\/a> to get all the metrics.<\/li>\n
                  5. Other Factors: <\/strong>The workload itself depends on a variety of factors such as backup schedule, traffic spikes, etc.<\/li>\n<\/ol>\n
                    Performance mainly depends on your use case and business commitments. This should be explored even more according to your use case!<\/p>\n
                    Conclusion<\/h2>\n
                    That’s all folks! We have covered the detailed guide on deploying the PostgreSQL<\/strong> statefulset on Kubernetes.<\/p>\n
                    We have also discussed high availability and replication using pgpool and repmgr.<\/p>\n
                    When it comes to production, careful consideration has to be done on persistent volume management, backup, high availability, and performance.<\/p>\n
                    \n
                    Ngu\u1ed3n:<\/strong> How to Deploy PostgreSQL Statefulset Cluster on Kubernetes \u2014 DevOpsCube<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
                    Source: https:\/\/devopscube.com\/deploy-postgresql-statefulset\/<\/p>\n","protected":false},"author":1,"featured_media":841,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-840","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-devops"],"_links":{"self":[{"href":"https:\/\/blog.ngocha.biz\/index.php?rest_route=\/wp\/v2\/posts\/840","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.ngocha.biz\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.ngocha.biz\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.ngocha.biz\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.ngocha.biz\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=840"}],"version-history":[{"count":0,"href":"https:\/\/blog.ngocha.biz\/index.php?rest_route=\/wp\/v2\/posts\/840\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ngocha.biz\/index.php?rest_route=\/wp\/v2\/media\/841"}],"wp:attachment":[{"href":"https:\/\/blog.ngocha.biz\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=840"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ngocha.biz\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=840"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ngocha.biz\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=840"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}