PostgreSQL AWS RDS Essential Training

Explore Amazon RDS, a fully managed service that simplifies setting up, operating, and scaling relational databases in the cloud, with engines such as MySQL, PostgreSQL, Oracle, SQL Server, and Aurora.

Learn how Amazon RDS uses EBS-backed DB instances with GP2/GP3 general purpose SSDs and provisioned IOPs for IO-intensive workloads, balancing cost and performance for development and production.

Learn how Amazon S3 provides scalable, secure object storage for data lakes, backups, and archiving, and how to create a bucket and folders to import and export database data.

Create a Postgres database instance on AWS RDS by selecting the Postgres engine, using free tier settings, and choosing a Udemy identifier, then configure credentials, storage, and public access.

Learn to manage PostgreSQL settings with parameter groups in AWS RDS, assign custom groups to DB instances or multi-AZ clusters, and apply static or dynamic changes with or without reboot.

Install postgres on an ec2 ubuntu server, configure ssh access with a key pair, run apt update, install postgres contrib, switch to postgres user, and verify activity; intro to Pgadmin.

Download and install Pgadmin 4 version 7.4 on Windows from ww.pgadmin.org, run the executable, accept the agreement, and launch Pgadmin to prepare for connecting to the RDS database.

Install and launch Azure Data Studio, install the Postgres extension, and connect to a Postgres database to manage databases across SQL Server and Postgres, using your preferred tool.

Learn how Amazon RDS automated backups work, including full storage snapshots of the entire database instance, retention, point-in-time recovery, and optional manual and cross-region backups.

Learn to replicate automated backups to another AWS region for disaster recovery by enabling cross-region backup replication of snapshots and transaction logs in AWS RDS.

Enable automated backups in AWS RDS, configure retention and a UTC backup window, and learn how to restore a database while avoiding maintenance window conflicts.

Create manual snapshots of a selected DB instance before making changes to production databases to enable rollback, then name the snapshot and wait about ten minutes.

Select a snapshot in the actions menu to restore and configure a new database instance, then delete the snapshot to avoid costs, and retain automated backups and a final snapshot.

Copy automated or manual PostgreSQL RDS snapshots to another region, restore in the new region, and create a database with public access while understanding copy timing and snapshot retention nuances.

Learn to export database snapshots to Amazon S3 by creating a unique S3 bucket, an IAM policy and role, and a KMS key, then run and verify the JSON export.

Delete a database snapshot in AWS RDS by selecting the snapshot in the snapshot section, choosing delete snapshot, and confirming the deletion in the North Virginia region.

Understand Postgres roles and permissions, including the default administrator account Postgres, and learn how to create it with master-username via the AWS CLI and its RDS super user privileges.

Understand the rds_superuser role in PostgreSQL on AWS RDS, including login privileges, default rights, and predefined roles like RDS admin, RDS password, and pg_stat_activity view monitoring.

Revoke default public privileges and grant granular permissions using roles like demo and read write. Enforce connect, schema usage, and table privileges to implement least privilege on the Udemy database.

Explore delegating and controlling user password management in PostgreSQL AWS RDS by enabling the restricted password management feature, assigning password privileges, and managing who can create or alter passwords.

Enforce scram sha-256 password encryption in Postgres on RDS by updating the password encryption parameter to scram sha-256, switching to scram-only authentication, and verifying all user passwords.

Copy DVD rental backup from an EC2 instance to an Ubuntu server, create a backups folder and set Postgres ownership, then restore to the RDS database using pg_dump and pg_restore.

Export data from a PostgreSQL RDS instance to S3 using pgdump, with options to dump entire database, a single schema, or a single table, and save backups as SQL files.

Move Postgres databases between AWS RDS instances quickly using the pg transport extension. Keep same Postgres version, attach a custom parameter group with shared_preload_libraries, and note limitations with read replicas.

Scale read workloads by offloading to a read replica, which remains read-only; promote it to a standalone, irreversible RDS instance (14.1+), with configurable region, instance class, and public access.

Disable multi-az deployments by modifying the DB instance, selecting do not create a standby instance, and applying changes immediately; the database becomes available without a multi-az standby.

Deploy a multi-az db cluster with a writer and two reader instances across three azs to provide high availability, increased read capacity, and lower write latency through semi-synchronous replication.

Establish baselines to monitor database performance, reliability, and availability, and detect anomalies in network throughput, client connections, i/o, metadata operations, and cpu utilization with cloudwatch for monitoring.

Use the Amazon console to view your RDS instance status and recent activity, including backups and modifications, and explore recommendations such as enabling performance insight, multi-az, and enhanced monitoring.

Learn how Amazon RDS uses CloudWatch to monitor database metrics, including CPU utilization, active sessions, top SQL and processes, and performance insight, with options for enhanced monitoring and time range.

Learn to monitor Amazon RDS metrics with CloudWatch, create dashboards and alarms, and analyze CPU utilization and logs to optimize your RDS databases.

Understand how the postgres vacuum command reclaims disk space and updates statistics and visibility map, with standard vacuum online and vacuum full locking table; auto vacuum is enabled in RDS.

Determine when to vacuum PostgreSQL tables by querying the pg_database view, monitor transaction ID ages, and set auto vacuum thresholds and alarms at 200M, 500M, 1B, and 1.5B.

Tune the maintenance_min parameter to allocate memory for autovacuum to scan tables and hold IDs, and adjust autovacuum_max_workers based on small vs large tables (1–2 GB or 2–4 GB).

Learn how Postgres uses statistics to drive query plans, how analyze and vacuum update those stats, and how autovacuum keeps plans accurate as data changes.

Learn how Postgres uses multiversion concurrency control with vacuum to reclaim space, compare standard vacuum and vacuum full, and see how vacuum demon schedules updates dynamically.

Learn how to prevent transaction ID wrap around in Postgres by regular vacuuming and adaptive autovacuum on Amazon RDS, with monitoring via CloudWatch and current parameter checks.

Explore how vacuum maintains a visibility map for each table to track pages containing only tuples visible to all active transactions, enabling skip of cleanups and faster index scans.