#PostgreSQL DBA

PostgreSQL DBA has seen a rapid growth of opportunities in the recent times. A PostgreSQL DBA (Database Administrator) specializes in managing PostgreSQL databases for performance and reliability. They handle tasks like installation, configuration, backup, recovery, and performance tuning. Learning PostgreSQL offers strong career opportunities in open-source database management.

✅ PostgreSQL Installations, Issues
✅ Database Design & Schemas
✅ Backup, Restore & PITR
✅ High Availability & Replication
✅ Performance Tuning, Tools
✅ Security, Roles & Access
✅ Monitoring (pgAdmin, Scripts)
✅ Upgrades, Patching & Migrations
✅ End-to-End Real-Time DBA Project
✅ 1:1 Mentorship, Interview Guidance

Module 1: PostgreSQL DBA

Chapter 1: Introduction to PostgreSQL

Overview of PostgreSQL
History and Evolution of PostgreSQL
Features, Versions and Benefits of PostgreSQL
PostgreSQL Architecture

Chapter 2: Installation

Installing PostgreSQL on Windows
Installing PostgreSQL on Linux using Yum repository and RPM
Installing PostgreSQL on Linux using Source Code
Installing PostgreSQL on Linux using Contrib Module

Chapter 3: Configuration File & Directory Layout

Data Directory
Configuration Files (postgresql.conf, conf, pg_ident.conf)
Other Physical Files
Database Directory Layout
Base Directory

Chapter 4: Database Cluster

PostgreSQL Cluster (Initdb, Start/Stop/Restart/Reload)
Start and stop using
PostgreSQL Cluster Demo on Windows
PostgreSQL Cluster Demo on Linux

Chapter 5: Process & Memory Architecture

Postmaster Process
Utility Processes
Memory Segments & Memory Components

Chapter 6: Create Objects (Database/User/Schema) and Privileges

Create/Drop Database
Create/Drop User
Create/Drop Schema and Search Schema Path
Grant/Revoke Privileges on Database Objects

Chapter 7: Tools (psql & pgAdmin) and catalog

Using psql command line interface (CLI)
pgAdmin(GUI) : Practical Use
g_catalog : Realtime Usage
Information_Schema & Medata Audits

Chapter 8: CRUD Operations

CRUD Introduction
Create Table, Data Types, Constraints, Functions and Operators
Retrieve Data in PostgreSQL
Update Records in a Table
Delete Records in a Table
Truncate

Chapter 9: Tablespaces

Tablespace Introduction
Default Tablespace (pg_default & pg_global)
Local & Global Tablespaces
Create/Drop Tablespaces
Temporary Tablespace

Chapter 10: Backup and Restore

Introduction to PostgreSQL Backups
Backup using pg_dump and psql utilities
Restore using pg_restore and psql utilities
Offline Backups
Online Backups
pg_basebackup utility

Chapter 11: Advanced Backup & Restore

pgBackRest
Point-In-Time-Recovery (PITR)

Chapter 12. MVCC (Multi-Version Concurrency Control) in PostgreSQL

What is MVCC in PostgreSQL?
How does MVCC work in PostgreSQL?
Key Data Structures in MVCC
Benefits of MVCC in PostgreSQL
Transaction ID Wraparound

Chapter 13. Database Maintenance, Vacuum and Analyze

Introduction to Maintenance
Vacuum
Analyze
Statistics
Clustering
Auto-Vacuum

Chapter 14: Joins and Constraints

Introduction on Joins and Constraints
Join Types (Inner, Equi and Outer)
Constraints (Primary Key, Unique, Foreign Key and NOT NULL ..)
SERIAL Column
ER Diagram

Chapter 15: Indexing and Types

Introduction to Indexes
Basic Index Types (B-Tree, Hash and Function/Expression)
Advanced Index Types (GiST, SP-GiST, GIN and BRIN).

Chapter 16: Performance Tuning – Part 1

Database Tuning (Vacuum, Analyze, Partitioning ..)
Query Tuning (EXPLAIN, ANALYZE, Indexing, Optimize JOINs, Optimize Subqueries)

Chapter 17: Performance Tuning – Part 2

Configuration Tuning

(shared_buffers, effective_cache_size, work_mem, maintenance_work_mem and etc…)

Monitoring and Maintenance (pg_stat_statements and pg_stat_user_tables).
Hardware Tuning (CPU, Memory, Storage and Network)

Chapter 18: Data Loading

Moving, Copying, Export, Import, copy ..
pgloader
Foreign Data Wrappers (FDW)

Chapter 19: Extensions

What are Extensions in PostgreSQL?
Types of Extensions in
Examples of Extensions in
How to Install/Uninstall Extensions in PostgreSQL

Chapter 20: High Availability and Replication

High Availability Architecture
Master/Primary and Slave/Standby
HA step-by-step configuration using Streaming Replication
Manual Failover and Switchover
promote command

Chapter 21-22: Advanced -1 High Availability Concepts

HA step-by-step setup using Streaming Replication
HA step-by-step using Logical Replication
Automatic Failover & Switchover

Chapter 23-24: Advanced -2 High Availability Tools and Software

pgpool: A PostgreSQL-specific connection pooling and replication tool
repmgr: A tool for managing PostgreSQL replication clusters
Patroni: A PostgreSQL-specific high availability tool that provides automated failover and switchover