Differences

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

--- postgresql_replication_ha [2020/02/07 16:08] – [Failover] andonovj
+++ postgresql_replication_ha [2024/11/09 19:13] (current) – andonovj
@@ Line 473: / Line 473: @@
-====Failover====
+=====Failover=====
 I was going to kill myself couple times, seriously. I was head banging for at least couple weeks because I Couldn't make PostgreSQL failover. So let me tell you couple things which I found out THE VERY HARD and PAINFUL way.
 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 484: / Line 484: @@
 master_slave_sub_mode = 'stream'
 </code>
+And the following ones, turned off:
+<code:none|Pgpool.conf>
+-bash-4.2$ cat pgpool.conf | grep replication
+replication_mode = off
+                                   # Activate replication mode
+                                   # when in replication mode
+                                   # replication mode, specify table name to
+</code>
+As the two settings are mutually exclusive. Only one can be active at a time.
 This indicates that you ALREADY have streaming replication and that you take care of it.
+====Current State====
 After that, let's see our current state of the cluster:
@@ Line 503: / Line 516: @@
 That clearly states that the postgresqlmaster is the master and postgresqlslaveone is the slave :) I know, stupid naming but bare with me :)
+====Database Failover====
 So what happens after I shutdown the first database:
@@ Line 594: / Line 608: @@
+====PGPool Failover====
 Now, on the slave (new master) you won't see anything on the Pgpool until you don't shutdown the pgpool too. Because usually when a master fails over, the entire server is dead :)
@@ Line 634: / Line 650: @@
 </code>
+====After failover====
+After all this is done, we can check the new status of the cluster :)
+<code:none|After Failover>
+[root@postgresqlslaveone tmp]#  pcp_watchdog_info -p 9898 -h 192.168.0.220 -U postgres
+Password:
+YES postgresqlslaveone:9999 Linux postgresqlslaveone postgresqlslaveone
+postgresqlslaveone:9999 Linux postgresqlslaveone postgresqlslaveone 9999 9000 4 MASTER
+postgresqlmaster:9999 Linux postgresqlmaster postgresqlmaster 9999 9000 10 SHUTDOWN
+[root@postgresqlslaveone tmp]#  psql -h 192.168.0.220 -p 9999 -U postgres postgres -c "show pool_nodes"
+Password for user postgres:
+ node_id |      hostname      | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change
+---------+--------------------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
+       | postgresqlmaster   | 5432 | down   | 0.500000  | standby | 0          | false             | 0                 |                   |                        | 2020-01-28 14:40:20
+       | postgresqlslaveone | 5432 | up     | 0.500000  | primary | 0          | true              | 0                 |                   |                        | 2020-01-28 15:34:16
+(2 rows)
+[root@postgresqlslaveone tmp]#
+</code>
@@ Line 1045: / 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>