Personal blog of Yzmir Ramirez

MongoDB high availability is essential to ensure reliability, customer satisfaction, and business resilience in an increasingly interconnected and always-on digital environment. Ensuring high availability for database systems introduces complexity, as databases are stateful applications. Adding a new operational node to a cluster can take hours or even days, depending on the dataset size. This is […]

Group replication is a fault-tolerant/highly available replication topology that ensures if the primary node goes down, one of the other candidates or secondary members takes over so write and read operations can continue without any interruptions. However, there are some scenarios where, due to outages, network partitions, or database crashes, the group membership could be broken, or we end […]

In most database systems, like MySQL, PostgreSQL, and MongoDB, replication of some kind is used to create a highly available architecture. Valkey/Redis is no different in this regard. Replication is native functionality to Valkey, supporting multiple replicas, and even chains of replication.To clear up any confusion, understand that Valkey replication is a different concept compared […]

Ensuring your monitoring system stays up and running is vital. High availability (HA) minimizes downtime for Percona Monitoring and Management (PMM) during hardware failures, in times of disaster recovery, or increased usage of the tool. It’s not just about extra storage, RAM, or CPU but rather having redundant systems ready to take over seamlessly, like […]

Percona Monitoring and Management (PMM) has become a valuable tool for database professionals, providing comprehensive insights into database health and performance. A recent update (version 2.41.0) introduced a significant enhancement: the ability to run PMM in high availability (HA) mode. This feature, currently in technical preview, offers exciting possibilities for ensuring the reliability and robustness […]

In my previous article, Comparisons of Proxies for MySQL, I showed how MySQL Router was the lesser performing Proxy in the comparison. From that time to now, we had several MySQL releases and, of course, also some new MySQL Router ones.Most importantly, we also had MySQL Router going back to being a level 7 proxy […]

Percona Monitoring and Management (PMM) is a state-of-the-art piece of software that exists in part thanks to great open source projects like VictoriaMetrics, PostgreSQL, and ClickHouse. The integration of those projects, plus the years of Percona expertise in the database space, makes PMM one of the best database monitoring solutions on the market.

Being software composed of different, multiple technologies can add complexity to a well-known concept: High Availability (HA). Achieving HA for PMM, as a whole, has proved to be a challenge but not an impossible task.

The easy part

Setting up the PMM cluster is the easy part. All it needs is a reverse proxy in front of a couple or more PMM instances. The go-to proxy is HAProxy configured for active/passive topology, that is, without load distribution.

For the purpose of the PoC, a single HAProxy instance is used (running as a docker container). The configuration file looks like this:

global
stats socket /var/run/api.sock user haproxy group haproxy mode 660 level admin expose-fd listeners
log stdout format raw local0 info

defaults
mode http
timeout client 10s
timeout connect 5s
timeout server 10s
timeout http-request 10s
log global

frontend stats
bind *:8404
stats enable
stats uri /
stats refresh 10s

frontend pmmfrontend
bind :80
default_backend pmmservers

backend pmmservers
option tcp-check
server pmm1 172.31.12.174:80 check port 80
server pmm2 172.31.11.132:80 check port 80 backup

The Docker container is run with this command:

docker run -d --name haproxy -v $(pwd):/usr/local/etc/haproxy:ro -p 80:80 -p 443:443 -p 8404:8404 haproxytech/haproxy-alpine:2.

The -v for the volume guarantees that the local copy of the haproxy.cfg file is the one used inside the container. Whenever you make a change in the cfg file, for the haproxy container to use it, just execute:

docker kill -s HUP haproxy

And to follow the haproxy logs:

docker logs -f haproxy

We have two frontends: One for the HAProxy stats and another for the PMM itself. The backend is a single one where the “passive” PMM instance (the one that is a pure “read replica”) is marked as “backup” so that traffic is only routed there in case the primary fails the health check.

For simplicity, the PMM instances are configured to listen to the 80 port (http) on the private IPs. This is made to avoid SSL certificates since everything goes through the same VPC (everything runs on ec2 instances on AWS). The health check, then, can be a simple “tcp-check” against port 80.

As you can see, stats are available via the port 8404. With this, the easy part is done.

For this example, the PMM SERVER endpoint will be where the HAProxy frontend is listening, and that’s the one used when registering a new PMM CLIENT.

And you can access PMM always using the same endpoint.

The not-so-easy part (made easy)

The proxy is configured to be aware of two different PMM instances — pmm1 and pmm2  — (using the private IPs 172.31.12.174 and 172.31.11.132 in this case), but we haven’t mentioned anything about those PMM instances.

And here is the not-so-easy part: One has to deploy at least two PMM instances on at least two different servers AND set up replicas. How to do it? This is the actual Proof of Concept: Enter the PMM HA script: https://github.com/nethalo/pmmha

The script will take care of installing PMM (if you already have it, you can skip this step), preparing the Primary, and setting up the Secondary. Simple as that. PMM will remain to be a black box.

The requirements are:

Steps to run the script:

git clone https://github.com/nethalo/pmmha.git
cd pmmha
bash pmm.sh

Failover

The failover will be handled by the HAProxy automatically when it detects that the current primary is no longer available. Traffic will be routed to the backup server from the backend, which, if properly set as a replica, will already have the historical data for metrics and QAN, and also the inventory will be ready to continue the data scrapping from the exporters.

Your feedback is key

We want to hear from you! Suggestions, feature requests, and comments in general are welcome, either in the comment section or via the GitHub repository.

Percona Monitoring and Management is a best-of-breed open source database monitoring solution. It helps you reduce complexity, optimize performance, and improve the security of your business-critical database environments, no matter where they are located or deployed.

Download Percona Monitoring and Management Today

Estimates vary, but most reports put the average cost of unplanned database downtime at approximately $300,000 to $500,000 per hour, or $5,000 to $8,000 per minute. With so much at stake, database high availability and fault tolerance have become must-have items, but many companies just aren’t certain which one they must have.

This blog article will examine shared attributes of high availability (HA) and fault tolerance (FT). We’ll also look at the differences, as it’s important to know what architecture(s) will help you best meet your unique requirements for maximizing data assets and achieving continuous uptime.

We’ll wrap it up by suggesting high availability open source solutions, and we’ll introduce you to support options for ensuring continuous high performance from your systems.

What does high availability mean?

High availability refers to the continuous operation of a database system with little to no interruption to end users in the event of system failures, power outages, or other disruptions. A basic high availability database system provides failover (preferably automatic) from a primary database node to redundant nodes within a cluster.

High availability does not guarantee 100% uptime, but an HA system enables you to minimize downtime to the point you’re almost there. Within IT, the gold standard for high availability is 99.999%, or “five-nines” of availability, but the level of HA needed really depends on how much system downtime you can bear. Streaming services, for example, run mission-critical systems in which excessive downtime could result in significant financial and reputational losses for the business. But many organizations can tolerate a few minutes of downtime without negatively affecting their end users.

The following table shows the amount of downtime for each level of availability.

Implementing high availability systems: How does it work?

High availability works through a combination of key elements. Some of the most important elements include:

