LinuxCBT.com

Syllabus

Focus: PostgreSQL DBMS Engine

Duration: 17-Hours

  • 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 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
    •  

LinuxCBT PostgreSQL Edition

  •  
DEMO