Failover architecture - Mailing list pgsql-general

Hi, everyone.  I'm working on a project that is already using
    PostgreSQL 9.0, including streaming replication.  I'm trying to help
    them figure out a good architecture for ensuring stability and
    failover under a variety of conditions, and wanted to ask the
    community for suggestions and help.

    Basically, they have a mission-critical application that talks to
    PostgreSQL, and which works quite well.  Because of the
    mission-critical nature of the application, it has been implemented
    twice, once at each data center.  The two data centers are connected
    via a network connection; one PostgreSQL server acts as the master,
    and the other acts as a (read-only) slave.  We're using pgpool in
    the second data center (i.e., the one with the PostgreSQL
    replication slave) to send all writes to the first data center
    (i.e., the one with the PostgreSQL replication master), but to
    balance reads across the two servers.

    This all works really well.  The automatic failover also works well,
    such that when the master goes down, the slave is promoted to the
    master, a bit of IP-address switching happens behind the scenes, and
    things continue to hum along.

    So far, so good.  But we have a few questions:

      Once the slave has been promoted to master, we have a single
        server, and a single point of failure.  Is there any simple way
        to get the former master to become a slave?  I assume that it
        would need to start the whole becoming-a-slave process from
        scratch, invoking pg_start_backup(), copying files with rsync,
        and then pg_stop_backup(), followed by connecting to the new
        master.  But perhaps there's a shorter, easier way for a "fallen
        master" to become a slave? 

      Is there any easy, straightforward way for a "fallen master"
        to re-take its position, demoting the promoted slave back to its
        original position of slave?  (With little or no downtime, of
        course.)  I assume not, but I just wanted to check; my guess is
        that you have to just make it a slave, and then start to follow
        the newly promoted master.

      If the network connection between the two data centers goes
        down, but if the computers are still up, we worry that we'll end
        up with two masters -- the original master, as well as the
        slave, which will (falsely) believe the master to be down, and
        will thus promote itself to master.  Given that PostgreSQL
        doesn't allow master-master synchronization, we're thinking of
        using a heartbeat to check if the other computer is available,
        in both directions -- and that if the master cannot detect the
        slave, then it goes into a read-only mode of some sort.  Then,
        when it detects the slave again, and can restart streaming, it
        goes back into read-write mode.  Is there a way (other than
        Bucardo, which doesn't seem to fit the bill for this project),
        is there any way for us to merge whatever diffs might be on the
        two servers, and then reconnect them in master-slave streaming
        mode when communication is re-established?

      Of course, Is there any easy way to do that?  If so, then what
        happens when pgpool tries forward an INSERT to the master while
        it's in read-only mode?  (For the record, I'm pretty sure that
        there isn't any easy or obvious way to make a database
        read-only, and that we can simulate read-only mode by adding
        INSERT/UPDATE triggers on each of the four -- yes, only four --
        tables in the database, silently ignoring data that's posted.  I
        floated this with the project managers, and they were OK with
        this idea -- but I wanted to double-check whether this is a
        viable solution, or if there's an obvious pitfall I'm missing
        and/or a better way to go about this.
      If we use master-slave replication, and communication is cut
        off, does the slave reconnect automatically?  I believe that the
        answer is "yes," and that the replication will continue so long
        as we're in the defined window for replication delays.

    Thanks for any suggestions and answers that you can provide.  And of
    course, if I've missed something obvious in the documentation, then
    a pointer to the appropriate resource would be more than welcome.n

    Reuven
    --
Reuven M. Lerner -- Web development, consulting, and training
Mobile: +972-54-496-8405 * US phone: 847-230-9795
Skype/AIM: reuvenlerner