[pgpool-general: 6993] 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 17:15:21 JST 2020
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