• No single point of failure (SPOF): You must eliminate any SPOF in the database environment, including any potential for an SPOF in physical or virtual hardware.
• Redundancy: Critical components of a database are duplicated so that if one component fails, the functionality continues by using a redundant component. For example, in a server cluster, multiple servers are used to host the same application so that if one server fails, the application can continue to run on the other servers.
• Load balancing: Traffic is distributed across multiple servers to prevent any one component from becoming overloaded. Load balancers can detect when a component is not responding and put traffic redirection in motion.
• Failure detection: Monitoring mechanisms detect failures or issues that could lead to failures. Alerts report failures or issues so that they are addressed immediately.
• Failover: This involves automatically switching to a redundant component when the primary component fails. If a primary server fails, a backup server can take over.

High availability architecture

Two previously mentioned absolutes — no SPOF and foolproof failover — must apply across the following areas if an HA architecture is to be achieved:

• Infrastructure — This is the hardware that database systems rely on. Without enough infrastructure (physical or virtualized servers, networking, etc.), there cannot be high availability.
• Topology management — This is the software management related specifically to the database and managing its ability to stay consistent in the event of a failure.
• Connection management — This is the software management related specifically to the networking and connectivity aspect of the database. Clustering solutions typically bundle with a connection manager. However, in asynchronous clusters, deploying a connection manager is mandatory for high availability.
• Backup and continuous archiving — This is of extreme importance if any replication delay happens and the replica node isn’t able to work at the primary’s pace. The backed-up and archived files can also be used for point-in-time recovery.

What is fault tolerance?

Fault tolerance refers to the ability of a database system to continue functioning in full, with no downtime, amid hardware or software failures. When a failure event occurs — such as a server failure, power outage, or network disruption — a fault-tolerant system will ensure that data integrity is preserved and that the system remains operational.

Implementing fault-tolerant systems: How does it work?

Here are some of the key components and characteristics of a fault-tolerant database environment:

• Replication: Data is replicated across multiple nodes or servers, so if one node fails, the data remains accessible from replicas. Replication can be synchronous (ensuring immediate consistency) or asynchronous (allowing some delay).
• Redundancy: The system stores multiple copies of data on separate devices. Redundancy provides backups and safeguards against data loss in case of hardware failures.
• Error detection and correction: Techniques such as checksums, parity bits, and error-correcting codes are used to detect and correct errors that might occur during data transmission or storage.
• Data integrity checks: Various mechanisms, such as checksums or hashing algorithms, are used to verify the integrity of stored data. This ensures that data remains consistent and accurate even in the presence of errors.
• Specialized hardware: Specialized hardware is used to detect a hardware fault and initiate an immediate switch to a redundant hardware component.

Architecture for fault-tolerant systems

Fault-tolerant information systems are designed to offer 100% availability. Some of the key elements of such designs include:

• Backup hardware systems — The hardware system is backed up by systems that are the same or perform the same functions but are at separate data centers and are not dependent on any of the same physical sources of power and functionality.
• Backup software systems — As with hardware, there is no loss of software functionality because there are mirrored copies performing the same functions but residing elsewhere and using a different source of power.
• Containerization platforms — Today, companies are increasingly using containerization platforms such as Kubernetes to run multiple instances of the same software. By doing so, if an error or other problem forces the software to go offline, traffic can be routed to other instances, and application functionality continues. 
• Alternate power sources — To withstand power outages, businesses have alternate sources on standby. Some businesses, for example, have powerful generators ready to roll if electricity is lost.
• Alternate environments — Fault-tolerant information systems can include separate cloud and on-premises databases that perform the same functions. For companies without their own physical servers, replicated cloud databases are maintained in different regions to provide contingencies for power outages.

High availability vs. Fault tolerance: Understanding the differences

There’s obviously a lot in common in terms of setup, functionality, and purpose. So with all the similarities (replication, load balancing, redundant components, and more), what are the differences? 

In general terms, the purpose of a high availability solution is to minimize downtime and provide continuous access to the database, while the purpose of fault tolerance is to maintain system functionality and data integrity at all times, including during failure events or faults. 

Still, generally speaking, but in financial terms, achieving fault tolerance is more costly, and the payoff often does not justify the expense. For example, it takes a lot of time, money, and expertise to completely mirror a system so that if one fails, the other takes over without any downtime. It can be considerably cheaper to establish a high availability database system in which there’s not total redundancy, but there is load balancing that results in minimal downtime (mere minutes a year).   

Basically, it comes down to a company’s needs, what’s at stake, and what fits its budget. Here are key questions to consider when deciding between high availability and fault tolerance:

How much downtime can your company endure? 

With high availability, you can achieve the gold standard previously mentioned — and your database system will be available 99.999% of the time. 

With a fault-tolerant system, you can spend a lot more and perhaps do better than the 5.26 minutes of downtime (in an entire year) that come with the “five nines” described immediately above. But is it mission-critical to do better than 99.999% availability?

How much complexity and how many redundant components are you willing to take on? 

High availability systems typically have redundant servers or clusters to ensure that if one component fails, another can take over seamlessly. 

Fault tolerance incorporates multiple versions of hardware and software, and it also includes power supply backups. The hardware and software can detect failures and instantly switch to redundant components, but they also constitute more complexity, parts, and cost.

Which option fits your budget?

A high availability database system requires redundancy, load balancing, and failover. It also must ensure that there is no single point of failure. But depending on your needs, there are varying levels of high availability, and an architecture can be fairly simple. 

Fault-tolerant systems are designed with more complex architectures, requiring sophisticated hardware and software components, along with specialized expertise to design, configure, and maintain. The additional complexity adds to the cost of the system.

Percona HA expertise and architectures

At Percona, we advise that, in most cases, the attributes and cost-effectiveness of high availability are the way to go. It’s plenty available and plenty scalable. We’re also big advocates of using open source software that’s free of vendor lock-in and is backed by the innovation and expertise of the global open source community to achieve high availability. 

At the same time, we’re aware that achieving high availability using open source software takes extra effort. HA doesn’t come with the complexity and price tag of a fault-tolerant system, but it still requires considerable time and expertise. 

So instead of you having to select, configure, and test architecture for building an HA database environment, why not use ours? You can use Percona architectures on your own, call on us as needed, or have us do it all for you.

Check out these helpful whitepapers, which include ready-to-use architectures:

Percona Distribution for MySQL: High Availability With Group Replication

Percona Distribution for PostgreSQL: High Availability With Streaming Replication

High availability support you can count on

Percona designs, configures, and deploys high availability databases so that applications always have access to essential data. We’ll help you ensure that applications stay up, databases stay bug-free, and your entire system runs at optimal performance levels.

Percona High Availability Database Support

FAQs

What is the difference between DR vs. HA and FT?

Disaster recovery focuses on recovering from major disruptive events and restoring operations. Fault tolerance and high availability focus on building systems that can withstand failures and continue operating with minimal interruption (HA) and without interruption (FT).

What are the goals of high availability and fault tolerance?

The goal of employing fault tolerance is to maintain system functionality and data integrity at all times, including during failures or faults. The goal of employing a high availability solution is to minimize downtime and provide continuous access to the database. 

What is the difference between HA and DR?

