StormaticsStormatics

Disaster Recovery Guide with pgbackrest

Recently, we worked with a client who was manually backing up their 800GB PostgreSQL database using pg_dump, which was growing rapidly and had backups stored on the same server as the database itself. This setup had several critical issues:- Single point of failure: If the server failed, both the database and its backups would be lost. - No point-in-time recovery: Accidental data deletion couldn’t be undone. - Performance bottlenecks: Backups consumed local storage, impacting database performance.To address these risks, we replaced their setup with pgBackRest, shifting backups to a dedicated backup server with automated retention policies and support for point-in-time recovery (PITR).This guide will walk you through installing, configuring, and testing pgBackRest in a real-world scenario where backups will be configured on a dedicated backup server, separate from the data node itself.
Read More

From 99.9% to 99.99%: Building PostgreSQL Resilience into Your Product Architecture

Most teams building production applications understand that “uptime” matters. I am writing this blog to demonstrate how much difference an extra 0.09% makes.At 99.9% availability, your system can be down for over 43 minutes every month. At 99.99%, that window drops to just over 4 minutes. If your product is critical to business operations, customer workflows, or revenue generation, those 39 extra minutes of downtime each month can be the difference between trust and churn.
Read More

Choosing the Right Barman Backup Type and Mode for Your PostgreSQL Highly Available Cluster

When running a PostgreSQL database in a High Availability (HA) cluster, it’s easy to assume that having multiple nodes means your data is safe. But HA is not a replacement for backups. If someone accidentally deletes important data or runs a wrong update query, that change will quickly spread to all nodes in the cluster. Without proper safeguards, that data is gone everywhere. In these cases, only a backup can help you restore what was lost.The case mentioned above isn’t the only reason backups are important. In fact, many industries have strict compliance requirements that make regular backups mandatory. This makes backups essential not just for recovering lost data, but also for meeting regulatory standards.Barman is a popular tool in the PostgreSQL ecosystem for managing backups, especially in High Availability (HA) environments. It's known for being easy to set up and for offering multiple types and modes of backups. However, this flexibility can also be a bit overwhelming at first. That’s why I’m writing this blog to break down each backup option in a simple and clear way, so you can choose the one that best fits your business needs.
Read More

Which PostgreSQL HA Solution Fits Your Needs: Pgpool or Patroni?

When designing a highly available PostgreSQL cluster, two popular tools often come into the conversation: Pgpool-II and Patroni. Both are widely used in production environments, offer solid performance, and aim to improve resilience and reduce downtime; however, they take different approaches to achieving this goal.We often get questions during webinars/talks and customer calls about which tool is better suited for production deployments. So, we decided to put together this blog to help you understand the differences and guide you in choosing the right solution based on your specific use case.Before we dive into comparing these two great tools for achieving high availability, let's first take a quick look at some of the key components involved in building a highly available and resilient setup.
Read More

Understanding Split-Brain Scenarios in Highly Available PostgreSQL Clusters

High Availability (HA) refers to a system design approach that ensures a service remains accessible even in the event of hardware or software failures. In PostgreSQL, HA is typically implemented through replication, failover mechanisms, and clustering solutions to minimize downtime and ensure data consistency. Hence, HA is very important for your mission-critical applications. In this blog post, we will try to explore a critical failure condition known as a split-brain scenario that can occur in PostgreSQL HA clusters. We will first see what split-brain means, and then how it can impact PostgreSQL clusters, and finally discuss how to prevent it through architectural choices and tools available in the PostgreSQL ecosystem
Read More

How to Upgrade Major PostgreSQL Versions: A Practical Production Guide

PostgreSQL versions follow a well-defined five-year support lifecycle. Each major release receives bug fixes, security patches, and minor updates for five years from its initial release date. After that point, the version reaches end-of-life (EOL) and no longer receives official updates.Staying on an EOL version exposes your systems to security risks, potential compatibility issues, and missing performance improvements introduced in later releases. You can always check the current support status of PostgreSQL versions on the official PostgreSQL Versioning Policy page.Upgrading to the latest version ensures long-term stability, access to new features, and better support. Recently, I worked on upgrading a critical production PostgreSQL environment from version 11 to 15. Version 15 was chosen because the client’s application had only been tested up to that release. The system supported large batch workloads and live applications, so we had to be meticulous. While this article draws from that specific project, the steps are broadly applicable to anyone planning a major PostgreSQL upgrade, especially when crossing several versions.This guide outlines a generalized, production-ready approach for performing major version upgrades using the pg_dump/pg_restore method.
Read More

Step by Step Guide on Setting Up Physical Streaming Replication in PostgreSQL

