Basic PostgreSQL Administration

This course keeps the official topic order and exposes the English handout as readable, native Markdown for review and future editorial work.

Source status: 官方 PDF 已提取为原生 Markdown

Translation baseline: English baseline. The current English archive is the working baseline; the Russian edition is retained for chapter-level cross-checking.

Course scope

Installation, server architecture, basic configuration and day-to-day PostgreSQL administration.

Source status

Contents

Supplementary materials

• catalogs.pdf
• dba1_new_features.pdf
• dba1_student_guide.pdf
• unix_commands.pdf

Editorial status

The available English handout is extracted from the official PDF. Where no current English archive was found, the route remains ready for a later source edition.

• Version matrix
• Work checklist
• Sources and permissions

00 Lesson 00: PostgreSQL Administration / Basic Course

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

• 1. Server Installation and Management
• 2. Using psql
• 3. Server Configuration
• Architecture
  • 4. PostgreSQL Fundamentals
  • 5. Isolation and MVCC
  • 6. Vacuuming

Page 8: Day 2

Architecture (Continued)

• 7. Buffer Сache and WAL
• Data Organization
  • 8. Databases and Schemas
  • 9. System Catalog
  • 10. Tablespaces
  • 11. Low Level
• Administration
  • 12. Monitoring

Page 9: Day 3

Access Control

• 13. Overview
• Backup
  • 14. Overview
• Replication
  • 15. Overview of Physical Replication
  • 16. Overview of Logical Replication

01 Lesson 01: Basic Tools / Server Installation and Management

Page 1: Basic Tools / Server Installation and Management

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: Topics

• Basic Сoncepts
• Installation from Source Code
• Server Management
• Package Installation
• Server Management in Ubuntu
• Cloud Solutions

Page 3: DB Cluster and Server

