[pgpool-general: 8685] Re: replication state not visible
Todd Stein
todd.stein at microfocus.com
Wed Mar 29 20:51:22 JST 2023
Hi Bo,
Thank you for your response. I really need to solve this one !!!
[postgres at catvmtspg02a DBToolkit]$ psql -h 16.78.121.42 -U pgpool -p 9999 -c "SELECT * FROM pg_stat_replication"
Password for user pgpool:
psql: error: connection to server at "16.78.121.42", port 9999 failed: FATAL: unable to get session context
[postgres at catvmtspg02a DBToolkit]$
Here are a few more related queries:
[postgres at catvmtspg02a DBToolkit]$ psql -h 16.78.121.1 -U pgpool -p 9999 -c "show pool_nodes"
Password for user pgpool:
psql: error: connection to server at "16.78.121.1", port 9999 failed: FATAL: unable to get session context
[postgres at catvmtspg02a DBToolkit]$ psql -h 16.78.121.1 -U pgpool postgres -p 9999 -c "show pool_nodes"
Password for user pgpool:
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
---------+------------------------------+------+--------+-----------+-----------+---------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
0 | catvmtspg02a.sac.swinfra.net | 5432 | up | up | 0.333333 | primary | primary | 2 | true | 0 | | | 2023-03-28 16:40:00
1 | catvmtspg02b.sac.swinfra.net | 5432 | up | up | 0.333333 | standby | standby | 0 | false | 0 | | | 2023-03-28 16:40:00
2 | catvmtspg02c.sac.swinfra.net | 5432 | up | up | 0.333333 | standby | standby | 0 | false | 0 | | | 2023-03-28 16:40:00
(3 rows)
[postgres at catvmtspg02a DBToolkit]$ psql -h 16.78.121.42 -U pgpool postgres -p 9999 -c "SELECT * FROM pg_stat_replication"
Password for user pgpool:
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
play_lag | sync_priority | sync_state | reply_time
--------+----------+---------+------------------------------+--------------+-----------------+-------------+-------------------------------+--------------+-----------+------------+------------+------------+------------+-----------+-----------+---
---------+---------------+------------+-------------------------------
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 | | |
| 0 | async | 2023-03-29 07:50:03.762755-04
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 | | |
| 0 | async | 2023-03-29 07:50:03.928561-04
(2 rows)
Regards,
Todd Stein
-----Original Message-----
From: Bo Peng <pengbo at sraoss.co.jp>
Sent: Wednesday, March 29, 2023 1:24 AM
To: Todd Stein <todd.stein at microfocus.com>
Cc: pgpool-general at pgpool.net
Subject: Re: [pgpool-general: 8671] replication state not visible
Hello,
Pgpool executes "SELECT * FROM pg_stat_replication" to retrieve replication_state and replication_sync_state.
Could you try to connect to PostgreSQL primary using pgpool user and run "SELECT * FROM pg_stat_replication"
on the server where pgpool is running?
psql -h <PostgreSQL primary> -U pgpool -p <PostgreSQL port> -c "SELECT * FROM pg_stat_replication"
On Mon, 27 Mar 2023 12:37:06 +0000
Todd Stein <todd.stein at microfocus.com> wrote:
> Thank you.
> Attached is a zip file labeled as a txt file containing config files and logs.
> sr_check_user is specified as pgpool. The pgpool user is in pg_monitor group.
>
>
>
> Regards,
>
> Todd Stein
>
> -----Original Message-----
> From: Bo Peng <pengbo at sraoss.co.jp>
> Sent: Monday, March 27, 2023 8:28 AM
> To: Todd Stein <todd.stein at microfocus.com>
> Cc: pgpool-general at pgpool.net
> Subject: Re: [pgpool-general: 8671] replication state not visible
>
> Hello,
>
> You also need to make sure the user specified in sr_check_user is PostgreSQL super user or or in pg_monitor group.
>
> Grant pg_monitor to user:
>
> GRANT pg_monitor TO <username>;
>
>
> On Mon, 27 Mar 2023 11:29:50 +0000
> Todd Stein <todd.stein at microfocus.com> wrote:
>
> > it is configured. I sent config files and logs on a different
> > thread ________________________________
> > From: Bo Peng <pengbo at sraoss.co.jp>
> > Sent: Monday, March 27, 2023 1:57:05 AM
> > To: Todd Stein <todd.stein at microfocus.com>
> > Cc: pgpool-general at pgpool.net <pgpool-general at pgpool.net>
> > Subject: Re: [pgpool-general: 8671] replication state not visible
> >
> > Hello,
> >
> > On Fri, 24 Mar 2023 15:38:03 +0000
> > Todd Stein <todd.stein at microfocus.com> wrote:
> >
> > > While I do have the application field populated, I am not seeing the replication state in the show pool_nodes table.
> >
> > You need to configure "backend_application_name" parameter.
> >
> > Please see the following doc:
> > https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww%2F&data=05%7C01%7Ctodd.stein%40microfocus.com%7Cb3f6344f7fc04ae28fc108db3015cc4d%7C856b813c16e549a585ec6f081e13b527%7C0%7C0%7C638156642410607733%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=YCuI4xooy7nwlePs%2BMYtAsDCJkQB8Iq1HAAPpfVt%2FfY%3D&reserved=0.
> > pgpool.net%2Fdocs%2Flatest%2Fen%2Fhtml%2Fruntime-config-backend-sett
> > in
> > gs.html%23GUC-BACKEND-APPLICATION-NAME&data=05%7C01%7Ctodd.stein%40m
> > ic
> > rofocus.com%7C9276e0e0ddb64c804e0108db2ebeb357%7C856b813c16e549a585e
> > c6
> > f081e13b527%7C0%7C0%7C638155168831102214%7CUnknown%7CTWFpbGZsb3d8eyJ
> > WI
> > joiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000
> > %7
> > C%7C%7C&sdata=DrL8VzVxtQi%2FnGr5m1oK526MYhUq5wYLL2xhbhj6hAE%3D&reser
> > ve
> > d=0
> >
> > > [postgres at catvmtspg02a DBToolkit]$ psql -x -h 10.78.121.1 -p 9999 -U pgpool postgres -c "show pool_nodes"
> > > Password for user pgpool:
> > > -[ RECORD 1 ]----------+-----------------------------
> > > node_id | 0
> > > hostname | catvmtspg02a.domain.net
> > > port | 5432
> > > status | up
> > > pg_status | up
> > > lb_weight | 0.333333
> > > role | primary
> > > pg_role | primary
> > > select_cnt | 0
> > > load_balance_node | true
> > > replication_delay | 0
> > > replication_state |
> > > replication_sync_state |
> > > last_status_change | 2023-03-24 10:52:59
> > > -[ RECORD 2 ]----------+-----------------------------
> > > node_id | 1
> > > hostname | catvmtspg02b.domain.net
> > > port | 5432
> > > status | up
> > > pg_status | up
> > > lb_weight | 0.333333
> > > role | standby
> > > pg_role | standby
> > > select_cnt | 0
> > > load_balance_node | false
> > > replication_delay | 0
> > > replication_state |
> > > replication_sync_state |
> > > last_status_change | 2023-03-24 10:52:59
> > > -[ RECORD 3 ]----------+-----------------------------
> > > node_id | 2
> > > hostname | catvmtspg02c.domain.net
> > > port | 5432
> > > status | up
> > > pg_status | up
> > > lb_weight | 0.333333
> > > role | standby
> > > pg_role | standby
> > > select_cnt | 0
> > > load_balance_node | false
> > > replication_delay | 0
> > > replication_state |
> > > replication_sync_state |
> > > last_status_change | 2023-03-24 10:52:59
> > >
> > > [postgres at catvmtspg02a DBToolkit]$ [postgres at catvmtspg02a
> > > DBToolkit]$ psql -c "SELECT * FROM pg_replication_slots;" -x -[
> > > RECORD 1 ]-------+-----------------------------
> > > slot_name | catvmtspg02c_domain_net
> > > plugin |
> > > slot_type | physical
> > > datoid |
> > > database |
> > > temporary | f
> > > active | t
> > > active_pid | 1703
> > > xmin |
> > > catalog_xmin |
> > > restart_lsn | 2/2A001A88
> > > confirmed_flush_lsn |
> > > wal_status | reserved
> > > safe_wal_size |
> > > two_phase | f
> > > -[ RECORD 2 ]-------+-----------------------------
> > > slot_name | catvmtspg02b_domain_net
> > > plugin |
> > > slot_type | physical
> > > datoid |
> > > database |
> > > temporary | f
> > > active | t
> > > active_pid | 1822
> > > xmin |
> > > catalog_xmin |
> > > restart_lsn | 2/2A001A88
> > > confirmed_flush_lsn |
> > > wal_status | reserved
> > > safe_wal_size |
> > > two_phase | f
> > >
> > >
> > >
> > >
> > > Regards,
> > >
> > > Todd Stein
> > > OpsBridge Technical Success
> > > OpenText
> > > (Cell) +1 (941) 248-8752
> > > tstein2 at opentext.com<mailto:tstein2 at opentext.com>
> > >
> >
> >
> > --
> > Bo Peng <pengbo at sraoss.co.jp>
> > SRA OSS LLC
> > https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww%2F&data=05%7C01%7Ctodd.stein%40microfocus.com%7Cb3f6344f7fc04ae28fc108db3015cc4d%7C856b813c16e549a585ec6f081e13b527%7C0%7C0%7C638156642410607733%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=YCuI4xooy7nwlePs%2BMYtAsDCJkQB8Iq1HAAPpfVt%2FfY%3D&reserved=0.
> > sraoss.co.jp%2F&data=05%7C01%7Ctodd.stein%40microfocus.com%7C9276e0e
> > 0d
> > db64c804e0108db2ebeb357%7C856b813c16e549a585ec6f081e13b527%7C0%7C0%7
> > C6
> > 38155168831102214%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIj
> > oi
> > V2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=xp5mkf%
> > 2F
> > VHbxnYwg8ZWX9kf%2BZfwQDgHXdwYrXeOWbDoc%3D&reserved=0
>
>
> --
> Bo Peng <pengbo at sraoss.co.jp>
> SRA OSS LLC
> https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.
> sraoss.co.jp%2F&data=05%7C01%7Ctodd.stein%40microfocus.com%7Cb3f6344f7
> fc04ae28fc108db3015cc4d%7C856b813c16e549a585ec6f081e13b527%7C0%7C0%7C6
> 38156642410607733%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoi
> V2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=bvXqn9y1J
> ZBxGPm716SE2JdUukfFxZMc92H9sumElQY%3D&reserved=0
--
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS LLC
https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.sraoss.co.jp%2F&data=05%7C01%7Ctodd.stein%40microfocus.com%7Cb3f6344f7fc04ae28fc108db3015cc4d%7C856b813c16e549a585ec6f081e13b527%7C0%7C0%7C638156642410607733%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=bvXqn9y1JZBxGPm716SE2JdUukfFxZMc92H9sumElQY%3D&reserved=0
More information about the pgpool-general
mailing list