PostgreSQL 12 High Availability Cookbook: Over 100 recipes to design a highly available server with the advanced features of PostgreSQL 12, 3rd Edition

Description

A comprehensive guide to understanding key techniques for architecture and hardware planning, monitoring, replication, backups, and decoupling Key Features Newly updated edition, covering the latest PostgreSQL 12 features with hands-on industry-driven recipes Create a PostgreSQL cluster that stays online even when disaster strikes Learn how to avoid costly downtime and data loss that can ruin your business Book Description Databases are nothing without the data they store. In the event of an outage or technical catastrophe, immediate recovery is essential. This updated edition ensures that you will learn the important concepts related to node architecture design, as well as techniques such as using repmgr for failover automation. From cluster layout and hardware selection to software stacks and horizontal scalability, this PostgreSQL cookbook will help you build a PostgreSQL cluster that will survive crashes, resist data corruption, and grow smoothly with customer demand. You’ll start by understanding how to plan a PostgreSQL database architecture that is resistant to outages and scalable, as it is the scaffolding on which everything rests. With the bedrock established, you'll cover the topics that PostgreSQL database administrators need to know to manage a highly available cluster. This includes configuration, troubleshooting, monitoring and alerting, backups through proxies, failover automation, and other considerations that are essential for a healthy PostgreSQL cluster. Later, you’ll learn to use multi-master replication to maximize server availability. Later chapters will guide you through managing major version upgrades without downtime. By the end of this book, you’ll have learned how to build an efficient and adaptive PostgreSQL 12 database cluster. What you will learn Understand how to protect data with PostgreSQL replication tools Focus on hardware planning to ensure that your database runs efficiently Reduce database resource contention with connection pooling Monitor and visualize cluster activity with Nagios and the TIG (Telegraf, InfluxDB, Grafana) stack Construct a robust software stack that can detect and avert outages Use multi-master to achieve an enduring PostgreSQL cluster Who this book is for This book is for Postgres administrators and developers who are looking to build and maintain a highly reliable PostgreSQL cluster. Although knowledge of the new features of PostgreSQL 12 is not required, a basic understanding of PostgreSQL administration is expected. Cover Title Page Copyright and Credits About Packt Contributors Table of Contents Preface Chapter 1: Architectural Considerations Setting expectations with RPO Getting ready How to do it... How it works... There's more... Defining timetables through RTO Getting ready How to do it... How it works... There's more... This may seem familiar Node counts Picking redundant copies Getting ready How to do it... How it works... There's more... Selecting locations Getting ready How to do it... How it works... There's more... See also Having enough backups Getting ready How to do it... How it works... There's more... See also Considering quorum Getting ready How to do it... How it works... There's more... Introducing indirection Getting ready How to do it... How it works... There's more... See also Preventing split brain Getting ready How to do it... How it works... There's more... Incorporating multi-master Getting ready How to do it... How it works... There's more... See also Leveraging multi-master Getting ready How to do it... How it works... There's more... See also Chapter 2: Hardware Planning Planning for redundancy Getting ready How to do it... How it works... There's more... See also Having enough IOPS Getting ready How to do it... How it works... There's more... A working example Making concessions Sizing storage Getting ready How to do it... How it works... There's more... Real-world example Adjusting the numbers Incorporating the spreadsheet Investing in a RAID Getting ready How to do it... How it works... There's more... See also Picking a processor Getting ready How to do it... How it works... There's more... Simultaneous multithreading  Clock boosting Power usage See also Allocating enough memory Getting ready How to do it... How it works... There's more... Exploring nimble networking Getting ready How to do it... How it works... There's more... A networking example Remember redundancy Saving the research Managing motherboards Getting ready How to do it... How it works... There's more... See also Selecting a chassis Getting ready How to do it... How it works... There's more... Saddling up to a SAN Getting ready How to do it... How it works... There's more... See also Tallying up Getting ready How to do it... How it works... There's more... Protecting your eggs Getting ready How to do it... How it works... There's more... Chapter 3: Minimizing Downtime Determining acceptable losses Getting ready How to do it... How it works... Configuration – getting it right the first time Getting ready How to do it... How it works... There's more... See also Configuration – managing scary settings Getting ready How to do it... How it works... There's more... Distinct settings More information See also Identifying important tables Getting ready How to do it... How it works... There's more... Reset stats Using pgstattuple See also Defusing cache poisoning Getting ready How to do it... How it works... See also Terminating rogue connections Getting ready How to do it... How it works... There's more... Reducing contention with concurrent indexes Getting ready How to do it... How it works... There's more... No transactions One at a time Dangers of OLTP use See also Managing system migrations Getting ready How to do it... How it works... There's more... See also Managing software upgrades Getting ready How to do it... How it works... There's more... See also Mitigating the impact of hardware failure Getting ready How to do it... How it works... There's more... Copying WAL files more easily Built-in delay Adding compression Secondary delay See also Applying bonus kernel tweaks Getting ready How to do it... How it works... There's more... Some additional background Be wary of THP Chapter 4: Proxy and Pooling Resources Exploring the magic of virtual IPs Getting ready How to do it... How it works... There's more... Obtaining and installing HAProxy Getting ready How to do it... How it works... See also Configuring HAProxy to load balance PostgreSQL Getting ready How to do it... How it works... There's more Determining connection costs and limits Getting ready How to do it... How it works... There's more... Installing PgBouncer Getting ready How to do it... How it works... See also Configuring PgBouncer safely Getting ready How to do it... How it works... There's more... What about pool_mode? Problems with prepared statements See also Connecting to PgBouncer Getting ready How to do it... How it works... See also Listing PgBouncer server connections Getting ready How to do it... How it works... There's more... See also Listing PgBouncer client connections Getting ready How to do it... How it works... There's more... See also Evaluating PgBouncer pool health Getting ready How to do it... How it works... There's more... See also Changing PgBouncer connections while online Getting ready How to do it... How it works... There's more... See also Enhancing PgBouncer authentication Getting ready How to do it... How it works... There's more... See also Chapter 5: Troubleshooting Performing triage Getting ready How to do it... How it works... There's more... Installing common statistics packages How to do it... How it works... Evaluating the current disk performance with iostat Getting ready How to do it... How it works... There's more... See also Tracking I/O-heavy processes with iotop Getting ready How to do it... How it works... There's more... See also Viewing past performance with sar Getting ready How to do it... How it works... There's more... See also Correlating performance with dstat Getting ready How to do it... How it works... See also Interpreting /proc/meminfo Getting ready How to do it... How it works... There's more... See also Examining /proc/net/bonding/bond0 Getting ready How to do it... How it works... Checking the pg_stat_activity view Getting ready How to do it... How it works... There's more... See also Checking the pg_stat_statements view Getting ready How to do it... How it works... There's more... Reset the status Catch more queries See also Deciphering database locks Getting ready How to do it... How it works... There's more... See also Debugging with strace Getting ready How to do it... How it works... There's more... See also Logging checkpoints properly Getting ready How to do it... How it works... There's more... See also Chapter 6: Monitoring Figuring out what to monitor Getting ready How to do it... How it works... There's more... Installing and configuring Nagios Getting ready How to do it... How it works... There's more... See also Configuring Nagios to monitor a database host Getting ready How to do it... How it works... There's more... See also Enhancing Nagios with Check_MK Getting ready How to do it... How it works... There's more... See also Getting to know check_postgres Getting ready How to do it... How it works... There's more... See also Installing and configuring Telegraf Getting ready How to do it... How it works... See also Adding a custom PostgreSQL monitor to Telegraf Getting ready How to do it... How it works... There's more... See also Installing and configuring InfluxDB Getting ready How to do it... How it works... There's more... See also Installing and configuring Grafana Getting ready How to do it... How it works... See also Building a graph in Grafana Getting ready How to do it... How it works... See also Customizing a Grafana graph Getting ready How to do it... How it works... There's more... See also Using InfluxDB tags in Grafana Getting ready How to do it... How it works... There's more... See also Chapter 7: PostgreSQL Replication Deciding what to copy Getting ready How to do it... How it works... Securing the WAL stream Getting ready How to do it... How it works... There's more... See also Setting up a hot standby Getting ready How to do it... How it works... See also Upgrading to asynchronous replication Getting ready How to do it... How it works... There's more... Cascading replication Using replication slots Viewing replication status on a replica See also Bulletproofing with synchronous replication Getting ready How to do it... How it works... There's more... Being less strict Being more strict Enabling extreme durability See also Faking replication with pg_receivewal Getting ready How to do it... How it works... There's more... See also Setting up Slony Getting ready How to do it... How it works... There's more... See also Copying a few tables with Slony Getting ready How to do it... How it works... There's more... See also Setting up Bucardo Getting ready How to do it... How it works... See also Copying a few tables with Bucardo Getting ready How to do it... How it works... See also Setting up pglogical Getting ready How to do it... How it works... See also Copying a few tables with pglogical Getting ready How to do it... How it works... There's more... See also Copying a few tables with native logical replication Getting ready How to do it... How it works... There's more... No sequences Tool integration Keys required for UPDATE and DELETE See also Chapter 8: Backup Management Deciding when to use third-party tools Getting ready How to do it... How it works... Installing and configuring Barman Getting ready How to do it... How it works... See also Backing up a database with Barman Getting ready How to do it... How it works... There's more... Retention policies Parallel backup See also Restoring a database with Barman Getting ready How to do it... How it works... There's more... Streaming replicas RPO zero See also Obtaining Barman diagnostics and information Getting ready How to do it... How it works... Sending Barman backups to a remote location Getting ready How to do it... How it works... Installing and configuring pgBackRest Getting ready How to do it... How it works... There's more... See also Backing up a database with pgBackRest Getting ready How to do it... How it works... There's more... More backup types Use full regularly See also Restoring a database with pgBackRest Getting ready How to do it... How it works... There's more... Installing and configuring WAL-E Getting ready How to do it... How it works... See also Managing WAL files with WAL-E Getting ready How to do it... How it works... There's more... Recovering WAL files Backing up the database Removing old files See also Chapter 9: High Availability with repmgr Preparing systems for repmgr Getting ready How to do it... How it works... Installing and configuring repmgr Getting ready How to do it... How it works... See also Cloning a database with repmgr Getting ready How to do it... How it works... There's more... Viewing the cluster Cloning from Barman See also Incorporating a repmgr witness Getting ready How to do it... How it works... See also Performing a managed failover Getting ready How to do it... How it works... There's more... Always watching Testing the waters Useful shortcuts See also Customizing the failover process Getting ready How to do it... How it works... There's more... See also Using an outage to test availability Getting ready How to do it... How it works... There's more... Returning a node to the cluster Getting ready How to do it... How it works... There's more... See also Integrating primary fencing Getting ready How to do it... How it works... There's more... See also Performing online maintenance and upgrades Getting ready How to do it... How it works... There's more... See also Chapter 10: High Availability with Patroni Understanding more about Patroni and its components Why HAProxy? Why etcd? Why Patroni? The stack Preparing systems for the stack Getting ready How to do it... How it works... See also Installing and configuring etcd Getting ready How to do it... How it works... There's more... See also Installing and configuring Patroni Getting ready How to do it... How it works... There's more... Cluster status Better service management See also Installing and configuring HAProxy Getting ready How to do it... How it works... See also Performing a managed switchover Getting ready How to do it... How it works... There's more... Using an outage to test availability Getting ready How to do it... How it works... There's more... Returning a node to the cluster Getting ready How to do it... How it works... There's more... Adding additional nodes to the mix Getting ready How to do it... How it works... There's more... See also Replacing etcd with ZooKeeper Getting ready How to do it... How it works... There's more... See also Replacing etcd with Consul Getting ready How to do it... How it works... There's more... See also Upgrading while staying online Getting ready How to do it... How it works... There's more... Chapter 11: Low-Level Server Mirroring Understanding our chosen filesystem components Why DRBD? Why LVM? Why XFS? The stack Preparing systems for volume mirroring Getting ready How to do it... How it works... There's more... See also Getting started with the LVM Getting ready How to do it... How it works... There's more... See also Adding block-level replication Getting ready How to do it... How it works... See also Incorporating the second LVM layer Getting ready How to do it... How it works... There's more... See also Verifying a DRBD filesystem Getting ready How to do it... How it works... There's more... See also Correcting a DRBD split brain Getting ready How to do it... How it works... See also Formatting an XFS filesystem Getting ready How to do it... How it works... See also Tweaking XFS performance Getting ready How to do it... How it works... There's more... See also Maintaining an XFS filesystem Getting ready How to do it... How it works... There's more... See also Using LVM snapshots Getting ready How to do it... How it works... See also Switching live stack systems Getting ready How to do it... How it works... There's more... Detaching a problematic node Getting ready How to do it... How it works... There's more... See also Chapter 12: High Availability via Pacemaker Before we begin... Installing the components Getting ready How to do it... How it works... There's more... See also Configuring Corosync Getting ready How to do it... How it works... See also Preparing start up services Getting ready How to do it... How it works... There's more... See also Starting with base options Getting ready How to do it... How it works... There's more... See also Adding DRBD to cluster management Getting ready How to do it... How it works... There's more... See also Adding LVM to cluster management Getting ready How to do it... How it works... There's more... Adding XFS to cluster management Getting ready How to do it... How it works... Adding PostgreSQL to cluster management Getting ready How to do it... How it works... There's more... Adding a virtual IP to proxy the cluster Getting ready How to do it... How it works... Adding an email alert Getting ready How to do it... How it works... Grouping associated resources Getting ready How to do it... How it works... Combining and ordering related actions Getting ready How to do it... How it works... Performing a managed resource migration Getting ready How to do it... How it works... There's more... Using an outage to test migration Getting ready How to do it... How it works... There's more... Chapter 13: High Availability with Multi-Master Replication Overview of multi-master Deciding whether multi-master is right for you Getting ready How to do it... How it works... See also Obtaining and installing BDR Getting ready How to do it... How it works... There's more... Starting with a single BDR node Getting ready How to do it... How it works... There's more... Creating an additional BDR node Getting ready How to do it... How it works... There's more... Testing DDL replication on each node Getting ready How to do it... How it works... There's more... Using sequences safely Getting ready How to do it... How it works... There's more... Global allocation sequences Timeshard introspection See also Configuring HAProxy for the multi-master approach Getting ready How to do it... How it works... There's more... See also Combining PgBouncer with HAProxy Getting ready How to do it... How it works... Performing a managed node switchover Getting ready How to do it... How it works... There's more... See also Improving failover speed Getting ready How to do it... How it works... Performing a major version upgrade online Getting ready How to do it... How it works... There's more... Chapter 14: Data Distribution Identifying horizontal candidates Getting ready How to do it... How it works... There's more... See also Setting up a foreign PostgreSQL server Getting ready How to do it... How it works... There's more... Altering foreign servers Dropping foreign servers See also Mapping a remote user Getting ready How to do it... How it works... There's more... See also Creating a foreign table Getting ready How to do it... How it works... There's more... Creating all tables for a foreign schema Dropping foreign tables See also Using a foreign table in a query Getting ready How to do it... How it works... There's more... Explaining strange planner decisions Improvements in PostgreSQL 9.6 Improvements in PostgreSQL 10 Improvements in PostgreSQL 11 Optimizing foreign table access Getting ready How to do it... How it works... There's more... Transforming foreign tables into local tables Getting ready How to do it... How it works... There's more... See also Creating a scalable nextval replacement Getting ready How to do it... How it works... There's more... Building a sharding API Getting ready How to do it... How it works... There's more... shard_manager extension Citus Postgres-XL BDR AutoScale See also Talking to the correct shard Getting ready How to do it... How it works... There's more... Keeping things fast by creating a cache Choosing an application data to map logical shard  Moving a shard to another server Getting ready How to do it... How it works... There's more... Chapter 15: Zero-downtime Upgrades Preparing upgrade requirements Getting ready How to do it… How it works… Remembering PgBouncer and pglogical Getting ready How to do it… How it works… There's more… See also Creating a publication set Getting ready How to do it… How it works… There's more… Handling sequences Getting ready How to do it… How it works… There's more… Bootstrapping the target cluster Getting ready How to do it… How it works… There's more… Starting the subscription Getting ready How to do it… How it works… There's more… Monitoring progress Getting ready How to do it… How it works… There's more… See also Switching targets Getting ready How to do it… How it works… There's more… Cleaning everything up Getting ready How to do it… How it works… Other Books You May Enjoy Index