Course Overview
This PostgreSQL Admin training is for system administration and covers Postgres administration, setup and configuration including clustering, point-in-time recovery and performance tuning.
Course Prerequisites
Understanding of PostgreSQL and Database concepts is recommended.
Outline
Here's a closer look at the nuts and bolts of this course:
Introduction to PostgreSQL
- A Brief History of PostgreSQL
- Getting Started
- Installation
- Architectural Fundamentals
- Using the Psql terminal-based client
PostgreSQL SQL Language specifics
- Creating a Database
- Accessing a Database
- Creating a New Table
- Populating a Table With Rows
- Querying a Table
- Joins Between Tables
- Aggregate Functions
- Updates
- Deletions
Advanced SQL
- View
- Foreign Keys
- Transactions
- Window Functions
- Inheritance
Indexes
- Multi-column Indexes
- Indexes and ORDER BY
- Combining Multiple Indexes
- Unique Indexes
- Indexes on Expressions
- Partial Indexes
- Setting up a Partial Index to Exclude Common Values
- Setting up a Partial Index to Exclude Uninteresting
Values
- Setting up a Partial Unique Index
- Operator Classes and Operator Families
- Examining Index Usage
- Full-Text Search
Transaction Isolation and Concurrency
- SQL Transaction Isolation Levels
- Read Committed Isolation Level
- Serializable Isolation Level
- Serializable Isolation versus True Serializability
- Explicit Locking
- Table-Level Locks
- Table-level lock modes
- Conflicting lock modes
- Row-Level Locks
- Deadlocks
- Advisory Locks
- Using EXPLAIN
Server Set-up and Operation
- The PostgreSQL User Account
- Creating a Database Cluster
- Starting the Database Server
- Server Start-up Failures
- Client Connection Problems
- Shutting Down the Server
Server Configuration
- Setting Parameters
- File Locations
Security
- Preventing Server Spoofing
- Encryption Options
- Secure TCP/IP Connections with SSL
- Using client certificates
- SSL Server File Usage
- Creating a Self-Signed Certificate
- Connections and Authentication
- Connection Settings
- Security and Authentication
Resource Consumption
- Memory
- Kernel Resource Usage
- Cost-Based Vacuum Delay
- Background Writer
- Asynchronous Behavior
- Write Ahead Log
- Settings
- Checkpoints
- Archiving/ Log shipping
- Point-in-time-recovery
- Query Planning
- Planner Method Configuration
- Planner Cost Constants
- Genetic Query Optimizer
- Other Planner Options
Error Reporting and Logging
- Where To Log
- When To Log
- Message severity levels
- What To Log
- Using CSV-Format Log Output
- Run-Time Statistics
- Query and Index Statistics Collector
- Statistics Monitoring