4 ms·

Sorry if I wasn't clear, but the goal of the migration wasn't for scale, it was for HA. I find it difficult to setup PostgreSQL without having a single point o

by latch 7y ago

Sorry if I wasn't clear, but the goal of the migration wasn't for scale, it was for HA. I find it difficult to setup PostgreSQL without having a single point of failure and introducing downtime for things such as upgrades. We aren't doing life changing stuff, true, but a downtime in our system can result in employees (often at the bottom of the economic ladder) not being able to get to work. That gnaws at me. I guess the better alternative is to go for a hosted approach, but that isn't without its own complications and challenges.

briandilley 7y ago
> I find it difficult to setup PostgreSQL without having a single point of failure and introducing downtime for things such as upgrades. Amazon does this with RDS, and so can you. This really isn't very tough to achieve with replicas. And for upgrades you just add a new replica with the latest version and promote it to master and replace the remaining nodes one at a time.
- skj 7y ago
  Man I love technical advice that has the word "just" in it. It's so simple!
- shawabawa3 7y ago
  RDS absolutely has downtime for upgrades, and even for master switchover
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.
daxfohl 7y ago
Nah, you're good. I didn't notice that HA was the goal. Saw yet another migrate-to-alt-db post, which is usually about scale, when said scale could easily be handled by SQL, and made the assumption and an unnecessarily snarky comment. That was my fault.

by latch 7y ago

briandilley 7y ago

> I find it difficult to setup PostgreSQL without having a single point of failure and introducing downtime for things such as upgrades. Amazon does this with RDS, and so can you. This really isn't very tough to achieve with replicas. And for upgrades you just add a new replica with the latest version and promote it to master and replace the remaining nodes one at a time.

skj 7y ago
Man I love technical advice that has the word "just" in it. It's so simple!
shawabawa3 7y ago
RDS absolutely has downtime for upgrades, and even for master switchover

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.

daxfohl 7y ago

Nah, you're good. I didn't notice that HA was the goal. Saw yet another migrate-to-alt-db post, which is usually about scale, when said scale could easily be handled by SQL, and made the assumption and an unnecessarily snarky comment. That was my fault.