差分
このページの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/ | + | $ curl -L -O https://sourceforge.net/projects/ |
</ | </ | ||
- ファイルを解凍する。< | - ファイルを解凍する。< | ||
- | $ tar zxvf libevent-2.0.20-stable.tar.gz | + | $ tar zxvf libevent-2.0.22-stable.tar.gz |
</ | </ | ||
- libevent のビルドとインストールを行う。< | - libevent のビルドとインストールを行う。< | ||
- | $ 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:// | + | $ curl -O http:// |
</ | </ | ||
- ファイルを解凍する。< | - ファイルを解凍する。< | ||
- | $ tar zxvf pgbouncer-1.5.3.tar.gz | + | $ tar zxvf pgbouncer-1.5.4.tar.gz |
</ | </ | ||
- PgBouncer のビルドとインストールを行う。< | - PgBouncer のビルドとインストールを行う。< | ||
- | $ cd pgbouncer-1.5.3 | + | $ cd pgbouncer-1.5.4 |
$ ./configure --prefix=/ | $ ./configure --prefix=/ | ||
$ make | $ make | ||
行 48: | 行 48: | ||
$ sudo useradd pgbouncer | $ sudo useradd pgbouncer | ||
</ | </ | ||
- | - オーナーとパーミッションの設定を行う。< | + | - オーナーを設定する。< |
$ sudo chown pgbouncer: | $ sudo chown pgbouncer: | ||
$ sudo chown pgbouncer: | $ sudo chown pgbouncer: | ||
行 54: | 行 54: | ||
- rc スクリプトを作成する。< | - rc スクリプトを作成する。< | ||
$ sudo vi / | $ sudo vi / | ||
- | </ | + | </ |
#!/bin/sh | #!/bin/sh | ||
# pgbouncer | # pgbouncer | ||
行 232: | 行 232: | ||
exit $script_result | exit $script_result | ||
- | </sxh> | + | </code> |
+ | - rc スクリプトのパーミッションを設定する。< | ||
+ | $ sudo chmod 755 / | ||
+ | </ | ||
+ | - 自動起動の設定を行う。< | ||
+ | $ sudo chkconfig pgbouncer on | ||
+ | </ | ||
+ | - デフォルト設定ファイルを作成して、設定ファイルとログファイルのパスを設定する。< | ||
+ | $ sudo vi / | ||
+ | </ | ||
+ | # | ||
+ | # pgbouncer defaults | ||
+ | # | ||
+ | |||
+ | # Path to the init file | ||
+ | BOUNCERCONF=/ | ||
+ | |||
+ | # Path to the log file | ||
+ | BOUNCERLOG=/ | ||
+ | </ | ||
+ | - の設定を行う。< | ||
+ | $ sudo vi / | ||
+ | </ | ||
+ | ;; database name = connect string | ||
+ | ;; | ||
+ | ;; connect string params: | ||
+ | ;; | ||
+ | ;; | ||
+ | ;; | ||
+ | [databases] | ||
+ | ;template1 = host=pgsql.mydomain.net dbname=template1 user=pgadmin password=pgadmin pool_size=1 | ||
+ | database_name1 | ||
+ | database_name2 | ||
+ | database_name3 | ||
+ | |||
+ | ; 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=' | ||
+ | |||
+ | ; use custom pool sizes | ||
+ | ; | ||
+ | |||
+ | ; fallback connect string | ||
+ | ;* = host=testserver | ||
+ | |||
+ | ;; Configuration section | ||
+ | [pgbouncer] | ||
+ | |||
+ | ;;; | ||
+ | ;;; Administrative settings | ||
+ | ;;; | ||
+ | |||
+ | logfile = / | ||
+ | pidfile = / | ||
+ | |||
+ | ;;; | ||
+ | ;;; 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 / | ||
+ | ; | ||
+ | ; | ||
+ | ; | ||
+ | |||
+ | ;;; | ||
+ | ;;; Authentication settings | ||
+ | ;;; | ||
+ | |||
+ | ; any, trust, plain, crypt, md5 | ||
+ | ;auth_type = trust | ||
+ | auth_type = any | ||
+ | ;auth_file = / | ||
+ | ;auth_file = / | ||
+ | |||
+ | ;;; | ||
+ | ;;; Users allowed into database ' | ||
+ | ;;; | ||
+ | |||
+ | ; 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: | ||
+ | ; | ||
+ | ; | ||
+ | ; | ||
+ | ;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+: | ||
+ | ; | ||
+ | ; | ||
+ | ; 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. | ||
+ | ; extra_float_digits here. | ||
+ | ; | ||
+ | ; | ||
+ | |||
+ | ; | ||
+ | ; When taking idle server into use, this query is ran first. | ||
+ | ; | ||
+ | ; | ||
+ | ; | ||
+ | |||
+ | ; If server was used more recently that this many seconds ago, | ||
+ | ; skip the check query. | ||
+ | ; | ||
+ | |||
+ | ;;; | ||
+ | ;;; Connection limits | ||
+ | ;;; | ||
+ | |||
+ | ; total number of clients that can connect | ||
+ | ; | ||
+ | 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 = 100 | ||
+ | |||
+ | ; how many additional connection to allow in case of trouble | ||
+ | ; | ||
+ | |||
+ | ; if a clients needs to wait more than this many seconds, use reserve pool | ||
+ | ; | ||
+ | |||
+ | ; log if client connects or server connection is made | ||
+ | ; | ||
+ | |||
+ | ; log if and why connection was closed | ||
+ | ; | ||
+ | |||
+ | ; log error messages pooler sends to clients | ||
+ | ; | ||
+ | |||
+ | |||
+ | ; If off, then server connections are reused in LIFO manner | ||
+ | ; | ||
+ | |||
+ | ;;; | ||
+ | ;;; Timeouts | ||
+ | ;;; | ||
+ | |||
+ | ;; Close server connection if its been connected longer. | ||
+ | ; | ||
+ | |||
+ | ;; Close server connection if its not been used in this time. | ||
+ | ;; Allows to clean unnecessary connections from pool after peak. | ||
+ | ; | ||
+ | |||
+ | ;; Cancel connection attempt if server does not answer takes longer. | ||
+ | ; | ||
+ | |||
+ | ;; If server login failed (server_connect_timeout or auth failure) | ||
+ | ;; then wait this many second. | ||
+ | ; | ||
+ | |||
+ | ;; Dangerous. | ||
+ | ;; in this time. Should be used to survive network problems, | ||
+ | ;; _not_ as statement_timeout. (default: 0) | ||
+ | ; | ||
+ | |||
+ | ;; Dangerous. | ||
+ | ;; 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) | ||
+ | ; | ||
+ | |||
+ | ;; Dangerous. | ||
+ | ;; Should be used to survive network problems. (default: 0) | ||
+ | ; | ||
+ | |||
+ | ;; Disconnect clients who have not managed to log in after connecting | ||
+ | ;; in this many seconds. | ||
+ | ; | ||
+ | |||
+ | ;; Clean automatically created database entries (via " | ||
+ | ;; stay unused in this many seconds. | ||
+ | ; autodb_idle_timeout = 3600 | ||
+ | |||
+ | ;;; | ||
+ | ;;; Low-level tuning options | ||
+ | ;;; | ||
+ | |||
+ | ;; buffer for streaming packets | ||
+ | ;pkt_buf = 2048 | ||
+ | |||
+ | ;; man 2 listen | ||
+ | ; | ||
+ | |||
+ | ;; networking options, for info: man 7 tcp | ||
+ | |||
+ | ;; Linux: notify program about new connection only if there | ||
+ | ;; is also data received. | ||
+ | ;; On Linux the default is 45, on other OS'es 0. | ||
+ | ; | ||
+ | |||
+ | ;; In-kernel buffer size (Linux default: 4096) | ||
+ | ; | ||
+ | |||
+ | ;; whether tcp keepalive should be turned on (0/1) | ||
+ | ; | ||
+ | |||
+ | ;; following options are Linux-specific. | ||
+ | ;; they also require tcp_keepalive=1 | ||
+ | |||
+ | ;; count of keepaliva packets | ||
+ | ; | ||
+ | |||
+ | ;; how long the connection can be idle, | ||
+ | ;; before sending keepalive packets | ||
+ | ; | ||
+ | |||
+ | ;; The time between individual keepalive probes. | ||
+ | ; | ||
+ | |||
+ | ;; DNS lookup caching time | ||
+ | ; | ||
+ | |||
+ | ;; DNS zone SOA lookup period | ||
+ | ; | ||
+ | </ | ||
+ | - サービスを起動する。< | ||
+ | $ sudo service pgbouncer start | ||
+ | </ | ||
+ | ===== PgBouncer の使い方 ===== | ||
+ | バージョン確認 | ||
+ | < | ||
+ | $ / | ||
+ | </ | ||
+ | |||
+ | コマンドラインからデーモンとして起動 | ||
+ | < | ||
+ | $ sudo / | ||
+ | </ | ||
+ | |||
+ | オンラインのまま再起動 | ||
+ | < | ||
+ | $ sudo / | ||
+ | </code> | ||
===== 参考文献 ===== | ===== 参考文献 ===== | ||
[[http:// | [[http:// | ||
+ | [[http:// | ||
+ | [[http:// | ||
+ | [[http:// | ||
+ | [[http:// | ||
+ | [[http:// | ||
+ |