Postgres-XL: Scalable Open Source PostgreSQL-based Database Cluster
postgres-xl.org
postgres-xl.org
It is probably because they don't provide the failover and assume you have a standby LB and an active LB. So only one needs to be shown and you handle the failover on the application side rather on their side. At least, that is what I generally see that sort of thing in.
- Hot standby that detects when the master is down and takes over as master when needed
- DNS-level solutions that distribute across multiple load balancers
But the DNS has a TTL that may not be honored by all ISPs, so how do you create a truly no single point of failure with the load balancer?
Because HTTP in general and with that HAProxy is very stateless, this is really easy and safe to do. I've had the opportunity to fail over multiple times already (mainly for software updates, once because of a hardware issue) and I never had any problems with the setup.
1. "Geo-DNS" is about using an anycast network to direct users to their nearest datacenter(s). This does _not_ aid High Availability at all.
2. DNS Round Robin is about distributing the load between multiple IPs. As a load balancing solution it is relatively poor, because you have no control over the actual balancing and can end up receiving most users though a single IP.
3. DNS Failover solutions that replace the IP when the server goes down, which is also a poor solution because of TTL and non-TTL browser caches.
4. DNS Round Robin but for the High Availability, not for the balancing. This is actually an interesting approach because most modern browsers automatically switch to using (one of) the other record(s) when the IP they were using goes down (sorry, I have no reference that clearly states which browser do and which ones don't exhibit this behavior). In fact, there are some sources around [1] that seem to identify this approach as the only one to achieve instant failover in the face of datacenter-wide outages.
[1] http://serverfault.com/questions/69870/multiple-data-centers...
My personal preference is to combine approaches #3 and #4: have all hosts in the round robin with a lowish TTL, but automatically remove any host that goes down.
Of cause Postgres-XL may not exactly be designed for my use case where I just want to be able to have one node fail or take a server down to do upgrades. Ideally it would be as simple as the built in Postgresql replication as become.
and sharding is currenty only-primary-key
The Global Transaction Manager here ensures that transaction ids and snapshots (the context in which statements are run, the other running transactions), are exactly the same across the entire cluster. Without it, there are theoretical possibilities of inconsistencies based on the timing of concurrently running transactions and when connections to other data nodes are taken.
The GTM ensures cluster-wide Multi-version Concurrency Control whereby readers do not block writers and writers do not block readers and ACID properties are respected globally.
As for taking a server down for upgrades, well, that is beyond the scope of the project. There have been people looking at XL for simple load balancing and HA, we should perhaps consider making it easier for this use case, but I think some use pgpool for that.
http://www.theregister.co.uk/2013/10/10/translattice_stormdb...
Lashes PostgreSQL, -R, and Postgres-XC together for unholy database tryst"
Some parts of Postgres-R are in the TransLattice Elastic Database (TED), but that is a proprietary closed source product, sorry. (I work for TransLattice, who also open sourced Postgres-XL.)
The intent is to always merge in upstream from PostgreSQL.
Anyway, you do not run on top of it, changes have been made deep in the bowels of PostgreSQL.
"The project includes architects and developers who previously worked on both Postgres-XC and Stado, and Postgres-XL contains code from Postgres-XC. The Postgres-XL project has its own philosophy and approach."
So should I see a brighter future for Postgres-XL rather than Postgres-XC? It seems that the Postgres-XC respository is still making some commits, though the rate is quite low.
Its very murky and to be honest, very frustrating. I just want a Postgres that scales and doesn't cost me $$$ like FoundationDB does. The Postgres-XC team is far too insular and dont seem to care about outside users. I hope Postgres-XL can inspire them to push ahead and then hopefully we can see a postgres 9.4 based postgresql clustering soloution for write scalability. (and no Pgpool-2 doesnt count)
It is natural, because they've just forked the project. But I think if there are some gotchas in the original project, Postgres-XL would also expose similar problems. What have been your main concerns using Postgres-XC? Any design issues?
Any bugs in XC in the planner and executor would not show up in XL (that is not to say that we in Postgres-XL do not have any of our own bugs :-)).
Bugs related to GTM could show up in both, but in Postgres-XL additional precautions have been taken to reduce the likelihood of problems.
I wonder why someone can't just add etcd to postgres and create self-sufficient distributed postgres nodes (I am oversimplifying, surely)
Since you can have more than one of them, no.
In the diagram they have 2, but where you have two, you can add more for more reliability, right? following that thinking down the rabbit hole, and you've got distributed transaction monitoring across the data nodes(paxos/raft)... Just wondering why that wasn't the goal
The one thing that's missing is automated failover, but personally, I wouldn't do that with a database because from experience, from all the issues that would cause an automated failover, only a very small part would actually be solved by such action, whereas most of them would probably be made a lot worse.
Especially considering that undoing a failover is very costly (in terms of time, not necessarily money) until we get 9.4 where failing back to the original master won't need a full resync.
Postgres-XL is hopefully at least reassuring that once those scaling problems do crop up, there is something available that can help.
http://www.amazon.com/PostgreSQL-High-Performance-Gregory-Sm...
I'd recommend doing a master-slave setup. Doing a master-master is a lot more complicated. If the master goes down, you can have monitoring software promote a slave to be the new master.
http://www.rassoc.com/gregr/weblog/2013/02/16/zero-to-postgr...
This will work over the internet between data centers if you're so inclined...depending on your traffic, a simple way to get started.
Plenty of great hosted database solutions e.g. AWS RDS (PostgreSQL, MySQL, Oracle), MongoLab, MongoHQ, Cloudant, Firebase, Azure etc.
I eventually got it working, but there were way too many informally created scripts that PgPool and PG had to know to trigger failovers, initiate resyncs from WALs, etc. I didn't like it at all, and around about then AWS started offering PG on RDS, so I just moved to that.
So, my advice would be, unless you've got someone on team for who that isn't that much work, you get a lot of benefit from going w/ hosted. RDS Postgres has been pretty great - not exceptional, but for my use cases, okay. Hoping they add cross region read replicas for PG sometime soon, as that would make a lot of expansion opportunities really easy.
Consistency is achieved because all of the nodes use the same transaction ids and snapshots (list of running transactions), via the Global Transaction Manager. There is no need to worry about statements being executed in a different order across the nodes.
I can't wait!
Tables can be designated as replicated or distributed (sharded). Replicated tables are typically fairly static. These are handled synchronously in the cluster on every datanode where the table resides. Actually, it first applies to a designated "primary" node, and upon completion, it will execute on the other nodes. The reason for this is to reduce the chance for deadlocks; if it succeeds on the primary, it has obtained all of the needed locks and we can be sure we can then get the locks on the other nodes.
In addition, the term synchronous replication is also used as in PostgreSQL's replication, but in Postgres-XL for a datanode having a standby replica, synchronously. It is a warm, non-queryable standby.
With regards to a WAN environment, Postgres-XL is not designed for that because of the latency, and for handling long network outages. If there is enough interest, that could be an area to enhance in the future, but consistency may be compromised.