PostgreSQL relies on replication for high availability, failover, and balancing read requests across multiple nodes. Streaming Replication ensures that data written to the primary PostgreSQL database is mirrored on to one or more standby (replica) nodes.

Standby nodes accept read-only connections, so all traffic related to any Reporting or BI Dashboard applications can be routed to standby nodes thereby reducing the load on primary database instance.

In this post lets look at how to configure Postgres streaming replication in a Kubernetes cluster.

Prerequisites

• Kubernetes Cluster
• NFS Server (Kubernetes PVC)

NOTE: This post assumes an existing Kubernetes cluster is already setup with a NFS mount. Setting up of kubernetes cluster is not in the scope of this post.

PostgreSQL Streaming Replication

Streaming replication works by streaming any modifications from Primary Database to Standby Database instances (number of standby databases can be configured based on the requirement). Standby nodes can be used to offload read queries there by reducing the load on Primary Database.

A Standby Database instance can be promoted to Primary in case the Primary Database crashes. It is always advised not to configure Primary and Standby Database instances on the same disk or NFS drive.

Following diagram illustrates a Primary Database configured with 2 Standby Database instances on different NFS drives. Applications can use Primary Database for write operations and redirect reads to Standby instances.

Kubernetes – Configure Streaming Replication

In this example we will be configuring a Primary Database instance and 1 Standby instance. Two NFS volume mounts will be used namely

• nfs – for Primary Database
• nfs-standby – for Standby Database

Setup Primary Database

Create a config map with environmental variables required for Postgres. Create a new file called db-config.yaml and pase the following contents.

apiVersion: v1
kind: ConfigMap
metadata:
  name: izdb-config
  namespace: default
  labels:
    type: config
    app: izdb
data:
  POSTGRES_PASSWORD: "changeme"
  TIMESCALEDB_TELEMETRY: "off"

Execute the below command to create the config map –

kubectl create -f db-config.yaml 

Deployment Pod definition for initialising Postgres primary database. Create a new file called db-primary.yaml and paste the below contents –

apiVersion: apps/v1
kind: Deployment
metadata:
  name: izdb
spec:
  replicas: 1
  selector:
    matchLabels:
      app: izdb
  template:
    metadata:
      labels:
        type: deployment
        app: izdb
    spec:
      containers:
      - image: postgres:10
        imagePullPolicy: IfNotPresent
        name: izdb
        ports:
          - containerPort: 5432
        envFrom:
          - configMapRef:
              name: izdb-config
        volumeMounts:
          - name: izdbmnt
            mountPath: /var/lib/postgresql/data
            subPath: izdb/data
      volumes:
      - name: izdbmnt
        persistentVolumeClaim:
          claimName: nfs

Execute the below command to create the pod –

kubectl create -f db-primary.yaml

Once izdb pod comes up the data directory in nfs i.e. izdb/data will be initialised with required files which is sufficient to run a standalone instance of Postgres server. But, to configure replication few parameters have to be changes in postgresql.conf and pg_hba.conf

Execute the following commands in the izdb pod (use “kubectl exec -it <pod_id> bash” to get into the pod/container). We are going to create a replication user and physical replication slot.

> su - postgres
> psql
# SET password_encryption = 'scram-sha-256';
# CREATE ROLE repuser WITH REPLICATION PASSWORD 'changeme' LOGIN;
# SELECT * FROM pg_create_physical_replication_slot('replica_1_slot');

Copy postgresql.conf and pg_hba.conf from the izdb pod to a local directory called config.

Append following contents to postgresql.conf

#Replication Settings

wal_level = replica
max_wal_senders = 2
max_replication_slots = 2
synchronous_commit = off

Append following contents to pg_hba.conf

host    replication     repuser         0.0.0.0/0       scram-sha-256

NOTE: 0.0.0.0/0 can be replaced with the Standby Database ip.

Execute the following command to create conf files map from the config directory –

kubectl create configmap izdb-conf-files-config --from-file config

Now lets mount the updated postgresql.conf and pg_hba.conf to postgres data directory to enable streaming replication. Replace the contents of db-primary.yaml with following –

apiVersion: apps/v1
kind: Deployment
metadata:
  name: izdb
spec:
  replicas: 1
  selector:
    matchLabels:
      app: izdb
  template:
    metadata:
      labels:
        type: deployment
        app: izdb
    spec:
      containers:
      - image: postgres:10
        imagePullPolicy: IfNotPresent
        name: izdb
        ports:
          - containerPort: 5432
        envFrom:
          - configMapRef:
              name: izdb-config
        volumeMounts:
          - name: izdbmnt
            mountPath: /var/lib/postgresql/data
            subPath: izdb/data
      volumes:
      - name: izdbmnt
        persistentVolumeClaim:
          claimName: nfs
      - name: izdb-conf-files-config-mnt
        configMap:
          name: izdb-conf-files-config
      initContainers:
      - name: init-izdb
        image: busybox:1.28
        command: ['sh', '-c', ' cp /var/lib/postgresql/data/postgresql.main.conf /var/lib/postgresql/data/postgresql.conf && cp /var/lib/postgresql/data/pg_hba.main.conf /var/lib/postgresql/data/pg_hba.conf && chmod 600 /var/lib/postgresql/data/postgresql.conf && chmod 600 /var/lib/postgresql/data/pg_hba.conf']
        volumeMounts:
        - name: izdbmnt
          mountPath: /var/lib/postgresql/data
          subPath: izdb/data
        - name: izdb-conf-files-config-mnt
          mountPath: /var/lib/postgresql/data/postgresql.main.conf
          subPath: postgresql.conf
        - name: izdb-conf-files-config-mnt
          mountPath: /var/lib/postgresql/data/pg_hba.main.conf
          subPath: pg_hba.conf            

