Setting Up Streaming Standby for PGO Crunchy PostgreSQL Cluster

· 5 min read · PostgreSQL

Setting Up Streaming Standby for PostgreSQL Cluster

Table of Contents

Introduction

What is a Streaming Standby PostgreSQL Cluster?

A streaming standby PostgreSQL cluster is a high-availability configuration where a secondary (standby) database server continuously receives real-time updates from a primary database server. This setup provides several key benefits:

  • Disaster Recovery: If the primary server fails, the standby can quickly take over
  • Read Scaling: Standby servers can be used for read-heavy operations
  • Minimal Data Loss: Continuous streaming replication ensures near-real-time data synchronization

Understanding the Components

  • Primary Cluster: The main database server handling write operations
  • Standby Cluster: A replica server that receives continuous updates from the primary
  • TLS Certificates: Ensure secure, encrypted communication between servers
  • Kubernetes: Container orchestration platform managing the database clusters

Prerequisites

Before beginning, ensure you have:

  1. A running PostgreSQL primary cluster
  2. Access to a Kubernetes cluster
  3. Required tools:
    • kubectl command-line tool
    • openssl for certificate generation
    • Basic understanding of Kubernetes and PostgreSQL concepts

Step 1: Prepare TLS Certificates

Why TLS Certificates?

TLS (Transport Layer Security) certificates ensure:

  • Encrypted communication between primary and standby clusters
  • Authentication of servers
  • Protection against man-in-the-middle attacks

Detailed Certificate Generation Process

Create a Directory for Keys

1

mkdir keys && cd keys

Create a Certificate Authority (CA) Certificate

1
2

openssl genrsa -out ca.key 4096
openssl req -new -x509 -key ca.key -out ca.crt -days 365 -subj "/CN=mydatabase-ca"
  • Generates a root certificate authority key and certificate
  • Valid for 365 days
  • Used to sign other certificates

Create a Server Certificate for Primary Cluster

1
2
3

openssl genrsa -out mydatabase-server.key 4096
openssl req -new -key mydatabase-server.key -out mydatabase-server.csr -subj "/CN=mydatabase-primary"
openssl x509 -req -in mydatabase-server.csr -CA ca.crt -CAkey ca.key -CAcreateserial -out mydatabase-server.crt -days 365
  • Generates a unique server certificate for the primary cluster
  • Signed by the CA certificate for added security

Create a Replication Certificate

1
2
3

openssl genrsa -out mydatabase-repl.key 4096
openssl req -new -key mydatabase-repl.key -out mydatabase-repl.csr -subj "/CN=_crunchyrepl"
openssl x509 -req -in mydatabase-repl.csr -CA ca.crt -CAkey ca.key -CAcreateserial -out mydatabase-repl.crt -days 365
  • Specifically for secure replication communication
  • Also signed by the CA certificate

Copy Keys to Standby Cluster

  • Use scp or rsync to transfer the keys directory
  • Ensure secure transmission of sensitive certificate files

Step 2: Create Kubernetes Secrets

What are Kubernetes Secrets?

  • Securely store sensitive information like certificates
  • Prevent hardcoding credentials in deployment files
  • Easily managed and rotated

Create Primary Cluster TLS Secret

1
2
3
4

kubectl create secret generic -n db-ns mydatabase-cluster.tls \
  --from-file=ca.crt=ca.crt \
  --from-file=tls.key=mydatabase-server.key \
  --from-file=tls.crt=mydatabase-server.crt

Create Replication TLS Secret

1
2
3
4

kubectl create secret generic -n db-ns mydatabase-replication.tls \
  --from-file=ca.crt=ca.crt \
  --from-file=tls.key=mydatabase-repl.key \
  --from-file=tls.crt=mydatabase-repl.crt

Step 3: Configure Primary Cluster

Configuring the Primary PostgreSQL Cluster

Add TLS secret references to your PostgreSQL cluster configuration:

1
2
3
4
5

spec:
  customTLSSecret:
    name: mydatabase-cluster.tls
  customReplicationTLSSecret:
    name: mydatabase-replication.tls

Here is the full yaml file:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76

---
apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: mydatabase
  namespace: db-ns
  labels:
    pg-cluster: mydatabase
    pgo-version: 5.6.7
