YJWANG

[DB] pgpool HA (Streaming_Replication) 본문

50.DB

[DB] pgpool HA (Streaming_Replication)

왕영주 2021. 1. 25. 15:04

refer to

Option

[root@worker02 ~]# su - postgres -c psql
psql (12.5)
Type "help" for help.

postgres=# ALTER USER postgres WITH PASSWORD 'postgres';

Manifest

==> ./pgpool_auth.yaml <==
apiVersion: v1
kind: Secret
metadata:
        name: hot-postgres-auth
type: Opaque
data:
        POSTGRES_USER: cG9zdGdyZXM= #echo -n 'postgres' |base64
        POSTGRES_PASSWORD: cG9zdGdyZXM= #echo -n 'postgres' |base64

==> ./pgpool_configmap.yaml <==
apiVersion: v1
kind: ConfigMap
metadata:
  name: pgpool-config
  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 = '10.97.90.20'
    backend_port0 = 5432
    backend_weight0 = 1
    backend_data_directory0 = '/var/lib/pgsql/12/data'
    backend_flag0 = 'ALLOW_TO_FAILOVER'
    backend_hostname1 = '10.97.90.21'
    backend_port1 = 5432
    backend_weight1 = 1
    backend_data_directory1 = '/var/lib/pgsql/12/data'
    backend_flag1 = 'ALLOW_TO_FAILOVER'
    sr_check_user = 'postgres'
    sr_check_period = 10
    enable_pool_hba = on
    master_slave_mode = on
    num_init_children = 32
    max_pool = 4
    child_life_time = 120
    child_max_connections = 0
    connection_life_time = 0
    client_idle_limit = 0
    connection_cache = on
    load_balance_mode = on
    search_primary_node_timeout = 120

  pcp.conf: |-
    postgres:e8a48653851e28c69d0506508fb27fc5
  pool_passwd: |-
    postgres:md53175bce1d3201d16594cebf9d7eb3f9d
  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             md5

==> ./pgpool_deploy_with_mount_configmap.yaml <==
apiVersion: apps/v1
kind: Deployment
metadata:
  name: pgpool
spec:
  replicas: 1
  selector:
    matchLabels:
      app: pgpool
  template:
    metadata:
      labels:
        app: pgpool
    spec:
      containers:
      - name: pgpool
        image: pgpool/pgpool:4.1
        command: ["sh", "-c", "$PGPOOL_BINARY_DIR/pgpool -n -f $PGPOOLCONF -F $PCP_CONF -a $POOL_HBA_CONF"]
        env:
        - name: POSTGRES_USER
          valueFrom:
            secretKeyRef:
              name: hot-postgres-auth
              key: POSTGRES_USER
        - name: POSTGRES_PASSWORD
          valueFrom:
            secretKeyRef:
              name: hot-postgres-auth
              key: POSTGRES_PASSWORD
        - name: PGPOOL_PARAMS_BACKEND_HOSTNAME0
          value: "10.97.90.20"
        - name: PGPOOL_PARAMS_BACKEND_HOSTNAME1
          value: "10.97.90.21"
        volumeMounts:
        - name: pgpool-config
          mountPath: /usr/local/pgpool-II/etc
      volumes:
      - name: pgpool-config
        configMap:
          name: pgpool-config
---
apiVersion: v1
kind: Service
metadata:
  name: pgpool
spec:
  selector:
    app: pgpool
  ports:
  - name: pgpool-port
    protocol: TCP
    port: 9999
    targetPort: 9999

-

[root@master01 postgre]# psql -h 10.233.46.99 -U postgres -p 9999 -c "\l"
                                 데이터베이스 목록
   이름    |  소유주  | 인코딩 |   Collate   |    Ctype    |      액세스 권한      
-----------+----------+--------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |        |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |        |             |             | postgres=CTc/postgres
 testdb    | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | 
 testdb2   | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | 
 testdb3   | postgres | UTF8   | en_US.UTF-8 | en_US.UTF-8 | 
(6개 행)

[root@master01 postgre]# psql -h 10.233.46.99 -U postgres -p 9999 -c "show pool_nodes"
 node_id |  hostname   | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state
 | last_status_change  
---------+-------------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+-----------------------
-+---------------------
 0       | 10.97.90.20 | 5432 | up     | 0.500000  | primary | 4          | false             | 0                 |                   |                       
 | 2021-01-25 05:14:03
 1       | 10.97.90.21 | 5432 | up     | 0.500000  | standby | 3          | true              | 0                 |                   |                       
 | 2021-01-25 05:14:03
(2개 행)

-

[root@master01 postgre]# for i in $(seq 1 100); do psql -h 10.233.46.99 -U postgres -p 9999 -c "select $i"; done
...
 ?column? 
----------
       98
(1개 행)

 ?column? 
----------
       99
(1개 행)

 ?column? 
----------
      100
(1개 행)

[root@master01 postgre]# psql -h 10.233.46.99 -U postgres -p 9999 -c "show pool_nodes"
 node_id |  hostname   | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state
 | last_status_change  
---------+-------------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+-----------------------
-+---------------------
 0       | 10.97.90.20 | 5432 | down   | 0.500000  | standby | 0          | false             | 0                 |                   |                       
 | 2021-01-25 05:48:51
 1       | 10.97.90.21 | 5432 | up     | 0.500000  | standby | 100        | true              | 0                 |                   |                       
 | 2021-01-25 05:43:23
(2개 행)

[root@master01 postgre]# kubectl rollout restart deployment pgpool 
deployment.apps/pgpool restarted

[root@master01 postgre]# psql -h 10.233.46.99 -U postgres -p 9999 -c "show pool_nodes"
 node_id |  hostname   | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state
 | last_status_change  
---------+-------------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+-----------------------
-+---------------------
 0       | 10.97.90.20 | 5432 | up     | 0.500000  | primary | 0          | true              | 0                 |                   |                       
 | 2021-01-25 06:01:33
 1       | 10.97.90.21 | 5432 | up     | 0.500000  | standby | 0          | false             | 0                 |                   |                       
 | 2021-01-25 06:01:33
(2개 행)
반응형
저작자표시
'50.DB' Related Articles more