Lesson 1: Overview of PostgreSQL High Availability
- Why HA?
- HA Methods for PostgreSQL
- Compare: Patroni vs Repmgr vs Pacemaker
- System overview architecture
Lesson 2: Streaming Replication in PostgreSQL
- Streaming Replication mechanism
- WAL (Write-Ahead Logging)
- Synchronous vs Asynchronous Replication
- Replication Slots
- Lab: Simple replication setup (Primary-Standby)
Lesson 3: Introducing Patroni and etcd
- What is Patroni? How it works
- DCS (Distributed Configuration Store) - etcd/Consul/ZooKeeper
- Consensus algorithm (Raft)
- Leader election & Failover mechanism_
- _Split-brain problem and how to solve_
Lesson 4: Prepare the infrastructure_
- Part requirements hard & software
- Network and firewall configuration
- Setup 3 VMs/Servers (VirtualBox/VMware/Cloud)
- SSH key-based authentication
- Time synchronization (NTP/chrony)
Lesson 5: Installing PostgreSQL_
- Install PostgreSQL from package repository
- Install from source (optional)
- Basic postgresql.conf configuration
- Understanding pg_hba.conf_
- Lab: Installing PostgreSQL on 3 nodes
Lesson 6: Install and configure etcd cluster_
- Download and install etcd_
- Configuration etcd cluster 3 nodes
- Create systemd service
- Check health of etcd cluster_
- etcdctl basic commands_
- Lab: Setup etcd cluster completely edit
Lesson 7: Install Patroni
- Install Python dependencies_
- Install Patroni via pip_
- Patroni.yml file structure
- Create systemd service for Patroni_
- Lab: Install Patroni on 3 nodes
Lesson 8: Configuring Patroni in detail
- Analyze the patroni.yml file each time section
- Bootstrap configuration
- PostgreSQL parameters tuning
- Authentication setup_
- Tags and constraints_
Lesson 9: Bootstrap PostgreSQL Cluster_
- Start Patroni times head
- Automatic bootstrap process
- Check cluster status with patronictl_
- Troubleshooting common issues_
- Lab: Bootstrap cluster into public
Lesson 10: Replication Management
- Synchronous vs Asynchronous replicas
- Configuring synchronous_mode
- synchronous_node_count
- Monitoring replication lag_
- Lab: Synchronous configuration replication_
Lesson 11: Callback scripts and Hooks_
- on_start, on_stop, on_role_change callbacks
- Custom scripts for notifications
- Integration with monitoring systems
- Lab: Write script to send alert when failover
Lesson 12: REST API and patronictl_
- Patroni REST API endpoints
- Using patronictl commands
- Automation with API
- Lab: Cluster management via CLI and API
Module 4: Failover & Recovery (4 lessons)
Lesson 13: Automatic Failover_
- Detection mechanism error
- Leader election process
- Failover timeline
- Testing automatic failover_
- Lab: Simulate primary node failure
Lesson 14: Switchover with a plan
- When needed switchover?
- Planned switchover vs Failover
- Zero-downtime maintenance
- Lab: Implementation switchover_
Lesson 15: Recovering failed nodes_
- Rejoin failed primary
- pg_rewind mechanism
- Rebuild replica from backup
- Lab: Recovery scenarios
Lesson 16: Backup and Point-in-Time Recovery_
- pg_basebackup
- WAL archiving_
- Continuous configuration archiving
- PITR (Point-in-Time Recovery)
- Lab: Restore database from backup
Lesson 17: Monitoring Patroni Cluster
- Metrics to Track
- Prometheus Integration + Grafana
- postgres_exporter
- Alerting rules
- Lab: Setup monitoring stack
Lesson 18: Performance Tuning
- PostgreSQL configuration tuning
- Connection pooling (PgBouncer)
- Load balancing (HAProxy/pgpool)
- Read replicas scaling
- Lab: Optimize cluster performance
Lesson 19: Logging and Troubleshooting
- PostgreSQL logs
- Patroni logs
- etcd logs
- Common issues and how to fix_
- Debug techniques
Lesson 20: Security Best Practices
- SSL/TLS configuration
- Authentication methods
- Network security
- Encryption at rest
- Audit logging
- Lab: Hardening cluster security
Lesson 21: Multi-datacenter Setup
- Cross-DC replication strategies
- Asynchronous cascading replication_
- Disaster recovery planning
- Geographic load balancing
Lesson 22: Patroni with Kubernetes_
- Patroni operator
- StatefulSets_
- Persistent Volumes_
- Helm charts
- Lab: Deploy on K8s_
Lesson 23: Patroni Configuration Management
- Dynamic configuration changes
- DCS-based configuration_
- patronictl edit-config_
- Config validation
- Lab: Update config without downtime_
Lesson 24: Upgrade Strategies
- PostgreSQL major version upgrade
- Patroni version upgrade
- Zero-downtime upgrade techniques
- Rollback procedures
- Lab: Upgrade cluster from PG 14 to 15
Lesson 25: Real-world Case Studies_
- Production architecture examples
- Scaling strategies_
- Cost optimization
- Lessons learned
Lesson 26: Automation with Ansible_
- Ansible playbooks for deployment_
- _Configuration management_
- Automated testing
- CI/CD integration
- Lab: Automated deployment
Lesson 27: Disaster Recovery Drills_
- DR planning_
- Testing procedures_
- Incident response
- Post-mortem analysis
- Lab: Full DR simulation
Lesson 28: Architectural design HA
- Requirements gathering
- Architecture design document
- Capacity planning
- Cost estimation
Lesson 29: Deploy Production-ready Cluster
- Complete deployment from scratch
- Documentation_
- Runbook creation
- Knowledge transfer
- Final assessment
Prerequisites:_
- Linux version
- PostgreSQL fundamentals
- Networking basics
- Shell scripting_
Section Objectives after the course:
- Implement PostgreSQL HA cluster production-ready
- Cluster management and operation_
- Incident handling and troubleshooting
- Optimize performance and security