spec:
  #image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres:ubi8-15.7-1
  #image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres:ubi8-13.9-1
  image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres:ubi8-13.8-1
  postgresVersion: 13
  instances:
  - name: mydatabase
    replicas: 1
    dataVolumeClaimSpec:
      accessModes: ["ReadWriteOnce"]
      resources:
        requests:
          storage: 5Gi
      storageClassName: rook-ceph-block
    resources:
      requests:
        cpu: "2"
        memory: "4Gi"
      limits:
        cpu: "2"
        memory: "4Gi"
  backups:
    pgbackrest:
      # image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbackrest:ubi8-2.51-1
      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbackrest:ubi8-2.40-1
      repos:
      - name: repo1
        volume:
          volumeClaimSpec:
            accessModes: ["ReadWriteOnce"]
            resources:
              requests:
                storage: 5Gi
            storageClassName: rook-ceph-block
  monitoring:
    pgmonitor:
      exporter:
        image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres:ubi8-15.7-1
  users:
  - name: mydatabaseuser
    databases: [mydb]
  customTLSSecret:
    name: mydatabase-cluster.tls
  customReplicationTLSSecret:
    name: mydatabase-replication.tls
  patroni:
    dynamicConfiguration:
      postgresql:
        parameters:
          max_connections: "600"
          shared_buffers: "4GB"
          effective_cache_size: "6GB"
          maintenance_work_mem: "2GB"
          checkpoint_completion_target: "0.9"
          wal_buffers: "16MB"
          default_statistics_target: "100"
          random_page_cost: "1.1"
          effective_io_concurrency: "200"
          min_wal_size: "1GB"
          max_wal_size: "4GB"
          max_worker_processes: "8"
          max_parallel_workers_per_gather: "4"
          max_parallel_workers: "8"
          max_parallel_maintenance_workers: "4"
          wal_keep_size: "2048MB"
          max_standby_archive_delay: "-1"
          hot_standby: "on"

Step 4: Expose Primary Cluster via NodePort

Why Use NodePort?

  • Allows external access to the PostgreSQL service
  • Assigns a high-numbered port accessible from outside the cluster
1
2
3

kubectl expose svc mydatabase-ha --port=5432 --target-port=5432 \
  --name=mydatabase-ha-nodeport --type=NodePort \
  --selector=postgres-operator.crunchydata.com/patroni=mydatabase-ha -n db-ns

Verify the Service

1

kubectl get svc mydatabase-ha-nodeport -n db-ns

Step 5: Configure Standby Cluster

Standby Cluster Configuration

Key configuration elements for the standby cluster:

1
2
3
4
5
6
7
8
9

spec:
  customTLSSecret:
    name: mydatabase-cluster.tls
  customReplicationTLSSecret:
    name: mydatabase-replication.tls
  standby:
    enabled: true
    host: <REDACTED_IP>  # Primary cluster's IP
    port: 5432

Here is the full yaml file:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77

---
apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: mydatabase-standby
  namespace: db-ns
  labels:
    pg-cluster: mydatabase
    pgo-version: 5.6.7
spec:
  image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres:ubi8-13.8-1
  postgresVersion: 13
  instances:
  - name: mydatabase-standby
    replicas: 1
    dataVolumeClaimSpec:
      accessModes: ["ReadWriteOnce"]
      resources:
        requests:
          storage: 5Gi
      storageClassName: rook-ceph-block
    resources:
      requests:
        cpu: "2"
        memory: "4Gi"
      limits:
        cpu: "2"
        memory: "4Gi"
  backups:
    pgbackrest:      
      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbackrest:ubi8-2.40-1
      repos:
      - name: repo1
        volume:
          volumeClaimSpec:
            accessModes: ["ReadWriteOnce"]
            resources:
              requests:
                storage: 5Gi
            storageClassName: rook-ceph-block
  monitoring:
    pgmonitor:
      exporter:
        image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres:ubi8-15.7-1
  customTLSSecret:
    name: mydatabase-cluster.tls
  customReplicationTLSSecret:
    name: mydatabase-replication.tls
  standby:
    enabled: true
    host: <A node IP>
    port: <Exposed NodePort>
  users:
  - name: mydatabaseuser
    databases: [mydb]
  patroni:
    dynamicConfiguration:
      postgresql:
        parameters:
          max_connections: "600"
          shared_buffers: "4GB"
          effective_cache_size: "6GB"
          maintenance_work_mem: "2GB"
          checkpoint_completion_target: "0.9"
          wal_buffers: "16MB"
          default_statistics_target: "100"
          random_page_cost: "1.1"
          effective_io_concurrency: "200"
          min_wal_size: "1GB"
          max_wal_size: "4GB"
          max_worker_processes: "8"
          max_parallel_workers_per_gather: "4"
          max_parallel_workers: "8"
          max_parallel_maintenance_workers: "4"
          wal_keep_size: "2048MB"
          max_standby_archive_delay: "-1"
          hot_standby: "on"

Step 6: Deploy and Verify the Standby Cluster

Deployment

1

kubectl apply -f mydatabase-standby.yaml

Verification Steps

Check Pod Status

1

kubectl get pods -n db-ns

Review Replication Logs

1

kubectl logs <standby-pod-name> -n db-ns

Troubleshooting

Common Issues and Solutions

  1. Connection Problems
    • Check firewall rules
    • Verify network connectivity
    • Confirm correct IP and port configurations
  2. TLS/Authentication Errors
    • Verify certificate generation
    • Check secret creation
    • Ensure matching CN (Common Name) in certificates
  3. Replication Lag
    • Monitor replication status
    • Check network bandwidth
    • Review PostgreSQL configuration parameters

Conclusion

By following these steps, you’ve created a robust, secure streaming standby PostgreSQL cluster. This configuration provides high availability, disaster recovery, and scalability for your database infrastructure.

Next Steps

  • Implement regular backup strategies
  • Set up monitoring and alerting
  • Practice failover scenarios
  • Continuously review and optimize configuration

Note: Always test in a staging environment before implementing in production.

Tags: kubernetes postgresql streaming-replication

Related Posts