<html>
<head>
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="moz-cite-prefix">Thank you Yugo,<br>
<br>
I'm glad that you managed to find the problem. I don't know why
did repmgr choose to implement slave promotion by restarting the
server, but that is another issue.<br>
<br>
I guess that pg_ctl promote or trigger file will work for my case
as you suggested.<br>
<br>
Again, thank you for your support.<br>
<div class="moz-signature">
<div style="margin:15px 0 0 0; font-family: 'Lucida Sans
Unicode', 'Lucida Grande', arial, sans-serif; color: #282828;
line-height: 1.5em; font-size: 11px;">
<b style="font-size: 13px;">Martin Kováčik</b><br>
<em>CEO</em><br>
<div style="margin-top: 5px;">
<b><span style="color:#a10309;">red</span><span
style="color:#282828;">Byte</span></b>, s.r.o.<br>
+421 904 236 791<br>
<a style="color: #282828; text-decoration: underline;"
href="mailto:kovacik@redbyte.eu">kovacik@redbyte.eu</a>, <a
style="color: #282828; text-decoration: underline;"
href="http://redbyte.eu">www.redbyte.eu</a>
</div>
</div>
</div>
On 10.03.2014 11:33, Yugo Nagata wrote:<br>
</div>
<blockquote cite="mid:20140310193325.78cbf10b.nagata@sraoss.co.jp"
type="cite">
<pre wrap="">Hi,
I'm sorry the late reply.
I looked at the pgpool logs and repmgr's source and found that
repmgr restarted the standby server to promote instead of using
pg_ctl promote or trigger_file.
When a backend server is restarted, pgpool regards this as fault
and detaches this from backend and ceases to connect this. So,
I think, unfortunately repmgr would not work well as a failover
method for pgpool.
On Mon, 03 Mar 2014 14:33:09 +0100
Martin Kováčik <a class="moz-txt-link-rfc2396E" href="mailto:kovacik@redbyte.eu"><kovacik@redbyte.eu></a> wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Hi Yugo,
thank you for helping me.
Repmgr is just a tool (couple of scripts) to help you manage a
postgresql cluster, please see a description at <a class="moz-txt-link-freetext" href="http://www.repmgr.org/">http://www.repmgr.org/</a> .
Im using repmgr v.2.0RC1.
I only use repmgr and not the repmgrd (repmgrd is a daemon which is able
to do automatic failover, but I use pgpool2 for that).
I'm sending you pgpool.log and repmgr.conf. In the pgpool.log you can
see the output of repmgr command during the failover.
Thank you.
*Martin Kováčik*
/CEO/
*redByte*, s.r.o.
+421 904 236 791
<a class="moz-txt-link-abbreviated" href="mailto:kovacik@redbyte.eu">kovacik@redbyte.eu</a> <a class="moz-txt-link-rfc2396E" href="mailto:kovacik@redbyte.eu"><mailto:kovacik@redbyte.eu></a>, <a class="moz-txt-link-abbreviated" href="http://www.redbyte.eu">www.redbyte.eu</a>
<a class="moz-txt-link-rfc2396E" href="http://redbyte.eu"><http://redbyte.eu></a>
On 03.03.2014 10:06, Yugo Nagata wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Hi,
I'm not familiar to repmgr, but I'll try to reproduce this.
Before that, could you please send log messages of pgpool and repmgr,
and repmgr.conf?
On Fri, 28 Feb 2014 15:16:50 +0100
Martin Kováčik <a class="moz-txt-link-rfc2396E" href="mailto:kovacik@redbyte.eu"><kovacik@redbyte.eu></a> wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Hi,
I'm trying to install a simple 2 node pgpool2 cluster with hot standby
streaming replication and automatic failover.
So far the streaming replication between 2 postresql servers is working
perfectly. For the failover part I'm using repmgr which also works great.
I can connect to both postgresql backends and also I can connect to both
postgresql backends through pgpool2.
The automatic failover works. When I stop the master node, I can see
that the standby becomes new master. I'm able to directly connect to new
master postgresql node and it is no longer in read only mode. However
when I want to connect to the cluster (after the failover) through
pgpool2 (using psql client) the connection waits forever and I'm no
longer to connect. I've turned on connection logging for postgresql and
there is no record of an attempted connection. Pgpool2 log also doesn't
show incomming connection attempt. To be able to connect through pgpool2
again, I must restart it.
I'm unable to connect only after the master failover. If I stop the
standby node, the backend is correctly detected as down, but I'm still
able to connect to pgpool2.
Please help. Thank you.
My setup is:
2x PostgreSQL 9.3.2 on x86_64-unknown-linux-gnu, compiled by gcc
(Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
pgpool-II version 3.3.2 (tokakiboshi) (I've also tried the latest master
at 46c9a71813768fa799580f3ae07599b9eaa14fef)
Linux 3.11.0-12-generic #19-Ubuntu SMP Wed Oct 9 16:20:46 UTC 2013
x86_64 x86_64 x86_64 GNU/Linux
My pgpool.conf is:
listen_addresses = '*'
port = 9999
socket_dir = '/tmp'
pcp_port = 9898
pcp_socket_dir = '/tmp'
backend_hostname0 = 'dbnode1'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/var/lib/postgresql/9.3/main'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = 'dbnode2'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/postgresql/9.3/main'
backend_flag1 = 'ALLOW_TO_FAILOVER'
enable_pool_hba = on
pool_passwd = 'pool_passwd'
authentication_timeout = 10
ssl = off
num_init_children = 8
max_pool = 4
child_life_time = 300
child_max_connections = 0
connection_life_time = 0
client_idle_limit = 0
log_destination = 'stderr'
print_timestamp = on
log_connections = on
log_hostname = on
log_statement = off
log_per_node_statement = off
log_standby_delay = 'none'
syslog_facility = 'LOCAL0'
syslog_ident = 'pgpool'
debug_level = 1
pid_file_name = '/var/run/pgpool/pgpool.pid'
logdir = '/tmp'
connection_cache = off
reset_query_list = 'ABORT; DISCARD ALL'
replication_mode = off
replicate_select = off
insert_lock = on
lobj_lock_table = ''
replication_stop_on_mismatch = off
failover_if_affected_tuples_mismatch = off
load_balance_mode = off
ignore_leading_white_space = on
white_function_list = ''
black_function_list = 'nextval,setval'
master_slave_mode = on
master_slave_sub_mode = 'stream'
sr_check_period = 5
sr_check_user = 'repmgr'
sr_check_password = ''
delay_threshold = 0
follow_master_command = ''
parallel_mode = off
pgpool2_hostname = ''
system_db_hostname = 'localhost'
system_db_port = 5432
system_db_dbname = 'pgpool'
system_db_schema = 'pgpool_catalog'
system_db_user = 'pgpool'
system_db_password = ''
health_check_period = 5
health_check_timeout = 30
health_check_user = 'repmgr'
health_check_password = ''
health_check_max_retries = 3
health_check_retry_delay = 10
failover_command = 'su postgres -c "ssh postgres@%H repmgr -f
/var/lib/postgresql/9.3/repmgr/repmgr.conf --verbose standby promote"'
failback_command = ''
fail_over_on_backend_error = on
search_primary_node_timeout = 10
recovery_user = 'nobody'
recovery_password = ''
recovery_1st_stage_command = ''
recovery_2nd_stage_command = ''
recovery_timeout = 90
client_idle_limit_in_recovery = 0
use_watchdog = false
trusted_servers = ''
ping_path = '/bin'
wd_hostname = 'dbnode1'
wd_port = 9000
wd_authkey = ''
delegate_IP = '10.5.4.241'
ifconfig_path = '/sbin'
if_up_cmd = 'ifconfig eth0:0 inet $_IP_$ netmask 255.255.255.0'
if_down_cmd = 'ifconfig eth0:0 down'
arping_path = '/usr/sbin' # arping command path
arping_cmd = 'arping -U $_IP_$ -w 1'
clear_memqcache_on_escalation = on
wd_escalation_command = ''
wd_lifecheck_method = 'heartbeat'
wd_interval = 3
wd_heartbeat_port = 9694
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
heartbeat_destination0 = 'dbnode2'
heartbeat_destination_port0 = 9694
heartbeat_device0 = ''
wd_life_point = 3
wd_lifecheck_query = 'SELECT 1'
wd_lifecheck_dbname = 'template1'
wd_lifecheck_user = 'repmgr'
wd_lifecheck_password = ''
other_pgpool_hostname0 = 'dbnode2'
other_pgpool_port0 = 9999
other_wd_port0 = 9000
relcache_expire = 0
relcache_size = 256
check_temp_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 = ''
--
*Martin*
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<br>
</body>
</html>