Skip to content

00 Lesson 00: Configuration and Monitoring / PostgreSQL 16

Native Markdown source page: Configuration and Monitoring / PostgreSQL 16
Note

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: dba2_00_introduction.html Archive: DBA2-handouts-16-en.zip PDF pages: 11

Page 1: Configuration and Monitoring / PostgreSQL 16

© 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.

Page 2: For Administrators

Source slide 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, 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.

Page 3: For Backend Developers

Source slide page 3: For Backend Developers{#source-figure-003 caption=“Source slide page 3: For Backend Developers”}

  • DEV2, 4 days

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 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.

https://postgrespro.com/community/courses

Page 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

Speaker notes

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:

https://postgrespro.com/community/books/internals

This course also covers a range of other administration topics not included in the DBA1 course.

Page 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)

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/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.

Page 6: PostgreSQL Clusters

student$ sudo pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
15  prod    5433 down   postgres /var/lib/postgresql/15/prod
/var/log/postgresql/postgresql-15-prod.log
16  main    5432 online postgres /var/lib/postgresql/16/main
/var/log/postgresql/postgresql-16-main.log
16  prod    5433 down   postgres /var/lib/postgresql/16/prod
/var/log/postgresql/postgresql-16-prod.log
16  slow    5432 down   postgres /var/lib/postgresql/16/slow
/var/log/postgresql/postgresql-16-slow.log

In the course VM, PostgreSQL 16 is installed from a package with a standard main cluster, which starts at OS boot and is used in almost all course lessons. For the Server Upgrade lesson, two prod clusters are configured for versions 15 and 16 on the same port 5433. Additionally, Locks in RAM lesson uses the slow cluster on the standard port 5432. All additional clusters are stopped and started manually. All practice tasks are performed under the OS user student. In the main cluster, a superuser and a database with the same name are created, so that psql can be run without parameters.

student$ psql
=> \conninfo \du student
You are connected to database "student" as user "student" via socket in
"/var/run/postgresql" at port "5432".
     List of roles
 Role name | Attributes
-----------+------------
 student   | Superuser
=> SHOW server_version;
          server_version
----------------------------------
 16.2 (Ubuntu 16.2-2.pgdg24.04+1)
(1 row)

In all clusters, all users can connect locally without authentication, and over the network with a password:

=> SELECT type, database, user_name, auth_method FROM pg_hba_file_rules();
 type  |   database    | user_name |  auth_method
-------+---------------+-----------+---------------
 local | {all}         | {all}     | trust
 host  | {all}         | {all}     | scram-sha-256
 host  | {all}         | {all}     | scram-sha-256
 local | {replication} | {all}     | trust
 host  | {replication} | {all}     | scram-sha-256
 host  | {replication} | {all}     | scram-sha-256
(6 rows)

Page 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

Page 8: Day 1

MVCC

    1. Isolation
    1. Page Layout and Row Versions
    1. Snapshots
    1. HOT Updates and Page Pruning

Page 9: Day 2

MVCC (Continued)

    1. Vacuuming
    1. Autovacuuming
    1. Freezing
  • WAL
      1. Buffer Cache
      1. Write-Ahead Log

Page 10: Day 3

WAL (Continued)

    1. Checkpoint
    1. WAL Tuning
  • Locks
      1. Object-Level Locks
      1. Row-Level Locks

Page 11: Day 4

Locks (Continued)

    1. Locks in RAM
  • Administration
      1. Managing Extensions
      1. Localization
      1. Server Upgrade