10 Education and Certification

Documentation

Reading the documentation is indispensable for professional use of PostgreSQL. It describes all the database features and provides an exhaustive reference that should always be readily available. Here, you can get full and precise information firsthand: it is written by developers themselves and is carefully kept up-to-date at all times. The PostgreSQL documentation is available at www.postgresql.org/docs or postgrespro.com/docs.

At Postgres Professional, we have translated the whole PostgreSQL documentation set into Russian, including the latest version. It is available on our website: postgrespro.ru/docs.

While working on this translation, we also compiled an English-Russian glossary, published at postgrespro.ru/education/glossary. We recommend consulting this glossary when translating English articles into Russian to use consistent terminology familiar to a wide audience.

There are also French (docs.postgresql.fr), Japanese (www.postgresql.jp/document), and Chinese (postgres.cn/docs) translations provided by national communities.

Training Courses

We develop training courses for those who start using PostgreSQL or would like to improve their professional skills.

Courses for database administrators:

Courses for application developers:

An additional course about our primary product for those who have completed the administration and development courses:

The PostgreSQL and Postgres Pro documentation contains every detail about PostgreSQL, but the information is often scattered across different chapters, so you may have to carefully read it several times before you gain full understanding.

Training courses are intended to complement the documentation rather than replace it. They consist of separate modules that gradually explain a particular topic, focusing on important practical information. Courses can broaden your outlook, structure the previously gained bits of knowledge, and help you find your way around the documentation, should you need to quickly get some particular details.

Each course topic includes theory and practice. In most cases, theory includes both slides and a live demo on a real system. Students receive all course slides with extensive annotations, outputs of demo scripts, solutions to practical assignments, and additional reference materials on select topics.

Where and How to Take a Training

For non-commercial use and self-study, all course materials, including videos, are available on our website for free. You can find their Russian version at postgrespro.ru/education/courses.

The courses currently translated into English are published at postgrespro.com/community/courses.

You can also take these courses at a specialized training center under the supervision of an experienced lecturer. Upon completing the course, you will receive a certificate of completion. Authorized training centers are listed here: postgrespro.ru/education/where.

DBA1. Basic PostgreSQL administration

Duration: 3 days

Background knowledge required:

Basic knowledge of databases and SQL. Familiarity with Unix.

Knowledge and skills gained:

General understanding of PostgreSQL architecture. Installation, initial setup, server management. Logical structure and physical data layout. Basic administration tasks. User and access management. Backup, recovery, and replication.

Topics:

Basic toolkit

1. Installation and server management
2. Using psql
3. Configuration

Architecture

4. PostgreSQL overview
5. Isolation and multi-version concurrency control
6. Vacuum
7. Buffer cache and write-ahead log

Data management

8. Databases and schemas
9. System catalog
10. Tablespaces
11. Low-level

Administration tasks

12. Monitoring

Access control

13. Roles and attributes
14. Privileges
15. Row-level security
16. Connection and authentication

Backups

17. Overview

Replication

18. Overview

Course materials in English are available for self-study at postgrespro.com/community/courses/DBA1.

DBA2. Configuring and monitoring PostgreSQL

Duration: 4 days

Background knowledge required:

SQL fundamentals. Good command of Unix OS. Familiarity with PostgreSQL within the scope of the DBA1 course.

Knowledge and skills gained:

Setting up various configuration parameters based on the understanding of server internals. Monitoring server activity and using the collected data for iterative tuning of PostgreSQL configuration. Configuring localization settings. Managing extensions and getting started with server upgrades.

Topics:

Multi-version concurrency control

1. Transaction isolation
2. Pages and row versions
3. Data snapshots
4. HOT updates
5. Vacuum
6. Autovacuum item Freezing

Logging

7. Buffer cache
8. Write-ahead log
9. Checkpoints
10. WAL configuration

Locking

11. Object locks
12. Row-level locks
13. Memory locks

Administration tasks

14. Managing extensions
15. Localization
16. Server upgrades

