PostgreSQL Connection Pool Pgbouncer installation steps detailed

Description
The Postgres is installed before Pgbouncer is installed

Software Download:
wget https://github.com/downloads/libevent/libevent/libevent-2.0.21-stable.tar.gz
wget http://pgfoundry.org/frs/download.php/3393/pgbouncer-1.5.4.tar.gz

Compile and install Libevent
Tar zxf libevent-2.0.21-stable.tar.gz
CD libevent-2.0.21-stable
./configure--prefix=/usr/local/libevent
Gmake
Gmake Install
Load libevent Dynamic Library
cd/etc/ld.so.conf.d/
Vim libevent2.0.21.conf
/usr/local/libevent/lib
Ldconfig

Configure Run environment variables
Vim/etc/profile
Path=/usr/local/pgbouncer/bin: $PATH
Export PATH
Implementation effective
Source/etc/profile


Switch to Postgres user, configure running environment
Su-postgres
mkdir/data/pgbouncer/-P

To configure a user password file:
Cd/data/pgbouncer
Vim User.txt
"Admin" "111111"
"User" "111111"
"Drfdai" "111111"
The first column is the username, the second column is the password, the connection Pgbouncer

Configuring Pgbouncer Parameters
[Databases]
write_db = host=192.168.1.240 port=5432 dbname=drfdai user=postgres password=postgres
read_db = host=192.168.1.241 port=5432 dbname=drfdai user=postgres password=postgres
[Pgbouncer]
Listen_port = 5433
LISTEN_ADDR = *
Auth_type = MD5
Auth_file =/data/pgbouncer/user.txt
LogFile =/data/pgbouncer/pgbouncer.log
Pidfile =/data/pgbouncer/pgbouncer.pid
Admin_users = Drfdai
Pool_mode = Session
Max_client_conn = 6000
Default_pool_size = 128

[Databases]
Configuring the Back-end PG Database Top
host= Database IP
port= Database Port
Dbname= database name
User= Database Account number
password= Database Password
[Pgbouncer]
Configuring Connection Pool Parameters
listen_port= Connection pool port, this port is required for user to connect
Listen_addr= allow connected ip,* to represent all IP
AUTH_TYPE=MD5 Verify the password of the account with MD5 method
auth_file= Storage account password for the file, refers to the connection pool to use the account password, the user connected in the use of
admin_users= user name to manage connection pooling
pool_mode= Specifies the pattern of the pool, which can have session,transaction,statement three modes
max_client_conn= allow maximum number of clients to connect to Pgbouncer
The default size of the default_pool_size= connection pool

If there is a connection pool backend with multiple databases, then configure multiple databases, I have two configured here, one is the main (write_db), one is from (read_db), read-write separation of

If the connected back-end database is not local, you need to open the remote allow connection pool access in the back-end database, otherwise the login fails

Pgbouncer Service Management
Detection profile
/usr/local/pgbouncer/bin/pgbouncer-v/data/pgbouncer/pgbouncer.ini

Start:
/usr/local/pgbouncer/bin/pgbouncer-d/data/pgbouncer/pgbouncer.ini
Reboot:
/usr/local/pgbouncer/bin/pgbouncer-r-d/data/pgbouncer/pgbouncer.ini
Stop it:
Psql-p 5433 pgbouncer-h 127.0.0.1-u drfdai-c ' shutdown '
Load configuration:
Psql-p 5433 pgbouncer-h 127.0.0.1-u drfdai-c ' reload '

Management Pgbouncer,
Login to Pgbouncer and run admin command
Management commands can be viewed by show help
pgbouncer=# Show help;
Notice:console usage
DETAIL:
Show help| Config| databases| pools| clients| servers| VERSION
Show stats| fds| sockets| active_sockets| lists| Mem
Show dns_hosts| Dns_zones
SET key = arg
RELOAD
PAUSE [<db>]
RESUME [<db>]
KILL <db>
SUSPEND
SHUTDOWN
Show

Connection test
Connecting to the WRITE_DB database
[Postgres@drfdai ~]$ psql-h 127.0.0.1-p 5433-u Drfdai
Password for user drfdai:
Psql (9.4.1)
Type ' help ' for help.

write_db=# \d
List of relations
Schema | Name |  Type | Owner
--------+--------+-------+----------
Public | Table1 | Table | Postgres
(1 row)

write_db=#

Connect Connection pool:
[Postgres@drfdai ~]$ psql-h 127.0.0.1-p 5433-u drfdai pgbouncer for user Password:
Psql (9.4.1, server 1.5.4/bouncer)
Type ' help ' for help.
pgbouncer=# Show Clients;
Type | user | Database |   State | Addr | Port | local_addr |    Local_port | C
Onnect_time |    Request_time | PTR | Link
------+--------+-----------+--------+-----------+-------+------------+------------+-----
----------------+---------------------+-----------+------
C | Drfdai | Pgbouncer | active | 127.0.0.1 | 38453 |       127.0.0.1 | 5433 | 2016
-01-06 09:46:16 | 2016-01-06 09:46:41 | 0x11ac550 |
(1 row)