Outline updated November 2026
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, SREs, system administrators, and data engineers who are new to PostgreSQL and will install and run it in production, including those coming from SQL Server, Oracle, or MySQL. Teams on managed services can continue with Amazon RDS for PostgreSQL or Azure PostgreSQL Flexible Server.
Description
This course teaches PostgreSQL administration from installation to high availability. It is for DBAs and operations engineers who run their own PostgreSQL servers. Students install and configure clusters, set up SCRAM-SHA-256 authentication, and move roles off deprecated MD5 passwords. They tune performance, including the asynchronous I/O subsystem, and set up backups, point-in-time recovery, and replication. The course ends with major-version upgrades, Kubernetes operators, and managed cloud services.
Learning Outcomes
- Install PostgreSQL from packages or source and create database clusters.
- Configure postgresql.conf and pg_hba.conf for connections, WAL, and I/O.
- Secure clusters with SCRAM-SHA-256, TLS, roles, and row-level security.
- Migrate roles from deprecated MD5 passwords to SCRAM.
- Tune queries, indexes, autovacuum, and memory settings.
- Back up and restore clusters with pg_basebackup and point-in-time recovery.
- Build streaming and logical replication with planned failover and switchover.
- Upgrade major versions with pg_upgrade or logical replication.
- Monitor clusters with statistics views, pg_stat_statements, and logs.
- Run PostgreSQL on Kubernetes with operators and on managed cloud services.
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.
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. The course is taught on PostgreSQL 18, with notes for releases 15 through 17 and for changes in PostgreSQL 19.
Training Topics
Introduction to PostgreSQL Administration
- PostgreSQL overview
- PostgreSQL architecture
- Processes, memory, and storage
- Key PostgreSQL features
- Major and minor releases
- The support lifecycle
Installation and Setup
- Installing from PGDG packages
- Installing from source
- Post-installation setup
- Creating and managing database clusters
- Data checksums and
initdbdefaults
Configuration
postgresql.confandpg_hba.conf- Setting parameters
- File locations
- Connection and authentication settings
- Resource consumption settings
- Asynchronous I/O with
io_method - I/O worker settings
- Write-ahead log configuration
- Query planning settings
Security
- Password authentication with SCRAM-SHA-256
- Moving roles off MD5 passwords
- Certificate, GSSAPI, LDAP, and OAuth authentication
- Role management and predefined roles
- Granting and revoking privileges
- Secure connections with TLS
- SSH tunnels
- Data encryption
- Row-level security
Performance Tuning
- Optimizing queries with EXPLAIN
- Using indexes effectively
- Managing table bloat
- Configuring autovacuum
- Connection pooling with PgBouncer
- Managing resource consumption
- Tuning memory and disk I/O
Backup and Restore
- SQL dumps
- File system level backups
- Continuous archiving and point-in-time recovery
- Base backups with
pg_basebackup - Incremental backups
- pgBackRest and Barman
- Restoring from backup
Replication and High Availability
- Replication overview
- Configuring streaming replication
- Synchronous vs. asynchronous replication
- Configuring hot standby
- Logical replication
- Failover slots for logical replication
- Failover and switchover procedures
- High availability with Patroni
Upgrades and Version Lifecycle
- Minor release updates
- Major version upgrades with
pg_upgrade - Low-downtime upgrades with logical replication
- Moving off end-of-life releases
Routine Maintenance
- Regular vacuuming and analyzing
- Reclaiming space with VACUUM FULL
pg_repackand the REPACK command- Reindexing
- Log file management
- Monitoring disk usage
- Managing long-running queries
Monitoring and Logging
- Statistics views such as
pg_stat_activity - I/O statistics with
pg_stat_io - Using
pg_stat_statements - Viewing and analyzing logs
- Alerts and notifications
- External monitoring tools
Advanced Topics
- Partitioning large tables
- Full-text search configuration
- Table and index storage parameters
- Foreign data wrappers
- Extensions and plugins
Troubleshooting and Best Practices
- Common issues and solutions
- Administration best practices
- Regular health checks
- Disaster recovery planning
Containerizing PostgreSQL
- Running PostgreSQL in Docker
- Kubernetes operators such as CloudNativePG
- Deploying with Helm charts
- Monitoring PostgreSQL in containers
PostgreSQL in the Cloud
- Deploying on Amazon RDS
- Deploying on Google Cloud SQL
- Deploying on Azure Database for PostgreSQL
- Managing PostgreSQL in the cloud