linux:pgpouncer

差分

このページの2つのバージョン間の差分を表示します。

この比較画面へのリンク

次のリビジョン		 前のリビジョン
linux:pgpouncer [2012/11/07 20:03] – 作成 ともやんlinux:pgpouncer [2019/05/18 02:23] (現在) – 外部編集 非ログインユーザー
行 4: 行 4:
 ===== libevent のインストール ===== ===== libevent のインストール =====
   - [[http://libevent.org/|libevent]] より libevent をダウンロードする。<code>   - [[http://libevent.org/|libevent]] より libevent をダウンロードする。<code>
-$ curl -L -O https://github.com/downloads/libevent/libevent/libevent-2.0.20-stable.tar.gz+$ mkdir ~/work 
 +$ cd ~/work 
 +$ curl -L -O https://sourceforge.net/projects/levent/files/libevent/libevent-2.0/libevent-2.0.22-stable.tar.gz
 </code> </code>
   - ファイルを解凍する。<code>   - ファイルを解凍する。<code>
-$ tar zxvf libevent-2.0.20-stable.tar.gz+$ tar zxvf libevent-2.0.22-stable.tar.gz
 </code> </code>
-  - libevent をビルドする。<code> +  - libevent のビルドとインストールを行う。<code> 
-$ cd libevent-2.0.20-stable+$ cd libevent-2.0.22-stable
 $ ./configure $ ./configure
 $ make $ make
行 24: 行 26:
 </code> </code>
  
 +===== PgBouncer のインストール =====
 +  - [[http://pgfoundry.org/frs/?group_id=1000258|PgFoundry: PgBouncer: ファイルリスト]] より PgBouncer をダウンロードする。<code>
 +$ mkdir ~/work
 +$ cd ~/work
 +$ curl -O http://pgfoundry.org/frs/download.php/3393/pgbouncer-1.5.4.tar.gz
 +</code>
 +  - ファイルを解凍する。<code>
 +$ tar zxvf pgbouncer-1.5.4.tar.gz
 +</code>
 +  - PgBouncer のビルドとインストールを行う。<code>
 +$ cd pgbouncer-1.5.4
 +$ ./configure --prefix=/usr/local --with-libevent=/usr/local
 +$ make
 +$ sudo make install
 +</code>
 +  - ディレクトリを作成する。<code>
 +$ sudo mkdir -p /var/log/pgbouncer
 +$ sudo mkdir -p /var/run/pgbouncer
 +</code>
 +  - 実行ユーザーを作成する。<code>
 +$ sudo useradd pgbouncer
 +</code>
 +  - オーナーを設定する。<code>
 +$ sudo chown pgbouncer:pgbouncer /var/log/pgbouncer
 +$ sudo chown pgbouncer:pgbouncer /var/run/pgbouncer
 +</code>
 +  - rc スクリプトを作成する。<code>
 +$ sudo vi /etc/rc.d/init.d/pgbouncer
 +</code><code>
 +#!/bin/sh
 +# pgbouncer    This is the init script for starting up the pgbouncer
 +#
 +# chkconfig: - 64 36
 +# description: Starts and stops the pgbouncer daemon.
 +#
 +# processname: pgbouncer
 +# pidfile: /var/run/pgbouncer/pgbouncer.pid
 +#
 +# v2.0 Devrim GUNDUZ <devrim@CommandPrompt.com>
 +# - Adjust order of startup and kill.
 +#
 +# v2.1 Devrim GUNDUZ <devrim@gunduz.org>
 +# - Fix chkconfig line
 +# - Start pgbouncer in daemon mode, so that it won't log twice
 +# - to the same log file.
 +#
 +# v2.2 Devrim GUNDUZ <devrim@gunduz.org>
 +# - Sleep 2 seconds before getting pid during start(), like
 +#   we do in PostgreSQL init script, to avoid false positive
 +#   startup errors.
 +
 +# Source function library.
 +INITD=/etc/rc.d/init.d
 +. $INITD/functions
 +
 +# Get function listing for cross-distribution logic.
 +TYPESET=`typeset -f|grep "declare"`
 +
 +# Get config.
 +. /etc/sysconfig/network
 +
 +# Find the name of the script
 +NAME=`basename $0`
 +if [ ${NAME:0:1} = "S" -o ${NAME:0:1} = "K" ]
 +then
 +        NAME=${NAME:3}
 +fi
 +
 +# For SELinux we need to use 'runuser' not 'su'
 +if [ -x /sbin/runuser ]
 +then
 +    SU=runuser
 +else
 +    SU=su
 +fi
 +
 +# Override defaults from /etc/sysconfig/pgbouncer if file is present
 +[ -f /etc/sysconfig/${NAME} ] && . /etc/sysconfig/${NAME}
 +
 +# Check that networking is up.
 +[ "${NETWORKING}" = "no" ] && exit 0
 +
 +script_result=0
 +
 +start(){
 +        BOUNCER_START=$"Starting ${NAME} service: "
 +
 +        # Make sure startup-time log file is valid
 +        if [ ! -e "$BOUNCERLOG" -a ! -h "$BOUNCERLOG" ]
 +        then
 +                touch "$BOUNCERLOG" || exit 1
 +                chown pgbouncer:pgbouncer "$BOUNCERLOG"
 +                chmod go-rwx "$BOUNCERLOG"
 +                [ -x /usr/bin/chcon ] && /usr/bin/chcon -u system_u -r object_r -t postgresql_log_t "$BOUNCERLOG" 2>/dev/null
 +        fi
 +
 +        # Make sure that pgbouncer is not already running:
 +        if [ -f /var/run/${NAME}/${NAME}.pid ]
 +        then
 +                echo "${NAME} is already running"
 +                echo_failure
 +                echo
 +                exit 1
 +        fi
 +
 +        echo -n "$BOUNCER_START"
 +        [ -d /var/run/${NAME} ] || mkdir /var/run/${NAME}
 +        chown pgbouncer:pgbouncer /var/run/${NAME}
 +
 +        $SU -l pgbouncer -c "pgbouncer -d '$BOUNCERCONF' & " >> "$BOUNCERLOG" 2>&1 < /dev/null
 +        sleep 2
 +        pid=`pidof -s pgbouncer`
 +        if [ $pid ]
 +        then
 +          success "$BOUNCER_START"
 +          touch /var/lock/subsys/${NAME}
 +          echo
 +        else
 +          failure "$BOUNCER_START"
 +          echo
 +          script_result=1
 +        fi
 +}
 +
 +stop(){
 +        echo -n $"Stopping ${NAME} service: "
 +        if [ -f /var/run/${NAME}/${NAME}.pid ]
 +        then
 +                kill -TERM `cat "/var/run/${NAME}/${NAME}.pid"`
 +                ret=$?
 +                if [ $ret -eq 0 ]
 +                then
 +                        echo_success
 +                else
 +                        echo_failure
 +                        script_result=1
 +                fi
 +                echo
 +                  rm -f "/var/run/${NAME}/${NAME}.pid"
 +                  rm -f "/var/lock/subsys/${NAME}"
 +        else
 +                echo "   ${NAME} is not running."
 +                echo_failure
 +                echo
 +                exit 1
 +        fi
 +}
 +
 +restart(){
 +    stop
 +    start
 +}
 +
 +condrestart(){
 +    [ -e /var/lock/subsys/${NAME} ] && restart
 +}
 +
 +condstop(){
 +    [ -e /var/lock/subsys/${NAME} ] && stop
 +}
 +
 +reload(){
 +    $SU -l pgbouncer -c "kill -HUP `cat /var/run/${NAME}/${NAME}.pid`" > /dev/null 2>&1 < /dev/null
 +}
 +pause(){
 +    $SU -l pgbouncer -c "kill -USR1 `cat /var/run/${NAME}/${NAME}.pid`" > /dev/null 2>&1 < /dev/null
 +}
 +resum(){
 +   $SU -l pgbouncer -c "kill -USR2 `cat /var/run/${NAME}/${NAME}.pid`" > /dev/null 2>&1 < /dev/null
 +}
 +# See how we were called.
 +case "$1" in
 +  start)
 +        start
 +        ;;
 +  stop)
 +        stop
 +        ;;
 +  status)
 +        status pgbouncer
 +        script_result=$?
 +        ;;
 +  restart)
 +        restart
 +        ;;
 +  pause)
 +        pause
 +        ;;
 +  continue)
 +        resum
 +        ;;
 +  condrestart)
 +        condrestart
 +        ;;
 +  condstop)
 +        condstop
 +        ;;
 +  reload|force-reload)
 +        reload
 +        ;;
 +  *)
 +        echo $"Usage: $0 {start|stop|status|restart|pause|continue|reload|force-reload|condrestart|condstop}"
 +        exit 1
 +esac
 +
 +exit $script_result
 +</code>
 +  - rc スクリプトのパーミッションを設定する。<code>
 +$ sudo chmod 755 /etc/rc.d/init.d/pgbouncer
 +</code>
 +  - 自動起動の設定を行う。<code>
 +$ sudo chkconfig pgbouncer on
 +</code>
 +  - デフォルト設定ファイルを作成して、設定ファイルとログファイルのパスを設定する。<code>
 +$ sudo vi /etc/sysconfig/pgbouncer
 +</code><code>
 +#
 +# pgbouncer defaults
 +#
 +
 +# Path to the init file
 +BOUNCERCONF=/etc/pgbouncer.ini
 +
 +# Path to the log file
 +BOUNCERLOG=/var/log/pgbouncer/pgbouncer.log
 +</code>
 +  - の設定を行う。<code>
 +$ sudo vi /etc/pgbouncer.ini
 +</code><code>
 +;; database name = connect string
 +;;
 +;; connect string params:
 +;;   dbname= host= port= user= password=
 +;;   client_encoding= datestyle= timezone=
 +;;   pool_size= connect_query=
 +[databases]
 +;template1 = host=pgsql.mydomain.net dbname=template1 user=pgadmin password=pgadmin pool_size=1
 +database_name1  = host=localhost dbname=database_name1 user=database_user1 password=password1
 +database_name2  = host=localhost dbname=database_name2 user=database_user2 password=password2
 +database_name3  = host=localhost dbname=database_name3 user=database_user3 password=password3
 +
 +; foodb over unix socket
 +;foodb =
 +
 +; redirect bardb to bazdb on localhost
 +;bardb = host=localhost dbname=bazdb
 +
 +; acceess to dest database will go with single user
 +;forcedb = host=127.0.0.1 port=300 user=baz password=foo client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'
 +
 +; use custom pool sizes
 +;nondefaultdb = pool_size=50 reserve_pool=10
 +
 +; fallback connect string
 +;* = host=testserver
 +
 +;; Configuration section
 +[pgbouncer]
 +
 +;;;
 +;;; Administrative settings
 +;;;
 +
 +logfile = /var/log/pgbouncer/pgbouncer.log
 +pidfile = /var/run/pgbouncer/pgbouncer.pid
 +
 +;;;
 +;;; Where to wait for clients
 +;;;
 +
 +; ip address or * which means all ip-s
 +listen_addr = 127.0.0.1
 +listen_port = 6432
 +
 +user = pgbouncer
 +
 +; unix socket is also used for -R.
 +; On debian it should be /var/run/postgresql
 +;unix_socket_dir = /tmp
 +;unix_socket_mode = 0777
 +;unix_socket_group =
 +
 +;;;
 +;;; Authentication settings
 +;;;
 +
 +; any, trust, plain, crypt, md5
 +;auth_type = trust
 +auth_type = any
 +;auth_file = /8.0/main/global/pg_auth
 +;auth_file = /etc/pgbouncer/userlist.txt
 +
 +;;;
 +;;; Users allowed into database 'pgbouncer'
 +;;;
 +
 +; comma-separated list of users, who are allowed to change settings
 +admin_users = postgres
 +
 +; comma-separated list of users who are just allowed to use SHOW command
 +stats_users = stats, postgres
 +
 +;;;
 +;;; 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
 +pool_mode = transaction
 +
 +;
 +; Query for cleaning connection immediately after releasing from client.
 +; No need to put ROLLBACK here, pgbouncer does not reuse connections
 +; where transaction is left open.
 +;
 +; Query for 8.3+:
 +;   DISCARD ALL;
 +;
 +; Older versions:
 +;   RESET ALL; SET SESSION AUTHORIZATION DEFAULT
 +;
 +; Empty if transaction pooling is in use.
 +;
 +server_reset_query = DISCARD ALL
 +
 +;
 +; Comma-separated list of parameters to ignore when given
 +; in startup packet.  Newer JDBC versions require the
 +; extra_float_digits here.
 +;
 +;ignore_startup_parameters = extra_float_digits
 +
 +;
 +; When taking idle server into use, this query is ran first.
 +;   SELECT 1
 +;
 +;server_check_query = select 1
 +
 +; If server was used more recently that this many seconds ago,
 +; skip the check query.  Value 0 may or may not run in immediately.
 +;server_check_delay = 30
 +
 +;;;
 +;;; Connection limits
 +;;;
 +
 +; total number of clients that can connect
 +;max_client_conn = 100
 +max_client_conn = 1000
 +
 +; default pool size.  20 is good number when transaction pooling
 +; is in use, in session pooling it needs to be the number of
 +; max clients you want to handle at any moment
 +;default_pool_size = 20
 +default_pool_size = 100
 +
 +; how many additional connection to allow in case of trouble
 +;reserve_pool_size = 5
 +
 +; if a clients needs to wait more than this many seconds, use reserve pool
 +;reserve_pool_timeout = 3
 +
 +; log if client connects or server connection is made
 +;log_connections = 1
 +
 +; log if and why connection was closed
 +;log_disconnections = 1
 +
 +; log error messages pooler sends to clients
 +;log_pooler_errors = 1
 +
 +
 +; If off, then server connections are reused in LIFO manner
 +;server_round_robin = 0
 +
 +;;;
 +;;; Timeouts
 +;;;
 +
 +;; Close server connection if its been connected longer.
 +;server_lifetime = 1200
 +
 +;; Close server connection if its not been used in this time.
 +;; Allows to clean unnecessary connections from pool after peak.
 +;server_idle_timeout = 60
 +
 +;; Cancel connection attempt 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 = 0
 +
 +;; Dangerous.  Client connection is closed if the query is not assigned
 +;; to a server in this time.  Should be used to limit the number of queued
 +;; queries in case of a database or network failure. (default: 0)
 +;query_wait_timeout = 0
 +
 +;; Dangerous.  Client connection is closed if no activity in this time.
 +;; Should be used to survive network problems. (default: 0)
 +;client_idle_timeout = 0
 +
 +;; Disconnect clients who have not managed to log in after connecting
 +;; in this many seconds.
 +;client_login_timeout = 60
 +
 +;; Clean automatically created database entries (via "*") if they
 +;; stay unused in this many seconds.
 +; autodb_idle_timeout = 3600
 +
 +;;;
 +;;; Low-level tuning options
 +;;;
 +
 +;; buffer for streaming packets
 +;pkt_buf = 2048
 +
 +;; man 2 listen
 +;listen_backlog = 128
 +
 +;; networking options, for info: man 7 tcp
 +
 +;; Linux: notify program about new connection only if there
 +;; is also data received.  (Seconds to wait.)
 +;; On Linux the default is 45, on other OS'es 0.
 +;tcp_defer_accept = 0
 +
 +;; In-kernel buffer size (Linux default: 4096)
 +;tcp_socket_buffer = 0
 +
 +;; whether tcp keepalive should be turned on (0/1)
 +;tcp_keepalive = 1
 +
 +;; 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
 +
 +;; DNS lookup caching time
 +;dns_max_ttl = 15
 +
 +;; DNS zone SOA lookup period
 +;dns_zone_check_period = 0
 +</code>
 +  - サービスを起動する。<code>
 +$ sudo service pgbouncer start
 +</code>
 +===== PgBouncer の使い方 =====
 +バージョン確認
 +<code>
 +$ /usr/local/bin/pgbouncer -V
 +</code>
 +
 +コマンドラインからデーモンとして起動
 +<code>
 +$ sudo /usr/local/bin/pgbouncer -d /etc/pgbouncer.ini
 +</code>
 +
 +オンラインのまま再起動
 +<code>
 +$ sudo /usr/local/bin/pgbouncer -R -d /etc/pgbouncer.ini
 +</code>
 ===== 参考文献 ===== ===== 参考文献 =====
 [[http://postgres.sios.com/modules/newbb/viewtopic.php?topic_id=149&forum=1|Postgres Plus ユーザーサイト - FAQ]]\\ [[http://postgres.sios.com/modules/newbb/viewtopic.php?topic_id=149&forum=1|Postgres Plus ユーザーサイト - FAQ]]\\
 +[[http://stansantiago.wordpress.com/2011/10/22/pgbounce-installation/|Pgbouncer installation « Stan Santiago's blog]]\\
 +[[http://geekery.blog.com/2011/05/08/build-libevent-and-transmission-on-rhelcentos-5-x/|build libevent and transmission on RHEL/CentOS 5.x | geekery]]\\
 +[[http://libevent.org/|libevent]]\\
 +[[http://pgfoundry.org/frs/?group_id=1000258|PgFoundry: PgBouncer: ファイルリスト]]\\
 +[[http://blog.livedoor.jp/horahuto/archives/4487570.html|pgbouncerのインストール : 個人的なメモ帳]]\\
 +
  • linux/pgpouncer.1352286212.txt.gz
  • 最終更新: 2019/05/18 02:23
  • (外部編集)