PostgreSQL Administration

Duration: 2 days

This PostgreSQL Admin training is for system administration and covers Postgres administration, setup and configuration including clustering, point-in-time recovery and performance tuning.

Understanding of PostgreSQL and Database concepts is recommended.

Please find the course objectives below:

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
For an onsite course please contact us