PostgreSQL DBA Free Coaching
DBA Training Program
// 5-week structured training Β· 19 sessions Β· hands-on labs
5
Weeks
19
Sessions
100+
Topics
β
Future Free
Week 01
Lab Setup, Linux & PostgreSQL Fundamentals
Session 01
Lab Setup & Storage Configuration
βΎ
Pre-requisites
- Install VirtualBox on Windows (Do your self)
- Install PgAdmin4 on Windows (Do your self)
- Configure Oracle Linux 9.6 VM (will share link)
Linux VM Setup
- Create Oracle Linux 9.6 VM
- VM Hardware Sizing for PostgreSQL
- Add Multiple Virtual Disks
Storage Configuration
- Create Partitions
- Create Filesystems
- Mount Filesystems
- Configure /etc/fstab
PostgreSQL Directory Layout
- Create Mount Points: /pgData, /pgWal, /pgArch, /pgBackup, /pgLog
Session 02
Linux Administration for DBAs
βΎ
- Basic Linux Commands
- File and Directory Management
- User and Group Management
- Permissions and Ownership
- Process Management
- Service Management using systemctl
- Network Commands
- Disk and Memory Monitoring
Session 03
PostgreSQL 17 Installation
βΎ
Overview
- PostgreSQL Overview and Versions
Installation Methods
- RPM Installation
- DNF/YUM Installation
- Source Code Installation
Database Initialization
- initdb
- Custom WAL Location
- PostgreSQL Service Configuration
Validation
- Start PostgreSQL
- Connect using psql
- Connect using PgAdmin4
Session 04
PostgreSQL Architecture
βΎ
Memory Architecture
- Shared Buffers
- WAL Buffers
- Work Memory
- Maintenance Work Memory
Process Architecture
- Postmaster
- Backend Processes
- Checkpointer
- Background Writer
- WAL Writer
- Autovacuum
Physical Storage Layout
- Base Directory
- Global Directory
- WAL Directory
- Tablespaces
Session 05
PostgreSQL Configuration Files
βΎ
- postgresql.conf
- postgresql.auto.conf
- pg_hba.conf
- pg_ident.conf
- Reload vs Restart
Week 02
Administration & Security
Session 06
Startup, Shutdown
βΎ
- PostgreSQL Startup Process
- Smart Shutdown
- Fast Shutdown (default)
- Immediate Shutdown
Session 07
Database Administration
βΎ
- Creating Databases
- Creating Schemas
- Schema Search Path
- Roles and Users
- Access Control
Session 08
Tablespaces Management
βΎ
- pg_default
- pg_global
- Custom Tablespaces
- Move Objects Between Tablespaces
- Tablespace Monitoring
Session 09
Security
βΎ
- pg_hba.conf
- Authentication Methods
- pg_ident.conf
- .pgpass
- SSL/TLS Setup
Session 10
Vacuum and Analyze
βΎ
- MVCC Concepts
- Dead Tuples
- VACUUM
- VACUUM FULL
- ANALYZE
- Autovacuum
Week 03
Backup & Recovery
Session 11
WAL Archiving
βΎ
- WAL Fundamentals
- Archive Mode
- Archive Command
Session 12
Logical Backup and Restore
βΎ
- pg_dump
- pg_dumpall
- Backup Formats
- pg_restore
- Restore using psql
Session 13
Physical Backup and Restore
βΎ
- pg_basebackup
- Full Cluster Backup
- Database Refresh on New Server
Session 14
PITR β Point-in-Time Recovery
βΎ
- PITR Concepts and Architecture
- PITR Demonstration (hands-on)
Session 15
Full Recovery & Disaster Recovery
βΎ
- Full Database Recovery
- Disaster Recovery Scenarios
Week 04
Replication, Failover
Session 16
Streaming Replication
βΎ
- Primary Configuration
- Replica Configuration
- Base Backup for Replica
- Verify Replication
Session 17
Replication Administration
βΎ
- Replication Slots
- Monitoring Replication
- Async Replication
- Sync Replication
- Convert from ASYNC to SYNC
Session 18
Failover and pg_rewind
βΎ
- Manual Failover
- Promote Replica
- pg_rewind
- Rejoin Old Primary as Replica
Week 05
Upgrades
Session 19
PostgreSQL Upgrades & Final Lab
βΎ
- Minor Version Upgrade
- Major Version Upgrades
- pg_upgrade
- Upgrade using pg_dump/pg_restore
- Upgrade Validation
- Final End-to-End Lab
Future Batch
Advanced Topics β Free Training
// Topics reserved for the next advanced batch Β· access is free
EXPLAIN / EXPLAIN ANALYZE
Index Types & Tuning
Table Partitioning
Performance Tuning
PostgreSQL Extensions
PgBouncer
pgmetrics
pgcollector
pgbadger
Repmgr
Patroni
Maintenance Operations
Oracle β PostgreSQL (Ora2Pg)
Advanced Data Migration
Logical Replication
Multi-Node HA Architectures
