Dimitri Fontaine: Postgres logical replication beyond one writer

In 2014, if you wanted Postgres servers on several continents, each of them accepting writes and all of them sharing their rows, there was one serious open source answer: BDR, from 2ndQuadrant. It needed a patched PostgreSQL 9.4 and a plugin, which means it needed a server that was not Postgres. Ten years of work on logical replication have since moved most of what BDR needed into the stock server, and in this article we build the same kind of system with Postgres 19 release candidate 1 and pg_auto_failover, to see how far that incremental progress has taken us, and what is still left to build.

This is the last article of a series on logical replication, and it should read fine on its own. In Part 1 we built a hub-and-workers system for write scalability, in Part 2 we consolidated several databases into one warehouse, and in Part 3 we did a zero-downtime major upgrade.

TL;DR We built five regions out of pg_auto_failover groups and core logical replication, and tested them by killing primaries under load. Every acknowledged row survived, every region ended with the same rows, and a fifth region joined the running system with five statements and no pause. Core now does the hard parts: row filters, loop prevention, failover-safe slots, sequence synchronization, and it counts conflicts. It still doesn’t replicate DDL, so schema changes are a short pause, or pglogical or pgcopydb when a pause isn’t an option. It doesn’t resolve conflicts or hand out unique ids to several writers either, and we avoid both by construction.