6 ms·

Pgactive: Postgres active-active replication extension

355 points by ForHackernews 1y ago

zknill 1y ago
Looks like it uses Postgres Logical replication to share changes made on one postgres instance to another. Conflict resolution is last-write-wins based on timestamp. Conflicting transactions are logged to a special table (pgactive_conflict_history), so you can see the history, resolve, etc. https://github.com/aws/pgactive/tree/main/docs https://github.com/aws/pgactive/tree/main/docs
- kosolam 1y ago
  Sounds interesting. So how soon one knows if his write has been accepted or rejected? Is it immediate or eventual?
  - ForHackernews 1y ago
    It's eventual consistency: Latest-write wins after the dust settles. As I understand it, this is a wrapper on top of Postgres' native logical replication features. Writes are committed locally and then published via a replication slot to subscriber nodes. You have ACID guarantees locally, but not across the entire distributed system. https://www.postgresql.org/docs/current/logical-replication.html https://www.postgresql.org/docs/current/logical-replication....
    - gritzko 1y ago
      So the outcomes are essentially random? It all feels like they expect developers to sift through the conflict log to resolve things manually or something. If a transaction did not go through on some of the nodes, what are the others doing then? What if they can not roll it back safely? Such a rabbit hole.
      - deleted 1y ago
        [deleted]
      - zknill 1y ago
        Typically applications will have some kind of logical separation of the data. Given this is targeted at replication of postgres nodes, perhaps the nodes are deployed across different regions of the globe. By using active-active replication, all the participating nodes are capable of accepting writes, which simplifies the deployment and querying of postgres (you can read and write to your region-local postgres node). Now that doesn't mean that all the reads and writes will be on conflicting data. Take the regional example, perhaps the majority of the writes affecting one region's data are made _in that region_. In this case, the region local postgres would be performing all the conflict resolution locally, and sharing the updates with the other nodes. The reason this simplifies things, is that you can treat all your postgres connections as-if they are just a single postgres. Writes are fast, because they are accepted in the local region, and reads are replicated without you having to have a dedicated read-replica. Ofc you're still going to have to design around the conflict resolution (i.e. writes for the same data issued against different instances), and the possibility of stale reads as the data is replicated cross-node. But for some applications, this design might be a significant benefit, even with the extra things you need to do.
        gritzko 1y ago
        I think I understand the use case. Like, we have in fact several regional Postgreses, but we want them to be one physical database for the sake of simplicity. Probably this should be in the motivational part of the README.
      - ForHackernews 1y ago
        In our case, we're designing around INSERT-only tables with a composite primary key that includes the site id, so (in theory) there will never be any conflicts that need resolution.
        zozbot234 1y ago
        > with a composite primary key that includes the site id It doesn't look like you'd need multi master replication in that case? You could simply partition tables by site and rely on logical replication.
  - okigan 1y ago
    It took 20 years to acknowledge that pushing eventual consistency to application layer is not worth it for most applications. Seems the same is playing out out in Postgres with this extension, maybe will take it another 20 years
    - tinkertamper 1y ago
      I'm curious about what you mean here. It sounds like you're saying that applications shouldn't concern themselves with consistency. Can you elaborate?
    - rubiquity 1y ago
      The idea of active-active is too seductive compared to how hard learning distributed systems is.
      - okigan 1y ago
        It is so seductive that people don’t read the footnotes that explain that active-active does not do what they think it does.
        m11a 1y ago
        I'd agree. There's so many footguns involved in multi-master setups, that most organisations should avoid this until they're big enough to hire distributed systems engineers to design a proper solution for the company. I personally don't love any of the Postgres multi-master solutions. You can scale surprisingly far on a single-master Postgres with read replicas.
