Comprehensive PostgreSQL Administration
Schedules

EnterpriseDB products are based upon PostgreSQL, the worlds most advanced and only truly open source database capable of handling high transaction and heavy reporting load applications.

Prerequisites:
Participants should have attended the “Intorduction to PostgreSQL Administration” Training

Course Outline:

Part 1: Introduction & Architecture
  • Architectural Summary
  • Process Architecture
  • Connect Request
  • Backend Spawning
  • Respond to Client
  • Full Process Architecture
  • Disk Read Buffering
  • Writing Buffers
  • Shared Buffer & Write-Ahead Log
  • Physical Database Architecture
  • Data File Architecture
  • Page Layout
  • Transaction Logging (WAL) and Archiving
    •  

    Part 2: 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 3: 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_dumpables
  • Non-Durable Settings
  • Labs
    •  

    Part 4: 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
    •  

    Part 5: Table Partitioning
  • Partitioning
  • Partitioning Methods
  • When to Partition
  • Partitioning Setup
  • Partitioning Example
  • Partitioning and Constraint Exclusion
  • Caveats
  • Lab
    •  

    Part 6: Connection Pooling

  • Pgpool-II
  • Pgpool-II Features
  • Install and Configure pgpool-II
  • Pgpool II Modes
  • Starting/Stopping pgpool-II
  • Pgpool-II Example
  • Pgbouncer
  • Pgbouncer Connection Modes
  • How Connections are Established
  • Setup and Manage Pgbouncer
  • Pgbouncer Example
  • Lab
    •  

    Part 7: 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
    •  

    Part 8: Procedural Languages

  • PostgreSQL Procedural Languages
  • Introduction to PL/PGSQL
  • How it works
  • PL/pgSQL Block Structure
  • Declaring Variables
  • Writing Executable Statements
  • Declaring Function Parameters
  • Control Structures
  • Exception Handling
  • PL/pgSQL Cursors
  • Triggers
  • Examples & Lab
    •  

    Part 9: Add on Utilities – Extension

  • What are Extension Modules?
  • Building Extension Modules
  • Installing Extension Modules
  • Module Index
  • Other resources