Course Overview:
The course talks about How to get going with PostgreSQL on the AWS Aurora Database Engine. It starts with basic PostgreSQL syntaxes and then slowly starts discussions on more advanced topics like replications, HA, optimizations etc.
Pre-requisites:
- A working knowledge of SQL and C
- Basic system administration (Unix/Linux or Windows)
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
