OpenCourser brand icon
Sign in
We may earn an affiliate commission when you visit our partners.

Amazon Aurora PostgreSQL from A to Z

Rajeev Sakhuja

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?

Read more

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:

  1. Design & deploy highly available, and scalable Aurora PostgreSQL clusters

  2. Ensure security of your DB cluster from all perspectives (Network, Data, A&A ...)

  3. Effectively use the Aurora features such as Global Database, Serverless, Cloning, Cache management etc.

  4. Carry out monitoring using CloudWatch metrics, Events, Logs

  5. 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.

  1. Hands on with AWS

  2. Experience with any RDBMS

  3. Basic Unix shell scripting

  4. 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

Enroll now

What's inside

Learning objectives

  • Fundamentals of postgresql
  • Rds aurora architecture & internals
  • Build & manage aurora postgresql database
  • Designing a aurora postgresql cluster
  • Best practices for aurora postgresql
  • Effectively use features such as global db, cloning, serverless, ccm ...

Syllabus

Introduction

Author introduction.

  • Course outline

  • Tips on getting the most out of this course

This section is for students who are new to PostgreSQL. Please free to skip it if you are already a PostgreSQL expert.
Read more

Section objectives

In this lesson students will learn the building blocks of PostgreSQL database server

PostgreSQL achieves high performance by way of Write Ahead Logging (WAL). In this lesson you will learn how WAL works.

PostgreSQL provides standby server support. By the end of this lesson you will have a solid understanding of how standby works!!

You will learn about various features for PostgreSQL scaling.

Throughout this course, you will use many PostgreSQL tools. This lesson will provide you a gentle introduction of some of  the tools.

Students will learn about commonly used PostgreSQL options on AWS (1) PG/EC2 (2) PG/RDS (3) PG/Aurora

Section Objectives

You will learn about all the options for setting up PostgreSQL on AWS cloud.

This lesson will help you in understanding the main differences between RDS PostgreSQL and Aurora PostgreSQL

Aurora has many unique features due to its decoupled storage architecture, in this lesson you will learn about all of those features.

You will learn about the Aurora PostgreSQL release cycles

This lesson will help you with deciding between RDS and Aurora PostgreSQL

Students will setup a Aurora PostgreSQL cluster using CloudFormation templates, AWS console & AWS CLI
Section Objectives

Setup the development environment:

* Clone the Git repo

* Install Visual studio code

Setup the test VPC using Cloud Formation

Setup the Aurora DB Cluster using a CloudFormation template

Setup an Amazon Linux 2 Bastion host in public subnet

Setup a Windows Bastion Host in public subnet

A quick overview of the utility scripts

Environment cleanup instructions

Deep dive into Aurora Architecture. Students will learn the difference between Aurora Postgres and Community Postgres.

Observe the various CloudWatch metrics for Aurora

In this lesson you will learn about how the decoupled storage architecture works under the covers.

Aurora's decoupled architecture requires user to pick up an appropriate DB VM/instance.

In this lesson you will learn how to select the right DB instance for a workload.

Aurora exposes multiple types of endpoints; by the end of this lesson, you will have a fairly good understanding of how these endpoints work.

In this hands on lesson, we will add a read replica to our test cluster.

You will learn about the quorum model used by Aurora for Reads/Writes to the storage tier.

Aurora's architecture is highly fault tolerant - in this lesson you will learn how high fault tolerance is achieved.

Aurora uses replication to keep the replicas up to date in terms of buffer cache. This mechanism is transparent to the user.

Learn how to monitor the replication lag.

Aurora offers multiple tools for monitoring the Databases. Students will learn about CloudWatch, Events, Logs & Performance Insights.

Learn the baselining mechanism to ensure proactive issue remediation.

You will learn about the commonly used PostgreSQL Pgbench tool.

Learn about the Aurora CloudWatch metrics.

You will learn about the common CloudWatch metric published by Aurora

By the end of this lesson, you will be able to describe the difference between CloudWatch & Enhanced metrics

In this exercise, you will try out enhanced metrics

RDS Database Logs

