StormaticsStormatics

How To Run Logical Replication On A Production PostgreSQL Cluster

Upgrading PostgreSQL in production is not fun. You have real data, real users, and a version of Postgres that's probably several major releases behind. The obvious approach (dump, upgrade, restore) works fine on a 10GB database on a Saturday night. It doesn't work when you have TBs of data, applications that can't afford hours of downtime, and a team watching the monitoring dashboard at 2am. Logical replication solves this. I’ve run it in production, and it works well, but there are a few practical considerations that are easier to appreciate once you’ve been through the process yourself. Why Logical Replication You keep your source cluster running (old Postgres version, production traffic, and all), bring up a destination cluster with the new version, and replicate all data changes while both run in parallel. When the destination has caught up, you cut over. Downtime is seconds to minutes, not hours. Source is your existing cluster (say, Postgres 14). Destination is your new cluster running Postgres 17. The thing most guides skim over is that logical replication does not copy schema, it only copies data. PostgreSQL replicates INSERT, UPDATE, and DELETE operations, but not your table definitions, indexes, roles, or anything structural. An example would be if logical replication is setup then truncate operation on a single source table would also be replicated, but table1, table2, and table3, etc. won't be replicated. You have to handle that yourself before replication starts. Miss this, and your subscription will either fail silently or throw confusing errors that don't point you back to the actual problem. Step 1: The Primary Key Check Logical replication requires every replicated table to have a replica identity to replicate UPDATE and DELETE operations. By default, that's the primary key. Tables without one will still replicate INSERTs, but any UPDATE or DELETE will error on the publisher. Replica Identity A published table must have a replica identity configured so that UPDATE and DELETE operations can be replicated, because the replica identity is what allows the correct row to be located on the subscriber. By default, the replica identity is the primary key. A suitable unique index can also be used, subject to certain requirements. If no suitable key exists, the replica identity can be set to FULL, which uses the entire row as the key. Replica identity FULL should be treated as a fallback to be used only when no other option is available, because without a suitable index on the subscriber, the row search can be very inefficient. If a table without a replica identity is added to a publication that replicates UPDATE or DELETE operations, those operations will fail on the publisher. INSERT operations proceed regardless of the replica identity.
Read More

When Patroni Silently Deletes Your Replication Slots

If you have ever been in the middle of a database migration and suddenly found your logical replication slots missing, you know how unsettling that feeling is. No obvious error. No warning. Just gone. That is exactly what happened to us while migrating data from a Patroni-managed cluster to a standalone PostgreSQL cluster. What started as a smooth operation turned into a debugging session that taught us something important about how Patroni behaves after a restart.The SetupWe were running a data migration where the source was a Patroni cluster and the target was a another PostgreSQL cluster. Logical replication was our chosen approach, which meant we had created replication slots on the Patroni side to track the changes flowing out. Things were going well at the start. Data was moving, lag was low, and we felt good about the plan.Something Went WrongAt some point, the publisher node on the Patroni side got restarted. After the restart, our logical replication slots were simply gone. This was strange. These were not temporary slots. Temporary slots are expected to disappear after a session ends, but permanent slots are supposed to survive restarts. PostgreSQL itself has no reason to drop them.
Read More

File Descriptors: The OS Limit That Takes Down PostgreSQL

