skypeの中の人が開発したpostgresql の高可用性を高めるためのツールであるpgbouncerとpl/proxy というツールの使い方を学ぶハンズオンで使うための参考資料です。
pgbouncerはコネクションプーラー、pl/proxy はクエリープロクシーで、これらを効果的に使うことで、オンラインのままスケールアウトすることができるすぐれもののツールです。
このハンズオンは、pgbouncerとpl/proxyがそれぞれどういった働きをするのかという基本をおさえ、実際に試すして理解することを目的としています。
最終的に、もっとも単純な構成で、簡単なパーティショニングとレプリケーションを実装してみることが期待されます。
::::::::::::::
0.initdb-clusters.sh
::::::::::::::
#!/bin/bash -f
#
# initdb clusters for test
#
DBNAME=jpug
DATAROOT=/home/$DBNAME/pgsql
for i in 0 1 2; do ¥
PGDATA=$DATAROOT/data${i}
if [ ! -e $PGDATA ]; then
echo "$USER ¥$ initdb -E UTF-8 -D $PGDATA"
initdb -E UTF-8 -D $PGDATA
( cd $PGDATA; ¥
mv postgresql.conf postgresql.conf.bak; ¥
sed -e "s/#port = 5432¥t/port = 5432$i¥t/" ¥
postgresql.conf.bak > postgresql.conf; ¥
grep "port =" postgresql.conf
)
else
echo -n "ERROR: already existed: "
file $PGDATA
grep "port =" $PGDATA/postgresql.conf
fi
done
exit
::::::::::::::
cluster-start.sh
::::::::::::::
#!/bin/bash -f
#
# start postgres for each data clusters
#
DBNAME=jpug
DATAROOT=/home/$DBNAME/pgsql
mkdir -p $DATAROOT/log
for i in 0 1 2; do ¥
PGDATA=$DATAROOT/data${i}
PGLOG=$DATAROOT/log/pgdata${i}
if [ -d $PGDATA ]; then
echo "$USER ¥$ pg_ctl -D $PGDATA -l $PGLOG start"
pg_ctl -D $PGDATA -l $PGLOG start
sleep 2
TESTDB=`psql -p 5432$i -Al | cut -f1 -d¥| | grep -v = | grep $DBNAME`
if [ "$TESTDB" = "" ]; then
echo "$USER ¥$ createdb -p 5432$i $DBNAME"
createdb -p 5432$i $DBNAME
fi
else
echo "ERROR: database is not existed: $PGDATA"
fi
done
exit
::::::::::::::
cluster-stop.sh
::::::::::::::
#!/bin/bash -f
#
# stop postgres for each data clusters
#
DBNAME=jpug
DATAROOT=/home/$DBNAME/pgsql
for i in 0 1 2; do ¥
#echo "$USER ¥$ dropdb -p 5432$i $DBNAME"
#dropdb -p 5432$i $DBNAME
PGDATA=$DATAROOT/data${i}
echo "$USER ¥$ pg_ctl -D $PGDATA stop"
pg_ctl -D $PGDATA stop
done
exit
::::::::::::::
pgbouncer-start.sh
::::::::::::::
#!/bin/bash -f
export LD_LIBRARY_PATH=/usr/local/lib
DBNAME=jpug
DATAROOT=/home/$DBNAME/pgsql
CONFIGFILE=$DATAROOT/pgbouncer_test.ini
echo "<pgbouncer.ini>: $CONFIGFILE"
grep port $CONFIGFILE
AUTHLINE=`grep auth_file $CONFIGFILE`
echo $AUTHLINE
AUTHFILE=`echo $AUTHLINE | cut -f2 -d=`
echo -n "<account>: "
grep $DBNAME $AUTHFILE
echo "$USER ¥$ pgbouncer -d $CONFIGFILE"
pgbouncer -d $CONFIGFILE
PIDFILE=`grep pidfile $CONFIGFILE | cut -f2 -d=`
echo -n "PID = "
cat $PIDFILE
echo ""
exit
::::::::::::::
pgbouncer-stop.sh
::::::::::::::
#!/bin/bash -f
export LD_LIBRARY_PATH=/usr/local/lib
DBNAME=jpug
DATAROOT=/home/$DBNAME/pgsql
CONFIGFILE=$DATAROOT/pgbouncer_test.ini
PIDFILE=`grep pidfile $CONFIGFILE | cut -f2 -d=`
echo "$USER ¥$ kill ¥`cat $PIDFILE¥`"
kill `cat $PIDFILE`
rm -f $DATAROOT/log/pgbouncer_test.pid
LOGFILE=`grep logfile $CONFIGFILE | cut -f2 -d=`
tail -1 $LOGFILE
exit
::::::::::::::
pgbouncer_test.ini
::::::::::::::
;;;
;;;see: http://pgbouncer.projects.postgresql.org/doc/config.html
;;;
;; database name = connect string
[databases]
proxy = host=127.0.0.1 port=54320 dbname=jpug user=jpug password=xxxxxxxx
part1 = host=127.0.0.1 port=54321 dbname=jpug user=jpug password=xxxxxxxx
part2 = host=127.0.0.1 port=54322 dbname=jpug user=jpug password=xxxxxxxx
;; Configuation section
[pgbouncer]
admin_users = admin
stats_users = stat_collector
;;;
;;; Administrative settings
;;;
logfile = /home/jpug/pgsql/log/pgbouncer_test.log
pidfile = /home/jpug/pgsql/log/pgbouncer_test.pid
;;;
;;; Where to wait for clients
;;;
; ip address or * which means all ip-s
listen_addr = 127.0.0.1
listen_port = 6666
;;;
;;; Authentication settings
;;;
; any, trust, plain, crypt, md5
auth_type = trust
#auth_type = plain
auth_file = /home/jpug/pgsql/pgbouncer_test_u.txt
;;;
;;; Pooler personality questions
;;;
; When server connection is released back to pool:
; session - after client disconnects
; transaction - after transaction finishes
; statement - after statement finishes
pool_mode = session
; When taking idle server into use, this query is ran first.
;
; Query for session pooling:
; ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT
; Query for statement/transaction pooling:
; SELECT 1
; Empty query disables the functionality
server_check_query = select 1
; If server was used more recently that this many seconds ago,
; skip the check query. If 0, the check query is always ran.
server_check_delay = 5000
;;;
;;; Connection limits
;;;
; total number of clients that can connect
max_client_conn = 50
default_pool_size = 50
;;;
;;; Timeouts
;;;
; Close server connection if its been connected longer.
server_lifetime = 120
; Close server connection if its not been used in this time.
; Allows to clean unneccessary connections from pool after peak.
server_idle_timeout = 60
; Cancel connection attepmt if server does not answer takes longer.
server_connect_timeout = 15
; If server login failed (server_connect_timeout or auth failure)
; then wait this many second.
server_login_retry = 15
; Dangerous. Server connection is closed if query does not return
; in this time. Should be used to survive network problems,
; _not_ as statement_timeout. (default: 0)
query_timeout = 20
; Dangerous. Client connection is closed if no activity in this time.
; Should be used to survive network problems. (default: 0)
client_idle_timeout = 0
;;;
;;; Low-level tuning options
;;;
; buffer for streaming packets
pkt_buf = 2048
;;;
;;; networking options, for info: man 7 tcp
;;;
; linux: notify program about new connection only if there
; is also data received. (Seconds to wait.)
tcp_defer_accept = 0
;; following options are reloadable, but apply only to
;; new connections.
; in-kernel buffer size (linux default: 4096)
tcp_socket_buffer = 0
; whether tcp keepalive should be turned on (0/1)
tcp_keepalive = 0
;; following options are linux-specific.
;; they also require tcp_keepalive=1
; count of keepaliva packets
tcp_keepcnt = 0
; how long the connection can be idle,
; before sending keepalive packets
tcp_keepidle = 0
; The time between individual keepalive probes.
tcp_keepintvl = 0
::::::::::::::
pgbouncer_test_u.txt
::::::::::::::
"postgres" "xxxxxxxx"
"jpug" "xxxxxxxx"
"admin" "xxxxxxx"
"someuser" "same_password_as_in_server"
::::::::::::::
test.sh
::::::::::::::
if `sed -e 's/#port = 5432¥t/port = 54320¥t/' postgresql.conf > postgresql.conf.new`; then
echo "success"
else
echo "fault"
fi
::::::::::::::
test1-1.remotetable.sql
::::::::::::::
-- load this on your remote server like:
-- psql -s -p 54321 jpug -h 127.0.0.1 -f test1-1.remotetable.sql
DROP TABLE ユーザ;
CREATE TABLE ユーザ (
ユーザ名 text,
メール text
);
INSERT INTO ユーザ VALUES('さいとう','z-saito@jpug');
INSERT INTO ユーザ VALUES('くわむら','juk@jpug');
insert into ユーザ values('saito', 'h-saitou@ipa');
insert into ユーザ values('kuwamura', 'kuwamura@gmail');
::::::::::::::
test1-2.proxyfunction.sql
::::::::::::::
-- load this for your local server like:
-- psql -s -p 54320 jpug -f $PGSHARE/contrib/plproxy.sql
-- psql -s -p 54320 jpug -f test1-2.proxyfunction.sql
CREATE LANGUAGE plpgsql;
CREATE LANGUAGE plproxy;
CREATE FUNCTION ユーザメール取得(指定ユーザ text)
RETURNS SETOF text AS $$
CONNECT 'port=54321 host=127.0.0.1 dbname=jpug user=jpug password=jpug';
SELECT メール FROM ユーザ WHERE ユーザ名 = $1;
$$ LANGUAGE plproxy;
SELECT * from ユーザメール取得('くわむら');
::::::::::::::
test1-3.proxyfunc2pgbouncer.sql
::::::::::::::
-- ここでは、PgBouncerを通して接続してみます
-- use pgbouncer port to start psql like:
-- psql -s -p 6666 proxy -f test1-3.proxyfunc2pgbouncer.sql
CREATE OR REPLACE FUNCTION ユーザメール取得(指定ユーザ text)
RETURNS SETOF text AS $$
CONNECT 'dbname=part1 port=6666 host=127.0.0.1';
SELECT メール FROM ユーザ WHERE ユーザ名 = $1;
$$ LANGUAGE plproxy;
SELECT * from ユーザメール取得('さいとう');
::::::::::::::
test2-1.proxyschema.sql
::::::::::::::
-- PL/Proxyの構成関数群を定義するためのスキーマ(plproxy)を生成する
-- psql -s -p 54320 jpug -f test2-1.proxyschema.sql
CREATE SCHEMA plproxy;
-- クエリをリモートデータベースに送る必要があるとき、 plproxyは
-- plproxy.get_cluster_partitions(cluster)関数を呼び出し、
-- 各パーティションに送るための接続文字列を取得する
-- (パーティションの数は2の階乗でなくてはならない)
CREATE OR REPLACE FUNCTION plproxy.get_cluster_partitions(cluster_name text)
RETURNS SETOF text AS $$
BEGIN
IF cluster_name = 'クラスタ' THEN
RETURN NEXT 'port=54321 host=127.0.0.1 dbname=jpug user=jpug password=xxxxxxxx';
RETURN NEXT 'port=54322 host=127.0.0.1 dbname=jpug user=jpug password=xxxxxxxx';
RETURN;
END IF;
RAISE EXCEPTION 'クラスタ名が見つかりません';
END;
$$ LANGUAGE plpgsql;
-- plproxy.get_cluster_version(cluster_name) 関数は、リクエスト毎に
-- 呼び出され plproxy.get_cluster_partitions() の結果キャッシュを
-- 出力として再利用できるかどうか決定する
CREATE OR REPLACE FUNCTION plproxy.get_cluster_version(cluster_name text)
RETURNS int4 AS $$
BEGIN
IF cluster_name = 'クラスタ' THEN
RETURN 1;
END IF;
RAISE EXCEPTION 'クラスタ名が見つかりません';
END;
$$ LANGUAGE plpgsql;
-- plproxy.get_cluster_config() 関数はパラメータを調整する。
-- ここでは接続の持続時間を設定している。
-- 他のパラメータについては本体付属文書を参照のこと。
CREATE OR REPLACE FUNCTION plproxy.get_cluster_config(
in cluster_name text,
out key text,
out val text)
RETURNS SETOF record AS $$
BEGIN
-- lets use same config for all clusters
key := 'connection_lifetime';
val := 30*60; -- 30min.
RETURN NEXT;
RETURN;
END;
$$ LANGUAGE plpgsql;
::::::::::::::
test2-2.proxyfunction1.sql
::::::::::::::
-- パーティショニングされるリモート呼び出し
-- psql -s -p 54320 jpug -f test2-2.proxyfunction1.sql
-- ここでは、ユーザ・テーブルは、ユーザ名のハッシュ値によって
-- いくつかのデータベースに分散されていることを前提としている。
-- パーティショニングされるデータベースへの接続文字列は
-- get_cluster_partitions() 関数の中にある。
-- 次の関数は、プロキシーサーバで実行され、適切にパーティショニング
-- されたリモートデータベースから、指定ユーザのメールアドレスを取得。
CREATE FUNCTION ユーザメール取得X(指定ユーザ text)
RETURNS SETOF text AS $$
CLUSTER 'クラスタ';
RUN ON hashtext(指定ユーザ);
SELECT メール FROM ユーザ WHERE ユーザ名 = $1;
$$ LANGUAGE plproxy;
-- 2つのデータベースにパーティショニングされている場合は、
-- ハッシュ値が偶数か奇数かによって振り分けられる
select hashtext('くわむら');
select ユーザメール取得X('くわむら');
select hashtext('kuwamura');
select ユーザメール取得X('kuwamura');
::::::::::::::
test3-1.remotefunction.sql
::::::::::::::
-- 簡単なINSERT機能の例
-- PL/Proxyを通してデータを挿入するために、
-- 挿入ファンクションは実際に実行されるリモート側データベースすべて
-- において、それぞれ定義する必要がある。
-- psql -s -p 54321 -h 127.0.0.1 jpug -f test3-1.remotefunction.sql
-- psql -s -p 54322 -h 127.0.0.1 jpug -f test3-1.remotefunction.sql
-- もちろん、テーブルもそれぞれのリモート側データベースに必要。
-- ここで、ユーザ・テーブルをあらためて作成。
DROP TABLE ユーザ;
CREATE TABLE ユーザ (
ユーザ名 text,
メール text
);
-- 各リモート側データベースに定義する挿入関数
CREATE OR REPLACE FUNCTION ユーザ挿入(ユーザI text, メールI text)
RETURNS integer AS $$
INSERT INTO ユーザ (ユーザ名, メール) VALUES ($1,$2);
SELECT 1;
$$ LANGUAGE SQL;
::::::::::::::
test3-2.proxyfunction.sql
::::::::::::::
-- 挿入クエリを適切なリモートデータベースに送るために、
-- プロキシーサーバ上にプロキシファンクションを定義する。
-- psql -s -p 54320 jpug -f test3-2.proxyfunction.sql
CREATE OR REPLACE FUNCTION ユーザ挿入(ユーザI text, メールI text)
RETURNS integer AS $$
CLUSTER 'クラスタ';
RUN ON hashtext(ユーザI);
$$ LANGUAGE plproxy;
SELECT ユーザ挿入('さいとう','z-saito@jpug');
SELECT ユーザ挿入('くわむら','juk@jpug');
SELECT ユーザ挿入('saito', 'h-saitou@ipa');
SELECT ユーザ挿入('kuwamura', 'kuwamura@gmail');
::::::::::::::
test3-3.proxyuser2pgbouncer.sql
::::::::::::::
-- PgBouncer経由で、プロキシと実体データベースのそれぞれに
-- クエリを投げて状態を確認。
-- psql -s -p 6666 proxy -f test3-3.proxyuser2pgbouncer.sql
SELECT ユーザメール取得X('さいとう');
SELECT ユーザメール取得X('くわむら');
SELECT ユーザメール取得X('saito');
SELECT ユーザメール取得X('kuwamura');
-- 実体のデータベースそれぞれにクエリを投げて状態を確認。
¥c part1
SELECT * from ユーザ;
¥c part2
SELECT * from ユーザ;
::::::::::::::
test3-3a.getusermail.sql
::::::::::::::
-- プロキシにクエリを投げて確認。
-- psql -p 54320 jpug -f test3-3a.getusermail.sql
SELECT ユーザメール取得X('さいとう');
SELECT ユーザメール取得X('くわむら');
SELECT ユーザメール取得X('saito');
SELECT ユーザメール取得X('kuwamura');
::::::::::::::
test3-3b.remoteuser.sql
::::::::::::::
-- 実体のデータベースそれぞれにクエリを投げて状態を確認。
-- psql -p 54321 jpug -f test3-3b.remoteuser.sql
-- psql -p 54322 jpug -f test3-3b.remoteuser.sql
SELECT * from ユーザ;
::::::::::::::
test4-1.remotefunction.sql
::::::::::::::
-- 簡単なINSERT機能の例
-- PL/Proxyを通してデータを挿入するために、
-- 挿入ファンクションは実際に実行されるリモート側データベースすべて
-- において、それぞれ定義する必要がある。
-- psql -s -p 54321 -h 127.0.0.1 jpug -f test4-1.remotefunction.sql
-- psql -s -p 54322 -h 127.0.0.1 jpug -f test4-1.remotefunction.sql
-- もちろん、テーブルもそれぞれのリモート側データベースに必要。
-- ここで、ユーザ・テーブルをあらためて作成。
DROP TABLE ユーザ;
CREATE TABLE ユーザ (
ユーザ名 text,
メール text
);
-- 各リモート側データベースに定義する挿入関数
DROP FUNCTION ユーザ挿入(ユーザI text, メールI text);
--- 戻り値が変わるので消して作り直し
CREATE OR REPLACE FUNCTION ユーザ挿入(ユーザI text, メールI text)
RETURNS TEXT AS $$ --- point!
INSERT INTO ユーザ (ユーザ名, メール) VALUES ($1,$2);
SELECT current_user||'['||ユーザ名||','||メール||']' FROM ユーザ WHERE ユーザ名 =$1; --- point!
$$ LANGUAGE SQL;
::::::::::::::
test4-2.proxyfunction.sql
::::::::::::::
-- 挿入クエリを適切なリモートデータベースに送るために、
-- プロキシーサーバ上にプロキシファンクションを定義する。
-- psql -s -p 54320 jpug -f test4-2.proxyfunction.sql
DROP FUNCTION ユーザ挿入(ユーザI text, メールI text);
--- 戻り値が変わるので消して作り直し
CREATE OR REPLACE FUNCTION ユーザ挿入(ユーザI text, メールI text)
RETURNS SETOF TEXT AS $$ --- point!
CLUSTER 'クラスタ';
RUN ON ALL; --- point!
$$ LANGUAGE plproxy;
SELECT ユーザ挿入('さいとう','z-saito@jpug');
SELECT ユーザ挿入('くわむら','juk@jpug');
SELECT ユーザ挿入('saito', 'h-saitou@ipa');
SELECT ユーザ挿入('kuwamura', 'kuwamura@gmail');
::::::::::::::
test5-1.proxyschema2pgbounce.sql
::::::::::::::
-- PL/Proxyの構成関数群を定義するためのスキーマ(plproxy)を生成する
-- ここでは、構成関数群を定義のうちパーティション取得のスキーマを
-- PgBouncer向けに変更する
-- psql -s -p 6666 proxy -f test5-1.proxyschema2pgbounce.sql
CREATE SCHEMA plproxy;
-- クエリをリモートデータベースに送る必要があるとき、 plproxyは
-- plproxy.get_cluster_partitions(cluster)関数を呼び出し、
-- 各パーティションに送るための接続文字列を取得する
-- (パーティションの数は2の階乗でなくてはならない)
CREATE OR REPLACE FUNCTION plproxy.get_cluster_partitions(cluster_name text)
RETURNS SETOF text AS $$
BEGIN
IF cluster_name = 'クラスタ' THEN
RETURN NEXT 'port=6666 host=127.0.0.1 dbname=part1';
RETURN NEXT 'port=6666 host=127.0.0.1 dbname=part2';
RETURN;
END IF;
RAISE EXCEPTION 'クラスタ名が見つかりません';
END;
$$ LANGUAGE plpgsql;
-- plproxy.get_cluster_version(cluster_name) 関数は、リクエスト毎に
-- 呼び出され plproxy.get_cluster_partitions() の結果キャッシュを
-- 出力として再利用できるかどうか決定する
CREATE OR REPLACE FUNCTION plproxy.get_cluster_version(cluster_name text)
RETURNS int4 AS $$
BEGIN
IF cluster_name = 'クラスタ' THEN
RETURN 1;
END IF;
RAISE EXCEPTION 'クラスタ名が見つかりません';
END;
$$ LANGUAGE plpgsql;
-- plproxy.get_cluster_config() 関数はパラメータを調整する。
-- ここでは接続の持続時間を設定している。
-- 他のパラメータについては本体付属文書を参照のこと。
CREATE OR REPLACE FUNCTION plproxy.get_cluster_config(
in cluster_name text,
out key text,
out val text)
RETURNS SETOF record AS $$
BEGIN
-- lets use same config for all clusters
key := 'connection_lifetime';
val := 30*60; -- 30min.
RETURN NEXT;
RETURN;
END;
$$ LANGUAGE plpgsql;
::::::::::::::
test5-2.proxyfunction.sql
::::::::::::::
-- 挿入クエリを適切なリモートデータベースに送るために、
-- プロキシーサーバ上にプロキシファンクションを定義する。
-- psql -s -p 6666 proxy -f test5-2.proxyfunction.sql
DROP FUNCTION ユーザ挿入(ユーザI text, メールI text);
--- 戻り値が変わるので消して作り直し
CREATE OR REPLACE FUNCTION ユーザ挿入(ユーザI text, メールI text)
RETURNS SETOF TEXT AS $$ --- point!
CLUSTER 'クラスタ';
RUN ON ALL; --- point!
$$ LANGUAGE plproxy;
SELECT ユーザ挿入('さいとう','z-saito@jpug');
SELECT ユーザ挿入('くわむら','juk@jpug');
SELECT ユーザ挿入('saito', 'h-saitou@ipa');
SELECT ユーザ挿入('kuwamura', 'kuwamura@gmail');