[pgpool-general: 284] Re: Feature Request … Master/Slave Mode Addition

[Matt Wise]

On Mar 22, 2012, at 3:57 PM, Tatsuo Ishii wrote:

>> I essentially want a mode in PGPool where PGPool will monitor all of
>> the DB servers and dynamically figure out which one is the master.
>> It will do all of the normal healthchecks, and even catch it
>> automatically when a new server is promoted to the master. Streaming
>> replication healthchecks would still run as well, and update
>> appropriately if the backend master changes.
> 
> First of all, it's pretty difficult to recognize which one is the
> master. As far as I know, there's no reliable way to detect which one
> is the master. If you know how to do it, please let me know. We do it
> in hard way when failover starts but we don't want to it regulary
> because it's so hard:-)

How do you guys check the replication delay?

> 
>> Any time a single server
>> goes down (thats not the master), it should stop sending transactions
>> there but continue to accept them and distribute them among the other
>> servers. When that backend server comes back online, it should be
>> re-added to the pool immediately.
> 
> As explained many times the reason why we don't it automatically is,
> we cannot trust the downed server. It may be right after initdb'ed or
> it may have completely outdated contents. Who knows.

When you know the server has been shut down administratively (shutdown command), you 
ought to be able to pretty safely say that it wasnt corrupted. It could at least be an option. 

In the event of a server crash or abrupt disconnect, you could then not automatically re-add
the server (config option?). However, if the server comes up and is replicating again OK, cant you
make a reasonable assumption that its OK?

> 
> BTW, you could issue pcp_attach_node command from your PostgreSQL
> starting script if you are sure the downed server's database is fine.

Yeah, its something I'm considering.. Though really I'd rather have the server validate
that replication is running, and reasonably up-to-date and do it itself. 

> 
>> This model also allows us to run two PGPool servers in parallel.. we
>> can run one as a primary (that maybe has the failover commands all
>> configured) and a second one as a backup with no failover commands
>> configured... just there to accept connections if the first one goes
>> offline!
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp