Course Objective

PostgreSQL - DBMS
Introduction - Topology - Features
Identify key systems to be used
Discuss key PostgreSQL features

Installation
Identify sources
Download & Install
Explore post-installation environment

'psql' - Terminal Monitor Usage
Discuss features and benefits
Connect to PostgreSQL instance
Explore usage

Access Control
Identify configuration
Explore default environment
Enable subnet access
Evaluate results

Log Configuration
Discuss features and benefits
Peruse default setup
Alter configuration to log simultaneously to multiple destinations
Generate log data
Evaluate results

Data Types
Enumerate supported data types
Discuss pros and cons of various types
Identify types in default configuration
Compare and contrast intra-category types

SQL - Data Definition Language (DDL) - Constraints
Use CREATE to generate objects
Use DROP to remove objects
Use ALTER to change structural definitions of ojbects
Define constraints on DB objects to suit storage requirements
Evaluate results
SQL - Data Manipulation Language (DML)
Populate tables with INSERT
Bulk-copy items to tables with COPY
Query tables using SELECT
Combine tables with JOINs
Define VIEWs on queries of interest
Quantify table data with Aggregate Functions
Modify table data using UPDATE
Remove table data using DELETE

Indexes
Discuss features and benefits
Explore defaults
Generate queries that hit and miss indices
CREATE TABLE IF NOT EXISTS indices
Evaluate results

PostgreSQL - Functions
Discuss features and benefits
Explore built-in functions
Apply functions to data sets
Nest functions
Evaluate results

Model: /var/log/messages
Create data model
Generate SQL schema
Transform & Import data
Perform queries
Write Perl script to parse & transform: /var/log/messages for importation
Install 'libpg' & Update Perl script to connect to PostgreSQL
Evaluate results

SQL Object Permissions
Extend access control discussions
Explore default permissions
Discuss PostgreSQL permissions-set
Use GRANT to assign & test various privileges
Use Revoke to unassign & test various privileges
Create test users and apply various permissions
Evaluate results

DBMS Archival & Restoration
Explore available tools & methods
Archive: DBs & Tables
Test restorations
Restore cross-platform to Windows
Restore cross-system to disparate distribution
Evaluate results

Security
Peruse default clear-text environment with: TCPDump
Construct SSH tunnels to secure transport layer
Initiate 'psql' sessions across SSH tunnels
Bolster security with SSL
Generate usage keys & update PostgreSQL configuration appropo
Test SSL communications
Intercept SSL traffic with: TCPDump for analysis
Evaluate results

DOWNLOAD