Postgres logical replication beyond one writer
In 2014, if you wanted Postgres servers on several continents, each of them
以下正文同步自 Hacker News 最新,版权归原站所有,已转换为易读排版。
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.
Take a company with customers on four continents and a head office that wants to see all of it. Each region’s data is written close to its users and belongs to that region. A region survives the loss of its primary without the others noticing. The head office has a warehouse that sees everything. A fifth region can be added next year without taking anything down. That’s the system BDR was designed for in 2014, and this is the shape we give it:
Four regions own data: us-east in New York, us-west in San Francisco, ap-south in Mumbai and ap-southeast in Sydney. A fifth site, a warehouse in Amsterdam, owns nothing and holds a copy of everything. Each site is a pg_auto_failover group of its own, a primary and two standbys, and the groups share no failover. They only talk through logical replication, in both directions: a region’s own rows flow one way, and a read-only mirror of everybody else’s rows flows back.
That’s five problems to solve, and we take them in this order:
Core Postgres replicates the rows. pg_auto_failover runs the failover of each group and keeps the settings of the replication slots right while it does. Core doesn’t replicate DDL, and that’s the one place where we look at other tools, pglogical and pgcopydb. Here is what core learned since BDR, for the parts of the system that use it:
What’s missing from the table is what Postgres still doesn’t do: replicate DDL, resolve a conflict, and give unique sequence values to several writers. We get back to each of them.
When the primary of a region fails, a logical consumer is attached to it, and we want it to get every row exactly once, or at least not to miss one. In our system that consumer is another region’s subscription.
Postgres 17 added failover = true for a replication slot, and the synchronized_standby_slots setting. What they promise is this: a logical walsender on a failover slot won’t send a client any WAL past what every standby listed in synchronized_standby_slots has already flushed. So when the primary dies and one of those standbys is promoted, nothing the consumer has received can be missing from the new primary. What can happen is duplicates, because the position the consumer confirmed may be behind what it processed. Never a hole.
That promise comes with conditions, and each one fails silently when you miss it:
A region doesn’t have to run on pg_auto_failover. Patroni is the other widely used way to run a group, and the high availability of a cloud provider’s managed service is a third. We use pg_auto_failover because we know it well, and we could contribute failover slots support to it while preparing this article. As far as the documentation says, Patroni keeps its own permanent logical slots, which it creates on the leader and copies to the standbys, advancing their positions periodically: a mechanism older than the failover flag of Postgres 17. For RDS for PostgreSQL 17 and later, AWS documents slot synchronization with failover = true, synchronized_standby_slots and a database name in rds.logical_slot_sync_dbname, and Azure documents that slots of a flexible server survive a failover with sync_replication_slots and hot_standby_feedback. On Google Cloud SQL, it is documented for PostgreSQL 17 and later on the Enterprise Plus edition, through its cross-region disaster recovery replica, with failover = true on the subscription and the replica as a synchronized standby; the pages about its regular high availability instances don’t say what happens to a logical slot there. We haven’t tested any of them. There are many more providers than we could read, from Scaleway and OVHcloud to the large ones in the US, China, Russia or India, and each has its own answer, so check the documentation of what you run before relying on it.
In a pg_auto_failover group, listing the standbys in synchronized_standby_slots (the second condition above) is the monitor’s job. It computes the list the same way it computes synchronous_standby_names, as the physical slot of every failover candidate, and pg_autoctl applies it on the primary and updates it when the candidates change:
$ pg_autoctl show state Name | Node | Host:Port | TLI: LSN | Connection | Reported State | Assigned State ----------+-------+----------------+----------------+--------------+---------------------+-------------------- us-east-a | 1 | us-east-a:5432 | 1: 0/5002AD8 | read-write | primary | primary us-east-c | 2 | us-east-c:5432 | 1: 0/5002AD8 | read-only | secondary | secondary us-east-b | 3 | us-east-b:5432 | 1: 0/5002AD8 | read-only | secondary | secondary
$ pg_autoctl show synchronized-standby-slots 'pgautofailover_standby_2,pgautofailover_standby_3'
It doesn’t create the logical slot though, that’s for the consumer to do, and there are three things the consumer must do on its side:
The check is one replication command, which a consumer can send on the connection it is about to stream from. Here it is on the new primary, after us-east-a was killed and us-east-c promoted:
$ psql "dbname=app replication=database" -Atc "IDENTIFY_SYSTEM" 7703946002234535959|2|0/5049AD8|app
The columns are the system identifier, the timeline (2: the history forked once), the position of the server’s WAL (xlogpos) and the database. The consumer compares them with what it stored, and refuses to stream if its stored position is past xlogpos, or past the point where the history it read from was left behind: a consumer ahead of the history it reads from is the one case the slot can’t protect. For the second half of that rule, the operator’s view of the same history is pg_autoctl show timeline, which lists the timelines with the position where each one was forked from its parent (the switchpoint), and each node’s position against them:
$ pg_autoctl show timeline TLI | Parent TLI | Switchpoint LSN ---------+------------+---------------- 1 | 0 | 0/0 2 | 1 | 0/50497C0
Name | NodeId | TLI | LSN | Status ---------------------+--------+------+-------------+----------------------------------------- us-east-a | 1 | 1 | 0/50021D0 | ok, on accepted lineage us-east-c | 2 | 2 | 0/5049AD8 | ok, on accepted lineage us-east-b | 3 | 2 | 0/5049AD8 | ok, on accepted lineage
Timeline 2 forked from timeline 1 at 0/50497C0. A consumer that had received anything on timeline 1 past that position holds rows the new primary doesn’t have.
We tried it: a region is a monitor and three nodes on Postgres 17, and a pg_recvlogical consumer follows a failover slot while a writer inserts about 25 rows a second, one per transaction. Each scenario ran three times, and the table gives the range. “Holes” are rows the new primary has that the consumer never received, and “ahead” are rows the consumer received that the new primary doesn’t have.
The slot does what it says. The consumer is never ahead when the setting is right, even when we cut the standbys off from the primary’s WAL before the kill: it just receives nothing from then on, because the walsender waits for them. When we empty the setting, the consumer ends up with 69 to 92 rows the new primary never had, and the new primary then writes at WAL positions the consumer already passed, so it never sees 3 to 13 rows. That’s exactly the case the IDENTIFY_SYSTEM check is for, and nothing in Postgres raises it for you.
After the kill, pg_autoctl has promoted us-east-c, moved to timeline 2, and shrunk the list to the one standby left:
$ pg_autoctl show synchronized-standby-slots 'pgautofailover_standby_3'
The slot isn’t what costs time: a killed primary blocks the writer for about 51 seconds, roughly ten for the monitor to declare the node unhealthy and the rest for pg_auto_failover’s drain timeout. Those are defaults you can tune, measured here on a local Docker network. They lean on the conservative side, because they’re chosen to protect against network partitions and split brain rather than to fail over as fast as possible; the Failover and Fault Tolerance page of the pg_auto_failover documentation details each timeout and what it guards against, so read it before shortening any of them.
We link sites with replication, not with consensus, so we avoid conflicts instead of resolving them: the region is part of the key of every table.
create table events ( region text not null, event_id bigint not null, payload text, written_at timestamptz not null default clock_timestamp(), primary key (region, event_id) );
No row can be written by two regions, because its key says who owns it. The application picks the value of region and that’s the only place where the rule is enforced. Replication needs no rule at all, and each region can use its own sequence for event_id: with a (region, id) key, we don’t need a global sequence. It costs a wider key, and that’s all.
Each link is a publication with a row filter, one of the Postgres 15 features. The regions form a chain, and each hop carries only the rows the other side doesn’t have:
New York publishes two views of its table, one for Amsterdam and one for San Francisco, and San Francisco only publishes its own rows back:
-- on us-east create publication pub_to_eu_west for table events where (region = 'us-east' or region = 'us-west'); create publication pub_to_us_west for table events where (region <> 'us-west');
-- on us-west create publication pub_to_us_east for table events where (region = 'us-west');
-- on us-west again, the subscription to us-east create subscription sub_us_west_from_us_east connection 'host=us-east-a,us-east-b,us-east-c port=5432 dbname=app user=postgres target_session_attrs=read-write' publication pub_to_us_west with (failover = true);
Two details in that subscription do the work of the previous section. failover = true makes the slot it creates on the publisher a failover slot, so the standbys keep a copy of it. And the connection string lists the three nodes of the group with target_session_attrs=read-write, so the subscription finds the primary wherever it is after a failover. The subscription that a group runs itself needs nothing: it’s in the catalogs, which physical replication ships to the standbys, and an apply worker only starts on a node that is a primary.
Why not origin = no
正文由 探How 从来源站点 RSS 同步,内容未经改写;遇到排版缺失或需要图片、视频时请以原文为准。