- zozbot234 1y ago
  Is this multi-master replication? It will be interesting if it can be accepted into Postgres proper.
  - stephenr 1y ago
    Sounds like "yes, with an if" where the "if" is "if you don't really care about data consistency". "Last write wins" sounds like a recipe for disaster IMO. This is still one of those things that keeps people on MySQL - there are not one, but two open-source solutions available that provide synchronous cluster replication, allowing for "safe" writes against multiple primaries.
    - nyrikki 1y ago
      It's all tradeoffs, with MySQL multi-master and multi-source models having their own issues and pg also has other options with their own tradoffs. ACID+distributed== tradoffs that will always keep this a horses for courses problem.
    - wfn 1y ago
      Out of curiosity, what conflict resolution options exist in mysql and/or mysql cluster (never checked / exp. in PG)? Because you'll always have to address conflicts of course - we come to CAP / PACELC. Hm [1][2] - looks like they support more strategies (possibly) but I mean none of them are somehow magical, and timestamp comparison based methods comprise the better part of offered strategy set (looks like?) - and "latest timestamp wins" at least used to be the default (did not read thoroughly mind you, was just curious)? But I could be totally wrong - (1) curious if someone could link to things / explain, and (2) fyi ('stephenr) last write wins based on timestamp is a thing im mysql world as well (though again maybe set of options / different conflict resolution methods available is larger in mysql?) [1]: https://dev.mysql.com/doc/refman/8.4/en/mysql-cluster-replication-conflict-resolution.html https://dev.mysql.com/doc/refman/8.4/en/mysql-cluster-replic... [2]: https://dev.mysql.com/blog-archive/enhanced-conflict-resolution-with-mysql-cluster-active-active-replication/ https://dev.mysql.com/blog-archive/enhanced-conflict-resolut... (nice writeup, maybe outdated idk?)
