Duration
21 hours (usually 3 days including breaks)
Requirements
- Basic knowledge in database administration
- Familiarity with SQL language
Audience
- Database administrators
- System administrators
Overview
PostgreSQL (also known as Postgres) is an open source relational database management system or RDBMS. It is built to handle large data workloads while maintaining stability, reliability, and optimal performance.
This instructor-led, live training (online or onsite) is aimed at database admins and sysadmins who wish to use PostgreSQL to manage large-scale data while optimizing the database systems for high availability and performance.
By the end of this training, participants will be able to:
- Install and configure PostgreSQL.
- Understand the features and architecture of PostgreSQL.
- Learn how to perform database operations with SQL.
- Perform database backup and recovery to prevent data loss.
- Configure the server for high availability.
- Learn the tools and techniques for high performance tuning.
- Explore the external tools for monitoring and observability.
Format of the Course
- Interactive lecture and discussion.
- Lots of exercises and practice.
- Hands-on implementation in a live-lab environment.
Course Customization Options
- To request a customized training for this course, please contact us to arrange.
Course Outline
Introduction
- Overview of PostgreSQL features and architecture
- Basics of database administration
Getting Started
- Installing PostgreSQL
- Configuring a pgAdmin 4 client
- Creating a database user
- Creating a database
Database Operations
- SQL syntax, functions, and operators
- Data definition and data types
- User-defined data types
- Table schemas
- Creating and modifying tables
- Manipulating data in tables
- Querying and filtering data
- Exploring constraints
- Conversion functions
- Joining multiple tables
- Combining queries
- Indexes and full-text search
- Concurrency control
- Regular expressions
Database Server Administration
- Security and encryption
- Access roles and permissions
- Backup and restore
- Upgrading PostgreSQL
- High availability
- Load balancing and replication
- Statistics and monitoring
High Performance Tuning
- Resource management
- Monitoring query performance
- Parameter tuning
- Index optimization
- Query optimization
- Scalability and partitioning
External Tools for Monitoring and Observability
- pgAdmin
- pgBadger
- pgbench
- node-exporter Prometheus, Grafana
- Zabbix
Troubleshooting
Summary and Next Steps