home > training > POSTSQL: PostgreSQL Fundamentals

PostgreSQL Fundamentals

POSTSQL

$2,395

  • 2 Days
  • Replay™ Class Recordings Included

Dates Available
Class Time
Guaranteed
to Run
Attend
Live Online
Rewatch with
Replay™
 
Apr 2 - Apr 3
Replay™ AvailableThis class date includes Interface Replay™ class recordings, available for online viewing 1 hour after each class day ends.
Attend Live Online
Rewatch with Replay™
Register
x
Course:
  • This field is for validation purposes and should be left unchanged.

Course Description

This two-day intensive course provides a practical, hands-on foundation in PostgreSQL administration, security, performance tuning, and operational troubleshooting.

Designed for technical professionals transitioning into PostgreSQL environments, the course combines relational database fundamentals with real-world administration tasks, ensuring participants leave with applied, job-ready skills.

Day 1 establishes core relational database and SQL foundations in a PostgreSQL context, followed by deep dives into installation, architecture, and configuration. Participants gain practical experience configuring authentication, tuning core parameters, and understanding deployment differences across Windows, Linux, and managed cloud environments.

Day 2 focuses on operational readiness: security models, role management, replication concepts, performance optimisation, backup and restore strategies, and structured troubleshooting techniques. The course concludes with a capstone operational simulation where attendees install, configure, secure, tune, back up, restore, and troubleshoot a PostgreSQL instance.

The emphasis throughout is practical competence rather than theory. Each concept is reinforced with guided lab exercises to ensure participants can confidently administer PostgreSQL environments in production contexts.

Outline

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
Audience

This course is ideal for:

  • Graduate or junior database administrators
  • SQL Server or Oracle DBAs transitioning to PostgreSQL
  • DevOps engineers supporting PostgreSQL workloads
  • Infrastructure engineers managing Linux or Windows database servers
  • Technical professionals preparing for operational PostgreSQL responsibilities
Prerequisites

To ensure participants gain maximum value, attendees should have:

Required Knowledge

  • Basic understanding of relational database concepts (tables, keys, relationships)
  • Familiarity with basic SQL syntax (SELECT, WHERE, JOIN)
  • Comfort working in a command-line or terminal environment
  • General understanding of server environments (Windows or Linux)

Recommended (but not mandatory)

  • Prior exposure to any RDBMS (SQL Server, MySQL, Oracle, etc.)
  • Basic understanding of networking concepts (ports, authentication, encryption)
  • Some exposure to database backup concepts
  • Familiarity with development or DevOps workflows
What You Will Learn

By the end of the course, participants will be able to:

  • Install and configure PostgreSQL in Windows, Linux, or cloud environments
  • Implement authentication and role-based security
  • Tune key configuration parameters safely
  • Create and manage indexes for performance
  • Interpret query execution plans
  • Implement backup and restore workflows
  • Diagnose and resolve common operational failures
  • Understand replication fundamentals
  • Operate PostgreSQL in a production-ready manner