High availability is used to prevent downtime due to individual component failures within a live environment, while disaster recovery focuses on recovering from major events that make the database inaccessible or non-functional.

With the average cost of unplanned downtime running from $300,000 to $500,000 per hour, businesses are increasingly using high availability (HA) technologies to maximize application uptime. Unfortunately, using certain open source database software as part of an HA architecture can present significant challenges. Despite all its upside, PostgreSQL software presents such challenges. 

PostgreSQL provides the foundation to start high availability, including features and configurations for physical and logical replication and consistent physical backups with point-in-time recovery (PITR) capabilities. But PostgreSQL does not offer a complete HA solution. Therefore, DBAs must take a piecemeal approach to achieve HA with PostgreSQL, bringing together open source extensions and tools from multiple sources. 

This mish-mash of technologies creates the potential for a single point of failure (SPOF) and an interruption of services to end users — the very thing high availability is supposed to prevent. Because most SPOFs are a consequence of scale, the risk of downtime only gets worse as the environment grows. Where a high availability design once worked well, it can no longer keep up with more complex requirements.  

This blog highlights considerations for keeping your own PostgreSQL databases highly available and healthy. We’ll examine the intricacies of establishing high availability with PostgreSQL software and provide links to Percona HA PostgreSQL reference architectures for businesses of every size (startup/small, medium, large, and enterprise).

Defining PostgreSQL high availability

High availability is essential for any business that relies on digital interactions — and today, that means just about everyone. The tolerable downtime depends on business size, operations, and objectives, but that downtime must be minimal to minuscule (see the “Measuring high availability” chart below). HA in PostgreSQL databases delivers virtually continuous availability, fault tolerance, and disaster recovery. It enables businesses and organizations to meet uptime and service level objectives, protect business-critical data, and maintain performant databases.

In general terms, to achieve HA in PostgreSQL, there must be:

• Redundancy: To ensure data redundancy and provide continuous performance when a primary server fails, multiple copies of the database reside in replica servers. The same changes made in the primary database are made in the replicas. This ensures continuous business operations. PostgreSQL software supports synchronous streaming replication, asynchronous streaming replication, and logical replication.
• No single point of failure (SPOF): If the failure of a database infrastructure component could cause downtime, that component is considered an SPOF. The existence of even one SPOF means high availability has not been achieved. The majority of SPOFs are a consequence of scale, where designs that were once adequate (excellent, even) no longer meet evolving, more complex requirements. Downtime due to SPOFs can also be attributed to bottlenecks from architectures designed for applications instead of databases.
• Failover: This functionality is all about switching — in both unplanned and planned situations — from a failed primary server to a standby server. When the primary server fails, a standby server takes over as the new primary. Failover is essential to minimizing downtime.

Also, in general terms, a high availability PostgreSQL solution must cover four key areas:

• Infrastructure: This is the physical or virtual hardware database systems rely on to run. Without enough infrastructure (physical or virtualized servers, networking, etc.), there cannot be high availability.
• Topology management: This is the software management related specifically to the database and its ability to stay consistent in the event of a failure.
• Connection management: This is the software management related specifically to the networking and connectivity aspect of the database. Clustering solutions typically bundle with a connection manager. However, in asynchronous clusters, deploying a connection manager is mandatory for high availability.
• Backup and continuous archiving: PostgreSQL asynchronous replication relies on the write-ahead log (WAL) stream, and it’s vital to have centralized storage that all nodes can access. This is the location where the primary node will archive the WAL segments, and the replicas will restore any segment that isn’t present on the primary anymore. It is of extreme importance to keep the replication working if any replication delay happens and the replica node isn’t able to work at the primary’s pace. Those files can also be used for point-in-time recovery if any disaster occurs.

Key considerations for determining high availability needs

Before going into more detail, it’s important to determine whether your workloads and services require an HA architecture and, if so, to what extent your HA should be. Answers to these questions will help you evaluate the need for a high availability PostgreSQL solution based on your business requirements, potential risks, and operational limitations:

• How much downtime can your business tolerate before taking a financial hit, losing customers, or both?
• Does your business’s industry have high availability requirements? If so, what are the legal liabilities?
• How long does it take to recover from a database failure in your current setup? Can you afford that recovery time?
• Can you afford the necessary hardware, software, and operational costs of maintaining a PostgreSQL HA solution?
• Do you have the expertise and resources to design, implement, and manage a high availability solution for PostgreSQL?

Measuring high availability

Depending on the answers to the previous questions, achieving high availability can mean different things for different businesses. It depends on how much downtime an organization can withstand. With streaming services, for example, excessive downtime could result in significant financial and reputational losses for the business. Other organizations can tolerate a few minutes of downtime without negatively affecting end users.

The following table shows the amount of downtime for each level of availability, from “two nines” to “five nines” (the gold standard of HA). You’ll see that high availability doesn’t deliver 100% uptime, but it’s close.

Popularity of PostgreSQL

Before delving further into the inner workings of PostgreSQL high availability, let’s briefly examine the burgeoning popularity of this open source relational database software.

PostgreSQL has rapidly gained favor among professional developers in recent years. StackOverflow statistics show that 26% of developers preferred it in 2017, 34% in 2019, and 40% in 2021. Most recently, in StackOverflow’s 2022 Stack Developer Survey, PostgreSQL took a slight lead over MySQL (46.48% to 45.68%) as the most popular database platform among professional developers.

PostgreSQL is favored among relational database options for its complex data analysis, data science, graphing, and AI-related capabilities. PostgreSQL is known for powerful and advanced features, including synchronous and asynchronous replication, full-text searches of the database, and native support for JSON-style storage, key-value storage, and XML. 

PostgreSQL is highly extensible, enabling users to add custom functionality through plug-ins and extensions. It also features tools such as repmgr and Patroni for automatic failover and cluster management.

Being a more advanced database management system, PostgreSQL is well-suited for performing complex queries in a large environment quickly. Because it readily supports failover and full redundancy, it’s often preferred by financial institutions and manufacturers. It’s also preferred for use with geographic information systems (GIS) and geospatial data. PostgreSQL ranks as the fourth most popular database management system (DB-Engines, March 2023).

And it’s open source

Because PostgreSQL software is open source, it’s free of proprietary restrictions that can come with vendor lock-in. Developers can customize the source code and try new applications without a big budget hit. Companies can more easily scale infrastructure — up or down — to meet economic conditions and changing business objectives. With open source software, a business is not trapped into using one provider’s software, support, or services. Instead, the business may design and redesign systems as customer expectations change and business objectives evolve.

Also, with open source, there’s a global community of dedicated volunteers driving the development of PostgreSQL database technology. Open source standards and community support enable developers and DBAs to focus on accelerating PostgreSQL feature creation and enhancing availability, performance, scalability, and security. 

How does PostgreSQL high availability work?

Now, let’s go into more detail about high availability with PostgreSQL. Several methods of the aforementioned replication form the backbone of PostgreSQL high availability. They allow for data redundancy, fault tolerance, failover amid disasters, power outages, human incursions — just about any scenario. Those methods include:

Streaming replication

