Image

PostgreSQL in cluster usando PGPOOL-II

Installazione e configurazione di Postgres 9.6 con replicazione dei dati propria e PGPOOL-II-3.7 con Load Balancing e Watchdog in HA



Diagramma



  • Server Master "benz"
    192.168.1.14

  • Server Slave "renegade"
    192.168.1.15

  • IP Virtuale per l'accesso al cluster "pgpool-II"
    192.168.1.16

Disattivare selinux e configurare il file /etc/hosts

$ sudo su
# sed -i 's/SELINUX=.*/SELINUX=disabled/' /etc/sysconfig/selinux
# sed -i 's/SELINUX=.*/SELINUX=disabled/' /etc/selinux/config
# setenforce 0
# echo "192.168.1.14     benz" >> /etc/hosts
# echo "192.168.1.15     renegade" >> /etc/hosts
# echo "192.168.1.16     vip_cluster" >> /etc/hosts

Se sarà necessario è possibile disabilitare il servizio di firewall di CentOS7

# systemctl stop firewalld
# systemctl disable firewalld

Installare Postgresql 9.6 nei nodi Master "benz" e Slave "renegade":

# yum -y install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
# yum -y install postgresql96 postgresql96-server postgresql-libs postgresql96-contrib repmgr96 rsync net-tools vim

Inizializzare il database e configurare postgresql

# /usr/pgsql-9.6/bin/postgresql96-setup initdb

Abilitare ed lanciare PostgreSQL sui due servers "benz" e "renegade"

# systemctl start postgresql-9.6
# systemctl enable postgresql-9.6

Configurazione del server master "benz"

# cd /var/lib/pgsql/9.6/data
# cp postgresql.conf postgresql.conf.orig
# chown postgres:postgres postgresql.conf.orig

Fare il login con l'utente "postgres" e editare il file di configurazione di PostgreSQL

# su postgres
$ vim postgresql.conf

Adesso applicheremo le seguenti modifiche:

listen_addresses = '*'
port = 5433
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 50
max_replication_slots = 10
synchronous_commit = local
synchronous_standby_names = renegade

e alla fine del file aggiungere la seguente linea:

pgpool.pg_ctl = '/usr/pgsql-9.6/bin/pg_ctl'

-------->>>>> ATTENZIONE <<<<<------------

Queste operazioni saranno applicate solamente sul server Master "benz"

archive_mode = on
archive_command = 'test ! -f /var/lib/pgsql/archivedir/%f && cp "%p" "/var/lib/pgsql/archivedir/%f"' 

Creare una nuova directory, cambiare i permessi e il proprietario per l'utente postgres nei due server Benz e Renegade.

$ mkdir -p /var/lib/pgsql/archivedir/
$ chmod 700 /var/lib/pgsql/archivedir/
$ chown -R postgres:postgres /var/lib/pgsql/archivedir/

-------->>>>> FINE ATTENZIONE <<<<<------------

Uscire dall'utente postgres

$ exit

Riavviare Postgresql

# systemctl restart postgresql-9.6

Creare gli utenti necessari per creare e gestire il cluster dentro del database postgresql

# su - postgres
$ psql -p 5433 -U postgres -c "CREATE USER repuser REPLICATION;"
$ psql -p 5433 -U postgres -c "CREATE USER pgpool REPLICATION;"

Modificare il file pg_hba.conf per permettere l'accesso all'utente "repuser" e "postgres" (usare gli indirizzi IP e dopo verificare se sarà possibile inserire gli hostname) semplicemente copiare tutto il contenuto in basso nel file "pg_hba.conf"

$ cd /var/lib/pgsql/9.6/data
$ vim pg_hba.conf
# 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

host    all            postgres        0.0.0.0/0               trust
host    postgres        postgres        192.168.1.14/32         trust
host    all            all             192.168.1.14/32         trust
host    postgres        postgres        192.168.1.15/32         trust
host    all             all             192.168.1.15/32         trust

# Ip Virtuale attivato da pgpool
host    postgres        postgres        192.168.1.16/32         trust

# Allow replication connections from localhost, by a user with the
# replication privilege.
host    replication     repuser         127.0.0.1/32            trust
host    replication     repuser         192.168.1.14/32         trust
host    replication     repuser         192.168.1.15/32         trust

Ricaricare la nuova configurazione di postgresql

$ exit
# systemctl reload postgresql-9.6