Course materials in Russian are available for self-study at postgrespro.ru/education/courses/DBA2.

DBA3. Replication and backups

Duration: 2 days

Background knowledge required:

SQL fundamentals. Good command of Unix OS. Familiarity with PostgreSQL within the scope of the DBA1 course.

Knowledge and skills gained:

Taking backups. Setting up physical and logical replication. Recognizing replication use cases. Understanding cluster technologies.

Topics:

Backups

1. Logical backup
2. Base backup
3. WAL archive

Replication

4. Physical replication
5. Switchover to a replica
6. Logical replication
7. Usage scenarios

Cluster Technologies

8. Overview

Course materials in Russian are available for self-study at postgrespro.ru/education/courses/DBA3.

DEV1. Basic server-side application development

Duration: 4 days

Background knowledge required:

SQL fundamentals. Experience with any procedural programming language. Basic knowledge of Unix OS.

Knowledge and skills gained:

General information about PostgreSQL architecture. Using the main database objects. Programming in SQL and PL/pgSQL on the server side. Using the main data types, including records and arrays. Setting up client-server communication channels.

Topics:

Basic toolkit

1. Installation and server management, psql

Architecture

2. A general overview of PostgreSQL
3. Isolation and MVCC
4. Buffer cache and WAL

Data organization

5. Logical structure
6. Physical structure

Bookstore application

7. Application schema and interface

SQL

8. Functions
9. Procedures
10. Composite types

PL/pgSQL

11. Overview and programming structures
12. Executing queries
13. Cursors
14. Dynamic commands
15. Arrays
16. Error handling
17. Triggers
18. Debugging

Access control

19. Access control overview

Backup

20. Logical backup

Course materials in English are available for self-study at postgrespro.com/community/courses/DEV1.

DEV2. Advanced server-side application development

Duration: 4 days

Background knowledge required:

General understanding of PostgreSQL architecture. Strong SQL and PL/pgSQL skills. Basic knowledge of Unix OS.

Knowledge and skills gained:

Understanding server internals. Using all PostgreSQL capabilities in application logic implementations. Extending database functionality to address specific tasks.

Topics:

Architecture

1. Isolation
2. Server internals
3. Vacuum
4. Write-ahead logging
5. Locks

Bookstore

6. Bookstore application 2.0

Extensibility

7. Connection pooling
8. Data types for large values
9. User-defined data types
10. Operator classes
11. Semi-structured data
12. Background processes
13. Asynchronous processing
14. Creating extensions
15. Programming languages
16. Aggregate and window functions
17. Full-text search
18. Physical replication
19. Logical replication
20. Foreign data

Course materials in Russian are available for self-study at postgrespro.ru/education/courses/DEV2.

QPT. Query Performance Tuning

Duration: 2 days

Background knowledge required:

Familiarity with Unix OS. Good command of SQL. Some knowledge of PL/pgSQL will be useful, but is not mandatory. Familiarity with PostgreSQL within the scope of the DBA1 course (for DBAs) or DEV1 (for developers).

Knowledge and skills gained:

In-depth understanding of query planning and execution. Performance tuning of the server instance. Troubleshooting query issues and optimizing queries.

Topics:

1. Airlines database

2. Query execution

3. Sequential scans

4. Index scans

5. Bitmap scans

6. Nested loop joins

7. Hash joins

8. Merge joins

9. Statistics

10. Query profiling

11. Optimization methods

Course materials in Russian are available for self-study at postgrespro.ru/education/courses/QPT.

PGPRO. Postgres Pro Enterprise Features

Duration: 2 days

Background knowledge required:

Familiarity with Unix OS. Good command of SQL. Some knowledge of PL/pgSQL will be useful, but is not mandatory.

Familiarity with PostgreSQL within the scope of the courses DBA1, DBA2 and QPT (for DBAs) or DEV1, DEV2 and QPT (for developers).

Knowledge and skills gained:

Proficiency with Postgres Pro Enterprise signature features.

Topics:

1. Editions and features

