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