Logical-Replication

Right, so your primary server has decided to take an unscheduled vacation. It’s crashed, it’s gone, it’s pining for the fjords. The show must go on, and that means you need to promote one of your standby servers to take its place. This isn’t just a configuration change; it’s a state change. You’re telling a replica, “Stop following orders and start giving them.” It’s a big moment, and doing it correctly is the difference between a smooth transition and a full-blown, why-is-the-database-down-on-a-Tuesday disaster.

Right, let’s talk about pg_basebackup. This is the workhorse, the trusty mule of PostgreSQL physical backups. It’s not glamorous, but when the proverbial fan gets clogged, this is the tool you’ll be desperately glad you set up correctly. In essence, pg_basebackup does one thing and does it well: it connects to a running PostgreSQL server and pulls a complete, bit-for-bit copy of the entire data directory (and optionally, the WAL archive) to create a perfect physical base backup. This is the literal foundation of any Point-in-Time Recovery (PITR) strategy. You can’t do PITR without one of these.

Right, let’s talk about replication slots. You’re probably here because you’ve seen the dreaded WARNING: oldest xmin is far in the past or, worse, a standby has fallen off the wagon because it couldn’t get the WAL files it needed. Replication slots are the solution to that second, more insidious problem. They’re a way to tell the primary server, “Hey, don’t you dare delete that WAL file until you are absolutely, positively sure my standby has consumed it.”

Right, so you’ve outgrown streaming replication. You need to replicate only a subset of your data, or maybe you’re doing a major version upgrade without downtime. Welcome to logical replication, the grown-up version of “just copy the whole data directory.” Instead of blindly shipping every bit and byte, it streams a log of the actual data-changing operations (INSERT, UPDATE, DELETE) from one database to another. It’s smarter, more flexible, and consequently, a bit more hands-on.

Right, let’s settle the great debate: should your replica be a dutiful, “Yes, sir, right away, sir!” subordinate, or more of a “I’ll get to it when I get to it” kind of background process? This is the core of synchronous versus asynchronous replication, and the choice is far more profound than a simple checkbox. It’s a trade-off between absolute data safety and raw performance, and getting it wrong can lead to some spectacularly unpleasant outcomes.

Right, let’s get your standby server listening to the primary. This isn’t just about copying files; it’s about creating a hotline between the two. The secret handshake for this connection is defined in two places: the primary_conninfo string on the standby and the recovery.conf (or postgresql.conf in v12+) file that tells a server “hey, you’re not the main character in this story.” First, a moment of silence for recovery.conf. In PostgreSQL 12, the designers, in their infinite wisdom, decided to merge its parameters into postgresql.conf. It’s a cleaner approach long-term, but it means you need to know which version you’re on. I’ll show you both, because I’m a brilliant friend and that’s what we do.

Right, let’s talk about the beating heart of any serious PostgreSQL setup: replication. Forget the marketing fluff; this is how you turn a single, lonely database server into a resilient, scalable system. And it all starts with the Write-Ahead Log, or WAL. If your database is a novel, the WAL is the continuous, unbroken stream of every single edit the author ever made. We don’t just copy the final book; we stream the entire writing process. This is the fundamental concept behind WAL-based replication, and it’s brilliantly simple and robust.