Skip Navigation Links

Course Length:
5 Days
Course Description:
This course covers administration, performance, advanced development, and replication—into a continuous sequence. Hands-on workshop exercises are provided for the performance and replication classes. Students are encouraged to ask questions about features the instructor can demonstrate at any time. The examples target PostgreSQL versions 9.0 and 9.1. Instructors for this class know PostgreSQL works at a deep level, including the common challenges when the database is deployed onto real-world production systems at scale. The ideal student will not just want to know what to do with PostgreSQL, they'll want to understand how the database works and makes its decisions.
Who Should Attend:
This course is for database administrators, systems administrators, or developers with working knowledge of database usage.
Benefits of Attendance:
Upon completion of this course, students will be able to:
  • Start and stop a PostgreSQL server
  • Change the configuration of the database
  • Create users and restrict what they are able to do
  • Execute SQL queries on the server
  • Monitor database activity and statistics
  • Understand what background maintenance the database does automatically
  • Setup a test environment with sample data
  • Look for problems in the server's log files
  • Find the free and commercial resources for PostgreSQL issues
  • Setup the server postgresql.conf for a production system
  • Look for performance issues caused by locking
  • Recognize unique PostgreSQL design features that developers can utilize
  • Read the output of a query plan
  • Adjust how the server executes joins between tables
  • Monitor which queries are using up the most server resources
  • Add and remove table indexes based on a production workload
  • 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
Prerequisites:
Students must have a working knowledge of SQL. Using the command line interface to a Linux system and some basic UNIX shell programming experience is needed to complete all of the replication workshop exercises.
Course Outline:
  • Database Administration
    1. Connecting & Troubleshooting
    2. PgAdmin III Overview
    3. Server Configuration
    4. Database Administration
    5. User Administration
    6. Understanding Database Object Types
    7. Permissions & Security
    8. Running SQL
    9. Monitoring
    10. MVCC and VACUUM
    11. Maintenance
    12. Data Migration & Test Environments
    13. Server Logs
    14. Help & Support
  • Advanced Development & Performance
    1. Server Architecture & Caching
    2. Tuning Parameters
    3. MVCC, VACUUM and HOT
    4. Locking & Concurrency
    5. Database design alternatives
    6. Advanced Development Summary
    7. Reading EXPLAINs
    8. Workload Analysis, Benchmarking and Profiling
    9. Join Planning
    10. Indexes
    11. SQL Performance Tips & Tricks
  • PostgreSQL 9 Replication & Recovery
    1. pg_dump and restore options
    2. PITR Backup and Recovery
    3. Replication Concepts
    4. High Availability options
    5. Streaming Replication
    6. Managing clusters using repmgr
    7. Hot Standby
    8. Synchronous Replication
    9. File based Log Shipping
    10. Introduction to Slony & Londiste
    11. Connection Pooling
    12. Upgrading PostgreSQL
    13. Sharding and Scale Out