Amazon Aurora PostgreSQL and Amazon RDS PostgreSQL

Curriculum description

In this fourteen-course curriculum, you will learn best practices and recommendations for using PostgreSQL. These courses provide an overview of managed PostgreSQL with Amazon Aurora and Amazon Relational Database Service (Amazon RDS) and detailed instruction on setting up, configuring, and managing PostgreSQL instances. Courses also cover more advanced topics, such as PostgreSQL monitoring, maintenance, and upgrading.
• Course level: Foundational
• Duration: 7 hours

Activities

This curriculum includes courses with presentations, videos, and knowledge assessments.

Curriculum objectives

In this curriculum, you will learn to:

• Set up, configure, and use PostgreSQL in Aurora and Amazon RDS.

• Move data to a PostgreSQL instance.

• Manage a PostgreSQL instance.

• Understand how to handle security, monitoring, and backups of a PostgreSQL instance.

• Scale and migrate a PostgreSQL instance.

Intended audience

This curriculum is intended for:

• Database professionals

• Students

• Anyone curious about PostgreSQL databases

Prerequisites

• None 

Curriculum outline

Course 1: Overview of Managed PostgreSQL with Amazon RDS and Aurora

• AWS Databases and Analytics

• Relational Databases

• Amazon RDS

• Amazon RDS Feature Highlights

• Amazon RDS Performance Insights

• Aurora

• Aurora Feature Highlights

Course 2: Set Up and Configure PostgreSQL

• Enter the RDS Console

• Create a PostgreSQL DB Instance

• Download a SQL Client

• Connect to the PostgreSQL Database

• Delete the DB Instance

• Introduction to PostgreSQL psql

• psql Client

• Connect Using psql Client

• Create a Test Table

• Export Results to a File

• SET Parameters

• Watch Queries

Course 3: Introduction to PostgreSQL SQL

• Enter the RDS Console

• SQL Primer

• SQL Functions

• Using Nested Statements

• Using Joins

• Using Integrity Constraints

• Manipulating Sequences

• Combining Queries

• Using Aggregates

Course 4: Moving Data in PostgreSQL

• COPY Command

• Foreign Data Wrappers

• Amazon S3 Import

• Pgloader Utility

Course 5: Managing PostgreSQL

• Connecting to PostgreSQL

• Creating PostgreSQL Databases

• Managing Databases

• Creating and Managing PostgreSQL Schemas

• Parameter Groups

• Roles

• Users

• Groups

• Privileges

• Ending Connections

Course 6: PostgreSQL Security

• Security Management

• Restricting Password Management

• Connecting to PostgreSQL over SSL

• Object Security Privileges

• Row-Level Security

• Object Security Auditing

Course 7: PostgreSQL Backups

• Backups

• Physical Backups

• Logical Backups

• Manual Snapshots

• SQL Dump

• Restoring Backups

Course 8: PostgreSQL Monitoring

• Automated Monitoring Tools

• PostgreSQL Native Monitoring

• Process Monitoring

• Wait Events

• SSL Monitoring

• Query Monitoring

Course 9: PostgreSQL System Catalog

• PostgreSQL System Catalog

• System Catalog Query

• Object Identifiers

• Structural Catalogs

• Informational Catalogs

• Performance Catalogs

Course 10: PostgreSQL Maintenance

• Routine Database Maintenance Optimization

• Bloat

• Transaction ID Wraparound

• Vacuum

• Autovacuum

• Data Statistics

• pg_repack

• Routine Reindexing

Course 11: PostgreSQL Major Upgrades

• Minor Version Upgrades

• Major Version Upgrades

• Specify a Version-Compatible Parameter Group

• Check Version Compatibility

• Check for Unsupported Usage

• Handle Amazon RDS Read Replicas or Amazon Aurora Secondary Clusters

• Perform a Backup

• Update Extensions

• Drop Extensions

• Drop Unknown Data Types

• Perform an Upgrade Dry Run

• Resolve a Failed Upgrade

• Upgrade the Production Instance

• Post-Upgrade Activity

Course 12: PostgreSQL Scalability

• Database Scaling

• Vertical Scaling

• Horizontal Scaling

• Cross-Region Replicas

• Logical Replication

• Managing Database Connections

Course 13: PostgreSQL Migration

• AWS DMS Console

• Creating a Replication Instance

• Specifying Source and Target Endpoints

• Creating a Task

• Monitoring Your Task

Course 14: Advanced Features of Amazon Aurora and Amazon RDS PostgreSQL

• Fast Database Cloning, Read Replicas, and Custom Endpoints

• Aurora Serverless

• Aurora Machine Learning

• Amazon RDS Proxy

• Aurora Global Database

• Extensions Introduction

• Installing and Using Extensions