PostgreSQL 9 Replication & Recovery Concepts
The class explains how to use the built-in replication in PostgreSQL 9, along with the fundamentals to understand how it works. High availability and multi-node scaling architectures are both explained. The course workshop focuses on setting up a pair of PostgreSQL on Linux databases to have the same content, followed by breaking the replication and using them independently.
Course Length: 2 days
Who Should Attend:
Database administrators, systems administrators, or developers with working knowledge of database usage.
Prerequisites:
Students must have a working knowledge of SQL and know the basics of operating a PostgreSQL server. The “PostgreSQL 9 Database Administration” course is recommended before this one. Using the command line interface to a Linux system and some basic UNIX shell programming experience is needed to complete all of the workshop exercises.
Course Outline:
pg_dump and restore options
PITR Backup and Recovery
Replication Concepts
High Availability options
Streaming Replication
Managing clusters using repmgr
Hot Standby
Synchronous Replication
File based Log Shipping
Introduction to Slony & Londiste
Connection Pooling
Upgrading PostgreSQL
Sharding and Scale Out
Benefits of Attendance:
Upon completion of this course, students will be able to:
Create and restore a snapshot backup of a PostgreSQL server
Deploy a high-availability standby replica
Understand the replication scripting hooks used for customizing behavior
Monitor whether a replica is keeping up with the master's traffic
Switch to a standby system when the master fails
Automate server management using the repmgr tool
Determine which queries might be answered from a read-only replica
Outline popular architectures for scaling a PostgreSQL server across multiple nodes
