Setting up PostGresql Replication with Loadbalancing with PGPOOL II

Installation (on both Master and Slave Nodes):


yum install https://yum.postgresql.org/9.5/redhat/rhel-7-x86_64/pgdg-centos95-9.5-3.noarch.rpm 
yum install postgresql95 postgresql95-server postgresql-libs postgresql95-contrib rsync

Initialize the database and configure postgresql


/usr/pgsql-9.5/bin/postgresql95-setup initdb cd /var/lib/pgsql/9.5/data cp postgresql.conf postgresql.conf.orig chown postgres postgresql.conf.orig

On Master Node:

systemctl start postgresql-9.5 su - postgres psql create user repuser replication; \du+ (TO check if the replication user was created with right rule) \q

Configure Database Access:

Open pg_hba.conf for editing

vi /var/lib/pgsql/9.5/data/pg_hba.conf

If Doing Replication Manager:

#TYPE  DATABASE USER ADDRESS METHOD
host    repmgr          repmgr      192.168.202.101/32  trust
host    replication     repmgr      192.168.202.101/32  trust
host    repmgr          repmgr      192.168.202.102/32  trust
host    replication     repmgr      192.168.202.102/32  trust
host    all             pgpool      192.168.202.103/32  trust

Else if Using PGPOOL :

host all pgpool 192.168.100.0/24 trust host all pgpool ::1/128 trust host all pgpool 127.0.0.1/32 trust host replication repuser 192.168.100.43/32 trust host all all 192.168.100.0/24 md5

Else ( Plain Postgresql Replication)

host replication repuser 192.168.100.0/24 trust

Configure PostgreSQL

Open postgresql.conf for editing:

vi postgresql.conf

listen_addresses = '*'
port = 5432 
#write ahead log configuration (wal means write ahead logging, changes to data files must be written only after those changes have been logged)
wal_level=hot_standby
fsync = on
wal_sync_method = open_sync
synchronous_commit = on
wal_keep_segments = 100 #(default size 16M , so 1600M) 
#replication
max_wal_senders = 1    #no of slave servers
synchronous_standby_names = 'postgresqlslave' # Enables synchronous replication. Will be used on slave instance configuration, can use anything
#General
max_connections = 100                   # (change requires restart)
shared_buffers = 128MB                  # min 128kB
dynamic_shared_memory_type = posix      # the default is the first option
log_destination = 'stderr'              # Valid values are combinations of
logging_collector = on                  # Enable capturing of stderr and csvlog
log_directory = 'pg_log'                # directory where log files are written,
log_filename = 'postgresql-%a.log'      # log file name pattern,
log_truncate_on_rotation = on           # If on, an existing log file with the
log_rotation_age = 1d                   # Automatic rotation of logfiles will
log_rotation_size = 0                   # Automatic rotation of logfiles will
log_line_prefix = '< %m >'                      # special values:
log_timezone = 'US/Eastern'
datestyle = 'iso, mdy'
timezone = 'US/Eastern'
lc_messages = 'en_US.UTF-8'                     # locale for system error message
lc_monetary = 'en_US.UTF-8'                     # locale for monetary formatting
lc_numeric = 'en_US.UTF-8'                      # locale for number formatting
lc_time = 'en_US.UTF-8'                         # locale for time formatting
default_text_search_config = 'pg_catalog.english'

On the Slave Node:

systemctl stop postgresql-9.5
su - postgres

cd /var/lib/pgsql/9.5/data/
rm -rf *
pg_basebackup -D /var/lib/pgsql/9.5/data -h 192.168.100.57 -U repuser

vi recovery.conf #(provides information for replication, application_name is the synchronous_standby_names configured on master)
standby_mode=on
trigger_file='/tmp/promotedb' #(If this file exists the server will stop being a replication server and start being a primary server)
primary_conninfo='host=192.168.100.57 port=5432 user=repuser application_name=postgresqlslave'

Now enable Slave to accept and read queries

vi /var/lib/pgsql/9.5/data/postgresql.conf
hot_standby=on
hot_standby_feedback = on

Restart Services and Set to auto start on boot:

systemctl restart postgresql-9.5
systemctl enable postgresql-9.5

CTRL+d systemctl restart postgresql-9.5

Install PGPOOL :

yum install postgresql95 pgpool-II-95 -y
cd /etc/pgpool-II-95
cp /etc/pgpool-II-95/pgpool.conf.sample-stream /etc/pgpool-II-95/pgpool.conf