Expose the izdb pod as a service to be accessed by other applications. Create a new file called izdb-service.yaml

apiVersion: v1
kind: Service
metadata:
  name: izdb
spec:
  type: NodePort
  selector:
    app: izdb
  ports:
    - protocol: TCP
      port: 5432
      targetPort: 5432

Execute the following command to create service –

kubectl create -f izdb-service.yaml

Setup Standby Database

Before configuring the Standby instance we need to sync Standby instance with Primary Database state. We will be configuring Standby instance in a different nfs drive called nfs-standby

Create a new file called izdb-standby.yaml and paste the below contents –

apiVersion: apps/v1
kind: Deployment
metadata:
  name: izdb-standby
spec:
  replicas: 1
  selector:
    matchLabels:
      app: izdb-standby
  template:
    metadata:
      labels:
        type: izdb-standby
        app: izdb-standby
    spec:
      containers:
      - image: busybox:1.28
        imagePullPolicy: Always
        name: izdb-standby
      volumes:
      - name: izdb-standby-mnt
        persistentVolumeClaim:
          claimName: nfs-standby
      - name: izdb-standby-config-mnt
        configMap:
          name: izdb-standby-conf-files-config
      initContainers:
      - name: init-izdb-standby
        image: postgres:10
        command: ['sh', '-c', 'PGPASSWORD="changeme" pg_basebackup -h izdb -D /var/lib/postgresql/data -U repuser -vP ']
        volumeMounts:
        - name: izdb-standby-mnt
          mountPath: /var/lib/postgresql/data
          subPath: izdb_standby/data    

Execute the following command to create a base backup of Primary Database instance –

kubectl create -f izdb-standby.yaml

NOTE: We are using a busy box instance as main container as we should not run Postgres in Primary mode after base backup. After base backup we need to start Postgres in Standby mode.

Copy postgresql.conf from the izdb-standby pod to a local directory called standby-config and append following contents –

hot_standby = on
wal_level = replica
max_wal_senders = 2
max_replication_slots = 2
synchronous_commit = off

Create a new file called recovery.conf in standby-config directory with following contents –

standby_mode = on
primary_conninfo = 'host=izdb port=5432 user=repuser password=changeme application_name=r1'
primary_slot_name = 'replica_1_slot' # Name of the replication slot we created on the master
trigger_file = '/var/lib/postgresql/data/change_to_master'

Execute the following command to create conf files map from the standby-config directory –

kubectl create configmap izdb-standby-conf-files-config --from-file standby-config

Delete the pod izdb-standby and replace the contents of izdb-standby.yaml with following contents

apiVersion: apps/v1
kind: Deployment
metadata:
  name: izdb-standby
spec:
  replicas: 1
  selector:
    matchLabels:
      app: izdb-standby
  template:
    metadata:
      labels:
        type: izdb-standby
        app: izdb-standby
    spec:
      containers:
      - image: postgres:10
        imagePullPolicy: IfNotPresent
        name: izdb-standby
        ports:
          - containerPort: 5432
        envFrom:
          - configMapRef:
              name: izdb-config
        volumeMounts:
          - name: izdb-standby-mnt
            mountPath: /var/lib/postgresql/data
            subPath: izdb_standby/data
      volumes:
      - name: izdb-standby-mnt
        persistentVolumeClaim:
          # use nfs for dev and nfs-standby for prod  
          #claimName: nfs
          claimName: nfs-standby
      - name: izdb-standby-config-mnt
        configMap:
          name: izdb-standby-conf-files-config
      initContainers:
      - name: init-izdb-standby
        image: timescale/timescaledb:latest-pg10
        command: ['sh', '-c', 'cp /var/lib/postgresql/data/postgresql.main.conf /var/lib/postgresql/data/postgresql.conf && cp /var/lib/postgresql/data/recovery.main.conf /var/lib/postgresql/data/recovery.conf && chmod 600 /var/lib/postgresql/data/postgresql.conf && chmod 600 /var/lib/postgresql/data/recovery.conf']
        volumeMounts:
        - name: izdb-standby-mnt
          mountPath: /var/lib/postgresql/data
          subPath: izdb_standby/data    
        - name: izdb-standby-config-mnt
          mountPath: /var/lib/postgresql/data/postgresql.main.conf
          subPath: postgresql.conf
        - name: izdb-standby-config-mnt
          mountPath: /var/lib/postgresql/data/recovery.main.conf
          subPath: recovery.conf              

Execute the following command to create Standby Database instance

kubectl create -f izdb-standby.yaml

With this Postgres Streaming Replication should be configured with one Primary and one Standby Database instances.

End Note

Hope you found this article interesting, do drop us a comment below with your inputs, views, and opinions regarding Kubernetes – Configure PostgreSQL Streaming Replication

Mulesoft Code Analysis

Interested in trying out our exciting new product on automated Mulesoft Code Analysis and learn how it reduces project costs by over 80%?

Please follow the link below to know more:

Quantifying benefits of IZ Analyzer for Mule Projects