4 ms·

What's the favored way to run highly available postgres these days?

by dpedu 4y ago

bsagdiyev 4y ago
I use Patroni behind HAProxy setup to automatically point to the new active host in the case of a failover. It's not a huge scale setup though so I'm sure there will be better ways or other input on this.
whitepoplar 4y ago
pg_auto_failover is nice: https://github.com/citusdata/pg_auto_failover https://github.com/citusdata/pg_auto_failover
cube2222 4y ago
AWS Aurora has been working well for me in general.
- baq 4y ago
  How painful is the cost?
  - throwawaythekey 4y ago
    It's been a while now but we saved about 10-100x moving from aurora to dynamo for a pure/simplish OLTP use case. Lost a lot of flexibility but the aurora costs were going to send us under. We also had a few issues where aurora was giving us *incorrect query results* and aws support were not helpful. Most of the issues were denied, even with simple reproductions, and then got fixed years later (~2-3) after we had given up. We still use it today but stay away from cutting edge features, unusual query patterns, and high volume use cases. Having said all that, SQL still seems like the correct choice for getting an MVP out quickly.
  - phamilton 4y ago
    Not OP, but a big Aurora fan. Not that bad actually, but it varies by use case. Reasons aurora can be cheaper than you think: 1) Autoscaling. Adding a new reader takes 15 minutes. Instead of provisioning for peak traffic and paying for it 24/7, run an extra reader for a few hours each day. 2) Metered IO. Aurora IO can handle 400k+ iops when needed. Or it can hum along at 100 iops. You pay only for what you use, you don't have to provision for peak load 24/7. Switching to Aurora saved us money over vanilla RDS. Self hosting postgres may be cheaper, but not by as much as would first appear.
  - benji-york 4y ago
    Here's a cost calculator: https://calculator.aws/#/addService/RDSPostgreSQL https://calculator.aws/#/addService/RDSPostgreSQL My rule of thumb is about $1,000 per month for a reasonably large DB in RDS.
throw0101c 4y ago
I'd like to know answers on this. The main thing I like about MySQL-based solutions is the availability of Galera multi-master software, which for simple configurations is very easy to get going. Drop a fairly cookie-cutter config on each host, and you're done: # Galera Provider Configuration wsrep_on=ON wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so # Galera Cluster Configuration wsrep_cluster_name="test_cluster" wsrep_cluster_address="gcomm://First_Node_IP,Second_Node_IP,Third_Node_IP" # Galera Synchronization Configuration wsrep_sst_method=rsync # Galera Node Configuration wsrep_node_address="This_Node_IP" wsrep_node_name="This_Node_Name" Combine with keepalived on each node which does a health check, and if one goes down/bad, the vIP is moved over to another host in the cluster with minimal fuss.
- jmartrican 4y ago
  Is that part of Galera free or licensed?
  - throw0101c 4y ago
    GPL 2.0 * https://github.com/codership/galera https://github.com/codership/galera
rektide 4y ago
My favorite route right now is running a postgres operator on Kubernetes & letting it do all the work for me. Zalando's operator use Patroni under the hood, to create a cluster over streaming replication. It also has Spilo, which orchestrates pg_basebackup or WAL-E for point-in-time backup. https://github.com/zalando/postgres-operator#postgresql-features https://github.com/zalando/postgres-operator#postgresql-feat... CrunchyData operator seems to have built their own streaming replication system coordinated by Raft. https://access.crunchydata.com/documentation/postgres-operator/v5/architecture/high-availability/#synchronous-replication-guarding-against-transactions-loss https://access.crunchydata.com/documentation/postgres-operat... Both are fantastically featureful well-integrated operators that are super well maintained. Both are very recommendable.
Ingaz 4y ago
Run Yugabyte maybe

What's the favored way to run highly available postgres these days?

by dpedu 4y ago

What's the favored way to run highly available postgres these days?

I use Patroni behind HAProxy setup to automatically point to the new active host in the case of a failover. It's not a huge scale setup though so I'm sure there will be better ways or other input on this.

whitepoplar 4y ago

pg_auto_failover is nice: https://github.com/citusdata/pg_auto_failover https://github.com/citusdata/pg_auto_failover

cube2222 4y ago

AWS Aurora has been working well for me in general.

baq 4y ago
How painful is the cost?
- throwawaythekey 4y ago
  It's been a while now but we saved about 10-100x moving from aurora to dynamo for a pure/simplish OLTP use case. Lost a lot of flexibility but the aurora costs were going to send us under. We also had a few issues where aurora was giving us *incorrect query results* and aws support were not helpful. Most of the issues were denied, even with simple reproductions, and then got fixed years later (~2-3) after we had given up. We still use it today but stay away from cutting edge features, unusual query patterns, and high volume use cases. Having said all that, SQL still seems like the correct choice for getting an MVP out quickly.
- phamilton 4y ago
  Not OP, but a big Aurora fan. Not that bad actually, but it varies by use case. Reasons aurora can be cheaper than you think: 1) Autoscaling. Adding a new reader takes 15 minutes. Instead of provisioning for peak traffic and paying for it 24/7, run an extra reader for a few hours each day. 2) Metered IO. Aurora IO can handle 400k+ iops when needed. Or it can hum along at 100 iops. You pay only for what you use, you don't have to provision for peak load 24/7. Switching to Aurora saved us money over vanilla RDS. Self hosting postgres may be cheaper, but not by as much as would first appear.
- benji-york 4y ago
  Here's a cost calculator: https://calculator.aws/#/addService/RDSPostgreSQL https://calculator.aws/#/addService/RDSPostgreSQL My rule of thumb is about $1,000 per month for a reasonably large DB in RDS.

throw0101c 4y ago

I'd like to know answers on this. The main thing I like about MySQL-based solutions is the availability of Galera multi-master software, which for simple configurations is very easy to get going. Drop a fairly cookie-cutter config on each host, and you're done: # Galera Provider Configuration wsrep_on=ON wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so # Galera Cluster Configuration wsrep_cluster_name="test_cluster" wsrep_cluster_address="gcomm://First_Node_IP,Second_Node_IP,Third_Node_IP" # Galera Synchronization Configuration wsrep_sst_method=rsync # Galera Node Configuration wsrep_node_address="This_Node_IP" wsrep_node_name="This_Node_Name" Combine with keepalived on each node which does a health check, and if one goes down/bad, the vIP is moved over to another host in the cluster with minimal fuss.

jmartrican 4y ago
Is that part of Galera free or licensed?
- throw0101c 4y ago
  GPL 2.0 * https://github.com/codership/galera https://github.com/codership/galera

rektide 4y ago

My favorite route right now is running a postgres operator on Kubernetes & letting it do all the work for me. Zalando's operator use Patroni under the hood, to create a cluster over streaming replication. It also has Spilo, which orchestrates pg_basebackup or WAL-E for point-in-time backup. https://github.com/zalando/postgres-operator#postgresql-features https://github.com/zalando/postgres-operator#postgresql-feat... CrunchyData operator seems to have built their own streaming replication system coordinated by Raft. https://access.crunchydata.com/documentation/postgres-operator/v5/architecture/high-availability/#synchronous-replication-guarding-against-transactions-loss https://access.crunchydata.com/documentation/postgres-operat... Both are fantastically featureful well-integrated operators that are super well maintained. Both are very recommendable.

Ingaz 4y ago

Run Yugabyte maybe