Verificare se l'utente "repuser" e "pgpool" sono stati creati

# su postgres
$ psql -p 5433 -U postgres -c "\du+;"

---->>>> FINE DELLA CONFIGURAZIONE DEL SERVER "benz" <<<<-----

Configurare il Server Slave "renegade"

Fermiamo il server PostgreSQL

# systemctl stop postgresql-9.6

Fare un backup della directory "data" di PostgreSQL

# cd /var/lib/pgsql/9.6/
# mv data data-backup

Creare una nuova directory, cambiando permessi e proprietario per l'utente "postgres"

# mkdir -p data/
# chmod 700 data/
# chown -R postgres:postgres data/

Adesso è necessario fare la copia di backup dei dati di PostgreSQL del server master "benz" per il server "renegade"

# su - postgres
# pg_basebackup -h 192.168.1.14 -p 5433 -U repuser -D /var/lib/pgsql/9.6/data --xlog

Dopo che il backup è terminato occorre modificare il file "postgresql.conf" per poterlo adattare al server Slave "renegade"

# cd /var/lib/pgsql/9.6/data/
# vim postgresql.conf
listen_addresses = '*'
port = 5433
hot_standby = on
hot_standby_feedback = on

Aggiungere alla fine dell'archivio la seguente linea;

pgpool.pg_ctl = '/usr/pgsql-9.6/bin/pg_ctl' 

Creare il file "recovery.conf"

$ vim /var/lib/pgsql/9.6/data/recovery.conf

e copiare il contenuto in basso

standby_mode = 'on'
primary_conninfo = 'host=benz port=5433 user=repuser application_name=renegade'
restore_command = 'scp benz:/var/lib/pgsql/archivedir/%f %p'
trigger_file = '/tmp/postgresql.trigger.5433'

Cambiare permessi e proprietario del file "recovery.conf"

$ chmod 600 /var/lib/pgsql/9.6/data/recovery.conf
$ chown postgres:postgres /var/lib/pgsql/9.6/data/recovery.conf

Riavviamo, sui due servers, il servizio postgresql-9.6 rispettando l'ordine di avvio cominciando prima dallo Slave "renegade" e dopo nel Master "benz".

$ exit
# systemctl start postgresql-9.6

------- Testing --------

Sul server master "benz"

# su postgres
$ psql -p 5433 -U postgres -c "CREATE DATABASE test_replica;"
$ psql -p 5433 -U postgres -c "\l"
$ psql -p 5433 -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
$ psql -p 5433 -x -c "select * from pg_stat_replication;"
$ exit

Sul server slave "renegade"

# su postgres
$ psql -p 5433 -U postgres -c "\l"
$ exit

Creazione di password condivise per permettere l'esecuzione di comandi remoti dentro i servers

N.B.: Questo passo deve essere eseguito solamente nel server master "benz" e successivamente le modifiche saranno copiate in tutti gli altri server

# su postgres
$ ssh-keygen -t rsa
$ cat ~/.ssh/id_rsa.pub > ~/.ssh/authorized_keys

confermare con "invio" tutte le richieste che saranno proposte e avremmo le nostre chiavi già pronte non resta che condividerle tra tutti i server e faremo questo di forma manuale.

nel server Master "benz"

$ vim ~/.ssh/authorized_keys

Una volta dentro "vim "digitare la sequenza di tasti "yyp" per duplicare la linea che gia esiste andare alla fine dell'ultima sequenza, dove c'è il nome dell'hostname, e cambiarlo con l'hostname dell'altro server slave o di tutti gli altri nodi.

N.B. Questa operazione deve essere fatta scambiando le sequenze generate in ogni server per gli altri server che compongono il cluster.

Copiare la directory ".ssh" dell'utente "postgres" negli altri servers per poter abilitare l'accesso ssh senza password. Uscire dall'utente postgres

$ exit
# scp -rp /var/lib/pgsql/.ssh root@renegade:/var/lib/pgsql/
# ssh root@renegade 'chown -R postgres.postgres /var/lib/pgsql/.ssh'
# ssh root@renegade 'chmod 700 /var/lib/pgsql/.ssh'
# ssh root@renegade 'chmod 644 /var/lib/pgsql/.ssh/authorized_keys'
# ssh root@renegade 'chmod 644 /var/lib/pgsql/.ssh/id_rsa.pub'
# ssh root@renegade 'chmod 600 /var/lib/pgsql/.ssh/id_rsa'

