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