Day 1: Foundations of PostgreSQL Administration
Chapter 1: Course Introduction
- Overview of course structure and objectives
- Introductions and expectations
Chapter 2: Installation and Configuration
- PostgreSQL versioning
- Installation on Linux/UNIX platforms
- Basic server configuration
- Post-installation setup and account management
Chapter 3: PostgreSQL Architecture
- Instance overview
- Data processing, shared memory, and directory structures
- Configuration files and WAL architecture
Chapter 4: Client Applications and Database Setup
- Introduction to tools (psql, pgAdmin)
- Database creation, configuration, and management
- Troubleshooting common connection issues
Day 2: Advanced Administration and Security
Chapter 5: PostgreSQL Schemas
- Schema concepts and organisation
- System and catalog schemas
Chapter 6: Role Management
- Creating roles, attributes, and memberships
- Troubleshooting access issues
Chapter 7: Fine-Grained Access Control
- Granting and revoking permissions
- Managing schema privileges and column-level access
Chapter 8: Index Creation and Management
- Types of indexes and best practices
- Partial, unique, and multi-column indexes
- Reindexing and index maintenance
Chapter 9: Transactions and Concurrency
- Transaction properties and isolation levels
- Multi-version concurrency control (MVCC)
- Deadlock detection and advisory locks
Day 3: Optimisation, Maintenance, and Recovery
Chapter 10: Database Structure and Tablespaces
- Physical storage layout
- Creating and managing tablespaces
Chapter 11: Logging and Extensions
- Configuring PostgreSQL logging
- Installing and managing extensions
Chapter 12: Backup and Recovery
- Backup and restore methods
- pg_dump, pg_restore, and point-in-time recovery
Chapter 13: Query Optimisation and Performance Tuning
- Using EXPLAIN and ANALYSE
- Index and query planner statistics
- Memory settings and performance tuning parameters
Chapter 14: Routine Maintenance and Monitoring
- Configuring autovacuum and maintenance tasks
- Monitoring activity and performance metrics
- Efficient database population and upkeep