Unlock the full potential of PostgreSQL with this comprehensive three-day, instructor-led course. Designed for database administrators and IT professionals, this hands-on training equips you with the skills to install, configure, manage, and optimize PostgreSQL databases across multi-platform environments. Learn to implement robust backup and recovery strategies, enhance performance and scalability, and ensure efficient routine maintenance. Whether you're transitioning to PostgreSQL or expanding your expertise, this course provides the practical knowledge needed to thrive in today’s database landscape. PostgreSQL Administration: Hands-On Training Benefits In this course, you will: Gain hands-on experience installing and configuring PostgreSQL on diverse platforms, ensuring you can adapt to any environment. Master the skills to efficiently manage database clusters, schemas, roles, and tablespaces, enabling seamless organization and access control. Learn to implement reliable backup, recovery, and disaster recovery solutions to safeguard critical data. Discover techniques to optimize PostgreSQL databases for peak performance, even as workloads grow. Acquire tools and strategies for routine database upkeep and server performance monitoring, ensuring long-term stability and efficiency. Prerequisites Basic Knowledge of RDBMS Concepts: Familiarity with relational database principles, SQL queries, and common database objects like tables, indexes, and views. Experience with Command-Line Tools: Comfort with using command-line interfaces for database administration tasks. Understanding of Operating Systems: Basic experience with Linux/UNIX and Windows operating systems, including file systems and user account management. Technical Background: Prior experience as a database administrator or similar technical role is beneficial but not required. Exam Information Learning Tree exam included. PostgreSQL Administration Training Outline Learning Objectives Day 1: Foundations of PostgreSQL Administration Chapter 1: Course Introduction Overview of course materials and agenda. 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 PostgreSQL instance overview. Data processing, shared memory, and directory structures. Configuration files and WAL architecture. Chapter 4: Client Applications and Database Setup Introduction to client tools (psql, pgAdmin). Database creation, configuration, and management. Troubleshooting common client connection issues. Day 2: Advanced Administration and Security Chapter 5: PostgreSQL Schemas Schema concepts and management. Information and system catalog schemas. Chapter 6: Role Management Role creation, attributes, and membership. Troubleshooting role access issues. Chapter 7: Fine-Grained Access Control Grant and revoke commands. Managing column-level permissions and schema privileges. Chapter 8: Index Creation and Management Index types and best practices. Partial, unique, and multi-column indexes. Reindexing and maintaining indexes efficiently. Chapter 9: Transactions and Concurrency Transaction properties and isolation levels. Multi-version concurrency control. 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 Methods for database backup and restore. pg_dump, pg_restore, and point-in-time recovery. Chapter 13: Query Optimisation and Performance Tuning Using EXPLAIN and ANALYZE to optimise queries. Index and query planner statistics. Memory settings and performance tuning parameters. Chapter 14: Routine Maintenance and Monitoring Configuring autovacuum and routine cleanup tasks. Monitoring activities and statistics. Efficiently populating databases.
