4 ms·

How far has postgres come w.r.t. setting up a cluster with automatic fail-over and recovery? I didn't see the author address this aspect of Rethink that has a

How far has postgres come w.r.t. setting up a cluster with automatic fail-over and recovery? I didn't see the author address this aspect of Rethink that has a lot going for it.

gaius 10y ago
Have a look at http://repmgr.org http://repmgr.org
- williamstein 10y ago
  Thanks. Indeed, I'm just using one PostgreSQL master, with no HA, but plan to set something up later, and have put repmgr on my todo list. (I'm the post author.)
  - nimrody 10y ago
    A single Postgres machine holds all data? So you scale the database by switching to a stronger machine? Or do you plan to shard the data somehow? Do you have any queries iterating over the entire table?
    - williamstein 10y ago
      > A single Postgres machine holds all data? Yes. It's currently using 80GB of disk, so regarding disk space I can scale for years. I have tiering system where older data gets stored in Google cloud storage, and grabbed on demand. So regarding space, there isn't an issue scaling up. It's also trivial to increase disk space on live VMs on GCE. > So you scale the database by switching to a stronger machine? That's my plan. We're at about 50% overall load as I write this, and here's the usage on the database server: "load average: 0.09, 0.16, 0.17". We're collecting historical Prometheus and other monitoring about load and queries, etc. By the time we need 32 cpus (the GCE limit), either the GCE limit will be raised, the company will be dead (no way in hell), or the company will be wildly successful. So in the only case in which I need to scale out, it will be an exciting new problem that I can hire a team to help with, and we'll benefit from understanding exactly what the problem is we need to solve. I have wasted an enormous amount of time until now due to premature worry about scalability, fueled by my naive assumptions about how quickly SageMathCloud would grow.
ploxiln 10y ago
Clustering and automatic fail-over and recovery is not always a great idea. It sounds like you want it implemented so you "don't have to worry about it", but fail-over and recovery systems are a lot more complicated and troublesome than an alert, some read-only degraded operation, and a manual promotion. Aphyr's "call me maybe" series shows just how hard it is to get automatic failover and recovery working just right. If you're not ready to become a full-time expert on the topic, and pay for more servers for the same load, then simple replication and manual promotion can result in less downtime and less dataloss. I've seen multiple small startups have cassandra clusters fail, because they did not maintain them properly. I've seen them mysteriously lose data in elasticsearch clusters, for the same reason. That said, GitLab has recently shown that even simple replication can be screwed up. I'd say, the lesson is, the most effective thing is to keep it simple (not "easy"), and avoid disaster caused by mundane mistakes.
- ansible 10y ago
  Yeah, if you can wait another year or two, something like CockroachDB [1] might fit the bill. That is a database that is distributed from the start. So you (theoretically) won't have to worry as much about individual datacenter failures, and scaling. [1] http://www.cockroachlabs.com http://www.cockroachlabs.com
  - StreamBright 10y ago
    I usually wait 5 years with a database/datastore after it goes GA before consider it for anything. Looks promising though.
    - ansible 10y ago
      Yes, I've liked the design from the very start, and they've got a nice group of people working on it. It's supposed to go into a 1.0 release this year. I'll probably try it out on a service where I'm writing simultaneously to two different databases, one local and one distributed. And then log any issues where I see an inconsistency. I won't be relying on it for a while for anything critical.

How far has postgres come w.r.t. setting up a cluster with automatic fail-over and recovery? I didn't see the author address this aspect of Rethink that has a lot going for it.

gaius 10y ago

Have a look at http://repmgr.org http://repmgr.org

williamstein 10y ago
Thanks. Indeed, I'm just using one PostgreSQL master, with no HA, but plan to set something up later, and have put repmgr on my todo list. (I'm the post author.)
- nimrody 10y ago
  A single Postgres machine holds all data? So you scale the database by switching to a stronger machine? Or do you plan to shard the data somehow? Do you have any queries iterating over the entire table?
  - williamstein 10y ago
    > A single Postgres machine holds all data? Yes. It's currently using 80GB of disk, so regarding disk space I can scale for years. I have tiering system where older data gets stored in Google cloud storage, and grabbed on demand. So regarding space, there isn't an issue scaling up. It's also trivial to increase disk space on live VMs on GCE. > So you scale the database by switching to a stronger machine? That's my plan. We're at about 50% overall load as I write this, and here's the usage on the database server: "load average: 0.09, 0.16, 0.17". We're collecting historical Prometheus and other monitoring about load and queries, etc. By the time we need 32 cpus (the GCE limit), either the GCE limit will be raised, the company will be dead (no way in hell), or the company will be wildly successful. So in the only case in which I need to scale out, it will be an exciting new problem that I can hire a team to help with, and we'll benefit from understanding exactly what the problem is we need to solve. I have wasted an enormous amount of time until now due to premature worry about scalability, fueled by my naive assumptions about how quickly SageMathCloud would grow.

ploxiln 10y ago

Clustering and automatic fail-over and recovery is not always a great idea. It sounds like you want it implemented so you "don't have to worry about it", but fail-over and recovery systems are a lot more complicated and troublesome than an alert, some read-only degraded operation, and a manual promotion. Aphyr's "call me maybe" series shows just how hard it is to get automatic failover and recovery working just right. If you're not ready to become a full-time expert on the topic, and pay for more servers for the same load, then simple replication and manual promotion can result in less downtime and less dataloss. I've seen multiple small startups have cassandra clusters fail, because they did not maintain them properly. I've seen them mysteriously lose data in elasticsearch clusters, for the same reason. That said, GitLab has recently shown that even simple replication can be screwed up. I'd say, the lesson is, the most effective thing is to keep it simple (not "easy"), and avoid disaster caused by mundane mistakes.

ansible 10y ago
Yeah, if you can wait another year or two, something like CockroachDB [1] might fit the bill. That is a database that is distributed from the start. So you (theoretically) won't have to worry as much about individual datacenter failures, and scaling. [1] http://www.cockroachlabs.com http://www.cockroachlabs.com
- StreamBright 10y ago
  I usually wait 5 years with a database/datastore after it goes GA before consider it for anything. Looks promising though.
  - ansible 10y ago
    Yes, I've liked the design from the very start, and they've got a nice group of people working on it. It's supposed to go into a 1.0 release this year. I'll probably try it out on a service where I'm writing simultaneously to two different databases, one local and one distributed. And then log any issues where I see an inconsistency. I won't be relying on it for a while for anything critical.