PostgreSQL
Administration
Overview
Course Objective
By the end of this course, participants will be able to:
Understand the architecture and components of PostgreSQL.
Install and configure PostgreSQL on various platforms.
Perform routine administrative tasks such as backup, restore, and monitoring.
Implement replication, high availability, and failover strategies.
Optimize database performance and manage concurrency.
Utilize advanced features like full-text search, foreign data wrappers, and procedural languages.
Who Should Attend
This course is designed for:
System Administrators responsible for managing PostgreSQL environments.
Database Architects seeking to implement robust and scalable database solutions using PostgreSQL.
Prerequisites
Training Calendar
Intake
Duration
Program Fees
Module
Module 1 - What is PostgreSQL?
• A Brief History of PostgreSQL
• Conventions
• Further Information
• Bug Reporting Guidelines
Module 2 - Introduction to PostgreSQL
• Installation and Creating Database
• The SQL Language
• Advanced Features
Module 3 - 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
Module 4 - 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
Module 5 - 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
FAQs
General Questions:
Q: What is the PostgreSQL – Administration course about?
A: This 2-day course provides system administrators and database architects with the knowledge and practical skills required to manage, tune, and optimize PostgreSQL databases. It covers database installation, configuration, performance tuning, replication, high availability, and advanced PostgreSQL features with hands-on experience.
Q: Who should attend this course?
A: This course is ideal for system administrators and database architects who want to strengthen their expertise in PostgreSQL database administration and performance optimization.
Q: What are the prerequisites for this course?
A: Participants should have a basic working knowledge of SQL to fully benefit from the course content and exercises.
Q: How long is the course?
A: The course duration is 2 days.
Q: What key topics are covered in this course?
A: Topics include: Introduction and history of PostgreSQL, installation and configuration, SQL language essentials and advanced features, server setup and administration, performance tuning and concurrency control, backup, restore, and recovery, replication, high availability, and failover strategies, full-text search and procedural language (PL/pgSQL), and monitoring and maintenance tasks.
Q: Will I receive a certification after completing the course?
A: While this course equips participants with the necessary skills for PostgreSQL administration, it does not include a certification exam. A certificate of completion may be provided by the training provider.
Program Content & Skills:
Q: What skills will I gain from the PostgreSQL – Administration course?
A: You will gain hands-on skills in installing, configuring, and administering PostgreSQL databases, performing backup and recovery, monitoring database activity, managing replication and high availability, tuning performance, and using advanced PostgreSQL features like full-text search and procedural languages.
Q: Will I learn how to optimize PostgreSQL performance?
A: Yes, the course covers performance tips, statistics usage by the planner, concurrency control, indexing strategies, and other techniques to optimize PostgreSQL performance in real-world environments.
Q: Can I learn how to set up PostgreSQL replication?
A: Yes, the course includes detailed coverage of replication methods such as log shipping, streaming replication, trigger-based replication with Slony and Bucardo, and failover setups using tools like DRBD and Pgpool.
Q: Is security management included in the course?
A: Yes, the course includes client authentication, role management, and configuration best practices to ensure secure access and operation of PostgreSQL databases.
Q: Will I be able to extend PostgreSQL after the course?
A: Yes, you will learn to use PL/pgSQL for writing functions and procedures, install third-party extensions, and work with advanced features such as foreign data wrappers and triggers to extend PostgreSQL’s capabilities.
Submit your interest today !
