<div dir="ltr"><div>Also to add, for some reason with this configuration and setup, while testing failover and failing the master database, pgpool correctly identified the master as failed and detached<br></div> it from the cluster. But all writes failed to the slave since the trigger was not done and the slave didn't become master (was still and read-only state). For some reason,<br>
failover_command = '/store/pgdata/failover_<div id=":14p" class="">stream.sh %d %P %H %R postgresql.trigger'<br></div><div id=":14p" class="">was not executed.<br><br></div><div id=":14p" class="">Normally, I expect to see something like that in the logs:<br>
2014-06-03 05:25:33 ERROR: pid 2970: make_persistent_db_connection: connection to 10.0.90.11(5432) failed<br>2014-06-03 05:25:33 ERROR: pid 2970: health check failed. 0 th host 10.0.90.11 at port 5432 is down<br>2014-06-03 05:25:33 LOG: pid 2970: set 0 th backend down status<br>
2014-06-03 05:25:33 LOG: pid 2970: wd_start_interlock: start interlocking<br>2014-06-03 05:25:33 LOG: pid 2970: wd_assume_lock_holder: become a new lock holder<br>2014-06-03 05:25:37 LOG: pid 17705: connection received: host=10.0.97.1 port=57218<br>
2014-06-03 05:25:37 ERROR: pid 17705: connect_inet_domain_socket: getsockopt() detected error: Connection refused<br>2014-06-03 05:25:37 ERROR: pid 17705: connection to 10.0.90.11(5432) failed<br>2014-06-03 05:25:37 ERROR: pid 17705: new_connection: create_cp() failed<br>
2014-06-03 05:25:37 LOG: pid 17705: new_connection: do not failover because fail_over_on_backend_error is off<br>2014-06-03 05:25:44 ERROR: pid 2970: wd_start_interlock: timed out<br>2014-06-03 05:25:44 LOG: pid 2970: starting degeneration. shutdown host 10.0.90.11(5432)<br>
2014-06-03 05:25:44 LOG: pid 2970: Restart all children<br>2014-06-03 05:25:44 LOG: pid 2970: execute command: /store/pgdata/failover_stream.sh 0 0 10.0.90.12 /store/pgdata postgresql.trigger<br>+ FAILED_NODE=0<br>+ OLD_MASTER=0<br>
+ NEW_MASTER=10.0.90.12<br>+ PGDATA=/store/pgdata<br>+ TRIGGER_FILE=postgresql.trigger<br>+ '[' 0 = 0 ']'<br>+ /usr/bin/ssh -T 10.0.90.12 /bin/touch /store/pgdata/postgresql.trigger<br>+ exit 0<br>2014-06-03 05:25:44 LOG: pid 2970: find_primary_node_repeatedly: waiting for finding a primary node<br>
2014-06-03 05:25:47 LOG: pid 2970: find_primary_node: primary node id is 1<br>2014-06-03 05:25:47 LOG: pid 2970: starting follow degeneration. shutdown host 10.0.90.11(5432)<br>2014-06-03 05:25:47 LOG: pid 2970: failover: 1 follow backends have been degenerated<br>
2014-06-03 05:25:47 LOG: pid 26259: start triggering follow command.<br>2014-06-03 05:25:47 LOG: pid 26259: execute command: echo $(date) host:10.0.90.11, new master id:1, old master id:0 >> /var/log/pgpool/master_failover.log<br>
2014-06-03 05:25:47 LOG: pid 2970: wd_end_interlock: end interlocking<br>2014-06-03 05:25:48 LOG: pid 2970: failover: set new primary node: 1<br>2014-06-03 05:25:48 LOG: pid 2970: failover: set new master node: 1<br>
2014-06-03 05:25:48 LOG: pid 2970: failover done. shutdown host 10.0.90.11(5432)<br><br><br></div><div id=":14p" class="">But in this case, when I shutdown 10.0.90.1 (master) and expect the failover script to be run, I don't see it being run at all. See the log snippet below:<br>
2014-06-05 23:38:57 ERROR: pid 2970: health check failed. 0 th host 10.0.90.11 at port 5432 is down<br>2014-06-05 23:38:57 LOG: pid 2970: health check retry sleep time: 1 second(s)<br>2014-06-05 23:38:58 ERROR: pid 2970: s_do_auth: expecting R got E<br>
2014-06-05 23:38:58 ERROR: pid 2970: make_persistent_db_connection: s_do_auth failed<br>2014-06-05 23:38:58 ERROR: pid 2970: health check failed. 0 th host 10.0.90.11 at port 5432 is down<br>2014-06-05 23:38:58 LOG: pid 2970: health check retry sleep time: 1 second(s)<br>
2014-06-05 23:38:59 ERROR: pid 2970: s_do_auth: expecting R got E<br>2014-06-05 23:38:59 ERROR: pid 2970: make_persistent_db_connection: s_do_auth failed<br>2014-06-05 23:38:59 ERROR: pid 2970: health check failed. 0 th host 10.0.90.11 at port 5432 is down<br>
2014-06-05 23:38:59 LOG: pid 2970: health check retry sleep time: 1 second(s)<br>2014-06-05 23:39:00 LOG: pid 2982: wd_send_response: WD_STAND_FOR_LOCK_HOLDER received it<br>2014-06-05 23:39:00 ERROR: pid 2970: s_do_auth: expecting R got E<br>
2014-06-05 23:39:00 ERROR: pid 2970: make_persistent_db_connection: s_do_auth failed<br>2014-06-05 23:39:00 ERROR: pid 2970: health check failed. 0 th host 10.0.90.11 at port 5432 is down<br>2014-06-05 23:39:00 LOG: pid 2970: set 0 th backend down status<br>
2014-06-05 23:39:00 LOG: pid 2970: wd_start_interlock: start interlocking<br>2014-06-05 23:39:01 LOG: pid 2970: starting degeneration. shutdown host 10.0.90.11(5432)<br>2014-06-05 23:39:01 LOG: pid 2970: Restart all children<br>
2014-06-05 23:39:02 LOG: pid 2970: find_primary_node_repeatedly: waiting for finding a primary node<br>2014-06-05 23:39:12 LOG: pid 2970: failover: no follow backends are degenerated<br>2014-06-05 23:39:12 LOG: pid 2970: wd_end_interlock: end interlocking<br>
2014-06-05 23:39:12 LOG: pid 2970: failover: set new primary node: -1<br>2014-06-05 23:39:12 LOG: pid 2970: failover: set new master node: 1<br>2014-06-05 23:39:12 LOG: pid 30220: connection received: host=10.0.97.1 port=45122<br>
2014-06-05 23:39:13 LOG: pid 30220: pool_send_and_wait: Error or notice message from backend: : DB node id: 1 backend pid: 23115 statement: UPDATE SCGroupRequestStatus SET Status = $1 WHERE Status = $2 and LastUpdateTime < $3 and Owner is NULL message: cannot execute UPDATE in a read-only transaction<br>
2014-06-05 23:39:13 LOG: pid 30326: do_child: failback event found. restart myself.<br>2014-06-05 23:39:13 LOG: pid 30456: do_child: failback event found. restart myself.<br>2014-06-05 23:39:13 LOG: pid 30468: do_child: failback event found. restart myself.<br>
2014-06-05 23:39:13 LOG: pid 2970: failover done. shutdown host 10.0.90.11(5432)<br>2014-06-05 23:39:13 LOG: pid 29758: worker process received restart request<br>2014-06-05 23:39:14 LOG: pid 29511: pcp child process received restart request<br>
2014-06-05 23:39:14 LOG: pid 2970: PCP child 29511 exits with status 256 in failover()<br>2014-06-05 23:39:14 LOG: pid 2970: fork a new PCP child pid 30470 in failover()<br>2014-06-05 23:39:14 LOG: pid 2970: worker child 29758 exits with status 256<br>
2014-06-05 23:39:14 LOG: pid 2970: fork a new worker child pid 30471<br>2014-06-05 23:39:25 LOG: pid 30472: connection received: host=10.0.97.1 port=45123<br>2014-06-05 23:39:59 LOG: pid 30466: connection received: host=10.0.70.1 port=50938<br>
2014-06-05 23:40:02 LOG: pid 30220: pool_send_and_wait: Error or notice message from backend: : DB node id: 1 backend pid: 23115 statement: UPDATE SCGroupRequestStatus SET Status = $1 WHERE Status = $2 and LastUpdateTime < $3 and Owner is NULL message: cannot execute UPDATE in a read-only transaction<br>
2014-06-05 23:41:02 LOG: pid 30220: pool_send_and_wait: Error or notice message from backend: : DB node id: 1 backend pid: 23115 statement: UPDATE SCGroupRequestStatus SET Status = $1 WHERE Status = $2 and LastUpdateTime < $3 and Owner is NULL message: cannot execute UPDATE in a read-only transaction<br>
<br><br></div><div id=":14p" class="">As you can see the node was detached from the cluster correctly, but the failover script was NOT executed? So, the trigger was not done, and the slave was not promoted to master.<br><br>
</div><div id=":14p" class="">Why is that? Please see the configurations in the previous email.<br><br></div><div id=":14p" class="">Also, this is the failover script:<br><br>#!/bin/bash -x<br>FAILED_NODE=$1 # %d<br>
OLD_MASTER=$2 # %P<br>NEW_MASTER=$3 # %H<br>PGDATA=$4 # %R<br>TRIGGER_FILE=$5<br><br>SSH_OPTIONS='-T -o StrictHostKeyChecking=no'<br><br>if [ $FAILED_NODE = $OLD_MASTER ]; then<br> /usr/bin/ssh $SSH_OPTIONS $NEW_MASTER /bin/touch $PGDATA/$TRIGGER_FILE<br>
fi;<br>exit 0;<br><br><br></div><div id=":14p" class="">Sincerely,<br>Alexandru<br></div><br></div><div class="gmail_extra"><br><br><div class="gmail_quote">On Sat, Jun 21, 2014 at 10:49 PM, Alexandru Cardaniuc <span dir="ltr"><<a href="mailto:cardaniuc@gmail.com" target="_blank">cardaniuc@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><br>
Hi All,<br>
<br>
Need some validation of the configuration that I did for the cluster of<br>
2 pgpools with a cluster of 2 PostgreSQL 9.3 databases. I went through<br>
all the documentation, but in some places it's not completely clear, so<br>
I am not sure if I misunderstood some of the things.<br>
<br>
Here's the info.<br>
<br>
Pgpool version: 3.3.3 and using watchdog (2 pgpools: 1 primary, 1<br>
standby)<br>
<br>
PostgreSQL version 9.3 (cluster of 2 databases with streaming<br>
replication and hot standby configured).<br>
<br>
This is my relevant PostgreSQL 9.3 config:<br>
------------------------------------------<br>
listen_addresses = '*'<br>
port = 5432<br>
max_connections = 550<br>
superuser_reserved_connections = 3<br>
wal_level = hot_standby<br>
checkpoint_segments = 10<br>
checkpoint_completion_target = 0.9<br>
max_wal_senders = 10<br>
wal_keep_segments = 100<br>
hot_standby = on<br>
------------------------------------------<br>
The documentation states that:<br>
"To enable read-only queries on a standby server, wal_level must be set<br>
to hot_standby on the primary, and hot_standby must be enabled in the standby."<br>
<br>
I use the same config above on both primary database and secondary with<br>
the assumption that 'wal_level = hot_standby' is ignored on the slave<br>
and 'hot_standby = on' is ignored on the master.<br>
<br>
Is that a correct assumption? That assumption shouldn't create any<br>
issues during pgpool/postgres failover?<br>
<br>
<br>
Also, about the pgpool config. This is the pgpool config for the 1st<br>
pgpool:<br>
--------------------------------------------------------------------<br>
listen_addresses = '*'<br>
port = 9999<br>
socket_dir = '/var/run/pgpool'<br>
pcp_port = 9898<br>
pcp_socket_dir = '/var/run/pgpool'<br>
num_init_children = 250<br>
child_life_time = 300<br>
child_max_connections = 0<br>
client_idle_limit = 0<br>
enable_pool_hba = false<br>
pool_passwd = 'pool_passwd'<br>
authentication_timeout = 60<br>
print_timestamp = true<br>
log_connections = true<br>
log_hostname = false<br>
log_statement = true<br>
log_per_node_statement = false<br>
debug_level = 0<br>
pid_file_name = '/var/run/pgpool/pgpool.pid'<br>
logdir = '/var/run/pgpool'<br>
connection_cache = false<br>
health_check_period = 60<br>
health_check_timeout = 15<br>
health_check_user = 'postgres'<br>
health_check_password = 'password'<br>
health_check_max_retries = 3<br>
health_check_retry_delay = 1<br>
search_primary_node_timeout = 10<br>
failover_command = '/store/pgdata/failover_stream.sh %d %P %H %R postgresql.trigger'<br>
failback_command = 'echo $(date) host:%h, new master id:%m, old master id:%M >> /var/log/pgpool/failback.log'<br>
follow_master_command = 'echo $(date) host:%h, new master id:%m, old master id:%M >> /var/log/pgpool/master_failover.log'<br>
fail_over_on_backend_error = false<br>
ignore_leading_white_space = true<br>
backend_hostname0 = '10.0.90.11'<br>
backend_port0 = 5432<br>
backend_weight0 = 1<br>
backend_data_directory0 = '/store/pgdata'<br>
backend_flag0 = 'ALLOW_TO_FAILOVER'<br>
backend_hostname1 = '10.0.90.12'<br>
backend_port1 = 5432<br>
backend_weight1 = 1<br>
backend_data_directory1 = '/store/pgdata'<br>
backend_flag1 = 'ALLOW_TO_FAILOVER'<br>
ssl = false<br>
max_pool = 1<br>
connection_life_time = 0<br>
reset_query_list = 'ABORT; DISCARD ALL'<br>
replication_mode = false<br>
replication_stop_on_mismatch = true<br>
replicate_select = false<br>
insert_lock = true<br>
recovery_user = 'postgres'<br>
recovery_password = 'password'<br>
recovery_1st_stage_command = 'base_backup.sh'<br>
recovery_2nd_stage_command = ''<br>
recovery_timeout = 120<br>
client_idle_limit_in_recovery = 1<br>
lobj_lock_table = ''<br>
master_slave_mode = true<br>
load_balance_mode = true<br>
master_slave_sub_mode = 'stream'<br>
delay_threshold = 0<br>
sr_check_period = 0<br>
sr_check_user = 'postgres'<br>
sr_check_password = 'password'<br>
log_standby_delay = 'none'<br>
parallel_mode = false<br>
system_db_hostname = 'localhost'<br>
system_db_port = 5432<br>
system_db_dbname = 'pgpool'<br>
system_db_schema = 'pgpool_catalog'<br>
system_db_user = 'pgpool'<br>
system_db_password = ''<br>
pgpool2_hostname = ''<br>
memory_cache_enabled = off<br>
use_watchdog = on<br>
wd_hostname = '10.0.90.11'<br>
wd_port = 9000<br>
delegate_IP = '10.0.90.1'<br>
ifconfig_path = '/store/pgdata/sbin'<br>
if_up_cmd = 'ifconfig eth0:0 inet $_IP_$ netmask 255.255.0.0'<br>
if_down_cmd = 'ifconfig eth0:0 down'<br>
arping_path = '/store/pgdata/sbin'<br>
arping_cmd = 'arping -U $_IP_$ -w 1'<br>
wd_lifecheck_method = 'heartbeat'<br>
wd_interval = 10<br>
wd_heartbeat_port = 9694<br>
wd_heartbeat_keepalive = 2<br>
heartbeat_destination0 = '10.0.90.12'<br>
heartbeat_destination_port0 = 9694<br>
other_pgpool_hostname0 = '10.0.90.12'<br>
other_pgpool_port0 = 9999<br>
other_wd_port0 = 9000<br>
--------------------------------------------------------------------<br>
<br>
second pgpool config file:<br>
--------------------------------------------------------------------<br>
listen_addresses = '*'<br>
port = 9999<br>
socket_dir = '/var/run/pgpool'<br>
pcp_port = 9898<br>
pcp_socket_dir = '/var/run/pgpool'<br>
num_init_children = 250<br>
child_life_time = 300<br>
child_max_connections = 0<br>
client_idle_limit = 0<br>
enable_pool_hba = false<br>
pool_passwd = 'pool_passwd'<br>
authentication_timeout = 60<br>
print_timestamp = true<br>
log_connections = true<br>
log_hostname = false<br>
log_statement = true<br>
log_per_node_statement = false<br>
debug_level = 0<br>
pid_file_name = '/var/run/pgpool/pgpool.pid'<br>
logdir = '/var/run/pgpool'<br>
connection_cache = false<br>
health_check_period = 60<br>
health_check_timeout = 15<br>
health_check_user = 'postgres'<br>
health_check_password = 'password'<br>
health_check_max_retries = 3<br>
health_check_retry_delay = 1<br>
search_primary_node_timeout = 10<br>
failover_command = '/store/pgdata/failover_stream.sh %d %P %H %R postgresql.trigger'<br>
failback_command = 'echo $(date) host:%h, new master id:%m, old master id:%M >> /var/log/pgpool/failback.log'<br>
follow_master_command = 'echo $(date) host:%h, new master id:%m, old master id:%M >> /var/log/pgpool/master_failover.log'<br>
fail_over_on_backend_error = false<br>
ignore_leading_white_space = true<br>
backend_hostname0 = '10.0.90.11'<br>
backend_port0 = 5432<br>
backend_weight0 = 1<br>
backend_data_directory0 = '/store/pgdata'<br>
backend_flag0 = 'ALLOW_TO_FAILOVER'<br>
backend_hostname1 = '10.0.90.12'<br>
backend_port1 = 5432<br>
backend_weight1 = 1<br>
backend_data_directory1 = '/store/pgdata'<br>
backend_flag1 = 'ALLOW_TO_FAILOVER'<br>
ssl = false<br>
max_pool = 1<br>
connection_life_time = 0<br>
reset_query_list = 'ABORT; DISCARD ALL'<br>
replication_mode = false<br>
replication_stop_on_mismatch = true<br>
replicate_select = false<br>
insert_lock = true<br>
recovery_user = 'postgres'<br>
recovery_password = 'password'<br>
recovery_1st_stage_command = 'base_backup.sh'<br>
recovery_2nd_stage_command = ''<br>
recovery_timeout = 120<br>
client_idle_limit_in_recovery = 1<br>
lobj_lock_table = ''<br>
master_slave_mode = true<br>
load_balance_mode = true<br>
master_slave_sub_mode = 'stream'<br>
delay_threshold = 0<br>
sr_check_period = 0<br>
sr_check_user = 'postgres'<br>
sr_check_password = 'password'<br>
log_standby_delay = 'none'<br>
parallel_mode = false<br>
system_db_hostname = 'localhost'<br>
system_db_port = 5432<br>
system_db_dbname = 'pgpool'<br>
system_db_schema = 'pgpool_catalog'<br>
system_db_user = 'pgpool'<br>
system_db_password = ''<br>
pgpool2_hostname = ''<br>
memory_cache_enabled = off<br>
use_watchdog = on<br>
wd_hostname = '10.0.90.12'<br>
wd_port = 9000<br>
delegate_IP = '10.0.90.1'<br>
ifconfig_path = '/store/pgdata/sbin'<br>
if_up_cmd = 'ifconfig eth0:0 inet $_IP_$ netmask 255.255.0.0'<br>
if_down_cmd = 'ifconfig eth0:0 down'<br>
arping_path = '/store/pgdata/sbin'<br>
arping_cmd = 'arping -U $_IP_$ -w 1'<br>
wd_lifecheck_method = 'heartbeat'<br>
wd_interval = 10<br>
wd_heartbeat_port = 9694<br>
wd_heartbeat_keepalive = 2<br>
heartbeat_destination0 = '10.0.90.11'<br>
heartbeat_destination_port0 = 9694<br>
other_pgpool_hostname0 = '10.0.90.11'<br>
other_pgpool_port0 = 9999<br>
other_wd_port0 = 9000<br>
--------------------------------------------------------------------<br>
<br>
Anything wrong or missing in the configs? I need a cluster of pgpools<br>
with watchdog and healthchecks monitoring a cluster of postgres<br>
databases with streaming replication setup.<br>
<br>
Any help in review or suggestions based on your knowledge or experience<br>
will be appreciated.<br>
<br>
Sincerely,<br>
Alexandru<br>
<span class="HOEnZb"><font color="#888888"><br>
--<br>
"A journey of a thousand miles begins with a single step."<br>
- Ancient Proverb<br>
</font></span></blockquote></div><br><br clear="all"><br>-- <br>Sincerely yours,<br>Alexandru Cardaniuc
</div>