everfrustrated 1y ago
I'm scratching my head trying to think why AWS would have worked on this? I can't think of it being used in any of their products. RDS uses block replication. Aurora uses it's own SAN replication layer. DMS maybe?
- gotimo 1y ago
  probably [Aurora DSQL](https://aws.amazon.com/rds/aurora/dsql/ https://aws.amazon.com/rds/aurora/dsql/) which released a couple of weeks ago
  - fizx 1y ago
    I thought dsql was distributed transactions. :thinking:
    - mjb 1y ago
      DSQL does use distributed transactions to offer active-active alongside strong consistency and isolation guarantees. Here's how: https://brooker.co.za/blog/2024/12/05/inside-dsql-writes.html https://brooker.co.za/blog/2024/12/05/inside-dsql-writes.htm...
  - loginatnine 1y ago
    It's definitely DSQL with the multi-region active active feature[1]. [1]https://aws.amazon.com/rds/aurora/dsql/features/#topic-1 https://aws.amazon.com/rds/aurora/dsql/features/#topic-1
    - riknos314 1y ago
      DSQL only uses Postgres for the query processor layer, so it doesn't require a replication library within postgres itself. Definitely NOT from DSQL. > We’re not using any of the storage or transaction processing parts of PostgreSQL, but are using the SQL engine, an adapted version of the planner and optimizer, and the client protocol implementation. [1] Rather, DSQL seems to do its region replication using the distributed journal abstraction [2]. [1] https://brooker.co.za/blog/2024/12/04/inside-dsql.html https://brooker.co.za/blog/2024/12/04/inside-dsql.html [2] https://brooker.co.za/blog/2024/12/06/inside-dsql-cap.html https://brooker.co.za/blog/2024/12/06/inside-dsql-cap.html
ltbarcly3 1y ago
Don't use this unless you know exactly what you are doing. This is not a way to get better performance or scalability in general.
- nijave 1y ago
  Seems rather niche. You can already improve async write availability with CQRS and a (durable) queue. Systems like Kafka implement this out of the box Seems sort of like a CQRS implementation on top of PG (you're using PG replication as the change queue to loosely separate writes/reads, losing transaction guarantees in the process)
dangoodmanUT 1y ago
It seems async? That's a major problem for transaction isolation
- dboreham 1y ago
  Pick your poison.
nico 1y ago
Tangential, but related. Is there a way to have a "locally writable" read replica, ie. a secondary db that reads from a primary, but that can also hold local changes that doesn't send back to the primary? One of the use cases is to have a development db that can get data from production or staging (and doesn't send local changes back) What I've done usually is have some script/cron/worker run periodically to get data, either via dump or running some queries, create a snapshot, store it in S3, then have a script on the local dev code that gets the snapshot and inserts/restores the data in the local db. This works for many cases, but index building can be a pain (take a long time), depending on the data
- AYBABTME 1y ago
  Just FYI that most people would recommend against doing this for legal reasons. PII information and the likes are not usually allowed to land in a staging or dev environment, for various reasons. Doing this or allowing it, is a huge liability.
  - nico 1y ago
    Agreed, and there’s a few ways to deal with that, like not including certain tables or excluding the data via queries when creating the snapshot Having said that, legal exposure and risk will highly depend on what you are working on. Probably for most projects this isn’t a big deal. IANAL, this is not legal advice
- xinu2020 1y ago
  Curious about this - How would local writes conflicting with remote updates be handled? I can't think of a merge strategy working on all scenario (or even most of the time)
  - nico 1y ago
    Great question, I don't know. However, at least in my case, I wouldn't mind the source data always overwriting the local data. In fact, that's the way it works now when loading a newer snapshot, the local db is dropped and then re-built from the snapshot Thinking about the developer experience though, when loading a snapshot manually, the dev knows they are overwriting their local db. However, if replication happened automatically/continuously on the background, it could lead to some really confusing/annoying behaviors
eatonphil 1y ago
A bit of the history as I've been told by 2nd Quadrant/EDB people (my teammates): BDR1 [0] came first and was, and is, open source. pgactive is based on BDR1. BDR2 was a closed-source rewrite of BDR1 that was later abandoned. pglogical v1 and v2 (PGL1, PGL2) were, and are, open-source [1]. pglogical v1, after heavy modification, was eventually merged into Postgres 10. Based on learnings from this logical replication in Postgres 10, 2nd Quadrant started pglogical v2. pgEdge is based on pglogical v2. Then later 2nd Quadrant started pglogical v3 (closed source) and BDR v3 (closed source). They were merged into just BDR v4. At some point the BDR product was renamed to Postgres Distributed (PGD) [2]. 2ndQuadrant was acquired by EDB. We (EDB) just released PGD v6. [0] https://github.com/2ndQuadrant/bdr/tree/bdr-plugin/REL1_0_STABLE https://github.com/2ndQuadrant/bdr/tree/bdr-plugin/REL1_0_ST... [1] https://github.com/2ndquadrant/pglogical https://github.com/2ndquadrant/pglogical [2] https://www.enterprisedb.com/docs/pgd/latest/ https://www.enterprisedb.com/docs/pgd/latest/
- cbsmith 1y ago
  PGDv6 is still closed source, yeah?
  - eatonphil 1y ago
    That's right.
- x4m 1y ago
  And there's also https://postgrespro.com/docs/enterprise/current/multimaster https://postgrespro.com/docs/enterprise/current/multimaster With a history behind it.
  - eatonphil 1y ago
    Right, I'm not so familiar with it but from that page: > The replication mechanism is based on logical decoding and an earlier version of the pglogical extension provided for community by the 2ndQuadrant team.
dang 1y ago
Related. Others? Pgactive: Active-Active Replication Extension for PostgreSQL on Amazon RDS - https://news.ycombinator.com/item?id=37838223 https://news.ycombinator.com/item?id=37838223 - Oct 2023 (1 comment)
iotapi322 1y ago
After setting up numerous clusters with repmgr and patroni along with running them in zero down time production... This is the very last plugin i would ever install. I like to sleep at night.
ahachete 1y ago
I'm not tired of reminding everyone that "conflict resolution" is no more than an euphemism for "breaking durability by dropping already committed and acknowledged data". Either architect for no data overlap on writes across all the "actives" (in which case software like pgactive could be a good deal) or use a purely distributed database (like Yugabyte).
- anentropic 1y ago
  I could see in the docs they recommended a scenario like: each master is the only writer for a given schema, to avoid conflicts, but the replication gives them all a copy of all the schemas to read from. And I was wondering what other ways, besides schemas, of dividing up 'writer responsibility' would also work? Partitions?
  - ahachete 1y ago
    For inserts is "easy" if there's no possibility to write the same PK value twice (e.g. PK are random values like uuids or include as part of a natural key a identifier from the writer that's unique -shard id, region id, you name it-). Once you have done that, for updates and deletes you need to keep the same rule (i.e. don't update "foreign" rows). If you do this, no other technique is needed. Partitions, however, are potentially a good technique to enforce some of these invariants, which gives us quick understanding of where data is originating from given the table name. Same could apply to schemas. RLS may also help enforce these invariants.
mosselman 1y ago
Coincidentally I’ve been trying to figure out a nice no-nonsense way to setup a HA postgres cluster with automatic failover and restoration of nodes and point in time recovery. I see a lot of patroni with etcd and haproxy being advised. It must work well for people to be so excited about it, but it feels a bit overwhelming to me when I look at the docker compose files. At the same time there is pgool which looks like mostly a single thing to deploy in front of each postgres server. Any tips from the pg-interested people here? I’d like a docker compose like experience to setup a cluster that is highly available with point in time recovery or at least no data loss.
- fancy_pantser 1y ago
  Are you looking for a tool like Barman?
  - hans_castorp 1y ago
    For failover handling repmgr is probably better (they can work together though)
    - mosselman 1y ago
      Thanks. What role does repmgr play?
  - mosselman 1y ago
    I don't know. Barman would only be part of the HA puzzle no?
- Tostino 1y ago
  I've been using cloudnativepg and it is pretty turn key for all of that.
  - nijave 1y ago
    Needs Kubernetes, though which might throw a complexity wrench in if you're not familiar. If you are familiar, then it's about as turn-key as you can get. There's also a different PG operator based on Patroni that's supposed to work pretty well iirc
the-alchemist 1y ago
Speaking of databases, has anyone run Bloomberg's db (https://github.com/bloomberg/comdb2 https://github.com/bloomberg/comdb2) recently?

355 points by ForHackernews 1y ago

Looks like it uses Postgres Logical replication to share changes made on one postgres instance to another. Conflict resolution is last-write-wins based on timestamp. Conflicting transactions are logged to a special table (pgactive_conflict_history), so you can see the history, resolve, etc. https://github.com/aws/pgactive/tree/main/docs https://github.com/aws/pgactive/tree/main/docs

kosolam 1y ago
Sounds interesting. So how soon one knows if his write has been accepted or rejected? Is it immediate or eventual?
- ForHackernews 1y ago
  It's eventual consistency: Latest-write wins after the dust settles. As I understand it, this is a wrapper on top of Postgres' native logical replication features. Writes are committed locally and then published via a replication slot to subscriber nodes. You have ACID guarantees locally, but not across the entire distributed system. https://www.postgresql.org/docs/current/logical-replication.html https://www.postgresql.org/docs/current/logical-replication....
  - gritzko 1y ago
    So the outcomes are essentially random? It all feels like they expect developers to sift through the conflict log to resolve things manually or something. If a transaction did not go through on some of the nodes, what are the others doing then? What if they can not roll it back safely? Such a rabbit hole.
    - deleted 1y ago
      [deleted]
    - zknill 1y ago
      Typically applications will have some kind of logical separation of the data. Given this is targeted at replication of postgres nodes, perhaps the nodes are deployed across different regions of the globe. By using active-active replication, all the participating nodes are capable of accepting writes, which simplifies the deployment and querying of postgres (you can read and write to your region-local postgres node). Now that doesn't mean that all the reads and writes will be on conflicting data. Take the regional example, perhaps the majority of the writes affecting one region's data are made _in that region_. In this case, the region local postgres would be performing all the conflict resolution locally, and sharing the updates with the other nodes. The reason this simplifies things, is that you can treat all your postgres connections as-if they are just a single postgres. Writes are fast, because they are accepted in the local region, and reads are replicated without you having to have a dedicated read-replica. Ofc you're still going to have to design around the conflict resolution (i.e. writes for the same data issued against different instances), and the possibility of stale reads as the data is replicated cross-node. But for some applications, this design might be a significant benefit, even with the extra things you need to do.
      - gritzko 1y ago
        I think I understand the use case. Like, we have in fact several regional Postgreses, but we want them to be one physical database for the sake of simplicity. Probably this should be in the motivational part of the README.
    - ForHackernews 1y ago
      In our case, we're designing around INSERT-only tables with a composite primary key that includes the site id, so (in theory) there will never be any conflicts that need resolution.
      - zozbot234 1y ago
        > with a composite primary key that includes the site id It doesn't look like you'd need multi master replication in that case? You could simply partition tables by site and rely on logical replication.
- okigan 1y ago
  It took 20 years to acknowledge that pushing eventual consistency to application layer is not worth it for most applications. Seems the same is playing out out in Postgres with this extension, maybe will take it another 20 years
  - tinkertamper 1y ago
    I'm curious about what you mean here. It sounds like you're saying that applications shouldn't concern themselves with consistency. Can you elaborate?
  - rubiquity 1y ago
    The idea of active-active is too seductive compared to how hard learning distributed systems is.
    - okigan 1y ago
      It is so seductive that people don’t read the footnotes that explain that active-active does not do what they think it does.
      - m11a 1y ago
        I'd agree. There's so many footguns involved in multi-master setups, that most organisations should avoid this until they're big enough to hire distributed systems engineers to design a proper solution for the company. I personally don't love any of the Postgres multi-master solutions. You can scale surprisingly far on a single-master Postgres with read replicas.
zozbot234 1y ago
Is this multi-master replication? It will be interesting if it can be accepted into Postgres proper.
- stephenr 1y ago
  Sounds like "yes, with an if" where the "if" is "if you don't really care about data consistency". "Last write wins" sounds like a recipe for disaster IMO. This is still one of those things that keeps people on MySQL - there are not one, but two open-source solutions available that provide synchronous cluster replication, allowing for "safe" writes against multiple primaries.
  - nyrikki 1y ago
    It's all tradeoffs, with MySQL multi-master and multi-source models having their own issues and pg also has other options with their own tradoffs. ACID+distributed== tradoffs that will always keep this a horses for courses problem.
  - wfn 1y ago
    Out of curiosity, what conflict resolution options exist in mysql and/or mysql cluster (never checked / exp. in PG)? Because you'll always have to address conflicts of course - we come to CAP / PACELC. Hm [1][2] - looks like they support more strategies (possibly) but I mean none of them are somehow magical, and timestamp comparison based methods comprise the better part of offered strategy set (looks like?) - and "latest timestamp wins" at least used to be the default (did not read thoroughly mind you, was just curious)? But I could be totally wrong - (1) curious if someone could link to things / explain, and (2) fyi ('stephenr) last write wins based on timestamp is a thing im mysql world as well (though again maybe set of options / different conflict resolution methods available is larger in mysql?) [1]: https://dev.mysql.com/doc/refman/8.4/en/mysql-cluster-replication-conflict-resolution.html https://dev.mysql.com/doc/refman/8.4/en/mysql-cluster-replic... [2]: https://dev.mysql.com/blog-archive/enhanced-conflict-resolution-with-mysql-cluster-active-active-replication/ https://dev.mysql.com/blog-archive/enhanced-conflict-resolut... (nice writeup, maybe outdated idk?)

