[pgpool-general: 6026] Re: Streaming Replication Check

[Mathias Buresch]

Thanks again for that quick answer! Also thanks to your mail Jaime.

So I will try to set "delay_threshold" to "1".
If that is not working I will think about disabling "load_balance_mode" or "synchronous_commit = remote_apply" which will have performance issues.

On 05.04.18, 12:47, "Tatsuo Ishii" <ishii at sraoss.co.jp> wrote:

    > Hi there,
    > 
    > maybe someone can help me to understand how “Streaming Replication Check” is working.
    > 
    > Is it checking replication lag by using pg_last_xlog_receive_location() and pg_last_xlog_replay_location() ??
    > Am I right that those check should not be dependent from database I use?
    
    Right.
    
    > If it is like that why I need “sr_check_database”?
    
    Because Pgpool-II needs a database to connect to. You could assign any
    database to sr_check_database whatever you like (postgres,
    template1...) as long as Pgpool-II is allowed to connect to.
    
    > Also why “delay_threshold” cannot be 0? Why 0 is disabling it?
    > 
    > The reason I ask is that a customer has problem with inserting something and “directly” read the insert and don’t get the correct answer – maybe because it is not synced and loadbalancing is forwarding to slave?!
    
    If I understand you customer's problem correctly, he/she needs a
    standby server which lags 0 from primary server. The only way to
    achieve this is let PostgreSQL servers run in synchronous replication
    mode with "remote_apply" (i.e. synchronous_commit = remote_apply)
    
    Replication delay = 0 just means transaction log's LSN is sync with
    the primary, which does not necessarily mean your customer reads
    the latest data. For that the transaction must be applied to
    database. synchronous_commit = remote_apply does the trick.
    
    The price is performance.
    
    Best regards,
    --
    Tatsuo Ishii
    SRA OSS, Inc. Japan
    English: http://www.sraoss.co.jp/index_en.php
    Japanese:http://www.sraoss.co.jp