PostgreSQL Fundamentals for Database Administrators

Module 1: Architecture and core files

• Process model postmaster, backends, background workers
• Memory and storage shared buffers, WAL, checkpoints
• Cluster, database, schema and the data directory layout
• Configuration basics postgresql.conf, pg_hba.conf, pg_ident.conf

Module 2: Installation and instance setup

• Packages and versions initdb, locales, and extensions at a glance
• Starting and controlling the server systemd and pg_ctl
• Roles and authentication methods scram, md5, peer, SSL
• Network settings listen_addresses, port, and client authentication

Module 3: Tools, SQL for DBAs, and access patterns

• psql essentials meta commands, listing objects, formatting, \copy
• Admin focused SQL privileges, transactions, and search_path
• Schema and database management organizing objects for teams
• Connection management pooling overview with pgbouncer

Module 4: Backup and recovery foundations

• Physical backups base backup with pg_basebackup
• Logical backups pg_dump and pg_restore strategies
• WAL archiving and point in time recovery overview
• Backup validation, retention, and restore drills

Module 5: Performance and maintenance basics

• Reading plans with EXPLAIN and EXPLAIN ANALYZE
• Index types btree, hash, gin, gist and common use cases
• Autovacuum, vacuum, analyze, and bloat control
• Safe starting points work_mem, shared_buffers, effective_cache_size

Module 6: Monitoring and observability

• Server logs log_line_prefix, log levels, and rotation
• pg_stat views activity, locks, replication, IO insights
• pg_stat_statements and tracking slow SQL
• Alerting and baseline dashboards for capacity and errors

Module 7: Security and compliance

• Role design login roles, groups, and least privilege
• Row level security and masking approaches
• SSL or TLS configuration and client certificates
• Auditing options pgaudit and structured logging practices

Module 8: Replication and high availability overview

• Streaming replication roles primary and standby
• Replication slots, hot standby, and failover concepts
• Read scaling patterns and safe promotion
• Upgrade paths pg_upgrade and logical replication for migrations