Pgactive: Postgres active-active replication extension

355 96 1 year ago

zknill2• 1 year 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

kosolam3• 1 year ago

Sounds interesting. So how soon one knows if his write has been accepted or rejected? Is it immediate or eventual?

ForHackernews8• 1 year 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....

okigan2• 1 year 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

zozbot2344• 1 year ago

Is this multi-master replication? It will be interesting if it can be accepted into Postgres proper.

stephenr3• 1 year 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.

dehrmann• 1 year ago

Did Postgres ever get a built-in, blessed replication offering? It's been a while since I set it up, but I remember this was always a big missing feature compared to Mysql.

everfrustrated2• 1 year 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• 1 year ago

probably [Aurora DSQL](https://aws.amazon.com/rds/aurora/dsql/) which released a couple of weeks ago

fizx• 1 year ago

I thought dsql was distributed transactions. :thinking:

loginatnine• 1 year ago

It's definitely DSQL with the multi-region active active feature[1].

[1]https://aws.amazon.com/rds/aurora/dsql/features/#topic-1

tneely• 1 year ago

DSQL uses an internal service, Journal[1], for its mutli-region writes.

[1] https://www.allthingsdistributed.com/2025/05/just-make-it-sc...

the_precipitate• 1 year ago

Yeah, and this doesn't seems to be that useful. At least I don't understand why one should do this on a strong ACID relational database.

hobs2• 1 year ago

In my experience multi-writer is because of latency or HADR stuff - have all your data in all regions at the same time, but the method (via the tlog) seems like it sort of defeats what those sorts of systems might be able to historically do (write multiple places from the app at the same time so as to have the lowest possible chance of data loss.)

ForHackernews8• 1 year ago

Apparently they've offered it as a feature in RDS Postgres for a couple years now https://aws.amazon.com/about-aws/whats-new/2023/10/pgactive-...

But only last month did they officially release it as open source to the community https://aws-news.com/article/2025-06-09-announcing-open-sour...

everfrustrated2• 1 year ago

Thanks, that seems to match.

0xFF0123• 1 year ago

A lot of theorising above, but this seems like the earliest reference

thayne• 1 year ago

Aurora uses it's own SAN replication layer

I don't think that is used for cross region replication

cfycom• 1 year ago

From the readme of the repo: "Use cases for this include running a Multi-Region high availability database cluster"

prdpx7• 1 year ago

i think AWS is using pgactive to sell "Aurora Postgres Global" https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide...

ltbarcly3• 1 year 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.

nijave2• 1 year 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• 1 year ago

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

dboreham2• 1 year ago

Pick your poison.

nico5• 1 year 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• 1 year 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.

nico5• 1 year 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• 1 year 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)

nico5• 1 year 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

mdavidn• 1 year ago

Load the snapshot to a "pristine" local database that you never modify. Whenever you need a "reset" of your dev database, drop it, then copy the pristine database using `createdb --template`. This copies prebuilt indexes rather than rebuild them, which is much faster.

nico5• 1 year ago

But when loading that pristine local db from the original source, it would still create the indices and thus take a long time?

The goal is not necessarily having an easy way to reset, but rather an easy/quick way to load real data

ForHackernews8• 1 year ago

AFAIK that is the standard behavior with a Postgres logical replication setup. There is nothing preventing you doing writes on the replica, they just won't get sent back anywhere else.

ethan_smith• 1 year ago

You can use Postgres logical replication with a filter to create a one-way replica, then just disable the replication slot when you want to make local changes without affecting the primary.

nico5• 1 year ago

then just disable the replication slot when you want to make local changes without affecting the primary

This would be all the time, ie. in my local dev, I’d like to have an up to date (daily is ok), copy of the source db that I can modify anytime, but that everyday will sync with the source, without having to fully rebuild indices

How would you go about managing/coordinating that?

eatonphil3• 1 year 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_ST...

[1] https://github.com/2ndquadrant/pglogical

[2] https://www.enterprisedb.com/docs/pgd/latest/

cbsmith• 1 year ago

PGDv6 is still closed source, yeah?

eatonphil3• 1 year ago

That's right.

x4m• 1 year ago

And there's also https://postgrespro.com/docs/enterprise/current/multimaster With a history behind it.

eatonphil3• 1 year 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• 1 year ago

Related. Others?

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

iotapi322• 1 year 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.

ahachete2• 1 year 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• 1 year 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?

ahachete2• 1 year 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.

mosselman3• 1 year 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• 1 year ago

Are you looking for a tool like Barman?

hans_castorp• 1 year ago

For failover handling repmgr is probably better (they can work together though)

mosselman3• 1 year ago

I don't know. Barman would only be part of the HA puzzle no?

Tostino• 1 year ago

I've been using cloudnativepg and it is pretty turn key for all of that.

nijave2• 1 year 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• 1 year ago

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

Pgactive: Postgres active-active replication extension

Related Stories