PgPool

I wasn't really up to date on high availability solutions for postgres. From browsing around it looked like pgpool provided a reasonable solution to the problem. So I went ahead and installed postgres on my ubuntu server and on my desktop machine (running windows, yuk I know).

While the pgpool config is fairly simple, it did take me some time to get it running correctly. Replication mode just didnt want to work at first and pgpool doesnt log anything, unless you start it with '-d -n' so it doesnt detach from the terminal.
The debug output showed a mismatch in a reply packet from the 2 postgres servers.. the cause was a slight difference in pg_hba.conf. Once fixed, the replication worked.

Next up was testing the high availability.. I stopped 1 postgres server, which caused my psql client to block.. seems like the default config on ubuntu didnt set the health check interval, so pgpool never noticed the server was down. After entering a reasonable value, pgpool detected the node as down and aborted/disconnected the psql connection.

This also means that an app has to reestablish the link itself, hence needs to be aware of this. The thing that bugged me though, was that changes done while 1 node is down, go unnoticed if you bring the node back up.. meaning there is no sync or even detection that both nodes are different, resulting in different results depending on which node your query gets executed. Maybe this is configurable? Something to check out another time.