PGPool II and PostgreSQL 9.5 on Centos 7

README.md

PGPool II and PostgreSQL 9.5 on Centos 7

About the virtual machines and network details, see this blog post

Instalation

Run on both servers:

yum install postgresql95-server postgresql95-contrib pgpool-II-95
systemctl enable postgresql-9.5

Setup Hotstandby

on master

/usr/pgsql-9.5/bin/postgresql95-setup initdb

Edit the pg_hba.conf (under /var/lib/pgsql/9.5/data directory) and enable localhost access and remote access for our slave server, replacing this:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            ident
#host    replication     postgres        ::1/128                 ident

for:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust
#### replication stuff
host    replication     dbreplication   192.168.100.200/32      md5

Edit the postgresql.conf (under /var/lib/pgsql/9.5/data directory) and change:

listen_addresses = '*'
wal_level = 'hot_standby'
max_wal_senders = 6

Now start the server...

systemctl start postgresql-9.5

...and add the new replication user (using psql command line utility):

CREATE ROLE dbreplication LOGIN REPLICATION PASSWORD 'testpassword';

on slave

Change to postgres user and run

su - postgres
pg_basebackup --pgdata=/var/lib/pgsql/9.5/data --write-recovery-conf -U dbreplication -h 192.168.100.100 --progress

Edit the postgresql.conf (under /var/lib/pgsql/9.5/data directory) and change:

hot_standby = on
hot_standby_feedback = on

Then start the server:

systemctl start postgresql-9.5

PGPool configuration

on master

Copy the sample file:

cp /etc/pgpool-II-95/pgpool.conf.sample-stream /etc/pgpool-II-95/pgpool.conf

Then edit the pgpool.conf file:

listen_addresses = '*'
health_check_period = 5
health_check_timeout = 0
health_check_user = 'postgres'

use_watchdog = on
wd_interval = 3

delegate_IP = '192.168.100.250'

if_up_cmd = 'ip addr add $_IP_$/24 dev eth1 label eth1:pool'
if_down_cmd = 'ip addr del $_IP_$/24 dev eth1'

master server pgpool.conf specifics

wd_hostname = '192.168.100.100'

other_pgpool_hostname0 = '192.168.100.200'
other_pgpool_port0 = 9999
other_wd_port0 = 9000

slave server pgpool.conf specifics

wd_hostname = '192.168.100.200'

other_pgpool_hostname0 = '192.168.100.100'
other_pgpool_port0 = 9999
other_wd_port0 = 9000

References

• PGPool Watchdog tutorial

Can you explain to me? what is IP for master and slave server?

About the virtual machines and network details, see this blog post <--- This broken link, could you update this link? Thanks before