[pgpool-general: 8686] Re: replication state not visible
Todd Stein
todd.stein at microfocus.com
Wed Mar 29 23:21:40 JST 2023
Hi Bo,
I'm trying to find the cause of my system not starting replication slots (on its own) and why killing postgres on the primary node, causes one of the standby nodes to be shutdown also.
Regards,
Todd Stein
-----Original Message-----
From: Todd Stein
Sent: Wednesday, March 29, 2023 7:51 AM
To: Bo Peng <pengbo at sraoss.co.jp>
Cc: pgpool-general at pgpool.net
Subject: RE: [pgpool-general: 8671] replication state not visible
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