2. Installation, configuration, upgrade

3. Transaction management

4. CFS – compressed file system

5. Query optimization

6. Adaptive optimization

7. Performance analysis

8. Load reporting – pgpro_pwr

9. User profiles

10. Audit

11. Task planner

12. Backup – 1

13. Backup – 2

14. Backup – 3

15. Synchronized cluster – multimaster

Course materials in Russian are available for self-study at: postgrespro.ru/education/courses/PGPRO.

Professional Certification

The certification program, which was launched in 2019, is useful for both database professionals and their employers. Holding a certificate can give you an advantage when job hunting or negotiating your salary. Besides, it’s a good opportunity to get an impartial evaluation of your knowledge.

For employers, the certification program facilitates recruiting, enables verification of PostgreSQL expertise of the current employees, and provides a means to control the quality of knowledge received in external employee training or to check the competence of third-party vendors and partners.

PostgreSQL certification is currently available only for database administrators, but in the future we plan to launch certification programs for PostgreSQL application developers too.

We offer three levels of certification, all of which require you to pass several tests.

Professional level confirms the knowledge in the following fields:

• General understanding of PostgreSQL architecture.

• Server installation, working in psql, tuning configuration settings.

• Logical and physical data structure.

• User and access management.

• General understanding of backup and replication concepts.

To obtain a certificate, you must successfully pass a test on the DBA1 course. For new applicants, the PostgreSQL 13 test version is recommended.

Expert level additionally confirms the knowledge in the following fields:

• PostgreSQL internals.

• Server setup and monitoring, database maintenance tasks.

• Performance optimization tasks, query tuning.

• Taking backups.

• Physical and logical replication setup for various usage scenarios.

There are two certification pathways:

• Have the Professional level for PostgreSQL 13 and pass DBA2-13, DBA3-13 and QPT-13 tests (in any order).

• Have the Expert level for PostgreSQL 10 and pass the transition test Expert 10–13.

Master level additionally confirms practical skills required for PostgreSQL database administration.

To get a certificate, it is required to have a certificate of the Expert level and successfully pass a hands-on test. This certification is currently under development.

Create an account under postgrespro.ru/user and sign up for a certification test in your profile.

To pass a test, you should have:

• a good command of the corresponding courses and the documentation sections they refer to

• hands-on experience in working with PostgreSQL via psql

While taking the test, you can refer to our course materials and the PostgreSQL documentation, but usage of any other sources of information is prohibited.

Achieving a particular level is acknowledged by a certificate. Certificates do not expire, but they are tied to a specific PostgreSQL version and will become obsolete as that version is deprecated. So in several years you may want to take a test for a more recent PostgreSQL version.

To learn more about certification, visit postgrespro.ru/education/cert.

Academic Collaboration

Our company is committed to cultivating the next generation of database experts. This requires efforts at the earliest level of education, and it is only possible through collaboration with universities, colleges, and schools.

Academic License

For educational organizations, we provide a free academic license for the Postgres Pro Standard DBMS. To apply, send a request to academy@postgrespro.ru.

Student Internships

We set up student labs at universities to provide internship opportunities. Our experts help the students select a graduation thesis related to PostgreSQL, develop and defend it.

Competitions

Our company participates in organizing hackathons and various competitions for school and university students, including:

• the ”Postgres Pro DBMS” section at the IT-Planet Competition: challenge.braim.org/landing/postgres_contest,

• the Russian National Open-Source Project Competition: foss.kruzhok.org.

Students who are winners or finalists of competitions hosted by the company or with our involvement, as well as lecturers from partner universities, can take professional certification tests for free. To receive a code for the free certification test, contact us at certification@postgrespro.ru and attach a supporting document.

Academic Courses

We offer several academic courses produced in cooperation with professors from leading universities. All the courses can be used in educational institutions for free. Lecturers can use textbooks, slides, lecture videos, and other educational materials published on our website: postgrespro.ru/education/university.