everfrustrated 1y ago

I'm scratching my head trying to think why AWS would have worked on this? I can't think of it being used in any of their products. RDS uses block replication. Aurora uses it's own SAN replication layer. DMS maybe?

gotimo 1y ago
probably [Aurora DSQL](https://aws.amazon.com/rds/aurora/dsql/ https://aws.amazon.com/rds/aurora/dsql/) which released a couple of weeks ago
- fizx 1y ago
  I thought dsql was distributed transactions. :thinking:
  - mjb 1y ago
    DSQL does use distributed transactions to offer active-active alongside strong consistency and isolation guarantees. Here's how: https://brooker.co.za/blog/2024/12/05/inside-dsql-writes.html https://brooker.co.za/blog/2024/12/05/inside-dsql-writes.htm...
- loginatnine 1y ago
  It's definitely DSQL with the multi-region active active feature[1]. [1]https://aws.amazon.com/rds/aurora/dsql/features/#topic-1 https://aws.amazon.com/rds/aurora/dsql/features/#topic-1
  - riknos314 1y ago
    DSQL only uses Postgres for the query processor layer, so it doesn't require a replication library within postgres itself. Definitely NOT from DSQL. > We’re not using any of the storage or transaction processing parts of PostgreSQL, but are using the SQL engine, an adapted version of the planner and optimizer, and the client protocol implementation. [1] Rather, DSQL seems to do its region replication using the distributed journal abstraction [2]. [1] https://brooker.co.za/blog/2024/12/04/inside-dsql.html https://brooker.co.za/blog/2024/12/04/inside-dsql.html [2] https://brooker.co.za/blog/2024/12/06/inside-dsql-cap.html https://brooker.co.za/blog/2024/12/06/inside-dsql-cap.html

ltbarcly3 1y ago

Don't use this unless you know exactly what you are doing. This is not a way to get better performance or scalability in general.

nijave 1y ago
Seems rather niche. You can already improve async write availability with CQRS and a (durable) queue. Systems like Kafka implement this out of the box Seems sort of like a CQRS implementation on top of PG (you're using PG replication as the change queue to loosely separate writes/reads, losing transaction guarantees in the process)

dangoodmanUT 1y ago

It seems async? That's a major problem for transaction isolation

dboreham 1y ago
Pick your poison.

nico 1y ago

Tangential, but related. Is there a way to have a "locally writable" read replica, ie. a secondary db that reads from a primary, but that can also hold local changes that doesn't send back to the primary? One of the use cases is to have a development db that can get data from production or staging (and doesn't send local changes back) What I've done usually is have some script/cron/worker run periodically to get data, either via dump or running some queries, create a snapshot, store it in S3, then have a script on the local dev code that gets the snapshot and inserts/restores the data in the local db. This works for many cases, but index building can be a pain (take a long time), depending on the data

AYBABTME 1y ago
Just FYI that most people would recommend against doing this for legal reasons. PII information and the likes are not usually allowed to land in a staging or dev environment, for various reasons. Doing this or allowing it, is a huge liability.
- nico 1y ago
  Agreed, and there’s a few ways to deal with that, like not including certain tables or excluding the data via queries when creating the snapshot Having said that, legal exposure and risk will highly depend on what you are working on. Probably for most projects this isn’t a big deal. IANAL, this is not legal advice
xinu2020 1y ago
Curious about this - How would local writes conflicting with remote updates be handled? I can't think of a merge strategy working on all scenario (or even most of the time)
- nico 1y ago
  Great question, I don't know. However, at least in my case, I wouldn't mind the source data always overwriting the local data. In fact, that's the way it works now when loading a newer snapshot, the local db is dropped and then re-built from the snapshot Thinking about the developer experience though, when loading a snapshot manually, the dev knows they are overwriting their local db. However, if replication happened automatically/continuously on the background, it could lead to some really confusing/annoying behaviors

eatonphil 1y ago

A bit of the history as I've been told by 2nd Quadrant/EDB people (my teammates): BDR1 [0] came first and was, and is, open source. pgactive is based on BDR1. BDR2 was a closed-source rewrite of BDR1 that was later abandoned. pglogical v1 and v2 (PGL1, PGL2) were, and are, open-source [1]. pglogical v1, after heavy modification, was eventually merged into Postgres 10. Based on learnings from this logical replication in Postgres 10, 2nd Quadrant started pglogical v2. pgEdge is based on pglogical v2. Then later 2nd Quadrant started pglogical v3 (closed source) and BDR v3 (closed source). They were merged into just BDR v4. At some point the BDR product was renamed to Postgres Distributed (PGD) [2]. 2ndQuadrant was acquired by EDB. We (EDB) just released PGD v6. [0] https://github.com/2ndQuadrant/bdr/tree/bdr-plugin/REL1_0_STABLE https://github.com/2ndQuadrant/bdr/tree/bdr-plugin/REL1_0_ST... [1] https://github.com/2ndquadrant/pglogical https://github.com/2ndquadrant/pglogical [2] https://www.enterprisedb.com/docs/pgd/latest/ https://www.enterprisedb.com/docs/pgd/latest/

cbsmith 1y ago
PGDv6 is still closed source, yeah?
- eatonphil 1y ago
  That's right.
x4m 1y ago
And there's also https://postgrespro.com/docs/enterprise/current/multimaster https://postgrespro.com/docs/enterprise/current/multimaster With a history behind it.
- eatonphil 1y ago
  Right, I'm not so familiar with it but from that page: > The replication mechanism is based on logical decoding and an earlier version of the pglogical extension provided for community by the 2ndQuadrant team.

dang 1y ago

Related. Others? Pgactive: Active-Active Replication Extension for PostgreSQL on Amazon RDS - https://news.ycombinator.com/item?id=37838223 https://news.ycombinator.com/item?id=37838223 - Oct 2023 (1 comment)

iotapi322 1y ago

After setting up numerous clusters with repmgr and patroni along with running them in zero down time production... This is the very last plugin i would ever install. I like to sleep at night.

ahachete 1y ago

I'm not tired of reminding everyone that "conflict resolution" is no more than an euphemism for "breaking durability by dropping already committed and acknowledged data". Either architect for no data overlap on writes across all the "actives" (in which case software like pgactive could be a good deal) or use a purely distributed database (like Yugabyte).

anentropic 1y ago
I could see in the docs they recommended a scenario like: each master is the only writer for a given schema, to avoid conflicts, but the replication gives them all a copy of all the schemas to read from. And I was wondering what other ways, besides schemas, of dividing up 'writer responsibility' would also work? Partitions?
- ahachete 1y ago
  For inserts is "easy" if there's no possibility to write the same PK value twice (e.g. PK are random values like uuids or include as part of a natural key a identifier from the writer that's unique -shard id, region id, you name it-). Once you have done that, for updates and deletes you need to keep the same rule (i.e. don't update "foreign" rows). If you do this, no other technique is needed. Partitions, however, are potentially a good technique to enforce some of these invariants, which gives us quick understanding of where data is originating from given the table name. Same could apply to schemas. RLS may also help enforce these invariants.

mosselman 1y ago

Coincidentally I’ve been trying to figure out a nice no-nonsense way to setup a HA postgres cluster with automatic failover and restoration of nodes and point in time recovery. I see a lot of patroni with etcd and haproxy being advised. It must work well for people to be so excited about it, but it feels a bit overwhelming to me when I look at the docker compose files. At the same time there is pgool which looks like mostly a single thing to deploy in front of each postgres server. Any tips from the pg-interested people here? I’d like a docker compose like experience to setup a cluster that is highly available with point in time recovery or at least no data loss.

fancy_pantser 1y ago
Are you looking for a tool like Barman?
- hans_castorp 1y ago
  For failover handling repmgr is probably better (they can work together though)
  - mosselman 1y ago
    Thanks. What role does repmgr play?
- mosselman 1y ago
  I don't know. Barman would only be part of the HA puzzle no?
Tostino 1y ago
I've been using cloudnativepg and it is pretty turn key for all of that.
- nijave 1y ago
  Needs Kubernetes, though which might throw a complexity wrench in if you're not familiar. If you are familiar, then it's about as turn-key as you can get. There's also a different PG operator based on Patroni that's supposed to work pretty well iirc

the-alchemist 1y ago

Speaking of databases, has anyone run Bloomberg's db (https://github.com/bloomberg/comdb2 https://github.com/bloomberg/comdb2) recently?