Fare il test di login dell'utente postgres in tutti i servers dal server "benz" per il server "renegade"

su postgres
ssh localhost
ssh renegade
ssh postgres@renegade
exit

Nel server slave "renegade" per il server master "benz"

su postgres
ssh localhost
ssh benz
ssh postgres@benz
exit

Installazione del PGPool-II e PgpoolAdmin L'installazione dei pacchetti del pgpool-II sarà eseguita nei due server il "benz" e il "renegade" in contemporanea e potremmo usare le due versioni di "pgpool-II 4" o "pgool-II 3.7" ma in questo tutorial abbiamo usato la 3.7

PGPOOL 3.7

# yum install http://www.pgpool.net/yum/rpms/3.7/redhat/rhel-7-x86_64/pgpool-II-release-3.7-1.noarch.rpm
# yum -y install pgpool-II-pg96 php-process pgpool-II-pg96-extensions pgpool-II-pg96-devel

Creare l'utente "pgpool" in tutti i servers del cluster "benz" e "renegade"

# useradd pgpool

Successivamente inserirermo una password che useremo per fare il login, alla richiesta della password digitiamo "pgpool"

# passwd pgpool

Creeremo una directory per la creazione dei logsnei due servers "benz" e "renegade

# mkdir /var/log/pgpool/
# chmod -R 755 /var/log/pgpool
# chown -R pgpool:pgpool /var/log/pgpool

Creare la directory archivedir nel server "benz" e renegade che sarà usata di appoggio nel caso di recovery on line

# su postgres
$ mkdir /var/lib/pgsql/archivedir
$ exit

Per semplificare la configurazione del cluster pgpool tutte le modifiche saranno realizzate nel server "benz" e alla fine le copieremo nel server "renegade"

# cd /etc/pgpool-II
# cp /etc/pgpool-II/pgpool.conf.sample-stream /etc/pgpool-II/pgpool.conf

N.B. per avere due pgpool installati e necessario, una volta configurato il primo, copiare la directory /etc/pgpool-II nell'altra macchina.

Configurare le impostazioni di pgpool (P.S. verificare che opzioni del file "pgpool.conf" siano uguali alle linee in basso) o semplicemente sostituire tutto il contenuto del file pgpool.conf con le configurazioni in basso.

N.B. nel file di configuraione in basso è necessario controllare il nome della scheda di rete usato come esempio che è "eth0" e deve essere modificato conforme alla configuraione della scheda di rete usata nel vostro sistema operativo.

# vim /etc/pgpool-II/pgpool.conf
listen_addresses = '*'
port = 5432
socket_dir = '/tmp'
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/tmp'
listen_backlog_multiplier = 2
serialize_accept = off
backend_hostname0 = 'benz'
backend_port0 = 5433
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/9.6/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = 'renegade'
backend_port1 = 5433
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/9.6/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
enable_pool_hba = on
pool_passwd = 'pool_passwd'
authentication_timeout = 60
ssl = off
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'
ssl_prefer_server_ciphers = 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 = 'stderr'
log_line_prefix = '%t: pid %p: '
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'
pid_file_name = '/var/run/pgpool/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 = '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 = 'postgres'
sr_check_password = 'postgres'
sr_check_database = 'postgres'
delay_threshold = 10000000
follow_master_command = ''
health_check_period = 5
health_check_timeout = 20
health_check_user = 'postgres'
health_check_password = 'postgres'
health_check_database = 'postgres'
health_check_max_retries = 2
health_check_retry_delay = 1
connect_timeout = 10000
failover_command = '/etc/pgpool-II/failover.sh %d %P %H %R'
failback_command = ''
fail_over_on_backend_error = on
search_primary_node_timeout = 300
recovery_user = 'postgres'
recovery_password = 'postgres'
recovery_1st_stage_command = 'recovery_1st_stage'
recovery_2nd_stage_command = ''
recovery_timeout = 90
client_idle_limit_in_recovery = 0
use_watchdog = on
trusted_servers = 'benz,renegade'
ping_path = '/usr/bin'
wd_hostname = 'benz'
wd_port = 9000
wd_priority = 1
wd_authkey = ''
wd_ipc_socket_dir = '/tmp'
delegate_IP = '192.168.1.16'
if_cmd_path = '/var/lib/pgsql/9.6/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 = '/var/lib/pgsql/9.6/sbin'
arping_cmd = 'arping -U $_IP_$ -w 1'
clear_memqcache_on_escalation = on
wd_escalation_command = ''
wd_de_escalation_command = ''
failover_when_quorum_exists = on
failover_require_consensus = on
allow_multiple_failover_requests_from_node = off
wd_monitoring_interfaces_list = 'any'
wd_lifecheck_method = 'heartbeat'
wd_interval = 10
wd_heartbeat_port = 9694
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
heartbeat_destination0 = 'renegade'
heartbeat_destination_port0 = 9694
heartbeat_device0 = ''
heartbeat_destination1 = 'benz'
heartbeat_destination_port1 = 9694
wd_life_point = 3
wd_lifecheck_query = 'SELECT 1'
wd_lifecheck_dbname = 'template1'
wd_lifecheck_user = 'postgres'
wd_lifecheck_password = 'postgres'
other_pgpool_hostname0 = 'renegade'
other_pgpool_port0 = 5432
other_wd_port0 = 9000
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 = ''

