Master / slave replication also has a synchronous mode, so lack of consistency isn't an issue. Either way, you still need to do some work on top of replication in order to get automatic failover.
Thanks for pointing this out. Seems like in the described setup (which does not use automatic failover) there really is no advantage then.
I’ll make a note to look into replacing the pgpool setup with a standard master/standby solution and see if I run into any problems that prevent me from doing that :).
After reading the manual again, I recall what made me not pursue the PostgreSQL built-in master/slave replication:
"""
Commits made when synchronous_commit is set to on or remote_write will wait until the synchronous standby responds. The response may never occur if the last, or only, standby should crash.
[…]
If you really do lose your last standby server then you should disable synchronous_standby_names and reload the configuration file on the primary server.
"""
The way I interpret this is that in case my one and only standby server crashes, my database will not allow any modifications until I intervene. With pgpool2, writes will continue to work on the master, and it’s my responsibility to eventually bring back the standby server.
I agree that is a little weird. It should at least be configurable if you want that behavior or not. 9.4 introduces the ALTER SYSTEM command which may make it easier to automate the process of disabling synchronous_standby_names when a failover happens. There are some other improvements to replication in 9.4 as well.
> The way I interpret this is that in case my one and only standby server crashes, my database will not allow any modifications until I intervene.
In synchronous mode...I mean, what else do you expect? How else would you expect it to handle a net split (I'm only guessing a split slave won't perform meads?).
I mean, if you're _setting_ this mode (which it doesn't seem you would), I'm assuming you desire this behavior, though, so, I'm not sure why you're so up-in-arms about it.
> With pgpool2, writes will continue to work on the master, and it’s my responsibility to eventually bring back the standby server.
Isn't that how it works with not in synchronous mode?
> After reading the manual again, I recall what made me not pursue the PostgreSQL built-in master/slave replication:
Also, what did you chose if not postgres? MySQL or did you pay for a license for MSSQL or Oracle. Is there another F/OSS database worth considering?
If you want to use sync commits, you need a minimum of three servers.
Think about what happens if you only have two machines. If the standby goes down, then it's impossible for data to be protected if the master also goes down.
With pgpool2 and two servers, I can have synchronous commits _as long as_ both servers are healthy, and still continue operating (with less durability) when only one server is healthy.
Of course, it’s impossible to protect against data loss when the remaining server also goes down, but you always have that risk :). As I said, I realize that a setup with only two servers cannot be perfect, but it’s all I’m willing to afford for a spare-time hobby.
So, in comparison, pgpool2 provides me with a more convenient mode of operation for my use-case.
I think OP wants at least one machine that is healthy and fully operational at any point in time. But the cost of a three machine setup is too great. There are three main failures scenarios.
1. Primary fails and standby takes over. Sync mode helps here because there should be no data loss for completed transactions. In async mode, there could be some data loss for completed transactions.
2. Standby fails and primary continues to operate normally. When the standby is back online, it catches up. Currently the primary would not be able to continue to operate normally because of those config settings.
3. Both primary and standby fail simultaneously. A very unlikely scenario but can be solved with WAL archiving which does have the risk of potential data loss.
