linux:pgpouncer

差分

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

この比較画面へのリンク

両方とも前のリビジョン 前のリビジョン
次のリビジョン		 前のリビジョン
linux:pgpouncer [2012/11/07 20:29] – [PgBouncer のインストール] ともやんlinux:pgpouncer [2019/05/18 02:23] (現在) – 外部編集 非ログインユーザー
行 6: 行 6:
 $ mkdir ~/work $ mkdir ~/work
 $ cd ~/work $ cd ~/work
-$ curl -L -O https://github.com/downloads/libevent/libevent/libevent-2.0.20-stable.tar.gz+$ 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
行 30: 行 30:
 $ mkdir ~/work $ mkdir ~/work
 $ cd ~/work $ cd ~/work
-$ curl -O http://pgfoundry.org/frs/download.php/3369/pgbouncer-1.5.3.tar.gz+$ curl -O http://pgfoundry.org/frs/download.php/3393/pgbouncer-1.5.4.tar.gz
 </code> </code>
   - ファイルを解凍する。<code>   - ファイルを解凍する。<code>
-$ tar zxvf pgbouncer-1.5.3.tar.gz+$ tar zxvf pgbouncer-1.5.4.tar.gz
 </code> </code>
   - PgBouncer のビルドとインストールを行う。<code>   - PgBouncer のビルドとインストールを行う。<code>
-$ cd pgbouncer-1.5.3+$ cd pgbouncer-1.5.4
 $ ./configure --prefix=/usr/local --with-libevent=/usr/local $ ./configure --prefix=/usr/local --with-libevent=/usr/local
 $ make $ make
行 48: 行 48:
 $ sudo useradd pgbouncer $ sudo useradd pgbouncer
 </code> </code>
-  - オーナーとパーミッションの設定を行う。<code>+  - オーナーを設定する。<code>
 $ sudo chown pgbouncer:pgbouncer /var/log/pgbouncer $ sudo chown pgbouncer:pgbouncer /var/log/pgbouncer
 $ sudo chown pgbouncer:pgbouncer /var/run/pgbouncer $ sudo chown pgbouncer:pgbouncer /var/run/pgbouncer
行 54: 行 54:
   - rc スクリプトを作成する。<code>   - rc スクリプトを作成する。<code>
 $ sudo vi /etc/rc.d/init.d/pgbouncer $ sudo vi /etc/rc.d/init.d/pgbouncer
-</code><sxh bash>+</code><code>
 #!/bin/sh #!/bin/sh
 # pgbouncer    This is the init script for starting up the pgbouncer # pgbouncer    This is the init script for starting up the pgbouncer
行 232: 行 232:
  
 exit $script_result exit $script_result
-</sxh>+</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.1352287782.txt.gz
  • 最終更新: 2019/05/18 02:23
  • (外部編集)