1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@ryoma-nagata(Ryoma Nagata)

PgPool-ll インストレーションガイド

1
Posted at

PgPool-ll インストレーションガイド

PgPoolによる負荷分散アーキテクチャのインストール方法についてメモ

アーキテクチャ図

image.png

1. Azure Database for PostgreSQL インストール (マスタサーバ作成)

下記リンク先の手順に従い、マスタサーバとなるテータベースの作成を行います。
クイック スタート:Azure Portal で Azure Database for PostgreSQL サーバーを作成する

2. Azure Database for PostgreSQL 読み取りレプリカ作成

下記リンク先の手順に従い、レプリカとなるデータベースを必要な数作成します。

Azure portal から Azure Database for PostgreSQL - 単一サーバーの読み取りレプリカを作成および管理する

3. Azure Database for PostgreSQLへの接続設定の変更

リソース作成後、接続できるようにAzure 内からの接続とAzure portal を使用してサーバー レベルの IP ファイアウォール規則を管理するを行います。
image.png

4. Ubuntu 18.04 LTS (VM) インストール

Azure Portal から リソースの作成 から Ubuntu 18.04 LTS Free をインストールします。

  1. リソースの作成を押下します。
    image.png

  2. 検索フィールドに "Ubuntu Server 18.04 LTS" を入力し、検索します。
    image.png

  3. 作成ボタンを押下します。
    image.png

  4. サーバの作成条件を入力・選択し、ページ下にある "次:ディスク" を押下します。
    image.png

  5. OSディスクの種類を選択し "新しいディスクを作成し接続する" を押下します。
    image.png

  6. "サイズを変更します" を押下します。
    image.png

  7. "サイズを変更します" を押下し、ディスクタイプ・ディスクサイズを選択します。
    image.png

    ※プロジェクトの要求に合わせて設定してください。

  8. "確認および作成" を押下します。
    image.png

  9. ページに示される作成条件を確認し、問題がなければ "作成" を押下します。

  10. Ubuntu(VM)用に作成されたネットワーク セキュリティ グループ をオープンします。
    image.png

  11. 使用するポート(22, 80, 9898, 9999)と使用しないポート(443) の設定を行います。
    image.png

5. PgPool-ll インストール

  1. 作成したVMにアクセスし、aptコマンドのパッケージ管理にPgPool-llを追加します。

    echo "deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main" | sudo tee -a /etc/apt/sources.list.d/pgdg.list
sudo su
curl -s https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
exit
sudo apt update
sudo apt upgrade

  2. 作成したVMにて下記のコマンドを実行します。
    ※コマンドを実行する前に環境変数(SERVERNAME, USERNAME, PASSWORD)を設定する必要があります。

    SERVERNAME : pgPool-ll のサーバ名(短縮名) を設定します。
    USERNAME : Postgresql に設定した ユーザID を設定します。
    PASSWORD : Postgresql に設定した ユーザのパスワードを設定します。