sudo su - postgres 
psql
CREATE ROLE pgpool SUPERUSER CREATEDB CREATEROLE INHERIT REPLICATION LOGIN ENCRYPTED PASSWORD 'secret';
\q
exit 

Configure pgpool configuration file (Note: Edit the settings , do not replace.

vi /etc/pgpool-II-95/pgpool.conf 

listen_addresses = '*'
port = 5432
backend_hostname0 = 'pgdb1'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/9.5/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = 'pgdb2'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/9.5/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
enable_pool_hba = on
pid_file_name = '/var/run/pgpool-II-95/pgpool.pid'
sr_check_user = 'pgpool'
sr_check_password = 'secret'
health_check_period = 10
health_check_user = 'pgpool'
health_check_password = 'secret'
failover_command = '/etc/pgpool-II-95/failover.sh %d %H'  
recovery_user = 'pgpool'
recovery_password = 'secret'
recovery_1st_stage_command = 'basebackup.sh'

Or if you would like to replace the whole file ( make sure you update your variables/settings)

listen_addresses = '*'
port = 9999
socket_dir = '/tmp'
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/tmp'
listen_backlog_multiplier = 2
serialize_accept = off
backend_hostname0 = 'localhost'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/9.5/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
enable_pool_hba = on
pool_passwd = 'pool_passwd'
authentication_timeout = 60
ssl = off
num_init_children = 32
max_pool = 4
child_life_time = 300
child_max_connections = 0
connection_life_time = 0
client_idle_limit = 0
log_destination = 'syslog'
log_line_prefix = '%t: pid %p: '   # printf-style string to output at beginning of each log line.
log_connections = off
log_hostname = off
log_statement = off
log_per_node_statement = off
log_standby_delay = 'if_over_threshold'
syslog_facility = 'LOCAL0'
syslog_ident = 'pgpool'
debug_level = 0
pid_file_name = '/var/run/pgpool-II-95/pgpool.pid'
logdir = '/tmp'
connection_cache = on
reset_query_list = 'ABORT; DISCARD ALL'
replication_mode = off
replicate_select = off
insert_lock = off
lobj_lock_table = ''
replication_stop_on_mismatch = off
failover_if_affected_tuples_mismatch = off
load_balance_mode = on
ignore_leading_white_space = on
white_function_list = ''
black_function_list = 'currval,lastval,nextval,setval'
database_redirect_preference_list = ''
app_name_redirect_preference_list = ''
allow_sql_comments = off
master_slave_mode = on
master_slave_sub_mode = 'stream'
sr_check_period = 10
sr_check_user = 'pgpool'
sr_check_password = 'secret'
sr_check_database = 'postgres'
delay_threshold = 10000000
follow_master_command = ''
health_check_period = 10
health_check_timeout = 20
health_check_user = 'pgpool'
health_check_password = 'secret'
health_check_database = ''
health_check_max_retries = 0
health_check_retry_delay = 1
connect_timeout = 10000
failover_command = ''
failback_command = ''
fail_over_on_backend_error = on
search_primary_node_timeout = 300
recovery_user = 'pgpool'
recovery_password = 'secret'
recovery_1st_stage_command = ''
recovery_2nd_stage_command = ''
recovery_timeout = 90
client_idle_limit_in_recovery = 0
use_watchdog = off
trusted_servers = ''
ping_path = '/bin'
wd_hostname = ''
wd_port = 9000
wd_priority = 1
wd_authkey = ''
wd_ipc_socket_dir = '/tmp'
delegate_IP = ''
if_cmd_path = '/sbin'
if_up_cmd = 'ip addr add $_IP_$/24 dev eth0 label eth0:0'
if_down_cmd = 'ip addr del $_IP_$/24 dev eth0'
arping_path = '/usr/sbin'
arping_cmd = 'arping -U $_IP_$ -w 1'
clear_memqcache_on_escalation = on
wd_escalation_command = ''
wd_de_escalation_command = ''
wd_monitoring_interfaces_list = ''  # Comma separated list of interfaces names to monitor.
wd_lifecheck_method = 'heartbeat'
wd_interval = 10
wd_heartbeat_port = 9694
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
heartbeat_destination0 = 'host0_ip1'
heartbeat_destination_port0 = 9694
heartbeat_device0 = ''
wd_life_point = 3
wd_lifecheck_query = 'SELECT 1'
wd_lifecheck_dbname = 'template1'
wd_lifecheck_user = 'nobody'
wd_lifecheck_password = ''
relcache_expire = 0
relcache_size = 256
check_temp_table = on
check_unlogged_table = on
memory_cache_enabled = off
memqcache_method = 'shmem'
memqcache_memcached_host = 'localhost'
memqcache_memcached_port = 11211
memqcache_total_size = 67108864
memqcache_max_num_cache = 1000000
memqcache_expire = 0
memqcache_auto_cache_invalidation = on
memqcache_maxcache = 409600
memqcache_cache_block_size = 1048576
memqcache_oiddir = '/var/log/pgpool/oiddir'
white_memqcache_table_list = ''
black_memqcache_table_list = ''

If you would like pgpool to trigger failover script when failover happens :

vi /etc/pgpool-II-94/failover.sh:

#!/bin/sh
failed_node=$1
new_master=$2
(
date
echo "Failed node: $failed_node"
set -x
/usr/bin/ssh -T -l postgres $new_master "/usr/pgsql-9.4/bin/repmgr -f /var/lib/pgsql/repmgr/repmgr.conf standby promote 2>/dev/null 1>/dev/null <&-" 2 exit 0; )>&1 | tee -a /tmp/pgpool_failover.log

chmod 755 /etc/pgpool-II-94/failover.sh
    

We also specified in the configuration that we want to use pool_hba.conf so we need to create this file in /etc/pgpool-II-94/pool_hba.conf:

cp pool_hba.conf.sample pool_hba.conf

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
local   all         all                               trust
host    all         all         127.0.0.1/32          trust
host    all         all         ::1/128               trust
host    all         all         0.0.0.0/0             md5
    

Every user that needs to connect via pgpool needs to be added in pool_passwd. First we need to create this file and let it be owned by postgres:

touch /etc/pgpool-II-95/pool_passwd
chown postgres:postgres /etc/pgpool-II-95/pool_passwd

Add users to file :

su - postgres -c "pg_md5 -m -u pgpool secret"
    

Now we need to allow connection via PCP to manage the pgpool.

echo "pgpool:$(pg_md5 secret)"|sudo tee /etc/pgpool-II-95/pcp.conf pgpool:5ebe2294ecd0e0f08eab7690d2a6ee69
    

Now configure firewall if enabled

firewall-cmd --permanent --zone=public --add-service=postgresql systemctl reload firewalld

Configure Pgpool service to auto start on

systemctl enable pgpool-II-95 ( you can look for service name : /usr/lib/systemd/system/) systemctl start pgpool-II-95

Test PGPOOL :

psql -U pgpool --host pgpool --dbname postgres -c "\list"

To get status of nodes connected to pgpool :

psql -U pgpool --dbname=postgres --host pgpool -c "show pool_nodes"

Test Failover:

systemctl stop postgresql-9.5 psql -U pgpool --dbname=postgres --host pgpool -c "show pool_nodes"

Now you see former has become primary and other has become standby

For pgpool, we need to re-attach the failed node in order for it to be visible and usable as a standby-node:

pcp_detach_node 0 localhost 9898 pgpool secret 0

At this point, we’re back in a redundant status where the old standby (pgdb2) functions as the primary and the old primary (pgdb1) functions as a standby. If both machines are equal, you can leave the situation as is and continue to use it in this way.

Recover to the original situation

When the master (pgdb2) stopped, a failover was triggered and pgdb1 gets assigned as primary again:

psql -U pgpool --dbname=postgres --host pgpool -c "show pool_nodes"

Now, sync pgdb2 with the new primary (pgdb1) and start it:

su - postgres /usr/pgsql-9.4/bin/repmgr -D /var/lib/pgsql/9.4/data -d repmgr -p 5432 -U repmgr -R postgres --force standby clone pgdb1 systemctl start postgresql-9.5

Reattach another server as slave:

pcp_detach_node 1 localhost 9898 pgpool secret 1

Test Replication:::

\l or \list 
\dt  all tables in current database

\connect database_name 


Create database:
create DATABASE test;

CREATE TABLE data(
      id serial primary key not null,
      time timestamp not null default CURRENT_TIMESTAMP,
      number integer not null
  );

insert into data(id,number) values(2,10);

Check on Slave:

\connect test;
SELECT * FROM data;

Extra Commands:

Applying changes on server:

pg_ctl -D /var/lib/pgsql/9.5/data reload
pg_ctl -D /var/lib/pgsql/9.5/data/ start

If any changes are made  to recovery file:

pg_ctl -D /var/lib/pgsql/data/ restart -m fast