PostgreSQL Administration
Basic course
© Postgres Professional, 2015–2022
Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov
Translated by Alexander Meleshko
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.
Please send your feedback, comments and suggestions to:
In no event shall Postgres Professional company be liable for any damages
or loss, including loss of profits, that arise from direct or indirect, special or
incidental 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.
DBA2, 4 days
Configuration and monitoring
QPT, 2 days
Query optimization
DBA3, 2 days
Backup and replication
For administrators
DBA1, 3 days
Basic course
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 with which you
can master SQL.
For administrators, we offer the following courses.
The DBA1 course (this one) is a basic overview course. It provides general
information about the architecture of PostgreSQL, the installation process,
basic configuration and server management. It also considers basic
administration tasks and access control issues. Lastly, it provides an
overview of backup and replication mechanisms.
The DBA2 course focuses on determining appropriate 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
The DBA3 course is devoted to backup, as well as physical and logical
replication configuration and scenarios for its use. It also provides an
overview of the methods and difficulties of building highly accessible,
scalable clusters.
The QPT course examines in detail the mechanisms for planning and
executing queries, discusses instance parameters related to performance,
and examines the options for finding problematic queries and optimizing
For backend developers
DEV1, 4 days
Basic backend development
DEV2, 4 days
Advanced course
QPT, 2 days
Query optimization
For backend developers, we offer the following courses.
The DEV1 course is the introduction to backend development. 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 fronted 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
The QPT course is 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.
About the DBA1 course
Duration: 3 days
Prior knowledge requirements
minimal familiarity with databases and SQL
Unix familiarity
What competences will be acquired
general information about the PostgreSQL architecture
installation, basic configuration, server management
data organization at logical and physical levels
basic administration tasks
user and access management
understanding backup and replication
Materials used
Pre-configured virtual machine
Xubuntu OS
PostgreSQL 13
documentation in English
Course handouts
presentations, demonstrations, practical assignments and assignment keys
(in html and pdf)
reference materials, such as Unix commands used in the course,
and a chart of the main tables of the system catalog, with psql commands
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
Completing practical assignments is very important to gain the basic skills
in working with PostgreSQL. Try to complete the assignments yourself first,
and then review the provided keys, even if the assignment did not raise
questions. Assignment keys may contain additional information that is not
mentioned in presentations and demonstrations.
Course handouts (presentations, demonstrations, practical assignments and
keys) are available in two formats: html is good for browsing and copying
code snippets, while pdf is split in pages and is more convenient for printing.
Additional reference materials will help you quickly find the information you
Course outline and schedule
Each training day is approximately 6 hours + lunch (1 hour)
Each lesson usually consists of:
presentation and demonstrations: ~20–60 minutes
practice: ~20–30 minutes, including a break
Day 1
Basic tools
01. Server installation and management
02. Using psql
03. Server configuration
04. PostgreSQL overview
05. Isolation and MVCC
06. Vacuuming
Day 2
Architecture (continued)
07. Buffer cache and WAL
Data organization
08. Databases and schemas
09. System catalog
10. Tablespaces
11. Low level
12. Monitoring
Access control
13. Roles and attributes
Day 3
Access control (continued)
14. Privileges
15. Row level security
16. Connection and authentication
17. Overview
18. Overview