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