Ubuntu 16.04 + PGPOOL 3.5.3 + PostgreSQL9.5.3

System Environment

OS = Ubuntu14.04

Database = PostgreSQL 9.4.4

PGpool = pgpool-II-3.4.3

PGpoolAdmin = pgpoolAdmin-3.4.1

Master (HOST1) 192.168.1.31
Install Ubuntu 14.04
เลือก ssh
path = /hospitalos
# nano /etc/hosts
192.168.1.30 PGPOOL
192.168.1.31 HOST1
192.168.1.32 HOST2
192.168.1.30 HOST3

Install PostgreSQL-9.4
# groupadd -r -g 62 postgres
# useradd -r -u 62 -M -d /hospitalos/PostreSQL/9.4/data/ -N -g postgres postgres
# ./postgresql-9.4.4–3-linux-x64.run
/hospitalos/PostgerSQL/9.4
# cp postgersql.conf /hospitalos/PostgreSQL/9.4/data
# cp pg_hba.conf /hospitalos/PostgreSQL/9.4/data

Install Path
# /etc/environment
/hospitalos/PostgreSQL/9.4/bin

Install Function pgpool
# cd pgpool-recovery
# make
# make install
# cd pgpool-reclass
# make
# make install
# su — postgres
# psql -f pgpool-recovery.sql postgres
# psql -f pgpool-recovery.sql template1

ssh
# ssh-keygen
# cat .ssh/id_rsa.pub > .ssh/authorized_keys
# su — postgres
# ssh HOST2 ls

created Database
# createdb hospitalos
# psql hospitalos < hospitals.sql

install online-recovery
# cp basebackup.sh /hospitalos/PostgreSQL/9.4/data/
# cp pgpool_remote_start /hospitalos/PostgreSQL/9.4/data/
# chmod 755 basebackup.sh
# chmod 755 pgpool_remote_start
# mkdir /var/log/pgpool/trigger
# chown postgres /var/log/pgpool/trigger
# mkdir /var/log/pgpool/trigger
# chown postgres /var/log/pgpool/trigger

Replicate to Slave
# su — postgres

# rsync -cva — inplace /hospitalos/PostgreSQL/9.4/data/ HOST2:/hospitalos/PostgreSQL/9.4/data/

#

rsync -cva — inplace /opt/PostgreSQL/9.5/data/ Host1:/opt/PostgreSQL/9.5/data/

pg_basebackup -v -D main -R -P -h 10.1.10.150 -p 5433 -U replication

Slave (HOST2) 192.168.1.32
Install Ubuntu 14.04
เลือก ssh package
# nano /etc/hosts
192.168.1.30 PGPOOL
192.168.1.31 HOST1
192.168.1.32 HOST2
192.168.1.30 HOST3

Install PostgreSQL-9.4
# ./postgresql-9.4.4–3-linux-x64.run
/hospitalos/PostgerSQL/9.4

Install Path
# /etc/environment
/hospitalos/PostgreSQL/9.4/bin

install online-recovery
# mkdir /var/log/pgpool/trigger
# chown postgres /var/log/pgpool/trigger

ssh
# ssh-keygen
# cat .ssh/id_rsa.pub > .ssh/authorized_keys
# su — postgres
ssh HOST1 ls
ssh HOST3 ls

Slave (HOST3) 192.168.1.33
Install Ubuntu 14.04
เลือก ssh package

# nano /etc/hosts
192.168.1.30 PGPOOL
192.168.1.31 HOST1
192.168.1.32 HOST2
192.168.1.30 HOST3

Install PostgreSQL-9.4
# groupadd -r -g 62 postgres

# useradd -r -u 62 -M -d /hospitalos/PostreSQL/9.4/data/ -N -g postgres postgres

# ./postgresql-9.4.4–3-linux-x64.run

/hospitalos/PostgerSQL/9.4

Install Path
# /etc/environment
/hospitalos/PostgreSQL/9.4/bin

install online-recovery
# mkdir /var/log/pgpool/trigger
# chown postgres /var/log/pgpool/trigger

