Postgresql Administrator Training

Essential skills for a Postgresql administrator include the ability to optimise data storage, and locations, improve query performance via index management and query plan analysis as well as setting up a robust backup policy and implementing replication. All of these skills are covered in our Postgresql database administrator course.

Whether you have database admin experience in another database or are new to database administration, this course will provide you with the skills you need to successfully manage your Postgresql cluster.

Is anything missing in the course outline that you want to be covered? Let us know. We are happy to customise our courses to suit your needs. For pricing and details please see the Postgresql administrator training page on our main site.

Introduction to PostgreSQL

• A Brief History of PostgreSQL
• Installation
• Architectural Fundamentals
• Using the psql terminal-based client

Advanced SQL

• Views
• Sequences
• 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

Backup & Fail Over

• Backing up Postgresql
• Replication (log shipping)
• Read replicas
• Hot standbys and failover

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