AWS PostgreSQL Advanced Administration

Overview

Duration: 5 days
In this course you will learn the essential details of AWS PostgreSQL Administration including PostgreSQL architecture, configuration, maintenance, monitoring, backup, recovery, and data movement. It is designed for people with experience in database administration and who are new to PostgreSQL

Objectives

-

Content

Day 1

Part 1: Introduction 
  • History of PostgreSQL & AWS CLOUD 
  • Major Features 
  • New Features of PostgreSQL 
  • Multi Version Concurrency Control 
  • Write-Ahead Logging 
  • Architectural Overview 
  • Limits

Part 2: PostgreSQL AWS Cloud
  • Understanding and use cases of cloud
  • Creating PostgreSQL Server Instance
  • Parameter Tuning
  • Overview - Migration of PostgreSQL to Cloud

Part 3: PostgreSQL System Architecture
  • Architectural Summary
  • Shared Memory
  • Statement Processing
  • Utility Processes
  • Disk Read Buffering
  • Write Buffering
  • Background Writer Cleaning Scan
  • Commit & Checkpoint
  • Physical Database Architecture
  • Data Directory Layout
  • Installation Directory Layout
  • Page Layout

Part 4: Configuration
  • Setting PostgreSQL Parameters
  • Access Control
  • Connection Settings
  • Security and Authentication
  • Settings 
  • Memory Settings
  • Query Planner Settings
  • WAL Settings
  • Log Management
  • Background Writer Settings
  • Statement Behavior
  • Vacuum Cost Settings
  • Autovacuum Settings

Day 2

Part 5: Installation

  • OS User & Permissions
  • Installation
  • Setting environment variables
  • Clusters
  • Creating a database cluster
  • Starting and Stopping the Server
  • (pg_ctl)
  • Connect to the server using psql

Part 6: Creating and Managing Databases
  • Object Hierarchy
  • Creating Databases
  • Creating Schemas
  • Schema Search Path
  • Roles, Users & Groups
  • Access Control

Part 7: Postgres Data Dictionary
  • The System Catalog Schema
  • System Information views/tables
  • System Information Functions

Part 8: pgAdmin III
  • Registering a server
  • Viewing and Editing Data
  • Query Tool
  • Databases
  • Languages
  • Schemas
  • Domains
  • Functions
  • Sequences
  • Tables
  • Columns
  • Constraints
  • Indexes
  • Maintenance
  • Rules
  • Triggers
  • Types
  • Views
  • Table spaces
  • Roles

Day 3

Part 9: Security Basics

  • Authentication 
  • Authorization
  • Levels of security 
  • pg_hba.conf file 
  • Users 
  • Object ownership 
  • Access control 
  • Application access parameters

Part 10: SQL
  • Data Types 
  • Tables 
  • SQL Queries 
  • Quoting 
  • Using SQL Functions 
  • Constraints 
  • Dropping or Removing Database 
  • Objects 
  • Views 
  • Sequences 
  • Indexes

Part 11: Backup and Recovery & Point-in Time Recovery
  • Backup Types 
  • SQL Dump 
  • Cluster Dump 
  • Offline Copy Backup 
  • Continuous Arc hiving 
  • pg_basebackup 
  • Point-In Time Recovery 
  • pg_upgrade

Part 12: Routine Maintenance
  • Explain and Explain Analyze
  • Table Statistics
  • Updating Planner Statistics
  • Scheduling Auto Vacuum
  • Preventing Transaction ID Wraparound Failures
  • The Visibility Map
  • Routine Reindexing

Part 13: PostgreSQL API Connectivity
  • Installing Third-Party Drivers 
  • Installation & Configuration of JDBC & ODBC Drivers 
Day 4

Part 14: Transactions and Concurrency
  • Transaction Definition 
  • Effects of Concurrency on 
  • Transactions 
  • Transaction Isolation Levels 
  • Multi-Version Concurrency Control 
  • Overview (MVCC) 
  • MVCC Example 
  • Internal Identifiers 
  • Transaction Wraparound 
  • MVCC Maintenance
  • MVCC Demo

Part 15: Performance Tuning
  • Hardware Configuration 
  • OS Configuration 
  • Server Parameter Tuning 
  • Connection Settings 
  • Memory Parameters 
  • Memory settings for Planner 
  • WAL Parameters 
  • Explain Plan 
  • Explain Example 
  • Statistics Collection 
  • Indexes 
  • Examining Index Usage 
  • Tips for Inserting Large Amount of Data 
  • Some Notes About pg_dump Non-Durable Settings 
  • Labs

Part 16: Replication & Failover

  • Database High Availability 
  • Causes of Data Loss 
  • Plan for Common Errors 
  • Selection Criteria 
  • High Availability Options 
  • Hot Streaming Replication, Architecture and Setup 
  • Streaming Replication Example

Day 5

Part 17: Table Partitioning

  • Partitioning
  • Partitioning Methods
  • When to Partition
  • Partitioning Setup
  • Partitioning Example
  • Partitioning and Constraint
  • Exclusion
  • Caveats
  • Lab

Part 18: Connection Pooling
  • Pgpool-II
  • Pgpool-II Features
  • Install and Configure pgpool-II
  • Pgpool II Modes
  • Starting/Stopping pgpool-II
  • Pgpool-II Example

Part 19: Database Monitoring
  • Database Statistics 
  • The Statistics Collector 
  • Database Statistic Tables 
  • Operating System Process Monitoring 
  • Current Sessions and Locks •Log Slow Running Queries
  • Disk Usage

Audience

This course is designed for people with experience in database administration and also who are new to the PostgreSQL Database.

Prerequisites

-

Certification

Schedule

Course ID:
AWS-PSQL-AA


Show Schedule for: