|
Hi, The quick
question is: How (if
possible) can I do to get data (maybe through xlogs) from two separate databases
and merge them into one? For those
that want to know my situation, here it is: I have two
postgresql 9.0 that are working as master/slave using streaming replication. At
some point, the slave will become primary. As most of you already know, I can
do this by just creating a trigger file. Everything is fine so far: when I
create a trigger file, the slave becomes writable, therefore becoming master. My setup is
using pgpool on top of 2 databases, as shown in the following drawing: Server
A
Server B +--------------+
+--------------+ |
AP0
|
| AP1 | |
| | |
| | |
V
|
| V | | pgpool0----|---.
.---|---pgpool1 | |
| | \ /
| | | |
V |
X |
V | | DB0 <----|----'
'----|---> DB1 | +--------------+
+--------------+
172.10.10.2
172.10.10.3 If Server A
is disconnected from the network, pgpool0 will not see DB1, and pgpool1 will
not see DB0. Therefore, pgpool0 will degenerate the failed backend. (For those
who don’t know pgpool, it’s just a database pool that has the same
interface as postgresql. The application thinks it’s talking to a
postgres database) In my case
above, this is what will happen: 1. pgpool0
detects DB1 disconnection and issues a failover to DB0, which is already
Master. That is, nothing to do. 2. pgpool1
detects DB0 disconnection and issues a failover to DB1, which is slave. That
is, create trigger file on DB1. At this point, both DBs are writable. 3. At this
point, split-brain problem could raise. That is, AP0 could potentially insert
new valid records through pgpool0, as well as AP1 through pgpool1. This means,
both DB0 and DB1 could potentially have valid data. I’m
aware of the existence of techniques like STONITH, or heartbeat. The problem is
that AP* must be running and available in the network and connected to a
database, therefore can not be just shutdown. So, the
question would be: How can I do to merge data from DB0 and DB1 and make it
available in the new master, whichever is chosen? Any ideas? Thanks in
advance, -Daniel |
