Introduction to PostgreSQL Administration
Schedules

EnterpriseDB products are based upon PostgreSQL, the worlds most advanced and only truly open source database capable of handling high transaction and heavy reporting load applications.

Prerequisites:
Participants should have a 2-3 months of experienced in any Database.

Course Outline:

Part 1: Introduction
  • History of PostgreSQL
  • Major Features
  • New Features of PostgreSQL 9.2
  • Multi Version Concurrency Control
  • Write-Ahead Logging
  • Architectural Overview
  • Limits
    •  

    Part 2: PostgreSQL System Architecture

  • Architectural Summary
  • Shared Memory
  • Inter-process Communications
  • 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
    •  

    Part 3: Installation

  • OS User & Permissions
  • Installation
  • Setting environment variables
  • Clusters
  • Creating a database cluster
  • Starting and Stopping the Server (pg_ctl)
  • Connect to the server using psql
    •  

    Part 4: 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
  • Vacuum Cost Settings
  • Autovacuum Settings
    •  

    Part 5: Creating and Managing Databases

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

    Part 6: Postgres Data Dictionary

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

    Part 7: Introduction to PSQL

  • Introduction
  • Conventions
  • Connecting to PostgreSQL
  • PSQL Command Line Parameters
  • Entering PSQL Commands
  • PSQL Meta-Commands
  • PSQL SET Parameters
  • Information Commands
    •  
Part 8: pgAdmin III
  • Registering a server
  • Viewing and Editing Data
  • Query Tool
  • Databases
  • Languages
  • Schemas
  • Domains
  • Functions
  • Sequences
  • Tables
  • Columns
  • Constraints
  • Indexes
  • Maintenance
  • Rules
  • Triggers
  • Types
  • Views
  • Table spaces
  • Roles
    •  

    Part 9: Security Basics

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

    Part 10: SQL

  • Data Types
  • Tables
  • SQL Queries
  • Quoting
  • Using SQL Functions
  • Constraints
  • Dropping or Removing Database Objects
  • Views
  • Sequences
  • Indexes
    •  

    Part 11: 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
    •  

    Part 12: 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
    •  

    Part 13: Moving Data

  • The COPY Command
  • Examples