Course Outline
Course Outline
Introduction & Architecture
History of PostgreSQL
Major Features
Limitations
Architectural Overview
PostgreSQL Terminology
PostgreSQL System Architecture
Architectural Summary
Database Clusters
Physical Database Architecture
Data File Architecture
Database File Layout
Database Page Layout
Process Architecture
Connect Request
Backend Spawning
Multi-Version Concurrency Control (MVCC)
Write-Ahead Logging
Installation
Installation
Creating a database cluster
Starting and Stopping the Server (Windows)
Starting and Stopping the Server (Other)
Lab Exercise - Installation
Install PostgreSQL from source
Create a database cluster
Start the database server
Connect to the server using psql
Stop the database server
Using PSQL Tool
Introduction to Migration Studio
Overview
GUI
edborascan
Performance Tuning
Explain and Explain Analyze
Forcing Query Plan Selection
Helping the Optimizer
Using Views
Creating Rules with Views
Indexing
Constraints
Clustering Rows
Routine Database Maintenance Tasks
Backup & Recovery
Log Management
Routine Vacuuming
Recovering Disk Space
Vacuum Full
Updating Planner Statistics
Preventing Transaction ID Wraparound
Failures
Routine Reindexing
Backup & Recovery
Dump & Restore
File System Backup - copying DATA folder
pg_start_backup and pg_stop_backup
PITR - Point in Time Recovery
Creating a base backup
Archive command
Recovery file parameters
Lab to test PITR
High Availability & Replication
Why Use Replication
Replication
Introduction to Slony
Security
Levels of security: pg_conf, schemas and
users and table level.
USERS; GROUPS; ROLES
Object Ownership
Access control
Application Access
pg_hba.conf
Schemas and Search Paths
Lab to create USERS, SCHEMAS, ROLES,
Alter SEARCH_PATH, GRANT and REVOKE
privileges
PostgreSQL Partitioning and Tablespaces
Partition Methods
Partition Setup
Table space Management
Monitoring & Statistics
Database Logs
OS Process Monitoring
Statistics Collector
Statistics Parameters
Statistics Views and Functions
Timing
