Postgresql pgpool을 활용한 클러스터 명령어 및 docker-compose

1. pgpool, PostgreSql container 시작 및 종료

pgpool 시작

sudo docker-compose up -d

 

pgpool 종료

sudo docker-compose down

 

PostgreSql 시작

sudo docker-compose up -d

 

PostgreSql 종료

sudo docker-compose down

 

2. pgpool 사용자 추가

DB서버에 사용자를 추가 할 경우 pgpool을 통해서 로그인을 하기 위해서는 pgpool docker-compose.yml 파일에 추가 해야 한다. 수정후 pgpool 재시작

 - PGPOOL_POSTGRES_CUSTOM_USERS=airflow,airflow1

 - PGPOOL_POSTGRES_CUSTOM_PASSWORDS=postgres,postgres

 

3. pgpool node조회 및 reattach

node를 조회 하고 down된 노드를 다시 attach 한다.

 

pgpool node조회

pgpool node조회(down)

pgpool node attach

 

4. PostgreSQL config파일 수정

docker-compose.yml 파읠의 volumes에 아래와 같이 폴더를 추가 한다.

 - /data/cogent/postgresql/extra-custom-conf/:/bitnami/postgresql/conf/conf.d/

 - /data/cogent/postgresql/custom-conf/:/bitnami/repmgr/conf/

extra-custom-conf 폴더를 생성 하고 아래에 extended.conf파일에

max_connections = 1000 추가한다.

추가 한후 재시작 하게 되면 반영된 것을 확인 할 수 있다.

 

5. PostgreSql 에 pgrouting Extension 추가

image에 pgrouting extension이 포함 되어 있지 않을 경우 관련된 파일을 container에 직접 추가 하고 image로 생성 해서 사용 해야 된다.

//파일 다운로드

wget –O pgrouting-3.2.1.tar.gz https://github.com/pgRouting/pgrouting/releases/download/v3.2.1/pgrouting-3.2.1.tar.gz

tar xvfz pgrouting-3.2.1.tar.gz

cd pgrouting-3.2.1

//compile

mkdir build

cd build

cmake  ..

make

sudo make install

//cmake .. 할때 에러 발생

-boost 에러 : sudo apt install libboost-program-options-dev

-postgresql 서버 찾을 수 없다고 나올때 : sudo apt-get install postgresql-server-dev-12

//SQL파일 카피

