PostgreSQL Administration Workshop

In this training you will learn the detailed architecture of the PostgreSQL and continue with the installation. Configuration, security, performance management and high availability features will be discussed in detail. Therefore, you will be able to install, administer and maintain PostgreSQL databases after having attended this 5 day training.

Target Audience

Database Administrators

Prerequisites

SQL knowledge will be required. Having some basic experiences in Linux operating systems will be beneficial although it is not required.

 

About the instructor Cuneyt Yilmaz

Cuneyt is working as senior instructor and consultant with specialty in Analytics, Business Intelligence, Data Management and Performance Tuning. He is a certified trainer for Microsoft and Oracle, and he also delivers training on MongoDB and PostgreSQL.

Cuneyt is based in Istanbul but has been working with customers in 35 countries across the Nordics and EMEA over the last 15 years. He combines top technology skills with his kindness and strong pedagogical skills. We dare to say, nobody leaves Cuneyt`s classroom disappointed! 

Course outline:

Module 1: Introduction

  • History of PostgreSQL
  • Major Features
  • New Features of PostgreSQL
  • Multi Version Concurrency Control
  • Write-Ahead Logging
  • Architectural Overview
  • Limits

Module 2: PostgreSQL System Architecture

  • Architectural Summary
  • Shared Memory
  • Statement Processing
  • Utility Processes
  • Disk Read Buffering
  • Write Buffering
  • Background Writer Cleaning Scan
  • Commit & Checkpoint
  • Physical Database Architecture
  • Data Directory Layout
  • Installation Directory Layout
  • Page Layout 

Module 3: Creating and Managing Databases

  • Object Hierarchy
  • Creating Databases
  • Creating Schemas
  • Schema Search Path
  • Roles, Users & Groups
  • Access Control

Module 4: Postgres Data Dictionary

  • The System Catalog Schema
  • System Information views/tables
  • System Information Functions

Module 5: Configuration

  • Setting PostgreSQL Parameters
  • Access Control
  • Connection Settings
  • Security and Authentication
  • Settings
  • Memory Settings
  • Query Planner Settings
  • WAL Settings
  • Log Management
  • Background Writer Settings
  • Statement Behavior

Module 6: Security

  • Authentication
  • Authorization
  • Levels of security
  • pg_hba.conf file
  • Users
  • Object ownership
  • Access control
  • Application access parameters

Module 7: Backup and Recovery & Point-in Time Recovery

  • Backup Types
  • SQL Dump
  • Cluster Dump
  • Offline Copy Backup
  • Continuous Archiving
  • pg_basebackup
  • Point-In Time Recovery
  • pg_upgrade

Module 8: Routine Maintenance

Explain and Explain Analyze
Table Statistics
Updating Planner Statistics
Vacuuming
Scheduling Auto Vacuum
Preventing Transaction ID Wraparound Failures
The Visibility Map
Routine Reindexing

Module 9: Table Partitioning

  • Partitioning
  • Partitioning Methods
  • When to Partition
  • Partitioning Setup
  • Partitioning Example
  • Partitioning and Constraint
  • Exclusion

Module 10: Database Monitoring

  • Database Statistics
  • The Statistics Collector
  • Database Statistic Tables
  • Operating System Process Monitoring
  • Current Sessions and Locks
  • Log Slow Running Queries
  • Disk Usage

Module 11 : Postgresql Indexes

  • B-tree Indexes
  • GIN Indexes
  • BRIN Indexes
  • HASH Indexes

Module 12 : Postgresql Upgrades

  • Using pg_update 

Module 13 : Postgresql Replication (repmgr)

  • repmgr installation
  • repmgr configuration
  • repmgr parameter file
  • repmgr executables

Module 14 : Postgresql Extensions