ssh
# ssh-keygen
# cat .ssh/id_rsa.pub > .ssh/authorized_keys
# su — Postgres
ssh HOST1 ls
ssh HOST2 ls

PGpool (PGPOOL) 192.168.1.30
Install Ubuntu 14.04
เลือก ssh package
# nano /etc/hosts
192.168.1.30 PGPOOL
192.168.1.31 HOST1
192.168.1.32 HOST2
192.168.1.30 HOST3

Install PostgreSQL-9.4
# groupadd -r -g 62 postgres
# useradd -r -u 62 -M -d /hospitalos/PostreSQL/9.4/data/ -N -g postgres postgres
# ./postgresql-9.4.4–3-linux-x64.run
/hospitalos/PostgerSQL/9.4
# cp postgersql.conf.pgpool /hospitalos/PostgreSQL/9.4/data

Install Path
# /etc/environment
/hospitalos/PostgreSQL/9.4/bin

Install Apache + php5 +pgp-pgsql
# apt-get install lamp-server^
# apt-get install php5-pgsql

Install pgpool-II-3.4.3.tar.gz
# tar xf pgpool-II-3.4.3.tar.gz

# cd pgpool-II-3.4.3

# apt-get install build-essential

apt-get install libpq-dev make

# apt-get install libpq

# ./configure — with-psql-libdir=/hospitalos/PostgreSQL/9.4/lib

/configure — with-psql-libdir=/opt/PostgreSQL/9.5/lib

# make
# make install
# cp * pool_conf/* /usr/local/etc/

# tar xf pgpoolAdmin-3.4.1.tar.gz

# mv pgpoolAdmin-3.4.1 /var/www/html/pgpool

cd pgpool-II-x.x.x/sql/pgpool-recovery

$ make
$ make install

$ psql -f pgpool-recovery.sql template1

$ cd pgpool-II-x.x.x/sql/pgpool-regclass
$ make

$ make install

$ psql -f pgpool-regclass.sql template1

ex)

$ cat >> /usr/local/pgsql/postgresql.conf

pgpool.pg_ctl = ‘/usr/local/pgsql/bin/pg_ctl’

pool_passwd

pg_md5 -m -u postgres pa55w0rd

http://pgpool/pgpool/install

ssh
# ssh-keygen
# cat .ssh/id_rsa.pub > .ssh/authorized_keys
ssh postgres@HOST1 ls
ssh postgres@HOST2 ls
# scp -r /root/.ssh /var/www/html
# scp -r /root/.ssh HOST1:/hospitalos/PostgreSQL/9.4/data/
# scp -r /root/.ssh HOST2:/hospitalos/PostgreSQL/9.4/data/

# mkdir /var/run/pgpool
# chown apache /var/run/pgpool
# mkdir /var/log/pgpool
# chown apache /var/log/pgpool

วิธีใช้งาน
Start PGpool ที่หน้า web
โปรแกรม Hosppitalos Connect ไปที่
host = 192.168.1.30
port = 5432
user = postgres
pass = postgres

วิธีการ ถ้า Host Down
ถ้า Host Down ให้กดปุ่ม recovery เท่านั้น ไม่ต้องไป start postgres ที่เครื่อง server
ถ้ามีการติดตั้ง Postgresql ใหม่หรือ add node เพิ่ม ให้ rsync -cva data มาจากเครื่อง Primary แล้วค่อยกดปุ่ม recovery

ที่มา http://www.pgpool.net/pgpool-web/contrib_docs/simple_sr_setting2_3.3/index.html
http://www.keyup.eu/en/blog/89-replication-and-load-balancing-with-postgresql-and-pgpool2
http://jensd.be/591/linux/setup-a-redundant-postgresql-database-with-repmgr-and-pgpool
http://linux.xvx.cz/2014/10/loadbalancing-of-postgresql-databases.html
http://raghurc.blogspot.com/2012/03/pgpool-ii-configuration-with-load.html
https://sonnguyen.ws/replication-master-slave-with-postgresql-9-4-in-ubuntu-14-04/