Postgres Professional has contributed to several courses taught in such universities as Lomonosov Moscow State University, Higher School of Economics, Moscow Aviation Institute, Reshetnev Siberian State University of Science and Technology, and Siberian Federal University. Contact us if you are a university representative and would like to add database courses to the curriculum.

We also seek partnership with teachers and instructors who are ready to develop new original PostgreSQL courses. On our part, we provide all the required support and advice, edit the manuscripts and drive them to publication, as well as make arrangements for open lectures of the course authors in top Russian universities.

SQL Basics

Course participants will learn about PostgreSQL and will be able to start working with it right away; no prior training is required. Starting with simple SQL queries, students will gradually get to more complex constructs, learn about transactions and query optimization.

This course is based on the following textbook (published in Russian):

E. Morgunov, PostgreSQL. SQL Basics. St. Petersburg : BHV-Petersburg, 2018.

ISBN 978-5-9775-4022-3

Contents:

• Introduction

• Configuring the environment

• Basic operations

• Data types

• DDL fundamentals

• Queries

• Data manipulation

• Indexes

• Transactions

• Performance tuning

A soft copy of this book in Russian is available on our website: postgrespro.ru/education/books/sqlprimer.

This course includes 36 hours of lectures and hands-on training. The author has been teaching it at top universities in Moscow and Krasnoyarsk for several years. You can download the course materials in Russian at postgrespro.ru/education/university/sqlprimer.

Evgeny Morgunov, Ph.D in Technical Sciences, associate professor at the Informatics and Computer Science Department of Reshetnev Siberian State University of Science and Technology.

Evgeny lives in Krasnoyarsk. Before joining the university in 2000, he worked as a programmer for over 10 years, including developing a banking application system. He got to learn PostgreSQL in 1998. Being an advocate of using free open-source software in academic activities, he has initiated the use of PostgreSQL and FreeBSD operating system as part of the “Programming Technology”course. Evgeny is a member of the International Society for Engineering Pedagogy (IGIP). He has been using PostgreSQL in teaching for more than 20 years.

Database Technology Fundamentals

A modern academic course that combines in-depth theory with relevant practical skills of database design and deployment.

B. Novikov, E. Gorshkova, and N. Grafeeva, Database Technology Fundamentals. 2nd ed. Moscow : DMK Press, 2020.

ISBN 978-5-97060-841-8

The first part contains the key information about database management systems: relational data model, the SQL language, transaction processing.

The second part dives into the underlying database technology and its development trends. Some topics covered in the first part are discussed again at a deeper level.

Contents:

Part I. From Theory to Practice

• Introduction

• Some database theory

• Getting started with databases

• Introduction to SQL

• Database access management

• Transactions and data consistency

• Database application development

• Relational model extensions

• Various types of database systems

Part II. From Practice to Proficiency

• Database system architecture

• Storage structures and the main algorithms

• Query execution and optimization

• Transaction management

• Database reliability

• Advanced SQL features

• Database functions and procedures

• PostgreSQL extensibility

• Full-text search

• Data security

• Database administration

• Replication

• Parallel and distributed database systems

A soft copy of this book in Russian is available on our website: postgrespro.ru/education/books/dbtech.

This course offers 24 hours of lectures and 8 hours of hands-on training. It was delivered by Boris Novikov at the faculty of Computational Mathematics and Cybernetics of Lomonosov Moscow State University. You can download the course materials in Russian at postgrespro.ru/education/university/dbtech.

Boris Novikov, Dr. Sci. in Physics and Mathematics, professor at the Informatics Department of Higher School of Economics in St. Petersburg.

His academic interests mainly concern various aspects of designing, developing, and deploying database systems and applications, as well as scalable distributed systems for Big Data processing and analytics.

Ekaterina Gorshkova, Ph.D. in Physics and Mathematics.

An expert in designing high-load data-intensive applications. Her academic interests include machine learning, data-flow analysis, and data retrieval.

Natalia Grafeeva, Ph.D. in Physics and Mathematics, associate professor at the Informatics and Data Analysis Department of St. Petersburg State University.

