PostgreSQL High Availability Architecture with Patroni

• Understanding the concept and individual solutions required for the cluster
• Create highly available PostgreSQL clusters with Patroni, etcd and pgBackRest
• Manage Patroni clusters in operation and control failover processes
• Ensure that databases are operated in a highly available, fail-safe and efficient manner

Target audience

• DB Administrators
• DevOps Engineers
• IT Professionals

Prerequisites 

• Basic knowledge of SQL
• Basic Linux skills (e.g., using commands and a text editor)
• In addition the following knowledge is essential to be able to comprehend the content of the course „Administration and Performance Tuning"

What you will learn

• Demonstrate technical understanding of the Patroni architecture and its components:Deep understanding of how Patroni, etcd, PostgreSQL operate and interact to ensure high availability.
• Become experienced in setting up and operating a Patroni cluster: Hands-on experience setting up and managing a Patroni cluster, including configuring etcd, Patroni, and PostgreSQL.
• Manage failover and reliability:Mastering Patroni‘s automatic failover mechanisms to ensure operations, even during primary database failures.
• Monitor and maintain highly available PostgreSQL environments:Ability to monitor the health of the Patroni cluster, troubleshoot issues and perform regular maintenance to ensure a stable and resilient environment.
  
• Knowledge of Patroni and its dependencies:Understanding the architecture of Patroni and its dependencies, including etcd for cluster management and PostgreSQL for the database layer.
  
• Best practices for monitoring and maintaining HA environments:Learn best practices for monitoring and maintaining high-availability PostgreSQL environments to minimise downtime and optimise performance.
  

Course content

Module 1: Introduction to High Availability with PostgreSQL

• Importance and requirements of High Availability (HA)
• Overview of Patroni as an HA solution
• Basics of cluster architecture

Module 2: building blocks of PostgreSQL clusters

• Transaction Log (WAL) for Crash Recovery
• Backups to create replica PostgreSQL instances
• configuring and monitoring PostgreSQL streaming replication

Module 3: Patroni and its components

• How Patroni works and its architecture
• Integration of etcd as a consensus mechanism
• Cluster management, leader election, failover

Module 4: Structure and configuration of a Patroni cluster

• Step-by-step instructions for setting up a Patroni cluster
• Configuration of etcd and PostgreSQL for HA
• Bootstrap a new Patroni cluster

Module 5: Cluster Administration

• Troubleshooting and monitoring Patroni clusters
• runtime reconfiguration (PostgreSQL parameters, pg_hba)
• cluster management (switchover, reinitialisation)
• configuring failover and synchronous replication

Module 6: Tangential Topics

• client connection routing
• configuration and maintenance tasks
• updates and upgrades
• backups and restores
• security and attack vectors of the patroni and etcd stack

Optional, not included by default

Module 7: Backup and restore with pgBackRest

• Introduction to pgBackRest and its functions
• Implementation of backup strategies
• Carrying out data restores and emergency procedures

Module 8: HAProxy for load balancing and traffic management

• Introduction to HAProxy and its role in highly available architectures
• Configuration of HAProxy for PostgreSQL clusters
• Load balancing and improving performance with HAProxy

Module 9: Monitoring and maintenance of HA environments

• Best practices for monitoring Patroni clusters
• Maintenance strategies to ensure stability and performance
• Identifying and resolving problems in HA setups

Course materials and environment

• We will provide access to a dedicated tab environment and virtual machines running a Linux distribution.
• Upon completion of the course, participants will receive a PDF of the slide deck that was used during the training sessions.
• All scripts and configuration files present on the virtual machines during training will be available for download and use.