[pgpool-general: 6995] Re: Empty values for replication_state and replication_sync_state columns with pcp_node_info
Tatsuo Ishii
ishii at sraoss.co.jp
Mon Apr 20 20:56:44 JST 2020
> 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
>> >>
>>
More information about the pgpool-general
mailing list