StormaticsStormatics

The Right Way to Give a Third-Party DBA Access to Your PostgreSQL Database

Giving an external team access to your PostgreSQL database is one of those decisions that deserves a little thought. The easiest option is to hand over a superuser account, but it's rarely the right one. A better approach is to create a dedicated role with only the privileges they actually need, and it takes just a few minutes to set up. Over the years, I've been on both sides of this conversation. I've been the external DBA being onboarded onto a client's database, and I've been the internal engineer deciding what access to grant. The pattern I'm going to walk you through is the one I'd reach for in either situation: a purpose-built, non-superuser role that gives an outside team exactly what they need to do real work, and nothing they shouldn't have.
Read More

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

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

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

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

PostgreSQL is Not Slow. Your Queries Are.

A field guide to the seven things that are actually making our database feel slow and how to stop blaming the wrong suspect. It usually starts with a Slack message: "The app feels slow". This is normally followed by a ticket, then an internal meeting, and finally someone, and there is always someone, saying: "I think we need to switch databases. PostgreSQL can't handle this load."
Read More

Archives

Categories