6 ms·

Out of curiosity what do you feel so hard to set up with PostgreSQL? Having a master with a read replica and then making the read replica the master in case th

by aprdm 7y ago

Out of curiosity what do you feel so hard to set up with PostgreSQL? Having a master with a read replica and then making the read replica the master in case the master goes down seems to be a very well known methodology. You can use a virtual ip with both servers behind it (keepalived) or a 0 ttl DNS solution (consul). Is you case more complex than that? With master to master replication I feel there are couple of gotchas and you need to design your data in a way to avoid conflict as much as you can. A solution like bdr seems to be an off-the-shelf one that is proven. I haven't really upgraded postgres in a rapid fashion, usually just keep with whatever version comes with the OS package manager forever.. or whatever docker image I start it with. I guess the same approach for HA can be used to upgrade one at a time, am assuming postgres doesn't break backwards compatibility very often. You can either automate the failover or have a system to SMS you when it happens. I like to do it manually personally since I have seen the automated failover fail more times than the manual procedure. I have probably managed over 30 postgres instances in my services that have grown up to 1/2 PB of data and I've never had a single issue with it... postgres, redis, rabbitmq, consul are technologies that you RTFM, set it up, and it just works! I wouldn't trust a technology that isn't established and widely adopted which is how I see cockroachdb a the moment.

latch 7y ago
No, our setup is basic. We use barman, so I guess repmgr by the same company (2ndQuadrant) would have been the route to go. I could have figured it out, set it up, and been done with it. But, without having done it before, I didn't feel very confident. There seems to be a lot of moving parts, a lot of options, it's changing quite a bit (12.0 introduced additional changes), and I'm still not sure how to do upgrades of major releases without downtime. We still use Postgresql, and I agree, it's setup and forget. But those aren't HA (and don't need to be).
- flowcont 7y ago
  Citus released this last year: https://github.com/citusdata/pg_auto_failover https://github.com/citusdata/pg_auto_failover It looks interesting although I haven't used it, I tried doing it with corosync + pacemaker.
manigandham 7y ago
Postgres is fine but that master/replica/failover connection is fragile, requires a whole bunch of 3rd-party tooling to get right, has downtime while the master is switched over, and is hard to test and be confident in. Compared to all that, CRDB is just a binary on each server pointing to each other. Operational simplicity matters and that's where CRDB is a great alternative.
- aprdm 7y ago
  Why do you say it is fragile, do you have any experiences to share ? Honest question as I never ran into anything
  - manigandham 7y ago
    As stated, it's not natively integrated. You need 3rd-party tools to ensure replication is setup correctly, to monitor the connection status and replication lag, monitor schema changes or anything else that might need a reset, monitor the master, initiate the failover, recover or reset the state after the failover happens, handle changes for WAL archival and backups, and handle any network changes to point back to the master/replica correctly for clients. None of that is automatic and every step introduces problems compounded by the amount of tools used to handle each step. Because of this, the setup is intensive and usually custom to every environment. This is hard to test and be confident in and the source of much downtime or data loss. Meanwhile CRDB handles all of this seamlessly. As long as you have a quorum of healthy nodes, your database is alive and working. Point a load balancer at all of the nodes and you will always have a database to contact with no other overhead. Postgres could get there, but it isn't anywhere there today.
    - aprdm 7y ago
      That's a fair point, thanks for the reply!
AgentME 7y ago
>Having a master with a read replica and then making the read replica the master in case the master goes down seems to be a very well known methodology. You can use a virtual ip with both servers behind it (keepalived) or a 0 ttl DNS solution (consul). >You can either automate the failover or have a system to SMS you when it happens. I don't want to be writing code to make things do automatic failover. I'm not a database engineer. I want a database where its standard setup does this stuff for me. (I would find it really interesting if there was a tightly-integrated "distribution" of Postgresql plus related services that had a setup process as straight-forward as CockroachDB and had automatic read-replicas and failover.)
- aprdm 7y ago
  You usually don’t have to write stuff it is all pretty much yum install and config a file

by aprdm 7y ago

latch 7y ago

No, our setup is basic. We use barman, so I guess repmgr by the same company (2ndQuadrant) would have been the route to go. I could have figured it out, set it up, and been done with it. But, without having done it before, I didn't feel very confident. There seems to be a lot of moving parts, a lot of options, it's changing quite a bit (12.0 introduced additional changes), and I'm still not sure how to do upgrades of major releases without downtime. We still use Postgresql, and I agree, it's setup and forget. But those aren't HA (and don't need to be).

flowcont 7y ago
Citus released this last year: https://github.com/citusdata/pg_auto_failover https://github.com/citusdata/pg_auto_failover It looks interesting although I haven't used it, I tried doing it with corosync + pacemaker.

manigandham 7y ago

Postgres is fine but that master/replica/failover connection is fragile, requires a whole bunch of 3rd-party tooling to get right, has downtime while the master is switched over, and is hard to test and be confident in. Compared to all that, CRDB is just a binary on each server pointing to each other. Operational simplicity matters and that's where CRDB is a great alternative.

aprdm 7y ago
Why do you say it is fragile, do you have any experiences to share ? Honest question as I never ran into anything
- manigandham 7y ago
  As stated, it's not natively integrated. You need 3rd-party tools to ensure replication is setup correctly, to monitor the connection status and replication lag, monitor schema changes or anything else that might need a reset, monitor the master, initiate the failover, recover or reset the state after the failover happens, handle changes for WAL archival and backups, and handle any network changes to point back to the master/replica correctly for clients. None of that is automatic and every step introduces problems compounded by the amount of tools used to handle each step. Because of this, the setup is intensive and usually custom to every environment. This is hard to test and be confident in and the source of much downtime or data loss. Meanwhile CRDB handles all of this seamlessly. As long as you have a quorum of healthy nodes, your database is alive and working. Point a load balancer at all of the nodes and you will always have a database to contact with no other overhead. Postgres could get there, but it isn't anywhere there today.
  - aprdm 7y ago
    That's a fair point, thanks for the reply!

AgentME 7y ago

>Having a master with a read replica and then making the read replica the master in case the master goes down seems to be a very well known methodology. You can use a virtual ip with both servers behind it (keepalived) or a 0 ttl DNS solution (consul). >You can either automate the failover or have a system to SMS you when it happens. I don't want to be writing code to make things do automatic failover. I'm not a database engineer. I want a database where its standard setup does this stuff for me. (I would find it really interesting if there was a tightly-integrated "distribution" of Postgresql plus related services that had a setup process as straight-forward as CockroachDB and had automatic read-replicas and failover.)

aprdm 7y ago
You usually don’t have to write stuff it is all pretty much yum install and config a file