With streaming replication, the entire database cluster is replicated from one server, known as the primary, to one or more standby servers. The primary server continuously streams the write-ahead logs (WAL) to the standby servers, which apply the changes to their own database copies. Streaming replication comes in two modes:

• Synchronous streaming replication: In this mode, the primary server waits for confirmation from at least one standby server before committing a transaction. That confirmation ensures the data is replicated to a certain level of redundancy with minimal data loss.
• Asynchronous streaming replication: This is the default way of replicating data in PostgreSQL setups. In this mode, the primary server does not wait for confirmation before committing to transactions. The upside is enhanced performance, but there can be data loss in the event of a failure.

Logical replication

This data replication method occurs at the logical level of the database objects, such as tables, rather than at the physical level of behind-the-scenes storage. Unlike streaming replication, which replicates the entire cluster, logical replication allows for more control over what data is replicated or not replicated. 

The two main components of logical replication are publishers and subscribers. A publisher shares data and a subscriber receives and applies the replicated data. This approach provides more flexibility for replication configuration.

Beyond — and connected to — the replication methods, these components also are essential in high availability: 

Failover

As stated, this is when a standby server becomes the new primary server due to various reasons, planned or sudden and unexpected. In PostgreSQL, failover can be triggered manually, or it can be automated — with monitoring systems initiating the action.

Failover includes these steps:

1. The monitoring system sends an alert that the primary server is unavailable, triggering the failover process.
2. Another server is chosen as the new primary server.
3. Applications are redirected to the new primary server, and business-as-usual continues using the new primary server.

Automatic switchover

This method is used to minimize downtown and replace the need for manual intervention. The database system triggers automatic failover the instant that predetermined conditions occur.

With automatic switchover, cluster management tools or outside integrated tools monitor the primary server 24/7, checking that it’s connected and doing 100% of its job. The automatic switchover process kicks in if any failure of the primary server is detected, and a standby server becomes the new primary server.

Cluster management tools

Several cluster management tools, such as Patroni and Replication Manager, simplify the configuration, monitoring, and management of PostgreSQL high availability setups. As introduced above, those tools automate tasks like failover, monitoring, and maintenance, making it easier to maintain a highly available PostgreSQL environment.

Load balancing

High availability often involves distributing the database workload across multiple servers to ensure optimal performance and avoid bottlenecks. PostgreSQL supports various load balancing techniques — including connection pooling and connection routing — to distribute incoming queries and transactions among multiple database servers.

Connection pooling includes the availability of established database connections that can be made by multiple applications. Pgbouncer and pgpool-II are connection managers that accept client connections, manage the connection pool, and direct requests to the appropriate backend server.

Proxy load balancers like pgpool-II, pgbouncer, and HAProxy have the capacity to route connections to different database servers. Such a proxy acts as the traffic cop between the applications and the database servers. Steered by load balancing configurations, the proxy forwards requests from applications to the appropriate backend server. Pgpool-II has the extra capacity of a seamless split of reads and writes, while the other two have to do it using a separate port, and the read/write split has to be managed by the application.

DNS load balancing distributes application connections across multiple PostgreSQL database servers using the Domain Name System (DNS). With DNS load balancing, DNS records with the same hostname but different IP addresses are configured. When applications connect to the PostgreSQL database, the DNS server connects the app to available IP addresses in some kind of prioritized or revolving manner. This method is fairly easy to set up, but it’s not so intuitively or easily adjusted to meet changing server setup configurations. 

Monitoring and alerting

To maintain high availability in PostgreSQL databases, it is crucial to have monitoring and alerting mechanisms in place. There are multiple tools available for monitoring the health and performance of PostgreSQL instances. Those tools ensure that notifications are sent as issues and potential issues are detected. 

Bringing it all together in PostgreSQL architectures

When implemented successfully, the components we’ve examined can make for successful high availability in PostgreSQL databases. But, as stated, the PostgreSQL community version does not come with ready-to-go HA. It takes work, the right architecture, and often some outside help.  

Let’s start with the architectures. 

There’s an unfortunate misconception that high availability solutions are too expensive for startups and small businesses. But when using open source tools, coupled with an architecture such as the one offered by Percona, high availability on PostgreSQL can be achieved without a big price tag or the need for an overly complex environment. It can be done by building the HA infrastructure within a single data center. 

As your business grows, so should your high availability architecture. For medium and large businesses, the consequences of downtime, both in terms of lost revenue and erosion of customer sentiment, can be significant. High availability requires more fault-tolerant, redundant systems and probably larger investments in IT staff. Still, when using open source tools, high availability can be achieved cost-effectively and without the threat of vendor lock-in that can come from paid enterprise SQLs. For medium and large businesses, Percona provides an architecture that spreads availability across data centers to add more layers of availability to the cluster. 

For enterprises, the challenges and potential consequences increase exponentially. An architecture must address a lot more. The Percona architecture, for example, features two disaster recovery sites and adds more layers to the infrastructure in order to stay highly available and keep the applications up and running. This architecture, based on tightly coupled database clusters spread across data centers and availability zones, can offer an HA level up to 99.999% when using synchronous streaming replication, the same hardware configuration in all nodes, and fast internode connection.

You can get more details — and view actual architectures — at the Percona Highly Available PostgreSQL web page or by downloading our white paper, Percona Distribution for PostgreSQL: High Availability With Streaming Replication. But for starters, here are elements of a minimalist high availability architecture for PostgreSQL. Such architecture will include at least these four components in the design:

• Database nodes: In PostgreSQL high availability architectures — such as those depicting streaming replication, logical replication, or shared-disk clustering — multiple data nodes distribute the workload and maintain data consistency. Typically, each data node contains a copy or subset of the data.
• HA controller: This mechanism identifies when a failure of the primary node occurs and makes sure that there’s not a network partition/split brain. This same component needs a mechanism to perform the failover in case of failure on the primary node. It will elect one of the replicas to be promoted as the new primary node.
• Connection router: This mechanism changes the query routing so that application requests reach the new primary node.
• Backup component: This software or mechanism provides fast, consistent, and reliable backup and restores for PostgreSQL and also guarantees that all WAL logs are shipped to a universally accessible storage area.

Again, the descriptions immediately above are for a minimalist HA architecture. It can, and usually does, get a lot more complicated. Percona provides proven architectures to get you going, and we offer cost-effective options for help. 

Steps to deploying and maintaining PostgreSQL high availability

Once the method of replication is determined and the architecture is designed, it’s time to deploy it. As with the architecture itself, deployment can be easier and more cost-effective when enlisting high availability support for PostgreSQL from outside experts. It depends on what expertise you have on staff. 

Every database environment is different, so deployment procedures can vary, but here are some general steps:

1. Configure the primary server. This server, the primary read-write node, will orchestrate data replication to standby servers.
2. Create a primary server backup on the standby server or servers. 
3. Configure the standby server to run in hot standby mode. The hot standby allows the standby server to be used for reads. It prevents complications and losses in the event of a failure or scheduled maintenance of the primary server.
4. Configure load balancing. Using pgpool-II, HAProxy, or other PostgreSQL load balancing tools, your database will be ready for high traffic and to distribute read queries across multiple servers.
5. Implement backup and disaster recovery. Since HA alone does not guarantee data protection, you should implement mechanisms for data durability and disaster recovery.
6.  Regularly schedule backups and test the restore process to ensure data integrity.
7. Test the setup. Here are a couple options. Ideally, both could be used. 

