That famous journey began simply—just follow the yellow brick road. But as we all learned, that pretty little lane entered some pretty challenging territory pretty quickly. The same is true for database administrators on the road to building more sophisticated environments to support their new Postgres deployments. More often than not, DBAs who master their operating system configurations, best their security and access control challenges and clear their hardware and storage hurdles suddenly find they hit bumps in the road when it comes to high availability, replication and performance tuning.
This trifecta represents the more sophisticated of database challenges that DBAs encounter in their path toward optimizing Postgres for enterprise environments. The key to tackling these challenges is examining them holistically and addressing them one at a time at the architectural level, often with a team of experts alongside.
High Availability Can Equal High Anxiety
EnterpriseDB (EDB) introduced in December EDB Failover Manager, a reliable and cost-effective High Availability solution for Postgres that eliminates the need for users to build their own or cobble together solutions from other providers. EDB Failover Manager complements the Postgres Streaming Replication feature for setting up standby databases by allowing the Master node to automatically failover to a standby replica node in the event of a software or hardware failure on the master. (For more information, download our white paper, High Availability with Postgres Plus Advanced Server, under the heading Postgres Technical Topics.
But still, EDB Failover Manager functions within a larger environment. And, users still may choose to develop HA architectures using other solutions, such as the Red Hat Cluster Suite, or they’ve made an investment in another solution that supports HA, such as EMC’s Avamar. Further, users need to factor in their other corporate information systems, such as how to integrate their ETLprocesses to ensure data from their new Postgres deployment is connected with data in other systems. And not just connected, but functioning efficiently.
DBAs need to manage a range of moving parts when setting up a high 9’s enterprise High Availability architecture. They often need help with the proper use of fencing devices that ensure databases don’t step on one another. They typically need assistance with the proper integration of disk storage and streaming replicas to ensure the best failover behavior and avoid the nightmare of a split-brain scenario. Weaving together a complex web of solutions can be challenging and even veteran DBAs seek out or can benefit from additional resources.
Seeing Double with Replication
Replicas play a key role in HA configurations and with multiple nodes and replicas, and it’s important for DBAs to institute policies and procedures for backing up data, with both binary and logical backups. Based on our experience, we recommend that in addition to binary backups, enterprises do logical backups, even though it just dumps your database into a huge SQL file so it’s really, really slow. But the benefit is when you take that logical backup and restore it against a different database and do some checking—a logical backup will find the little problems binary backups do not. So while binary backups are fast to reload, they don’t always catch data corruption. These kinds of issues often come up as enterprises begin to expand their deployments across the enterprise and our experience has produced some best practices that may be counterintuitive.
In working with so many enterprise Postgres deployments, we have also found that DBAs need help in monitoring replicas and repairing them should they find them out of synch. True, replication means this shouldn’t happen and many assume it doesn’t. But it takes a sharp and sophisticated eye to see when replicas are out of whack and a steady and studied hand to make repairs.
Performance Killers and Magic Elixirs
Optimizing for performance draws upon a range of issues, from how Postgres servers interact with other servers or services running on the same server, to connection pooling and load balancing traffic across multiple services to the optimal use of storage. Issues such as whether you’re using table spaces or relocating database directories and whether you’re using attached storage or not as well as operating system parameters also affect performance. And still, there are other issues that have an impact.
Performance is where the rubber meets the road when it comes to making enterprise stakeholders happy. Further, performance tuning is an ongoing process. I know it’s tempting to move on to the next project once databases are up and running and applications have been created and released into system use. Changes in usage patterns and data loads, misaligned Java queries, poorly written SQL queries and misaligned indexes—these can drag down performance months or years after applications are in use. We typically discover that it’s issues like these and not Postgres that cause the DBAs the biggest headaches. What’s more, these issues have easy fixes if you know what to look for.
Team Spirit on the Road to Success
We have found that addressing HA, replication and performance optimization are a little more involved than a couple calls to our support desk. We created a package of targeted training modules and customized services to help users at this point in the Postgres deployment lifecycle.
This package, Postgres Team Success, also factors in how to train staff as more DBAs become involved to manage expanded deployments. It’s the most advanced of our Postgres Deployment Services, which we’ve been exploring in this series of blog posts. For an overview of the services, please read the first post in the series.
Marc Linster is senior vice president of products and services at EnterpriseDB.