PostgreSQL

Duration: 5 days

Students should have a basic understanding of database concepts

Please find the course objectives below:

Day 1

  • Installation
  • Architectural Fundamentals (Logical and Physical layout)
  • 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

Day 2

  • Physical Architectural
  • Logical Architectural
  • Starting, stopping and finding status of postmaster
  • Exploring utilities in Postgresql (process and server / postmaster)
  • Working with above said process related utilities
  • Schemas in Postgresql
  • Hands-on Exercise / implementation

Day 3

  • Concurrency control
  • Introduction
  • Transaction Isolation (Levels with hands-on)
  • Explicit Locking
  • Hands-on Exercise / implementation

Day 4

  • Configuration files in postgresql
  • Server configuration
  • Connectivity configuration
  • User auth. & privileges
  • Access Control
  • Performance Tips
  • Using EXPLAIN
  • EXPLAIN ANALYZE
  • Statistics
  • Explicit JOIN Clauses
  • Disable Autocommit
  • Use COPY
  • Remove Indexes
  • Remove Foreign Key Constraints
  • Run ANALYZE
  • Hands-on Exercise / implementation

Day 5

  • Routine Database Maintenance Tasks
  • Routine Vacuuming
  • Routine Reindexing
  • Understand Auto-vacuuming
  • Log File Maintenance
  • Backup and Restore
  • SQL Dump
  • Pg_dump & pg_dumpall
  • File System Level Backup
  • Setting up WAL archiving
  • Continuous Archiving and Point-In-Time Recovery (PITR)
  • Hands-on Exercise / implementation
For an onsite course please contact us