• End-to-end (E2E) testing, though time-consuming, will show whether all components and applications of your HA setup work as intended.
• Chaos engineering is another option. This is when engineers introduce problems throughout the database infrastructure, so they can identify failure points and create solutions — avoiding costly downtime and potential loss of frustrated customers.

Security considerations for high availability PostgreSQL

Keeping databases secure demands attention, of course. Yet again, it should be emphasized that every environment is unique, and specific security requirements will vary accordingly. It’s important to check out PostgreSQL documentation. In some cases, if there is no on-staff expertise to maintain adequate security levels of your high availability cluster, it’s wise to consider support.

But whether you can keep your environment secure on your own or need that outside help, there are some general best practices for securing a cluster in a high availability environment:

Configure SSL/TLS encryption of communication with the database cluster. You can disable unnecessary procedures and make it so connections occur only across trusted networks.

Control access. Implementing role-based access control (RBAC) for PostgreSQL can be a helpful way of managing permissions and restricting access to sensitive data.

Use strong authentication. That means strong user passwords that are regularly changed. In more sensitive situations, it’s advisable to implement more secure methods like certificate-based authentication or LDAP integration. PostgreSQL supports a variety of external authentication methods, including GSSAPI, LDAP, and RADIUS. 

Audit, log, and regularly review. You can configure PostgreSQL to log queries, failed logins,  administrative activity, and any other events. Then, you can examine the logs at any time to spot potential security breaches or suspicious activity.

Conduct regular updates. Apply the latest PostgreSQL security patches and bug fixes ASAP when available. Stay active, or at least observant, as a participant in the global open source community. Stay informed about the latest PostgreSQL security information, including news about vulnerabilities and recommended patches.

Percona high availability architectures and support

There’s no getting around it; achieving high availability with PostgreSQL takes some work — whether you do it or have someone else do it. But if you’d like to avoid or ditch the piecemeal approach to high availability described earlier, Percona HA PostgreSQL architectures pull together open source tools and components to enhance and harden interoperability, ensuring your applications always have access to the data they need. You can implement an architecture yourself, call on Percona as needed, or have us manage your PostgreSQL HA database completely. 

Regardless of the level of support you choose (and you can change your mind at any time), you’ll have a proven architecture, and you’ll avoid vendor lock-in, potentially exorbitant costs, and other pitfalls of going with proprietary arrangements. 

A good place to start is our Highly Available PostgreSQL web page. There, you’ll find ready-to-use PostgreSQL high availability architectures designed specifically and separately to address the needs of small businesses and startups, medium and large businesses, and enterprise and globally distributed businesses.

Also, check out our white paper — Percona Distribution for PostgreSQL: High Availability With Streaming Replication. It will show you how to pull together open source tools and components for achieving the highest levels of HA on PostgreSQL, even as you scale. The white paper also provides the ready-to-use architectures.

High Availability PostgreSQL from Percona

Download white paper: Percona Distribution for PostgreSQL: High Availability With Streaming Replication

FAQs

The following are commonly asked questions and short answers about high availability in PostgreSQL databases. More detailed answers are presented in the sections above.

What is the difference between high availability and disaster recovery?

High availability provides continuous access to a PostgreSQL database through replication, failover mechanisms, and the absence of single points of failure. Disaster recovery is about restoring data and services after a catastrophic event by using backups, replication, and recovery procedures. 

Can high availability architecture eliminate all downtime?

High availability doesn’t deliver 100% uptime, but it’s close. The gold standard of HA is “five nines,” which means a database is available 99.999% of the time.

What are common challenges when implementing high availability architecture?

The PostgreSQL community version doesn’t come with high availability features and functionality out of the box. That often requires a piecemeal approach to achieving HA. DBAs must bring together a few open source extensions and tools from multiple sources.

What technologies can be used for high availability architecture?

A multitude of technologies must be used together to achieve high availability in a PostgreSQL database. Essential technologies include replication, failover, load balancing, and cluster management.

How often should high availability architecture be tested and validated?

The frequency of testing and validation depends on several factors, including what’s at stake for the business and the complexity of the HA architecture. Certainly, a quarterly in-depth check is advisable, and for more critical situations, monthly might might be advisable..

To make data count and to ensure cloud computing is unabated, companies and organizations must have highly available databases. That doesn’t mean downtime is nonexistent, but it must be limited so that significant/irreparable damage (as defined by the business or industry) does not occur.

This guide provides an overview of what high availability means, the components involved, how to measure high availability, and how to achieve it.

Defining high availability

In general terms, high availability refers to the continuous operation of a system with little to no interruption to end users in the event of hardware or software failures, power outages, or other disruptions. A basic high availability database system provides failover (preferably automatic) from a primary database node to redundant nodes within a cluster.

HA is sometimes confused with “fault tolerance.” Although the two are related, the key difference is that an HA system provides quick recovery of all system components to minimize downtime. Some disruption might occur, but it will be minimal. Fault tolerance aims for zero downtime and data loss. As such, fault tolerance is more expensive to implement because it requires dedicated infrastructure that completely mirrors the primary system.

In general terms, again, when it comes to database reliability, the stakes are high. Database downtime can hurt or doom any company with anything to do with the internet. More specifically, a 2022 ITIC survey found that downtime costs more than $300,000 per hour for 91% of small-, mid-size, and large enterprises. Among just the mid-size and large respondents, 44% percent said a single hour of downtime could potentially cost them more than $1 million.

Introducing key parts of high availability

• Data backup and recovery: A database backup is created and stored in another location to help keep the system operational after a natural disaster, a cyber attack, or other problem.
• Data replication: Data is continually copied from one database to another to ensure that the system remains operational even if one database fails.
• Clustering: Multiple nodes work together (and separately if necessary) to provide access to data, ensuring that the system remains operational even if one node fails. (More in the following sub-section.)
• Load balancing: Requests are evenly distributed across multiple database servers, ensuring the system remains operational even if one server fails.
• Automated failover: To keep the database operational and minimize downtime, it automatically switches to a backup server if the primary server fails.

More about high availability clusters

HA clusters — also known as failover clusters — are groups of interconnected servers that work together to ensure that an application or service remains available to users. Redundancy and failover protection ensure that if one server fails or goes offline, another server will automatically take over its workload. Typically, the servers are configured in a primary/replica configuration, with one server designated as the primary server that handles all incoming requests and the others designated as replica servers that monitor the primary and take over its workload if it fails.

Additionally, most database setups these days include a read-only mode or some capabilities that help distribute the load. With PostgreSQL, when using physical replication, all the replicas will be in read-only mode. Logical replication has the capability to allow the writes, but it’s not recommended to have writes in both nodes at the same time without a strong conflict-resolution solution. You can, alternatively, set specific tables or a database to read-only mode using the pg_hba.conf file, or you can set read-only access permissions. With MySQL, you can set the whole instance to a read-only mode using a specific parameter in the software’s configuration. You can also restrict write operations by restricting users to read-only privileges pertaining to specific databases or tables. With MongoDB, you can set read-only privileges in which users can query data but cannot perform write operations.