Her academic interests include databases, data retrieval, Big Data, and smart data analysis. She is an expert in information system design, development, and maintenance, as well as in course design and teaching.

Books

PostgreSQL Internals

This book is for those who will not settle for a black-box approach when working with a database. Targeted at readers who have some experience with PostgreSQL, this book will also be useful for those who are familiar with another database system but switch over to PostgreSQL and would like to understand how they differ.

E. Rogov, PostgreSQL 14 Internals. Moscow : DMK Press, 2022

ISBN 978-5-6045970-4-0 (in English) ISBN 978-5-93700-305-8 (in Russian)

You will not find any ready-made recipes in this book. But the provided explanations of the inner mechanics will enable you to critically evaluate other people’s experience and come to your own conclusions. The author goes into details of PostgreSQL internals and shows how to run experiments and verify information that inevitably gets deprecated.

Egor Rogov has been working in the education department in Postgres Professional since 2015; he develops and teaches training courses, publishes blog posts, writes and edits books.

Contents:

Introduction

Part I. Isolation and MVCC

Isolation • Pages and Tuples • Snapshots • Page pruning and HOT updates • Vacuum and autovacuum • Freezing • Rebuilding tables and indexes

Part II. Buffer cache and WAL

Buffer cache • Write-ahead log • WAL modes

Part III. Locks

Relation-level locks • Row-level locks • Miscellaneous locks • Locks on memory structures

Part IV. Query execution

Query execution stages • Statistics • Table access methods • Index access methods • Index scans • Nested loop • Hashing • Sorting and Merging

Part V. Index types

Hash • B-tree • GiST • SP-GiST • GIN • BRIN

A soft copy of this book is available on our website: postgrespro.com/community/books/internals.

PostgreSQL Monitoring

Monitoring is a critical aspect of PostgreSQL administration. The book encompasses this topic, providing vast information on available tools, tips on how to use them and ways of processing the information you get from them.

This book provides insights into PostgreSQL operations and monitoring techniques, helping readers optimize database performance and effectively address administrative challenges.

A. Lesovsky, PostgreSQL Monitoring. Moscow : Bumba, 2024

ISBN 978-5-907754-42-3

Contents:

• Introduction

• About this book

• Statistics overview

• Activity statistics

• Query and function execution

• Databases

• Shared memory and input/output

• Write-ahead Log

• Replication

• Vacuum

• Operation execution process

• Appendix: Testing environment

Alexey Lesovsky is an expert database administrator, system administrator, developer and devops engineer with almost 20 years of experience in managing large-scale complex systems as well as in software design and development.

The book comes together with a docker environment for you to test the examples and run experiments in. It is an interesting read for database administrators, system administrators, reliability experts and performance optimization enthusiasts.

A soft copy of this book in Russian is available on our website: postgrespro.ru/education/books/monitoring.

A Database Guide

The book outlines the architectural foundations of all modern database management systems and explains the algorithms and data structures they utilize. Special attention is paid to the implementation of the same principles in functionally similar platforms.

A must-read for anyone dissatisfied with their “Software Engineering in Three Months” course. It supplements purely practical skills by giving understanding of underlying patterns. This is a book for software architects and senior developers—the elite and those aspiring to join their ranks.

V. Komarov, A Database Guide. Moscow : DMK Press, 2024

ISBN 978-5-93700-287-7

A soft copy of this book in Russian is available on our website: postgrespro.ru/education/books/dbguide.

Vladimir Komarov is an IT generalist: software developer, database administrator, data and infrastructure architect, lecturer and a bit of a evangelist.

Contents:

Part I. Database Classification

Data models • Extra classification criteria

Part II. Data Access

Storage structures • Data processing

Part III. DBMS Architecture

Data consistency guarantees • DBMS structure

Part IV. Distributed Databases

The distribution compromise Modifying distributed data

Part V. Recovery

Replication • Backup

Part VI. Database Operation

Database management • Hardware • Financials

Part VII. Database Security

Access control • Internal threats