{#source-figure-003 caption=“Source slide page 3: DB Cluster and Server”}

Speaker notes

Before talking about installation, let’s look at some basic concepts.

PostgreSQL is a program that belongs to the class of database management systems.

When this program is running, we call it a PostgreSQL server or a server instance. So far, the server seems to be a “black box” for us, but gradually we will get acquainted with how it works.

The data managed by PostgreSQL is stored in databases. One instance of PostgreSQL simultaneously manages several databases. This set of databases is called a database cluster. We will talk more about databases in the lesson Data Organization. Databases and Schemas.

So, a database cluster is data in files. A server or a server instance is a program that manages a database cluster.

Page 4: Source Code

Installation from source code

• stable server version
• you can use non-standard parameters
• or build the server for a non-standard architecture
• Installation from git
  • current server version
  • used primarily by kernel developers; requires a broader set of tools

Speaker notes

You should at least have an idea of how to install the program from source code, so that you could build PostgreSQL with non-standard parameters or on a non-standard architecture, if necessary.

You can find the source code in 2 formats: gzip and bzip2 at https://www.postgresql.org/ftp/source, as well as in the VM in the student user’s home directory.

You can also get the source code straight from the project’s git repository: git://git.postgresql.org/git/postgresql.git (there are other mirrors, including github).

This way you can build not just the stable version, but also a version at any specific commit, including the most recent one.

When installing from the git repository, a wider set of tools is required. For example, the lexer and parser are made using Flex and Bison, and git stores the source codes for these tools. They generate C files when built, which are then compiled. The source code archives include the pre-built C files already.

Page 5: Required Software

Hard requirements

• tar, gzip/bzip2, GNU make, C compiler (C89)
• Used but can be disabled
  • GNU Readline, zlib, lz4, and zstd libraries
• Extras
  • Perl, Python, and Tcl programming languages
  • for using PL/Perl, PL/Python, PL/Tcl
  • Kerberos, OpenSSL, OpenLDAP, PAM for authentication and encryption
  • ICU library for cross-platform UNICODE support
• Separate tools when building from the git repository

Speaker notes

The configuration and installation process is traditionally based on GNU Make and Autoconf:

https://postgrespro.com/docs/postgresql/16/install-make

This course covers the traditional build method.

Starting with version 16, PostgreSQL also supports the Meson build system as an alternative.

https://postgrespro.com/docs/postgresql/16/install-meson

A number of programs and utilities are required when building from source.

The readline library allows you to edit the command line, use the command history and auto-completion. In a server installation, it may not be necessary if you are never going to run psql via the console.

The libraries zlib, lz4, and zstd are used for compression.

Page 6: Unpacking the Archive

The source code package is located in the student user’s home directory. Let’s unpack and open it:

student$ cd

student$ tar xzf /home/student/postgresql-16.0.tar.gz

student$ ls -l /home/student/postgresql-16.0

total 876
-rw-r--r--  1 student student    365 Sep 11  2023 aclocal.m4
drwxrwxr-x  2 student student   4096 Sep 11  2023 config
-rwxr-xr-x  1 student student 584200 Sep 11  2023 configure
-rw-r--r--  1 student student  87156 Sep 11  2023 configure.ac
drwxrwxr-x 61 student student   4096 Sep 11  2023 contrib
-rw-r--r--  1 student student   1192 Sep 11  2023 COPYRIGHT
drwxrwxr-x  3 student student   4096 Sep 11  2023 doc
-rw-r--r--  1 student student   4288 Sep 11  2023 GNUmakefile.in
-rw-r--r--  1 student student    277 Sep 11  2023 HISTORY
-rw-r--r--  1 student student  64592 Sep 11  2023 INSTALL
-rw-r--r--  1 student student   1875 Sep 11  2023 Makefile
-rw-r--r--  1 student student 101920 Sep 11  2023 meson.build
-rw-r--r--  1 student student   6266 Sep 11  2023 meson_options.txt
-rw-r--r--  1 student student   1213 Sep 11  2023 README
drwxrwxr-x 16 student student   4096 Sep 11  2023 src

Configuration

We’ll set up the configuration process in the directory containing the unpacked PostgreSQL source code using the configure script.

student$ cd /home/student/postgresql-16.0

student$ make distclean

The configure command accepts a number of parameters. Example: –prefix — installation directory, default/usr/local/pgsql –enable-debug — to enable debug information See the documentation for the full list of parameters. The command also makes use of environment variables. For example, CC and CFLAGS configure the C compiler. By default, the configure script prepares compilation and installation, placing all executable files, libraries, and other PostgreSQL files in the directory: /usr/local/pgsql. Typically, the installation directory is accessible for writing only to the OS superuser. For demonstration purposes, we use the –prefix option to specify the server software installation directory in the pgsql16 subdirectory of the student user’s home directory. We are also using port 5555 instead of the default 5432:

student$ ./configure --prefix=/home/student/pgsql16 --with-pgport=5555

The output of this command is extremely verbose, so we are skipping it entirely.

PostgreSQL Compilation and Installation

Options: make — build only the server make world — build the server, all extensions and documentation Let’s compile the server. Depending on your computer performance, this may take minutes or even tens of minutes.

student$ make

Now, install the server.

student$ make install

To compile the extensions, repeat the make and make install commands from within the contrib directory.

student$ cd contrib

Page 7: Page 7

student$ make

student$ make install

If you build the server together with the extensions and docs (make world), you can install everything with just the make install-world command.

Page 8: Creating a Сluster

{#source-figure-008 caption=“Source slide page 8: Creating a Сluster”}

Tool

• initdb
• Features
  • a new database cluster cannot belong to the OS superuser
  • PGDATA is a convenient variable that represents the cluster directory
  • configuration files are created in the PGDATA directory
  • enabling checksum calculation for data pages is a good idea

Speaker notes

After installing the server, you need to create a database cluster. The initdb tool does that for you.

For security reasons, the directory in which the cluster is initialized cannot belong to the OS superuser. Usually, the cluster ownership is assigned to the postgres user.

The cluster owner can define the PGDATA environment variable pointing to the cluster directory. This variable is used by some server tools when they need to find out the location of the cluster. Such tools include initdb, as well as pg_ctl, the main server management tool, which we will discuss in a bit.

During cluster initialization, initdb creates configuration files in the PGDATA directory. We will talk more about configuration files in another lesson.

Initdb has many keys that affect its operation. One important key is -k or –data-checksums. It enables or disables calculating checksums for data pages. The checksum check is performed when accessing any data page in the cluster. This slightly reduces performance, but allows you to quickly detect data corruption.

For more details, see https://postgrespro.com/docs/postgresql/16/app-initdb

Page 9: Creating a Cluster

Now, let’s create a data directory. In production, it’s a good practice to have a separate OS user as the owner of the data directory. Here, we will just use the student user and the /home/student/pgsql16 directory.

student$ mkdir /home/student/pgsql16/data

This directory is often referred to as PGDATA. PGDATA is the environment variable pointing to it, and it’s a convenient shortcut.

student$ export PGDATA=/home/student/pgsql16/data

All server tools and utilities are located in the bin directory. We recommend you to add it into the PATH variable:

student$ export PATH=/home/student/pgsql16/bin:$PATH

initdb is a tool used to initialize a database cluster. In the -U option, you can specify the name of the DBMS superuser. If you do not specify it, then the OS username is used, in our case student. Usually this user is postgres, so let’s set this name explicitly. The -k option includes page checksum calculation, which allows timely detection of data corruption. If PGDATA is not set, specify a directory for the data in the -D option. In our case, we do not actually need to specify this option.

student$ initdb -U postgres -k -D /home/student/pgsql16/data

Page 10: Server Management

Tool

• pg_ctl
• Main operations
  • starting, stopping and checking the status of the server
  • reloading configuration parameters
  • switching to a standby

Speaker notes

The main server management operations include starting and stopping the server, getting the current status of the server, reloading the configuration, and some others.

pg_ctl, a stock PostgreSQL tool, is designed to perform these actions.

pg_ctl should be run as the owner of the database cluster.

For more details about server management for database administrators, see:

https://postgrespro.com/docs/postgresql/16/app-pg-ctl

https://postgrespro.com/docs/postgresql/16/runtime

Page 11: Server Management

We can start the server now. In the -l option, specify the server message log file. The -D option is omitted because the PGDATA variable is set.

student$ pg_ctl start -l /home/student/logfile

Let’s verify that the server is running: use the psql tool to connect to the server and return current time:

student$ psql -U postgres -p 5555 -c 'SELECT now();'

             now
------------------------------
 2025-11-27 13:45:51.89913+03
(1 row)

The following command stops the server:

student$ pg_ctl stop

Page 12: Package Installation

{#source-figure-012 caption=“Source slide page 12: Package Installation”}

Package installation is usually preferable

• Linux ( , Debian, Red Hat, SUSE and others)
  • included in the OS distribution
  • repository (yum, apt) or RPM/DEB packages
• FreeBSD, OpenBSD
  • packages from the Ports and Packages Collection
• Mac OS X
• Windows

Speaker notes

The preferred option is to use ready-made packages, since in this case a clear, supported and easily updated installation is obtained.

Packages exist for most popular systems. Each of them may have its own features that you should get to know before installing.

Package repositories of some systems already include PostgreSQL, but usually not the latest version. The latest version is always available in the PostgreSQL Global Development Group (PGDG) repository:

https://www.postgresql.org/download/

We will be working with the Ubuntu PostgreSQL package.

Page 13: Creating a Cluster

{#source-figure-013 caption=“Source slide page 13: Creating a Cluster”}

Tool

• pg_createcluster
• initdb
• Features
  • initialization is performed when installing the package, creates
  • a cluster named “main”
  • checksum calculation is not enabled by default
  • defines the location of server configuration and log files
  • sets up automatic server startup at OS startup

Speaker notes

pg_createcluster is a wrapper for the initdb tool designed to initialize a cluster in Ubuntu.

The help for the pg_createcluster command can be obtained using man: $ man pg_createcluster

pg_createcluster is executed automatically when the package is installed and creates a database cluster named “main”.

Note that cluster initialization is done with checksum calculation for data pages disabled.

Executables, configuration files and the server log are placed in accordance with the Ubuntu practices.

In addition, the installation sets up the PostgreSQL server to start and stop when Ubuntu starts and stops.

To delete a cluster, the pg_dropcluster tool is used.

Both pg_createcluster and pg_dropcluster are specific to Ubuntu. In other systems, you need to explicitly initialize a cluster using initdb and use the appropriate operating system tools.

Page 14: Creating a Cluster in Ubuntu

In the provided VM, PostgreSQL is installed from the distribution package:

student$ sudo apt install -y postgresql-16

PostgreSQL directory:

student$ sudo ls -l /usr/lib/postgresql/16

total 8
drwxr-xr-x 2 root root 4096 Nov 27 12:16 bin
drwxr-xr-x 4 root root 4096 Nov 27 12:16 lib

The owner of the server software is the root user. pg_config shows the parameters the server was compiled with:

student$ sudo /usr/lib/postgresql/16/bin/pg_config --configure

 '--build=x86_64-linux-gnu' '--prefix=/usr' '--includedir=${prefix}/include'
'--mandir=${prefix}/share/man' '--infodir=${prefix}/share/info' '--sysconfdir=/etc'
'--localstatedir=/var' '--disable-option-checking' '--disable-silent-rules'
'--libdir=${prefix}/lib/x86_64-linux-gnu' '--runstatedir=/run'
'--disable-maintainer-mode' '--disable-dependency-tracking' '--with-tcl' '--with-perl'
'--with-python' '--with-pam' '--with-openssl' '--with-libxml' '--with-libxslt'
'--mandir=/usr/share/postgresql/16/man' '--docdir=/usr/share/doc/postgresql-doc-16'
'--sysconfdir=/etc/postgresql-common' '--datarootdir=/usr/share/'
'--datadir=/usr/share/postgresql/16' '--bindir=/usr/lib/postgresql/16/bin'
'--libdir=/usr/lib/x86_64-linux-gnu/' '--libexecdir=/usr/lib/postgresql/'
'--includedir=/usr/include/postgresql/' '--with-extra-version= (Ubuntu
16.11-1.pgdg24.04+1)' '--enable-nls' '--enable-thread-safety' '--enable-debug'
'--disable-rpath' '--with-uuid=e2fs' '--with-gnu-ld' '--with-gssapi' '--with-ldap'
'--with-pgport=5432' '--with-system-tzdata=/usr/share/zoneinfo' 'AWK=mawk'
'MKDIR_P=/bin/mkdir -p' 'PROVE=/usr/bin/prove' 'PYTHON=/usr/bin/python3' 'TAR=/bin/tar'
'XSLTPROC=xsltproc --nonet' 'CFLAGS=-g -O2 -fno-omit-frame-pointer
-mno-omit-leaf-frame-pointer -flto=auto -ffat-lto-objects -fstack-protector-strong
-fstack-clash-protection -Wformat -Werror=format-security -fcf-protection
-fno-omit-frame-pointer' 'LDFLAGS=-Wl,-Bsymbolic-functions -flto=auto -ffat-lto-objects
-Wl,-z,relro -Wl,-z,now' '--enable-tap-tests' '--with-icu' '--with-llvm'
'LLVM_CONFIG=/usr/bin/llvm-config-19' 'CLANG=/usr/bin/clang-19' '--with-lz4'
'--with-zstd' '--with-systemd' '--with-selinux' '--enable-dtrace'
'build_alias=x86_64-linux-gnu' 'CPPFLAGS=-Wdate-time -D_FORTIFY_SOURCE=3' 'CXXFLAGS=-g
-O2 -fno-omit-frame-pointer -mno-omit-leaf-frame-pointer -flto=auto -ffat-lto-objects
-fstack-protector-strong -fstack-clash-protection -Wformat -Werror=format-security
-fcf-protection'

A database cluster called main is already initialized and is located in /var/lib/postgresql/16/main. The owner of the directory is postgres user. Here’s what the directory contains:

student$ sudo ls -l /var/lib/postgresql/16/main

total 92
drwx------ 1 postgres postgres 4096 Nov 27 13:08 base
drwx------ 1 postgres postgres 4096 Nov 27 13:45 global
drwx------ 2 postgres postgres 4096 Nov 27 13:08 pg_commit_ts
drwx------ 2 postgres postgres 4096 Nov 27 13:08 pg_dynshmem
drwx------ 4 postgres postgres 4096 Nov 27 13:08 pg_logical
drwx------ 4 postgres postgres 4096 Nov 27 13:08 pg_multixact
drwx------ 2 postgres postgres 4096 Nov 27 13:08 pg_notify
drwx------ 2 postgres postgres 4096 Nov 27 13:08 pg_replslot
drwx------ 2 postgres postgres 4096 Nov 27 13:08 pg_serial
drwx------ 2 postgres postgres 4096 Nov 27 13:08 pg_snapshots
drwx------ 1 postgres postgres 4096 Nov 27 13:45 pg_stat
drwx------ 2 postgres postgres 4096 Nov 27 13:08 pg_stat_tmp
drwx------ 2 postgres postgres 4096 Nov 27 13:08 pg_subtrans
drwx------ 2 postgres postgres 4096 Nov 27 13:08 pg_tblspc
drwx------ 2 postgres postgres 4096 Nov 27 13:08 pg_twophase
-rw------- 1 postgres postgres    3 Nov 27 13:08 PG_VERSION
drwx------ 3 postgres postgres 4096 Nov 27 13:08 pg_wal
drwx------ 2 postgres postgres 4096 Nov 27 13:08 pg_xact
-rw------- 1 postgres postgres   88 Nov 27 13:08 postgresql.auto.conf
-rw------- 1 postgres postgres  130 Nov 27 13:45 postmaster.opts
-rw------- 1 postgres postgres  108 Nov 27 13:45 postmaster.pid

Page 15: Page 15

Page 16: Server Management

Tool

• pg_ctlcluster
• pg_ctl
• Main operations
  • starting, stopping and checking the status of the server
  • reloading configuration parameters
  • switching to a standby

Speaker notes

In the Ubuntu PostgreSQL package, pg_ctl is not run directly, but through a special wrapper pg_ctlcluster. The help for pg_ctlcluster can be obtained using man: $ man pg_ctlcluster

In other systems, pg_ctl can be used directly.

Page 17: Server Management in Ubuntu

With the package installation, PostgreSQL is automatically added to the OS startup list. So, there is no need to start it manually. You can explicitly control the server using the pg_ctlcluster tool, which must be run either as the OS postgres user or via sudo. The pg_ctlcluster tool can manage multiple servers of different versions. It has the following parameters: 16 — server version number main — server name Command Stop:

student$ sudo pg_ctlcluster 16 main stop

student$ pg_lsclusters 16 main

Ver Cluster Port Status Owner    Data directory              Log file
16  main    5432 down   postgres /var/lib/postgresql/16/main
/var/log/postgresql/postgresql-16-main.log

Start:

student$ sudo pg_ctlcluster 16 main start

Restart:

student$ sudo pg_ctlcluster 16 main restart

Get server status:

student$ sudo pg_ctlcluster 16 main status

pg_ctl: server is running (PID: 3539)
/usr/lib/postgresql/16/bin/postgres "-D" "/var/lib/postgresql/16/main" "-c"
"config_file=/etc/postgresql/16/main/postgresql.conf"

Reload configuration:

student$ sudo pg_ctlcluster 16 main reload

For a package installation, the server log file is located at:

student$ ls -l /var/log/postgresql/postgresql-16-main.log

-rw-r----- 1 postgres adm 7377 Nov 27 13:46 /var/log/postgresql/postgresql-16-main.log

Let’s look at the last messages:

student$ tail -n 10 /var/log/postgresql/postgresql-16-main.log

2025-11-27 13:45:57.933 MSK [3485] LOG:  shutting down
2025-11-27 13:45:57.948 MSK [3485] LOG:  checkpoint starting: shutdown immediate
2025-11-27 13:45:58.058 MSK [3485] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0
WAL file(s) added, 0 removed, 0 recycled; write=0.030 s, sync=0.021 s, total=0.125 s;
sync files=2, longest=0.013 s, average=0.011 s; distance=0 kB, estimate=0 kB;
lsn=0/1921C98, redo lsn=0/1921C98
2025-11-27 13:45:58.067 MSK [3484] LOG:  database system is shut down
2025-11-27 13:45:58.317 MSK [3539] LOG:  starting PostgreSQL 16.11 (Ubuntu
16.11-1.pgdg24.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
13.3.0-6ubuntu2~24.04) 13.3.0, 64-bit
2025-11-27 13:45:58.317 MSK [3539] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2025-11-27 13:45:58.332 MSK [3539] LOG:  listening on Unix socket
"/var/run/postgresql/.s.PGSQL.5432"
2025-11-27 13:45:58.369 MSK [3542] LOG:  database system was shut down at 2025-11-27
13:45:58 MSK
2025-11-27 13:45:58.390 MSK [3539] LOG:  database system is ready to accept connections
2025-11-27 13:46:00.978 MSK [3539] LOG:  received SIGHUP, reloading configuration files

Page 18: Cloud Solutions

PostgreSQL in a virtual environment

• virtualization overhead
• the administrator has full access to the instance
• PostgreSQL as a Service
  • offered by many cloud providers
  • the provider takes over the administration routine
  • limited management, backup and monitoring tools

Speaker notes

Virtualization solutions can be used to run databases, including PostgreSQL. The trade off for the convenience of this approach is considerable overhead costs. For high-load systems, any additional intermediate layers between the DBMS and the hardware are undesirable.

In addition, many leading cloud providers offer PostgreSQL as a service (Database as a Service, managed database).

In this case, the provider takes over most of the administration tasks. This limits your control over the instance. Moreover, management capabilities for tasks such as performance monitoring or backup and recovery are limited by the tools offered by the service provider.

We will not consider the features of individual cloud solutions within the course. You can study the documentation offered by different service providers to learn more.

Page 19: Takeaways

Two installation options — package or source code

• Cloud solutions available
• The server is run by a dedicated OS user
• Initialize a database cluster before using the server
• Specific commands for server management

Page 20: Practice

Enabling checksum calculation for a cluster.

• 1. Stop the server.
• 2. Check whether checksums are calculated for the cluster.
• 3. Enable checksum calculation.
• 4. Start the server.

Speaker notes

2, 3. Use the pg_checksums tool. To run it, enter the full path: /usr/lib/postgresql/16/bin/pg_checksums

https://postgrespro.com/docs/postgresql/16/app-pgchecksums

Page 21: 1. Stopping the Server

student$ sudo pg_ctlcluster 16 main stop

2. Verification

To see if page checksum calculation is enabled, run the pg_checksums utility with the –check option:

student$ sudo /usr/lib/postgresql/16/bin/pg_checksums --check -D /var/lib/postgresql/16/main

pg_checksums: error: data checksums are not enabled in cluster

3. Enabling Checksum Calculation

Run pg_checksums with the –enable option:

student$ sudo /usr/lib/postgresql/16/bin/pg_checksums --enable -D /var/lib/postgresql/16/main

Checksum operation completed
Files scanned:   1244
Blocks scanned:  3749
Files written:  1026
Blocks written: 3749
pg_checksums: syncing data directory
pg_checksums: updating control file
Checksums enabled in cluster

Checksum calculation is now enabled.

4. Starting the Server

student$ sudo pg_ctlcluster 16 main start

Page 22: Practice+

1. Install PostgreSQL from source code as shown in the demo.

• Create a database cluster, start the server.
• Make sure the server is running.
• Stop the server.

Page 23: 1. Compiling PostgreSQL from Source Code

All necessary commands were shown during the demo, but were not executed. Repeat the process by yourself within the provided virtual machine.

02 Lesson 02: Basic Tools / Using psql

Page 1: Basic Tools / Using psql

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 ofincome, 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: Topics

• Launching psql and Connecting to the Database
• Getting Help
• Working with psql
• Configuring psql

Page 3: Purpose of psql

Terminal client for working with PostgreSQL

• Comes with the DBMS
• Used by administrators and developers for interactive work and script execution

Speaker notes

There are other third-party tools available, but they are not considered in the scope of the course.

The psql terminal client will be used throughout the course. Those who are used to working with GUI tools may find it uncomfortable at first. Nevertheless, it is very powerful if you get used to it.

This is the only client supplied with the DBMS. The knowledge of psql will be useful to both developers and DB administrators, regardless of which tool they choose to work with at the end of the day.

https://postgrespro.com/docs/postgresql/16/app-psql

Page 4: Connection

Launch

$ psql -d database -U user -h host -p port

• New connection in psql

=> \c[onnect] database user node port

• Information about the current connection

=> \conninfo

Speaker notes

The required connection parameters include: database name, user name, server name, port number. If these parameters are not specified, psql will try to connect using the default values:

● database — matches the user name;

● user — matches the OS user name;

● node — connection via Unix socket;

● port — usually 5432.

To make a new connection without leaving psql, run the \connect command. It uses the current connection’s parameters as defaults.

The \conninfo command provides information about the current connection.

Additional information about connection configuration options:

https://postgrespro.com/docs/postgresql/16/libpq-envars

https://postgrespro.com/docs/postgresql/16/libpq-pgservice

https://postgrespro.com/docs/postgresql/16/libpq-pgpass

Page 5: Getting Help

In the OS command line

$ psql --help
$ man psql

• In psql
  • => \?
  • list of psql commands
  • => \? variables
  • psql variables
  • => \h[elp]
  • list of SQL commands
  • => \h command
  • syntax of the SQL command
  • => \q
  • quit

Speaker notes

Reference information on psql can be obtained not only from the documentation, but also from within the system directly.

psql with the –help option displays a startup help message. If the documentation package is installed with the system, you can view the manual for psql using the man psql command.

psql can execute SQL commands as well as its own commands. All psql commands start with a backslash and, as a rule, can be abbreviated to their first letter.

Inside psql, you can get a list and a brief description of all psql commands: \?.

The \help command provides a list of SQL commands that the server supports, as well as the syntax of an SQL command (if specified).

Another command that is useful to know, although it has nothing to do with the help, is \q — exit psql. Alternatively, you can use the exit and quit commands to quit.

Page 6: Executing SQL Commands and Formatting the Output

Run psql:

student$ psql

Check the connection:

=> \conninfo

You are connected to database "student" as user "student" via socket in
"/var/run/postgresql" at port "5432".

Using the default parameters, we have connected to the student database as the student user. You will learn more about databases and users in later modules. The \c[onnect] command creates a new connection without leaving psql. SQL commands, unlike psql ones, may span multiple rows. To send an SQL command for execution, end it with a semicolon:

=> SELECT schemaname, tablename, tableowner
FROM pg_tables
LIMIT 5;

 schemaname |       tablename       | tableowner
------------+-----------------------+------------
 pg_catalog | pg_statistic          | postgres
 pg_catalog | pg_type               | postgres
 pg_catalog | pg_foreign_table      | postgres
 pg_catalog | pg_authid             | postgres
 pg_catalog | pg_statistic_ext_data | postgres
(5 rows)

psql can give output in different formats. Here are some of them: Aligned format Unaligned format Extended format The aligned format is the default. It sets each column’s width based on its contents. There is also the header and the total row. psql commands to switch display modes: \a — switches aligned format and unaligned format mode \t — switches the header and footer display Let’s switch to non-aligned, turn the header and the total row off, and use a whitespace as the separator:

=> \t \a

Tuples only is on.
Output format is unaligned.

=> \pset fieldsep ' '

Field separator is " ".

=> SELECT schemaname, tablename, tableowner FROM pg_tables LIMIT 5;

pg_catalog pg_statistic postgres
pg_catalog pg_type postgres
pg_catalog pg_foreign_table postgres
pg_catalog pg_authid postgres
pg_catalog pg_statistic_ext_data postgres

=> \t \a

Tuples only is off.
Output format is aligned.

The extended format is convenient for displaying multiple columns for one or several records:

=> \x

Expanded display is on.

=> SELECT * FROM pg_tables WHERE tablename = 'pg_class';

-[ RECORD 1 ]-----------
schemaname  | pg_catalog
tablename   | pg_class
tableowner  | postgres
tablespace  |
hasindexes  | t
hasrules    | f
hastriggers | f
rowsecurity | f

=> \x

Expanded display is off.

The extended mode can be set for a single query. To do that, add \gx at the end instead of a semicolon:

=> SELECT * FROM pg_tables WHERE tablename = 'pg_proc' \gx

Page 7: Page 7

-[ RECORD 1 ]-----------
schemaname  | pg_catalog
tablename   | pg_proc
tableowner  | postgres
tablespace  |
hasindexes  | t
hasrules    | f
hastriggers | f
rowsecurity | f

You can see all the formatting options by using the \pset command. If used with no parameters, it will display current settings:

=> \pset

border                   1
columns                  0
csv_fieldsep             ','
expanded                 off
fieldsep                 ' '
fieldsep_zero            off
footer                   on
format                   aligned
linestyle                ascii
null                     ''
numericlocale            off
pager                    1
pager_min_lines          0
recordsep                '\n'
recordsep_zero           off
tableattr
title
tuples_only              off
unicode_border_linestyle single
unicode_column_linestyle single
unicode_header_linestyle single
xheader_width            full

Interacting with the OS

psql can run shell commands:

=> \! pwd

/home/student

You can set OS environment variables:

=> \setenv HELLO Hello

=> \! echo $HELLO

Hello

It can write output into a file with the \o[ut] command:

=> \o tmp/dba1_log

=> SELECT schemaname, tablename, tableowner FROM pg_tables LIMIT 5;

There is nothing on the screen! Let’s check the file:

=> \! cat tmp/dba1_log

 schemaname |       tablename       | tableowner
------------+-----------------------+------------
 pg_catalog | pg_statistic          | postgres
 pg_catalog | pg_type               | postgres
 pg_catalog | pg_foreign_table      | postgres
 pg_catalog | pg_authid             | postgres
 pg_catalog | pg_statistic_ext_data | postgres
(5 rows)

Let’s get the output back to the screen:

=> \o

Executing Scripts

Another way to run a query is with the \g command. You can specify parameters for this particular query in the brackets. Query output can be redirected to OS command by appending the command after a vertical bar. For example, display query output with line numbers:

=> SELECT format('SELECT count(*) FROM %I;', tablename)
FROM pg_tables
LIMIT 3
\g (tuples_only=on format=unaligned) | cat -n

     1 SELECT count(*) FROM pg_statistic;
     2 SELECT count(*) FROM pg_type;
     3 SELECT count(*) FROM pg_foreign_table;

The \g command can specify a filename to redirect output:

=> SELECT format('SELECT count(*) FROM %I;', tablename)
FROM pg_tables
LIMIT 3
\g (tuples_only=on format=unaligned) tmp/dba1_log

Page 8: Page 8

This is what we get:

=> \! cat tmp/dba1_log

SELECT count(*) FROM pg_statistic;
SELECT count(*) FROM pg_type;
SELECT count(*) FROM pg_foreign_table;

We can execute this file as a script using \i[nclude]:

=> \i tmp/dba1_log

 count
-------
   409
(1 row)

 count
-------
   613
(1 row)

 count
-------
     0
(1 row)

Other ways to run commands from a file: psql < filename psql -f filename You can skip creating a file in the last example if you end the query with \gexec:

=> SELECT format('SELECT count(*) FROM %I;', tablename)
FROM pg_tables
LIMIT 3
\gexec

 count
-------
   409
(1 row)

 count
-------
   613
(1 row)

 count
-------
     0
(1 row)

gexec considers the contents of each column of each row an SQL-operator, and tries to execute them one by one.

psql Variables and Control Structures

Not unlike shell, psql has its own variables, including some pre-defined ones (with special meaning for psql). Store the value of the OS USER environment variable in a psql variable:

=> \getenv User USER

Set a variable Test:

=> \set Test Hi

To substitute a variable’s value, use a colon prefix before the variable name:

=> \echo :Test :User!

Hi student!

To reset a variable, use:

=> \unset Test

=> \echo :Test

:Test

Query results can be saved to variables. For this purpose use the \gset command termination:

=> SELECT now() AS curr_time \gset

=> \echo :curr_time

2025-11-27 13:46:21.349396+03

The query must return only one record. If used with no parameters, \set displays all currently set variables and their values:

=> \set

Page 9: Page 9

AUTOCOMMIT = 'on'
COMP_KEYWORD_CASE = 'preserve-upper'
DBNAME = 'student'
ECHO = 'none'
ECHO_HIDDEN = 'off'
ENCODING = 'UTF8'
ERROR = 'false'
FETCH_COUNT = '0'
HIDE_TABLEAM = 'off'
HIDE_TOAST_COMPRESSION = 'off'
HISTCONTROL = 'none'
HISTFILE = 'hist'
HISTSIZE = '500'
HOST = '/var/run/postgresql'
IGNOREEOF = '0'
LAST_ERROR_MESSAGE = ''
LAST_ERROR_SQLSTATE = '00000'
ON_ERROR_ROLLBACK = 'off'
ON_ERROR_STOP = 'off'
PORT = '5432'
PROMPT1 = '%/%R%x%# '
PROMPT2 = '%/%R%x%# '
PROMPT3 = '>> '
QUIET = 'off'
ROW_COUNT = '1'
SERVER_VERSION_NAME = '16.11 (Ubuntu 16.11-1.pgdg24.04+1)'
SERVER_VERSION_NUM = '160011'
SHELL_ERROR = 'false'
SHELL_EXIT_CODE = '0'
SHOW_ALL_RESULTS = 'on'
SHOW_CONTEXT = 'errors'
SINGLELINE = 'off'
SINGLESTEP = 'off'
SQLSTATE = '00000'
USER = 'student'
User = 'student'
VERBOSITY = 'default'
VERSION = 'PostgreSQL 16.11 (Ubuntu 16.11-1.pgdg24.04+1) on x86_64-pc-linux-gnu, compiled
by gcc (Ubuntu 13.3.0-6ubuntu2~24.04) 13.3.0, 64-bit'
VERSION_NAME = '16.11 (Ubuntu 16.11-1.pgdg24.04+1)'
VERSION_NUM = '160011'
curr_time = '2025-11-27 13:46:21.349396+03'

In scripts, you can use conditional operators. For example, let’s check if working_dir has a value, and if it does not, set it as the current directory. The following command checks if the value is set and returns a Boolean value:

=> \echo :{?working_dir}

FALSE

This conditional psql operator checks if the variable exists and sets the default value if needed:

=> \if :{?working_dir}
   -- variable is defined
\else
   -- set the value as the output of the OS command
   \set working_dir `pwd`
\endif

Now we can be sure that the working_dir variable is defined:

=> \echo :working_dir

/home/student

System Catalog Commands

There is a set of commands (mostly starting with \d) used to quickly and conveniently get information about database objects. Example:

=> \d pg_tables

              View "pg_catalog.pg_tables"
   Column    |  Type   | Collation | Nullable | Default
-------------+---------+-----------+----------+---------
 schemaname  | name    |           |          |
 tablename   | name    |           |          |
 tableowner  | name    |           |          |
 tablespace  | name    |           |          |
 hasindexes  | boolean |           |          |
 hasrules    | boolean |           |          |
 hastriggers | boolean |           |          |
 rowsecurity | boolean |           |          |

We will see more of these commands later.

Configuring psql

On startup, psql runs two scripts (if they exist): First a common system psqlrc script Then a custom .psqlrc file

Page 10: Page 10

The user configuration file must be in the home directory. The system script’s location can be discovered with the following command:

student$ pg_config --sysconfdir

/etc/postgresql-common

Neither file exists by default. You can use the files to configure your session parameters, for example: psql prompt Program for page-by-page viewing of query results Variables for storing the text of frequently used commands For example, let’s store a query that returns 5 largest tables in a variable top5:

=> \set top5 'SELECT tablename, pg_total_relation_size(schemaname||''.''||tablename) AS bytes FROM pg_tables ORDER BY bytes DESC LIMIT 5;'

Now we can execute the query by just typing:

=> :top5

   tablename    |  bytes
----------------+---------
 pg_proc        | 1245184
 pg_rewrite     |  745472
 pg_attribute   |  720896
 pg_description |  630784
 pg_statistic   |  294912
(5 rows)

If you write the \set command into the ~/.psqlrc file, the top5 variable will be available immediately after psql startup. Thanks to readline support, psql can autocomplete keywords and object names, and it also stores the command history. The name and the size of the history file are set by HISTFILE and HISTSIZE variables.

Page 11: Takeaways

psql is a terminal client for working with PostgreSQL

• Connection parameters are required at startup
• Executes SQL and psql commands
• Includes tools for interactive work, as well as for preparing and executing scripts

Page 12: Practice

1. Run psql and check the current connection information. 2. Display a detailed list of databases. 3. By default, psql uses less command for page-by-page output. Replace it with less -XS and display the detailed database list again. 4. The default prompt shows only the name of the database. Configure the prompt to display in the following format: user@database=#. 5. Configure psql to display the execution time for all commands. Make sure that this setting is saved when you restart.

Speaker notes

1. When starting psql, if you omit the connection parameters, the default values will apply.

2. Use the \l+ command.

3. The pager program is configured using the PSQL_PAGER environment variable. The setting can be configured in the .psqlrc file using the \setenv command. This will set the value to ’less -XS’ specifically for psql sessions, while maintaining the OS default settings in all other cases. By default, less wraps long lines during viewing and clears its output upon exit. The -XS parameter disables this default behavior.

4. Prompt customization is described in the documentation:

https://postgrespro.com/docs/postgresql/16/app-psql#APP-PSQL-PROMPTI NG

5.The psql command to output the duration of a query execution can be found in the PostgreSQL documentation or within psql itself with the \? command.

Page 13: 1. Running psql and Displaying Connection Information

student$ psql

=> \conninfo

You are connected to database "student" as user "student" via socket in
"/var/run/postgresql" at port "5432".

2. Paging Results Using less

When query output exceeds terminal dimensions, psql sends it to the less pager. You can navigate through query results using standard navigation keys. The h command displays the help file. The q command exits the display mode.

=> \l+

List of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU
Locale | ICU Rules |   Access privileges   |  Size   | Tablespace |
Description
-----------+----------+----------+-----------------+-------------+-------------+----------
--+-----------+-----------------------+---------+------------+----------------------------
----------------
 postgres  | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
  |           |                       | 7361 kB | pg_default | default administrative
connection database
 student   | student  | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
  |           |                       | 7516 kB | pg_default |
 template0 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
  |           | =c/postgres          +| 7361 kB | pg_default | unmodifiable empty database
           |          |          |                 |             |             |
  |           | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |
  |           | =c/postgres          +| 7516 kB | pg_default | default template for new
databases
           |          |          |                 |             |             |
  |           | postgres=CTc/postgres |         |            |
(4 rows)

3. Configuring Page View in .psqlrc

When using the less pager with -XS flags, long lines will not wrap and output remains visible after exiting the less pager. For such configuration, simply set the PSQL_PAGER environment variable using the \setenv command. We will save this setting in the ~/.psqlrc script:

student$ echo "\setenv PSQL_PAGER 'less -XS'" > ~/.psqlrc

4. Customizing Prompts

To include role information in your prompts, prepend %n@ to both PROMPT1 and PROMPT2 variables.

student$ echo "\set PROMPT1 '%n@%/%R%x%# '" >> ~/.psqlrc

student$ echo "\set PROMPT2 '%n@%/%R%x%# '" >> ~/.psqlrc

The PROMPT1 variable controls the primary prompt displayed for the first line of a user’s query input. For multi-line queries, PROMPT2 controls the prompt display from the second line onward. While both variables share identical default values, you can configure distinct prompts for initial and following lines. Note that PROMPT3 serves only for COPY command operations.

5. Output of SQL Execution Timing

student$ echo '\timing on' >> ~/.psqlrc

The complete contents of your .psqlrc file will be like this:

Page 14: Page 14

student$ cat ~/.psqlrc

\setenv PSQL_PAGER 'less -XS'
\set PROMPT1 '%n@%/%R%x%# '
\set PROMPT2 '%n@%/%R%x%# '
\timing on

For the changes to take effect, restart your psql session.

=> \q

student$ psql

After restarting, verify the new configuration: Prompt (must include role name) Displaying detailed information about databases Output of command execution time

Page 15: Practice+

1. Open a transaction and execute a command that ends with any error. Make sure that no other commands can be executed inside this transaction.

• 2. Set the ON_ERROR_ROLLBACK parameter to on and make sure that after the error, you can continue executing commands inside the transaction.

Speaker notes

1. To open a transaction, run the command

BEGIN;

2. Setting the ON_ERROR_ROLLBACK parameter to ON causes psql to create a SAVEPOINT before each SQL command inside an open transaction and, in case of an error, roll back to this savepoint.

https://postgrespro.com/docs/postgresql/16/sql-savepoint

Page 16: 1. psql and In-Transaction Errors

student$ psql

The psql tool autocommits transactions by default, so each SQL command is executed within a separate transaction. So each SQL command is executed within a separate transaction. To start a transaction explicitly, the BEGIN command is used:

student@student=# BEGIN;

BEGIN

student@student=*# CREATE TABLE t (id int);

CREATE TABLE

Consider that we have made a typo in the following command:

student@student=*# INSERTINTO t VALUES(1);

ERROR:  syntax error at or near "INSERTINTO"
LINE 1: INSERTINTO t VALUES(1);
        ^

The asterisk will change to an exclamation mark, indicating an error. Now, rewrite the command:

student@student=!# INSERT INTO t VALUES(1);

ERROR:  current transaction is aborted, commands ignored until end of transaction block

But PostgreSQL cannot roll back just a single command, so it terminates and rolls back the whole transaction. To continue, we must send a command that says that the transaction is complete. It can be either COMMIT or ROLLBACK, since the transaction is already cancelled.

student@student=!# COMMIT;

ROLLBACK

Creating the table was cancelled, so there is no such table in the database:

student@student=# SELECT * FROM t;

ERROR:  relation "t" does not exist
LINE 1: SELECT * FROM t;
                      ^

The ON\_ERROR\_ROLLBACK Variable

We can change how psql behaves here.

student@student=# \set ON_ERROR_ROLLBACK on

Now, before every transaction command, there will be a savepoint created. In case of an error, it will roll back to the last savepoint. This way, transaction commands can continue executing.

student@student=# BEGIN;

BEGIN

student@student=*# CREATE TABLE t (id int);

CREATE TABLE

student@student=*# INSERTINTO t VALUES(1);

ERROR:  syntax error at or near "INSERTINTO"
LINE 1: INSERTINTO t VALUES(1);
        ^

student@student=*# INSERT INTO t VALUES(1);

INSERT 0 1

student@student=*# COMMIT;

COMMIT

student@student=# SELECT * FROM t;

Page 17: Page 17

 id
----
  1
(1 row)

The ON_ERROR_ROLLBACK variable can be set to interactive. This will make such behavior work only in the interactive mode, but not when executing scripts.

student@student=# DROP TABLE t;

DROP TABLE

03 Lesson 03: Basic Tools / Server Configuration

Page 1: Basic Tools / Server Configuration

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: Topics

• Configuration Parameters
• Configuration Files
• Parameter Management at the Instance and Session Levels

Page 3: Configuration Parameters

Purpose

• managing DBMS operation and behavior
• Setting parameters
  • for an instance via configuration files
  • for a separate database or user
  • for the current session

Speaker notes

There are multiple parameters in PostgreSQL that control the DBMS behavior. These parameters affect resource management, server processes, and much more.

For example, the max_connections parameter limits the number of concurrent connections to the server.

The full list of configuration parameters and their descriptions is available in the documentation: https://postgrespro.com/docs/postgresql/16/runtime-config

In this topic, we will not cover any specific configuration parameters, but rather discuss how to set their values.

Configuration parameters are generally managed in configuration files. The parameter values set in the configuration files affect the whole DBMS instance, unless explicitly configured otherwise.

Some parameters can be set for a specific database or for a specific user’s sessions. Such settings will overrule those declared in configuration files. These types of settings will be discussed in further lessons of the course.

Lastly, many parameters can be changed at the session level, during client operaton.

Page 4: postgresql.conf

Main configuration file

• loaded when the server starts
• option to load additional configuration files
• located in the data directory (PGDATA) by default

/etc/postgresql/16/main

• After any changes to the parameters, the file has to be reloaded

$ pg_ctl reload

$ pg_ctlcluster 16 main reload

• => SELECT pg_reload_conf();
• changes to some parameters require a server restart to apply

Speaker notes

The main configuration file is postgresql.conf.

The default file’s location is defined during initial PostgreSQL compilation. The server executable accepts -c config_file as a command line argument to set the configuration file path.

By default, the file is located in the data catalog (PGDATA), but package distributions usually place it somewhere else, depending on the OS-specific conventions.

This is a well-documented plaintext file which stores parameters in a key-value format.

Additional configuration files can be included. By default, PostgreSQL on Ubuntu automatically includes all .conf files from /etc/postgresql/16/main/conf.d.

If the same parameter is defined in the configuration files multiple times, only the most recently read value will be used.

For any changes to parameters to apply, the file must be reloaded. Some parameters require a server restart to apply.

Page 5: The postgresql.conf File and the pg_file_settings View

The configuration file name is a value of the read-only config_file parameter. The configuration file can be specified using a command-line switch when starting postgres.

=> SHOW config_file;

               config_file
-----------------------------------------
 /etc/postgresql/16/main/postgresql.conf
(1 row)

Take a look at a part of a config file.

=> SELECT pg_read_file('/etc/postgresql/16/main/postgresql.conf', 1516, 861) \g (tuples_only=on format=unaligned)

#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------

# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.

data_directory = '/var/lib/postgresql/16/main'
# use data in another directory
# (change requires restart)
hba_file = '/etc/postgresql/16/main/pg_hba.conf'
# host-based authentication file
# (change requires restart)
ident_file = '/etc/postgresql/16/main/pg_ident.conf'
# ident configuration file
# (change requires restart)

# If external_pid_file is not explicitly set, no extra PID file is written.
external_pid_file = '/var/run/postgresql/16-main.pid'
# write an extra
PID file
# (change requires restart)

The main configuration file postgresql.conf can include additional configuration files. Directives: include_dir is a directory with additional configuration files. include includes an additional configuration file. include_if_exists Includes an additional configuration file, if one exists. These directives are typically placed at the end of postgresql.conf:

student$ sudo grep -A3 ^include /etc/postgresql/16/main/postgresql.conf

include_dir = 'conf.d'
# include files ending in '.conf' from
# a directory, e.g., 'conf.d'
#include_if_exists = '...'
# include file only if it exists
#include = '...'
# include file

To see current settings in configuration files, query the pg_file_settings view:

=> SELECT sourceline, name, setting, applied, error FROM pg_file_settings;

Page 6: Page 6

 sourceline |            name            |                setting                 |
applied | error
------------+----------------------------+----------------------------------------+-------
--+-------
         42 | data_directory             | /var/lib/postgresql/16/main            | t
  |
         44 | hba_file                   | /etc/postgresql/16/main/pg_hba.conf    | t
  |
         46 | ident_file                 | /etc/postgresql/16/main/pg_ident.conf  | t
  |
         50 | external_pid_file          | /var/run/postgresql/16-main.pid        | t
  |
         64 | port                       | 5432                                   | t
  |
         65 | max_connections            | 100                                    | t
  |
         68 | unix_socket_directories    | /var/run/postgresql                    | t
  |
        108 | ssl                        | on                                     | t
  |
        110 | ssl_cert_file              | /etc/ssl/certs/ssl-cert-snakeoil.pem   | t
  |
        113 | ssl_key_file               | /etc/ssl/private/ssl-cert-snakeoil.key | t
  |
        130 | shared_buffers             | 128MB                                  | t
  |
        153 | dynamic_shared_memory_type | posix                                  | t
  |
        247 | max_wal_size               | 1GB                                    | t
  |
        248 | min_wal_size               | 80MB                                   | t
  |
        565 | log_line_prefix            | %m [%p] %q%u@%d                        | t
  |
        603 | log_timezone               | Europe/Moscow                          | t
  |
        607 | cluster_name               | 16/main                                | t
  |
        715 | datestyle                  | iso, mdy                               | t
  |
        717 | timezone                   | Europe/Moscow                          | t
  |
        731 | lc_messages                | en_US.UTF-8                            | t
  |
        733 | lc_monetary                | en_US.UTF-8                            | t
  |
        734 | lc_numeric                 | en_US.UTF-8                            | t
  |
        735 | lc_time                    | en_US.UTF-8                            | t
  |
        741 | default_text_search_config | pg_catalog.english                     | t
  |
(24 rows)

The view displays non-comment lines of configuration files. The applied column indicates whether the specified value will be applied upon reloading the configuration. Specifically, the column will show false if: The change requires a restart of the server. There is a line with the same parameter that will be read later. There is an error in one of the lines where the parameter is specified. The view also displays the configuration file name and line number, making it easy to locate errors.

The pg\_settings View

For example, let’s take the work_mem parameter. It defines how much memory is allocated for operations like sorting or hash join. The default value can be insufficient for some queries. To learn more about the work_mem parameter, check out our Query Performance Tuning course (QPT). Active configuration parameter values are shown in the pg_settings view. For example, here’s its output regarding the work_mem parameter:

=> SELECT name, unit, setting, boot_val, reset_val,
  source, sourcefile, sourceline, pending_restart, context
FROM pg_settings
WHERE name = 'work_mem' \gx

Page 7: Page 7

{#source-figure-007 caption=“Source slide page 7: Page 7”}

-[ RECORD 1 ]---+---------
name            | work_mem
unit            | kB
setting         | 4096
boot_val        | 4096
reset_val       | 4096
source          | default
sourcefile      |
sourceline      |
pending_restart | f
context         | user

Below are the main columns of the pg_settings view: name, unit — parameter name and unit setting — current value boot_val — default value reset_val — initial value for sessions source — source of the current parameter value sourcefile, sourceline — configuration file and line number, if the current value was specified in the file pending_restart — true if the value is changed in the configuration file, but server restart is required to be applied The context column shows what action has to be taken for the parameter change to take effect. Possible values include: internal — cannot be changed, the value is set during installation postmaster — server restart required sighup — requires a reload of the configuration files superuser — superuser can change for their session user — any user can change for their session

Order of Applying Settings

When reloading the configuration, the main file is read first, followed by additional files. If the same parameter is defined multiple times, the latest value is applied. For example, let’s specify the work_mem parameter twice in an additional configuration file:

student$ echo work_mem=12MB | sudo tee /etc/postgresql/16/main/conf.d/work_mem.conf

work_mem=12MB

student$ echo work_mem=8MB | sudo tee -a /etc/postgresql/16/main/conf.d/work_mem.conf

work_mem=8MB

Contents of the /etc/postgresql/16/main/conf.d/work_mem.conf file:

=> SELECT sourcefile, sourceline, name, setting, applied
FROM pg_file_settings WHERE sourcefile LIKE '%/work_mem.conf';

                  sourcefile                  | sourceline |   name   | setting | applied
----------------------------------------------+------------+----------+---------+---------
 /etc/postgresql/16/main/conf.d/work_mem.conf |          1 | work_mem | 12MB    | f
 /etc/postgresql/16/main/conf.d/work_mem.conf |          2 | work_mem | 8MB     | t
(2 rows)

The applied = f for the first line indicates that it will not be applied. The context field of the work_mem parameter says “user”. This means that the parameter can be changed by the user during their session. We will discuss how to do it in a bit. To change the value for all sessions, you just need to reload the configuration file:

=> SELECT pg_reload_conf();

 pg_reload_conf
----------------
 t
(1 row)

Verify that work_mem now has taken the value from the second line of the configuration file:

=> SELECT name, unit, setting, boot_val, reset_val,
  source, sourcefile, sourceline, pending_restart, context
FROM pg_settings
WHERE name = 'work_mem'\gx

Page 8: Page 8

-[ RECORD 1 ]---+---------------------------------------------
name            | work_mem
unit            | kB
setting         | 8192
boot_val        | 4096
reset_val       | 8192
source          | configuration file
sourcefile      | /etc/postgresql/16/main/conf.d/work_mem.conf
sourceline      | 2
pending_restart | f
context         | user

Page 9: postgresql.auto.conf

Configuration file managed by SQL commands

• ALTER SYSTEM
• adds or changes a line
• SET parameter TO value;
• ALTER SYSTEM RESET parameter;
• deletes a line
• ALTER SYSTEM RESET ALL;
• deletes all lines

read after postgresql.conf

• Location
  • always in the data directory (PGDATA)
• Actions needed to apply

same as for postgresql.conf

Speaker notes

The file postgresql.auto.conf is always loaded last. This file is always located in the data catalog (PGDATA).

It should never be modified manually, but only with the ALTER SYSTEM command. ALTER SYSTEM is an SQL interface for managing configuration parameters.

For any changes made with ALTER SYSTEM to take place, the server must reload the configuration files, as it does with postgresql.conf.

The contents of both files (postgresql.conf and postgresql.auto.conf) can be checked using the pg_file_settings view, and the current parameter values are shown in the pg_settings view.

More about the ALTER SYSTEM command:

https://postgrespro.com/docs/postgresql/16/sql-altersystem

Page 10: The ALTER SYSTEM Command and the postgresql.auto.conf File

Let’s set a new value for the work_mem parameter:

=> ALTER SYSTEM SET work_mem TO '16mb';

ERROR:  invalid value for parameter "work_mem": "16mb"
HINT:  Valid units for this parameter are "B", "kB", "MB", "GB", and "TB".

What happened? ALTER SYSTEM checks if the provided value is valid.

=> ALTER SYSTEM SET work_mem TO '16MB';

ALTER SYSTEM

Now it works! The command saves the new value 16MB to a file named postgresql.auto.conf:

=> SELECT pg_read_file('postgresql.auto.conf')
\g (tuples_only=on format=unaligned)

# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
work_mem = '16MB'

However, the value has not been applied yet:

=> SHOW work_mem;

 work_mem
----------
 8MB
(1 row)

For the new work_mem setting to apply, reload the configuration file first:

=> SELECT pg_reload_conf();

 pg_reload_conf
----------------
 t
(1 row)

=> SELECT name, unit, setting, boot_val, reset_val,
  source, sourcefile, sourceline, pending_restart, context
FROM pg_settings
WHERE name = 'work_mem'\gx

-[ RECORD 1 ]---+-------------------------------------------------
name            | work_mem
unit            | kB
setting         | 16384
boot_val        | 4096
reset_val       | 16384
source          | configuration file
sourcefile      | /var/lib/postgresql/16/main/postgresql.auto.conf
sourceline      | 3
pending_restart | f
context         | user

Lines are removed from postgresql.auto.conf with the ALTER SYSTEM RESET command:

=> ALTER SYSTEM RESET work_mem;

ALTER SYSTEM

=> SELECT pg_read_file('postgresql.auto.conf')
\g (tuples_only=on format=unaligned)

Page 11: Page 11

# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.

Reload the configuration file again. The old value from work_mem.conf is now applied:

=> SELECT pg_reload_conf();

 pg_reload_conf
----------------
 t
(1 row)

=> SELECT name, unit, setting, boot_val, reset_val,
  source, sourcefile, sourceline, pending_restart, context
FROM pg_settings
WHERE name = 'work_mem'\gx

-[ RECORD 1 ]---+---------------------------------------------
name            | work_mem
unit            | kB
setting         | 8192
boot_val        | 4096
reset_val       | 8192
source          | configuration file
sourcefile      | /etc/postgresql/16/main/conf.d/work_mem.conf
sourceline      | 2
pending_restart | f
context         | user

Page 12: Current Session

{#source-figure-012 caption=“Source slide page 12: Current Session”}

set until the end of the session or transaction

• setting parameters is transactional
• custom parameters are allowed

Speaker notes

Parameter values can be changed directly during the session with the SET command or the set_config function. The SHOW command or the current_setting function display the current parameter value.

By setting a new value, you can specify its expiration date: until the end of the session (by default) or until the end of the transaction (SET LOCAL).

In any case, setting parameters is transactional: if the current transaction is rolled back, any parameters modified within it will return to the state they were in at the start of the transaction.

In addition to the PostgreSQL system parameters, the same commands and functions can be used to create and get the values of custom parameters.

Page 13: Setting Parameters for the Current Session

The SET command is used to set parameter values for the duration of the current session:

=> SET work_mem TO '24MB';

SET

The set_config function also works:

=> SELECT set_config('work_mem', '32MB', false);

 set_config
------------
 32MB
(1 row)

The third parameter of the function defines if the parameter should be used for the duration of the current transaction (true) or until the end of the current session (false). This may be important when using a connection pool, when transactions by multiple users may be performed within the same session.

Checking Parameter Values During a Session

There are multiple ways to check the current parameter value:

=> SHOW work_mem;

 work_mem
----------
 32MB
(1 row)

=> \dconfig work_mem

List of configuration parameters
 Parameter | Value
-----------+-------
 work_mem  | 32MB
(1 row)

=> SELECT current_setting('work_mem');

 current_setting
-----------------
 32MB
(1 row)

=> SELECT name, setting, unit FROM pg_settings WHERE name = 'work_mem';

   name   | setting | unit
----------+---------+------
 work_mem | 32768   | kB
(1 row)

Reset the value to what was active at the start of the session:

=> RESET work_mem;

RESET

Setting Parameter Values within a Transaction

Open a transaction and set a new work_mem value:

=> BEGIN;

BEGIN

=> SET work_mem TO '64MB';

SET

Page 14: Page 14

=> SHOW work_mem;

 work_mem
----------
 64MB
(1 row)

If the transaction is rolled back, so is the new parameter value. If the transaction commits, however, the parameter remains changed.

=> ROLLBACK;

ROLLBACK

=> SHOW work_mem;

 work_mem
----------
 8MB
(1 row)

You can set a new parameter value just for the duration of the current transaction:

=> BEGIN;

BEGIN

=> SET LOCAL work_mem TO '64MB'; -- or set_config('work_mem','64MB',true);

SET

=> SHOW work_mem;

 work_mem
----------
 64MB
(1 row)

=> COMMIT;

COMMIT

When the transaction commits, the old value returns:

=> SHOW work_mem;

 work_mem
----------
 8MB
(1 row)

Custom Parameters

Custom parameters can be defined during session. You can also check if such a parameter is already defined. Custom parameter names must include the dot (.) in order to distinguish them from standard parameters.

=> SELECT CASE
  WHEN current_setting('myapp.currency_code', true) IS NULL
    THEN set_config('myapp.currency_code', 'RUB', false)
  ELSE
    current_setting('myapp.currency_code')
END;

 current_setting
-----------------
 RUB
(1 row)

Now myapp.currency_code can be used as a global variable during the session:

=> SELECT current_setting('myapp.currency_code');

 current_setting
-----------------
 RUB
(1 row)

Page 15: Page 15

Custom parameters can be defined in configuration files. This will make them initialize in all sessions.

Page 16: Takeaways

The main configuration file is postgresql.conf

• ALTER SYSTEM is an SQL interface for managing configuration parameters stored in postgresql.auto.conf
• When configuration files are modified, they have to be reloaded
• Some parameters can be changed just for the current session
• Changes to some parameters require a server restart to apply

Page 17: Practice

1. Get a list of parameters that require a server restart to apply.

• 2. In the additional included configuration file, make an error when changing the max_connections parameter.
  • Restart the server. Make sure that the server does not start and
  • check the message log.
  • Fix the error and start the server.

Speaker notes

2. To get the location of the postgresql.conf file, check the value of the config_file parameter, as shown in the demonstration.

At the end of this file, you will find include_dir directive that loads additional configuration files.

Create a file named max_connections.conf in the directory specified by this directive. Edit this file either as the owner (the postgres user) or as a superuser.

To obtain the necessary privileges, use the sudo command.

Use your preferred text editor. For example: sudo nano /etc/postgresql/16/main/conf.d/max_connections.conf

To exit nano, use the Ctrl+X key combination.

Page 18: 1. Parameters that Require a Server Restart to Apply

=> SELECT name, setting, unit FROM pg_settings WHERE context = 'postmaster';

                name                 |                 setting                 | unit
-------------------------------------+-----------------------------------------+------
 archive_mode                        | off                                     |
 autovacuum_freeze_max_age           | 200000000                               |
 autovacuum_max_workers              | 3                                       |
 autovacuum_multixact_freeze_max_age | 400000000                               |
 bonjour                             | off                                     |
 bonjour_name                        |                                         |
 cluster_name                        | 16/main                                 |
 config_file                         | /etc/postgresql/16/main/postgresql.conf |
 data_directory                      | /var/lib/postgresql/16/main             |
 data_sync_retry                     | off                                     |
 debug_io_direct                     |                                         |
 dynamic_shared_memory_type          | posix                                   |
 event_source                        | PostgreSQL                              |
 external_pid_file                   | /var/run/postgresql/16-main.pid         |
 hba_file                            | /etc/postgresql/16/main/pg_hba.conf     |
 hot_standby                         | on                                      |
 huge_page_size                      | 0                                       | kB
 huge_pages                          | try                                     |
 ident_file                          | /etc/postgresql/16/main/pg_ident.conf   |
 ignore_invalid_pages                | off                                     |
 jit_provider                        | llvmjit                                 |
 listen_addresses                    | localhost                               |
 logging_collector                   | off                                     |
 max_connections                     | 100                                     |
 max_files_per_process               | 1000                                    |
 max_locks_per_transaction           | 64                                      |
 max_logical_replication_workers     | 4                                       |
 max_pred_locks_per_transaction      | 64                                      |
 max_prepared_transactions           | 0                                       |
 max_replication_slots               | 10                                      |
 max_wal_senders                     | 10                                      |
 max_worker_processes                | 8                                       |
 min_dynamic_shared_memory           | 0                                       | MB
 old_snapshot_threshold              | -1                                      | min
 port                                | 5432                                    |
 recovery_target                     |                                         |
 recovery_target_action              | pause                                   |
 recovery_target_inclusive           | on                                      |
 recovery_target_lsn                 |                                         |
 recovery_target_name                |                                         |
 recovery_target_time                |                                         |
 recovery_target_timeline            | latest                                  |
 recovery_target_xid                 |                                         |
 reserved_connections                | 0                                       |
 shared_buffers                      | 16384                                   | 8kB
 shared_memory_type                  | mmap                                    |
 shared_preload_libraries            |                                         |
 superuser_reserved_connections      | 3                                       |
 track_activity_query_size           | 1024                                    | B
 track_commit_timestamp              | off                                     |
 unix_socket_directories             | /var/run/postgresql                     |
 unix_socket_group                   |                                         |
 unix_socket_permissions             | 0777                                    |
 wal_buffers                         | 512                                     | 8kB
 wal_decode_buffer_size              | 524288                                  | B
 wal_level                           | replica                                 |
 wal_log_hints                       | off                                     |
(57 rows)

2. Setting the max\_connections Parameter

The current value of max_connections:

=> \dconfig max_conn*

Page 19: Page 19

List of configuration parameters
    Parameter    | Value
-----------------+-------
 max_connections | 100
(1 row)

Imagine we try to set it to 50, but mistype and enter the letter O instead of the zero:

student$ echo max_connections=5O | sudo tee /etc/postgresql/16/main/conf.d/max_connections.conf

max_connections=5O

The error can be discovered by looking at the pg_file_settings view:

=> SELECT * FROM pg_file_settings WHERE name = 'max_connections'\gx

-[ RECORD 1 ]---------------------------------------------------
sourcefile | /etc/postgresql/16/main/postgresql.conf
sourceline | 65
seqno      | 6
name       | max_connections
setting    | 100
applied    | f
error      |
-[ RECORD 2 ]---------------------------------------------------
sourcefile | /etc/postgresql/16/main/conf.d/max_connections.conf
sourceline | 1
seqno      | 25
name       | max_connections
setting    | 5O
applied    | f
error      | setting could not be applied

But here, we didn’t look at pg_file_settings and restarted the server right away:

=> \q

student$ sudo pg_ctlcluster 16 main restart

Job for postgresql@16-main.service failed because the service did not take the steps
required by its unit configuration.
See "systemctl status postgresql@16-main.service" and "journalctl -xeu
postgresql@16-main.service" for details.

The server does not start. The reason is recorded in the server log. Here is what it says:

student$ tail -n 5 /var/log/postgresql/postgresql-16-main.log

2025-11-27 13:56:17.124 MSK [31821] LOG:  database system is shut down
2025-11-27 13:56:17.380 MSK [32454] LOG:  invalid value for parameter "max_connections":
"5O"
2025-11-27 13:56:17.382 MSK [32454] FATAL:  configuration file
"/etc/postgresql/16/main/conf.d/max_connections.conf" contains errors
pg_ctl: could not start server
Examine the log output.

Let’s fix the error:

student$ echo max_connections=50 | sudo tee /etc/postgresql/16/main/conf.d/max_connections.conf

max_connections=50

The server is not running, so we’ll use an operating system command to verify.

student$ cat /etc/postgresql/16/main/conf.d/max_connections.conf

max_connections=50

Restart the server:

student$ sudo pg_ctlcluster 16 main start

The server starts. Check the max_connections value:

student$ psql

=> SHOW max_connections;

 max_connections
-----------------
 50
(1 row)

Page 20: Page 20

Page 21: Practice+

1. Set the parameter work_mem = 32MB in the psql tool’s command line options. 2. In the Ubuntu package distribution, the postgresql.conf file is not located in the PGDATA directory. How does the server find this configuration file at startup?

Speaker notes

1. Use either the options key in the connection string or the PGOPTIONS environment variable.

More on how connection strings are formed:

https://postgrespro.com/docs/postgresql/16/libpq-connect#LIBPQ-CONNST RING

2. To see the location of the postgresql.conf file, check the config_file parameter.

To find where the parameter is set, run the ps command for the postgres main process. The process ID (PID) is the first line of the postmaster.pid file, which is located in the data directory (PGDATA).

Page 22: 1. Setting Parameters at Application Start

If the app uses the libpq library to connect to the server, there are two ways you can set parameters at application start. The first way is to add the options key to the connection string:

student$ psql "options='-c work_mem=32MB'" -c 'SHOW work_mem'

 work_mem
----------
 32MB
(1 row)

The second way is set the PGOPTIONS environment variable:

student$ export PGOPTIONS='-c work_mem=32MB'; psql -c 'SHOW work_mem'

 work_mem
----------
 32MB
(1 row)

2. The config\_file Variable

The file postgresql.conf isn’t located within the data catalog:

=> \dconfig (config_file|data_directory)

             List of configuration parameters
   Parameter    |                  Value
----------------+-----------------------------------------
 config_file    | /etc/postgresql/16/main/postgresql.conf
 data_directory | /var/lib/postgresql/16/main
(2 rows)

How does the server know where postgresql.conf is? In the Ubuntu package distribution, the config_file value is defined in the server startup command text. This allows the postgresql.conf file to be stored outside of PGDATA. To check the server’s startup command line, you can use the ps command. Find the PID of the main server process – it’s listed in the first line of the postmaster.pid file located in the PGDATA directory:

student$ sudo cat /var/lib/postgresql/16/main/postmaster.pid | head -n 1

32795

Now we can retrieve the postmaster process command line:

student$ ps -p 32795 -ho command

/usr/lib/postgresql/16/bin/postgres -D /var/lib/postgresql/16/main -c
config_file=/etc/postgresql/16/main/postgresql.conf

04 Lesson 04: Architecture / PostgreSQL Fundamentals

Page 1: Architecture / PostgreSQL Fundamentals

Copyright

© Postgres Professional, 2017–2025 Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Igor Gnatyuk 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: Topics

• Client-Server Protocol
• Transactionality and its Implementation
• Query Processing and Execution
• Processes and Memory Structures
• Storing Data on Disk and Data Processing
• System Extensibility

Page 3: Client and Server

{#source-figure-003 caption=“Source slide page 3: Client and Server”}

• connection
• authentication
• query generation
• query execution
• transaction management
• transactionality support

Speaker notes

A client application, such as psql or any other program written in any programming language, connects to the server and “communicates” with it. In order for the client and the server to understand each other, they must use the same communication protocol. Usually, the client uses a driver that implements the protocol and provides a set of functions to use in the program. Internally, the driver can use the standard protocol implementation (the libpq library), or can implement the protocol itself.

The language the client is written in is unimportant, as the functionality behind the syntax is defined by the protocol. As an example, we will use the SQL language and the psql client. Of course, no one really would program a client in SQL, but we will use it here purely for educational purposes. It should not be that difficult to substitute any of the SQL commands provided below with corresponding statements in your programming language of choice.

Generally speaking, a connection protocol allows the client to connect to a database in a cluster. The server performs authentication: decides if the client should be allowed to connect, i.e. by demanding a password.

Then, the client sends the server queries in the SQL language, the server executes the queries and sends back the results. A powerful and convenient query language is one of the fundamentals of relational databases.

Another one is the ability to maintain transaction support.

https://postgrespro.com/docs/postgresql/16/protocol

Page 4: Transactions

{#source-figure-004 caption=“Source slide page 4: Transactions”}

• atomicity — everything or nothing consistency — integrity constraints and user restrictions isolation — parallel processes impact
• durability — no data loss after a failure

Speaker notes

A transaction is a sequence of operations that preserves the consistency of data, provided that the operations are performed completely and without interference from other transactions.

Transactions must satisfy four properties collectively known as ACID:

● Atomicity. A transaction is either executed in full or not executed at all. To that end, the beginning of a transaction is marked with the BEGIN command, and the end with either COMMIT (commit changes) or ROLLBACK (undo changes).

● Consistency. Transactions move the database from one consistent state to another consistent one (consistency here means that certain restrictions are fulfilled).

● Isolation. Transactions running simultaneously should not affect the specific one.

● Durability. Once data is committed, it should not be lost even after a server failure.

In PostgreSQL, the client application is the side usually responsible for transaction management (that is, for determining what commands make up a transaction, and for committing or canceling the transaction). Transactions can be managed on the server side by stored procedures.

https://postgrespro.com/docs/postgresql/16/sql-begin

https://postgrespro.com/docs/postgresql/16/sql-savepoint

https://postgrespro.com/docs/postgresql/16/transactions

Page 5: Transaction Management

By default, psql runs in autocommit mode:

=> \echo :AUTOCOMMIT

on

This means that any single command sent without explicitly opening the transaction is committed immediately. Check if this mode is enabled by default in the PostgreSQL driver for your favourite programming language. Create a table with one row:

=> CREATE TABLE t(
    id integer,
    s text
);

CREATE TABLE

=> INSERT INTO t(id, s) VALUES (1, 'foo');

INSERT 0 1

Will another transaction see this table and row?

=> SELECT * FROM t;

 id |  s
----+-----
  1 | foo
(1 row)

Yes. Compare the result:

=> BEGIN; -- explicit start of transaction

BEGIN

=> INSERT INTO t(id, s) VALUES (2, 'bar');

INSERT 0 1

What will the other transaction see now?

=> SELECT * FROM t;

 id |  s
----+-----
  1 | foo
(1 row)

The changes are not yet committed so the other transaction does not see them.

=> COMMIT;

COMMIT

What about now?

=> SELECT * FROM t;

 id |  s
----+-----
  1 | foo
  2 | bar
(2 rows)

When autocommit is off, a transaction is implicitly opened upon command input. The command then must be committed manually.

=> \set AUTOCOMMIT off

=> INSERT INTO t(id, s) VALUES (3, 'baz');

INSERT 0 1

What do we see now?

Page 6: Page 6

=> SELECT * FROM t;

 id |  s
----+-----
  1 | foo
  2 | bar
(2 rows)

The changes are not there, as the transaction was opened implicitly.

=> COMMIT;

COMMIT

Now, finally:

=> SELECT * FROM t;

 id |  s
----+-----
  1 | foo
  2 | bar
  3 | baz
(3 rows)

Turn the default autocommit mode back on.

=> \set AUTOCOMMIT on

Individual changes can be rolled back without interrupting the entire transaction (although this is rarely used).

=> BEGIN;

BEGIN

=> SAVEPOINT sp;

SAVEPOINT

=> INSERT INTO t(id, s) VALUES (4, 'qux');

INSERT 0 1

=> SELECT * FROM t;

 id |  s
----+-----
  1 | foo
  2 | bar
  3 | baz
  4 | qux
(4 rows)

=> ROLLBACK TO sp;

ROLLBACK

Check the table:

=> SELECT * FROM t;

 id |  s
----+-----
  1 | foo
  2 | bar
  3 | baz
(3 rows)

The changes have been rolled back, but the transaction is still open:

=> INSERT INTO t(id, s) VALUES (4, 'xyz');

INSERT 0 1

Page 7: Page 7

=> COMMIT;

COMMIT

=> SELECT * FROM t;

 id |  s
----+-----
  1 | foo
  2 | bar
  3 | baz
  4 | xyz
(4 rows)

Page 8: Query Processing

{#source-figure-008 caption=“Source slide page 8: Query Processing”}

parsing ← system catalog rewriting ← rules planning ← statistics execution ← data

Speaker notes

Query execution is complicated. First, a query is sent from a client to the server as text. The server parses the text, analyzing its syntax (whether letters are formed into words, and words into commands) and semantics (whether there are tables and other objects in the database that the query refers to by name). To do that, the server needs data on what is actually stored in the database. This meta-data is called the system catalog and is stored in special tables in the same database.

A query can be rewritten (transformed). For example, a view name can be substituted with the query text. Users can implement their own transformations using the rule system.

SQL is a declarative language: a query defines what data to get, but not how to get it. It is at this point when the query (already parsed and presented in the form of a tree) is passed on to the planner, which develops an execution plan. For example, the planner can decide whether or not to use indexes to find the data for the query. To plan the execution efficiently, the planner needs certain information about the tables it is going to work with, such as the size of the tables and the distribution of data within them. Together, this information is called statistics.

When a plan is selected, the query is executed in accordance with it, and the result is returned to the client in its entirety.

https://postgrespro.com/docs/postgresql/16/query-path

This is convenient and simple when we are talking about just a row or two, provided by the simple mode of the protocol.

Page 9: Prepared Statements

{#source-figure-009 caption=“Source slide page 9: Prepared Statements”}

parsing rewriting

• binding ← parameter values planning execution

Speaker notes

Each query goes through the steps listed above: parsing, rewriting, planning and execution. But if the same query (possibly with different parameters) is executed over and over again, there is no point in parsing it anew every time.

Therefore, in addition to the usual query execution process, the PostgreSQL protocol provides an extended mode that can control statement execution more precisely.

One of its features is the ability to prepare a statement. When a statement is prepared, it is parsed and rewritten as usual and its parse tree is saved.

When the statement is executed, specific parameter values are bound to it. If necessary, planning is redone (in some cases, PostgreSQL remembers the query plan and does not repeat this step). Then, the statement is executed.

Another advantage of prepared statements is that they are protected from possible SQL injections.

https://postgrespro.com/docs/postgresql/16/sql-prepare

https://postgrespro.com/docs/postgresql/16/sql-execute

Page 10: Prepared Statements

In SQL, you can prepare a statement by using the PREPARE command (it is a PostgreSQL extension not present in the SQL standard):

=> PREPARE q(integer) AS
  SELECT * FROM t WHERE id = $1;

PREPARE

The statement is parsed and rewritten, and the parse tree is saved. A prepared statement can be called by its name using arbitrary parameters:

=> EXECUTE q(1);

 id |  s
----+-----
  1 | foo
(1 row)

For non-parametric statements, an execution plan is saved as well. For queries with parameters, as in the example, the parameter values are taken into account at the planning stage. The planner may decide that the plan built without considering the parameters is good enough. In this case, it will not try to plan the query again. How do you prepare and execute a statement using your favourite programming language? Can you execute a statement WITHOUT preparing it? All prepared statements in the current session can be displayed with:

=> SELECT * FROM pg_prepared_statements \gx

-[ RECORD 1 ]---+---------------------------------
name            | q
statement       | PREPARE q(integer) AS           +
                |   SELECT * FROM t WHERE id = $1;
prepare_time    | 2025-11-27 13:47:05.19667+03
parameter_types | {integer}
result_types    | {integer,text}
from_sql        | t
generic_plans   | 0
custom_plans    | 1

Page 11: Cursors

{#source-figure-011 caption=“Source slide page 11: Cursors”}

parsing rewriting

• binding ← parameter values planning execution
• output

Speaker notes

The client may not want to get all the output at once. There can be too much data, and not all of it may be needed.

This issue is solved by cursors, another feature of the extended mode. The protocol can open a cursor for any operator, and then receive the output row by row.

A cursor can be imagined as a sliding window that shows only a part of the output at a time. When an output row is received, the window shifts down. In other words, cursors allow you to work with relational data (that comes in sets) iteratively, row by row.

An open cursor is represented on the server by a so-called portal. This term is mentioned in the documentation, but in general, the words “cursor” and “portal” can be considered synonyms.

A statement used within a cursor is implicitly prepared (that is, its parsing tree and possibly execution plan are saved).

https://postgrespro.com/docs/postgresql/16/sql-declare

https://postgrespro.com/docs/postgresql/16/sql-fetch

Page 12: Cursors

When the SELECT command is executed, the server sends and the client receives all the rows at once:

=> SELECT * FROM t ORDER BY id;

 id |  s
----+-----
  1 | foo
  2 | bar
  3 | baz
  4 | xyz
(4 rows)

Cursors let the client to retrieve data in batches of rows.

=> BEGIN;

BEGIN

=> DECLARE c CURSOR FOR
  SELECT * FROM t ORDER BY id;

DECLARE CURSOR

=> FETCH c;

 id |  s
----+-----
  1 | foo
(1 row)

You can set the size of the batch:

=> FETCH 2 c;

 id |  s
----+-----
  2 | bar
  3 | baz
(2 rows)

The batch size is paramount for huge outputs, when processing them one row at a time is inefficient. What if we reach the end of the table?

=> FETCH 2 c;

 id |  s
----+-----
  4 | xyz
(1 row)

=> FETCH 2 c;

 id | s
----+---
(0 rows)

FETCH will just stop returning rows. All regular programming languages have a way to check for this condition. How do you get data row by row using the cursor in your favorite programming language? Is it possible NOT to use the cursor and get all the rows at once? How is the cursor batch size set? You can close your cursor when done, freeing up some resources:

=> CLOSE c;

CLOSE CURSOR

However, cursors are automatically closed when the transaction ends, so explicit closing is optional (except WITH HOLD cursors.)

=> COMMIT;

Page 13: Page 13

COMMIT

Page 14: Processes and Memory

{#source-figure-014 caption=“Source slide page 14: Processes and Memory”}

Speaker notes

Between processing queries from clients, the server must store technical information, such as parsed queries and their plans and the status of open cursors (portals). Where is it stored and how?

Under the hood, a PostgreSQL server consists of several interacting processes. First of all, when the server starts, a process traditionally called postmaster is started. It starts all other processes (using the fork system call in Unix). It also “babysits” them: if any of the processes crashes, postmaster will restart it (or restart the entire server if it considers that the failed process could have damaged any of the shared data).

Operations of the server are maintained by a number of background processes. The main ones will be discussed in the following lessons.

In order for the processes to exchange information between them, postmaster allocates shared memory that all the processes can access. In addition to shared memory, each process has its own local memory, accessible only to itself.

Postmaster also listens for incoming connections. For each connecting client, postmaster generates a designated backend process for the client to communicate with on the server side, and each client gets its own process. The backend process performs authentication, among other things.

The space required to execute a client’s query (parsed queries and their plans, cursor states, system catalog cache, a place to sort data, etc.) is allocated in the local memory of the backend process of this client.

Page 15: Multiple Clients

{#source-figure-015 caption=“Source slide page 15: Multiple Clients”}

Speaker notes

When multiple clients connect to a server, each one gets a backend process created for it. As long as there are not too many clients, RAM is sufficient, and connections do not occur too often, sustaining many connections at once is not a problem in itself.

However, when multiple processes try to access the same database object, things must be done to ensure that one process will not change the data while another is in the process of reading it.

For objects in shared memory, this is ensured by short-term locks. PostgreSQL does this carefully enough so that the system scales well with an increase in the number of processors (cores).

Tables are more complicated. Locks will have to be held until the end of transactions (that is, potentially for a long time), so scalability may suffer. To avoid that, PostgreSQL uses a multiversion concurrency control mechanism (MVCC) and snapshot isolation: multiple versions of the same data can exist simultaneously, and each process sees only its own (but always consistent) data snapshot. Now, only those processes that are trying to change data that has already been changed, but not yet committed by other processes, will be locked.

MVCC is the main mechanism that enables the first three properties of transactions (atomicity, consistency, and isolation). We will talk about it more in its dedicated lesson.

Page 16: Connection Pool

{#source-figure-016 caption=“Source slide page 16: Connection Pool”}

Speaker notes

If there are too many clients, or connections are established and broken too often, using a connection pool may help. This functionality is usually provided by the application server or third-party pool managers (the most popular of which is pgBouncer).

With a connection pool in place, clients connect not to the PostgreSQL server directly, but to the pool manager. The manager keeps several connections to the database server open and uses free ones to fulfill client queries. From the server’s point of view, the number of clients remains constant regardless of how many clients access the pool manager.

The drawback is that multiple clients end up sharing the same backend process, which, as we remember, stores client-specific state in its local memory (such as parsed queries for prepared statements). Therefore, care should be taken when developing applications for such deployments.

One of pgBouncer’s features is the ability to temporarily pause client connections without disconnecting them. This pause can be used for server software updates or other operations that require a server restart.

The use of connection pools is discussed in greater detail in the DEV2 course.

Page 17: Data Storage

{#source-figure-017 caption=“Source slide page 17: Data Storage”}

Speaker notes

Data is stored as regular OS files on disks. How exactly the data is distributed among the files is discussed in one of following lessons.

Logically, the files are divided into pages (sometimes the term block is used). A page is usually 8KB in size. It can be changed within some limits (16 KB or 32 KB), but only during server compilation. A cluster that has been compiled and started up this way can work with pages of only one size.

Each page has a certain internal structure. It contains a header and actual data. There may be free space between them if the page is not fully occupied.

Since disks work much slower than RAM (especially HDD, but SSD too), data heading to disk is cached first. A buffer cache, a certain amount of space in RAM, is allocated for recently read pages. The idea is that the system may want to read the same pages multiple times, and keeping them on hand may save time compared to repeated disk scans. Any recently changed data is also cached for some time before being written on disk.

Page 18: Data Storage

{#source-figure-018 caption=“Source slide page 18: Data Storage”}

Speaker notes

The PostgreSQL buffer cache is located in shared memory so that all processes have access to it.

PostgreSQL does not directly access disks storing its data. Instead, it relies on the operating system. The operating system also has its own data cache. Therefore, if a page is not found in the buffer cache, there is a chance that it is in the OS cache and access to the disk will be avoided.

In case of a failure (for example, power supply dies), the contents of the RAM are lost, and the data changed but not yet written to disk will be lost. This is unacceptable and breaks the durability property of transactions. To avoid that, PostgreSQL keeps a log that allows it to redo lost operations and restore data to a consistent state. We will talk about the buffer cache and the write-ahead log in the dedicated lesson later on.

Page 19: Extensibility

{#source-figure-019 caption=“Source slide page 19: Extensibility”}

Speaker notes

PostgreSQL is designed with extensibility in mind. It allows to create new data types based on existing ones, write stored procedures for data processing, and offers a convenient toolkit for administration, monitoring, and performance tuning.

You can always write an extension that adds the necessary functionality that you need. Most extensions can be installed “hot”, without stopping the server. Thanks to this architecture, there are plenty of existing extensions doing things such as:

● adding support for programming languages (in addition to standard SQL, PL/pgSQL, PL/Perl, PL/Python and PL/Tcl);

● introducing new data types and operators to work with them;

● creating new index types that work more efficiently with specific data types (in addition to standard B-trees, hash indexes, GiST, SP-GiST, GIN, BRIN);

● starting background workers to perform additional tasks;

● connecting to external data sources;

● collecting system load information, performing monitoring, and generating reports;

● exploring system data structures.

Extensibility is discussed in more detail in the DBA2 and DEV2 courses.

Page 20: Takeaways

A server manages a database cluster

• The protocol allows clients to connect to the server, transmit queries and manage transactions
• Each client is served by a dedicated backend process
• Data is stored in files and accessed via the operating system
• Data is cached both in local memory (system catalog, parsed queries) and in shared memory (buffer cache)

05 Lesson 05: Architecture / Isolation and MVCC

Page 1: Architecture / Isolation and MVCC

Copyright

© Postgres Professional, 2017–2025 Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Igor Gnatyuk 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: Topics

• Multiversion Concurrency Control
• Data Snapshot
• Isolation Levels
• Vacuuming and its Horizon
• Locking
• Transaction Status

Page 3: MVCC

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

Storing multiple versions of the same row

• versions have different validity time
• timestamp = transaction ID (IDs are given in ascending order)

Speaker notes

When multiple sessions are running at the same time, two transactions may access the same row at the same time. If both transactions are just reading the row, there is no problem. If both transactions try to write, no problem either (in this case, they line up and make the changes one after the other). The tricky part is when one transaction wants to read a row and another one wants to change it at the same time.

There are two simple ways about it. You can let such transactions block each other, but then performance suffers. Otherwise, you can let the reading transaction immediately see the changes made by the writing transaction, even if they are not committed (this is called a “dirty read”). This is dangerous, because the changes can be rolled back.

PostgreSQL goes the hard way and utilizes what is known as Multiversion concurrency control. In essence, the system stores multiple versions of each row. So, a writing transaction operates on its own version, while a reading transaction sees its own version.

To distinguish between the versions, PostgreSQL marks each one with two timestamps, which together specify a version’s “validity time”.

The timestamps are essentially just transaction IDs, which always come in ascending order. (In reality, the whole thing is a bit more complicated, but not worth getting into right now.) Upon creation, a row version is marked with the ID of the transaction that executed the INSERT command. When deleted, the version is marked with the ID of the transaction that did the DELETE command (but is not physically deleted). An UPDATE command is a DELETE and an INSERT executed back to back.

https://postgrespro.com/docs/postgresql/16/mvcc-intro

Page 4: Data Snapshot

{#source-figure-004 caption=“Source slide page 4: Data Snapshot”}

Representation of database data in a consistent state at a specific point in time

• transaction ID defines the point in time
• list of active transactions helps the system
• to exclude changes that have not been committed

Speaker notes

PostgreSQL uses snapshot-based transaction isolation.

A transaction accessing a table should see only one of the versions of each row (or none at all). To achieve this, PostgreSQL presents the transaction with a data snapshot created at a certain point in time. The snapshot includes the most recent versions of all committed rows but does not include any non-committed changes from active transactions. In other words, the snapshot takes the version of each row that corresponds to the moment when the snapshot was created.

A snapshot is not a physical copy of the data, but just a few numbers:

● the ID of the last committed transaction at the time of snapshot,

● list of active transactions at that point in time.

The list is needed in order to exclude from the snapshot any changes those transactions may have made but not yet committed.

With just these numbers, we can always tell which row version will be visible in the snapshot. Sometimes it will be the current (most recently committed) version, as with row 1 in the diagram. Sometimes not: row 2 has been deleted after the snapshot has been created (and the change has already been committed), but the transaction still continues to see it while working with the snapshot. This is the correct behavior, the snapshot gives a consistent representation of data at the selected point in time.

Some rows will not get into the snapshot at all: row 3 was deleted before the snapshot was made, so it is not included.

Page 5: Isolation Levels

Read Uncommitted

• not supported by PostgreSQL: works as Read Committed
• Read Committed (default)
  • the snapshot is created as of the time a statement starts
  • repeated query may return different data
• Repeatable Read
  • the snapshot is created as of the time the first transaction statement starts
  • a transaction may fail with a serialization error
• Serializable
  • total isolation, but additional overhead
  • a transaction may fail with a serialization error

Speaker notes

The SQL standard defines four isolation levels: the stricter the level, the less concurrent transactions affect each other. At the time when the standard was adopted, it was believed that the stricter the level, the more difficult it is to implement and the stronger its impact on performance (since then, these views have changed somewhat).

The most lax level of Read Uncommitted allows dirty reads. It is not supported by PostgreSQL, because it is of no practical value and does not give a performance gain.

The Read Committed level is the default isolation level in PostgreSQL. At this level, data snapshots are created at the beginning of each SQL statement execution. Thus, the statement works with an unchanged and consistent data snapshot, but two identical queries following one after the other may show different data.

At the Repeatable Read level, the snapshot is built at the beginning of a transaction (when executing the first statement). This makes all queries inside the same transaction see the same data. This level is convenient, for example, for generating reports from several queries.

The Serializable level guarantees total isolation. At this level, you can use any statements as if the transaction is running alone. The drawback is that some transactions will fail, and your application must be able to repeat such transactions.

https://postgrespro.com/docs/postgresql/16/transaction-iso

Page 6: Row Version Visibility

How do you verify that the same row can exist in multiple versions? Create a table:

=> CREATE TABLE t(s text);

CREATE TABLE

And insert one row. Remember that if you do not explicitly start a transaction with BEGIN, psql executes the command and immediately commits the result:

=> INSERT INTO t VALUES ('Version one');

INSERT 0 1

Start the transaction and get its ID:

=> BEGIN;

BEGIN

=> SELECT pg_current_xact_id();

 pg_current_xact_id
--------------------
                736
(1 row)

The transaction sees the first (and so far the only) version of the row:

=> SELECT *, xmin, xmax FROM t;

      s      | xmin | xmax
-------------+------+------
 Version one |  735 |    0
(1 row)

Here, the hidden columns show the transaction IDs that limit the visibility of the row version: xmin is the ID of the previous transaction which created the version, and xmax=0 means that this version is current. Now start another transaction in another session:

=> BEGIN;

BEGIN

=> SELECT pg_current_xact_id();

 pg_current_xact_id
--------------------
                737
(1 row)

The transaction sees the same and only version:

=> SELECT *, xmin, xmax FROM t;

      s      | xmin | xmax
-------------+------+------
 Version one |  735 |    0
(1 row)

Now change the row from within the second transaction.

=> UPDATE t SET s = 'Version two';

UPDATE 1

This is what we get:

=> SELECT *, xmin, xmax FROM t;

Page 7: Page 7

      s      | xmin | xmax
-------------+------+------
 Version two |  737 |    0
(1 row)

What will the first transaction see?

=> SELECT *, xmin, xmax FROM t;

      s      | xmin | xmax
-------------+------+------
 Version one |  735 |  737
(1 row)

Since the change is not yet committed, the first transaction continues to see the first version of the row.

=> COMMIT;

COMMIT

What will the first transaction see now?

=> SELECT *, xmin, xmax FROM t;

      s      | xmin | xmax
-------------+------+------
 Version two |  737 |    0
(1 row)

The first transaction also sees the second version of the string. After commit, the first version of the row is no longer visible in any transaction.

=> COMMIT;

COMMIT

Page 8: Vacuuming and its Horizon

{#source-figure-008 caption=“Source slide page 8: Vacuuming and its Horizon”}

There is a single horizon for each database

• Long-running transactions can hold back the horizon
  • thereby blocking the vacuuming of outdated row versions

Speaker notes

MVCC makes it possible to effectively implement snapshot-based isolation, but as a result, old (“dead”) row versions (“dead tuples”) accumulate in table pages.

Historical versions are needed for some time so that transactions can work with their data snapshots and, in case of rollback, get back to old values. For each database, there is a transaction ID (xid), so that all historical versions deleted by transactions with lower xids are no longer visible in any snapshot. This xid is called vacuum horizon.

Vacuuming is performed by special background processes; but can also be executed manually using the VACUUM command.

Long-running transactions and queries can hold back the horizon, which prevents the removal of accumulated row versions. If you do not vacuum historical data in a timely manner, tables and indexes will bloat, consuming excessive disk space, and the search for current row versions will slow down.

https://postgrespro.com/docs/postgresql/16/routine-vacuuming

Page 9: Locking

Row locks

• reading never locks rows
• changing rows locks them for changes, but not for reads
• Table locks
  • prohibit changing or deleting a table while it is being worked on
  • prohibit reading the table when rebuilding or moving
  • etc.
• Lock lifetime
  • set as needed or manually
  • released automatically upon transaction completion

Speaker notes

So what does MVCC provide? It allows the system to have only the most necessary minimum of locks, thereby increasing performance.

The main locks are set at the row level. Reading never blocks either reading or writing transactions. Changing a row does not lock it for reading. The only case when a transaction will wait for a lock to be released is if it tries to change a row that has already been changed by another transaction that has not been committed yet.

Locks can also be set at a higher level, particularly on tables. They are needed so that no one can delete the table while other transactions are reading data from it, or to prohibit access to the table being rebuilt. Such locks generally do not cause problems, since deleting or rebuilding tables is only done once in a while. However, some changes of table structure trigger implicit table rebuilding, completely blocking access to the table and its indexes. Locks are explained in detail in the Locking module of the DBA2 course.

All necessary locks are set automatically and automatically removed at the end of the transaction. However, if a lock was acquired after the savepoint was set, it will be released immediately when rolling back to that savepoint.

You can also set additional custom locks, but this is rarely necessary.

https://postgrespro.com/docs/postgresql/16/explicit-locking

Page 10: Locking

Repeat the experiment, but now let both transactions try to change the same row.

=> BEGIN;

BEGIN

=> UPDATE t SET s = 'Version three' RETURNING *;

       s
---------------
 Version three
(1 row)

UPDATE 1

And in the second transaction:

=> BEGIN;

BEGIN

=> UPDATE t SET s = 'Version four' RETURNING *;

The second transaction “hung up”: it cannot change the row until the first transaction releases the lock.

=> COMMIT;

COMMIT

Now the second transaction can continue:

      s
--------------
 Version four
(1 row)

UPDATE 1

=> COMMIT;

COMMIT

Both transactions have committed their changes. The first session reads the table again and sees the current row: this is the result committed by the second transaction:

=> SELECT * FROM t;

      s
--------------
 Version four
(1 row)

Page 11: Transaction Status

Transaction status (clog)

• service information; two bits per transaction
• stored in files on disk
• cached in shared memory
• Commit
  • the “transaction committed” bit is set
• Termination
  • the “transaction aborted” bit is set
  • performed as fast as commit (no data rollback needed)

Speaker notes

For multiversion concurrency control to work, the server needs to understand the status of transactions. A transaction can be active or finished. A transaction can end either in a commit or in an abort. Therefore, two bits are required to represent the state of each transaction.

Transaction statuses (commit log, clog) are stored in special service files in the PGDATA/pg_xact directory and worked upon in the server’s shared memory, as to avoid constantly accessing the disk.

At any transaction completion (either successful or not), it is enough to set the appropriate status bits. Both transaction commit and abort occur equally quickly.

If an aborted transaction managed to create new row versions, these versions are not destroyed (there is no “physical” rollback of data). Thanks to the status information, other transactions will see that the transaction that created or deleted the row versions was actually aborted, and will not take changes made by it into account.

Page 12: Takeaways

Multiple versions of each row can be stored in data files

• Transactions work with data snapshots, representations of database data in a consistent state at a specific point in time
• Isolation levels have different snapshot creation times
• Dead row versions beyond the vacuum horizon must be periodically vacuumed
• Writers do not block readers, readers do not block anyone

Page 13: Practice

1. Create a table with one row. Begin a transaction at the Read Committed isolation level and query the table. In another session, delete the row and commit the changes. How many rows will the first transaction see after executing the same query again? Try and see. Complete the first transaction. 2. Repeat the same thing, but now let the transaction work at the isolation level Repeatable Read:

• BEGIN ISOLATION LEVEL REPEATABLE READ;
• Explain the differences.

Page 14: 1. Read Committed Isolation Level

Create a table:

=> CREATE TABLE t(n integer);

CREATE TABLE

=> INSERT INTO t VALUES (42);

INSERT 0 1

Query from the first transaction (default isolation level is Read Committed):

=> BEGIN;

BEGIN

=> SELECT * FROM t;

 n
----
 42
(1 row)

Delete the row in the second transaction and commit the changes:

=> DELETE FROM t;

DELETE 1

Repeat the query:

=> SELECT * FROM t;

 n
---
(0 rows)

The first transaction sees the change: the row has been deleted.

=> COMMIT;

COMMIT

2. Repeatable Read Isolation Level

Return the row:

=> INSERT INTO t VALUES (42);

INSERT 0 1

Query from the first transaction:

=> BEGIN ISOLATION LEVEL REPEATABLE READ;

BEGIN

=> SELECT * FROM t;

 n
----
 42
(1 row)

Delete the row in the second transaction and commit the changes:

=> DELETE FROM t;

DELETE 1

Repeat the query:

=> SELECT * FROM t;

Page 15: Page 15

 n
----
 42
(1 row)

At this isolation level, the first transaction does not see any changes: the row still exists for it.

=> COMMIT;

COMMIT

Page 16: Practice+

1. Begin a transaction and create a new table with one row. Without completing the transaction, open a second session and query the table in it. Check what the transaction will return in the second session.

• Commit the transaction in the first session and repeat the query
• to the table in the second session.
• 2. Repeat task 1, but roll back rather than commit the transaction in the first session. What has changed?
• 3. In the first session, start a transaction and make a query to the previously created table. Will it be possible to delete this table in the second session before the transaction is completed? Try and see.

Page 17: 1. Transactions and DDL Commands: Commit

Start a transaction and create a new table:

=> BEGIN;

BEGIN

=> CREATE TABLE t1(n integer);

CREATE TABLE

=> INSERT INTO t1 VALUES (42);

INSERT 0 1

In the second session, query the table:

=> SELECT * FROM t1;

ERROR:  relation "t1" does not exist
LINE 1: SELECT * FROM t1;
                      ^

Until the transaction that created the table is completed, all other transactions do not see the table. The table will be visible only after the completion of the transaction that created it:

=> COMMIT;

COMMIT

=> SELECT * FROM t1;

 n
----
 42
(1 row)

2. Transactions and DDL Commands: Rollback

Start a transaction and create a new table:

=> BEGIN;

BEGIN

=> CREATE TABLE t2(n integer);

CREATE TABLE

=> INSERT INTO t2 VALUES (42);

INSERT 0 1

The query in the second session sees no changes, as expected:

=> SELECT * FROM t2;

ERROR:  relation "t2" does not exist
LINE 1: SELECT * FROM t2;
                      ^

When the first transaction is rolled back, the table creation command is also rolled back:

=> ROLLBACK;

ROLLBACK

=> SELECT * FROM t2;