HA clusters are used in web hosting, email hosting, database hosting, and other services that require high availability.

How does high availability work?

High availability works through a combination of the following:

• No single point of failure (SPOF): You must eliminate any single point of failure in the database environment, including physical or virtual hardware the database system relies on that would cause it to fail.
• Redundancy: Critical components of a database are duplicated so that if one component fails, the functionality continues by using a redundant component. For example, in a server cluster, multiple servers are used to host the same application so that if one server fails, the application can continue to run on the other servers.
• Load balancing: Traffic is distributed across multiple servers to prevent any one component from becoming overloaded. Load balancers can detect when a component is not responding and put traffic redirection in motion.
• Failure detection: Monitoring mechanisms detect failures or issues that could lead to failures. Alert mechanisms report those failures or issues so that they are addressed immediately.
• Failover: This involves automatically switching to a redundant component when the primary component fails. If a primary server fails, a backup server can take over and continue to serve requests.

Components of high availability infrastructure

Multiple copies of data

Data redundancy helps prevent data loss due to hardware or software failures. By storing multiple copies of data, database administrators can recover lost or corrupted data and maintain data integrity. Redundancy is also critical for disaster recovery. In the event of a natural disaster, cyber attack, or other catastrophic event, redundant systems can help ensure that the database can be quickly restored to a functioning state.

Multiple servers or databases

By copying and maintaining data on multiple servers or databases, database administrators can ensure that there is always a backup copy available. They can use that replicated data to quickly restore the database to a functioning state in case of a system failure, attack, or other potentially damaging event.

Load balancers

Load balancers help distribute incoming traffic across multiple database servers, ensuring that no single server is overloaded with requests. This helps prevent the possibility of a single point of failure, which could cause downtime or slow performance. It also supports the flexibility and scalability of the database infrastructure.

Failover mechanisms

The primary purpose of failover — the automatic process of switching from a failed or offline database server to a standby or backup server — is to ensure user access and functionality of databases without interruption, even if there is a hardware or software failure. By implementing failover mechanisms, organizations can ensure that their critical applications and services remain available even in the event of a failure, minimizing the impact on their business operations.

How to achieve high availability

Not all companies are the same, nor are their requirements for HA. When planning your database HA architecture, the size of your company is a great place to start to assess your needs. For example, if you’re a small business, paying for a disaster recovery site outside your local data center is probably unnecessary and may cause you to spend more money than the data loss is worth. All companies, regardless of size, should consider how to strike a balance between availability goals and cost.

• Startups and small businesses: Most startups and small businesses can achieve an effective HA infrastructure within a single data center on a local node. This base architecture keeps the database available for your applications in case the primary node goes down, whether that involves automatic failover in case of a disaster or planned switchover during a maintenance window.
• Medium to large businesses: If you have enough additional budget, consider adding a disaster recovery site outside your local data center. This architecture spans data centers to add more layers of availability to the database cluster. It keeps your infrastructure available and your data safe and consistent even if a problem occurs in the primary data center. In addition to the disaster recovery site, this design includes an external layer of nodes. If communication between the sites is lost, the external node layer acts as a “source of truth” and decides which replica to promote as a primary. In doing so, it keeps the cluster healthy by preventing a split-brain scenario and keeps the infrastructure highly available.
• Enterprises: An enterprise HA architecture adds another layer of insurance for companies that offer globally distributed services and could face devastating revenue and reputational losses in the event of substantial downtime. This architecture features two disaster recovery sites, adding more layers for the infrastructure to stay highly available and keep applications up and running. Based on tightly coupled database clusters spread across data centers and geographic availability zones, this architecture can offer 99.999% uptime when used with synchronous streaming replication, the same hardware configuration in all nodes, and fast internode connections.

How to measure high availability

High availability does not guarantee 100% uptime, but it allows you to get pretty close. Within IT, the gold standard for high availability is 99.999%, or “five-nines” of availability, but the level of HA needed really depends on how much downtime you can bear. Streaming services, for example, run mission-critical systems in which excessive downtime could result in significant financial and reputational losses for the business. But many organizations can tolerate a few minutes of downtime without negatively affecting their end users.

The following table shows the amount of downtime for each level of availability, from “two nines” to “five nines.”

Here are additional metrics used to determine the reliability of a database, make adjustments that minimize downtime, and set benchmarks for meeting business continuity requirements. The desired value or range for each of these depends on the specific needs and risk tolerance of the company or organization that uses it:

• Mean time between failures (MTBF) — a measure of the average time between two failures of a database or component. The MTBF is calculated by dividing the total operating time of the system by the number of failures that have occurred during that time.
• Mean downtime (MDT) — the average amount of time that a database system is unavailable due to planned or unplanned downtime. The MDT is calculated by adding up the total amount of time that the system was down over a given period and dividing it by the number of downtimes.
• Recovery time objective (RTO) — the maximum time that a system can be down before it starts to have a significant effect on business operations. Organizations use the RTO to determine how quickly they must recover their systems to meet business continuity requirements.
• Recovery point objective (RPO) — the point in time by which an organization must recover its data in case of a disaster or system failure. For example, an RPO of 1 hour means that an organization can tolerate a maximum data loss of 1 hour’s worth of data. To ensure that the RPO is met, organizations typically implement backup and recovery procedures that enable them to restore data to a specific point in time.

High availability architecture for open source databases

No matter where a database infrastructure is located (on-premises, public or private cloud environment, or hybrid environment) or what technology is used — be it MySQL, PostgreSQL, or MongoDB — it’s vital to eliminate single points of failure. To be prepared for disaster recovery and attain high availability, server redundancy must be built into the database architecture.

And for that redundancy to be beneficial, the design of the database infrastructure must incorporate failover mechanisms that duplicate essential database functions and components.

The considerations described above (and in other sections) must be addressed across the following areas if a high availability architecture is to be achieved:

• Infrastructure: This is the physical or virtual hardware that database systems rely on to run. Without enough infrastructure (physical or virtualized servers, networking, etc.), there cannot be high availability.
• Topology management: This is the software management related specifically to the database and managing its ability to stay consistent in the event of a failure.
• Connection management: This is the software management related specifically to the networking and connectivity aspect of the database. Clustering solutions typically bundle with a connection manager. However, in asynchronous clusters, deploying a connection manager is mandatory for high availability.
• Backup and continuous archiving: This is of extreme importance if any replication delay happens and the replica node isn’t able to work at the primary’s pace. The backed-up and archived files can also be used for point-in-time recovery if any disaster occurs.

Which database is best for high availability?

When choosing a database for high availability, some proprietary options offer attractive specialized features, including built-in replication, automatic failover, and advanced clustering. Those ready-to-go capabilities can simplify the implementation of high availability. Dedicated vendor support can also be a lure. Proprietary vendors can provide expertise, guidance, and timely resolutions that ensure the stability and availability of a database.

But with the right expertise — whether it’s from within your organization or through outside assistance — open source databases can provide the same benefits, as well as other advantages, including:

• Cost-effectiveness: Open source software is typically free to use, which can significantly reduce the overall cost of establishing high availability. Conversely, proprietary databases often require expensive licenses and ongoing maintenance fees.
• Flexibility: With open source databases, you have access to the source code, allowing you to modify and optimize the database to meet your specific high availability requirements. This flexibility can be crucial in designing a scalable architecture.
• Community support: Large and active communities of users, developers, and DBAs can be invaluable when it comes to troubleshooting issues, finding solutions, and staying up-to-date with the latest developments in high availability techniques. The transparency of open code enhances security as potential vulnerabilities can be identified and patched more quickly by the community.
• Freedom from vendor lock-in: With open source software, you are not tied to a specific vendor or locked into a particular technology stack. You have the freedom to choose and integrate various tools and technologies that best suit your high availability strategy.

HA is an important goal, but switching to a proprietary database solely for HA limits the other benefits of open source database software. In addition to enabling a strong database HA architecture, open source avoids costly licensing fees, offers data portability, gives you the freedom to deploy anywhere, anytime you want, and delivers great software designed by a community of contributors who prioritize innovation and quality.

Getting started with open source database high availability

There are numerous open source options available, but covering all of them here would not be practical. (For a more detailed examination of open source options, read The Ultimate Guide to Open Source Databases.)

Here, for this examination of high availability, we are focusing on three of the most popular open source options:

• MySQL: This relational (table-based) database software supports asynchronous replication, which provides redundancy and allows for failover in case the primary database becomes unavailable. MySQL supports the creation of read replicas, which can distribute the read workload, improve performance, and provide high availability for read-intensive applications. Another feature (among many) of note is MySQL Cluster, a distributed, shared-nothing database clustering solution. It combines synchronous replication, automatic data partitioning, and node-level failover to provide high availability and scalability.
• PostgreSQL: This relational database software supports numerous high availability options, including asynchronous streaming replication, logical replication, and tools such as repmgr and Patroni for automatic failover and cluster management. PostgreSQL also supports synchronous replication in which the primary server waits for confirmation from at least one standby server before acknowledging a transaction. PostgreSQL point-in-time recovery uses continuous archiving to create a backup that allows you to recover the database to a specific state.
• MongoDB: While not open source (see our blog Is MongoDB Open Source? Is Planet Earth Flat? for more), we’ll categorize it here for easy reading. This non-relational (document-based) database uses replica sets to provide high availability. MongoDB’s replica sets maintain multiple copies of data across different nodes. The primary node receives write operations and replicates them to the secondary nodes asynchronously or synchronously, depending on the chosen replication mode. As a result, data is redundantly stored, reducing the risk of data loss.

Some open source databases include great options for HA but generally don’t include a built-in HA solution. That means you’ll need to review the various extensions and tools available carefully. Those extensions and tools can be excellent for enriching their respective databases, but as your environment scales, the database might not keep pace with evolving, more complex requirements.

Greater complexity means that you and your team will need certain skills and knowledge. For example, it might be necessary to write application connectors, integrate multiple systems, and align business requirements with your HA solution. You should also understand open source databases, applications, and infrastructure. Consider the possibility of needing outside expertise to help you manage your HA architecture.

Comparing high availability to similar systems

High Availability vs. Disaster Recovery

High availability is focused on preventing downtime and ensuring that the database remains available, while disaster recovery is focused on recovering from a catastrophic event and minimizing negative effects on the business. High availability typically involves redundant hardware, software, application, and network components that can quickly take over if the primary component fails, while disaster recovery typically involves regular backups, replication to a secondary site, and a well-defined recovery plan that outlines the steps to be taken in the event of a disaster. Companies certainly can and should incorporate high availability and disaster recovery into database architecture.

High Availability vs. Fault Tolerance

High availability ensures that the database remains up and running almost all the time, while fault tolerance is focused on preventing data loss or corruption in the event of a fault or error. Redundancy and failover mechanisms figure prominently in high availability architecture, a design that gives users continued access even if there is a hardware or software failure. A fault-tolerant system is designed to correct and detect errors as they occur such that normal operation is not interrupted. This can mean that instead of failing completely, a system continues to operate, but possibly at a reduced level of efficiency.

High Availability vs. Redundancy

Redundancy is a key element of high availability, so it’s not an either-or situation. As stated, high availability refers to a database that’s functional almost all the time (i.e., “five nines” or some other standard depending on company size and requirements). In other words, there’s little or no downtime. Redundancy, a specific part of high availability, refers to the duplication of critical components or data to provide a backup in case the primary component or data is lost or corrupted. To establish redundancy, the same data must be accessible from at least two places. That means having a primary system and a secondary system. The secondary system includes failover monitoring, so when a failure occurs, data traffic is redirected to that secondary system. In addition to redundancy, high availability includes load balancing, failure detection, and failover, as well as ensuring there is no single point of failure (SPOF).

Best practices for achieving high availability

For medium, large, and enterprise-size companies (as well as startups and small companies, depending on resources), the following general practices can help when planning, implementing, maintaining, and testing high availability architectures. Please note that these are presented as general best practices. (For more detailed information, check out the Percona architecture and solution links in subsequent sections.) Best practices include:

Conduct research and identify objectives

Early on, determine what you need and what you can afford. Here are the early steps:

• Perform analysis: A good place to start might be to conduct a Failure Mode Analysis (FMA). An FMA or similar analysis can identify possible failure points in the database infrastructure. This will help in determining the redundancy requirements of the different components.
• Determine requirements: The level of HA that is needed depends on how much downtime a company can bear. For example, some companies that offer streaming services will suffer significant financial and reputational losses in just a couple of minutes of downtime. Other organizations can tolerate a few minutes of downtime without negatively affecting their end users.
• Consider costs: Multiple factors will, of course, affect how much you should budget. Depending on your setup, costs can include:
  • Hardware devices (servers, storage devices, network switches, etc.)
  • Networking equipment (switches, routers, etc.)
  • Software for clustering, replication, load balancing, failover, etc.; with proprietary licensing, costs can increase significantly as you scale
  • Cloud egress fees that might be charged when moving data out of a vendor’s network
  • Staffing and training (an HA system requires specialized expertise)
  • Testing to ensure the system will function during likely and unlikely conditions
  • Scalability to allow for maximum growth

Focus on redundancy and failover

Whether a company’s database infrastructure is on-premises, in a public or private cloud environment, or is run in a hybrid environment, it is mission-critical that there are no single points of failure. To avoid them, achieve high availability, and be poised for disaster recovery, there must be server redundancy. And for redundancy to matter, the database infrastructure design must include failover. In other words, there must be mechanisms for putting the duplication of essential database functions and components into action.

To establish redundancy, the same data must be accessible from at least two places. That means having a primary system and a secondary system. The secondary system includes failover monitoring, so when a failure occurs, data traffic is redirected to that secondary system.

Use shared-nothing (or shared-none) clusters

High availability can be achieved using the concepts of “shared-all” and “shared-nothing” (also called “shared-none”) clusters. Open source technologies often prefer to use shared-nothing clusters because they are not only simpler to implement and deploy but also to scale. By incorporating that setup, the developer or DBA will ensure that each node within a cluster stores, manages, and processes its data. That will minimize problems that can occur when nodes share access to and the ability to modify the same memory or storage.

The most common shared-nothing architecture has a primary node responsible for receiving all data updates and propagating the updates to the replica nodes with little to no interference or conflict with other nodes. (Each node has its own cache buffer.) If one instance fails, another instance in another node can fill the role entirely, and high availability remains.

In addition to preventing single points of failure and system-wide shutdowns, a big advantage of a shared-nothing architecture is scalability. You can easily scale a shared-nothing system by adding nodes. Since those nodes don’t share data, there won’t be dueling processes that could slow or halt operations.

Apply load balancing

In addition to incursions from the outside and errors generated in-house, unusually high customer use can overwhelm a company’s database operations. That’s why it’s essential to include load balancing in your design. In times of high use, load balancing redirects workloads to components of the infrastructure best suited to pick up the extra work.

To keep services — and the databases that deliver them — running smoothly, a tiered architecture of server clusters helps. In the event that a server fails, a replica server in a different cluster can assume the role of the failed server. The load balancer is the key; it serves as a traffic cop of sorts, directing and redirecting flow from end users/applications to servers where data will flow as intended. If a server fails, the load balancer redirects traffic to a replicated server in another cluster.

Use a container-orchestration platform

Containers can provide multi-cloud compatibility and cost-efficiency, and they are a great tool for isolating applications. But containers are not faster than anything else with the same resources. They can, in fact, be a lot more complex to troubleshoot than the processes of virtual machines (VMs) and bare metal environments. Still, companies find themselves adding more and more containers across multiple hosts.

To get real benefits from those containers, it’s advisable to use a system of orchestrating and managing containerized applications. Kubernetes is a platform that can provide that system. It enables developers and DBAs to manage sizable containers across multiple hosts. Using Kubernetes can yield many benefits, including greater automation than that of traditional VMs, enhanced fault tolerance, and increased flexibility and scalability.

Deploy in multiple regions

Sometimes there’s a misperception that “cloud” means safe. Just because you’ve entrusted your database with a cloud provider instead of hosting on-site doesn’t mean you won’t lose service. Cloud provider data centers go down, and so do entire regions of data centers. So whether you host your own servers, you use a cloud provider, or you use a combination of both, it’s wise to replicate databases across regions, not in the same region.

With replication in different geographic regions, you can assure instantaneous failover in the event of an entire region going down.

Maintain deployment consistency

Using proven and secure code and processes is important in maintaining data integrity and reliability. Deployment consistency is essential in preventing the introduction of bugs and other intrusions that could lead to data corruption or data loss and could slow or interrupt applications and even entire systems. By maintaining database deployment consistency, organizations can ensure that their data is always up-to-date and accurate and that their applications perform reliably. Data consistency is especially important in financial and healthcare systems, where incorrect or outdated data can have serious and even fatal consequences.

Conduct end-to-end (E2E) testing

This practice tests the entire system to ensure that all database components and software applications (and any subsystems) work as intended — individually and in unison. E2E testing verifies that components meet performance, security, and usability requirements. It is often used to identify issues that arise from the interaction between different components of the system, such as when data is passed from one component to another. This type of testing requires a solid understanding of user behaviors and goals, which can be varied. E2E testing, therefore, can be time-consuming and potentially more expensive than some limited budgets allow.

Incorporate chaos engineering

Chaos engineering is testing that involves intentionally introducing failures into a software system to identify potential issues and weaknesses. By intentionally introducing problems throughout the database infrastructure, engineers can identify failure points and create solutions — avoiding costly downtime and potential loss of frustrated customers.

In well-orchestrated chaos engineering, there’s an experimental version of the system and a control version. A barrage of problems and outright attacks are injected into the system. The technical team then reviews the logging data to determine how the incursions affected the system.

Chaos engineering is designed to uncover unexpected, even shocking, surprises. Unlike targeted testing of traditional DevOps procedures, chaos engineering is widespread throughout the entire system. Since it’s performed on live systems, there is a chance that users can be briefly affected, but chaos engineering is a great motivator for following resilient design and maintenance practices. Most of all, chaos engineering is a way to fend off disaster.

Consider outside help

Consider the possibility that you might need outside expertise to help you plan, create, and manage an HA architecture. If an open source solution is right for you, but you need help implementing it, Percona recommends that you research to ensure the outside company is committed to the global open source community. Also, make sure you’ll have the freedom to innovate and to scale — up or down — as your company sees fit. Avoid unnecessary vendor lock-in. If you don’t use Percona, find another company that gives you those freedoms while helping you build the high availability infrastructure you need.

Percona architectures for highly available MySQL and PostgreSQL

Percona uses open source software to design architectures for high availability databases. Businesses of all sizes — small, medium, large, and enterprise — can use our designs to create their own architectures and ensure that applications and data stay available and in sync. If you need help, call on us for on-demand support as you implement an architecture. Or we can do it for you completely.

Here are resources (with viewable architectures) for help getting started:

High availability MySQL architectures

Check out Percona architecture and deployment recommendations, along with a technical overview, for a MySQL solution that provides a high level of high availability and assumes the usage of high read/write applications.

Percona Distribution for MySQL: High Availability with Group Replication

If you need even more high availability in your MySQL database, check out Percona XtraDB Cluster (PXC).

High availability PostgreSQL architectures

Access a PostgreSQL architecture description and deployment recommendations, along with a technical overview, of a solution that provides high availability for mixed-workload applications.

Percona Distribution for PostgreSQL: High Availability with Streaming Replication

High availability support you can count on

Percona designs, configures, and deploys high availability databases so that applications always have access to essential data. Then, we apply the same level of expertise and commitment to ensuring your databases continue to run smoothly and continuously. We’ll help you ensure that applications stay up, databases stay bug-free, and your entire system runs at optimal performance levels.

Percona High Availability Database Support

FAQ

The following are commonly asked questions and short answers about high availability in databases. More detailed answers are presented in the sections above.

What does high availability mean?

High availability refers to a database’s ability to remain operational and accessible to users without significant downtime or interruptions. To achieve high availability, a database system must be designed with redundancy and failover mechanisms and have no single points of failure. High availability is particularly important in the e-commerce, financial, and healthcare industries.

What is an example of high availability?

Database replication is an example of high availability. Multiple copies of the same database are maintained on different servers so that if one server fails, the database can still be accessed from another server. Different techniques are used to achieve database high availability, including primary-replica replication or multi-primary replication.

How does high availability work?

High availability of a database infrastructure works through a combination of redundancy, load balancing, failure detection, failover, and having no single point of failure (SPOF). Those elements combine to ensure that data is always accessible and that there is minimal downtime or interruption in service, even in the face of hardware or software failures.

What does 99.99% availability mean?

99.99% availability (or “four nines”) means that a database is down no more than 52.60 minutes per year, 4.38 minutes per month, 1.01 minutes per week, and 8.64 seconds per day.

What does 99.999% availability mean?

99.999% availability (or “five nines”) means that a database is down no more than 5.26 minutes per year, 26.30 seconds per month, 6.05 seconds per week, and 864.00 milliseconds per day.