PostgreSQL Database Administrator
The open-source database behind modern applications and every cloud, from installation to Patroni high availability.
About this program
Why this career pays off
PostgreSQL has become the default database for new applications, SaaS products, fintech and government platforms, and every cloud sells it as a managed service. Companies moving off expensive licences need administrators who can run it properly: backups that restore, replication that fails over and queries that stay fast. This program takes you from a fresh installation to production operations in 28 hands-on modules, ending with a full production lab. It pairs naturally with our Oracle Database Architect track for a two-database profile.
Watch: is this program right for you?
What you'll learn, module by module
A complete, no-surprises view of the curriculum. Click any module to expand.
01 Introduction to PostgreSQL
- History, major features and use cases
- Open-source ecosystem
- Terminology and common object names
- Architecture overview
02 PostgreSQL System Architecture
- Client/server and process-based architecture
- Postmaster, backend and background processes
- Shared and per-process memory
- Statement processing, read and write paths
- Checkpoints, commit processing and WAL
03 Physical Database Architecture
- PGDATA and data directory layout
- Relation files, forks and tablespaces
- WAL directory
- Page layout, tuple storage and TOAST
04 PostgreSQL Installation
- OS prerequisites, users and permissions
- YUM/DNF, APT, RPM and source installation
- initdb and cluster initialisation
- pg_ctl, systemd and pg_controldata
05 psql and Administration Utilities
- Connecting and command-line parameters
- Meta-commands and information commands
- Variables, history and output control
- Administration utilities
06 PostgreSQL Configuration
- postgresql.conf and postgresql.auto.conf
- Viewing and changing parameters, ALTER SYSTEM
- Connection, security and authentication settings
- Memory, WAL, checkpoint and planner settings
- Logging and background writer settings
07 Creating and Managing Databases
- Object hierarchy, databases and templates
- Schemas and the search path
- Tables, constraints, sequences, views and materialized views
- Indexes, functions, procedures and extensions
- Tablespaces and table partitioning
08 Users, Roles and Security
- Users, roles and role attributes
- Database, schema and object privileges; GRANT and REVOKE
- Default privileges and ownership
- pg_hba.conf, authentication methods and SCRAM
- SSL, row-level security and best practices
09 SQL for PostgreSQL DBAs
- Data types
- SELECT, INSERT, UPDATE, DELETE, TRUNCATE
- Joins, subqueries and CTEs
- GROUP BY, ORDER BY, views and sequences
- Constraints and partitioning basics
10 Data Dictionary and System Catalogs
- pg_catalog and information_schema
- System catalog tables and views
- Statistics and session monitoring views
- Object metadata and size information
11 Moving and Loading Data
- COPY and \copy
- CSV and text import and export
- Bulk loading
- Migration considerations
12 Transactions, MVCC and Locking
- Transactions, COMMIT and ROLLBACK
- Isolation levels
- MVCC, tuple versions and dead tuples
- Table and row locks, blocking sessions
- Deadlock detection and prevention
13 Routine Database Maintenance
- VACUUM, VACUUM ANALYZE and VACUUM FULL
- ANALYZE and optimizer statistics
- Autovacuum
- Table and index bloat
- Transaction ID wraparound and freeze management
14 Backup and Recovery
- Logical and physical backups
- pg_dump, pg_restore, pg_dumpall and pg_basebackup
- Backup formats and large databases
- Verification and restore strategies
- Recovery planning, RPO and RTO
15 WAL Archiving and Point-in-Time Recovery
- WAL concepts and archiving
- Archive and recovery configuration
- Recovery targets and restore points
- WAL replay and PITR planning
16 pgBackRest
- Architecture and repositories
- Full, differential and incremental backups
- WAL archiving and retention
- Validation and restore operations
17 SQL Performance Tuning
- Query planner and execution plans
- EXPLAIN and EXPLAIN ANALYZE
- Sequential, index and bitmap scans; join methods
- Sorts and parallel queries
- Indexing strategies and slow query identification
- pg_stat_statements and optimisation methodology
18 PostgreSQL Performance Tuning
- Tuning methodology
- CPU, memory, storage and I/O tuning
- Shared buffers, work memory and effective cache size
- Checkpoint, WAL and autovacuum tuning
- Planner settings and OS-level considerations
19 Monitoring and Troubleshooting
- Health, session and connection monitoring
- Long-running queries, blocking and deadlocks
- Wait events, disk usage and WAL growth
- Checkpoint and autovacuum monitoring
- CPU, memory and I/O wait troubleshooting
- Logs and production incident analysis
20 Streaming Replication
- Physical and streaming replication
- Primary and standby architecture, WAL sender and receiver
- Asynchronous and synchronous replication
- Hot standby, replication slots and cascading
- Standby creation, monitoring and lag
21 Replication Operations and Failover
- Planned switchover and unplanned failover
- Standby promotion and timelines
- Split-brain prevention
- Rejoining an old primary with pg_rewind
- Missing WAL, slot issues and lag troubleshooting
22 High Availability with Patroni and etcd
- HA concepts and Patroni architecture
- Patroni configuration and the distributed configuration store
- etcd architecture, quorum and leader election
- Automatic failover, planned and manual switchover
- Replica reinitialisation, monitoring and HA troubleshooting
23 Connection Pooling with PgBouncer
- Connection architecture and overhead
- Pooling concepts and PgBouncer architecture
- Session, transaction and statement pooling
- Pool sizing and monitoring
24 Monitoring and Observability Tools
- pg_stat views and pg_stat_statements
- PostgreSQL logging and pgAdmin
- Prometheus, postgres_exporter and Grafana
- PEM overview, alerting and capacity monitoring
25 Upgrade and Migration
- Minor and major version upgrades
- Planning and compatibility checks
- pg_upgrade
- Dump-and-restore and logical-replication migration
- Ora2Pg overview and post-upgrade validation
26 Production DBA Troubleshooting
- Slowness, high CPU, memory and I/O wait
- Disk saturation and connection exhaustion
- Blocking, deadlocks and long transactions
- WAL growth, replication and backup failures
- Autovacuum, startup and Patroni failover issues
- Production RCA methodology
27 Production DBA Health Checks
- Daily checks: replication, backup, disk, WAL, connections
- Long-running SQL, locks and autovacuum health
- Table and index growth, capacity planning
- Security checks, weekly and monthly routines
28 Final Hands-on Production Lab
- Install, create and secure a cluster
- Backup, restore and PITR
- SQL tuning, lock and deadlock troubleshooting
- Streaming replication, promotion and pg_rewind
- Patroni/etcd HA with automatic failover and switchover
- PgBouncer, pgBackRest and monitoring
- Production troubleshooting, health checks and RCA
Who this program is for
- Graduates (IT or non-IT) who want an in-demand open-source database career
- Oracle or SQL Server DBAs adding PostgreSQL to their profile
- Support engineers, testers and system administrators moving into database roles
- Developers who want to own the database layer
Roles you can target after
- PostgreSQL Database Administrator
- Database Reliability Engineer
- Cloud Database Engineer (RDS, Azure Database, OCI PostgreSQL)
- Migration Specialist (Oracle to PostgreSQL)
- Remote DBA Support Specialist
Tools & technologies covered
PostgreSQL 15/16 psql pgAdmin pg_dump/pg_restore pg_basebackup pgBackRest WAL archiving streaming replication pg_rewind Patroni etcd PgBouncer pg_stat_statements Prometheus postgres_exporter Grafana pg_upgrade Ora2Pg Linux
Common questions
SHA Cloud Career Path ?
Foundation ->Cloud Administration ->Cloud Support / Junior Cloud Engineer ->Cloud Engineer ->Senior Cloud Engineer->Cloud / Solutions Architect
Database Architect path?
Database Foundation:→ Junior DBA / Database Support→ DBA→ Senior DBA / Database Engineer→ Database Architect
Can I directly work as Database Architect?
No, not at all. Below is the path you have to follow:
Database Foundation:→ Junior DBA / Database Support→ DBA→ Senior DBA / Database Engineer→ Database Architect
Can I joined directly as Cloud Architect?
No, Not at all.
Foundation ->Cloud Administration ->Cloud Support / Junior Cloud Engineer ->Cloud Engineer ->Senior Cloud Engineer->Cloud / Solutions Architect
Don't Know Which IT Career Is Right for You?
You don't need to choose a technology before understanding the career. Your IT Career Map:
Graduate->Understand your background & strengths->Choose a direction->If you enjoy...Infrastructure, systems, servers, then a possible path is Cloud / Infrastructure-> If you enjoy...Databases, data, backend systems, then a possible path is Database / Cloud DBA. If you enjoy...Programming + mathematics + analytics, then a possible path is Data / AI. If you enjoy...Automation + infrastructure, then a possible path is DevOps / Cloud Engineering. If you enjoy...Security & systems, then a possible path is Cloud Security / Cybersecurity. If you enjoy...Multiple cloud platforms, then a possible path is Multi-Cloud / Gen & Agentic AI and on ......Final steps once you decide: Foundation → Specialisation → Projects → Interview Preparation → Job Search _> Got placement. So you need one mentor for this guidance who himself works in the IT Industry that to minimum of 10- 12 years.