PostgreSQL on AWS Aurora is a practical, hands-on training program designed to help IT professionals develop the skills needed to deploy, administer, secure, and optimize PostgreSQL databases using Amazon Aurora. The course covers essential database administration concepts, Aurora architecture, configuration, security, backup and recovery, monitoring, performance optimization, high availability, and scalability. Participants will gain practical experience managing PostgreSQL workloads in the AWS cloud and learn how to build reliable, secure, and high-performing database environments for modern applications.
Course Overview:
PostgreSQL on AWS Aurora is a comprehensive, hands-on training program designed to help participants build practical skills in deploying, managing, securing, and optimizing PostgreSQL databases on Amazon Aurora. The course begins with an introduction to AWS, VPCs, security groups, Amazon RDS, and Aurora architecture before progressing into PostgreSQL SQL fundamentals and advanced database capabilities.
Participants will learn how to create and configure Aurora PostgreSQL DB clusters, manage connections and storage, migrate data, and work with SQL features such as joins, aggregate functions, views, triggers, transactions, foreign keys, window functions, and inheritance. The course then explores advanced database administration topics including indexing, transaction isolation, concurrency, locking, deadlocks, query execution plans, and performance optimization.
The program also provides practical knowledge of Aurora security, high availability, replication, scaling, backups, snapshots, point-in-time recovery, monitoring, and failover. Participants will work with Aurora replicas, parameter groups, Performance Insights, database activity streams, logging, execution plan management, and troubleshooting techniques.
By the end of the course, participants will be able to apply PostgreSQL and AWS Aurora best practices to build and maintain secure, reliable, scalable, and high-performing cloud database environments for modern applications.
Target Audience:
This course is designed for:
- Database Administrators (DBAs)
- PostgreSQL Database Administrators
- PostgreSQL Developers
- AWS Cloud Engineers
- Cloud Database Engineers
- Database Engineers
- DevOps Engineers
- System Administrators
- Software Developers working with PostgreSQL databases
- Data Engineers
- Cloud Architects
- Solutions Architects
- IT Professionals responsible for database deployment, performance, security, or cloud infrastructure
Pre-requisites:
Participants should have:
- Working knowledge of SQL and C programming.
- Basic understanding of database concepts and relational databases.
- Basic system administration experience with Unix/Linux or Windows.
- Familiarity with general cloud or AWS concepts is helpful but not required.
Course Duration:
- 21 hours – 3 days
Course Content:
Day 1
- Introduction to AWS
- VPC
- Security Groups
- Understanding RDS
- RDS Introduction
- Different DB Engines in RDS
- PostgreSQL in Aurora
- Architectural Fundamentals
- What is Aurora DB?
- Aurora DB Clusters
- Connection Management
- Storage and Reliability
- Security
- High Availability & Replication
- Setup
- Aurora PostgreSQL DB Cluster
- Configuration
- Understanding the billing model
- Migrating Data
- PostgreSQL SQL Language specifics
- Querying a Table
- Joins Between Tables
- Aggregate Functions
- Updates
- Deletions
- Advanced SQL
- Views, Triggers, Analytical Functions
- Foreign Keys
- Transactions
- Window Functions, Inheritance
Day 2
- Indexes
- Index types
- Indexes and ORDER BY
- Combining Multiple Indexes
- Operator Classes and Operator Families
- Examining Index Usage
- Full Text Search
- Transaction Isolation and Concurrency
- Transaction Isolation Levels
- Serializable Isolation Level
- Explicit Locking
- Table-Level Locks
- Table-level lock modes
- Conflicting lock modes
- Row-Level Locks
- Deadlocks
- Advisory Locks
- Using EXPLAIN
- Security
- SSL Server File Usage
- Data Protection
- Identity and Access Management
- Logging and Monitoring
- Compliance, Resilience
- Best Practices
- Managing an Aurora DB Cluster
- Adding Replicas
- Performance and Scaling
- Parameter Groups
- Cloning Databases
- Auto Scaling
- Backing Up and Restore
- Backup Storage
- Creating a DB Cluster Snapshot
- Restoring from a DB Cluster Snapshot
- Copying a Snapshot
- Sharing a Snapshot
- Point-in-Time Recovery
- Maintaining DB Cluster
- Updates
Day 3
- Monitoring an Aurora DB Cluster
- Overview of Monitoring
- Viewing a DB Cluster
- Aurora DB Cluster Metrics
- Performance Insights
- Aurora Recommendations
- Database Activity Streams
- RDS Event Notification
- Log Files
- Logging API Calls
- Security
- Updating Applications for New SSL/TLS Certificates
- Migrating Data to Aurora PostgreSQL
- Logical Replication
- Execution Plans
- Managing Query Execution Plans for Aurora PostgreSQL
- Best Practices for Query Plan Management
- dba_plans view
- Capturing Execution Plans
- Using Managed Plans
- Fast Recovery after Failover
- Upgrading Engine Version
- Best Practices with Aurora PostgreSQL
- Troubleshooting Techniques
