PostgreSQL Training

Participants are required to have basic knowledge of DATABASE and RDBMS.

• How relational databases work and how are they relevant in today’s times
• What sort of a database management system is PostgreSQL
• How to install and setup PostgreSQL and run queries on it
• How to create applications with PostgreSQL as a back end database management system

Module 1: Introduction

• History of PostgreSQL
• Major Features
• New Features of PostgreSQL
• Multi Version Concurrency Control
• Write-Ahead Logging
• Architectural Overview
• Limits

Module 2: 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 

Module 3: Creating and Managing Databases

• Object Hierarchy
• Creating Databases
• Creating Schemas
• Schema Search Path
• Roles, Users & Groups
• Access Control

Module 4: Postgres Data Dictionary

• The System Catalog Schema
• System Information views/tables
• System Information Functions

Module 5: 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

Module 6: Security

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

Module 7: Backup and Recovery & Point-in Time Recovery

• Backup Types
• SQL Dump
• Cluster Dump
• Offline Copy Backup
• Continuous Archiving
• pg_basebackup
• Point-In Time Recovery
• pg_upgrade

Module 8: Routine Maintenance

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

Module 9: Table Partitioning

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

Module 10: Database Monitoring

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