<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)">
<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.StylE-mailovZprvy17
{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="CS" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal"><a name="_Hlk30747702">Hello,<o:p></o:p></a></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">We’ve recently switched our pgPool/PostgreSQL cluster from the native replication to MASTER-SLAVE replication (streaming replication via replication slots). Everything seems to
be working just fine (like failover, online recovery or load balancing), but if I query the node status with pcp_node_info command, the information about replication state does not get displayed:<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">[pgpool@AISGPGP01 ~]$ pcp_node_info -h /home/pgpool/pcp_socket_dir --no-password -v 0<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">Hostname : aisgdbd01.cca.cz<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">Port : 5432<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">Status : 2<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">Weight : 0.500000<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">Status Name : up<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">Role : primary<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">Replication Delay : 0<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">Replication State :<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">Replication Sync State :<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">Last Status Change : 2021-02-01 16:07:01<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">The same can be observed, when I try to query the node status with SQL command:<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">aisgdvyv=# SHOW POOL_NODES;<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">---------+------------------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">0 | aisgdbd01.cca.cz | 5432 | up | 0.500000 | primary | 1507331 | true | 0 | | | 2021-02-01 16:07:01<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">1 | aisgdbd02.cca.cz | 5432 | up | 0.500000 | standby | 806751 | false | 0 | | | 2021-02-01 16:08:00<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">If I try to directly query the pg_stat_replication view on the PRIMARY, I get this result:<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">aisgdvyv=# SELECT * FROM pg_stat_replication;<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">-[ RECORD 1 ]----+------------------------------<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">pid | 4429<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">usesysid | 16393<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">usename | replication<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">application_name | walreceiver<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">client_addr | 172.20.15.67<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">client_hostname |<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">client_port | 32944<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">backend_start | 2021-02-01 16:07:55.678388+01<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">backend_xmin |<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">state | streaming<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">sent_lsn | E5/10170BD8<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">write_lsn | E5/10170BD8<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">flush_lsn | E5/10170BD8<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">replay_lsn | E5/10170BD8<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">write_lag | 00:00:00.000208<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">flush_lag | 00:00:00.000907<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">replay_lag | 00:00:00.000972<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">sync_priority | 0<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">sync_state | async<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">It seems like pgPool cannot access the information. I have created a user „pgpool_streamcheck“ as a member of the group „pg_monitor“ to be used for the replication delay check:<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">aisgdvyv=# \dg<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"> List of roles<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"> Role name | Attributes | Member of<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">---------------------+------------------------------------------------------------+-----------------------<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">…<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">pgpool_healthcheck | | {}<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">pgpool_streamcheck | | {pg_monitor}<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">replication | Replication | {}<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">…<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">From the logs of pgPool it seems to be able to query the delay since I can occasionally see it exceeds the set threshold:<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">Feb 2 22:39:08 AISGPGP01 pgpool[28300]: [2205-1] 2021-02-02 22:39:08 ::: APP - [No Connection] :A: DB - [No Connection] :D: USER - [No Connection] :U: |LOG: Replication of node:1
is behind 8224 bytes from the primary server (node:0)<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">Feb 2 22:39:08 AISGPGP01 pgpool[28300]: [2205-2] 2021-02-02 22:39:08 ::: APP - [No Connection] :A: DB - [No Connection] :D: USER - [No Connection] :U: |CONTEXT: while checking
replication time lag<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">I am sending you the pgpool.conf file as an attachment. Could you please look it over and tell me, if I have set something wrong? We are using the pgPool version 4.1.5 and the PostgreSQL
version 11.10.<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">Thank you for your time.<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702"><span lang="EN-US">With best regards,<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_Hlk30747702">Vladimír Láznička<o:p></o:p></span></p>
</div>
</body>
</html>