PostgreSQL writes log files to the local file system. In this lesson you will learn how to access these logs.

In this hands on exercise, you we will run some queries against the PostgreSQL engine and observe the log messages.

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.

In this hands on exercise you will learn how to store Aurora events in CloudWatch

Students will learn about Aurora High Availability & Scaling Architecture

Try out the standard PostgreSQL extension : Address Standardizer

In this lesson you will learn how Aurora dynamically resizes the storage.

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,

In this hands on exercise, you will try out the vertical scaling.

Try out the Auto Scaling policy in this exercise.

You will learn how Aurora manages the failover.

Try out the Aurora Failover in your test setup.

Aurora offers this feature for fast recovery after the failover.

Students will learn how to manage the PostgreSQL configuration using Parameter Groups; and how to use PostgreSQL extensions on Aurora.

You will learn how to manage PostgreSQL engine configuration by way of Parameter Groups

Try out the custom parameter group in this exercise.

You will learn about RDS option groups and PostgreSQL extensions.

Learn about PostgreSQL extensions

Learn how Aurora supports the PostgreSQL extensions.

Try out the extension for export/import of data to/from S3 bucket

Applications require highly performant database queries. In this section, students will learn how to identify query bottlenecks using the various tools.

An overview of the standard PostgreSQL stats subsystem

An overview of the stats functions available for accessing the stats data

Hands on exercise that will provide you hands on experience with the stats subsystem

Learn about the PostgreSQL locks and waits

Check out the locks and wait events in your test cluster

Learn about the extension : pg-stat-statements

Use the pg-stat-statements to see the statements and client status in real time

Students will learn about PostgreSQL MVCC, Vacuum, Analyze, Explain commands

Learn about the various components of the backend process that executes the SQL queries in PostgreSQL

Learn about the query plan generated by the Explain command

Try out the explain command to generate the query plan

Learn what Analyze command does and why it is important

Learn about the PostgreSQL Multi Version Concurrency Control protocol

Learn how table bloat is addressed by Vacuuming

Learn how wrap around is addressed by Vacuuming

Learn to use Vacuum command

Try out the vacuum command

Learn how to use Auto vacuuming

Student will use Performance Insights to identify bottlenecks for tuning the queries.

Performance Insights Overview - what is it used for?

Performance Insights - Counter metrics

Learn about the Performance Insights key metrics:

  • DB Load

  • Average Active Sessions

Follow along to learn how to use the Performance Insights dashboard

Deeper dive on how IO wait events directly impact the query performance

Learn to tune the buffer cache for optimal query performance

Learn to use the Performance Insights dashboard.

Understand the reasons fort excessive Aurora IOXactSync events

Good to know

Know what's good
, what to watch for
, and possible dealbreakers
Focuses on designing, building, and managing Aurora PostgreSQL clusters, which is essential for professionals working with cloud-native applications
Covers cluster management tasks such as vacuuming, analyze, and explain, which are crucial for maintaining database health and performance
Explores Aurora's architecture and its differences from community PostgreSQL, which is vital for making informed decisions about database selection
Teaches how to effectively use Aurora features like Global Database, Serverless, and Cache Management, which are valuable for building scalable applications
Examines query performance tuning using tools such as Performance Insights, which is essential for optimizing database performance
Starts with the fundamentals of PostgreSQL, but does not teach SQL or database design, which may require learners to seek out additional resources

Save this course

Save Amazon Aurora PostgreSQL from A to Z to your list so you can find it easily later:
Save

Activities

Be better prepared before your course. Deepen your understanding during and after it. Supplement your coursework and achieve mastery of the topics covered in Amazon Aurora PostgreSQL from A to Z with these activities:
Review Relational Database Concepts
Solidify your understanding of relational database concepts before diving into Aurora PostgreSQL. This will help you grasp the underlying principles and appreciate Aurora's architecture.
Browse courses on Relational Databases
Show steps
  • Review database normalization principles.
  • Study SQL basics: queries, joins, and transactions.
  • Understand ACID properties of database transactions.
