StormaticsStormatics

Upgrading PostgreSQL 9.6 to 17 with pg_upgrade

When you are upgrading across major PostgreSQL versions, there are a few ways to go. Dump and restore is the simplest to reason about, but downtime scales directly with database size, so for anything multi-terabyte, it is off the table. Logical replication gets you near-zero downtime, but it only works from PostgreSQL 10 onward; if your source cluster is on less than version 10, that path does not exist in a native way. That leaves pg_upgrade, the community-maintained tool for in-place major version upgrades. With the –link flag, it creates hard links instead of copying data files, so the upgrade step itself stays fast, no matter how big the database is.
Read More

Zero-Pain PostgreSQL DDL Migrations: Avoiding Locks and Long-running Queries in Production

Database migrations are a critical step in the lifecycle of any application, allowing teams to deploy new features, create and change database objects, and scale infrastructure. However, in high-volume mission-critical environments, executing Data Definition Language (DDL) statements can quickly turn into a production nightmare. The primary culprit behind application downtime during DDL deployments is the mismanagement of PostgreSQL's locking mechanism. A single poorly planned ALTER TABLE statement can request an exclusive lock, blocking incoming application queries, exhausting connection pools, causing cascading timeouts, and ultimately leading to revenue loss. To achieve true zero-downtime deployments, database migrations must be fast, defensive, and meticulously designed to avoid heavy locks and long-running queries.
Read More

PostgreSQL Disaster Recovery with pgBackRest TLS Transport

If you've read the pgBackRest DR guide on this blog, you already know the standard setup: two servers, passwordless SSH, pgBackRest pulling backups across the wire. It works reliably, and it's what most teams run. SSH works well for small deployments. The challenge emerges at scale: as the number of machines grows, managing individual key pairs, distributing them, rotating them, and auditing who has what becomes increasingly complex. SSH also supports host-based authentication, where host keys are used to authenticate connections in an Ident-like model, which simplifies certain setups. But, enforced key rotation across a large fleet remains genuinely difficult. In essence, TLS works with the X.509 public key infrastructure to manage and verify public keys. Rather than pre-sharing them, the key owner can provide them embedded in a certificate that includes more information about who the key belongs to, the validity period, and so forth. A certificate authority then signs the certificate. The receiver of the key only needs to know the certificate authority’s public key to verify it and then decide whether to trust it. As a result, rather than pre-sharing keys to validate authentication, this allows fewer keys to be shared initially, thereby improving management at scale. That's exactly the problem pgBackRest's TLS server mode solves, although this now adds a new layer of systems to manage in the certificate authorities.
Read More

Inside a PostgreSQL Checkpointer Bug: A Production Postmortem

One of our client’s PostgreSQL 16.8 production databases started logging what looked like a memory error: ERROR: invalid memory alloc request size The error immediately pointed toward two likely suspects: - Memory exhaustion - Memory corruption As it turned out, neither was the culprit. Instead, it had encountered a known PostgreSQL bug that trapped the checkpointer in an infinite retry loop. The only way to recover was a forced restart, followed by an extended period of WAL replay during crash recovery. This article explains what happened, why manual checkpoints couldn't fix it, and how a PostgreSQL minor version upgrade permanently resolved the issue. Understanding the purpose of a checkpoint When a transaction modifies data, PostgreSQL does not immediately write the changed page to disk. Instead, it follows a two-step process: Write the change to the Write-Ahead Log (WAL) - a sequential, append-only record of every modification. Keep the modified page in shared memory as a dirty buffer until it is written later. This design is intentional. WAL writes are sequential and therefore inexpensive, whereas writing data pages directly to their final location requires random disk I/O, which is much more costly. Decoupling these two operations is a fundamental part of PostgreSQL's I/O architecture. Eventually, however, the dirty buffers in memory must be synchronized with the actual data files on disk. That is the job of a checkpoint. During a checkpoint, the checkpointer: Flushes every dirty buffer from shared memory to its corresponding data file. Calls fsync() on those files to ensure the data has reached durable storage rather than remaining in the operating system's cache. Records the checkpoint location in the WAL once all writes have been safely persisted. This checkpoint record is critical for crash recovery. If PostgreSQL crashes, recovery only needs to replay WAL generated after the most recent completed checkpoint, because everything before that point has already been written safely to disk. Without checkpoints, PostgreSQL would have to replay the entire WAL history from the beginning, making recovery increasingly slow as WAL accumulates. To keep track of which files still require an fsync() before a checkpoint can finish, the checkpointer maintains an internal structure called the fsync request queue. Every data file modified during checkpoint processing is added to this queue. As each file is successfully fsynced, its entry is removed. Under normal conditions, the queue drains steadily until the checkpoint completes. The problem begins when it doesn't.
Read More

MCP For PostgreSQL: Automated Health Checks & Performance Analysis

AI agents are becoming increasingly capable at operational tasks: summarizing logs, analyzing query plans, identifying anomalies, and assisting with incident response. For databases in particular, this creates an obvious opportunity. Much of day-to-day troubleshooting follows repeatable workflows that lend themselves well to automation. As someone who spends most of my time working with PostgreSQL, I find the interesting question isn't whether an LLM can help analyze a slow database. It can. The harder question is how to do that safely. Production databases sit behind layers of controls, processes, and accountability. Access is granted carefully because mistakes are expensive. When an engineer investigates an incident, that trust comes from experience and clearly defined responsibilities. Extending those capabilities to an AI agent raises a different challenge: how do you give it enough access to be useful without giving it enough access to be dangerous? That problem is exactly what Model Context Protocol (MCP) attempts to address. Rather than exposing a database directly to an LLM, MCP introduces a layer of controlled capabilities. Instead of unrestricted access, the model receives a set of predefined tools with well-defined boundaries.
Read More

PostgreSQL Wait Events: A Production Diagnostic Guide

A query plan tells you what PostgreSQL intended to do. A wait event tells you what it actually did with its time. Most performance work I see in production starts one layer too high, in the EXPLAIN output, when the cheaper answer is already sitting in pg_stat_activity.This is a refresh of a guide I wrote in late 2024. Two things have changed since then. PostgreSQL 17 added pg_wait_events as a first-class catalog view. PostgreSQL 18 shipped asynchronous I/O and changed how I/O wait should be read in production. Both shifts deserve their own treatment, and I have folded them into this update along with five production cases from work my team did over the last two quarters.
Read More

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

Archives

Categories