Sempre nel server master "benz" creare il file "failover.sh" e aggiungere le linee in basso

# vim /etc/pgpool-II/failover.sh
#! /bin/sh -x
# Execute command by failover.
# special values:  %d = node id
#                  %h = host name
#                  %p = port number
#                  %D = database cluster path
#                  %m = new master node id
#                  %M = old master node id
#                  %H = new master node host name
#                  %P = old primary node id
#                  %R = new master database cluster path
#                  %r = new master port number
#                  %% = '%' character

falling_node=$1          # %d
old_primary=$2           # %P
new_primary=$3           # %H
pgdata=$4                # %R

pghome=/usr/pgsql-9.6
log=/var/log/pgpool/failover.log

date >> $log
echo "failed_node_id=$falling_node new_primary=$new_primary" >> $log

if [ $falling_node = $old_primary ]; then
    if [ $UID -eq 0 ]
    then
        su postgres -c "ssh -T postgres@$new_primary $pghome/bin/pg_ctl promote -D $pgdata"
    else
        #ssh -T postgres@$new_primary $pghome/bin/pg_ctl promote -D $pgdata
        ssh -t postgres@$new_primary $pghome/bin/pg_ctl promote -D $pgdata
    fi
    exit 0;
fi;
exit 0;

Aggiungere permessi di esecuzione al file:

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

Creare con l'utente "postgres" i file necessari per il recovery on line nel server "benz"

# su postgres
$ vim /var/lib/pgsql/9.6/data/recovery_1st_stage

Copiamoci dentro lo script in basso

#!/bin/bash -x
# Recovery script for streaming replication.

pgdata=$1
remote_host=$2
remote_pgdata=$3
port=$4

pghome=/usr/pgsql-9.6
archivedir=/var/lib/pgsql/archivedir
hostname=$(hostname)

