Course Code: postgreadminbspk
Duration: 28 hours
Course Outline:

Introduction

  • PostgreSQL History
  • Architecture overview and naming conventions
  • Installation and initial configuration

Administration

  • Connecting to the Database
  • Overview of client tools
    • PgAdmin III / IV
    • psql
  • Configuring the Server
  • Setting up and Managing Users
  • Overview of Database information schema
  • Permissions & Security
  • Monitoring the Database
  • Ensuring Atomicity and Isolation
  • Backing up and Restoring PostreSQL
    • pg_dump / pg_dumpall
    • Setting up Point in Time Recovery
  • Migrating Data to Another System
  • Working with PostreSQL Logs

The SQL Language

  • Indexes: BTREE, BRIN, GIST, GIN
  • Full Text Search
  • Transactions and isolation levels
  • Full Text Search

Performance Optimization

  • Reducing I/O with HOT
  • Locking & Concurrency
  • Parameters affecting performance
  • Evaluating the Design of the Database
  • Optimizing SQL Queries with EXPLAIN
  • Extensions for performance
    • auto_explain
    • pgbench
    • Statements monitoring
  • Inspecting the Workload
  • Benchmarking and Profiling
  • Query Optimization
  • Using Indexes Efficiently

PostgreSQL Replication

  • Overview of PostgreSQL Replication
  • High Availability in PostgreSQL
  • Streaming / Binary Replication
  • Running Queries on Database in Hot Standby
  • Asynchronous / Synchronous Replication
  • File based Log Shipping
  • Connection Pooling
  • Failover

PL/SQL

  • Basic syntax
  • Creating procedures / functions / anonymous blocks
  • Sample usage in daily administration