Outline updated November 2026

Foundation21 hours delivered over 3-5 daysLive, online or on-site

Request this course

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 initdb defaults

Configuration

  • postgresql.conf and pg_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_repack and 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

Private team training

Bring this course to your team

Tell us about your team and we will tailor this outline to your tools, stack, and schedule. Courses can also be combined into a longer program.

  1. Tell us what you needYour tools, stack, team size, and timeline.
  2. We send a proposalA course or program, an outline adjusted to your team, and possible dates.
  3. Your team trains liveOnline or on-site, with courseware for every student.

Prefer to talk? Call +1 434-509-6890 or email help@cloudcontraptions.com.

We reply within 1 business day.

Training partner?