ablog

不器用で落着きのない技術者のメモ

Web/AP ->NLB -> Pgpool-II on EC2 -> Aurora PostgreSQL 構成メモ

AWS

Pgpool-II EC2 インスタンス

  • psql をインストール
$ sudo yum -y install gcc
$ sudo yum install -y postgresql postgresql-server postgresql-devel postgresql-contrib postgresql-docs
  • Aurora に接続してみる
$ psql "host=aurora-postgres117.cluster-******.ap-northeast-1.rds.amazonaws.com user=awsuser  dbname=writer port=5432"
  • Pgpool-II ダウンロード
$ wget www.pgpool.net/download.php?f=pgpool-II-4.1.3.tar.gz
  • Pgpool-II インストール
$ tar xfvz pgpool-II-4.1.3.tar.gz
$ cd pgpool-II-4.1.3
$ ./configure 
$ make
$ sudo make install

$ sudo mkdir /var/run/pgpool
$ sudo mkdir /var/log/pgpool
$ sudo chown postgres:postgres /var/log/pgpool/
$ sudo chown postgres:postgres /var/run/pgpool/
  • パスワードファイル作成
$ sudo touch /usr/local/etc/pool_passwd
$ sudo chown postgres:postgres /usr/local/etc/pool_passwd
  • /etc/pgpool-II/pgpool.conf で接続設定
listen_addresses = '*'
backend_hostname0 = 'aurora-postgres117.cluster-******.ap-northeast-1.rds.amazonaws.com'
backend_port0 = 5432
  • ユーザー名、パスワードを設定
$ cd /etc/pgpool-II/
$ sudo pg_md5 --md5auth --username=awsuer password123
$ sudo cat pool_passwd
awsuer:md5***

$ sudo  cp -p /usr/lib/systemd/system/pgpool.service /etc/systemd/system/
$ sudo systemctl enable pgpool.service
$ sudo systemctl is-enabled pgpool.service
enabled
  • 起動
$ sudo systemctl start pgpool.service
$ sudo systemctl status pgpool.service

接続確認

$ psql "host=localhost user=awsuser dbname=writer port=9999"

pgbench

  • パスワード設定
$ vi ~/.pgpass
$ vi chmod 600 ~/.pgpass
aurora-postgres117.cluster-******.ap-northeast-1.rds.amazonaws.com:5432:writer:awsuser:******
aurora-postgres117.cluster-ro-******.ap-northeast-1.rds.amazonaws.com:5432:writer:awsuser:******
172.**.*.165:9999:writer:awsuser:******
  • pgbench をインストール
$ sudo yum -y install postgresql postgresql-contrib
  • データをセットアップ
$ pgbench -i -s 100 -U awsuser -h aurora-postgres117.cluster-******.ap-northeast-1.rds.amazonaws.com -d writer
  • 負荷をかける
$ pgbench -r -c 2 -j 2 -t 1000 -U awsuser -h aurora-postgres117.cluster-******.ap-northeast-1.rds.amazonaws.com -d writer
  • Pgpool-II 経由で負荷をかける
$ pgbench -r -c 2 -j 2 -t 1000 -U awsuser -h 172.**.*.165 -d writer -p 9999

参考