Course Code: pstgrethl
Duration: 28 hours
Prerequisites:

-

Overview:

-

Course Outline:

PostgreSQL Administration (Day 1)

 

 

  • Architecture overview

 

  • Configuring the Server

 

  • Setting up and Managing Users

 

  • Overview of Database Object Types

 

  • Setting Permissions & Security

 

  • Querying the Database

 

  • Monitoring the Database

 

  • Ensuring Atomicity and Isolation

 

  • Migrating Data to Another System

 

  • Working with PostreSQL Logs

 

Performance Optimization (Day 2)

 

 

  • Reducing I/O with HOT

 

  • Locking & Concurrency

 

  • Evaluating the Design of the Database

 

  • Optimizing SQL Queries with EXPLAIN

 

  • Inspecting the Workload

 

  • Benchmarking and Profiling

 

  • Optimzing Joins

 

  • Using Indexes Efficiently

 

  • Memory management

 

PostgreSQL Replication & Backup (Day 3)

 

 

  • Logical & Physical backups

 

  • PITR

 

  • Transactional Log archiving

 

  • Overview of PostgreSQL Replication

 

  • High Availability in PostgreSQL

 

  • Streaming Replication

 

  • Managing Clusters Using repmgr

 

  • Running Queries on Database in Hot Standby

 

  • Synchronous Replication

 

  • File based Log Shipping

 

  • Trigger-based Logical Replication with Slony & Londiste

 

  • Connection Pooling

 

  • Upgrading PostgreSQL

 

High Availability (Day 4)

 

 

  • PgPool