Course Code:
postgresbspk
Duration:
14 hours
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
- Data Definition
- Data Manipulation
- Queries
- Data Types
- Functions and Operators
- Foreign Data Wrappers
- Type Conversion
- Indexes
- Triggers
- Full Text Search
- The Information Schema
- Concurrency Control
- Performance Tips
- How the Planner Uses Statistics
Server Administration
- Source Code vs Distribution-Provided Packages (RHEL / Ubuntu, both distro supported and official package repos from PostgreSQL are in use on our systems)
- 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 (pgdump and pg_basebackup in use currently)
- Recovery Configuration
- Monitoring Database Activity
- Monitoring Disk Usage
- Reliability and the Write-Ahead Log
- Installation of Third-Party Server Extensions
High Availability, Load Balancing, and Replication
- Brewer's CAP Theorem
- Synchronous vs Asynchronous Replication (Both in use)
- Log Shipping (Warm Standby) (needs implementation)
- Streaming Master-Slave Replication (Hot Standby) (In use)
- Trigger-Based Master-Slave Replication with Slony
- Connection Pooling and Synchronous Replication with Pgpool (Considering its use)
- Replacing a Failed Server
- PGBounce – load balancer