Course Code: bsppostg
Duration: 21 hours
Prerequisites:
  • There are no specific requirements needed to attend this course. It is recommended to start with this training if you have never taken a formal training of PostgreSQL
Overview:

This training is designed for Database Administrators and Architects who want to learn PostgreSQL from basics.

You will learn PostgreSQL architecture, installation, designing as well as how to perform backup and recovery databases in PostgreSQL. 

Course Outline:

Contents from PostgreSQL Essentials:

  • Introduction
  • System Architecture and Internals 
    • Process Architecture
    • Memory Internals
    • Storage Architecture 
  • Installations
    • Downloading PostgreSQL 
    • Preparing System for Installation
    • Install PostgreSQL 
    • Create, Start, Stop a Database Server
    • Viewing Information 
  • PSQL Tool
    • Connecting to a Database
    • Executing SQL
    • psql Meta Commands
  • PGAdmin4
    • Connecting to a Database
    • Basic Tasks using GUI
  • Database Designing
    • Database Users
    • Creating and Managing Databases
    • Schemas
    • Tables
    • Data Types
    • Sequences
    • Views
    • Materialized Views
  • Database Backups 
    • Logical or SQL Dump Backups
    • Physical Backups - Offline
    • Physical Backups - Online
    • Archiving
  • Restoring Backups
    • Restoring SQL Dumps
    • Restoring Offline Backups
    • Point in Time Recovery
    • Partial Restore
  • Proactive Maintenance 
    • Database Statistics
    • Data Fragmentation and Vacuuming
    • Data Re-organization
    • Automatic Maintenance Configuration 
    • Re-indexing 
  • System Parameters
    • Parameter File
    • Changing Server Parameters
    • Resource Parameters
    • Planner Parameters
    • Logging Parameters
    • Transaction Logging Parameters

Contents from PostgreSQL:

What is PostgreSQL?

  • A Brief History of PostgreSQL
  • Conventions
  • Further Information
  • Bug Reporting Guidelines
  • Process Architecture
  • Memory Internals
  • Storage Architecture 

Introduction to PostgreSQL

  • Installation and Creating Database
  • The SQL Language
  • Advanced Features

The SQL Language

  • SQL Syntax
  • Data Definition
  • Data Manipulation
  • Queries
  • Data Types
  • Functions and Operators
  • Foreign Data Wrappers
  • Type Conversion
  • Indexes
  • Triggers
  • Full Text Search
  • The Information Schema
  • PL/pgSQL - SQL Procedural Language
  • Concurrency Control
  • Performance Tips
  • How the Planner Uses Statistics

Server Administration

  • Source Code vs Distribution-Provided Packages
  • Installation from Source Code
  • Installation from Source Code on Windows
  • Deployment of Binaries Obtained by Compiling Source Code
  • Server Setup, Operation
  • Database Physical Storage
  • Filesystem Durability Requirements and Required Mount Options
  • Server Configuration
  • Special Considerations for Container-Based Deployments
  • Client Authentication
  • Database Roles
  • Managing Databases
  • Localization
  • Routine Database Maintenance Tasks
  • Backup and Restore
  • Recovery Configuration
  • Monitoring Database Activity
  • Monitoring Disk Usage
  • Reliability and the Write-Ahead Log
  • Regression Tests
  • Installation of Third-Party Server Extensions

High Availability, Load Balancing, and Replication

  • Brewer's CAP Theorem
  • Synchronous vs Asynchronous Replication
  • Log Shipping (Warm Standby)
  • Streaming Master-Slave Replication (Hot Standby)
  • Connection Pooling and Synchronous Replication with Pgpool
  • Failover Configurations using DRBD
  • Replacing a Failed Server
  • Recovering from Network Partition

Performance Optimization

  • Monitoring queries
  • Monitoring database
  • Troubleshooting for performance issues
  • Database statistics
  • Query optimization