Duration
5 days
Introduction
- History of PostgreSQL
- Major Features
- New Features of PostgreSQL
- Multi Version Concurrency Control
- Write-Ahead Logging
- Architectural Overview
- Limits
PostgreSQL System Architecture
- Architectural Summary
- Shared Memory
- Inter-process Communications
- 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
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
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:
Creating and Managing Databases
- Object Hierarchy
- Creating Databases
- Creating Schemas
- Schema Search Path
- Roles
- Users
- Groups
- Access Control
Introduction to PSQL
Introduction
- Conventions
- Connecting to PostgreSQL
- PSQL Command Line Parameters
- Entering PSQL Commands
- PSQL Meta-Commands
- PSQL SET Parameters
- Information Commands
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
- Tablespaces
- Roles
Security basics
- Authentication
- Authorization
- Levels of security
- pg_hba.conf file
- Users
- Object ownership
- Access control
- Application access parameters
SQL
- Data Types
- Tables
- SQL Queries
- Quoting
- Using SQL Functions
- Constraints
- Dropping or Removing Database Objects
- Views
- Sequences
- Indexes
Day 3:
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
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
Postgres Data Dictionary
- The System Catalog Schema
- System Information views/tables
- System Information Functions
Moving Data
- The COPY Command
- Examples
Day 4:
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
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
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
- Slony-I Replication, Architecture and Setup
- Switchovers and Failovers
- Replication Limitations
- Slony Replication Example
- Labs
Day 5:
Table Partitioning
- Partitioning
- Partitioning Methods
- When to Partition
- Partitioning Setup
- Partitioning Example
- Partitioning and Constraint Exclusion
- Caveats
- Lab
Monitoring
- Database Monitoring
- Database Statistics
- The Statistics Collector
- Database Statistic Tables
- Operating System Process Monitoring
- Current Sessions and Locks
- Log Slow Running Queries
- Disk Usage
- Postgres Enterprise Manager
- PEM - Features
- PEM - Architecture
- Lab
Add on Utilities – Extension
- What are Extension Modules?
- Building Extension Modules
- Installing Extension Modules
- Module Index
- Other resources
Programme scope, delivery arrangements and suitability are confirmed during enquiry.