Differences

This shows you the differences between two versions of the page.

--- postgresql_replication_ha [2020/02/08 00:54] – external edit 127.0.0.1
+++ postgresql_replication_ha [2024/11/09 19:13] (current) – andonovj
@@ Line 478: / Line 478: @@
 With PgPool, you can either use postgresql replication to migrate you the data, OR Pgpool replication. By default, when you install pgpool, it will be the FIRST thing you install, HOWEVER in our case was the second, so there is a need of a little modification.
-Please ENSURE the following parameters are set on the notes:
+Please ENSURE the following parameters are set on the nodes:
 <code:none|Pgpool.conf>
@@ Line 1080: / Line 1080: @@
 logger -i -p local1.info follow_master.sh: end: follow master command complete
 exit 0
+</Code>
+=====Implemention with Kubernetes=====
+To implement pgpool can be done either
+) Via variables
+) Configmaps
+In this case, we will use a config map:
+<Code:Configmap>
+apiVersion: v1
+kind: ConfigMap
+metadata:
+  name: pgpool-config
+  namespace: db-test
+  labels:
+    app: pgpool-config
+data:
+  pgpool.conf: |-
+    listen_addresses = '*'
+    port = 9999
+    socket_dir = '/var/run/postgresql'
+    pcp_listen_addresses = '*'
+    pcp_port = 9898
+    pcp_socket_dir = '/var/run/postgresql'
+    backend_hostname0 = experience-db-cluster-alinma-rw
+    backend_port0 = 5432
+    backend_weight0 = 1
+    backend_flag0 = 'ALWAYS_PRIMARY|DISALLOW_TO_FAILOVER'
+    backend_auth_method0 = 'scram-sha-256'
+    backend_password0 = 'experience_db'
+    backend_hostname1 = experience-db-cluster-alinma-ro
+    backend_port1 = 5432
+    backend_weight1 = 1
+    backend_flag1 = 'DISALLOW_TO_FAILOVER'
+    backend_password1 = 'experience_db'
+    backend_auth_method1 = 'scram-sha-256'
+    backend_hostname2 = experience-db-cluster-alinma-ro
+    backend_port2 = 5432
+    backend_weight2 = 2
+    backend_flag2 = 'DISALLOW_TO_FAILOVER'
+    backend_password2 = 'experience_db'
+    backend_auth_method2 = 'scram-sha-256'
+    sr_check_user = 'experience_db'
+    sr_check_password = 'experience_db'
+    sr_check_period = 10
+    enable_pool_hba = on
+    master_slave_mode = on
+    num_init_children = 32
+    max_pool = 4
+    child_life_time = 300
+    child_max_connections = 0
+    connection_life_time = 0
+    client_idle_limit = 0
+    connection_cache = on
+    load_balance_mode = on
+    PGPOOL_PCP_USER = 'experience_db'
+    PGPOOL_PCP_PASSWORD = 'experience_db'
+  pcp.conf: |-
+    experience_db:be22aea2ca31a561e65894d88a2bad32
+  pool_passwd: |-
+    experience_db:be22aea2ca31a561e65894d88a2bad32
+  pool_hba.conf: |-
+    local   all         all                               trust
+    host    all         all         127.0.0.1/32          trust
+    host    all         all         ::1/128               trust
+    host    all         all         0.0.0.0/0             scram-sha-256
+</Code>
+After we create that configmap with:
+<Code:bash|Create configmap>
+kk apply -f configmap.yaml
+</Code>
+We can create the deployment and the service now:
+<Code:bash|Create Depoyment and Service>
+apiVersion: apps/v1
+kind: Deployment
+metadata:
+  name: pgpool
+spec:
+  replicas: 3
+  selector:
+    matchLabels:
+      app: pgpool
+  template:
+    metadata:
+      labels:
+        app: pgpool
+    spec:
+      containers:
+      - name: pgpool
+        image: pgpool/pgpool
+        env:
+        - name: POSTGRES_USERNAME
+          value: "experience_db"
+        - name: POSTGRES_PASSWORD
+          value: "experience_db"
+        - name: PGPOOL_PASSWORD_ENCRYPTION_METHOD
+          value: "scram-sha-256"
+        - name: PGPOOL_ENABLE_POOL_PASSWD
+          value: "true"
+        - name: PGPOOL_SKIP_PASSWORD_ENCRYPTION
+          value: "false"
+        # The following settings are not required when not using the Pgpool-II PCP command.
+        # To enable the following settings, you must define a secret that stores the PCP user's
+        # username and password.
+        #- name: PGPOOL_PCP_USER
+        #  valueFrom:
+        #    secretKeyRef:
+        #      name: pgpool-pcp-secret
+        #      key: username
+        #- name: PGPOOL_PCP_PASSWORD
+        #  valueFrom:
+        #    secretKeyRef:
+        #      name: pgpool-pcp-secret
+        #      key: password
+        volumeMounts:
+        - name: pgpool-config
+          mountPath: /config
+        #- name: pgpool-tls
+        #  mountPath: /config/tls
+      volumes:
+      - name: pgpool-config
+        configMap:
+          name: pgpool-config
+      # Configure your own TLS certificate.
+      # If not set, Pgpool-II will automatically generate the TLS certificate if ssl = on.
+      #- name: pgpool-tls
+      #  secret:
+      #    secretName: pgpool-tls
+---
+apiVersion: v1
+kind: Service
+metadata:
+  name: pgpool
+spec:
+  selector:
+    app: pgpool
+  ports:
+  - name: pgpool-port
+    protocol: TCP
+    port: 9999
+    targetPort: 9999
 </Code>