Physical streaming replication in PostgreSQL allows you to maintain a live copy of your database on a standby server, which continuously receives updates from the primary server’s WAL (Write-Ahead Log). This standby (or hot standby) can handle read-only queries and be quickly promoted to primary in case of failover, providing high availability and disaster recovery.In this guide, I will walk through provisioning a primary PostgreSQL 16 server and a standby server on Linux, configuring them for streaming replication, and verifying that everything works. I assume you are an experienced engineer familiar with Linux, but new to PostgreSQL replication, so I will keep it friendly and straightforward.Figure: Real-time data streaming from a primary PostgreSQL server (left) to a standby server (right). The standby constantly applies WAL records received from the primary over a network connection, keeping an up-to-date copy of the database ready for failover.Step 1: Prepare Two Linux Servers and Install PostgreSQL 16 Before diving into PostgreSQL settings, set up two Linux servers (virtual or physical). One will act as the primary database server, and the other as the standby (read replica). For a smooth replication setup, both servers should be as similar as possible in OS, hardware, and PostgreSQL version. In particular, ensure the following prerequisites:PostgreSQL 16 is installed on both servers via the official PostgreSQL repositories. Both servers must run the same major PostgreSQL version and architecture (mixing different versions won’t work for physical replication). If you haven’t installed PostgreSQL yet, do so now (e.g., on Ubuntu: sudo apt install postgresql-16, or on RHEL/CentOS: use the PostgreSQL Yum repository). Make sure the PostgreSQL service is running on the primary server.Network connectivity: The standby must be able to reach the primary on the PostgreSQL port (default 5432). If the servers are in a cloud environment like AWS EC2, configure the security group or firewall to allow the standby’s IP to connect to the primary on port 5432. For example, in AWS you’d add an inbound rule permitting the standby’s private IP address (or subnet) access to port 5432 on the primary. It is best to use private network interfaces for replication to reduce latency and avoid exposing the database publicly.System settings: Ensure your servers have the necessary OS user and permissions for PostgreSQL. The installation usually creates a postgres UNIX user that owns the data directories. You will run many commands as this postgres user. Also, verify that important prerequisites like consistent time sync (NTP) are in place, as it is generally good practice for database servers (though not specific to replication).With the infrastructure ready, let’s proceed to configure the primary PostgreSQL server to accept replication connections.
Read More

A Guide to Restoring a PostgreSQL Database from Disaster Using Azure Flexible Server

Backups are crucial for any mission-critical application as they protect against unforeseen disasters. Regular backups help minimize the Recovery Point Objective (RPO), allowing systems to recover quickly with minimal data loss. However, it's equally important to store backups safely. If backups are kept in the same location as the primary site and something goes wrong, you may have no way to recover, leading to complete data loss.To reduce these risks, many organizations choose fully managed servers to host their databases. One popular option is Azure Flexible Server for PostgreSQL, which offers a reliable, scalable, and managed solution. Azure provides 3 levels of redundancy in three different ways, and not only that, you can recover backups using these same three methods. These areLocally Redundant Storage Zone Redundant Storage Geo RedundantEach level of redundancy offers unique advantages when it comes to restoring backups. In today's blog, we will explore all three types of backups and recovery methods. We will dive into the differences between each type and learn how to restore your backup if your primary site goes down.
Read More

3 Essential PostgreSQL Priorities for 2025

As IT budgets tighten and workloads increase, 2025 is the year to focus on maximizing PostgreSQL efficiency, security, and reliability. Whether you are running fully-managed or self-managed PostgreSQL databases, these three priorities- Reducing cloud costs - Increasing data security, and - Enhancing availabilitywill be key to staying competitive.Here is a deep dive into each priority and actionable steps to make them a reality.1. Reduce Cloud Costs Without Compromising PerformanceCloud costs can escalate quickly when PostgreSQL instances are not optimized for the workload. Here is how to implement cost-saving measures with technical precision:Instance Sizing and ScalingAnalyze Workload Patterns: Use tools like pg_stat_activity and pg_stat_user_tables to identify peak usage and idle times. Leverage this data to choose the right instance type and size.Autoscaling with Load Balancers: Deploy PostgreSQL in a cloud environment using managed services that support autoscaling or set up custom scaling policies.Storage and Index OptimizationPartitioning: Use table partitioning to manage large datasets efficiently and reduce query processing times. For instance, partition large logs by time, and ensure that queries use partition pruning.Index Tuning: Remove redundant indexes using pg_stat_user_indexes and optimize index types (e.g., switching from B-Tree to GiST or GIN indexes for specific queries). This reduces storage requirements and speeds up query performance.Query OptimizationEXPLAIN and ANALYZE: Run slow queries through EXPLAIN to pinpoint inefficiencies. Common culprits include sequential scans on large tables and ineffcient join strategies with large datasets.Caching Frequently Accessed Data: Use tools like pgpool-II to enable query result caching and connection pooling, minimizing redundant query execution.These optimizations not only reduce costs but also improve overall database responsiveness.
Read More

Implementing Bi-Directional Replication in PostgreSQL

In today's fast-paced digital world, ensuring that your data is always up-to-date and accessible is crucial. For businesses using PostgreSQL, replication is a key feature that helps achieve this. While many are familiar with streaming replication, bi-directional replication offers unique advantages that can enhance data availability and reliability. In this blog post, we'll explore what bi-directional replication is, how it differs from streaming replication, and provide a practical example to setup bi directional replication in PostgreSQL
Read More

Archives

Categories