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
Course Outline :: PostgreSQL Administration::
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
Creating and Managing Databases
Object Hierarchy
Creating Databases
Creating Schemas
Schema Search Path
Roles
Users
Groups
Access Control
Course Outline :: PostgreSQL Administration::
Day 2:
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
Course Outline :: PostgreSQL Administration::
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
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
Course Outline :: PostgreSQL Administration::
Day 3:
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
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
Course Outline :: PostgreSQL Administration::
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
Day 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
Table Partitioning
Course Outline :: PostgreSQL Administration::
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.