PostgreSQL Fundamentals

Module 1: Understanding Relational Databases (Concept + PostgreSQL Context)

• What an RDBMS is
• Databases vs Schemas
• Tables, Views, and Materialized Views
• Keys and Relationships (PK, FK, Constraints)
• Basic Normalization
• Functions vs Stored Procedures (PostgreSQL differences)
• ODBC & JDBC Connection Fundamentals
  • Drivers
  • Connection Strings
  • Authentication Models

Lab:

• Explore schema structure
• Create tables with primary and foreign keys
• Create a simple function
• Connect via psql and client tool

Module 2: Understanding SQL in PostgreSQL

• SELECT, FROM, WHERE
• Sorting (ORDER BY)
• JOIN Types
• UNION vs UNION ALL
• Nested Queries
• Basic Aggregation
• Intro to Query Plans (EXPLAIN)

Lab:

• Multi-table joins
• Nested subqueries
• Filtering & sorting real data
• EXPLAIN basic query

Module 3: Installation & Environment Differences

• PostgreSQL 16 Architecture
• Windows vs RHEL vs Cloud Deployment Differences
• Package-based Install (Linux)
• Windows Installer Overview
• Managed Services Overview (AWS Direction)

Lab:

• Walkthrough of Installation Structure
• Directory Layout
• Service Management

Module 4: Configuration Deep Dive

• postgresql.conf (Core Tuning Parameters)
• pg_hba.conf (Authentication Rules)
• postgres.auto.conf (Managed Changes)
• Reload vs Restart
• Parameter Categories (Memory, WAL, Logging)

Lab:

• Modify logging level
• Adjust memory parameter
• Configure pg_hba rule
• Reload configuration safely

Module 5: Authentication & Security

• Authentication Methods (md5, scram, peer, trust)
• Kerberos Overview (Conceptual)
• Encryption (SSL/TLS Basics)
• Windows Domain-Joined Considerations
• Role Management and Privileges

Lab:

• Create roles
• Configure authentication rule
• Enforce password policy
• Enable SSL (concept demo)

Module 6: Replication & Storage

• Physical vs Logical Replication
• WAL Fundamentals
• Replication Slots
• Storage Layout
• Table Partitioning Basic
• PostGIS Overview (Extensions Model)

Lab:

• Enable extension (PostGIS overview demo)
• Create partitioned table
• Inspect WAL activity (conceptual demo)

Module 7: Performance Optimization

• Index Fundamentals
• EXPLAIN and EXPLAIN ANALYZE
• Common Tuning Mistakes
• Memory Settings Overview
• Monitoring Key Views (pg_stat_*)

Lab:

• Create index
• Compare query plans
• Identify slow query causes

Module 8: Backup & Restore

• pg_dump vs pg_basebackup
• Logical vs Physical Backups
• Restore Workflow
• Backup Validation
• Common Restore Failures

Lab:

• Perform logical backup
• Restore into new database
• Validate integrity

Module 9: Troubleshooting & Logs

• PostgreSQL Logging System
• /var/log/postgresql
• /var/log/messages (Linux)
• Windows Logging Differences
• Log Levels
• Common Failure Scenarios:
  • Connection Refused
  • Authentication Failed
  • Disk Full
  • Replication Lag

Lab:

• Simulate connection failure
• Adjust log level
• Investigate error message
• Resolve permission issue

Module 10: Capstone Operational Exercise

• Simulated Scenario:
• Install or configure instance
• Adjust authentication
• Create database & schema
• Load data
• Query data
• Create index
• Perform backup
• Restore to new database
• Troubleshoot introduced issue
  • Connection refused
  • Authentication failed
  • Disk full
  • Replication lag

Labs:

• Simulate connection failure
• Adjust log level
• Investigate error message
• Resolve permission issue

Module 11: Capstone Operational Exercise

• Install or configure instance
• Adjust authentication
• Create database & schema
• Load data
• Query data
• Create index
• Perform backup
• Restore to new database
• Troubleshoot introduced issue