Amazon Aurora PostgreSQL from A to Z
- Descrição
- Currículo
- FAQ
- Revisões
PS:
-
Focus of this course is on Designing, Building and Managing Aurora PostgreSQL clusters
-
This course will NOT teach you SQL or Database design
Who will benefit from this course?
-
Cloud Architects, & Engineers looking to leverage Aurora PostgreSQL
-
Database Administrators interested in diving deep into Aurora Postgres
-
Cloud Developers looking to leverage Aurora Postgres for building cloud native applications
-
Anyone interested in learning how Aurora Postgres is different from community version of Postgres
Objectives
By the end of this course you will be able to:
-
Design & deploy highly available, and scalable Aurora PostgreSQL clusters
-
Ensure security of your DB cluster from all perspectives (Network, Data, A&A …)
-
Effectively use the Aurora features such as Global Database, Serverless, Cloning, Cache management etc.
-
Carry out monitoring using CloudWatch metrics, Events, Logs
-
Query performance tuning using tools such as Performance Insights
Pre-Requisites
This course is intended for anyone who is interested in using AWS Aurora PostgreSQL database for their cloud applications. If you are new to PostgreSQL then no worries as the course starts with the fundamentals of PostgreSQL.
-
Hands on with AWS
-
Experience with any RDBMS
-
Basic Unix shell scripting
-
Open to learn
Course outline
Course is divided into multiple sections. Each section starts with a lesson titled “Section Objectives”. To gain a better understanding of the topics covered in the course, please check out the first lesson (preview) in sections of your interest.
-
Fundamentals of PostgreSQL
-
Aurora Architecture deep dive & cluster setup
-
Monitoring features and tools (CloudWatch, Enhanced Monitoring, Performance Insights ..)
-
Designing highly available, and scalable clusters
-
Cluster configuration management
-
Query performance tuning
-
Aurora Postgres Security
-
Effectively using Aurora features such as Global Database, Serverless, Cache management
-
Aurora Disaster Recovery
-
Backup Recovery
-
Cluster management tasks such as vaccuming, analyze, explain etc.
Why I built this course?
Aurora is complex and architecturally very different from the databases available in the market. When I started to learn it, the biggest challenge was the non availability of Aurora specific information. The only source of information is the Amazon Aurora documentation website which in my humble opinion is not the most effective way to learn. It took me quite sometime to wrap my head around Aurora’s architecture and features; the tutorials in the documentation involve manual steps, mostly on the console, as a result they are not easy to follow. Long story short, for folks new to Aurora (& Postgres) learning Aurora can be daunting.
Based on my personal experience with learning Aurora Postgres, I decided to put together this course to help others get up to speed with Aurora in minimum possible time. So if I have to describe in one line, why I built this course – “It is to accelerate students learning”.
Disclaimer
-
Course content developed using publicly available sources such as AWS documentation & blogs
-
Opinions/views expressed in the course are my (Rajeev Sakhuja) own and does not reflect opinions/views of my employer or Amazon/AWS
-
3
Section ObjectivesSection objectives
-
4
Part-1 : Storage, Processes & Query processing
In this lesson students will learn the building blocks of PostgreSQL database server
-
5
Part-2 : WAL, Checkpointing, Archiver & Background processes
PostgreSQL achieves high performance by way of Write Ahead Logging (WAL). In this lesson you will learn how WAL works.
-
6
Part-3 : High availability, Standby servers & Replication
PostgreSQL provides standby server support. By the end of this lesson you will have a solid understanding of how standby works!!
-
7
Part-4 : Scaling, Read vs Write scaling
You will learn about various features for PostgreSQL scaling.
-
8
Part-5 : Tools, Config, Logs & Catalog
Throughout this course, you will use many PostgreSQL tools. This lesson will provide you a gentle introduction of some of the tools.
-
9
Section Objectives
Section Objectives
-
10
AWS PostgreSQL options
You will learn about all the options for setting up PostgreSQL on AWS cloud.
-
11
RDS Vs Aurora PostgreSQL
This lesson will help you in understanding the main differences between RDS PostgreSQL and Aurora PostgreSQL
-
12
Aurora specific features
Aurora has many unique features due to its decoupled storage architecture, in this lesson you will learn about all of those features.
-
13
Aurora - PostgreSQL version & releases
You will learn about the Aurora PostgreSQL release cycles
-
14
Deciding between RDS & Aurora PostgreSQL
This lesson will help you with deciding between RDS and Aurora PostgreSQL
-
15
Section Objectives
-
16
Development environment & project repository
Setup the development environment:
* Clone the Git repo
* Install Visual studio code
-
17
Hands on : Test VPC Setup
Setup the test VPC using Cloud Formation
-
18
Hands on : Aurora DB Cluster Setup
Setup the Aurora DB Cluster using a CloudFormation template
-
19
Hands on : Bastion Host Setup
Setup an Amazon Linux 2 Bastion host in public subnet
-
20
Hands on : Windows Bastion Host Setup
Setup a Windows Bastion Host in public subnet
-
21
AWS CLI & Utility Scripts
A quick overview of the utility scripts
-
22
Hands on : Test Setup Cleanup
Environment cleanup instructions
-
23
Section Objectives
Section Objectives
-
24
Aurora Storage Architecture
In this lesson you will learn about how the decoupled storage architecture works under the covers.
-
25
Database Instance Classes & I/O performance
Aurora's decoupled architecture requires user to pick up an appropriate DB VM/instance.
-
26
Database Instance Right Sizing
In this lesson you will learn how to select the right DB instance for a workload.
-
27
Primary instance, Replicas & Endpoints
Aurora exposes multiple types of endpoints; by the end of this lesson, you will have a fairly good understanding of how these endpoints work.
-
28
Hand on : Read Replica
In this hands on lesson, we will add a read replica to our test cluster.
-
29
Aurora Reads & Writes
You will learn about the quorum model used by Aurora for Reads/Writes to the storage tier.
-
30
AZ +1 Fault Tolerance Model
Aurora's architecture is highly fault tolerant - in this lesson you will learn how high fault tolerance is achieved.
-
31
Aurora Standby Replication
Aurora uses replication to keep the replicas up to date in terms of buffer cache. This mechanism is transparent to the user.
-
32
Hands on : Replication Lag
Learn how to monitor the replication lag.
-
33
Section Objectives
Section Objectives
-
34
Baseline, KPI & Metric Monitoring
Learn the baselining mechanism to ensure proactive issue remediation.
-
35
Introduction to Pgbench tool
You will learn about the commonly used PostgreSQL Pgbench tool.
-
36
RDS CloudWatch Metrics & Dimensions
Learn about the Aurora CloudWatch metrics.
-
37
RDS Aurora Metrics
You will learn about the common CloudWatch metric published by Aurora
-
38
Hands on : Aurora CloudWatch Metrics
Observe the various CloudWatch metrics for Aurora
-
39
RDS Enhanced Metrics
By the end of this lesson, you will be able to describe the difference between CloudWatch & Enhanced metrics
-
40
Hands on : Enhanced Metrics
In this exercise, you will try out enhanced metrics
-
41
RDS Database Logs
-
42
PostgreSQL Log files & configuration
PostgreSQL writes log files to the local file system. In this lesson you will learn how to access these logs.
-
43
Hands on : Checkout PostgreSQL Logs
In this hands on exercise, you we will run some queries against the PostgreSQL engine and observe the log messages.
-
44
RDS Events
Aurora emits events that users can subscribe to. In this lesson you will learn about the various categories of events and the payload format of these events.
-
45
Hands on : RDS Event retention to CloudWatch
In this hands on exercise you will learn how to store Aurora events in CloudWatch
-
46
Section Objectives
Section Objectives
-
47
Aurora Storage Scaling
In this lesson you will learn how Aurora dynamically resizes the storage.
-
48
Aurora Compute Scaling
User decides on the size of the DB instance and the number of read replicas. In this lesson you will learn about the Aurora horizontal, vertical & autot scaling capabilities,
-
49
Hands on : Vertical Scaling
In this hands on exercise, you will try out the vertical scaling.
-
50
Hands on : Auto Scaling
Try out the Auto Scaling policy in this exercise.
-
51
Aurora Failover
You will learn how Aurora manages the failover.
-
52
Hands on : Aurora failover
Try out the Aurora Failover in your test setup.
-
53
Aurora Cluster Cache Management
Aurora offers this feature for fast recovery after the failover.
-
54
Section Objectives
Section Objectives
-
55
Cluster & DB Parameter Groups
You will learn how to manage PostgreSQL engine configuration by way of Parameter Groups
-
56
Hands on : Parameter Groups
Try out the custom parameter group in this exercise.
-
57
RDS Option Groups & Extensions
You will learn about RDS option groups and PostgreSQL extensions.
-
58
PostgreSQL Extensions
Learn about PostgreSQL extensions
-
59
Aurora Supported Extensions
Learn how Aurora supports the PostgreSQL extensions.
-
60
Hands on : Address standardizer extension
Try out the standard PostgreSQL extension : Address Standardizer
-
61
Hands on : Using the S3 extension
Try out the extension for export/import of data to/from S3 bucket
-
62
Section Objectives
Section Objectives
-
63
PostgreSQL Stats Subsystem
An overview of the standard PostgreSQL stats subsystem
-
64
PostgreSQL Stats Functions
An overview of the stats functions available for accessing the stats data
-
65
Hands on : Stats views and functions
Hands on exercise that will provide you hands on experience with the stats subsystem
-
66
PostgreSQL Wait events and Locks
Learn about the PostgreSQL locks and waits
-
67
Hands on : Wait & Lock events
Check out the locks and wait events in your test cluster
-
68
pg_stat_statements extension
Learn about the extension : pg-stat-statements
-
69
Hands on : pg_stat_statements extension
Use the pg-stat-statements to see the statements and client status in real time
-
70
Section Objectives
Section Objectives
-
71
PostgreSQL Query Processing, Plan, Paths, Optimizer
Learn about the various components of the backend process that executes the SQL queries in PostgreSQL
-
72
Explain command
Learn about the query plan generated by the Explain command
-
73
Hands on : Explain command
Try out the explain command to generate the query plan
-
74
PostgreSQL Analyze command
Learn what Analyze command does and why it is important
-
75
Multi Version Concurrency Control (MVCC)
Learn about the PostgreSQL Multi Version Concurrency Control protocol
-
76
Vacuuming - Addressing table bloat
Learn how table bloat is addressed by Vacuuming
-
77
Vacuum - Addressing the wraparound
Learn how wrap around is addressed by Vacuuming
-
78
Vacuum command
Learn to use Vacuum command
-
79
Hands on : vacuum command
Try out the vacuum command
-
80
Auto vacuuming
Learn how to use Auto vacuuming
