[pgpool-general: 6964] Re: auto_failback = on not working

[Tatsuo Ishii]

The auto fail back functionality looks into replication_state and you
need to turn on it.
To turn on it, you need to configure backend_application_name:
https://www.pgpool.net/docs/latest/en/html/runtime-config-backend-settings.html#RUNTIME-CONFIG-BACKEND-DATA
and
application_name in primary_conninfo of recovery.conf.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> Hi,
> 
> I enabled the health check and below is the the updated configuration.
> Still, the standby wouldn't come up. BTW, the documentation only
> mentioned about SR Check.
> 
> ** Standby is still down more than 60 secs.
> 
> -----------------------------
>  node_id | hostname | port | status | lb_weight |  role   | select_cnt
> | load_balance_node | replication_delay | replication_state |
> replication_sync_state | last_sta
> tus_change  
> ---------+----------+------+--------+-----------+---------+----------
> --+-------------------+-------------------+-------------------+------
> ------------------+---------
> ------------
>  0       | PGHOST1  | 5433 | up     | 0.500000  | primary |
> 2          | true              |
> 0                 |                   |                        | 2020-
> 04-
> 08 20:06:18
>  1       | PGHOST2  | 5433 | down   | 0.500000  | standby |
> 0          | false             |
> 0                 |                   |                        | 2020-
> 04-
> 08 20:09:30
> (2 rows)
> 
> 
> ****  Standby is replicating OK after restarting postgresql
> 
> -[ RECORD 1 ]----+----------------------------
> pid              | 24249
> usesysid         | 16384
> usename          | replication
> application_name | walreceiver
> client_addr      | 10.0.2.18
> client_hostname  | 
> client_port      | 53142
> backend_start    | 2020-04-08 20:09:59.7633+08
> backend_xmin     | 
> state            | streaming
> sent_location    | 0/5801A030
> write_location   | 0/5801A030
> flush_location   | 0/5801A030
> replay_location  | 0/5801A030
> sync_priority    | 0
> sync_state       | async
> 
> 
> **** Health Check Parameter Enabled
> 
> #--------------------------------------------------------------------
> ----------
> # HEALTH CHECK GLOBAL PARAMETERS
> #--------------------------------------------------------------------
> ----------
> 
> health_check_period = 10
>                                    # Health check period
>                                    # Disabled (0) by default
> health_check_timeout = 20
>                                    # Health check timeout
>                                    # 0 means no timeout
> health_check_user = 'postgres'
>                                    # Health check user
> health_check_password = 'P at ssw0rd1'
>                                    # Password for health check user
>                                    # Leaving it empty will make Pgpool-
> II to first look for the
>                                    # Password in pool_passwd file
> before using the empty password
> 
> health_check_database = ''
>                                    # Database name for health check. If
> '', tries 'postgres' frist, then 'template1'
> 
> health_check_max_retries = 0
>                                    # Maximum number of times to retry a
> failed health check before giving up.
> health_check_retry_delay = 1
>                                    # Amount of time to wait (in
> seconds) between retries.
> connect_timeout = 10000
>                                    # Timeout value in milliseconds
> before giving up to connect to backend.
>                                    # Default is 10000 ms (10 second).
> Flaky network user may want to increase
>                                    # the value. 0 means no timeout.
>                                    # Note that this value is not only
> used for health check,
>                                    # but also for ordinary conection to
> backend.
> 
> 
> On Wed, 2020-04-08 at 15:42 +0900, Takuma Hoshiai wrote:
>> On Wed, 08 Apr 2020 14:04:59 +0800
>> hostmaster <hostmaster at mylinuxsite.com> wrote:
>> 
>> > Hi all,
>> > 
>> > I have a 2 node setup (2 pgpool and 2 Postgresql9.6)  At the start
>> > there is a primary and standby.  I then stop the standby database
>> > and
>> > then restart it after a while. After more than 60 seconds.  I check
>> > the
>> > status using 'show pool nodes'. The result is that the standby is
>> > still
>> > down. 
>> > 
>> > My understanding is that if you set the auto_failback = on pgpool
>> > will
>> > automatically attach the standby. Is my understanding correct?
>> > Appreciate any help.  
>> 
>> I think that you should enable the feature of health check, because
>> auto failback need its process.
>> 
>> Best Regards,
>> 
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general