#title pgpool-II
[[TableOfContents]]

작성중...

==== 순서 ====
 * postgresql, pgpool-II 설치
 * replication 설정
 * pgpool-II 설정

==== server ====
 * os: centos7
 * pgpool : 192.168.56.101
 * db1: 192.168.56.102 --> master
 * db2: 192.168.56.103 --> slave

attachment:pgpool-II/pgpool2.png
==== pgpool-II install ====
192.168.56.101 서버에 install
{{{
rpm -Uvh http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-1.noarch.rpm
yum update
yum install pgpool-II-pg94
}}}

==== postgresql9.4 install ====
192.168.56.102, 192.168.56.103 서버에 install
{{{
rpm -Uvh http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-1.noarch.rpm
yum update
yum install postgresql94-server postgresql94-contrib


/usr/pgsql-9.4/bin/postgresql94-setup initdb


systemctl enable postgresql-9.4
systemctl start postgresql-9.4


firewall-cmd --permanent --add-port=5432/tcp
firewall-cmd --permanent --add-port=80/tcp
firewall-cmd --reload


setsebool -P httpd_can_network_connect_db 1

su - postgres
psql
\password postgres 
}}}

==== master 설정 ====
.conf 파일이 있는 디렉토리로 이동
{{{
#locate pg_hba.conf
#/var/lib/pgsql/9.4/data/pg_hba.conf

cd /var/lib/pgsql/9.4/data/
}}}

pg_hba.conf파일에 replication 서버ip 등록
{{
vi pg_hba.conf
}}}

{{{
host    replication     postgres        192.168.56.102/32          trust
host    replication     postgres        192.168.56.103/32          trust
host    all             all             192.168.56.101/32       trust
host    all             all             192.168.56.1/32         trust
}}}


postgesql.conf 수정
{{{
vi postgresql.conf
}}}

{{{
wal_level=host_standby #archive로 설정하면 slave에 접근이 안됨
max_wal_senders=10 #multi slave node 수 설정
listen_addresses = '*'
archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/9.4/ARCHIVE/%f'
# synchronous_standby_names = 'slave1' # 동기방식으로 복제할 경우
}}}

archive 로그 디렉토리 생성
{{{
mkdir /var/lib/pgsql/9.4/ARCHIVE
}}}

권한 변경
{{{
chown -R postgres.postgres /var/lib/pgsql
}}}


postgresql restart
{{{
su - postgres
cd /usr/pgsql-9.4/bin

./pg_ctl -D /var/lib/pgsql/9.4/data stop -m fast
./pg_ctl start -l /var/lib/pgsql/9.4/data/pg_log/postgres.log -D /var/lib/pgsql/9.4/data
}}}

or

{{{
systemctl restart postgresql-9.4
systemctl stop postgresql-9.4
}}}

==== slave 설정 ====
master 복사
{{{
cd /
systemctl stop postgresql-9.4
mv /var/lib/pgsql/9.4/data /var/lib/pgsql/9.4/data_old

su - postgres
pg_basebackup -h 192.168.56.102 -p 5432 -U postgres -D /var/lib/pgsql/9.4/data --xlog --checkpoint=fast --progress
ls /var/lib/pgsql/9.4/data
exit
}}}

postgesql.conf 수정
{{{

cd /var/lib/pgsql/9.4/data/
vi postgresql.conf
}}}

{{{
wal_level = minimal
hot_standby = on
#archive_mode = on
#archive_command = 'cp %p /var/lib/pgsql/9.4/ARCHIVE/%f'
}}}

recovery.conf 생성
{{{
cd /var/lib/pgsql/9.4/data/
vi recovery.conf
}}}

{{{
restore_command = 'cp /var/lib/pgsql/9.4/ARCHIVE/%f %p'
archive_cleanup_command = 'pg_archivecleanup /var/lib/pgsql/9.4/ARCHIVE/ %r'
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=192.168.56.102 port=5432'
#application_name=slave1' # 동기방식으로 복제할 경우
}}}

권한 변경
{{{
chown -R postgres.postgres /var/lib/pgsql
}}}

postgresql restart
{{{
systemctl restart postgresql-9.4
}}}

==== 동기화 확인 ====
master 서버에서..
{{{
su - postgres
psql -x -c "create table replication_test(id int)"
psql -x -c "insert into replication_test(id) values(1)"
psql -x -c "select * from replication_test"
}}}

slave 서버에서..
{{{
su - postgres
psql -x -c "select * from replication_test"
}}}

master 서버에서..
{{{
su - postgres
psql -x -c "Select * From pg_stat_replication"
}}}


==== pgpool-II 설정 ====

==== 테스트 ====

==== 참고 ====
 * http://cutewebi.tistory.com/727
 * http://www.unixmen.com/postgresql-9-4-released-install-centos-7/ 
 * http://avicom.tistory.com/94
 * http://splee75.tistory.com/104