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 <&-"
exit 0;
) 2>&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