[pgpool-general: 6544] Re: Not quite getting what "replication delay" is representing ... ?

[Martin Goodson]

On 02/05/2019 06:29, Tatsuo Ishii wrote:
> For PostgreSQL 9.5, Pgpool-II sends "SELECT SELECT
> pg_current_xlog_location()" to the primary node, and "SELECT
> pg_last_xlog_replay_location()" to the standby node with user =
> sr_check_user and password = sr_check_password against
> sr_check_database to calculate the replication lag. What do you ge if
> you issue the query by hand?
> 
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
Thank you for the explanation, my apologies for the delay in replying.

Trying these queries by hand revealed what I already knew - absolutely 
minimal replication lag over an hour long test period. Clearly something 
was going wrong, so I took another look at the pgpool.conf config file 
to see if I had missed something obvious.

Turned out I had. There was a typo in the address for backend node 1, 
and it was pointing to the same server as node 0 (the FQDNs are 
unfortunately very similar). In effect, my primary and standby nodes 
were the same database. No wonder I was seeing significant replication lag!

Very embarrassing, and obviously now fixed, but I was somewhat surprised 
that pgpool accepted that upon startup. Should there be a check to make 
sure the backends are, indeed, unique databases?

Regards,

M.
-- 
Martin Goodson

"Have you thought up some clever plan, Doctor?"
"Yes, Jamie, I believe I have."
"What're you going to do?"
"Bung a rock at it."