PostgreSQL Administration

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

• Multicolumn 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