3 ms·
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
by joevandyk 12y ago
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.
- secure 12y agoWith 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.
- joevandyk 12y agoWhat's the point of using sync commits if you don't actually ensure that the data is on two machines before the transaction is committed?
- rpedela 12y agoI 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.