ARIEL TECHNOLOGIES INDIA PVT LTD

IT TRAINING & DEVELOPMENT

postgresql training course outline

Introduction 

History of PostgreSQL

Major Features

New Features of PostgreSQL 9.2

Multi Version Concurrency Control

Write-Ahead Logging

Architectural Overview

Limits
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
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
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
Creating and Managing Databases

Object Hierarchy

Creating Databases

Creating Schemas

Schema Search Path

Roles

Users

Groups

Access Control
Postgres Data Dictionary

The System Catalog Schema

System Information views/tables

System Information Functions
Introduction to PSQL

Introduction

Conventions

Connecting to PostgreSQL

PSQL Command Line Parameters

Entering PSQL Commands

PSQL Meta-Commands

PSQL SET Parameters

Information Commands

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
Security Basics

Authentication

Authorization

Levels of security

pg_hba.conf file

Users

Object ownership

Access control

Application access parameters
SQL

Data Types

Tables

SQL Queries

Quoting

Using SQL Functions

Constraints

Dropping or Removing Database Objects

Views

Sequences

Indexes

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

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
Moving Data

The COPY Command

Examples