Duration
28 hours (usually 4 days including breaks)
Requirements
A working knowledge of SQL and C
Basic system administration (Unix/Linux or Windows)
Overview
This course handles the administration and performance tuning of PostgreSQL databases. Attendees will learn the use of specialised PostgreSQL (AKA Postgres) modules such as replication, connection pooling and full text searching.
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
- Type Conversion
- Indexes
- Full Text Search
- Concurrency Control
- Performance Tips
Server Administration
- Installation from Source Code
- Installation from Source Code on Windows
- Server Setup and Operation
- Server Configuration
- Client Authentication
- Database Roles
- Managing Databases
- Localization
- Routine Database Maintenance Tasks
- Backup and Restore
- High Availability, Load Balancing, and Replication
- Recovery Configuration
- Monitoring Database Activity
- Monitoring Disk Usage
- Reliability and the Write-Ahead Log
- Regression Tests
Client Interfaces
- libpq – C Library
- Large Objects
- ECPG – Embedded SQL in C
- The Information Schema
Server Programming
- Extending SQL
- Triggers
- The Rule System
- Procedural Languages
- PL/pgSQL – SQL Procedural Language
- PL/Tcl – Tcl Procedural Language
- PL/Perl – Perl Procedural Language
- PL/Python – Python Procedural Language
- Server Programming Interface
Internals
- Overview of PostgreSQL Internals
- System Catalogs
- Frontend/Backend Protocol
- PostgreSQL Coding Conventions
- Native Language Support
- Writing A Procedural Language Handler
- Writing A Foreign Data Wrapper
- Genetic Query Optimizer
- Index Access Method Interface Definition
- GiST Indexes
- GIN Indexes
- Database Physical Storage
- BKI Backend Interface
- How the Planner Uses Statistics
Duration
14 hours (usually 2 days including breaks)
Requirements
A basic working knowledge of SQL
Overview
This course covers the administration and performance tuning of PostgreSQL databases.
Target audience includes system administrators and database architects. Attendees will learn the use of specialised PostgreSQL (AKA Postgres) modules such as replication, connection pooling and full text searching.
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
