[pgpool-general: 6141] Re: PgPool in streaming replication mode without failover and primary down

Tue Jun 26 03:34:41 JST 2018

Ah ok, thanks, so I have learned something more about pgpool (I did not know it was possible to give multiple flags to a backend) 
I got it working more or less :-)
So I setbackend_flag0 = 'ALWAYS_MASTER|ALLOW_TO_FAILOVER'andbackend_flag1 = 'ALLOW_TO_FAILOVER'
and then
failover_command=''

fail_over_on_backend_error = nohealth_check_period = 5health_check_max_retries = 0health_check_retry_delay = 0load_balance_mode = no
when the primary is stopped, the failover_command is executed (not because of the health check but because pgpool detects the administrative shutdown and therefore it triggers a failover, this is as documented). The failover does nothing though (empty command).
The only problem is that the backend 2 got detached from the pool, I see this in the log
2018-06-25 18:13:20: pid 30392: LOG:  starting follow degeneration. shutdown host pg02(5432)
And so I cannot connect anymore before both hosts are down.
2018-06-25 18:13:41: pid 30462: FATAL:  pgpool is not accepting any new connections
2018-06-25 18:13:41: pid 30462: DETAIL:  all backend nodes are down, pgpool requires at least one valid node
2018-06-25 18:13:41: pid 30462: HINT:  repair the backend nodes and restart pgpool
I have to perform pcp_attach_node of the standby manually and then it works. Of course I could automate the attach node via the follow_master command, so it is not a big deal
It is not a set-up that I can use, I need to have automatic failover (and also automatic recovery of failed masters and failed standbys)
Thanks,
Pierre 

    On Monday, June 25, 2018, 6:32:47 PM GMT+2, Andre Piwoni <apiwoni at webmd.net> wrote:  

 Pierre, please see Tatsuo explanation above. 
If I don't use DISALLOW_TO_FAILOVER flag and primary is stopped then I'm able to use read replica. It may take 30 seconds for failover to happen and during this time no connectivity is allowed.My backend flag for pimary is set to 'ALWAYS_MASTER|ALLOW_TO_FAILOVER' since I don't have failover for the primary set at the moment so there's no point in searching for new primary. ALLOW_TO_FAILOVER seems to be needed to allow read-only functionality when primary is down and no other slave is promoted to primary.You may need to create sr_check_user too.
On Sun, Jun 24, 2018 at 12:54 PM, Pierre Timmermans <ptim007 at yahoo.com> wrote:

I cannot get this to work neither, I tried various combination of parameters. Once the primary is stopped pgpool does not accept connections at all. 
In particular, if I set the failover_command to '' (empty string) then when the primary is stopped pgpool starts searching for a new primary (find_primary_node: checking backend no x repeated in the log) but in the meantime it does not accept connections.
Another solution to this problem is to not use pgpool but use a connection string of the form
postgresql://user:secret@host1 :5432;host2:5432/somedb? target_session_attrs=any
https://paquier.xyz/ postgresql-2/postgres-10- libpq-read-write/

but I am not sure all clients support this format (the nodejs pg-node does not). 
Pierre 

    On Wednesday, June 20, 2018, 5:50:06 PM GMT+2, Andre Piwoni <apiwoni at webmd.net> wrote:  

 Wolf,Do you mind sharing relevant configuration and version of pgpool you are using? Do you have more than one slave?
I have tried the following combinations:
backend_flag0 = 'ALWAYS_MASTER|DISALLOW_TO_ FAILOVER'
backend_flag1 = 'DISALLOW_TO_FAILOVER'

backend_flag0 = 'ALWAYS_MASTER'
backend_flag1 = 'DISALLOW_TO_FAILOVER'

backend_flag0 = 'ALWAYS_MASTER' 
backend_flag1 = 'ALLOW_TO_FAILOVER' 
master_slave_mode = on
master_slave_sub_mode = 'stream'

sr_check_period = 0 and default sr_check_period = 10
health_check_period = 0
failover_command = ''
fail_over_on_backend_error = off and fail_over_on_backend_error = on

On Wed, Jun 20, 2018 at 8:40 AM, Wolf Schwurack <wolf at uen.org> wrote:

Yes it is possible I have all my Pgpool hosts setup that way. My setup is a streaming replica and I don't apply the failover.sh script. If the master goes down pgpool fails over to the standby in readonly mode. 

Wolfgang Schwurack
Database/System Administrator
Utah Education Network
801-587-9444
wolf at uen.org

On 6/19/18, 9:48 PM, "pgpool-general-bounces@ pgpool.net on behalf of Bo Peng" <pgpool-general-bounces@ pgpool.net on behalf of pengbo at sraoss.co.jp> wrote:

    Which version of Pgpool-II do you use?
    It also happened by latest version of Pgpool-II 3.7.4?

    On Tue, 19 Jun 2018 20:00:58 -0700
    Andre Piwoni <apiwoni at webmd.net> wrote:

    > I have been trying to figure how can I configure PgPool with one primary
    > and one hot-standby slave to route read-requests to hot-standby when
    > primary is down without any failover. Is it currently possible? Please
    > don't ask why.
    > 
    > Thank you,
    > Andre Piwoni

    -- 
    Bo Peng <pengbo at sraoss.co.jp>
    SRA OSS, Inc. Japan

    ______________________________ _________________
    pgpool-general mailing list
    pgpool-general at pgpool.net
    http://www.pgpool.net/mailman/ listinfo/pgpool-general

______________________________ _________________
pgpool-general mailing list
pgpool-general at pgpool.net
http://www.pgpool.net/mailman/ listinfo/pgpool-general

-- 

Andre Piwoni

Sr. Software Developer,BI/Database

WebMD Health Services

Mobile: 801.541.4722

www.webmdhealthservices.com

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20180625/b297ff0d/attachment-0001.html>