ssh -T postgres@$remote_host "
rm -rf $remote_pgdata
$pghome/bin/pg_basebackup -h $hostname -U repuser -D $remote_pgdata -x -c fast
rm -rf $archivedir/*

cd $remote_pgdata
cp postgresql.conf postgresql.conf.bak
sed -e 's/#*hot_standby = off/hot_standby = on/' postgresql.conf.bak > postgresql.conf
rm -f postgresql.conf.bak
cat > recovery.conf << EOT
standby_mode = 'on'
primary_conninfo = 'host="$hostname" port=$port user=repuser'
restore_command = 'scp $hostname:$archivedir/%f %p'
EOT
"

Aggiungere permessi di esecuzione al file:

$ chmod 755 /var/lib/pgsql/9.6/data/recovery_1st_stage

Creazione del file "pgpool_remote_start" che lancia il pgpool remoto

$ vim /var/lib/pgsql/9.6/data/pgpool_remote_start

e copiarci dentro lo script in basso

#! /bin/sh -x

pghome=/usr/pgsql-9.6
remote_host=$1
remote_pgdata=$2

# Start recovery target PostgreSQL server
ssh -T $remote_host $pghome/bin/pg_ctl -w -D $remote_pgdata start > /dev/null 2>&1 < /dev/null &

Aggiungere permessi di esecuzione al file:

$ chmod 755 /var/lib/pgsql/9.6/data/pgpool_remote_start

Per utilizzare la funzionalità di recupero online, sono richieste le funzioni di pgpool_recovery, pgpool_remote_start, pgpool_switch_xlog, quindi è necessario installare pgpool_recovery sul template1 del server "benz" e in tempo reale saranno duplicate nel server slave "renegade"

$ psql -p 5433 template1
=# CREATE EXTENSION pgpool_recovery;
=# CREATE EXTENSION pgpool_adm;
=# \dx

Uscire dalla console di PostgreSQL

=# \q

Uscire dall'utente postgres

$ exit

Questo passo deve essere eseguito in entrambi i server "benz" e "renegade"

Creare la directory /var/lib/pgsql/9.6/sbin e copiarci dentro i file "ifconfig", "ip" e "arping"

# mkdir /var/lib/pgsql/9.6/sbin
# cp /sbin/ifconfig /var/lib/pgsql/9.6/sbin
# cp /sbin/ip /var/lib/pgsql/9.6/sbin
# cp /sbin/arping /var/lib/pgsql/9.6/sbin
# chmod u+s /var/lib/pgsql/9.6/sbin/arping
# chmod u+s /var/lib/pgsql/9.6/sbin/ifconfig
# chmod u+s /var/lib/pgsql/9.6/sbin/ip

Solo sul server "benz" sara necessario generare le chiavi md5 per gli utenti "pgpool" e "postgres"

# cd /etc/pgpool-II
# pg_md5 -m -u postgres postgres
# pg_md5 -m -u pgpool pgpool

Confermare se la creazione delle chiave e avvenuta con successo.

# cat pool_passwd

Per utilizzare il comando PCP è richiesta l'autenticazione dell'utente "postgres". Specifichiamo il nome utente e la password cifrata in md5 nel file "pcp.conf". Creiamo la password crittografata per l'utente "postgres" e aggiungiamo la sequenza "" in "/etc/pgpool-II/pcp.conf".

Sostituire la seconda parola "postgres" con la password desiderata

# echo "postgres:$(pg_md5 postgres)"|sudo tee /etc/pgpool-II/pcp.conf

Aprire il file "pool_hba.conf" per settare i permessi necessari per poter accedere ai nodi del cluster

# vim /etc/pgpool-II/pool_hba.conf

modifichiamo il file "pool_hba.conf" di accordo con il blocco successivo

# "local" is for Unix domain socket connections only
local   all         all                               trust
# IPv4 local connections:
host    all         all         127.0.0.1/32          trust
host    all         all         ::1/128               trust
host    all         all         0.0.0.0/0             trust
host    all         pgpool      192.168.1.0/24        trust
host    all         postgres    192.168.1.0/24        trust
host    replication repuser     192.168.1.0/24        trust

Copiare tutte le configurazioni di pgpool dal server "benz" al server "renegade"

Posizionandosi nel server "benz" digitiamo il seguente comando:

# scp -rp /etc/pgpool-II root@renegade:/etc/

Modificare il file "pgpool.conf" nel server "renegade"

Posizionandosi nel server "renegade" digitiamo i comandi in basso:

# cd /etc/pgpool-II
# vim pgpool.conf

e andremo ad adattare il file "pgpool.conf" del nostro server con il blocco in basso.

heartbeat_destination0 = 'benz'
heartbeat_destination1 = 'renegade'
heartbeat_destination_port1 = 9694
wd_hostname = 'renegade'
other_pgpool_hostname0 = 'benz'

altrimenti portemmo sostituire il contenuto con le configurazioni in basso verificando il nome delle schede di rete, di accordo con le proprie neessità, nei comandi "if_up_cmd" e "if_down_cmd" contenuti nella configurazione in basso.

N.B. nel file di configuraione in basso è necessario controllare il nome della scheda di rete usato come esempio che è "eth0" e deve essere modificato conforme alla configuraione della scheda di rete usata nel vostro sistema operativo.

listen_addresses = '*'
port = 5432
socket_dir = '/tmp'
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/tmp'
listen_backlog_multiplier = 2
serialize_accept = off
backend_hostname0 = 'benz'
backend_port0 = 5433
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/9.6/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = 'renegade'
backend_port1 = 5433
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/9.6/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
enable_pool_hba = on
pool_passwd = 'pool_passwd'
authentication_timeout = 60
ssl = off
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'
ssl_prefer_server_ciphers = 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 = 'stderr'
log_line_prefix = '%t: pid %p: '
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'
pid_file_name = '/var/run/pgpool/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 = '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 = 'postgres'
sr_check_password = 'postgres'
sr_check_database = 'postgres'
delay_threshold = 10000000
follow_master_command = ''
health_check_period = 5
health_check_timeout = 20
health_check_user = 'postgres'
health_check_password = 'postgres'
health_check_database = 'postgres'
health_check_max_retries = 2
health_check_retry_delay = 1
connect_timeout = 10000
failover_command = '/etc/pgpool-II/failover.sh %d %P %H %R'
failback_command = ''
fail_over_on_backend_error = on
search_primary_node_timeout = 300
recovery_user = 'postgres'
recovery_password = 'postgres'
recovery_1st_stage_command = 'recovery_1st_stage'
recovery_2nd_stage_command = ''
recovery_timeout = 90
client_idle_limit_in_recovery = 0
use_watchdog = on
trusted_servers = 'benz,renegade'
ping_path = '/usr/bin'
wd_hostname = 'renegade'
wd_port = 9000
wd_priority = 1
wd_authkey = ''
wd_ipc_socket_dir = '/tmp'
delegate_IP = '192.168.1.16'
if_cmd_path = '/var/lib/pgsql/9.6/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 = '/var/lib/pgsql/9.6/sbin'
arping_cmd = 'arping -U $_IP_$ -w 1'
clear_memqcache_on_escalation = on
wd_escalation_command = ''
wd_de_escalation_command = ''
failover_when_quorum_exists = on
failover_require_consensus = on
allow_multiple_failover_requests_from_node = off
wd_monitoring_interfaces_list = 'any'
wd_lifecheck_method = 'heartbeat'
wd_interval = 10
wd_heartbeat_port = 9694
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
heartbeat_destination0 = 'benz'
heartbeat_destination_port0 = 9694
heartbeat_device0 = ''
heartbeat_destination1 = 'renegade'
heartbeat_destination_port1 = 9694
wd_life_point = 3
wd_lifecheck_query = 'SELECT 1'
wd_lifecheck_dbname = 'template1'
wd_lifecheck_user = 'postgres'
wd_lifecheck_password = 'postgres'
other_pgpool_hostname0 = 'benz'
other_pgpool_port0 = 5432
other_wd_port0 = 9000
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 = ''

Adesso verifichiamo che il pgpool sia configurato correttamente lanciando pgpool nel server "renegade"

# systemctl start pgpool.service
# systemctl enable pgpool.service

Lanciamo pgpool nel server "benz"

# systemctl start pgpool.service
# systemctl enable pgpool.service

Comandi per la verifica e l'interazione con il cluster e i nodi:

Per poter verificare che il cluster sta funzionando

# su postgres
$ psql -p 5432 -h 192.168.1.16 -U postgres -c "show pool_nodes"

Mostra lo stato di watchdog con i server che sono attivi

$ pcp_watchdog_info -v -p 9898 -h 192.168.1.16 -U postgres

password: postgres

Mostra la configurazione dei backend del cluster

$ psql -h 192.168.1.16 -p 5432 -U postgres -c "pgpool show backend;"

Per recuperare un server che sta nello stato di "standby" sarà necessario eseguire il comando manuale

# pcp_recovery_node -h 192.168.1.16 -p 9898 -U postgres -n 1 

password: postgres (il valore di -n è 0 o 1 nel caso che sia il primo o secondo server a essere recuperato)

Per verificare quanti nodi sono presenti

$ pcp_node_count -h 192.168.1.16 -p 9898 -U postgres

password: postgres

Verificare lo stato di un sigolo nodo: Sostituire lo "zero" finale con l'ID del server che si vuole verificare. lo "zero" indica il server principale

$ pcp_node_info --verbose -h 192.168.1.16 -p 9898 -U postgres 0

password: postgres

Verifica lo stato del cluster

$ pcp_pool_status -h 192.168.1.16 -p 9898 -U postgres

password: postgres

Per rimuovere un nodo dal cluster sarà necessario eseguire il comando manuale

# pcp_detach_node -h 192.168.1.16 -p 9898 -U postgres -n 1 

password: postgres
(-n è 0 o 1 nel caso che sia il primo o il secondo server che deve essere rimosso)

Per aggiungere un nodo al cluster sarà necessario eseguire il comando manuale

# pcp_attach_node -h 192.168.1.16 -p 9898 -U postgres -n 1 

password: postgres
(-n è 0 o 1 nel caso che sia il primo o il secondo server a essere rimosso)

Per promuovere un nodo nel cluster come master/slave sarà necessario eseguire il comando manuale

# pcp_promote_node -h 192.168.1.16 -p 9898 -U postgres -n 1

password: postgres
(-n è 0 o 1 nel caso che sia il primo o il secondo server a essere promosso)

Installazione e Configurazione di PgpoolAdmin 3.7 in entrambi i servers "benz" e "renegade"

Per eseguire l'installazione di "pgpoolAdmin 3.7" useremo il repository online passando l'indirizzo web a yum:

# yum install http://www.pgpool.net/yum/rpms/3.7/redhat/rhel-7-x86_64/pgpoolAdmin-3.7.0-1pgdg.rhel7.noarch.rpm

N.B.: nel caso si vuole installare "pgpoolAdmin 4" sarà usato il comando in basso:

# yum -y install pgpoolAdmin

Verificare la home dell'utente apache in /etc/passwd

# cat /etc/passwd | grep apache

sarà mostrato la seguente linea:
`apache : x : 48 : 48 : Apache:/usr/share/httpd:/sbin/nologin

Creare il file ".pcppass"

# vim /usr/share/httpd/.pcppass

inserire il seguente contenuto:

*:*:postgres:postgres

e modificare i permessi permettendo ad Apache l'accesso all'utente "postgres"

# chown apache.apache /usr/share/httpd/.pcppass
# chmod 600 /usr/share/httpd/.pcppass

Verificare che il contenuto del file ".pcppass" rispetti questo formato:

host:port:user:password 

dove la password è scritta usando testo in chiaro e inoltre sarà possibile usare anche gli asterischi come nell'esempio in basso

host:port:user:password
*:*:user:password

Cambiamo i permessi dei files "pgpool.conf" e di "pcp.conf"

# chmod 666 /etc/pgpool-II/pcp.conf
# chmod 666 /etc/pgpool-II/pgpool.conf

Abilitiamo e lanciamo il server Apache

# systemctl enable httpd
# systemctl start httpd

La parte sottostante sarà ripetuta nei due server "benz" e "renegade"

Aprire il browser e andare nell'indirizzo del server "benz":

http://192.168.1.14/pgpoolAdmin/install/

Aprire il browser e andare nell'indirizzo del server "renegade":

http://192.168.1.15/pgpoolAdmin/install/

Durante la configurazione di PgpoolAdmin sarà necessario fare delle piccole modifiche dentro dell'interfaccia grafica di pgpoolAdmin

pgpool.conf File = /etc/pgpool-II/pgpool.conf
pcp.conf File = /etc/pgpool-II/pcp.conf
pgpool Command = /usr/bin/pgpool
PCP directory = /usr/bin

Se l'installazione è andata a buon fine sarà necessario, per problemi di sicurezza, rinominare la directory di installazione di PgpoolAdmin

# mv /var/www/html/pgpoolAdmin/install /var/www/html/pgpoolAdmin/install-old

Per effettuare il login nell'interfaccia grafica di pgpoolAdmin usare i seguenti dati

Login Name: postgres
password: postgres

Osservazioni: Per poter generare il blocco con le configurazioni del file "pgpool.conf" è stato usato il comando in basso per poter cancellare tutti i commenti e le linee vuote per compattare il contenuto del file.

Comando per pulire i commenti dai file di configuraione di Postgres

sed -e '/^[ \t]*#/d' /etc/pgpool-II/pgpool.conf | grep -v -e '^$' > /etc/pgpool-II/pgpool.conf-formattato.conf

Buon cluster a tutti !!!! ;-)

Referenze: http://www.sraoss.jp/ https://www.vidispine.com/ https://dinfratechsource.com https://www.vidispine.com/blog/ http://www.pgpool.net/docs/ http://www.pgpool.net/mediawiki http://www.pgpool.net/docs/ http://www.pgpool.net/pgpool-web https://blog.dbi-services.com/1 https://blog.dbi-services.com/2](https://blog.dbi-services.com/vertically-scale-your-postgresql-infrastructure-with-pgpool-2-automatic-failover-and-reconfiguration/) https://blog.dbi-services.com/3](https://blog.dbi-services.com/vertically-scale-your-postgresql-infrastructure-with-pgpool-3-adding-another-standby/) https://www.amitnepal.com https://www.fatdragon.me http://saule1508.github.io/ https://access.crunchydata.com https://archyslife.blogspot.com https://blog.neptune-ubi.com/