<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
</head>
<body>
<div>
<div>
<div dir="ltr">sorry, the difference in the names is due to me changing them for public viewing. they do match in my lab. </div>
</div>
<div id="ms-outlook-mobile-signature">
<div></div>
</div>
</div>
<hr style="display:inline-block;width:98%" tabindex="-1">
<div id="divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" style="font-size:11pt" color="#000000"><b>From:</b> Bo Peng <pengbo@sraoss.co.jp><br>
<b>Sent:</b> Wednesday, March 29, 2023 10:04:33 PM<br>
<b>To:</b> Todd Stein <todd.stein@microfocus.com><br>
<b>Cc:</b> pgpool-general@pgpool.net <pgpool-general@pgpool.net><br>
<b>Subject:</b> Re: [pgpool-general: 8671] replication state not visible</font>
<div> </div>
</div>
<div class="BodyFragment"><font size="2"><span style="font-size:11pt;">
<div class="PlainText">Hello,<br>
<br>
> [postgres@catvmtspg02a DBToolkit]$ psql -h 16.78.121.42 -U pgpool postgres -p 9999 -c "SELECT * FROM pg_stat_replication"<br>
> Password for user pgpool:<br>
> pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | re<br>
> play_lag | sync_priority | sync_state | reply_time<br>
> --------+----------+---------+------------------------------+--------------+-----------------+-------------+-------------------------------+--------------+-----------+------------+------------+------------+------------+-----------+-----------+---<br>
> ---------+---------------+------------+-------------------------------<br>
> 381423 | 16385 | repl | catvmtspg02b.sac.swinfra.net | 16.78.121.44 | | 38634 | 2023-03-28 16:45:34.183458-04 | | streaming | 2/FD0001C0 | 2/FD0001C0 | 2/FD0001C0 | 2/FD0001C0 | | |<br>
> | 0 | async | 2023-03-29 07:50:03.762755-04<br>
> 381531 | 16385 | repl | catvmtspg02c.sac.swinfra.net | 16.78.121.45 | | 58088 | 2023-03-28 16:45:44.223045-04 | | streaming | 2/FD0001C0 | 2/FD0001C0 | 2/FD0001C0 | 2/FD0001C0 | | |<br>
> | 0 | async | 2023-03-29 07:50:03.928561-04<br>
> (2 rows)<br>
<br>
The application name is:<br>
<br>
catvmtspg02b.sac.swinfra.net<br>
catvmtspg02c.sac.swinfra.net<br>
<br>
Your configurations in pgpool.conf are:<br>
<br>
backend_application_name1 = 'catvmtspg02b.domain.net'<br>
backend_application_name2 = 'catvmtspg02c.domain.net'<br>
<br>
You need to specify backend_application_name that matches the applicatoin names.<br>
<br>
On Wed, 29 Mar 2023 11:51:22 +0000<br>
Todd Stein <todd.stein@microfocus.com> wrote:<br>
<br>
> Hi Bo,<br>
> Thank you for your response. I really need to solve this one !!!<br>
> <br>
> [postgres@catvmtspg02a DBToolkit]$ psql -h 16.78.121.42 -U pgpool -p 9999 -c "SELECT * FROM pg_stat_replication"<br>
> Password for user pgpool:<br>
> psql: error: connection to server at "16.78.121.42", port 9999 failed: FATAL: unable to get session context<br>
> [postgres@catvmtspg02a DBToolkit]$<br>
> <br>
> Here are a few more related queries:<br>
> <br>
> [postgres@catvmtspg02a DBToolkit]$ psql -h 16.78.121.1 -U pgpool -p 9999 -c "show pool_nodes"<br>
> Password for user pgpool:<br>
> psql: error: connection to server at "16.78.121.1", port 9999 failed: FATAL: unable to get session context<br>
> [postgres@catvmtspg02a DBToolkit]$ psql -h 16.78.121.1 -U pgpool postgres -p 9999 -c "show pool_nodes"<br>
> Password for user pgpool:<br>
> node_id | hostname | port | status | pg_status | lb_weight | role | pg_role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change<br>
> ---------+------------------------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------<br>
> 0 | catvmtspg02a.sac.swinfra.net | 5432 | up | up | 0.333333 | primary | primary | 2 | true | 0 | | | 2023-03-28 16:40:00<br>
> 1 | catvmtspg02b.sac.swinfra.net | 5432 | up | up | 0.333333 | standby | standby | 0 | false | 0 | | | 2023-03-28 16:40:00<br>
> 2 | catvmtspg02c.sac.swinfra.net | 5432 | up | up | 0.333333 | standby | standby | 0 | false | 0 | | | 2023-03-28 16:40:00<br>
> (3 rows)<br>
> <br>
> [postgres@catvmtspg02a DBToolkit]$ psql -h 16.78.121.42 -U pgpool postgres -p 9999 -c "SELECT * FROM pg_stat_replication"<br>
> Password for user pgpool:<br>
> pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | re<br>
> play_lag | sync_priority | sync_state | reply_time<br>
> --------+----------+---------+------------------------------+--------------+-----------------+-------------+-------------------------------+--------------+-----------+------------+------------+------------+------------+-----------+-----------+---<br>
> ---------+---------------+------------+-------------------------------<br>
> 381423 | 16385 | repl | catvmtspg02b.sac.swinfra.net | 16.78.121.44 | | 38634 | 2023-03-28 16:45:34.183458-04 | | streaming | 2/FD0001C0 | 2/FD0001C0 | 2/FD0001C0 | 2/FD0001C0 | | |<br>
> | 0 | async | 2023-03-29 07:50:03.762755-04<br>
> 381531 | 16385 | repl | catvmtspg02c.sac.swinfra.net | 16.78.121.45 | | 58088 | 2023-03-28 16:45:44.223045-04 | | streaming | 2/FD0001C0 | 2/FD0001C0 | 2/FD0001C0 | 2/FD0001C0 | | |<br>
> | 0 | async | 2023-03-29 07:50:03.928561-04<br>
> (2 rows)<br>
> <br>
> <br>
> <br>
> Regards,<br>
> <br>
> Todd Stein<br>
> <br>
> -----Original Message-----<br>
> From: Bo Peng <pengbo@sraoss.co.jp> <br>
> Sent: Wednesday, March 29, 2023 1:24 AM<br>
> To: Todd Stein <todd.stein@microfocus.com><br>
> Cc: pgpool-general@pgpool.net<br>
> Subject: Re: [pgpool-general: 8671] replication state not visible<br>
> <br>
> Hello,<br>
> <br>
> Pgpool executes "SELECT * FROM pg_stat_replication" to retrieve replication_state and replication_sync_state.<br>
> <br>
> Could you try to connect to PostgreSQL primary using pgpool user and run "SELECT * FROM pg_stat_replication"<br>
> on the server where pgpool is running?<br>
> <br>
> psql -h <PostgreSQL primary> -U pgpool -p <PostgreSQL port> -c "SELECT * FROM pg_stat_replication"<br>
> <br>
> <br>
> On Mon, 27 Mar 2023 12:37:06 +0000<br>
> Todd Stein <todd.stein@microfocus.com> wrote:<br>
> <br>
> > Thank you.<br>
> > Attached is a zip file labeled as a txt file containing config files and logs.<br>
> > sr_check_user is specified as pgpool. The pgpool user is in pg_monitor group.<br>
> > <br>
> > <br>
> > <br>
> > Regards,<br>
> > <br>
> > Todd Stein<br>
> > <br>
> > -----Original Message-----<br>
> > From: Bo Peng <pengbo@sraoss.co.jp><br>
> > Sent: Monday, March 27, 2023 8:28 AM<br>
> > To: Todd Stein <todd.stein@microfocus.com><br>
> > Cc: pgpool-general@pgpool.net<br>
> > Subject: Re: [pgpool-general: 8671] replication state not visible<br>
> > <br>
> > Hello,<br>
> > <br>
> > You also need to make sure the user specified in sr_check_user is PostgreSQL super user or or in pg_monitor group.<br>
> > <br>
> > Grant pg_monitor to user:<br>
> > <br>
> > GRANT pg_monitor TO <username>;<br>
> > <br>
> > <br>
> > On Mon, 27 Mar 2023 11:29:50 +0000<br>
> > Todd Stein <todd.stein@microfocus.com> wrote:<br>
> > <br>
> > > it is configured. I sent config files and logs on a different <br>
> > > thread ________________________________<br>
> > > From: Bo Peng <pengbo@sraoss.co.jp><br>
> > > Sent: Monday, March 27, 2023 1:57:05 AM<br>
> > > To: Todd Stein <todd.stein@microfocus.com><br>
> > > Cc: pgpool-general@pgpool.net <pgpool-general@pgpool.net><br>
> > > Subject: Re: [pgpool-general: 8671] replication state not visible<br>
> > > <br>
> > > Hello,<br>
> > > <br>
> > > On Fri, 24 Mar 2023 15:38:03 +0000<br>
> > > Todd Stein <todd.stein@microfocus.com> wrote:<br>
> > > <br>
> > > > While I do have the application field populated, I am not seeing the replication state in the show pool_nodes table.<br>
> > > <br>
> > > You need to configure "backend_application_name" parameter.<br>
> > > <br>
> > > Please see the following doc:<br>
> > > <a href="https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww%2F&data=05%7C01%7Ctodd.stein%40microfocus.com%7Cd3e018bb390d4ff32a1408db30c31e79%7C856b813c16e549a585ec6f081e13b527%7C0%7C0%7C638157386819489588%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=IG0XqEqTkMkM%2FvaQng0fPbNubZ%2BssRtQUAerSmQFy%2Fw%3D&reserved=0">
https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww%2F&data=05%7C01%7Ctodd.stein%40microfocus.com%7Cd3e018bb390d4ff32a1408db30c31e79%7C856b813c16e549a585ec6f081e13b527%7C0%7C0%7C638157386819489588%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=IG0XqEqTkMkM%2FvaQng0fPbNubZ%2BssRtQUAerSmQFy%2Fw%3D&reserved=0</a>.<br>
> > > pgpool.net%2Fdocs%2Flatest%2Fen%2Fhtml%2Fruntime-config-backend-sett<br>
> > > in <br>
> > > gs.html%23GUC-BACKEND-APPLICATION-NAME&data=05%7C01%7Ctodd.stein%40m<br>
> > > ic<br>
> > > rofocus.com%7C9276e0e0ddb64c804e0108db2ebeb357%7C856b813c16e549a585e<br>
> > > c6 <br>
> > > f081e13b527%7C0%7C0%7C638155168831102214%7CUnknown%7CTWFpbGZsb3d8eyJ<br>
> > > WI<br>
> > > joiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000<br>
> > > %7 <br>
> > > C%7C%7C&sdata=DrL8VzVxtQi%2FnGr5m1oK526MYhUq5wYLL2xhbhj6hAE%3D&reser<br>
> > > ve<br>
> > > d=0<br>
> > > <br>
> > > > [postgres@catvmtspg02a DBToolkit]$ psql -x -h 10.78.121.1 -p 9999 -U pgpool postgres -c "show pool_nodes"<br>
> > > > Password for user pgpool:<br>
> > > > -[ RECORD 1 ]----------+-----------------------------<br>
> > > > node_id | 0<br>
> > > > hostname | catvmtspg02a.domain.net<br>
> > > > port | 5432<br>
> > > > status | up<br>
> > > > pg_status | up<br>
> > > > lb_weight | 0.333333<br>
> > > > role | primary<br>
> > > > pg_role | primary<br>
> > > > select_cnt | 0<br>
> > > > load_balance_node | true<br>
> > > > replication_delay | 0<br>
> > > > replication_state |<br>
> > > > replication_sync_state |<br>
> > > > last_status_change | 2023-03-24 10:52:59<br>
> > > > -[ RECORD 2 ]----------+-----------------------------<br>
> > > > node_id | 1<br>
> > > > hostname | catvmtspg02b.domain.net<br>
> > > > port | 5432<br>
> > > > status | up<br>
> > > > pg_status | up<br>
> > > > lb_weight | 0.333333<br>
> > > > role | standby<br>
> > > > pg_role | standby<br>
> > > > select_cnt | 0<br>
> > > > load_balance_node | false<br>
> > > > replication_delay | 0<br>
> > > > replication_state |<br>
> > > > replication_sync_state |<br>
> > > > last_status_change | 2023-03-24 10:52:59<br>
> > > > -[ RECORD 3 ]----------+-----------------------------<br>
> > > > node_id | 2<br>
> > > > hostname | catvmtspg02c.domain.net<br>
> > > > port | 5432<br>
> > > > status | up<br>
> > > > pg_status | up<br>
> > > > lb_weight | 0.333333<br>
> > > > role | standby<br>
> > > > pg_role | standby<br>
> > > > select_cnt | 0<br>
> > > > load_balance_node | false<br>
> > > > replication_delay | 0<br>
> > > > replication_state |<br>
> > > > replication_sync_state |<br>
> > > > last_status_change | 2023-03-24 10:52:59<br>
> > > ><br>
> > > > [postgres@catvmtspg02a DBToolkit]$ [postgres@catvmtspg02a <br>
> > > > DBToolkit]$ psql -c "SELECT * FROM pg_replication_slots;" -x -[ <br>
> > > > RECORD 1 ]-------+-----------------------------<br>
> > > > slot_name | catvmtspg02c_domain_net<br>
> > > > plugin |<br>
> > > > slot_type | physical<br>
> > > > datoid |<br>
> > > > database |<br>
> > > > temporary | f<br>
> > > > active | t<br>
> > > > active_pid | 1703<br>
> > > > xmin |<br>
> > > > catalog_xmin |<br>
> > > > restart_lsn | 2/2A001A88<br>
> > > > confirmed_flush_lsn |<br>
> > > > wal_status | reserved<br>
> > > > safe_wal_size |<br>
> > > > two_phase | f<br>
> > > > -[ RECORD 2 ]-------+-----------------------------<br>
> > > > slot_name | catvmtspg02b_domain_net<br>
> > > > plugin |<br>
> > > > slot_type | physical<br>
> > > > datoid |<br>
> > > > database |<br>
> > > > temporary | f<br>
> > > > active | t<br>
> > > > active_pid | 1822<br>
> > > > xmin |<br>
> > > > catalog_xmin |<br>
> > > > restart_lsn | 2/2A001A88<br>
> > > > confirmed_flush_lsn |<br>
> > > > wal_status | reserved<br>
> > > > safe_wal_size |<br>
> > > > two_phase | f<br>
> > > ><br>
> > > ><br>
> > > ><br>
> > > ><br>
> > > > Regards,<br>
> > > ><br>
> > > > Todd Stein<br>
> > > > OpsBridge Technical Success<br>
> > > > OpenText<br>
> > > > (Cell) +1 (941) 248-8752<br>
> > > > tstein2@opentext.com<mailto:tstein2@opentext.com><br>
> > > ><br>
> > > <br>
> > > <br>
> > > --<br>
> > > Bo Peng <pengbo@sraoss.co.jp><br>
> > > SRA OSS LLC<br>
> > > <a href="https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww%2F&data=05%7C01%7Ctodd.stein%40microfocus.com%7Cd3e018bb390d4ff32a1408db30c31e79%7C856b813c16e549a585ec6f081e13b527%7C0%7C0%7C638157386819489588%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=IG0XqEqTkMkM%2FvaQng0fPbNubZ%2BssRtQUAerSmQFy%2Fw%3D&reserved=0">
https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww%2F&data=05%7C01%7Ctodd.stein%40microfocus.com%7Cd3e018bb390d4ff32a1408db30c31e79%7C856b813c16e549a585ec6f081e13b527%7C0%7C0%7C638157386819489588%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=IG0XqEqTkMkM%2FvaQng0fPbNubZ%2BssRtQUAerSmQFy%2Fw%3D&reserved=0</a>.<br>
> > > sraoss.co.jp%2F&data=05%7C01%7Ctodd.stein%40microfocus.com%7C9276e0e<br>
> > > 0d<br>
> > > db64c804e0108db2ebeb357%7C856b813c16e549a585ec6f081e13b527%7C0%7C0%7<br>
> > > C6 <br>
> > > 38155168831102214%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIj<br>
> > > oi <br>
> > > V2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=xp5mkf%<br>
> > > 2F<br>
> > > VHbxnYwg8ZWX9kf%2BZfwQDgHXdwYrXeOWbDoc%3D&reserved=0<br>
> > <br>
> > <br>
> > --<br>
> > Bo Peng <pengbo@sraoss.co.jp><br>
> > SRA OSS LLC<br>
> > <a href="https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww%2F&data=05%7C01%7Ctodd.stein%40microfocus.com%7Cd3e018bb390d4ff32a1408db30c31e79%7C856b813c16e549a585ec6f081e13b527%7C0%7C0%7C638157386819489588%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=IG0XqEqTkMkM%2FvaQng0fPbNubZ%2BssRtQUAerSmQFy%2Fw%3D&reserved=0">
https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww%2F&data=05%7C01%7Ctodd.stein%40microfocus.com%7Cd3e018bb390d4ff32a1408db30c31e79%7C856b813c16e549a585ec6f081e13b527%7C0%7C0%7C638157386819489588%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=IG0XqEqTkMkM%2FvaQng0fPbNubZ%2BssRtQUAerSmQFy%2Fw%3D&reserved=0</a>.<br>
> > sraoss.co.jp%2F&data=05%7C01%7Ctodd.stein%40microfocus.com%7Cb3f6344f7<br>
> > fc04ae28fc108db3015cc4d%7C856b813c16e549a585ec6f081e13b527%7C0%7C0%7C6<br>
> > 38156642410607733%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoi<br>
> > V2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=bvXqn9y1J<br>
> > ZBxGPm716SE2JdUukfFxZMc92H9sumElQY%3D&reserved=0<br>
> <br>
> <br>
> --<br>
> Bo Peng <pengbo@sraoss.co.jp><br>
> SRA OSS LLC<br>
> <a href="https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.sraoss.co.jp%2F&data=05%7C01%7Ctodd.stein%40microfocus.com%7Cd3e018bb390d4ff32a1408db30c31e79%7C856b813c16e549a585ec6f081e13b527%7C0%7C0%7C638157386819489588%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=SDuLnGebJaAHVe2Qk0CaHp%2FHz4a2ziacV0x5uxBDLbI%3D&reserved=0">
https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.sraoss.co.jp%2F&data=05%7C01%7Ctodd.stein%40microfocus.com%7Cd3e018bb390d4ff32a1408db30c31e79%7C856b813c16e549a585ec6f081e13b527%7C0%7C0%7C638157386819489588%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=SDuLnGebJaAHVe2Qk0CaHp%2FHz4a2ziacV0x5uxBDLbI%3D&reserved=0</a><br>
<br>
<br>
-- <br>
Bo Peng <pengbo@sraoss.co.jp><br>
SRA OSS LLC<br>
<a href="https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.sraoss.co.jp%2F&data=05%7C01%7Ctodd.stein%40microfocus.com%7Cd3e018bb390d4ff32a1408db30c31e79%7C856b813c16e549a585ec6f081e13b527%7C0%7C0%7C638157386819489588%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=SDuLnGebJaAHVe2Qk0CaHp%2FHz4a2ziacV0x5uxBDLbI%3D&reserved=0">https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.sraoss.co.jp%2F&data=05%7C01%7Ctodd.stein%40microfocus.com%7Cd3e018bb390d4ff32a1408db30c31e79%7C856b813c16e549a585ec6f081e13b527%7C0%7C0%7C638157386819489588%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=SDuLnGebJaAHVe2Qk0CaHp%2FHz4a2ziacV0x5uxBDLbI%3D&reserved=0</a><br>
</div>
</span></font></div>
</body>
</html>