PostgreSQL for Administrators

Class Duration

21 hours of live training delivered over 3-5 days to accommodate your scheduling needs

Student Prerequisites

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

Target Audience

DBAs, DevOps/SREs, sysadmins, and data engineers aiming to own PostgreSQL in production—install and configure clusters, harden security, tune performance, automate backups and PITR, implement replication and high availability, and operate on Docker/Kubernetes and AWS, GCP, or Azure with monitoring.

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.

Learning Outcomes

Install PostgreSQL from binaries or source, perform post-install setup, and create/manage database clusters.
Configure core settings and files (postgresql.conf, pg_hba.conf), including connections, authentication, WAL, and planner options.
Implement robust security with roles/privileges, MD5/SCRAM/GSSAPI, SSL/SSH, encryption, and row-level security.
Diagnose and optimize performance using EXPLAIN/ANALYZE, effective indexing, AutoVacuum, bloat control, and memory/disk I/O tuning.
Design and execute backup strategies with pg_dump, filesystem snapshots, pg_basebackup, and PITR; restore confidently.
Build high availability with streaming (sync/async) and logical replication, hot standby, and clean failover/switchover.
Monitor and log effectively using built-in views, pg_stat_statements, log analysis, alerts, and external monitoring tools.
Operate at scale with partitioning, storage parameters, FDWs, extensions, routine maintenance, troubleshooting, and container/cloud deployments.

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.

Software Requirements

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.

Training Topics

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