#title pgbouncer
[[TableOfContents]]

{{{PgBouncer is a lightweight connection pooler for PostgreSQL.}}}

==== 서버 정보 ====
 * 192.168.56.101 --> pgbouncer1.6
 * 192.168.56.102 --> postgresql9.4, pg_hba.conf 파일에 192.168.56.102 접속 허용

==== 설치 ====
{{{
rpm -Uvh http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-1.noarch.rpm
yum update
yum install pgbouncer
}}}

==== 방화벽 오픈 ====
{{{
firewall-cmd --permanent --add-port=6543/tcp
firewall-cmd --reload
}}}

==== DB 및 테이블 생성 ====
192.168.56.102서버
{{{
psql -x -c "CREATE DATABASE sample WITH ENCODING='UTF8' CONNECTION LIMIT=-1;" 
psql -x -d sample -c "create table test(id int)" 
psql -x -d sample -c "insert into test(id) values(1)"
psql -x -d sample -c "select * from test" 
}}}

==== config ====
{{{
vi /etc/pgbouncer/config.ini
}}}

{{{
[databases]
db1 = host=192.168.56.102 port=5432 dbname=sample user=postgres password=9999

[pgbouncer]
pool_mode = session
listen_port = 6543
listen_addr = *
auth_type = md5
auth_file = /etc/pgbouncer/users.txt
logfile = pgbouncer.log
pidfile = pgbouncer.pid
admin_users = postgres
}}}

==== 패스워드 md5 ====
sql
{{{
select 'md5' || md5 ('9999' || 'postgres');
}}}

shell script
{{{
user=postgres; passwd=9999; echo -n md5; echo $passwd$user | md5sum
}}}
==== users.txt ====
{{{
vi /etc/pgbouncer/users.txt
}}}

{{{
"postgres" "md59b9579bc6327c1a529d21c8a011e552d"
}}}

==== 실행 ====
{{{
su - postgres
pgbouncer -d /etc/pgbouncer/config.ini -R
}}}

==== 192.168.56.1에서 접속 테스트 ====
{{{
psql -h 192.168.56.101 -p 6543 -d db1 -U postgres
}}}
attachment:pgbouncer/pgbouncer_test.png