EIEIOTECHNOVATION
← Training programmes

EIEIO / DATA SCIENCE

PostgreSQL 13 Associate Certification

Course outline

3 days
Discuss this programme →

Duration

PostgreSQL 13 Associate Certification

3 days

Overview

This course provides comprehensive preparation for the PostgreSQL 13 Associate Certification exam. Participants will delve into key topics covering database administration, query optimization, backup and recovery, and advanced SQL techniques. Through a combination of theoretical understanding and practical exercises, learners will acquire the knowledge and skills necessary to successfully pass the certification exam and become proficient in managing PostgreSQL databases.

Learning Outcomes

  • Understand the fundamental concepts of PostgreSQL database management.
  • Gain proficiency in database initialization, configuration, and maintenance tasks.
  • Learn advanced SQL techniques for data manipulation and query optimization.
  • Develop skills in database monitoring, performance tuning, and troubleshooting.
  • Acquire knowledge of backup and restore procedures to ensure data integrity and availability.
  • Familiarize yourself with PostgreSQL client tools and utilities for efficient database administration.
  • Prepare for the PostgreSQL 13 Associate Certification exam through guided study of exam-relevant topics and practice exercises.

Outline

  • Database Initialization and Configuration
  • Understanding the initdb process
  • Configuring Write-Ahead Logging (WAL) for data integrity
  • Exploring WAL internals and storage page layout
  • Autovacuum and Routine Maintenance
  • Configuring autovacuum for automatic database maintenance
  • Performing manual VACUUM operations for optimizing table storage
  • Utilizing pg_class and pg_database for monitoring database statistics
  • Client Tools and Utilities
  • Exploring the psql command-line interface for interacting with PostgreSQL databases
  • Configuring client settings for optimal performance and usability
  • Understanding default roles and managing user roles in PostgreSQL
  • Data Manipulation and Query Optimization
  • Utilizing the COPY command for efficient data loading and unloading
  • Mastering Common Table Expressions (CTEs) for complex query operations
  • Optimizing UPDATE statements for performance enhancements
  • Monitoring and Performance Tuning
  • Implementing query planning techniques for optimizing query execution
  • Monitoring database performance using progress reporting and query statistics
  • Troubleshooting common performance issues and bottlenecks
  • Backup and Recovery
  • Implementing backup strategies using pg_basebackup and pg_dump
  • Understanding the role of WAL in backup and recovery procedures
  • Validating backups using pg_verifybackup and pg_controldata
  • Exam Preparation and Practice
  • Reviewing exam-relevant topics and practice questions
  • Simulating exam conditions through timed practice exams
  • Tips and strategies for successful exam completion

Audience

This course is designed for database administrators, system administrators, and IT professionals seeking to obtain the PostgreSQL 13 Associate Certification. It is also suitable for developers and database enthusiasts looking to deepen their understanding of PostgreSQL database management and optimization techniques. Prior experience with SQL and database management concepts is recommended.

Programme scope, delivery arrangements and suitability are confirmed during enquiry.