[pgpool-general: 3457] Re: Questions about getting pgpool to handle failover more smoothly

[opensource at kyoshiro.org]

Hello,

> This is the functionality that I'm looking for: Two nodes, with one
> master and one slave, that will cover for each other if and when there
> is a problem.
>
> My goal in using pgpool is to provide our application with a single
> address and port for accessing the database.  By connecting to pgpool,
> it doesn't matter which is the primary (master) and which is the
> secondary (slave); pgpool will route the query to the right place.

My company is looking for the same kind of function, and we have indeed
started using pgpool-II for query partitionning and failover detection,
but using streaming replication.

Although, whenever a node fails and comes back online, pgpool never
attaches it again automatically, even if it's started replicating and
catched up with the master.

In order to automate all, I've been looking into scripting this, using a
combination of :
* pcp_node_info ;
* pg_last_xlog_receive_location(), pg_last_xlog_replay_location(),
pg_last_xact_replay_timestamp(), pg_is_in_recovery() on the slave ;
* pg_stat_replication table on the master ;
in order to determine if node is ready to be reattached, and
pcp_attach_node to make it happen.

But I also have read in your todo list
(http://www.pgpool.net/mediawiki/index.php/TODO) :

    Automatically reattach a node in streaming master/slave configuration

    In streaming master/slave configuration there could be an option to
    automatically reattach a node if it's up-to-date with the master (0
    bytes behind). It often happens that due to minor network outage a
    slave node is dropped off from pgpool and stays down even if the the
    node has resumed replication with master and is up-to-date.pgpool
    already knows how much slave is behind master so i guess this
    wouldn't be too difficult to implement? (from bugtrack #17)

Would it be complicated to implement directly in pgpool-II such an
automatic reattach system ? If I were to use scripting to detect a node
is up again and catched up, where should I place the different steps,
between failover_command, recovery_1st_stage_command or
recovery_2nd_stage_command ?

これからお世話になります
どうもありがとうございます！

Loïc Gomez
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20150204/d3d18114/attachment.html>