Synchronous Multimaster Replication postgresql pgpool 2

Question

We would like to build a system of two Postgresql 8.4 servers, with pgpool 2 in front that will make all writes go to both systems. In the event of a failure on one of the nodes, it will degrade and pgpool will direct all writes at the remaining node.

From there we can manually re-sync everything and bring it all back up.

Im currently doing some testing to this effect, and noticing some interesting things.

My script has a simple loop that inserts rows in the db. When I shutdown the network interface on one server, the script pauses, pgpool waits for a reply from the down server, but doesnt degrade it, until somebody else tries to connect. Once somebody else create a new connection it will then return an error, and degrade the server. Then if I run the script again it will then direct the writes to the single remaining server.

This seems like a little too much activity needed to degrade a server. Am I missing something? is this normal?

Cheers

Mark

Answer 1

I believe the behavior you are describing is normal for pgpool - It degrades a server when it can't connect to it, but if a connection is already "established" it doesn't die as quickly as you might expect (this is likely to deal with transient network glitches and avoid degrading the environment for a "hiccup").

---

Out of curiosity, have you considered the streaming log replication available in Postgres 9? The latest implementation is near-real-time, and allows you to run queries on the slave servers. I believe synchronous replication is coming soon (or may have already been implemented).
You would need a script or manual process to handle a failure (restart one of the "slave" servers as master and move a virtual IP over), but the implementation is not otherwise substantially different from what you'll get out of pgpool (re-sync when a server fails, etc.)

The big advantage here is that you're using stuff that's built in to the Postgres server core, and it got some pretty extensive testing and documenting in the 9.0 beta process. It's my de-facto recommendation if you have no constraints that make it impractical.