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