PostgreSQL for Administrators
Duration
3 days
Description
This comprehensive course is designed for computer programming professionals seeking to master PostgreSQL administration. It covers everything from the basics of PostgreSQL architecture to advanced topics like replication, high availability, and deploying PostgreSQL in the cloud. The course provides practical, hands-on experience with installing, configuring, and securing PostgreSQL, as well as performance tuning and routine maintenance. It also delves into advanced topics like containerizing PostgreSQL and deploying it on major cloud platforms. By the end of the course, participants will have a deep understanding of PostgreSQL administration and be equipped with the skills to manage PostgreSQL in a variety of environments.
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.
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.
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.
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
- Conclusion
- Summary of Key Concepts
- Q&A
- Further Resources and Next Steps