SERVERNAME={pgpool-ll server short name}
USERNAME={postgresql user id}
PASSWORD={postgresql user password}
sudo su  
# Install Pgpool II and Memcached
apt-get -y install pgpool2 memcached
# Pgpool query cache requires a work directory to record table oids
mkdir -p /var/log/pgpool/oiddir
chown postgres:postgres /var/log/pgpool/oiddir
# Before modifying Pgpool and Memcached config files, save them 
cp /etc/pgpool2/pgpool.conf  /etc/pgpool2/pgpool.conf.save
cp /etc/pgpool2/pool_hba.conf /etc/pgpool2/pool_hba.conf.save
cp /etc/memcached.conf /etc/memcached.conf.save
# Create self-signed certificate to allow connecting to Pgpool using SSL
# Note: Adjust this step to meet your security requirements
openssl req -new -newkey rsa:4096 -days 3650 -nodes -x509 -subj "/C=US/ST=Denial/L=Seattle/O=Dis/CN=www.pgpooltest.org" -keyout /etc/pgpool2/server.key  -out /etc/pgpool2/server.crt
# Modify Pgpool config
# Turn SSL on
sed -i 's/ssl = off/ssl = on/g' /etc/pgpool2/pgpool.conf
# Use SSL key and cert created above
sed -i "s/#ssl_key = '.\/server.key'/ssl_key = '\/etc\/pgpool2\/server.key'/g" /etc/pgpool2/pgpool.conf
sed -i "s/#ssl_cert = '.\/server.cert'/ssl_cert = '\/etc\/pgpool2\/server.crt'/g" /etc/pgpool2/pgpool.conf
# Accept all incoming connections 
sed -i "s/listen_addresses = 'localhost'/listen_addresses = '*'/g" /etc/pgpool2/pgpool.conf
# Set Azure PostgreSQL backend
sed -i "s/backend_hostname0 = 'localhost'/backend_hostname0 = '$SERVERNAME.postgres.database.azure.com'/g" /etc/pgpool2/pgpool.conf 
# Use pool_hba.conf for client authentication
sed -i 's/enable_pool_hba = off/enable_pool_hba = on/g' /etc/pgpool2/pgpool.conf
# Enable query cache
sed -i "s/memory_cache_enabled = off/memory_cache_enabled = on/g" /etc/pgpool2/pgpool.conf 
# Use Memcached as query cache store
sed -i "s/memqcache_method = 'shmem'/memqcache_method = 'memcached'/g" /etc/pgpool2/pgpool.conf
# Require all clients connecting to Pgpool to authenticate with password (scram-sha-256 in this case)
echo "host all all 0.0.0.0/0 scram-sha-256" > /etc/pgpool2/pool_hba.conf
# Write pool_passwd used by Pgpool for password authentication with Azure PostgreSQL backend
echo $USERNAME@$SERVERNAME:$PASSWORD > /etc/pgpool2/pool_passwd
chmod 600 /etc/pgpool2/pool_passwd
chown postgres:postgres /etc/pgpool2/*
# Modify Memcached config
# Allow Memcached to use 1024 MB of memory
sed -i "s/m 64/m 1024/g" /etc/memcached.conf
# Start Pgpool and Memcached services
# Start Memcached
service memcached restart
# Set defaults for Pgpool service
echo "PGPOOL_CONFIG_FILE=/etc/pgpool2/pgpool.conf" >> /etc/default/pgpool2
   echo "PGPOOL_HBA_CONFIG_FILE=/etc/pgpool2/pool_hba.conf" >> /etc/default/pgpool2
echo "PGPOOL_PCP_CONFIG_FILE=/etc/pgpool2/pcp.conf" >> /etc/default/pgpool2
   echo "PGPOOL_PID_FILE=/var/run/postgresql/pgpool.pid" >> /etc/default/pgpool2
    update-rc.d pgpool2 defaults
    #Start Pgpool
    service pgpool2 restart
    exit

  1. PgPool-ll の設定を行います。
    /etc/pgpool/pgpool.conf をvimなどのエディタで編集します。

    ※confファイルの中身は非常に長いものになっているため、変更する項目だけを掲載しています。

    pgpool.conf
     #PgPool-ll DB Connection Port No.
 port = 9999
 #Azure Database for PostgreSQL のマスタサーバ設定
 backend_hostname0 = '{マスタサーバのホスト名}'
 backend_port0     =  {マスタサーバのポートNo}
 backend_weight0   =  {のマスタサーバのロードバランス配分:0≦m}
 #Azure Database for PostgreSQL のレプリカサーバ設定 (作成したレプリカ分追加・編集します)
 backend_hostname{n} = '{レプリカサーバn のホスト名}'
 backend_port{n}     =  {レプリカサーバn のポートNo}
 backend_weight{n}   =  {レプリカサーバn のロードバランス配分:0≦m}
 backend_application_name{n} = 'server{n}'
 replication_mode = on
 replicate_select = off
 insert_lock = on
 load_balance_mode = on
 statement_level_load_balance = on
 master_slave_mode = off

  2. PgPool-ll を再起動します。

    sudo systemctl restart pgpool2.service

  3. PgPool-ll への接続確認
    ローカルPCなどにDBクライアントアプリケーション（今回はAzure Data Studioを想定）から PgPool-llへ接続出来ることを確認します。
    image.png

  4. Postgresql動作確認
    PgPool-llへの接続を行ったDBクライアントアプリケーションから SHOW POOL_NODESの
    実行を行い、Postgresql のマスター&スレーブが正しく動作していることを確認します。

  • 確認ポイント
    1. STATUS列が"up"になっていること
    2. lb_weightにPgPool-llの設定(/etc/pgpool/pgpool.conf)値が反映されていること
    3. role列にマスタサーバとして設定したhostname の値が master, それ以外が slave となっていること
      image.png

6. phpPgAdmin インストール

  1. phpパッケージをインストールします。

    sudo apt install software-properties-common
sudo apt-add-repository ppa:ondrej/php
sudo apt update
sudo apt install php

  2. apache2パッケージをインストールします。

    apt -y install apache2

  3. phpPgAdminパッケージをインストールします。

    sudo apt-get -y install phppgadmin php-pgsql

  4. phpPgAdmin設定ファイル (/etc/phppgadmin/config.inc.php)を編集します。

    config.inc.php
    // PgPool-llの設定 (/etc/pgpool/pgpool.conf) で設定したポートNo (poet) 設定値
$conf['servers'][0]['port'] = 9999;
// Azure Database for PostgreSQLの生成で作成されたデフォルトDB
$conf['servers'][0]['defaultdb'] = 'postgres';
// テーブルオーナー以外のアクセスを許可
$conf['owned_only'] = false

  5. apache2設定ファイル (/etc/apache2/conf-enabled/phppgadmin.conf)を編集します。

    phppgadmin.conf
    #外部からのアクセスを許可します。
Require all granted

  6. apache2を再起動します。

    sudo systemctl restart postgresql apache2

  7. ブラウザからURL({hostname}/phppgadmin/index.php)にアクセスし動作を確認します。
    image.png

7. 参考 - ロードバランス動作確認

  1. 確認用テーブル作成

    CREATE TABLE public.staff
(
 id "character(4)" COLLATE pg_catalog."default" NOT NULL,
 name text COLLATE pg_catalog."default" NOT NULL,
 age integer
);

  2. 確認用データ登録

    insert into STAFF values('0001', 'ダイアモンド✡ユカイ', 53);
insert into STAFF values('0002', '大悟', 52);
insert into STAFF values('0003', 'DaiGo', 29);
insert into STAFF values('0004', 'DAIGO', 6);
insert into STAFF values('0005', '大地真央', 5);
insert into STAFF values('0006', '大地康雄', 29);
insert into STAFF values('0007', '大東駿介', 8);
insert into STAFF values('0008', '平愛梨', 28);
insert into STAFF values('0009', '平祐奈', 34);
insert into STAFF values('0010', '田尾安志', 41);
insert into STAFF values('0011', 'タカ', 8);
insert into STAFF values('0012', '高岡早紀', 40);
insert into STAFF values('0013', '高岡奏輔', 28);
insert into STAFF values('0014', '高樹沙耶', 18);
insert into STAFF values('0015', '高木菜那', 46);
insert into STAFF values('0016', '高木ブー', 47);
insert into STAFF values('0017', '高木美帆', 35);
insert into STAFF values('0018', '高木美保', 21);
insert into STAFF values('0019', '高倉健', 21);
insert into STAFF values('0020', '貴景勝光信', 53);
insert into STAFF values('0021', '高島彩', 26);
insert into STAFF values('0022', '高島忠夫', 2);
insert into STAFF values('0023', '高嶋ちさ子', 8);
insert into STAFF values('0024', '高嶋政伸', 24);
insert into STAFF values('0025', '高嶋政宏', 29);
insert into STAFF values('0026', '高島礼子', 6);
insert into STAFF values('0027', '高城亜樹', 26);
insert into STAFF values('0028', '高田純次', 9);
insert into STAFF values('0029', '高田文夫', 14);
insert into STAFF values('0030', '高田万由子', 5);
insert into STAFF values('0031', '高田みづえ', 48);
insert into STAFF values('0032', '高知東生', 12);
insert into STAFF values('0033', '高津臣吾', 45);
insert into STAFF values('0034', '高月彩良', 0);
insert into STAFF values('0035', '貴闘力忠茂', 42);
insert into STAFF values('0036', '高梨沙羅', 28);
insert into STAFF values('0037', '高梨臨', 8);
insert into STAFF values('0038', '高野人母美', 46);
insert into STAFF values('0039', '貴ノ浪貞博', 57);
insert into STAFF values('0040', '貴乃花光司', 52);
insert into STAFF values('0041', '高橋愛', 10);
insert into STAFF values('0042', '高橋礼華', 46);
insert into STAFF values('0043', '高橋一生', 26);
insert into STAFF values('0044', '高橋和也', 58);
insert into STAFF values('0045', '高橋克典', 22);
insert into STAFF values('0046', '高橋克実', 45);
insert into STAFF values('0047', '高橋恵子', 43);
insert into STAFF values('0048', '高橋元太郎', 18);
insert into STAFF values('0049', '高橋茂雄', 4);
insert into STAFF values('0050', '高橋ジョージ', 10);
insert into STAFF values('0051', '高橋大輔', 20);
insert into STAFF values('0052', '高橋尚子', 22);
insert into STAFF values('0053', '髙橋ひかる', 13);
insert into STAFF values('0054', '高橋尚成', 6);
insert into STAFF values('0055', '高橋英樹', 29);
insert into STAFF values('0056', '高橋ひとみ', 25);
insert into STAFF values('0057', '高橋真麻', 55);
insert into STAFF values('0058', '高橋真梨子', 57);
insert into STAFF values('0059', '高橋光臣', 1);
insert into STAFF values('0060', '高橋みなみ', 21);
insert into STAFF values('0061', '高橋優', 55);
insert into STAFF values('0062', '高橋ユウ', 26);
insert into STAFF values('0063', '高橋幸宏', 38);
insert into STAFF values('0064', '高橋由美子', 53);
insert into STAFF values('0065', '高橋由伸', 7);
insert into STAFF values('0066', '高橋メアリージュン', 36);
insert into STAFF values('0067', '高畑淳子', 51);
insert into STAFF values('0068', '高畑充希', 55);
insert into STAFF values('0069', '高畑裕太', 2);
insert into STAFF values('0070', '高原直泰', 7);
insert into STAFF values('0071', 'TAKAHIRO', 10);
insert into STAFF values('0072', '高見恭子', 30);
insert into STAFF values('0073', '高見知佳', 2);
insert into STAFF values('0074', '高見盛精彦', 19);
insert into STAFF values('0075', '高見沢俊彦', 21);
insert into STAFF values('0076', '高峰秀子', 1);
insert into STAFF values('0077', '高見山大五郎', 41);
insert into STAFF values('0078', '高山一実', 11);
insert into STAFF values('0079', '宝田明', 24);
insert into STAFF values('0080', '田川寿美', 50);
insert into STAFF values('0081', '太川陽介', 12);
insert into STAFF values('0082', '滝川クリステル', 31);
insert into STAFF values('0083', '多岐川華子', 34);
insert into STAFF values('0084', '多岐川裕美', 43);
insert into STAFF values('0085', '滝沢カレン', 30);
insert into STAFF values('0086', '滝沢秀明', 25);
insert into STAFF values('0087', '滝藤賢一', 52);
insert into STAFF values('0088', '瀧本美織', 53);
insert into STAFF values('0089', '田口淳之介', 31);
insert into STAFF values('0090', '田口浩正', 59);
insert into STAFF values('0091', '宅麻伸', 43);
insert into STAFF values('0092', '宅間孝行', 59);
insert into STAFF values('0093', '武豊', 1);
insert into STAFF values('0094', '武井咲', 14);
insert into STAFF values('0095', '武井壮', 51);
insert into STAFF values('0096', '竹内まりや', 9);
insert into STAFF values('0097', '竹内結子', 2);
insert into STAFF values('0098', '竹内力', 55);
insert into STAFF values('0099', '竹内涼真', 15);
insert into STAFF values('0100', '竹下景子', 28);
insert into STAFF values('0101', '竹下佳江', 24);
insert into STAFF values('0102', '武田久美子', 23);
insert into STAFF values('0103', '武田真治', 54);
insert into STAFF values('0104', '武田双雲', 41);
insert into STAFF values('0105', '竹田恒泰', 24);
insert into STAFF values('0106', '武田鉄矢', 23);
insert into STAFF values('0107', '武田修宏', 26);
insert into STAFF values('0108', '武田梨奈', 40);
insert into STAFF values('0109', '武田玲奈', 53);
insert into STAFF values('0110', '竹中直人', 10);
insert into STAFF values('0111', '竹野内豊', 57);
insert into STAFF values('0112', '竹原慎二', 6);
insert into STAFF values('0113', '竹本孝之', 28);
insert into STAFF values('0114', '田崎真也', 5);
insert into STAFF values('0115', '田嶋陽子', 14);
insert into STAFF values('0116', '田島令子', 54);
insert into STAFF values('0117', '舘ひろし', 40);
insert into STAFF values('0118', '田知本遥', 6);
insert into STAFF values('0119', '辰巳琢郎', 39);
insert into STAFF values('0120', '辰吉丈一郎', 26);
insert into STAFF values('0121', '伊達みきお', 16);
insert into STAFF values('0122', '立川志の輔', 48);
insert into STAFF values('0123', '田中絹代', 14);
insert into STAFF values('0124', '田中邦衛', 21);
insert into STAFF values('0125', '田中圭', 19);
insert into STAFF values('0126', '田中健', 55);
insert into STAFF values('0127', '田中聖', 49);
insert into STAFF values('0128', '田中卓志', 51);
insert into STAFF values('0129', '田中哲司', 13);
insert into STAFF values('0130', '田中直樹', 32);
insert into STAFF values('0131', '田中史朗', 31);
insert into STAFF values('0132', '田中将大', 49);
insert into STAFF values('0133', '田中雅美', 6);
insert into STAFF values('0134', '田中美佐子', 0);
insert into STAFF values('0135', '田中美里', 38);
insert into STAFF values('0136', '田中道子', 24);
insert into STAFF values('0137', '田中美奈子', 41);
insert into STAFF values('0138', '田中みな実', 39);
insert into STAFF values('0139', '田中康夫', 14);
insert into STAFF values('0140', '田中裕子', 22);
insert into STAFF values('0141', '田中裕二', 57);
insert into STAFF values('0142', '田中要次', 37);
insert into STAFF values('0143', '田中好子', 46);
insert into STAFF values('0144', '田中理恵', 56);
insert into STAFF values('0145', '田中律子', 25);
insert into STAFF values('0146', '田中麗奈', 49);
insert into STAFF values('0147', '田辺誠一', 1);
insert into STAFF values('0148', '田辺徳雄', 21);
insert into STAFF values('0149', '田辺靖雄', 37);
insert into STAFF values('0150', '谷隼人', 50);
insert into STAFF values('0151', '谷亮子', 2);
insert into STAFF values('0152', '谷繁元信', 27);
insert into STAFF values('0153', '谷原章介', 33);
insert into STAFF values('0154', '谷村新司', 49);
insert into STAFF values('0155', '楽しんご', 21);
insert into STAFF values('0156', '田畑智子', 55);
insert into STAFF values('0157', '田原総一朗', 3);
insert into STAFF values('0158', '田原俊彦', 48);
insert into STAFF values('0159', 'タブレット純', 41);
insert into STAFF values('0160', '多部未華子', 17);
insert into STAFF values('0161', '玉置浩二', 19);
insert into STAFF values('0162', '玉木宏', 54);
insert into STAFF values('0163', '玉袋筋太郎', 47);
insert into STAFF values('0164', '玉村豊男', 20);
insert into STAFF values('0165', '玉森裕太', 15);
insert into STAFF values('0166', '玉山鉄二', 46);
insert into STAFF values('0167', '田丸麻紀', 33);
insert into STAFF values('0168', '田村淳', 58);
insert into STAFF values('0169', '田村裕', 45);
insert into STAFF values('0170', '田村正和', 59);
insert into STAFF values('0171', '田村亮', 20);
insert into STAFF values('0172', '田村亮', 0);
insert into STAFF values('0173', '為末大', 59);
insert into STAFF values('0174', '田山涼成', 44);
insert into STAFF values('0175', '樽美酒研二', 37);
insert into STAFF values('0176', 'ダルビッシュ有', 5);
insert into STAFF values('0177', 'ダレノガレ明美', 3);
insert into STAFF values('0178', '俵万智', 20);
insert into STAFF values('0179', '檀ふみ', 4);
insert into STAFF values('0180', '壇蜜', 6);
insert into STAFF values('0181', '檀れい', 22);
insert into STAFF values('0182', '段田安則', 2);
insert into STAFF values('0183', '団長安田', 56);
insert into STAFF values('0184', 'ダンディ坂野', 36);
insert into STAFF values('0185', '丹波哲郎', 58);
insert into STAFF values('0186', 'ダンプ松本', 41);
insert into STAFF values('0187', 'たかし', 18);
insert into STAFF values('0188', 'だいたひかる', 30);
insert into STAFF values('0189', 'NULL', 31);
insert into STAFF values('0190', '戸田恵梨香', 32);
insert into STAFF values('0191', 'フランチェスカ・ヘイワード', 27);

  3. ロードバランス動作確認
    SHOW POOL_NODES の1回目・2回目実行時のselect_cnt列値の前後比較を行い、master行が
    カウントアップしないこと、およびslave行がカウントアップされることを確認します。

    確認方法1 - 以下のSQLを順次実行し、確認します。

    1.SHOW POOL_NODES;
2.select * from staff where id = '0001';
  select * from staff where id = '0001';
3.SHOW POOL_NODES;

    確認方法2 - Azure Data Studio より Notebookとして実行し、確認します。
    ※枠内のJSONをNotebook.ipynbとして保存し、Azure Data Studioでロード・実行します。

    Notebook.ipynb
    {
 "metadata": {
     "kernelspec": {
         "name": "SQL",
         "display_name": "SQL",
         "language": "sql"
     },
     "language_info": {
         "name": "sql",
         "version": ""
     }
 },
 "nbformat_minor": 2,
 "nbformat": 4,
 "cells": [
     {
         "cell_type": "code",
         "source": [
             "SHOW POOL_NODES"
         ],
         "metadata": {
             "azdata_cell_guid": "7b284a00-4b14-43df-ac4c-e0dda753721c"
         },
         "outputs": [],
         "execution_count": null
     },
     {
         "cell_type": "code",
         "source": [
             "select * from staff where id = '0001';\r\n",
             "select * from staff where id = '0001';"
         ],
         "metadata": {
             "azdata_cell_guid": "28a99b8d-7d07-43cb-b379-51fbe985f183",
             "tags": []
         },
         "outputs": [],
         "execution_count": null
     },
     {
         "cell_type": "code",
         "source": [
             "SHOW POOL_NODES"
         ],
         "metadata": {
             "azdata_cell_guid": "186ca40e-b728-49bf-8d4b-88ed44e2793d"
         },
         "outputs": [],
         "execution_count": null
  }
 ]
}
1
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?