PostgreSQL DBA

Module 1: Database Basics – RDBMS

• Topics: Relational model, normalization, ACID properties, indexing, transactions.

• Lab: Design a normalized schema and run basic CRUD operations.

Module 2: PostgreSQL Installation and Configuration

• Topics: Installing on Linux/Windows, postgresql.conf, pg_hba.conf, port and connection settings.

• Lab: Install PostgreSQL, configure custom port, enable remote access.

Module 3: PostgreSQL Upgrade

• Topics: Minor vs major upgrades, pg_upgrade, logical replication for migration.

              Lab: Perform a version upgrade using pg_upgrade and test compatibility.

Module 4: PostgreSQL Backup and Restore

• Topics: Physical vs logical backups, pg_dump, pg_basebackup, WAL archiving.

• Lab: Take a backup, drop a table, and restore it.

Module 5: User Creation and Security

• Topics: Roles, privileges, password policies, SSL, row-level security.

• Lab: Create users with different roles, test access control.

Module 6: Troubleshooting and Performance Tuning

• Topics: Query optimization, EXPLAIN, indexes, vacuuming, connection pooling.

• Lab: Analyze slow queries, add indexes, tune parameters.

Module 7: PostgreSQL Replication

• Topics: Streaming replication, logical replication, failover, replication slots.

• Lab: Set up master-slave replication, simulate failover.

Module 8: PostgreSQL Monitoring

• Topics: pg_stat_statements, pgBadger, Prometheus + Grafana, alerting.

• Lab: Set up monitoring dashboard and analyze query stats.