[pgpool-general: 6996] Re: Empty values for replication_state and replication_sync_state columns with pcp_node_info
Lucas Luengas
lucasluengas at gmail.com
Mon Apr 20 21:44:05 JST 2020
Hello.
I have followed your instructions and I have configured primary_conninfo of
postgresql.conf on both servers.
Thank you for your support.
On Mon, Apr 20, 2020 at 1:56 PM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> > Hello
> > I don't had configured application_name in primary_conninfo of
> > postgresql.conf.
> > I have configured application_name=server2. It's same value such as
> > backend_application_name1
>
> That doesn't help. Pgpool-II calls pg_stat_replication function on
> PostgreSQL primary, which displays replication state of each standby
> server, including replication state, replication sync state and
> application name of walreceiver. Walreciver process is executed by
> postmaster on standby server. The walreceiver connects to walsender
> process of PostgreSQL primary server along with the application name,
> which is configured in primary_conninfo. Since Pgpool-II looks for the
> output row of pg_stat_replication by using application name as a
> key, the application name of walreciver and backend_application_name
> must match. That's the reason why you need to configure application
> name in primary_conninfo. In your case primary_conninfo should look
> something like:
>
> primary_conninfo = '... application_name=''server1''' (on server 0)
> primary_conninfo = '... application_name=''server2''' (on server 1)
>
> > Thank you very much.
> >
> >
> > On Mon, Apr 20, 2020 at 10:15 AM Tatsuo Ishii <ishii at sraoss.co.jp>
> wrote:
> >
> >> Have you set application_name in primary_conninfo of postgresql.conf?
> >>
> >> > Hello
> >> > Thanks for your fast reply.
> >> > I have configured these values: backend_application_name0 and
> >> > backend_application_name1.
> >> >
> >> > [root at server1 pgpool-II]# psql -h 172.16.0.1 -p 9999 -U nobody -c
> >> "pgpool
> >> > show backend_application_name0" postgres
> >> > backend_application_name0
> >> > ---------------------------
> >> > server1
> >> > (1 row)
> >> >
> >> > [root at server1 pgpool-II]# psql -h 172.16.0.1 -p 9999 -U nobody -c
> >> "pgpool
> >> > show backend_application_name1" postgres
> >> > backend_application_name1
> >> > ---------------------------
> >> > server2
> >> > (1 row)
> >> >
> >> > [root at server1 pgpool-II]#
> >> >
> >> >
> >> >
> >> > On Mon, Apr 20, 2020 at 9:54 AM Tatsuo Ishii <ishii at sraoss.co.jp>
> wrote:
> >> >
> >> >> You need to set application name.
> >> >> See
> >> https://www.pgpool.net/pipermail/pgpool-general/2020-April/007022.html
> >> >>
> >> >> Best regards,
> >> >> --
> >> >> Tatsuo Ishii
> >> >> SRA OSS, Inc. Japan
> >> >> English: http://www.sraoss.co.jp/index_en.php
> >> >> Japanese:http://www.sraoss.co.jp
> >> >>
> >> >> > Empty values for replication_state and replication_sync_state
> columns
> >> >> with
> >> >> > pcp_node_info
> >> >> >
> >> >> > Hello.
> >> >> >
> >> >> > I have testing pgpool 4.1.1 with Postgresql 12.2 on Linux Centos 7.
> >> >> >
> >> >> > I get empty values for replication_state and replication_sync_state
> >> >> columns
> >> >> > with pcp_node_info or show pool_nodes.
> >> >> >
> >> >> > I have two servers. I have configured Postgresql streaming
> replication
> >> >> and
> >> >> > it's running with success. I get info for replication using sql
> >> sentence
> >> >> > pg_stat_replication.
> >> >> > For pgpool, I have configure backend_application_name0,
> >> >> > backend_application_name1, sr_check_period (10) and sr_check_user
> >> >> (nobody).
> >> >> > Using sr_check_user = postgres I get same results (empty values for
> >> >> > replication_state and replication_sync_state columns).
> >> >> >
> >> >> > What am I doing wrong?
> >> >> >
> >> >> > Then I show some information:
> >> >> >
> >> >> >
> >> >> > [root at server1 pgpool-II]# pcp_node_info --host 172.16.0.1 --port
> 9898
> >> >> > --username admin -w --node-id 0
> >> >> > 172.16.0.1 5432 2 0.500000 up primary 0 2020-04-20 09:38:04
> >> >> >
> >> >> > [root at server1 pgpool-II]# pcp_node_info --host 172.16.0.1 --port
> 9898
> >> >> > --username admin -w --node-id 1
> >> >> > 172.16.0.2 5432 2 0.500000 up standby 0 2020-04-20 09:38:04
> >> >> >
> >> >> > [root at server1 pgpool-II]# pcp_node_info --host 172.16.0.1 --port
> 9898
> >> >> > --username admin -w --node-id 0 --verbose
> >> >> > Hostname : 172.16.0.1
> >> >> > Port : 5432
> >> >> > Status : 2
> >> >> > Weight : 0.500000
> >> >> > Status Name : up
> >> >> > Role : primary
> >> >> > Replication Delay : 0
> >> >> > Replication State :
> >> >> > Replication Sync State :
> >> >> > Last Status Change : 2020-04-20 09:38:04
> >> >> > [root at server1 pgpool-II]# pcp_node_info --host 172.16.0.1 --port
> 9898
> >> >> > --username admin -w --node-id 1 --verbose
> >> >> > Hostname : 172.16.0.2
> >> >> > Port : 5432
> >> >> > Status : 2
> >> >> > Weight : 0.500000
> >> >> > Status Name : up
> >> >> > Role : standby
> >> >> > Replication Delay : 0
> >> >> > Replication State :
> >> >> > Replication Sync State :
> >> >> > Last Status Change : 2020-04-20 09:38:04
> >> >> > [root at server1 pgpool-II]#
> >> >> >
> >> >> >
> >> >> >
> >> >> > [root at server1 ~]# psql -h 172.16.0.1 -p 9999 -U nobody -c "select
> *
> >> from
> >> >> > pg_stat_replication" postgres
> >> >> > pid | usesysid | usename | application_name | client_addr |
> >> >> > client_hostname | client_port | backend_start |
> backen
> >> >> > d_xmin | state | sent_lsn | write_lsn | flush_lsn |
> >> replay_lsn |
> >> >> > write_lag | flush_lag | replay_lag | sync_priority | sync_sta
> >> >> > te | reply_time
> >> >> >
> >> >>
> >>
> ------+----------+-----------+------------------+-------------+-----------------+-------------+-------------------------------+-------
> >> >> >
> >> >>
> >>
> -------+-----------+------------+------------+------------+------------+-----------+-----------+------------+---------------+---------
> >> >> > ---+-------------------------------
> >> >> > 3664 | 16384 | userrepli | walreceiver | 172.16.0.2 |
> >> >> > | 47482 | 2020-04-20 09:10:20.191692+02 |
> >> >> > 522 | streaming | 0/19001298 | 0/19001298 | 0/19001298 |
> >> 0/19001298 |
> >> >> > | | | 0 | async
> >> >> > | 2020-04-20 09:33:32.341974+02
> >> >> > (1 row)
> >> >> >
> >> >> > [root at server1 ~]#
> >> >> >
> >> >> >
> >> >> > [root at server1 ~]# pgpool -v
> >> >> > pgpool-II version 4.1.1 (karasukiboshi)
> >> >> > [root at server1 ~]#
> >> >> >
> >> >> > [root at server1 ~]# psql -V
> >> >> > psql (PostgreSQL) 12.2
> >> >> > [root at server1 ~]#
> >> >> >
> >> >> >
> >> >> > [root at server1 ~]# psql -h 172.16.0.1 -p 9999 -U nobody -c "pgpool
> >> show
> >> >> > sr_check_period" postgres
> >> >> > sr_check_period
> >> >> > -----------------
> >> >> > 10
> >> >> > (1 row)
> >> >> >
> >> >> > [root at server1 ~]# psql -h 172.16.0.1 -p 9999 -U nobody -c "pgpool
> >> show
> >> >> > sr_check_user" postgres
> >> >> > sr_check_user
> >> >> > ---------------
> >> >> > nobody
> >> >> > (1 row)
> >> >> >
> >> >> > [root at server1 ~]#
> >> >> >
> >> >> > [root at server1 ~]# psql -h 172.16.0.1 -p 9999 -U nobody -c "pgpool
> >> show
> >> >> > backend_application_name0" postgres
> >> >> > backend_application_name0
> >> >> > ---------------------------
> >> >> > server1
> >> >> > (1 row)
> >> >> >
> >> >> > [root at server1 ~]# psql -h 172.16.0.1 -p 9999 -U nobody -c "pgpool
> >> show
> >> >> > backend_application_name1" postgres
> >> >> > backend_application_name1
> >> >> > ---------------------------
> >> >> > server2
> >> >> > (1 row)
> >> >> >
> >> >> > [root at server1 ~]#
> >> >> >
> >> >> >
> >> >> > [root at server1 ~]# psql -h 172.16.0.1 -p 9999 -U nobody -c "show
> >> >> pool_nodes"
> >> >> > postgres
> >> >> > node_id | hostname | port | status | lb_weight | role |
> >> select_cnt
> >> >> |
> >> >> > load_balance_node | replication_delay | replication_state |
> >> >> > replication_sync_state | last_status_change
> >> >> >
> >> >>
> >>
> ---------+------------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+
> >> >> > ------------------------+---------------------
> >> >> > 0 | 172.16.0.1 | 5432 | up | 0.500000 | primary | 0
> >> >> |
> >> >> > true | 0 | |
> >> >> > | 2020-04-20 09:23:40
> >> >> > 1 | 172.16.0.2 | 5432 | up | 0.500000 | standby | 0
> >> >> |
> >> >> > false | 0 | |
> >> >> > | 2020-04-20 09:23:40
> >> >> > (2 rows)
> >> >> >
> >> >> > [root at server1 ~]#
> >> >> >
> >> >> > Thanks in advance. Regards
> >> >>
> >>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20200420/9271c635/attachment-0001.html>
More information about the pgpool-general
mailing list