Skip to main content

PostgreSQL for Administrators

Executive Summary

This course teaches developers how to administer PostgreSQL databases in any environment. Learn to install, configure, and secure PostgreSQL, optimize performance, implement replication, and ensure high availability. You'll also explore backup strategies, container deployment with Docker and Kubernetes, and cloud-based hosting on AWS, Azure, and Google Cloud. Ideal for professionals managing PostgreSQL in production.

Programming Professionals Collaborating

Description

The course is a comprehensive, hands-on course designed for programming professionals who want to master the administration of PostgreSQL databases. Covering a full spectrum of topics—from foundational PostgreSQL architecture to advanced areas such as replication, high availability, and cloud deployment—this course equips participants with the skills necessary to install, configure, secure, and maintain PostgreSQL in real-world environments. Learners will engage in practical exercises involving performance tuning, routine maintenance, and containerization strategies, as well as explore deployment scenarios on major cloud platforms. By the end of the course, participants will be fully prepared to manage PostgreSQL effectively across diverse infrastructures.

Objectives

  • Gain a comprehensive understanding of PostgreSQL, its architecture, and key features.
  • Master the installation, setup, and configuration of PostgreSQL, including understanding configuration files and settings.
  • Understand and implement security measures in PostgreSQL, including user authentication, role management, and data encryption.
  • Learn how to tune PostgreSQL for performance, including monitoring and optimizing queries, managing resource consumption, and using indexes effectively.
  • Master the techniques for backup and restore in PostgreSQL, including SQL Dump, File System Level Backup, and Continuous Archiving.
  • Understand and implement replication and high availability strategies in PostgreSQL, including configuring streaming replication and managing failover procedures.
  • Learn how to carry out routine maintenance and monitoring tasks in PostgreSQL, including vacuuming, reindexing, and log file management.
  • Understand how to deploy and manage PostgreSQL in containerized and cloud environments, including Docker, Kubernetes, and major cloud platforms like AWS, Google Cloud, and Azure.

Duration

21 hours of intensive training with live instruction delivered over three to five days to accommodate varied scheduling needs. 

Request Information

Course Outline

Introduction to PostgreSQL Administration
  • Overview of PostgreSQL
  • PostgreSQL Architecture
  • Key Features of PostgreSQL
Installation and Setup
  • Installing PostgreSQL from Binaries
  • Installing PostgreSQL from Source
  • Post-Installation Setup
  • Creating and Managing Database Clusters
Configuration
  • Understanding Configuration Files (postgresql.conf, pg_hba.conf)
  • Setting Parameters
  • File Locations
  • Connection and Authentication Configuration
  • Resource Consumption Settings
  • Write-Ahead Log (WAL) Configuration
  • Query Planning and Execution Configuration
Security
  • User Authentication Methods (MD5, SCRAM, GSSAPI, etc.)
  • Role Management
  • Granting and Revoking Privileges
  • Secure TCP/IP Connections with SSL and SSH
  • Data Encryption
  • Implementing Row-Level Security
Performance Tuning
  • Monitoring and Optimizing Queries with EXPLAIN
  • Using Indexes Effectively
  • Understanding and Managing Bloat
  • Configuring and Using AutoVacuum
  • Managing Resource Consumption
  • Tuning Memory and Disk I/O
Backup and Restore
  • SQL Dump
  • File System Level Backup
  • Continuous Archiving and Point-in-Time Recovery (PITR)
  • Using pg_basebackup
  • Restoring from Backup
Replication and High Availability
  • Introduction to Replication
  • Configuring Streaming Replication
  • Synchronous vs. Asynchronous Replication
  • Configuring Hot Standby
  • Using Logical Replication
  • Failover and Switchover Procedures
Routine Maintenance
  • Regular Vacuuming and Analyzing
  • Reindexing
  • Log File Management
  • Monitoring Disk Usage
  • Managing Long-Running Queries
Monitoring and Logging
  • Using Built-in Monitoring Tools
  • Configuring and Using pg_stat_statements
  • Viewing and Analyzing Logs
  • Setting up Alerts and Notifications
  • Using External Monitoring Tools
Advanced Topics
  • Using Partitioning for Large Tables
  • Full-Text Search Configuration
  • Understanding and Using Table and Index Storage Parameters
  • Configuring and Using Foreign Data Wrappers
  • Extending PostgreSQL with Extensions and Plugins
Troubleshooting and Best Practices
  • Common Issues and Solutions
  • Best Practices for Database Administration
  • Regular Health Checks
  • Disaster Recovery Planning
  • Keeping PostgreSQL Updated
Containerizing PostgreSQL
  • Running PostgreSQL in Docker Containers
  • Managing PostgreSQL in Kubernetes
  • Using Helm Charts for PostgreSQL Deployment
  • Monitoring PostgreSQL in Containerized Environments
PostgreSQL in the Cloud
  • Deploying PostgreSQL on AWS RDS
  • Deploying PostgreSQL on Google Cloud SQL
  • Deploying PostgreSQL on Azure Database for PostgreSQL
  • Managing PostgreSQL in Cloud Environments

Prerequisites

  • No prior experience with PostgreSQL is necessary.
  • Students should have a basic understanding of SQL and relational databases.

Training Materials

All students receive comprehensive courseware covering all topics in the course. Courseware is distributed via GitHub in the form of documentation and extensive code samples. Students practice the topics covered through challenging hands-on lab exercises. Students will need a free, personal GitHub account to access the courseware. Students are provided a cloud-based environment for all demonstrations and lab exercises.