Replicated PostgreSQL with pgpool2
michael.stapelberg.de
michael.stapelberg.de
I use wal-e myself and its indispensable and easy to use.
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.
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.
https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_...
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.
[1] https://kloudless.com [2] http://wiki.postgresql.org/wiki/PgBouncer
http://webcache.googleusercontent.com/search?q=cache:MPIiThx...
Traceroutes welcome in case it is reproducibly down for you :).
As for a simpler way, I think suitable scripts for recovery should be shipped with pgpool2 as a first step.
A second step would be integrating pgpool2 functionality into PostgreSQL itself. That way, the whole authentication problem would go away, and you would not need to run a separate program. Also, the WAL shipping could be replaced by just letting the non-primary nodes use a replication connection to the primary node directly.
That would not get rid of all of the complexity, but it’d hide a lot more of it from the user :).
Scalable, Reliable PostgreSQL is not really there yet.
There are many ways to do it, yes, but most people just want one thing: the db failing over in case it goes down.
So, there is a single solution. If you want greater flexibility, pgpool2 may be an option; otherwise, just use what's built-in.
Everything, it all needs to be simplified. Yes it's the "difficult" problem for a database, but it's also the one thing where I feel Postgres is lacking. What's needed is the Postgres community to quit messing around and accept that clustering is no longer something they can fob off to other projects. They need to adopt either Postgres-XC or preferably Postgres-XL and push it forward with full force.
The current state of affairs is "pgpool, slony, repmgr, buccardo, and the rest, pick one, spend hours testing how well they fit your use case. Then use that." Now this is ok when a project is new. But Postgres is about 20 years old. Clustering is not new, many of these solutions are old, we finally have a very good clustering option but it's not even mentioned on half the official wiki pages about clustering last time I looked. Postgres is a professional grade tool, so it's ok to not endorse things not considered ready yet... But Postgres-XL is in use in production deployments, it's built out of Postgres itself not via proxies or other layering over or inside vanilla Postgres. It's not a hack, and endorsing it as the definitive future of clustered Postgres but not necessarily endorsing it as "as good as regular Postgres" is long overdue. At least that's my opinion as a serious production Postgres user who makes the decision to use other databases more often than not, due to lack of good clustering.
But how you orchestrate the recovery process, test the recovery process, centralise your backup files for faster tar/upload to offsite storage. All these things are "not simple" when all you have is the Postgres docs.
Postgres is more than just a database/database engine. Its a database ecosystem.
If you truly need reliability, of course you'll have to invest some time into setting it up. If this isn't something you're interested in, just go with a hosted postgres instance like Heroku.
I just want sharding with automatic failover to separate datacenter in a simple package...
Sharding is a problem in Postgres and I am sure they will fix the disconnect between how the data is inserted and how it's read pretty soon.
Automatic failover and datacentre awareness are not typical features in Open Source RDBMS, although you some NOSQL solutions may do this for you.
I'm willing to overlook many weaknesses in PG as we get blistering performance and amazing stability combined with features like hot schema upgrades.