Configuration and Monitoring
PostgreSQL 16
16
Copyright
© Postgres Professional, 2017–2026
Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Igor Gnatyuk
Translated by: Elena Sharafutdinova
Photo: Oleg Bartunov (Phu Monastery and Bhrikuti Peak, Nepal)
Use of Course Materials
Non-commercial use of course materials (presentations, demonstrations) is
allowed without restrictions. Commercial use is possible only with the written
permission of Postgres Professional. It is prohibited to make changes to the
course materials.
Feedback
Please send your feedback, comments and suggestions to:
edu@postgrespro.ru
Disclaimer
Postgres Professional assumes no responsibility for any damages and
losses, including loss of income, caused by direct or indirect, intentional or
accidental use of course materials. Postgres Professional company
specifically disclaims any warranties on course materials. Course materials
are provided “as is”, and Postgres Professional company has no obligations
to provide maintenance, support, updates, enhancements, or modifications.
2
For Administrators
DBA1, 3 days
Basic Course
on PostgreSQL Administration
DBA2, 4 days
Configuration
and Monitoring
QPT, 3 days
Query Optimization
DBA3, 2 days
Backup
and Replication
SQL Basics
Any textbook of your choice
PGPRO, 3 days
Features of
Postgres Pro Enterprise
DBS, 2 days
Security Basics
Prior knowledge of the basics of the SQL language is required to complete
all courses. There is no course on the SQL language in our line of courses,
but there are many books and other educational resources that can help you
master SQL.
For administrators, we offer the following courses.
The DBA1 basic course provides general information about the PostgreSQL
architecture, installation, server configuration and management. It also
contains overview of access control, backup and replication mechanisms.
The DBA2 course focuses on setting up configuration parameters based on
an understanding of the internals of the server. It also talks about monitoring
the server and using feedback for iterative parameter tweaking.
The DBA3 course is devoted to backup, as well as physical and logical
replication configuration and scenarios for their use. It also provides an
overview of the methods and difficulties of building highly available, scalable
clusters.
The DBS course discusses the setup of client connections and access
control in the DBMS.
The QPT course examines in detail the mechanisms for planning and
executing queries, discusses the configuration of instance parameters
related to performance, and examines the options for finding problematic
queries and optimizing them.
The PGPRO course discusses additional features provided by the Postgres
Pro Enterprise DBMS.
3
For Backend Developers
DEV1, 4 days
Basic Course
for Backend Developers
DEV2, 4 days
Advanced Course
QPT, 3 days
Query Optimization
SQL Basics
Any textbook of your choice
PGPRO, 3 days
Features of
Postgres Pro Enterprise
DBS, 2 days
Security Basics
For backend developers, we offer the following courses.
The DEV1 course is the basic course. It provides general information about
the architecture of PostgreSQL and the use of basic database objects;
server-side programming in SQL and PL/pgSQL languages using basic data
types (including the composite type and arrays), as well as client-side
interactions.
The DEV2 course examines the features of the server’s internal structure
that affect the development of application code, and comprehensively
discusses extensibility: the ability to extend server mechanisms with your
own code, which allows you to use PostgreSQL to solve a wide variety of
tasks.
The DBS, QPT, and PGPRO courses are intended for both developers and
administrators.
Developers may also be interested in administration courses if there is a
need to study the internal structure of PostgreSQL in more detail, as well as
in the case when there is no dedicated administrator role on the project.
4
About DBA2 Course
Duration: 4 days
Prior knowledge requirements
experience with Unix
familiarity with PostgreSQL equivalent to the DBA1 course
What competences will be acquired
setting configuration parameters based on server internal architecture
server monitoring using feedback for iterative parameter tuning
setting localization related parameters
extension management and introduction to server upgrade
The goal of this course is to provide a thorough understanding of
PostgreSQL internal architecture. This knowledge is essential for setting
parameters and use of monitoring to refine initial settings.
These topics are covered in detail in the book PostgreSQL Internals by Egor
Rogov:
This course also covers a range of other administration topics not included
in the DBA1 course.
5
Materials Used
Pre-configured virtual machine
Xubuntu 24.04 OS
PostgreSQL 16
documentation in English
Course handouts
Student’s Guide
presentations, demonstrations, practice tasks and practice keys
(in html and pdf)
If you are taking the course on your own, start with the Student’s Guide.
Among other things, it shows where to download and how to use the virtual
machine and other materials. All course materials, including the Student’s
Guide, are available at: https://postgrespro.com/community/courses/DBA2
Completing practice tasks is very important to gain the basic skills in working
with PostgreSQL. Try to complete the practice tasks yourself first, and then
review the provided keys, even if the practice task did not raise questions.
Practice keys may contain additional information that is not mentioned in
presentations and demonstrations.
Course handouts (presentations, demonstrations, practice tasks and keys)
are available in two formats. Html is good for browsing and copying code
snippets. Pdf is split in pages and is more convenient for printing.
7
Course Outline and Schedule
Each training day is approximately 6 hours + lunch
Each lesson usually consists of
presentation and demonstration: ~30–60 minutes
practice: ~30–40 minutes, including a break
8
Day 1
MVCC
01. Isolation
02. Page Layout and Row Versions
03. Snapshots
04. HOT Updates and Page Pruning
9
Day 2
MVCC (Continued)
05. Vacuuming
06. Autovacuuming
07. Freezing
WAL
08. Buffer Cache
09. Write-Ahead Log
10
Day 3
WAL (Continued)
10. Checkpoint
11. WAL Tuning
Locks
12. Object-Level Locks
13. Row-Level Locks
11
Day 4
Locks (Continued)
14. Locks in RAM
Administration
15. Managing Extensions
16. Localization
17. Server Upgrade
