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
|