Brush Up on AWS Fundamentals
Familiarize yourself with core AWS services and concepts. This will make it easier to navigate the AWS console and understand the context in which Aurora PostgreSQL operates.
Show steps
  • Review AWS IAM roles and policies.
  • Understand AWS VPC networking concepts.
  • Practice launching and configuring EC2 instances.
  • Learn about AWS S3 storage.
Read 'PostgreSQL Up and Running'
Supplement your learning with a comprehensive guide to PostgreSQL. This book will provide a deeper understanding of the underlying database technology that Aurora is built upon.
View PostgreSQL: Up and Running: A Practical Guide... on Amazon
Show steps
  • Obtain a copy of 'PostgreSQL Up and Running'.
  • Read the chapters on installation, configuration, and basic SQL.
  • Explore the advanced topics like replication and performance tuning.
Four other activities
Expand to see all activities and additional details
Show all seven activities
Discuss Aurora Architecture with Peers
Reinforce your understanding of Aurora's architecture by discussing it with peers. Explaining concepts to others can help solidify your own knowledge.
Show steps
  • Form a study group with other students.
  • Prepare discussion points on Aurora's shared storage layer.
  • Explain Aurora's read replica mechanism to each other.
Follow AWS Tutorials on Aurora Monitoring
Gain hands-on experience with Aurora monitoring tools by following AWS tutorials. This will help you proactively identify and resolve performance issues.
Show steps
  • Find AWS tutorials on CloudWatch metrics for Aurora.
  • Implement enhanced monitoring for your Aurora cluster.
  • Set up performance insights and analyze query performance.
Build a Sample Application with Aurora PostgreSQL
Solidify your knowledge by building a sample application that uses Aurora PostgreSQL. This will give you practical experience with database design, query optimization, and application integration.
Show steps
  • Design a simple database schema for your application.
  • Implement CRUD operations using SQL queries.
  • Connect your application to the Aurora PostgreSQL cluster.
  • Deploy your application to AWS.
Write a Blog Post on Aurora Disaster Recovery
Deepen your understanding of Aurora's disaster recovery capabilities by writing a blog post. This will force you to research and synthesize information on this important topic.
Show steps
  • Research Aurora's backup and recovery mechanisms.
  • Describe different disaster recovery scenarios.
  • Explain how Aurora's global database feature can be used for DR.
  • Publish your blog post online.

Career center

