<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-2">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 70.85pt 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="SL" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal">Hi,<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I'm testing pgpoll installation with three servers according to documentation on the page: <a href="http://www.pgpool.net/docs/latest/en/html/example-cluster.html"><span style="color:blue">http://www.pgpool.net/docs/latest/en/html/example-cluster.html</span></a><o:p></o:p></p>
<p class="MsoNormal">I'm using Centos 7 with PostgreSQL 11.3 and pgpool 4.0.5.<o:p></o:p></p>
<p class="MsoNormal">I have three servers: pgsql1 (primary), pgsql2 (standby), pgsql3 (standby).<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I have following problem: When primary (pgsql1) goes down, first standby (pgsql2) is normaly promoted as primary, but second standby (pgsql3) doesn't follow new primary, it goes to status down instead (postgresql is actualy running normaly
on pgsql3):<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">-bash-4.2$ psql -h 192.168.56.150 -p 9999 -U pgpool postgres -c "show pool_nodes"<o:p></o:p></p>
<p class="MsoNormal">Password for user pgpool:<o:p></o:p></p>
<p class="MsoNormal">node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | last_status_change<o:p></o:p></p>
<p class="MsoNormal">---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+---------------------<o:p></o:p></p>
<p class="MsoNormal">0 | pgsql1 | 5432 | down | 0.333333 | standby | 0 | false | 0 | 2019-05-31 09:51:23<o:p></o:p></p>
<p class="MsoNormal">1 | pgsql2 | 5432 | up | 0.333333 | primary | 0 | true | 0 | 2019-05-31 09:51:23<o:p></o:p></p>
<p class="MsoNormal">2 | pgsql3 | 5432 | <span style="color:red">down</span> | 0.333333 | standby | 0 | false | 0 | 2019-05-31 09:51:23<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I found this lines in the pgpool log file:<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">May 31 09:51:23 pgsql3 pgpool[4524]: [207-1] 2019-05-31 09:51:23: pid 4524: LOG: find_primary_node: primary node is 1<o:p></o:p></p>
<p class="MsoNormal">May 31 09:51:23 pgsql3 pgpool[4524]: [208-1] 2019-05-31 09:51:23: pid 4524: LOG: find_primary_node: standby node is 2<o:p></o:p></p>
<p class="MsoNormal">May 31 09:51:23 pgsql3 pgpool[4524]: [209-1] 2019-05-31 09:51:23: pid 4524: LOG: starting follow degeneration. shutdown host pgsql1(5432)<o:p></o:p></p>
<p class="MsoNormal">May 31 09:51:23 pgsql3 pgpool[4524]: [210-1] 2019-05-31 09:51:23: pid 4524: LOG: starting follow degeneration.
<span style="color:red">shutdown host pgsql3(5432)<o:p></o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">When the follow-master comes, the script checks if postgresql on server pgsql3 is running, and because it doesn't scripte exits:<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">May 31 09:51:23 pgsql3 pgpool[5034]: [214-1] 2019-05-31 09:51:23: pid 5034: LOG: execute command: /etc/pgpool-II/follow_master.sh 2 pgsql3 5432 /var/lib/pgsql/11/data 1 0 pgsql2 0 5432 /var/lib/pgsql/11/data<o:p></o:p></p>
<p class="MsoNormal">May 31 09:51:23 pgsql3 root[5077]: follow_master.sh: start: pg_basebackup for 2<o:p></o:p></p>
<p class="MsoNormal">May 31 09:51:23 pgsql3 root[5078]: + FAILED_NODE_ID=2<o:p></o:p></p>
<p class="MsoNormal">May 31 09:51:23 pgsql3 root[5078]: + FAILED_NODE_HOST=pgsql3<o:p></o:p></p>
<p class="MsoNormal">May 31 09:51:23 pgsql3 root[5078]: + FAILED_NODE_PORT=5432<o:p></o:p></p>
<p class="MsoNormal">May 31 09:51:23 pgsql3 root[5078]: + FAILED_NODE_PGDATA=/var/lib/pgsql/11/data<o:p></o:p></p>
<p class="MsoNormal">May 31 09:51:23 pgsql3 root[5078]: + NEW_MASTER_NODE_ID=1<o:p></o:p></p>
<p class="MsoNormal">May 31 09:51:23 pgsql3 root[5078]: + OLD_MASTER_NODE_ID=0<o:p></o:p></p>
<p class="MsoNormal">May 31 09:51:23 pgsql3 root[5078]: + NEW_MASTER_NODE_HOST=pgsql2<o:p></o:p></p>
<p class="MsoNormal">May 31 09:51:23 pgsql3 root[5078]: + OLD_PRIMARY_NODE_ID=0<o:p></o:p></p>
<p class="MsoNormal">May 31 09:51:23 pgsql3 root[5078]: + NEW_MASTER_NODE_PORT=5432<o:p></o:p></p>
<p class="MsoNormal">May 31 09:51:23 pgsql3 root[5078]: + NEW_MASTER_NODE_PGDATA=/var/lib/pgsql/11/data<o:p></o:p></p>
<p class="MsoNormal">May 31 09:51:23 pgsql3 root[5078]: + PGHOME=/usr/pgsql-11<o:p></o:p></p>
<p class="MsoNormal">May 31 09:51:23 pgsql3 root[5078]: + ARCHIVEDIR=/var/lib/pgsql/archivedir<o:p></o:p></p>
<p class="MsoNormal">May 31 09:51:23 pgsql3 root[5078]: + REPL_USER=repl<o:p></o:p></p>
<p class="MsoNormal">May 31 09:51:23 pgsql3 root[5078]: + PCP_USER=pgpool<o:p></o:p></p>
<p class="MsoNormal">May 31 09:51:23 pgsql3 root[5078]: + PGPOOL_PATH=/usr/bin<o:p></o:p></p>
<p class="MsoNormal">May 31 09:51:23 pgsql3 root[5078]: + PCP_PORT=9898<o:p></o:p></p>
<p class="MsoNormal">May 31 09:51:23 pgsql3 root[5078]: + logger -i -p local1.info follow_master.sh: start: pg_basebackup for 2<o:p></o:p></p>
<p class="MsoNormal">May 31 09:51:23 pgsql3 root[5078]: + ssh -T -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null postgres@pgsql3 /usr/pgsql-11/bin/pg_ctl -w -D /var/lib/pgsql/11/data status<o:p></o:p></p>
<p class="MsoNormal">May 31 09:51:23 pgsql3 root[5078]: + [[ 255 -eq 0 ]]<o:p></o:p></p>
<p class="MsoNormal">May 31 09:51:23 pgsql3 root[5078]: + logger -i -p local1.info follow_master.sh: failed_nod_id=2 is not running. skipping follow master command.<o:p></o:p></p>
<p class="MsoNormal">May 31 09:51:23 pgsql3 root[5082]: <span style="color:red">follow_master.sh: failed_nod_id=2 is not running. skipping follow master command.</span><o:p></o:p></p>
<p class="MsoNormal">May 31 09:51:23 pgsql3 root[5078]: + exit 0<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Postgres log file on pgsql3:<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">2019-05-31 09:48:11.142 CEST [4764] LOG: started streaming WAL from primary at 0/F000000 on timeline 1<o:p></o:p></p>
<p class="MsoNormal">WARNING: terminating connection because of crash of another server process<o:p></o:p></p>
<p class="MsoNormal">DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.<o:p></o:p></p>
<p class="MsoNormal">HINT: In a moment you should be able to reconnect to the database and repeat your command.<o:p></o:p></p>
<p class="MsoNormal">2019-05-31 09:51:18.023 CEST [4764] FATAL: could not receive data from WAL stream: server closed the connection unexpectedly<o:p></o:p></p>
<p class="MsoNormal"> This probably means the server terminated abnormally<o:p></o:p></p>
<p class="MsoNormal"> before or while processing the request.<o:p></o:p></p>
<p class="MsoNormal">Host key verification failed.<o:p></o:p></p>
<p class="MsoNormal">2019-05-31 09:51:18.156 CEST [4747] LOG: invalid record length at 0/F000060: wanted 24, got 0<o:p></o:p></p>
<p class="MsoNormal">2019-05-31 09:51:18.160 CEST [5012] FATAL: could not connect to the primary server: could not connect to server: Connection refused<o:p></o:p></p>
<p class="MsoNormal"> Is the server running on host "pgsql1" (192.168.56.111) and accepting<o:p></o:p></p>
<p class="MsoNormal"> TCP/IP connections on port 5432?<o:p></o:p></p>
<p class="MsoNormal">Host key verification failed.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">What is wrong? Can you please help me out of this situation.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal" style="margin-bottom:10.0pt;line-height:115%;text-autospace:ideograph-other;vertical-align:baseline">
Best regards!<o:p></o:p></p>
<table class="MsoNormalTable" border="0" cellspacing="0" cellpadding="0" width="0" style="border-collapse:collapse">
<tbody>
<tr style="height:58.6pt">
<td width="15" style="width:11.45pt;padding:0cm 0cm 0cm 0cm;height:58.6pt">
<p class="MsoNormal" align="center" style="text-align:center"><span style="font-size:12.0pt;mso-fareast-language:SL"><img border="0" width="12" height="71" style="width:.125in;height:.7395in" id="Picture_x0020_7" src="cid:image002.png@01D5179C.40A26820" alt="izum"><o:p></o:p></span></p>
</td>
<td width="542" style="width:406.75pt;padding:0cm 0cm 0cm 2.85pt;height:58.6pt">
<p class="MsoNormal" style="margin-left:1.35pt"><span style="font-size:12.0pt;color:#548DD4;mso-fareast-language:SL">Domen Šetar</span><span style="font-size:14.0pt;color:#1F497D;mso-fareast-language:SL"><br>
</span><i><span lang="EN-GB" style="font-size:10.0pt;color:#333333;mso-fareast-language:SL">Computer Systems Support</span></i><span style="font-size:12.0pt;color:#1F497D;mso-fareast-language:SL"><br>
</span><span style="font-size:10.0pt;color:#939598;mso-fareast-language:SL">IZUM – Institute of Information Science</span><span style="font-size:10.0pt;color:#F57E20;mso-fareast-language:SL">
</span><span style="font-size:10.0pt;color:#939598;mso-fareast-language:SL">| Prešernova ulica 17 | 2000 Maribor |</span><span style="font-size:10.0pt;color:#F57E20;mso-fareast-language:SL">
</span><span style="font-size:10.0pt;color:#939598;mso-fareast-language:SL">Slovenia</span><i><span style="font-size:10.0pt;color:#333333;mso-fareast-language:SL"><br>
</span></i><span style="font-size:10.0pt;color:#939598;mso-fareast-language:SL">T: +386 2 25 20 339</span><span style="font-size:10.0pt;color:#F57E20;mso-fareast-language:SL">
</span><span style="font-size:10.0pt;color:#939598;mso-fareast-language:SL">| M: +386 41 676 342</span><span style="font-size:10.0pt;color:#F57E20;mso-fareast-language:SL">
</span><span style="font-size:10.0pt;color:#939598;mso-fareast-language:SL">| </span>
<span style="font-size:12.0pt;font-family:"Times New Roman",serif;mso-fareast-language:SL"><a href="http://www.izum.si/"><span style="font-size:10.0pt;font-family:"Calibri",sans-serif;color:#548DD4">www.izum.si</span></a></span><span style="font-size:10.0pt;color:#548DD4;mso-fareast-language:SL">
</span><span style="font-size:10.0pt;color:#939598;mso-fareast-language:SL">|</span><span style="font-size:10.0pt;color:#73BD9A;mso-fareast-language:SL">
</span><span style="font-size:10.0pt;mso-fareast-language:SL"><a href="mailto:domen.setar@izum.si"><span style="color:blue">domen.setar@izum.si</span></a></span><span lang="EN-GB" style="font-size:12.0pt;mso-fareast-language:SL"><o:p></o:p></span></p>
</td>
</tr>
</tbody>
</table>
<p class="MsoNormal"><span style="mso-fareast-language:SL"><o:p> </o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</body>
</html>