PostgreSQL replication and high availability
You set up streaming and logical replication, manage synchronous replicas, replication slots and lag, and run automatic failover and drills with Patroni.
Level 401 · 16 lessons · 15 practice labs
Lessons
Streaming replication
- Replication concepts: primary, standby and WAL shipping
- Setting up streaming replication
- Replication slots and WAL build-up
- Synchronous replication and synchronous_commit
- Read queries on a hot standby and conflicts
- Monitoring lag
- Short task: set up two standby servers
Logical replication
- Publications and subscriptions
- Upgrades and data moves with logical replication
- Logical replication conflicts
- Short task: move a table to a new server live
High availability
- Manual promotion and pg_rewind
- Automatic failover with Patroni
- Connection routing: HAProxy and multi-host connection strings
- Failover drills
- Capstone: a payment database that stays up
Practice labs
- Set up a standby with streaming replication: Set up a standby with pg_basebackup and verify a new row on the primary appears there.
- Chain two standby servers: Set up cascading replication where the second standby feeds from the first standby, not the primary.
- Solve writes hanging under synchronous replication: Fix a synchronous setup where commits hang when a standby drops and compare synchronous_commit options.
- Measure lag in bytes and seconds: Calculate standby lag in bytes and seconds from pg_stat_replication.
- Clear WAL bloated by an abandoned slot: Find the disk filled by an unused replication slot and remove the slot safely.
- Stop queries being cancelled on the standby: Balance long reports cancelled on the standby by conflicts using hot_standby_feedback and delay settings.
- Publish one table and subscribe: Define the products table as a publication and get a live copy with a subscription on a second server.
- Move a table to a new server live: Move the orders table to a new server with logical replication and switch traffic with a brief cut.
- Resolve a subscription conflict: Diagnose from the log a logical replication stopped by a row inserted on the subscriber and resume it.
- Promote a standby by hand: Promote the standby with pg_promote when the primary fails and point the app at the new address.
- Rejoin the old primary with pg_rewind: Make the diverged old primary a standby of the new primary with pg_rewind without rebuilding it.
- Run an automatic failover drill: Shut down the primary in a managed three-node cluster and measure failover time and the multi-host connection's behaviour.
- Write a multi-host connection string: Make the app always connect to the writable server with target_session_attrs.
- Point a load balancer at the primary: Set up a load balancer with health checks that sends writes to the primary and reads to standbys.
- Build a downtime budget for a payment database: Run a test plan that verifies failover, lag and data-loss targets with measurements for Karaca Finans's payment database.
About this course
You set up streaming and logical replication, manage synchronous replicas, replication slots and lag, and run automatic failover and drills with Patroni.
What you will learn
- Streaming replication
- Logical replication
- High availability
Prerequisites
You should have practised the topics of the same path up to level 301.
Lab environment
Lessons run in your own Kali Linux terminal in the browser, connected to a private lab network. Nothing to install.
Frequently asked questions
- Is this course right for my level?
- The course is at level 401; it assumes you know the topics of the lower-level courses.
- Is there anything to install?
- No. Every lesson runs in your own lab environment that opens in the browser.
- How do lessons work?
- Each lesson has 8 to 10 steps and a short quiz. Early steps show the command to type, later steps give less help; if two attempts fail, the solution opens.
- Can I try it for free?
- Yes. The first three lessons of every course are free.