Learners who complete Amazon Aurora PostgreSQL from A to Z will develop knowledge and skills that may be useful to these careers:
Cloud Database Engineer
A Cloud Database Engineer specializes in the implementation, management, and maintenance of database systems in cloud environments, making the skills learned in this course directly relevant. This role involves designing and deploying scalable database solutions, ensuring high availability, and optimizing performance. This course helps build a foundation for working with Amazon Aurora PostgreSQL, which is a critical component for many cloud applications, by teaching how to manage Aurora clusters, monitor performance, and implement security measures. It's helpful for anyone looking to specialize in cloud database technologies.
See salaries and explore the career path for Cloud Database Engineer
Database Reliability Engineer
A Database Reliability Engineer, or DRE, focuses on the reliability, scalability, and performance of database systems. This course is very useful for a DRE working with Aurora PostgreSQL. The course focuses on designing and deploying highly available and scalable clusters, ensuring database security, and implementing monitoring and performance tuning. This is directly aligned with the responsibilities of a DRE. A DRE wishing to work with Aurora Postgres would find this a valuable course.
See salaries and explore the career path for Database Reliability Engineer
Cloud Engineer
Cloud Engineers are responsible for implementing and managing cloud infrastructure, and this course may be particularly useful if they work with databases. The course's emphasis on building, and managing Aurora PostgreSQL clusters aligns directly with the responsibilities of a cloud engineer who needs to manage database services. This course covers such topics as security, monitoring, and performance tuning, making it an important resource for those who need to effectively deploy and maintain Aurora PostgreSQL within their cloud environments. The course teaches how to use tools like CloudWatch and Performance Insights, which are essential for a cloud engineer.
See salaries and explore the career path for Cloud Engineer
Database Administrator
A Database Administrator is responsible for the performance, integrity, and security of databases, and this course may be useful for those who work with or plan to work with Aurora PostgreSQL. This role includes tasks such as database setup, configuration, monitoring, and troubleshooting, all of which are covered in the course. This course stands out by offering a deep dive into Aurora-specific features, such as global databases, serverless configurations, and performance tuning tools. Taking this course may help one to effectively manage Aurora PostgreSQL database clusters, ensuring they are secure and available.
See salaries and explore the career path for Database Administrator
Cloud Solutions Architect
Cloud Solutions Architects design and implement cloud-based solutions, and they often need to understand database technologies to create holistic systems. This course will help a Cloud Solutions Architect by focusing on designing, building, and managing Aurora PostgreSQL clusters. Architects need to design for high availability, scalability, and security, all of which are covered in this course. This course specifically addresses how the Aurora version differs from the community version of PostgreSQL, which would be particularly salient to a solutions architect.
See salaries and explore the career path for Cloud Solutions Architect
DevOps Engineer
DevOps Engineers focus on automating and streamlining software development and deployment processes, and this course may be useful if they manage databases. This course will help DevOps engineers by teaching how to deploy, manage, and monitor Aurora PostgreSQL clusters, which can be integrated into automated workflows. It covers important topics such as infrastructure as code, monitoring, and performance tuning, which are vital for DevOps practices. This course helps those who need to effectively manage Aurora PostgreSQL databases as part of their continuous delivery pipeline.
See salaries and explore the career path for DevOps Engineer
Site Reliability Engineer
Site Reliability Engineers are responsible for ensuring the reliability and availability of systems, and this course may be beneficial to an SRE who works with databases. This course will help a Site Reliability Engineer by providing the knowledge and skills to design, deploy, and manage highly available and scalable Aurora PostgreSQL clusters. SREs must implement monitoring and alerting systems, performance tuning, and security measures to ensure system robustness. This course may help those who need to manage Aurora PostgreSQL databases as part of their infrastructure.
See salaries and explore the career path for Site Reliability Engineer
Systems Engineer
Systems Engineers manage and maintain an organization's computer systems, and this course may be helpful for those working with database systems, especially in cloud environments. This course will help a Systems Engineer by teaching how to design, deploy, and manage Aurora PostgreSQL clusters, which is a specific technology they may be required to oversee. The course covers core concepts such as database architecture, security, monitoring, and performance tuning, which are all important aspects of system management. This course helps anyone who needs to manage and optimize database systems within a larger IT infrastructure.
See salaries and explore the career path for Systems Engineer
Data Architect
A Data Architect designs and oversees the implementation of data management systems, and this course may be useful for those who need to understand Aurora PostgreSQL. This course will help a data architect by teaching them how to design highly available, scalable, and secure Aurora PostgreSQL clusters. Since data architects need to ensure that data systems meet business needs for performance and reliability, this course, with its emphasis on cluster management, monitoring, and performance optimization is particularly valuable, and is highly correlated with their work.
See salaries and explore the career path for Data Architect
Data Engineer
Data Engineers build and manage the infrastructure for data processing and storage, and this course may be of use for those who work with Aurora PostgreSQL. This course will help a Data Engineer by teaching them how to design, deploy, and manage Aurora PostgreSQL clusters effectively. Data engineers need to ensure data systems are scalable, reliable, and performant, which is directly addressed in this course. This course will assist those who need to work with Aurora as part of their data infrastructure.
See salaries and explore the career path for Data Engineer
Application Developer
Application Developers build and maintain software applications, and this course may be useful for those working with Aurora PostgreSQL databases. This course may be useful to Application Developers as it provides direct training on how to manage and optimize Aurora PostgreSQL databases for use in cloud native applications. While this course focuses on the database itself, understanding how Aurora works can help developers write better applications. This course may be particularly helpful for developers creating cloud based applications that need a relational database.
See salaries and explore the career path for Application Developer
Database Migration Specialist
Database Migration Specialists focus on moving data and database systems from one environment to another. This course may be useful for a Database Migration Specialist, who needs to understand the specifics of Aurora PostgreSQL's architecture and features. This course's coverage of cluster management, security, and disaster recovery becomes salient when migrating to Aurora. This course may enable them to effectively manage and optimize Aurora PostgreSQL during migrations, ensuring minimal downtime and data integrity.
See salaries and explore the career path for Database Migration Specialist
Cloud Support Engineer
Cloud Support Engineers provide technical support to customers using cloud services. This course may be useful for a Cloud Support Engineer who needs to understand Aurora PostgreSQL. This course covers Aurora-specific features, monitoring tools, and troubleshooting techniques, which are all essential for supporting customers. This course may help cloud support engineers handle issues related to Aurora PostgreSQL effectively and assist customers with their database needs.
See salaries and explore the career path for Cloud Support Engineer
Technical Consultant
Technical Consultants provide expertise on technology solutions to clients. This course will help a Technical Consultant who focuses on database and cloud technologies by providing a deep understanding of Amazon Aurora PostgreSQL. This course, with its emphasis on cluster design, security, and performance tuning, offers a body of knowledge that consultants can leverage to advise clients. Consultants need a firm grasp of the technology landscape, and this course may give them an advantage if they are working with Aurora.
See salaries and explore the career path for Technical Consultant
Solutions Engineer
Solutions Engineers provide technical expertise and support to clients in order to sell or implement technological solutions, and this course may be helpful for those working with database technologies. This course will help a Solutions Engineer by focusing on the detailed aspects of designing, building, and managing Aurora PostgreSQL clusters. Solutions Engineers need a solid grounding in the technologies they are selling or implementing, and this course may provide them with that understanding in order to best serve their clients.
See salaries and explore the career path for Solutions Engineer

