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.
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)
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
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.
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.
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).
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?
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.
13 comments
[ 5.5 ms ] story [ 40.8 ms ] threadhttps://github.com/aws/pgactive/tree/main/docs
RDS uses block replication. Aurora uses it's own SAN replication layer.
DMS maybe?
This is not a way to get better performance or scalability in general.
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)
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
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_ST...
[1] https://github.com/2ndquadrant/pglogical
[2] https://www.enterprisedb.com/docs/pgd/latest/
Pgactive: Active-Active Replication Extension for PostgreSQL on Amazon RDS - https://news.ycombinator.com/item?id=37838223 - Oct 2023 (1 comment)
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).
And I was wondering what other ways, besides schemas, of dividing up 'writer responsibility' would also work? Partitions?
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.