Why isn't the built-in master/slave replication plus WAL archiving to something like S3 sufficient?
pgpool2 on the other hand provides statement-level consistency, i.e. a statement is only successful when it is committed to all healthy nodes.
I agree that for many use-cases, the built-in master/slave replication is probably good enough, but I wanted to try going all the way… :)
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 :).
""" 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.
https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_...
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?
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.
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.
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.
When I started Reesd, the main reason was to be able to use (a single invokation of) scp to ship WAL segments to three different machines in different datacenters.
Actually, now that Reesd is deployed, it itself uses multiple of the above mechanisms. It ships WAL segments (exactly as a user of Reesd could do it, by uploading to a Reesd bucket), uses synchronous replication between the primary and a first standby, and asynchronous replication with a second standby. Both standby's are also configured to use WAL segments if available. Indeed, starting a standby will first use the WAL segments before connecting to the primary to begin streamming replication.