DBA 2: PostgreSQL Advanced
This advanced multi-day training is designed for database administrators who want to strengthen their knowledge of PostgreSQL internals and best practices.
Upcoming dates
Dates are being finalised — email us and we'll let you know the moment registration opens.
What you'll learn
The course is designed to equip you with the essential skills to install, operate, maintain, and monitor PostgreSQL for reliable and efficient database management.
The course covers
- PostgreSQL architecture and processes
- Optimal configuration techniques
- MVCC and the transactional engine
- Physical backups and Point-in-Time Recovery (PITR)
- Partitioning and connection pooling
Prerequisites
You must either have completed our DBA 1 (PostgreSQL Essentials) course or possess practical experience with PostgreSQL daily operations — creating databases, managing roles, performing routine maintenance tasks, and exporting/importing data.
You should also be comfortable with SQL transactions and able to manage PostgreSQL services and configurations on Linux systems.
Familiarity with performance monitoring and basic shell scripting is also recommended.
For details see the course terms & conditions.
Syllabus
1PostgreSQL architecture and Write-Ahead Logging
This chapter introduces PostgreSQL’s internal architecture, focusing on how it manages processes, memory, and durability. Highlights include:
- The main background processes and their responsibilities
- Shared and per-process memory areas , and their tuning
- How PostgreSQL organises files in PGDATA
- The role of Write-Ahead Logging ( WAL ) in ensuring data consistency and recovery
2MVCC, VACUUM and isolation levels
This chapter explores PostgreSQL’s Multi-Version Concurrency Control (MVCC) model and how it ensures both consistency and concurrency. Highlights include:
- How PostgreSQL manages row versioning with system columns such as xmin, xmax, and ctid
- The impact of isolation levels (READ COMMITTED, REPEATABLE READ, SERIALIZABLE) on transaction consistency
- The internals of VACUUM and autovacuum : cleaning dead tuples, trigger conditions, and tuning options
- Strategies to prevent bloat and protect against transaction ID wraparound
3Point-in-Time Recovery and WAL Archiving in PostgreSQL
This chapter introduces the principles and practices of Point-in-Time Recovery (PITR) in PostgreSQL. Highlights include:
- Continuous archiving and its role in recovery
- Configuring WAL archiving for PITR
- Performing file system backups with pg_basebackup or manual methods
- Executing a point-in-time recovery and controlling recovery targets
- Best practices for backups, restore testing, and minimising downtime
4Declarative table partitioning in PostgreSQL
This chapter introduces declarative table partitioning in PostgreSQL, a feature designed to simplify the management of large datasets. Highlights include:
- An overview of the partitioning methods: list, range, and hash
- How to define multi-column and nested (sub-)partitions
- Managing partitions : attach, detach, and default partitions
- Performance considerations and planning-time behaviour
- Common limitations and best practices for effective partitioning
5Connection pooling in PostgreSQL with PgBouncer
This chapter explores how PostgreSQL can efficiently handle large numbers of client connections through connection pooling, focusing on PgBouncer. It covers:
- Why and when to use a connection pooler
- Comparison of pooling modes : session, transaction, and statement
- Installation, configuration, and monitoring of PgBouncer
- Advanced usage : delegated authentication, query cancellation, and multi-instance scaling
Frequently asked questions
What prerequisites do I need for DBA2?
You should have completed DBA1 (PostgreSQL Essentials) or have practical experience with PostgreSQL daily operations, SQL transactions, service management, and basic shell scripting.
Will I receive a certificate?
Yes. Participants who attend all five sessions receive a Certificate of Attendance from Data Egret.
How is DBA2 different from DBA1?
DBA1 covers PostgreSQL fundamentals (installation, configuration, day-to-day tasks). DBA2 goes deeper into architecture, WAL internals, MVCC, physical backups with PITR, partitioning, and connection pooling.
Are sessions recorded?
No. Sessions are live and interactive, with real-time Q&A. They are not recorded.
Can my company book this for a group?
Yes. We offer group bookings and dedicated sessions. Contact us at contact@dataegret.com for details.
Which session should I choose - May or September?
Both sessions cover identical content. Choose whichever fits your schedule better.
Instructor
Close to 15 years in the database space, specialising in client support, maintenance and architectural consulting. A recognised PostgreSQL contributor and a contributor to pgBackRest.
Ready to book, or still deciding?
Live, small-group, taught by the people who run PostgreSQL in production. Questions first? Write to us — no sales pitch.