Most PostgreSQL outages that trace back to file descriptor exhaustion get misread as a database problem. The failure is one layer down: the kernel runs out of file descriptors and PostgreSQL takes the hit. This post covers how that happens under high connection counts, how to read the log sequence when it does, and how to fix it. What are file descriptors and why PostgreSQL burns through them In Linux, the kernel represents almost everything as a file descriptor: TCP sockets, open table files, index files, WAL segments, temp files for sorts and joins, log files. Every open() or accept() call increments a counter. The kernel enforces a system-wide ceiling called fs.file-max. When total FD usage across all processes on the machine hits that ceiling, every new open() fails; regardless of which process is asking. There's also a second, separate limit called the per-process ceiling (RLIMIT_NOFILE, controlled by ulimit -n), which caps how many FDs a single process can hold. Either limit can produce the "out of file descriptors" log message or a single backend hitting its per-process ulimit. Both need to be checked during the diagnosis. PostgreSQL is process-based. Each client connection spawns its own OS process. Each backend holds FDs for its client socket, the table and index files it's accessing (managed through PostgreSQL's internal VFD system, capped by max_files_per_process, default 1,000), WAL segments, and any temp files. An idle backend holds 10–15 FDs. An active write backend touching multiple tables with indexes can hold 50–200 or more. The theoretical worst case is max_connections x max_files_per_process. In practice you won't hit that ceiling, but even a fraction of it is dangerous when thousands of connections are open at once.
Read More

The Night Our Tables Wouldn’t Stop Growing

We were doing everything right. The migration plan was solid, the team was experienced, and we had done this kind of thing before. But somewhere around midnight, someone on the team noticed something strange. Tables on the destination side were ballooning at an unexpected rate with hundreds of gigabytes being used, while the source side tables sat quietly at just a few megabytes. Something was very wrong, and we had no idea what.
Read More

Automating PostgreSQL Index Tuning Using AI

If you have a slow query, one of the obvious moves is to add an index. So you look at the WHERE clause, pick a column, run CREATE INDEX, and test again. Sometimes it helps, often it doesn't. And now you have an index sitting there, not helping reads, but slowing down every write, because INSERT, UPDATE, and DELETE all have to maintain it. And it gets worse as your system grows.Five queries are manageable. You can reason about column choices, test combinations, and check EXPLAIN output. When you are dealing with fifty queries across a dozen tables, you are evaluating hundreds of possible column combinations manually, each one potentially breaking something in production if you get the locking wrong.
Read More

Critical PMM Alerts Every PostgreSQL DBA Must Track

Have you ever left for home on a Friday evening feeling confident about your work for the day, at peace knowing your system would survive the coming weekend? We’ve all felt that way at some point. Meanwhile, the disk on the server had quietly reached 90% utilization. Write-Ahead Log (WAL) files had accumulated enormously, one long-running query had been running for over an hour, and nobody noticed because, some time earlier, the dashboard had looked fine.
Read More

Long-running transactions, job queues, and the cascade that wreaks havoc

A scheduled PostgreSQL migration step held an open transaction snapshot for hours during the initial data copy. A job queue running at production write velocity began to slow down. Twelve hours later, the queue was seven million rows deep, the primary was pinned at 100 percent CPU across 24 cores, and customer support was fielding complaints about delayed transactions.
Read More

Before You Move a Single Row, Plan Your Cutover

You have deployed your new cluster. Now comes the work of moving your data and cutting over to it. Reading that sentence, you might assume cutover is something you figure out at the end, after the migration is done. And in practice, that is the order in which things happen. But technically, it is your cutover strategy that decides how you migrate, not the other way around. The strategy you pick determines how you configure replication, how many slots you provision, how you handle schema changes, and what your rollback path looks like. So before you touch replication, decide how you want to cut over. In this post, I will walk through the two most common cutover strategies, what each one costs you, and what each one gives you back. Approach 1: Cut Over One Database at a Time Imagine you are moving an entire office to a new location. One way to do it is to move one team at a time, finance this week, engineering next week. Each team settles in before the next one arrives. If something goes wrong with finance’s move, it doesn’t affect engineering. You fix the problem, learn from it, and continue. That’s exactly how this approach works with databases. You pick one database, migrate it, test it, cut it over, confirm everything is fine, and then move to the next. Why This Approach Makes Sense 1. It’s Easier to Manage When you are watching one database go through a cutover, you know exactly where to look if something breaks. Your team isn’t juggling ten things at once. Attention is focused, and problems surface quickly. 2. Issues Show Up Early The first database you cut over is like a fire drill. You discover what your runbook missed, what monitoring didn’t catch, what your rollback steps actually look like in practice, all with limited impact. By the time you reach database number five, your team is smooth and confident. 3. Replication Slots Don’t Pile Up During logical replication, the publisher (your old cluster) has to keep a replication slot open for each active subscriber. These slots hold WAL data, so the subscriber doesn’t miss anything. If you keep many databases in sync at the same time, those slots pile up, resulting in more disk usage, more memory pressure, and more risk. When you cut over one database at a time, you drop the slot as soon as that cutover is done. The publisher breathes easier. 4. Rolling Back Is Simple If something goes wrong after cutting over a single database, you only need to revert one database. Your reverse replication path is short and clean. You are not trying to undo a dozen cutovers at once. 5. CPU Stays Calm Managing replication for many databases at the same time puts a real load on the publisher. WAL senders, replication workers, and the constant work of tracking changes across many slots can spike your CPU at the worst possible moment, right when you need the system to be stable. One database at a time keeps the load predictable. 6. Downtime Can Be Just Seconds A well-prepared cutover for a single database is fast. You pause writes, confirm the subscriber has caught up (lag is zero), update your connection strings, and resume. 7. Less Room for Human Error Cutover is a high-stress, time-sensitive operation. The fewer things your team has to do at once, the fewer mistakes happen. One database at a time means one checklist, one confirmation, one rollback plan, not ten of them running in parallel. 8. The Migration Can Be Done in Hours or a Few Days Each database is self-contained. You don’t have to wait for every database in the cluster to be ready before you can start cutting over. Even if a database is huge, the migration window is limited to just that one.
Read More

PostgreSQL’s Growing Role in AI Infrastructure

Around 80% of LLMs and AI-powered applications choose either PostgreSQL or Supabase when selecting a database. That level of adoption says something important about where engineering teams are placing their trust. Supabase has become one of the most common starting points for AI products. Most AI frameworks support PostgreSQL and pgvector directly. For many teams, PostgreSQL is already part of the stack before the AI layer is even introduced.
Read More

SSL in PostgreSQL

SSL stands for Secure Sockets Layer. Its successor is TLS (Transport Layer Security), but in the PostgreSQL world, and in most documentation, people still call it SSL out of old habit. Don’t let that confuse you. When someone says “SSL” in a Postgres context, they mean modern TLS-based encryption. Here’s the problem it solves. By default, when your application connects to PostgreSQL, everything travels across the network in plain text. Usernames. Passwords. Every query you run. Every row of data that comes back. If anyone can intercept that traffic, someone on the same network, a compromised internal service, they can read all of it. A basic packet sniffer is enough. No special skills needed.
Read More

Archives

Categories