[pgpool-general: 3518] Re: Node disabled problems

[Tatsuo Ishii]

> Hey,
> 
> I am using PGpool on an AWS instance with the following settings:
> 
> backend_flag0 = 'ALLOW_TO_FAILOVER'
> backend_flag1 = 'ALLOW_TO_FAILOVER'
> failover_command = ''
> failback_command = ''
> fail_over_on_backend_error = off
> 
> I am managing failovers from an external product (EDB Enterprise Failover
> Manager). This seems to work well most of the time.
> 
> Sometimes I get the master node failing because of the AWS network flaking
> out on me, this works as expected and I get into the following state:
> 
> postgres=# show pool_nodes;
>  node_id |   hostname   | port | status | lb_weight |  role
> ---------+--------------+------+--------+-----------+---------
>  0       | 10.51.9.174  | 5432 | 3      | 0.500000  | primary
>  1       | 10.51.10.173 | 5432 | 2      | 0.500000  | standby
> (2 rows)
> 
> My question is, is there any way in PGPool to automatically re-enable the
> primary node when it shows up again? If it *really* went down then my EFM
> would fail it over (which runs pcp_promote_node) - so I just want it to
> enable it when it shows up again. I can't explain why EFM seems to be more
> fault tolerant than PGPool - but I'm ignoring that for now.

That's depending on what is "really went down". You can configure
pgpool-II's parameters for example "the servers goes down if pgpool-II
retries three times and still the access fails".

> I tried using DISALLOW_TO_FAILOVER , but this removes any way of disabling
> a single node and stops all PGPool access when I stop the standby node.
> This won't work for me. I need to be able to perform the following
> operations:
> 
>    - promote a node in PGPool to master (I use pcp_promote_node currently)
>    - disable a node in PGPool (I use pcp_detach_node currently)
>    - enable a node I have disabled (I use pcp_enable_node currently)
>    - Operate on just the master node
>    - Operate on just the slave node (read only)

Some of these requirements conflict each other. For example:

- promote a node to master
- operate on just the slave node

That says you need to tell pgpool-II when you want promote and when
you don't want.

So it seems it's not matter of technical things, rather the definition
of requirements. Once your requirement becomes cleaner, probably you
could create you own failover command which handles such that complex
requirement.

> I can't seem to find a way to hit all these and still have a functioning
> environment.
> 
> I'm getting the feeling that maybe I can't get all this from PGPool, but
> any help would be appreciated. I'm on PGPool 3.3.1

pgpool-II 3.3.1 is too old.

> Cheers,
> 
> 
> James Sewell,
> PostgreSQL Team Lead / Solutions Architect
> ______________________________________
> 
> 
>  Level 2, 50 Queen St, Melbourne VIC 3000
> 
> *P *(+61) 3 8370 8000  *W* www.lisasoft.com  *F *(+61) 3 8370 8099
> 
> -- 
> 
> 
> ------------------------------
> The contents of this email are confidential and may be subject to legal or 
> professional privilege and copyright. No representation is made that this 
> email is free of viruses or other defects. If you have received this 
> communication in error, you may not copy or distribute any part of it or 
> otherwise disclose its contents to anyone. Please advise the sender of your 
> incorrect receipt of this correspondence.