Course Outline

What is PostgreSQL?

  • A Brief History of PostgreSQL
  • Conventions
  • Further Information
  • Bug Reporting Guidelines

Introduction to PostgreSQL

  • Installation and Creating Database
  • The SQL Language
  • Advanced Features

The SQL Language

  • SQL Syntax
  • Data Definition
  • Data Manipulation
  • Queries
  • Data Types
  • Functions and Operators
  • Foreign Data Wrappers
  • Type Conversion
  • Indexes
  • Triggers
  • Full Text Search
  • The Information Schema
  • PL/pgSQL - SQL Procedural Language
  • Concurrency Control
  • Performance Tips
  • How the Planner Uses Statistics

Server Administration

  • Source Code vs Distribution-Provided Packages
  • Installation from Source Code
  • Installation from Source Code on Windows
  • Deployment of Binaries Obtained by Compiling Source Code
  • Server Setup, Operation
  • Database Physical Storage
  • Filesystem Durability Requirements and Required Mount Options
  • Server Configuration
  • Special Considerations for Container-Based Deployments
  • Client Authentication
  • Database Roles
  • Managing Databases
  • Localization
  • Routine Database Maintenance Tasks
  • Backup and Restore
  • Recovery Configuration
  • Monitoring Database Activity
  • Monitoring Disk Usage
  • Reliability and the Write-Ahead Log
  • Regression Tests
  • Installation of Third-Party Server Extensions

High Availability, Load Balancing, and Replication

  • Brewer's CAP Theorem
  • Synchronous vs Asynchronous Replication
  • Log Shipping (Warm Standby)
  • Streaming Master-Slave Replication (Hot Standby)
  • Trigger-Based Master-Slave Replication with Slony
  • Trigger-Based Multi-Master Replication with Bucardo
  • Connection Pooling and Synchronous Replication with Pgpool
  • Failover Configurations using DRBD
  • Replacing a Failed Server
  • Recovering from Network Partition

Requirements

A basic working knowledge of SQL

 14 Hours

Testimonials (7)