00 Lesson 00: PostgreSQL Administration / Basic Course
This page is extracted from the official PDF into native Markdown. Slide text, code and speaker notes are kept in reading order; the original PDF and HTML remain the layout references. HTML source: dba1_00_introduction.html Archive: DBA1-handouts-16-en.zip PDF pages: 9
Page 1: PostgreSQL Administration / Basic Course
Copyright
© Postgres Professional, 2017–2025 Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Aleksei Beresnev Translated by: Liudmila Mantrova, Alexander Meleshko, 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.
Page 2: For Administrators
{#source-figure-002 caption=“Source slide page 2: For Administrators”}
- DBA2, 4 days
Speaker notes
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 process, basic server configuration and management. It also considers basic administration tasks and provides an overview of access control, 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 tweaking.
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 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.
Page 3: For Backend Developers
{#source-figure-003 caption=“Source slide page 3: For Backend Developers”}
Speaker notes
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 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.
https://postgrespro.com/community/courses
Page 4: About the DBA1 Course
Duration: 3 days
- Prior knowledge requirements
- Basic knowledge of databases and SQL
- Unix fundamentals 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
- access control information
- understanding backup and replication
Page 5: Materials Used
Pre-configured virtual machine
- Xubuntu 22.04 OS
- PostgreSQL 16
- documentation in English
- Course handouts
- presentations, demonstrations, practice tasks and practice keys
- (in html and pdf)
- reference materials: Unix commands used in the course,
- chart of the main tables of the system catalog with psql commands
Speaker notes
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/DBA1
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.
Additional reference materials will help you quickly find the information you need.
Page 6: Course Outline and Schedule
Each training day is approximately 6 hours + lunch
- Each lesson usually consists of
- presentation and demonstration: ~20-60 minutes
- practice: ~20-30 minutes, including a break
Page 7: Day 1
Basic Tools
- Server Installation and Management
- Using psql
- Server Configuration
- Architecture
- PostgreSQL Fundamentals
- Isolation and MVCC
- Vacuuming
Page 8: Day 2
Architecture (Continued)
- Buffer Сache and WAL
- Data Organization
- Databases and Schemas
- System Catalog
- Tablespaces
- Low Level
- Administration
- Monitoring
Page 9: Day 3
Access Control
- Overview
- Backup
- Overview
- Replication
- Overview of Physical Replication
- Overview of Logical Replication