cp ./pgrouting-3.2.1/build/sql/*.sql /tmp/sql/

cp /usr/share/postgresql/12/extension/pgrouting.control /tmp/sql/

//container에 파일 카피

sudo docker cp /tmp/sql/. cogent-pg0:/opt/bitnami/postgresql/share/extension/

//lib카피

sudo cp /lib/x86_64-linux-gnu/libstdc++.so.6 /tmp/lib/

sudo cp ./pgrouting-3.2.1/build/lib/libpgrouting-3.2.so /tmp/lib/

//container에 파일 카피

sudo docker cp /tmp/sql/. cogent-pg0:/opt/bitnami/postgresql/share/extension/

sudo docker cp /tmp/lib/libstdc++.so.6 cogent-pg0:/usr/lib/x86_64-linux-gnu/

sudo docker cp /tmp/lib/libpgrouting-3.2.so cogent-pg0:/opt/bitnami/postgresql/lib/

 

 

6. PostgreSql 변경된 이미지 생성

PostgreSql에 추가되 pgrouting Extension을 포함한 이미지 생성

수정된 Container를 이미지로 생성 한다.

sudo docker cogent-pg0 stop

sudo docker commit -m "pgrouting extension add" cogent-pg0 cogent/postgresql-repmgr:12

생성된 이미지 확인

sudo docker image ls

 

docker-compose.yml 수정

image: bitnami/postgresql-repmgr:12 -> image: cogent/postgresql-repmgr:12

container 실행

sudo docker-compose up -d

 

 

7. pgbench를 이용한 load balancing 테스트

pbbench 관련 테이블 생성(초기화)

pgbench -h 192.168.xx.xx -p 54320 -U postgres -i -s 50

 

 

pbbench 실행

pgbench -h 192.168.xx.xx -p 54320 -U postgres -c 10 -j 10 -S -T 60

 

8. pgpool docker-compose.yml

version: '2.1'

services:

  pgpool:

    image: bitnami/pgpool:4

    container_name: cogent-pgpool

    ports:

      - 54320:5432

    environment:

      - PGPOOL_BACKEND_NODES=0:192.168.xx.xx:54321,1:192.168.xx.xx:54322,2:192.168.xx.xx:54323,3:192.168.xx.xx:54324

      - PGPOOL_SR_CHECK_USER=postgres

      - PGPOOL_SR_CHECK_PASSWORD=postgres

      - PGPOOL_ENABLE_LDAP=no

      - PGPOOL_POSTGRES_USERNAME=postgres

      - PGPOOL_POSTGRES_PASSWORD=postgres

      - PGPOOL_ADMIN_USERNAME=postgres

      - PGPOOL_ADMIN_PASSWORD=postgres

      - PGPOOL_POSTGRES_CUSTOM_USERS=airflow

      - PGPOOL_POSTGRES_CUSTOM_PASSWORDS=!skb1234

      - PGPOOL_ENABLE_LOAD_BALANCING=yes

      - PGPOOL_AUTO_FAILBACK=yes

      - PGPOOL_BACKEND_APPLICATION_NAMES=pg-0,pg-1,pg-3,pg-4

      - PGPOOL_ENABLE_POOL_HBA=yes

      - PGPOOL_MAX_POOL=10000

    healthcheck:

      test: ["CMD", "/opt/bitnami/scripts/pgpool/healthcheck.sh"]

      interval: 10s

      timeout: 5s

      retries: 5

 

 

9. PostgreSql docker-compose.yml

version: '2.1'

services:

  pg-0:

    image: bitnami/postgresql-repmgr:12

    container_name: cogent-pg0

    ports:

      - 54321:5432

    volumes:

      - pg_0_data:/bitnami/postgresql

      - /data/ocean_upload/oldfile:/data/ocean_upload/oldfile

      - /data/cogent/postgresql/extra-custom-conf/:/bitnami/postgresql/conf/conf.d/

      - /data/cogent/postgresql/custom-conf/:/bitnami/repmgr/conf/

    environment:

      - POSTGRESQL_POSTGRES_PASSWORD=postgres

      - POSTGRESQL_USERNAME=postgres

      - POSTGRESQL_PASSWORD=postgres

      - POSTGRESQL_DATABASE=postgres

      - POSTGRESQL_NUM_SYNCHRONOUS_REPLICAS=3

      - REPMGR_PASSWORD=postgres

      - REPMGR_PRIMARY_HOST=pg-0

      - REPMGR_PARTNER_NODES=pg-0,pg-1,pg-3,pg-4

      - REPMGR_NODE_NAME=pg-0

      - REPMGR_NODE_NETWORK_NAME=pg-0

      - POSTGRESQL_CLUSTER_APP_NAME=pg-0

  pg-1:

    image: bitnami/postgresql-repmgr:12

    container_name: cogent-pg1

    ports:

      - 54322:5432

    volumes:

      - pg_1_data:/bitnami/postgresql

      - /data/ocean_upload/oldfile:/data/ocean_upload/oldfile

      - /data/cogent/postgresql/extra-custom-conf/:/bitnami/postgresql/conf/conf.d/

      - /data/cogent/postgresql/custom-conf/:/bitnami/repmgr/conf/

    environment:

      - POSTGRESQL_POSTGRES_PASSWORD=postgres

      - POSTGRESQL_USERNAME=postgres

      - POSTGRESQL_PASSWORD=postgres

      - POSTGRESQL_DATABASE=postgres

      - POSTGRESQL_NUM_SYNCHRONOUS_REPLICAS=3

      - REPMGR_PASSWORD=postgres

      - REPMGR_PRIMARY_HOST=pg-0

      - REPMGR_PARTNER_NODES=pg-0,pg-1,pg-3,pg-4

      - REPMGR_NODE_NAME=pg-1

      - REPMGR_NODE_NETWORK_NAME=pg-1

      - POSTGRESQL_CLUSTER_APP_NAME=pg-1

  pg-3:

    image: bitnami/postgresql-repmgr:12

    container_name: cogent-pg3

    ports:

      - 54323:5432

    volumes:

      - pg_3_data:/bitnami/postgresql

      - /data/ocean_upload/oldfile:/data/ocean_upload/oldfile

      - /data/cogent/postgresql/extra-custom-conf/:/bitnami/postgresql/conf/conf.d/

      - /data/cogent/postgresql/custom-conf/:/bitnami/repmgr/conf/

    environment:

      - POSTGRESQL_POSTGRES_PASSWORD=postgres

      - POSTGRESQL_USERNAME=postgres

      - POSTGRESQL_PASSWORD=postgres

      - POSTGRESQL_DATABASE=postgres

      - POSTGRESQL_NUM_SYNCHRONOUS_REPLICAS=3

      - REPMGR_PASSWORD=postgres

      - REPMGR_PRIMARY_HOST=pg-0

      - REPMGR_PARTNER_NODES=ppg-0,pg-1,pg-3,pg-4

      - REPMGR_NODE_NAME=pg-3

      - REPMGR_NODE_NETWORK_NAME=pg-3

      - POSTGRESQL_CLUSTER_APP_NAME=pg-3

  pg-4:

    image: bitnami/postgresql-repmgr:12

    container_name: cogent-pg4

    ports:

      - 54324:5432

    volumes:

      - pg_4_data:/bitnami/postgresql

      - /data/ocean_upload/oldfile:/data/ocean_upload/oldfile

      - /data/cogent/postgresql/extra-custom-conf/:/bitnami/postgresql/conf/conf.d/

      - /data/cogent/postgresql/custom-conf/:/bitnami/repmgr/conf/

    environment:

      - POSTGRESQL_POSTGRES_PASSWORD=postgres

      - POSTGRESQL_USERNAME=postgres

      - POSTGRESQL_PASSWORD=postgres

      - POSTGRESQL_DATABASE=postgres

      - POSTGRESQL_NUM_SYNCHRONOUS_REPLICAS=3

      - REPMGR_PASSWORD=postgres

      - REPMGR_PRIMARY_HOST=pg-0

      - REPMGR_PARTNER_NODES=ppg-0,pg-1,pg-3,pg-4

      - REPMGR_NODE_NAME=pg-4

      - REPMGR_NODE_NETWORK_NAME=pg-4

      - POSTGRESQL_CLUSTER_APP_NAME=pg-4

volumes:

  pg_0_data:

    driver: local

  pg_1_data:

    driver: local

  pg_3_data:

    driver: local

  pg_4_data:

    driver: local