Course Outline

🔹 Day 1: PostgreSQL Installation, Configuration & Security

Session 1: PostgreSQL Architecture & Setup

• PostgreSQL architecture overview
• Installing PostgreSQL on Linux and Windows
• Configuration files: postgresql.conf, pg_hba.conf basics

Session 2: User Management and Security

• Roles, permissions, and authentication methods
• Managing users and groups with SQL and pgAdmin
• SSL/TLS configuration and connection security

Session 3: Backup and Recovery Strategies

• Logical backups using pg_dump and pg_restore
• Physical backups with pg_basebackup
• Point-in-time recovery (PITR) concepts and setup

Lab Activities:

• Install and configure PostgreSQL with secure settings
• Create roles, assign privileges, and configure access control
• Perform full and partial backups and practice restoring data

🔹 Day 2: Monitoring, Performance Tuning & Maintenance

Session 1: Monitoring PostgreSQL Instances

• Using pg_stat_activity, pg_stat_database, and system views
• Setting up and using monitoring tools: pgAdmin, pgBadger, and Prometheus + Grafana
• Logging best practices and analyzing logs

Session 2: Performance Tuning and Query Optimization

• Understanding PostgreSQL query planner and EXPLAIN plans
• Index types and usage (B-tree, GIN, GiST, BRIN)
• Vacuuming, autovacuum, and table bloat management

Session 3: Maintenance Tasks and Automation

• Routine maintenance commands and scheduling
• Partitioning and table management
• Using cron jobs and scripts for automation

Lab Activities:

• Monitor live database activity and identify slow queries
• Analyze query plans and optimize indexes
• Set up autovacuum tuning and schedule maintenance tasks

🔹 Day 3: Replication, High Availability & Troubleshooting

Session 1: Replication Techniques

• Streaming replication setup and management
• Logical replication and publication/subscription model
• Failover and switchover processes

Session 2: High Availability and Load Balancing

• Using Patroni, Pgpool-II, or repmgr for HA
• Connection pooling and load balancing strategies
• Backup and disaster recovery planning

Session 3: Troubleshooting and Best Practices

• Diagnosing common PostgreSQL issues
• Crash recovery and data corruption handling
• Security audits and compliance checks

Lab Activities:

• Configure streaming replication between primary and standby
• Simulate failover and recovery
• Perform troubleshooting exercises on simulated issues