Module 1: Introduction InstallationL

• Database Basics and Database Types

• PostgreSQL: Advantages, Usage

• PostgreSQL as an Open Source Database

• PostgreSQL: History and Versions

• PostgreSQL: Real-time Usage

• PostgreSQL Installation on LINUX OS

• bin, data, and PATH Concepts

• Command Line Tools

• Post Installation: Environment Variables, Path, bin, and PGDATA Variables

Module 2: Server Architecture

• PostgreSQL Server Architecture

• Server Process and Client Process

• Postgres Program: Internal Process

• Postmaster Process and Forks

• Background Process, Connections

• Utility Processes: Bgwriter, WAL

• Checkpointer and StatsCollector

• Log Writer, Autovacuum Utilities

• Memory Segments and Usage

• Shared Buffer and WAL Buffer

• Dirty Data and Background Writer

• Background Process, Backend Process

Module 3: Cluster Creation

• initdb Utility Introduction

• Creating Cluster using initdb

• Changing Port Number of a New Cluster

Module 4: Database Creation and Maintenance

• Default Databases in Postgres: postgres, template0, template1

• Database Creation from Bash

• Database Creation from DB Level

Module 5: Schema Creation and Maintenance

• Default Schema: public

• Creating Schema

• Creating Table Inside Schema

• Setting Default Schema

• Moving Tables Across Schemas

Module 6: Tablespace Creation and Maintenance

• Tablespaces and Real-time Advantages

• pg_default, pg_global Tablespaces

• Creating and Modifying Tablespaces

• Creating Tables Inside Tablespace

Module 7: Users, Roles, and Privileges

• User Creation

• Assigning Privileges to User

• Dropping User

• Assigning Privileges to Role

• Assigning Role to User

• Privileges Management

Module 8: Performance Tuning

• User Creation

• Assigning Privileges to User

• Dropping User

• Assigning Privileges to Role

• Assigning Role to User

• Privileges Management

• Transactions and Lock Management

• Table Level Locks, Row Level Locks

• Query Blocking and Deadlocks

• Lock Queue, Lock Management Options

• Page Locks, Shared and Exclusive Locks

• pg_stat_activity

• pg_cancel_backend, pg_terminate_backend

• Vacuum, Vacuum Full, Freeze, Analyze

• Partitioning

• pg_badger – Log Analysis Tool

Module 9: Job Scheduling

• pg_cron Introduction

• Installing pg_cron

• Configuring pg_cron Parameters

• Scheduling Jobs

Module 10: Accessing Other Databases

• DBlink

• Foreign Data Wrapper (FDW)

Module 11: Backup & Restore - Part 1

• Backups: Types, Levels in Postgres

• Logical and Physical Backups

• Backup Tools: pg_dump, pg_dumpall

• Online and Offline Backups

• Logical Backups: Single Table, Multiple Tables, Single Database

• Logical Backup Options and Usage

• pg_backrest – Third-Party Backup Tool

Module 12: Backup & Restore - Part 2

• wal_level and archive_mode Options

• archive_command with File Formats

• WAL Archival Process

• Point-In-Time Recovery (PITR)

• pg_switch_wal, pg_basebackup Utilities

• Physical Backups and Continuous Archival

Module 13: Upgrade

• pg_upgrade Utility

• Checking Consistency

• Deleting Old Cluster

Module 14: Migration

• ora2pg Introduction

• Downloading Required Files

• Setting Migration Parameters

• Migrating Data

Module 15: High Availability & Disaster Recovery (HA-DR) with Postgres

• PostgreSQL Replication and Advantages

• WAL Sender, WAL Receiver, Startup

• pg_hba.conf Entries for Replication Host

• pg_basebackup Utility

• Single Master Replication Setup

• recovery.conf File and Configurations

• primary_conninfo and standby_mode

• Replication Setup Validations

• pg_stat_replication Monitoring

Module 16: Connection Pooling

• PgBouncer Introduction

• Installing PgBouncer

• Configuring PgBouncer

Module 17: Tools and Extensions

• pg_hint_plan

• pg_audit

• pg_stat_statements

• barman