Reading list

We've selected one books that we think will supplement your learning. Use these to develop background knowledge, enrich your coursework, and gain a deeper understanding of the topics covered in Amazon Aurora PostgreSQL from A to Z.
Cover image
PostgreSQL: Up and Running
Save
Provides a comprehensive introduction to PostgreSQL, covering everything from installation and configuration to advanced features like replication and performance tuning. It's a great resource for those who are new to PostgreSQL or want to deepen their understanding of the database. While the course covers the fundamentals, this book provides more depth and breadth. It is particularly useful for understanding the underlying PostgreSQL concepts that Aurora builds upon.
PostgreSQL: Up and Running: A Practical Guide to...
Kindle Edition
$$

Share

Help others find this course page by sharing it with your friends and followers:
Facebook
LinkedIn
Reddit
X
Link
Email

Similar courses

Similar courses are unavailable at this time. Please try again later.
Effort
17 total hours
Via
Udemy
Instructor
Rajeev Sakhuja
Language
English
Good to know
Focuses on designing, building, and managing Aurora PostgreSQL clusters, which is essential for professionals working with cloud-native applications
Covers cluster management tasks such as vacuuming, analyze, and explain, which are crucial for maintaining database health and performance
Explores Aurora's architecture and its differences from community PostgreSQL, which is vital for making informed decisions about database selection
Teaches how to effectively use Aurora features like Global Database, Serverless, and Cache Management, which are valuable for building scalable applications
Examines query performance tuning using tools such as Performance Insights, which is essential for optimizing database performance
Starts with the fundamentals of PostgreSQL, but does not teach SQL or database design, which may require learners to seek out additional resources
Share and help others discover this course.
Facebook LinkedIn X Reddit Link Email
Don't miss out
Enroll today to gain access to this course
Enroll in this course
Our mission

OpenCourser helps millions of learners each year. People visit us to learn workspace skills, ace their exams, and nurture their curiosity.

Our extensive catalog contains over 50,000 courses and twice as many books. Browse by search, by topic, or even by career interests. We'll match you to the right resources quickly.

Find this site helpful? Tell a friend about us.

Affiliate disclosure

We're supported by our community of learners. When you purchase or subscribe to courses and programs or purchase books, we may earn a commission from our partners.

Your purchases help us maintain our catalog and keep our servers humming without ads.

Thank you for supporting OpenCourser.

© 2016 - 2025 OpenCourser