PostgreSQL High Availability Architecture with Patroni

• Learn about PostgreSQL transaction log, streaming replication, and backups.
• Understand the concept as a whole, as well as the individual solutions required to run a cluster.
• Create highly available PostgreSQL clusters with Patroni and etcd.
• 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

This course is aimed at database administrators, DevOps engineers and IT professionals who want to implement and operate high availability solutions for PostgreSQL.

Prerequisites and additional requirements

The participant should:

• know basic SQL
• have basic Linux skills (basic commands, ability to use 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 this course will give you

Skills & Knowledge

• Demonstrate technical understanding of the Patroni architecture and its components: Deep understanding of how Patroni, etcd, PostgreSQL work and how they 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 in the event of 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: Knowledge of 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.

Program 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

• Participants will get access to a dedicated lab environment with dedicated virtual machines running some Linux distribution.
• Upon completion of the course, participants will receive a PDF of the slide deck that was used during the training sessions
• Participants can download and use all scripts and configuration files present on their dedicated virtual machines during training
• Upon completion of the course, participants will receive a complimentry copy of the most recent edition of Hans-Jürgens Schönig’s book Mastering PostgreSQL