Copyright

The textbook, practical assignments, and presentations (hereinafter referred to as documents) are intended for educational purposes.

The documents are protected by copyright and intellectual property laws.

You may copy and print documents for personal use for self-study, as well as for training at training centers and educational institutions authorized by Tantor Labs LLC. Training centers and educational institutions authorized by Tantor Labs LLC may create training courses based on the documents and use the documents in educational programs with the written permission of Tantor Labs LLC.

You may not use the documents for paid training of employees or others without permission from Tantor Labs LLC. You may not license or commercially use the documents, in whole or in part, without permission from Tantor Labs LLC.

When using information from documents (text, images, commands) for non-commercial purposes (presentations, reports, articles, books), please keep a link to the documents.

The text of the documents cannot be changed in any way.

The information contained in these documents is subject to change without notice, and we do not guarantee its accuracy. If you discover any errors or copyright infringements, please notify us.

Disclaimer for content, products and services of third parties:

Tantor Labs LLC and its affiliates assume no liability and expressly disclaim any warranties of any kind, including loss of income, arising from the direct or indirect, special, or incidental use of this document. Tantor Labs LLC and its affiliates are not liable for any losses, costs, or damages arising from the use of the information contained in this document or the use of third-party links, products, or services.

Copyright © 2026, Tantor Labs LLC

Author : Oleg Ivanov

I

Preliminary preparation

To successfully complete the course, basic skills in Linux operating systems and a basic knowledge of the SQL language (SELECT, UPDATE, INSERT, and DELETE commands) are sufficient. Operating system skills include: running a terminal, viewing directory and file contents in the terminal, copying and editing text files using the ls, cp, mv, cat, and mcedit commands, and changing file permissions using the chmod and chown commands .

This course covers the basic tasks of administering PostgreSQL databases. Tantor Postgres version 18 is used in practical exercises.

This course covers the basic tasks of PostgreSQL database administration. While basic, it's not trivial. The level of detail in the topics is high, and even if you're already familiar with PostgreSQL administration, you'll find new information in each topic.

This course is universal: the material is applicable to all PostgreSQL family database management systems . Much of the material applies not only to PostgreSQL version 18 but also to previous versions. Whenever possible, the course materials indicate which versions introduced the features being covered.

Course materials

Course materials:

1) A textbook in the form of a book in pdf format, which contains the theoretical part of the course.

2) Practical assignments in pdf and html format (current versions are available on the website https://dba1.ru )

3) A virtual machine with the Astra Linux 1.8 operating system and Tantor Postgres version 18 DBMS installed . Access to the virtual machine may be provided for the duration of the course, or an .ova image may be provided. The virtual machine image can be used with Oracle VirtualBox version 6.1 or higher or any other virtualization software.

The course materials can be used at any time after the course.

Course sections

Installing and managing DBMS

Installation

Instance management

psql utility

PostgreSQL architecture

General information and memory structures

Multiversion

Routine maintenance

Executing queries

PostgreSQL extensions

Configuring PostgreSQL

Logical and physical structure of the cluster

Diagnostic journal

Safety

Physical and logical redundancy

Physical and logical replication

Tantor Platform Review

Tantor Postgres Features

About the course

The course is designed for in-person or remote learning with an instructor. It consists of a theoretical section divided into chapters, practical exercises, and breaks. Breaks are combined with practical exercises, which are completed independently on a virtual machine prepared for the course.

Approximate schedule:

1) starts at 10:00

2) Lunch break 13:00-14:00. The start of lunch may shift by half an hour between 12:30 and 13:30, as it usually coincides with the break between chapters.

3) the theoretical part ends before 17:00 (on the last day of the course before 15:00).

Chapters are 30-60 minutes long. The exact start time for chapters and the time for practical assignments is determined by the instructor. The length of exercises may vary among students, but this does not affect the effectiveness of the course material. Students can complete exercises during breaks between theoretical sessions or at the end of each day, after the theoretical portion . The order of chapters and exercises does not affect the effectiveness of the course material.

The completion of tasks is not checked.

To successfully master the course material, it is sufficient to:

Listen to the instructor, ask questions if you have any, read the practical assignments, and complete them independently. When completing the practical assignments, you can type commands on the keyboard, but you can also copy them from the assignments into the terminal. Entering commands, correcting typos, and learning the error messages that appear when typing incorrect commands helps you remember the commands better. The impression of understanding the assignments is deceptive; it's important to recall keywords and command capabilities while working.

About Tantor

Since 2016, the Tantor team has been working in the international PostgreSQL DBMS support market, serving clients from Europe, North and South America, and the Middle East. The Tantor team developed the Tantor Platform software and subsequently created the Tantor Postgres DBMS, based on the open-source PostgreSQL DBMS.

In 2021, the company completely refocused on the Russian market, where it focused its core activities on the design and development of the Tantor Postgres DBMS , as well as the development of the Tantor Platform—a tool for managing and monitoring PostgreSQL-based databases.

The design and development of products is based on many years of accumulated experience in the operation of high-load software systems in the public and private sectors.

At the end of 2022, the company joined the Astra Group.

Tantor Postgres DBMS

The Tantor Postgres DBMS is a relational database in the PostgreSQL family with enhanced performance and stability. It is available in several editions (builds): BE (Basic Edition), SE (Special Edition), SE 1C, Certified Version 1 (certified SE and SE 1C) , and Certified Version 2 (Certified BE).

Special Edition for high-load OLTP systems and data warehouses up to 100 TB in size.

Special Edition 1C for 1C applications.

Technical support, assistance with architectural design, and migration from other vendors' DBMSs are available for all editions. Tantor Labs software is included in the "Unified Register of Russian Software for Electronic Computers and Databases."

When purchasing a Tantor Postgres DBMS, you receive a free license for the Tantor Platform for managing the purchased Tantor Postgres DBMS.

An overview of the improvements in Tantor Postgres 17.5 for 1C is given in the article https://infostart.ru/1c/articles/2432864/ 

Tantor XData

The Tantor XData hardware and software suite (HSE) delivers high-performance, high-availability, and large-scale workloads. Tantor Postgres SE consolidates diverse workloads. and SE 1C on the XData database engine in corporate data centers helps organizations improve operational efficiency, reduce administration, and lower costs.

The Tantor XData hardware and software system (HSS) is designed for migration from foreign vendors' systems and provides equivalent workload capacity. It is a replacement for high-load DBMSs with a size of up to ~50 TB per instance, serving OLTP workloads, running on foreign vendors' HSS. It is also suitable for DBMSs serving data warehouses with a size of up to ~120 TB per instance.

It's a replacement for heavy-duty 1C ERP systems when migrating from foreign DBMSs. It allows you to consolidate multiple DBMSs in a single software system. It can be used when migrating from SAP to 1C:ERP.

Designed for creating cloud platforms.

An advantage of using xData is the presence of a convenient graphical system for monitoring the operation of the DBMS: the Tantor Platform.

The second generation will be produced from 2025. PACK:

xData 2 A - on x86-64 processors based on Aquarius servers.

xData 2 Y - on x86-64 processors based on Yadro servers.

xData 2 B - on Baikal-S processors based on Elpitech servers

Since 2026, the third generation of PAK has been produced:

xData Gen3 - uses AMD processors rather than Intel; first version to include the Tantor Polar database

https://tantorlabs.ru/products/xdata-gen3/tpost/zioiy9l091-postgresql-kotorii-masshtabiruetsya-kak 

Tantor Polar

Tantor Polar is a set of instances running a single database cluster. Available on the third-generation Tantor XData (Gen3). One instance is the primary, accepting both reads and writes. The remaining instances are replicas, accepting read-only requests.

Queries can be executed by processes on multiple instances, called Elastic Parallel Query (ePQ), based on the Greenplum Open Resource Coordinator/Optimizer (GPORCA) scheduler .

To connect clients, a built-in pooler (Shared Server) is used, which uses dispatcher processes and sets (pools) of server processes.

Database cluster files are accessed by instances via the PolarFS cluster file system , which can be mounted simultaneously on multiple nodes. PolarFS accesses block devices directly, in O_DIRECT mode ( direct I/O) , without using the Linux page cache.

Disks are connected to individual storage nodes. NVMe-oF is used to transfer data from storage nodes to compute nodes running the instances, transferring data via Remote Direct Memory Access (RDMA).

Replica instances receive a stream of changes (WAL) from the master instance and apply them to the data blocks in their buffer cache. To track changes, they use a LogIndex—a reference that maps the identifier of each data block to a list of log records (LSN) that modified the block.

Tantor Polar database clusters can be geographically distributed, and transaction losses in the event of a primary cluster instance failure are avoided using the DataMax (FarSync) process, which synchronously receives a stream of log data from the primary instance of the first cluster and asynchronously transmits it over the network to the second Tantor Polar cluster.

https://habr.com/en/articles/1023046/ 

Tantor Platform

The Tantor platform is software for managing Tantor Postgres DBMS, PostgreSQL forks, and Patroni clusters. It allows for convenient management of multiple DBMSs. It belongs to the same class of software products as Oracle Enterprise Manager Cloud Control.

Benefits of using the Tantor Platform:
1. Collection of PostgreSQL instance performance indicators, storage and processing of indicators, recommendations for performance tuning
2. Intuitive and functional graphical interface allows you to focus on the performance indicators of PostgreSQL instances
3. Automates routine tasks, increasing work efficiency and reducing the likelihood of errors
4. Manages not only the Tantor Postgres DBMS, but also other DBMS of the PostgreSQL family
5. Integration with mail systems, directory services, instant messengers
6. The Tantor Platform includes Tensor software

Tantor DLH Platform

Tantor Labs is releasing the Tantor DLH Platform—software that enables data transformation and loading using Extract Transform Load (ETL) or Extract Load Transform (ELT) logic in the Tantor Postgres DBMS for data warehouses and data marts. It belongs to the same class of software products as Oracle Data Integrator.

PostgreSQL Extensions Improvements

Tantor Labs employees develop and create extensions for the PostgreSQL DBMS.

Extension repositories: https://github.com/orgs/TantorLabs/repositories 

List of extensions:

1. pg_cluster

2. pg_anon

3. pg_perfbench MIT License

4. ansible_tantor_agent MIT License

5. pg_configurator MIT License

6. pg_store_plans

7. ldap2pg PostgreSQL License

8. citus GNU Affero General Public License v3.0

9. wal-g Apache License, Version 2.0 (lzo - GPL 3.0+)

10. odyssey BSD 3-Clause "New" or "Revised" License

11. plantuner

12. pg_orchestrator MIT License

13. pgtools

14. pipelinedb Apache License 2.0

15. pg_dphyp

16. pg_cluster

17. oauth_validator

PGBootCamp Conferences

Tantor Labs is an active participant in organizing PostgreSQL community conferences as part of the global PG BootCamp initiative.

Participation in the conference is free and possible online and offline : https://pgbootcamp.ru/ 

You can become a speaker at a conference.

Conference papers are openly available: https://github.com/PGBootCamp

Performances : https://www.youtube.com/@PGBootCampRussia and https://rutube.ru/channel/32804184/ 

The PGBootCamp conference was held:

Moscow, March 19, 2026

Yekaterinburg, April 10, 2025

Kazan, September 17, 2024

Minsk, April 16, 2024

Moscow, October 5, 2023

The Tantor JAM conference is held in the fall; participation is in-person and free. Tantor Jam was held in Moscow on September 19, 2025, and September 10, 2026. Presentation materials: https://tantorlabs.ru/jam-2025 and https://tantorlabs.ru/jam-2024 

1a

Prerequisites

PostgreSQL runs on Linux, macOS, Windows, BSD, and Solaris ( https://www.postgresql.org/download/ ). On Linux, PostgreSQL can be installed from deb and rpm packages using the dpkg and rpm utilities, and from repositories using the apt and yum utilities. PostgreSQL links with libraries and may require installation or updates of these libraries. In vanilla PostgreSQL, the software is divided into packages: postgresql-18 (DBMS), depends on postgresql-common (wrapper utilities), and depends on postgresql-client-18 (client utilities and libraries). The postgresql-common package installs the wrapper utilities pg_ctlcluster (a wrapper for pg_ctl ), pg_createcluster ( initdb ), pg_backupcluster ( pg_basebackup ), and others. Wrappers are intended to simplify work with multiple clusters, but they complicate things in production use. Extensions are provided in a large number of separate packages ( postgresql-18-pg-uuidv7 , postgresql-18-repack , etc.).

Tantor Postgres is released only for Linux in deb or rpm packages. Only some modules are provided as separate packages. Fewer packages simplify installation and updates. Tantor Postgres and most forks do not use wrapper utilities and are managed by standard utilities ( pg_ctl , initdb ).

Astra Linux comes with the tantor- free -server-18 package .

Tantor Postgres is available for the following operating systems:

Linux with RedHat Packet Manager (rpm): Redos 7.3, 8; AltLinux p10, p11

MSVSphere; Oracle Linux 8; Rocky 8, 9

Linux with Debian package manager (deb): Astra Linux Special Edition 4.7, 1.7, 1.8; Ubuntu 20, 22; Debian 10, 11, 12, 13.

Distributions for other operating systems (e.g. ROSA) are released upon request.

Equipment:

Number of central processor cores: from 4;

RAM: from 4GB;

Free disk space: at least 40GB (plus space for user data to be stored). Solid-state drives (SSD/NVMe) are recommended.

https://docs.tantorlabs.ru/tdb/en/18_3/be/install-binaries.html 

Checking installation possibility

Programs use shared libraries that provide useful functionality and were used during their compilation. If these libraries aren't installed in the operating system, errors may occur during operation, the cause of which can be difficult to determine. Distributions list the libraries whose functionality utilities and processes can access. These packages are called "requires" and are considered dependencies. Dependencies can include not only packages but also the requirements of command files called during installation and other tools.

Since the list of dependencies may differ across different versions and builds of PostgreSQL, the documentation does not list the required libraries or packages.

In practice, obtaining a list of packages that need to be installed is a challenging task.

To get a complete list of dependencies for a specific distribution, you can use the following commands:

For the Debian package manager: dpkg -I tantor*.deb

For RedHat package manager: rpm -qp --requires tantor*.rpm

The utilities' response consists of a list of packages and, possibly, versions of packages and libraries. To check that dependencies are met before installation, you can use the command:

rpm -i --test tantor*.rpm

or

apt satisfy postgresql-18

Example:

The following packages have unmet dependencies:

postgresql-18 : Depends: postgresql-client-18 (= 18.3-1.pgdg11+1) but it is not going to be installed

Depends: postgresql-common (>= 275~) but 246astra6+ci1 is to be installed

Depends: libicu67 (>= 67.1-1~) but it is not installable

Depends: libldap-2.4-2 (>= 2.4.7) but it is not installable

Depends: libpq5 (>= 17~~) but 15.14-astra.se3.1 is to be installed

Depends: libssl1.1 (>= 1.1.1) but it is not installable

Depends: liburing1 (>= 0.7) but it is not installable

Recommends: postgresql-18-jit but it is not going to be installed

Installer

To simplify installation, Tantor Postgres can be installed using the installer. Download the installer using the command:

wget https:// public.tantorlabs.ru /db_installer.sh

Once the download is complete, change the file permissions so the script can run: chmod +x db_installer.sh

You can download the distribution from your personal account https://lk.astra.ru/iso-images and specify the path to the downloaded file to the installer using the --from-file parameter :

./db_installer.sh --from-file =./tantor-se-server-18_17.5.0_amd64.deb

The installer can download the distribution from the repository. To do this, set the NEXUS_URL environment variable :

su -

export NEXUS_URL="nexus-public.tantorlabs.ru"

apt update

./db_installer.sh --edition= be

apt update command updates package lists in the repositories, storing them in /var/lib/apt/lists . Downloaded packages will be cached in /var/cache/apt .

You need to update because the installer may request the installation of additional packages that are needed to install Tantor Postgres.

Possible errors:

tantor-se-client-*.deb ) was installed , but the package containing tantor-se-server-* includes the tantor-se-client-* libraries . In this case, the installer will return an error and a command to resolve it by uninstalling the package with which the conflict was detected:

E: Unmet dependencies. Try 'apt --fix-broken install' with no packages (or specify a solution).

After running apt --fix-broken install , the utility will ask for confirmation to uninstall the package.

2) The installer creates the file /etc/apt/sources.list.d/tantorlabs.list or /etc/yum.repos.d/tantorlabs.repo , so you won't need to set environment variables later. If authentication fails or you decide not to authenticate, you'll need to delete these files.

/etc/apt/sources.list.d/ or /etc/yum.repos.d directory may contain files with addresses of non-existent repositories or with parameter errors. These files should be removed.

https://docs.tantorlabs.ru/tdb/en/18_3/be/binary-download-execute.html 

Local installation

Tantor Postgres Basic Edition (BE) is available for evaluation. To install Tantor Postgres BE, you only need to set one environment variable:

export NEXUS_URL="nexus-public.tantorlabs.ru"

Update package lists from repositories:

apt update

Run the installer, specifying the desired parameters:

./db_installer.sh --edition=be --major-version=18 --do-initdb

You can specify the major version and whether to create a cluster after installation. You can also create a cluster after installation using the initdb utility .

The installer allows you to install any Tantor Postgres DBMS build from package files. This can be useful if the host doesn't have internet access.

Before you begin installation, make sure you've downloaded the correct binary package compatible with your operating system and architecture. The file should have the .deb extension for Debian-based systems and the .rpm extension for Red Hat-based systems.

To begin installation, navigate to the directory where the downloaded file is located. Make sure the db_installer.sh installation script is present and has the correct execution permissions. Local installation is performed using the following command:

./db_installer.sh --do-initdb --edition=se --major-version=18 --from-file=./tantor-se-server-*.deb

You need to specify the major version with the --major-version=18 parameter , and it must match the version (usually present in the package file name), otherwise the installer may create a directory with an incorrect version number.

You can also install the package without using the installation script, using the operating system's package manager:

rpm -i tantor*.rpm or dpkg -i tantor*.deb

In this case, the cluster won't be created and can be created later using the initdb utility . In fact, the installer can be useful during local installations because it can perform additional actions. A disadvantage is that the program code (a wrapper around the package manager) may introduce errors. For example, it may not account for all possible operating system configuration details.

Installation process

During installation:

1) the postgres user is created or modified in the operating system:

useradd -r -g postgres -c "Tantor database server" -d /var/lib/postgresql -s /bin/bash postgres
There is no need to change the postgres username to another one for security reasons.

2) the directory /opt/tantor/db/18 is created , in which the DBMS software is located.

The /usr/lib/systemd/system/tantor-se-server-18.service service descriptor file is created to start the instance serving the database cluster. A database cluster is a directory in the host's file system. For client programs to work with the DBMS (send SQL commands, receive data), a set of processes must be started on the host that will read and write to the cluster directory and maintain a connection ("socket") with the client program. This set of processes and the memory they use in the host operating system are called a PostgreSQL database cluster instance, or, for short, an instance .
The service status can be checked with the command:
systemctl status tantor-se-server-18

4) The directory /var/run/postgresql and the file /usr/lib/tmpfiles.d/tantor-db.conf are created
. The file is used by the temporary file cleaning service. The directory is the default directory for Unix socket files (configuration parameter ( unix_socket_directories ).
You can check that the directory /usr/lib/tmpfiles.d does not contain other files that may have remained from previous installations of PostgreSQL, in which the same directory was specified, but with different parameters:
systemctl status systemd-tmpfiles-* | grep Duplicate

5) a directory for cluster files is created /var/lib/postgresql/tantor-se-18/data

6) the lines export PATH=/opt/tantor/db/18/bin:$PATH are added to the end of the file
/var/lib/postgresql/.bash_profile

Note: You can verify that
the LD_PRELOAD environment variable does not contain any libraries that could override the PostgreSQL libraries, as LD_PRELOAD takes precedence. Library paths are also specified in files in the /etc/ld.so.conf.d/ directory.

After installation

PostgreSQL has no limit on the number of instances running on a single host. However, production database servers are typically heavily loaded and don't run multiple instances on a single node. Multiple instances can be run on a single node temporarily, for example, during a migration to a new version.

Vanilla PostgreSQL includes the pg_controlcluster and pg_createcluster utilities , which are wrappers for the standard pg_ctl and initdb utilities . This is intended to simplify working with multiple clusters on a single node. Tantor Postgres does not use these utilities.

After installation you can:

1) add the path to the cluster directory to the postgres user profile file ( /var/lib/postgresql/.bash_profile ):
export PGDATA=/var/lib/postgresql/tantor-se-18/data
This will simplify the launch of cluster management utilities; when calling the utilities, you will not need to specify the parameter ( -D or --pgdata ) that specifies the path to the cluster directory.

2) create a cluster if it has not been created yet

3) start the cluster with the command: systemctl start tantor-se-server-18

4) If automatic instance startup was disabled (enabled by default), then enable: systemctl enable tantor-se-server-18

5) set the initial values of the cluster configuration parameters using the configurator https://tantorlabs.ru/pgconfigurator 

6) Tantor Postgres can be managed by the Tantor Platform without purchasing additional licenses . Managing other vendors' DBMSs and vanilla PostgreSQL on the Tantor Platform requires a license, the cost of which depends on the number of processor cores.

Installing add-ons

Add-ons, which include executable files, are supplied in separate RPM and DEB packages. Available add-ons can be found in the repository: nexus-public.tantorlabs.ru

db_extension_installer.sh installer script , which installs packages required by the extension from repositories registered in Linux. The script can download the extension package from the repository or use an already downloaded file. To run the script, set the environment variable:

root@tantor:~# export NEXUS_URL= nexus-public.tantorlabs.ru

and run the script, specifying the parameters. The values for the parameters can be found by browsing the repository contents in a browser or from the documentation .

For example, there is a package in the repository:

pg-configurator - tantor - all _26.1.21-1astra1.8-1_all.deb

The parameters will have the following values:

root@tantor:~# ./db_extension_installer.sh --database-type= tantor --database-major-version=18 --edition= all --extension= pg-configurator

Examples of add-ons supplied in packages: wal-g pgbouncer python3 pg-timetable pg-configurator pg-anon patroni ldap2pg keepalived haproxy etcd ansible mysql-fdw oracle-fdw tds-fdw (SQL Server) pg-probackup pg-trace .

Tantor add-on executable files are usually installed in the directory:

/opt/tantor/usr/bin , which can be included in the PATH environment variable .

After installing the add-on, you can run it. Example:

postgres@tantor:~# /opt/tantor/usr/bin/pg_configurator

...
autovacuum_analyze_scale_factor = 0.05
autovacuum_analyze_threshold = 530

...

An example of the add-on installation description in the documentation : https://docs.tantorlabs.ru/tdb/en/18_3/be/pg_timetable.html ).

Configurators

The database cluster is created using the initdb command-line utility . This utility creates a postgresql.conf file with default values. These values are designed to support a lightly loaded application, so the DBMS can be used on a desktop for small tasks. It is assumed that production-specific settings will be configured separately.

For initial configuration, you can use the pg_configurator utility , created and maintained by Tantor Labs. The utility is available at https://tantorlabs.ru/pgconfigurator/ , and the command-line shell is available at https://github.com/TantorLabs/pg_configurator 

The utility accepts 7 or ~20 parameters and makes recommendations based on them.

Analogues:

1. PGconfigurator www.cybertec-postgresql.com, web version pgconfigurator.cybertec.at makes recommendations based on 13 parameters

2. PGСonfig https://github.com/pgconfig/api , the web version www.pgconfig.org makes recommendations based on 8 parameters

3. PGTune https://github.com/le0pard/pgtune , created by a 2ndQuadrant employee, the web version pgtune.leopard.in.ua gives recommendations based on 7 parameters

During DBMS operation, the Tantor Platform Configurator can recommend configuration parameters. The Platform Configurator makes recommendations based on approximately 25 parameters.

Guide to setting up PostgreSQL with 1C products:

https://wiki.astralinux.ru/tandocs/nastrojka-postgresql-tantor-dlya-raboty-1s-294394904.html 

Creating a cluster using the initdb utility

initdb command line utility , which is run under the postgres user.

Before running the utility, you need to create a directory where the files of the created PGDATA cluster will be located, set permissions and ownership rights for this directory and the directories in which it is located for the postgres user.

When an instance is started, checks are performed on the PGDATA directory (subdirectories are not checked):

1) the owner must be the postgres user

2) permissions must be 0700 (drwx --- ---) or 0 750 (drwx rx ---)

 ( zero means the number is octal). The -g or --allow-group-access option can be used to set less restrictive permissions on

the postgres , template0 , template1 databases created during cluster creation , but can be selected when creating other databases:

1) --lc-collate (if not set, it is taken from the LC_COLLATE environment variable ) - character order, affects the comparison and sorting of text

2) --lc-ctype ( LC_CTYPE ) - character classification (uppercase letters, lowercase letters, digit symbols, and other character classes), affects the upper(), lower(), isalpha() functions

3) --encoding (the value after the period in the LOCALE variable ) – character encoding scheme. Should be set to UTF8.

Example: initdb -g --locale-provider= libc --encoding=UTF8 --locale=en_US.UTF8 --lc-collate=en_US.UTF8 --lc-ctype=en_US.UTF8

If you don't specify any parameters, environment variables are used. You can get a list of environment variables using the locale command .

locale -a combinations .

Configure with the dpkg-reconfigure locales command .

In version 16, the utility now has the -c (or --set ) parameter, which can be used to add configuration parameter values to the end of the created postgresql.conf configuration file :

initdb -c cluster_name='replica' --set port=5433 -D .

Localization providers

The localization provider (the library that provides the functions) is selected by the initdb --locale-provider={builtin| libc | icu } parameter or LOCALE_PROVIDER of the create database command .

libc is the default provider, icu appeared in version 10, builtin appeared in version 17.

builtin provider is independent of operating system libraries and uses only PostgreSQL core code. Its drawback is that it only supports three locales: C (identical to the C locale in libc ), C.UTF-8 (used only if the database encoding is UTF8), and PG_UNICODE_FAST . In all of them, the letter Ё comes before Cyrillic letters and the letter ё after (as in the C , POISIX , and C.utf8 locales of the libc provider ).

Replicas should run on the same Linux versions with the same locale sets installed in the operating systems. For example, when upgrading from RHEL7 to RHEL8, the sort order of special characters ( $ ) changed. _ ) in the libc library to comply with the Unicode 9 standard.

For the icu provider, behavior is independent of the operating system and database encoding. For libc, the same locale name may (for some locales) have different behavior on different operating systems. However, this difference is not decisive for choosing a provider, since the behavior of libc and icu depends on their versions .

When using the libc and icu providers , changing the operating system version, which changes library versions , requires issuing the REFRESH COLLATION command and rebuilding the indexes. Index creation can take a long time, leading to downtime. Using the builtin provider eliminates this need. The builtin provider is also slightly faster than libc and icu , but the order of the " ё " and " Ё " characters makes the builtin provider unsuitable for Cyrillic .

https://docs.tantorlabs.ru/tdb/en/18_3/be/locale.html#LOCALE-PROVIDERS 

Selecting localization parameters

For cluster and database creation, it's better to choose UTF8 encoding over single-byte encodings ( ENCODING='WIN1251' ). Although Cyrillic characters in UTF8 occupy two bytes, this difference is mitigated by compression. By default, compression is used for the text type when toasting (EXTENDED strategy). When storing text, even if Cyrillic and Latin characters are intended, the text may contain Unicode characters and characters with diacritics.

For the ICU provider , when selecting ICU_LOCALE='ru-RU', Cyrillic comes before Latin.

ICU_LOCALE are specified as BCP 47 language tags ( https://www.rfc-editor.org/info/bcp47 ), if you specify a libc -style locale , it will be converted to language tags.

After creating the database, it is worth checking with the psql \l command what localization parameters the database was actually created with.

create database lab01builtin LOCALE_PROVIDER= icu ICU_LOCALE=' Non-Existent ' TEMPLATE=template0;

NOTICE: using standard form " non-existent " for ICU locale " Non-Existent "

WARNING: ICU locale "non-existent" has unknown language " non "

HINT: To disable ICU locale validation, set the parameter " icu_validation_level " to "disabled".

\l x lab01builtin

List of databases

-[ RECORD 1 ]-----+-------------

Name | lab01builtin

Owner | postgres

Encoding | UTF-8

Locale Provider | icu

Collate | en_US.UTF-8

Ctype | en_US.UTF-8

Locale | non-existent

ICU Rules |

Access privileges |

https://docs.tantorlabs.ru/tdb/en/18_3/se/sql-createdatabase.html#CREATE-DATABASE-LOCALE 

1b

pg_ctl instance management utility

To enable the newly created database cluster to serve queries, an instance must be started. An instance is started and stopped using the pg_ctl command-line utility .

ctl is an abbreviation for control.

The advantage of the utility is its simplicity.

Commands that the utility can execute:

start - launching an instance

stop -m smart | fast | immediate - stopping the instance

If the buffer cache is large, it's a good idea to perform a checkpoint before terminating the instance, that is, issue the checkpoint command. This will reduce downtime, which includes the time it takes to terminate the instance. Terminating an instance terminates sessions, begins downtime, and performs a checkpoint. If a checkpoint has already been performed before this "final" checkpoint, the number of dirty blocks in the buffer cache will be small, and the final checkpoint will be faster.

restart - restart, equivalent to stopping and starting, so you can specify the stop mode with the -m (or --mode= ) parameter and other parameters, new environment variables ( PGDATA ) will be applied at startup.

reload - rereads configuration files without stopping the instance

status - displays the instance status

promote - complete the replica recovery and convert it to a master

Also, the utility has the commands initdb , logrotate , kill , but they are rarely used.

Relative paths and pg_ctl

To launch an instance, you need to specify the cluster directory - PGDATA . This can be done by setting an environment variable or by specifying it in the parameter pg_ctl -D path to the cluster directory.

If relative paths were used in environment variables or parameters during startup, they will be counted from the directory from which the restart command is run, which can lead to errors:

postgres@tantor:~/tantor-se-18/data$ export PGDATA=" . "

postgres@tantor:~/tantor-se-18/data$ pg_ctl start

server started

postgres@tantor:~/tantor-se-18/data$ cd ..

postgres@tantor:~/tantor-se-18$ pg_ctl restart

pg_ctl: directory " . " is not a database cluster directory

postgres@tantor:~/tantor-se-18$ pg_ctl status

pg_ctl: directory " . " is not a database cluster directory

postgres@tantor:~/tantor-se-18$ cd data

postgres@tantor:~/tantor-se-18/data$ pg_ctl status

pg_ctl: server is running (PID: 20290)

/opt/tantor/db/18/bin/postgres

postgres@tantor:~/tantor-se-18/data$ pg_ctl restart

waiting for server to shut down.... done

server stopped

waiting for server to start.... done

server started

The postgres process

pg_ctl starts the postgres process , which forks the other processes in the instance and listens for incoming connections. The postgres process has parameters that pg_ctl can pass to it . In older versions of PostgreSQL, the postgres process was called postmaster.

To pass configuration parameters from pg_ctl to postgres, the -o option is used . For example,

pg_ctl start -o "-- config_file=./postgresql.conf -- work_mem=8MB "

You can also use the syntax

pg_ctl start -o "-c config_file=./postgresql.conf -c work_mem=8MB "

See the list of parameters that can be passed to postgres:

postgres --help

The --single option starts the postgres process in single-user, single-process mode:

postgres --single

PostgreSQL stand-alone backend 18.3

backend> vacuum full

To exit single mode, use the key combination <ctrl+d> .

This is not a psql utility prompt; there are no psql commands in this mode, only commands that the server process (synonymous with backend) can accept.

The --single parameter cannot be passed via pg_ctl , since there is no interprocess communication.

This mode eliminates interprocess communication and memory locks. This allows commands to execute faster. This mode is used in rare cases for commands that repair cluster contents, such as vacuum full .

Managing an instance via systemctl

Linux uses systemd to launch services. The distribution ships with a service description file , /usr/lib/systemd/system/tantor-se-server-18.service , and the administrator does not need to create it. By default, Type=forking is used .

By default, the timeout is set to 5 minutes by the TimeoutSec=300 parameter in this file.

systemd Forcefully terminate the instance if it doesn't start within this time . On production servers, recovering from a crash using logs can take a significant amount of time.

It is worth using the value infinity , which disables the timeout .

While the server is running, its PID is stored in the first line of the PGDATA/postmaster.pid file . This file is used to prevent multiple instances from running in the same directory and can be used to obtain the process PID.

If the instance processes are terminated and the postmaster.pid file prevents the instance from starting, the postmaster.pid file can be deleted .

systemctl is the main command for working with systemd . By default, it runs with root user privileges .

Launching an instance:

systemctl start tantor-se-server-18.service

The suffix " .service " can be omitted, as it is used by default.

You can check whether the instance has been added to startup using the command
systemctl is-enabled tantor-se-server-18

systemctl and pg_ctl

While the server is running, its PID is stored in the first line of the PGDATA/postmaster.pid file . This file is used to prevent multiple instances from running in the same directory and can be used to obtain the process PID.

If the instance processes are terminated and the postmaster.pid file prevents the instance from starting, the postmaster.pid file can be deleted .

If you get the following error when starting an instance using the systemctl utility:

Starting Tantor Special Edition database server 18...

pg_ctl: another server might be running; trying to start server anyway

lock file "postmaster.pid" already exists

HINT: Is another postmaster running in data directory "/var/lib/postgresql/tantor-se-18/data"?

pg_ctl: could not start server

This may mean that the instance is not started by systemd but by the pg_ctl utility and systemd cannot start or stop the instance because it was started by the pg_ctl utility .

You can check the list of processes in the operating system.

pg_ctl utility for starting/stopping and other actions .

, the systemctl stop tantor-se-server-18 command cannot stop the instance, it does not produce a result, and it may create the false impression that the instance is terminated.

pg_ctl utility option -s (or --silent ) does not print informational messages, only errors.

-w (or --wait ) -t (or timeout= ) does not return a prompt, waiting for the command to complete for a maximum of the value set by the PGCTLTIMEOUT environment variable or, if the variable is not set, 60 seconds .

The parent systemd process has PID=1 :

postgres@tantor:~$ ps -ef | grep init

root 1 0 0 /sbin/init splash

Working in a Docker container

The postmaster process ID (PID) in the container must not be equal to one (1). The process with PID 1 is the first user process that starts after the Linux kernel initializes. Process 1 spawns (starts) all other processes. It is the parent of all other processes it spawns. All processes must have a parent. Process 1 has the following property: if the parent of any process dies, the kernel automatically assigns process 1 as the parent of the orphaned process . Process 1 must adopt all orphans.

The postgres process monitors the state of its child processes and receives an exit status when any child process terminates. The postmaster's default behavior if a child process terminates with a status other than 0 (normal termination) is to restart the instance. In addition to session termination, the instance will be unavailable while recovery is performed using the log.

In a Docker container, process 1 is the process for which the container is created. The postgres process should not have PID= 1 :

root@tantor:~# docker exec -it container /usr/bin/ps -ef

PID USER TIME COMMAND

  1 postgres 0:38 postgres

To use initd (tini) to start an instance in a container, you need to use the --init option .

Mutable files, particularly PGDATA, must reside on volumes ; otherwise, data will be lost when the container is deleted. Example of creating and running a container:

sudo docker pull postgres

sudo docker run -d --init -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres -e POSTGRES_INITDB_ARGS="--data-checksums" -e POSTGRES_HOST_AUTH_METHOD=trust -p 5434:5434 -e PGDATA=/var/lib/postgresql/data -d -v /root/data :/var/lib/postgresql/data --name postgres postgres

Running an instance in a container does not add high availability.

Running an instance in a container provides slightly better performance than running it in a virtual machine.

Three instance stop modes

The instance can be stopped using the pg_ctl stop command .

Command syntax:

pg_ctl stop [-D $PGDATA ][-ms[mart]|f[ast]|i[mmediate]][-W][-t seconds ][-s]

There are three modes to choose from:

Smart mode prevents new connections and waits for existing sessions to voluntarily disconnect. This can take hours, preventing new connections, resulting in downtime. In Oracle Database, this mode is called "normal shutdown." Unlike Oracle Database, after signaling a shutdown in Smart mode, you can signal a shutdown in Fast mode. If you've started Smart mode, you can shut down the instance in Fast mode.

fast - new connections are denied, and all server processes are signaled to abort transactions and exit (the Linux signal SIGTERM 15 ). The remaining background processes of the instance are then terminated in the correct order. One of the last actions is a checkpoint. In Oracle Database, this mode is called "shutdown immediate." Unlike Oracle Database, transaction rollbacks in PostgreSQL are performed immediately, so the shutdown delay is primarily determined by the duration of the checkpoint.

fast - the default stop mode for stopping via pg_ctl and via systmemctl

On clusters with a large amount of memory used by an instance, you can minimize instance shutdown time, or downtime. To do this, initiate a checkpoint before stopping the instance with the checkpoint command . After the checkpoint completes , send a signal to stop the instance. In this case, the checkpoint (the final checkpoint) that will be executed anyway when the instance is stopped (in smart or fast mode) will have to write less data to disk, and the final checkpoint will complete faster.

fast modes, all changed data in memory (that needs to be saved, i.e., "protected by the write-ahead log") is written to files at the checkpoint, and information about the successful shutdown of the instance is written to the pg_control control file . This is called a "graceful shutdown." When the instance is subsequently started, the pg_control control file determines that the instance was shut down gracefully and no WAL reading is required.

Stopping an instance

Immediate shutdown mode. The parent postmaster process will send an immediate stop signal (QUIT 3 ) to other processes and wait for them to terminate. If any process does not terminate within 5 seconds, it will be sent a KILL signal (9), after which the postmaster process itself will be terminated. Stopping in this mode will require a rollback of WAL files. The next time the instance is started. Immediate mode should be used in extreme cases, such as when the instance hangs (no disk activity, no progress) while stopping in fast mode . The equivalent of stopping in immediate mode is the QUIT signal . The KILL (9) signal should not be sent to the postgres process, as the shared memory and semaphores will not be released until the operating system is rebooted or until they are manually released with the ipcrm command. Shared memory segments and semaphores can be viewed using the ipcs operating system command. Avoid sending the KILL (9) signal to other processes in the instance, including server processes (as is common with Oracle Database), as this will result in an immediate shutdown or restart of the instance. In Oracle Database, the equivalent of immediate mode is called "shutdown abort."

Using pg_ctl stop is the most convenient way to shut down an instance, but you can send a signal to the postgres process directly:

kill -INT $(head -1 $PGDATA/postmaster.pid)

kill -INT ` head -1 $PGDATA/postmaster.pid ` #in this command, the quotes are backticks

To detach sessions and interrupt a running command (in someone else's session without interrupting it), it is convenient to use the functions pg_terminate_backend (send SIGTERM 15 to the server process) and pg_cancel_backend (send SIGINT 2 ).

Before performing procedures that require a proper shutdown, you should ensure that:
1) all processes of the stopped instance have been unloaded from memory (are not present in the operating system)

In older versions of PostgreSQL, there were bugs where background processes (worker processes, including autovacuum) continued to run after the postmaster process was stopped due to the fact that they remained in critical sections of the program code for a long time.

2) the status of the correct cluster shutdown was written to the control cluster:

pg_controldata | grep state

Database cluster state: shut down

https://docs.tantorlabs.ru/tdb/en/18_3/se/server-shutdown.html 

Instance Stop Messages

In the cluster diagnostic log, when performing checkpoints (parameter log_checkpoints=on ), there will be messages like:

LOG: checkpoint starting: shutdown immediate

LOG: checkpoint complete: wrote 0 buffers (0.0%), wrote 3 SLRU buffers; 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.001 s, total=0.008 s; sync files=2, longest=0.001 s, average=0.001 s; distance=0 kB, estimate=0 kB; lsn=0/3D8DB68, redo lsn=0/3D8DB68

PostgreSQL does not have a shutdown immediate command . The text " shutdown immediate " in the log refers to checkpoint properties, not the instance shutdown mode. When shutting down an instance in immediate mode

pg_ctl stop -m immediate command ), the final checkpoint is not performed .

Text in checkpoint messages (after LOG: checkpoint starting: ) means:

shutdown - a checkpoint is caused by stopping the instance

immediate - execute the checkpoint at maximum speed, ignoring the value of the checkpoint_completion_target parameter

force : perform a checkpoint even if nothing has been written to the WAL since the previous checkpoint (there was no activity in the cluster), this happens if the instance is shut down or at the end of recovery

wait : Wait for the checkpoint to complete before returning control to the process that called the checkpoint (without wait , the process will run the checkpoint and continue running).

end-of-recovery : checkpoint at the end of log rolling (cluster recovery by startup process)

w al : checkpoint caused by log files reaching half the size specified by max_wal_size ('by size', 'on demand')

time : the checkpoint was triggered by reaching the checkpoint_timeout parameter value ("by time")

Management utilities (SQL command wrappers)

/opt/tantor/db/18/bin directory (the path to which is added to the PATH environment variable for the postgres user during installation) contains utilities for working with the database cluster. We've already covered the initdb utility. Next, we'll look at the main utility&mdash; the psql terminal client , which allows you to run SQL commands.

cluster management operations can be performed using command-line utilities. Wrappers exist for some SQL commands. In command-line scripts, it's convenient to use wrappers instead of writing the command invocation via psql:

psql -c "command"

There is no difference in the result between using shell utilities and SQL commands.

Wrapper utilities:

clusterdb - a wrapper for the SQL CLUSTER command

createdb is a shell for the CREATE DATABASE command. There's no difference between creating a database with this utility or with the command.

createuser - a wrapper for the CREATE ROLE command

dropdb - a wrapper for the DROP DATABASE command

dropuser - a wrapper for the SQL DROP ROLE command

reindexdb is a wrapper for the REINDEX SQL command . The -j parameter allows you to specify the number of commands to execute in parallel.

vacuum db is a shell for the VACUUM command.

vacuum lo has nothing to do with vacuuming (VACUUM) . vacuumlo is a convenient, periodic utility for removing (purging) orphaned large objects from cluster databases. There are various ways to automate the removal of orphaned large objects (for example, using triggers), and this utility is one such method . A better way is to use the "lo" extension, which contains the lo_manage() function for use in triggers that prevent orphaned large objects.

-e utility parameter outputs commands that utilities generate and send for execution.

Description of utilities:

https://docs.tantorlabs.ru/tdb/en/18_3/se/reference-client.html 

Backup utilities

pg_archivecleanup is used in the archive_cleanup_command parameter value to remove unnecessary WAL files on the physical replica (standby cluster) .

pg_basebackup is a utility for creating cluster backups for clones, replicas, and storage. It can retrieve files over the network using the replication protocol .

pg_combinebackup - ( version 17 ) combines incremental backups with full backups.

pg_createsubscriber - ( version 17 ) quickly creates a clone from a physical replica with seamless logical replication. Reduces the data copying phase when creating subscriptions. Used when upgrading to a new version to minimize downtime.

pg_dump - creates a logical copy of database objects.

pg_dumpall &ndash; creates a logical copy of the entire cluster or shared cluster objects in the form of a text script for creating databases and objects within them . The -g parameter is of interest , allowing you to dump shared cluster objects.

pgcopydb is a Tantor Postgres utility for automating logical data transfers between databases with maximum speed. The utility uses pg_dump, pg_restore , and logical backup techniques.

pg_receivewal - used to pull WAL (streaming archive) file contents via the replication protocol. It is also used to organize WAL log storage on hosts that store backups .

pg_recvlogical - for logical replication, rarely used.

pg_resetwal clears the WAL log. It's used with the --wal-segsize parameter to change the size of WAL segments if you want to change their size after cluster creation. This is done either because there are a large number of files in the pg_wal directory or because the maximum size of the shared memory log buffer ( wal_buffers ) is limited by the WAL file size. The impact of WAL buffer size on performance is nonlinear.

pg_restore is a utility for restoring from logical backups created by the pg_dump utility in some modes (in other modes, psql is used for restoration)

pg_waldump - displays the contents of WAL segments, used for debugging complex recovery cases

pg_walsummary - ( version 17 ) shows the contents of the WAL summary file.

https://docs.tantorlabs.ru/tdb/en/18_3/se/reference-server.html 

Management utilities (other)

pg_amcheck - refers to the standard PostgreSQL extension amcheck , which has a set of functions for checking for corruption in objects that physically store data, called relations. Relationships (synonymous with "class") include tables, indexes, sequences, views, foreign tables, materialized views, and composite types. If amcheck reports corruption, it means it actually exists; false positives are excluded.

pg_checksums - enables/disables the calculation of checksums for data blocks and verification of cluster data blocks. In Oracle Database, the equivalent is the dbv (dbverify) utility.

pg_rewind - for synchronizing clusters, usually to restore the former master (primary cluster) after a failover to a physical replica (standby cluster), as well as in upgrade procedures (transition to a new primary version);

pg_upgrade - used when upgrading to a new major version of PostgreSQL, as well as when migrating from vanilla PostgreSQL to Tantor Postgres;

pg_test_fsync - used when setting parameters for writing to the WAL log;

pg_test_timing - measures the speed and stability of timestamp acquisition; in version 19, the timing_clock_source ( auto | system | tsc ) configuration parameter was added , allowing you to select a time source. If tsc is available, it is selected.

Useful utilities

pg_config - information about the installation and assembly parameters of the DBMS;

pg_controldata - displays the contents of the cluster control file $PGDATA/global/pg_control in text form ;

pgbench is the standard PostgreSQL utility for load testing;

https://docs.tantorlabs.ru/tdb/en/18_3/se/reference-client.html 

Management Utilities (continued)

pg_isready checks that the cluster is accepting connections, similar to psql -c "\q" . While this utility is more convenient for obtaining results, psql allows you to specify additional commands to check the availability of objects from the perspective of a specific client application.

oid2name is a convenient utility for finding the object to which a file belongs in a cluster directory (PGDATA) and tablespaces, as well as other information about the membership of files and directories to cluster objects. Similar operations can be performed using SQL commands and SQL functions, but this is much more complex.

postgresql-check-db-dir - script for a superficial check of the PGDATA directory structure, called by systemd before calling pg_ctl to start an instance, to ensure that the PGDATA directory contains something resembling a cluster directory.

pgcompacttable is a utility for reducing the size of table files.

pg_repack is an extension that allows you to reorganize files that store data without locking the entire object. It's similar to the VACUUM FULL command , but without exclusive locking. PostgreSQL version 19 introduced the REPACK [VERBOSE, ANALYZE, CONCURRENTLY] table command . With the CONCURRENTLY parameter, the command works similarly to pg_repack : it uses space during the command execution, and an exclusive lock is acquired at the end of the command execution.

Discussed earlier in this chapter:

pg_ctl - Manages a cluster instance

initdb - creates a cluster

https://docs.tantorlabs.ru/tdb/en/18_3/se/reference-server.html 

1c

psql terminal client

PostgreSQL has a standard terminal client (command line utility) psql .

This course doesn't aim to monotonously describe all of psql's capabilities; there are many. psql's functionality is broader than that of similar utilities in other DBMSs. The following slides cover features encountered in everyday work . Additional practical examples are provided for this chapter.

psql allows you to interactively enter commands, send them to the server process, and view the results of command execution. You can also pass commands to psql non-interactively&mdash;commands can be taken from a file or a command-line parameter.

psql -f script.sql

psql -c "CREATE SCHEMA sh; CREATE TABLE sh.t (n numeric);"

psql has configuration files. The global configuration file is located in the directory pointed to by the output of the pg_config --sysconfdir utility.

For Tantor Postgres, this is the file /opt/tantor/db/18/etc/postgresql/psqlrc

The local file for the operating system user is located in his home directory, the default value is ~/.psqlrc The location of the local file can be overridden by the PGCONFIG environment variable .

By default, the files are not created, but you can create them. In Oracle Database, the glogin.sql file is used for sqlplus.

~/.psqlrc and psqlrc files can be made version-specific by appending a hyphen and the major or minor PostgreSQL version identifier to the file name. For example, ~/.psqlrc-18 or ~/.psqlrc-17.5 . Both files apply, but the more specific file takes precedence.

Using these files you can make working in psql more convenient.

https://docs.tantorlabs.ru/tdb/en/18_3/se/app-psql.html#psql 

psql: connecting to a database

psql connects to a specific database in the cluster. Connecting to the database requires authentication, which is typically configured separately for local connections via Unix sockets, network connections from the same host to localhost (127.0.0.1), and connections from other hosts. PostgreSQL supports a variety of authentication methods, which will be discussed in subsequent chapters of the course. Authentication is possible without a password, but the session must be associated with a cluster user. Connecting without associating a user previously created in the cluster is only possible in single-user mode. In single-user mode, the connection is made under a user who is implicitly granted superuser privileges.

Role and user are synonyms and are identical concepts. The CREATE ROLE and CREATE USER commands produce the same result, except that the CREATE ROLE command sets the NOLOGIN attribute by default , while the CREATE USER command sets LOGIN by default .

After presenting the role name, the server process checks the privileges: whether the role can create a session (has the LOGIN attribute ) with a specific database. The SUPERUSER attribute does not include the right to create a session; users with both the SUPERUSER and NOLOGIN attributes can exist simultaneously.

Connecting to multiple databases in a single session, even from the same cluster, is not possible. Databases are isolated from each other in terms of security and privileges. To work with tables in different databases simultaneously, you can use the postgres_fdw (Foreign Data Wrapper) or dblink extensions . To copy data between databases, you can use a streaming data transfer (" pipe ") and the pg_dump utility .

Connecting to a database

psql command line parameters that can be used to specify which database and user to connect to:

-U name or --username= name - default value: the operating system user name under which psql is running

-d dbname or --dbname= dbname - default value: user name specified by -U parameter

-h host or --host= host - default value: /var/run/postgresql (on the instance side, this same value is set during assembly and is displayed in the unix_socket_directories parameter ) .

When connecting, you can use shortened syntax:

psql database_name username .

For example psql postgres postgres

If psql or other utilities return an error:

Is the server running locally and accepting

connections on Unix domain socket " /tmp/ .s.PGSQL.5432 "?

then the old version of the utility is launched (for example, from the path /usr/bin/psql ).

In addition to passing the -h parameter , you can specify the Unix socket directory in the PGHOST environment variable , for example, export PGHOST= /var/run/postgresql

-p port or --port=port - default value: 5432

For local connections via a Unix socket, a port is also used.

The postgres process creates a file whose suffix is the port number. For example , /run/postgresql/.s.PGSQL.5432

Client-side load balancing in the libpq network library was introduced in version 16:

psql --host=tantor,localhost load_balance_hosts=random --port=5432,5432

psql "host=tantor,localhost load_balance_hosts=random port=5432,5432"

https://docs.tantorlabs.ru/tdb/en/18_3/se/libpq-connect.html#LIBPQ-CONNECT-LOAD-BALANCE-HOSTS 

Connection parameters

A useful psql command for displaying connection details is \conninfo

Up to version 18:

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

In version 18 :

Connection Information

Parameter | Value

----------------------+---------------------

Database | postgres

Client User | postgres

Socket Directory | /var/run/postgresql

Server Port | 5432

Options |

Protocol Version | 3.0

Password Used | False

GSSAPI Authenticated | false

Backend PID | 3891

SSL Connection | false

Superuser | on

Hot Standby | Off

(12 rows)

The username under which the connection was created (authentication was completed) is returned. The SET ROLE and SET SESSION AUTHORIZATION commands do not change the result of \conninfo

To reconnect in psql, use the command

\c database_name username host port

If you don't want to specify certain parameters and want to use the current connection's values, use a dash instead of the parameter in its position. The trailing dash is optional. For example:

\c - user1

You are now connected to database "postgres" as user " user1 ".

\c - - localhost

You are now connected to database "postgres" as user " user1 " on host " localhost " (address "127.0.0.1") at port "5432".

If a new connection cannot be established, the existing connection is maintained.

Getting help with psql commands

After installation, PostgreSQL can be run on the psql server without parameters, and then psql will connect locally (via a Unix socket) to the postgres database under the postgres user.

psql commands begin with a backslash: " \ "

psql --help command line options

psql command help \?

SQL Command List \h
After \h you can enter the initial words of a command and get help for that command.

To see what SQL commands psql generates, to execute commands starting with \d (describe - get a description of the object), you need to set the parameter:

postgres=# \set ECHO_HIDDEN on

postgres=# \db

/******** QUERY *********/

SELECT spcname AS "Name",

pg_catalog.pg_get_userbyid(spcowner) AS "Owner",

pg_catalog.pg_tablespace_location(oid) AS "Location"

FROM pg_catalog.pg_tablespace

ORDER BY 1;

/***************************/

List of tablespaces

Name | Owner | Location

------------+----------+----------

pg_default | postgres |

pg_global | postgres |

(2 rows)

Command history and paged output

If the text does not fit on the screen, the "pager" functionality is used: you will see a colon at the end of the command output.

Pressing the <ENTER> key will display another line.

If you want to display the next page, then after the colon you need to press the "z" key

To return to the previous page, press the "b" (back) key.

If you want to interrupt the output, you can press "q" (quit).

If you want to get help and find out what other keyboard shortcuts there are, you can type the letter "h" (help) after the colon.

You can disable pagination with the command \pset pager off

Pagination is implemented by passing the output result to the operating system utility less or more .

The command history is, by default, accessible by pressing the up/down arrows on your keyboard.

psql stores the history of commands typed interactively in the file ~/.psql_history .

The location of this file is specified by the HISTFILE or PSQL_HISTORY environment variables .

Next to ~/.psql_history There is a file ~/.bash_history with the history of the operating system terminal commands.

File names that begin with a period are considered "hidden", meaning that the ls command without the -a parameter does not show such files.

psql works best with servers running its own version. When connecting to a newer or significantly older version of PostgreSQL, psql commands (those beginning with a backslash) may fail .

Formatting output in psql

You can view the current formatting settings by typing the command \pset

If you need to repeat a command at intervals, you can use the command :

\watch seconds count=number min_rows=rows , exit ctrl +c or automatically upon reaching count or if the query returns fewer rows than specified in the optional min_rows parameter ;

\a Toggle vertical column alignment;

\t Enable/Disable Display of Header and Footer

\x enable/disable verbose output (line by line)

When running long queries and comparing execution speed, it is convenient to enable display of execution time:

postgres@tantor:~$ psql -c "select 'abc' name" -x

Pager usage is off.

-[ RECORD 1 ]

name | abc

postgres@tantor:~$ psql -q

postgres=# \x on \\ select 'abc' name; \x off

-[ RECORD 1 ]
name | abc

psql -q option suppresses informational messages ; psql will only display query results. Example of informational messages:

postgres@tantor:~$ psql

Pager usage is off.

psql (18.3)

Type "help" for help.

postgres=# \x off \\

select 'abc' name;

\x on

Expanded display is off.

name

------

abc

Expanded display is on.

Outputting the query result in HTML format

If the number of columns is large and a terminal client with a proportional font is inconvenient for display, psql can generate the output in HTML format instead of text. This can be accomplished using the -html or -H parameter or \pset format html.

An example of a command that sends an SQL command for execution and launches a browser with the result in HTML format:

psql -c " command; " -H -o f.html | xdg-open f.html

In one line you can get the result of large samples in a readable format.

This handy command can be more convenient and faster to execute than using graphical utilities like pgAdmin, and also in cases where graphical utilities are not installed on the operating system.

psql command prompt

It happens that the administrator issued a command in the wrong window.
Changing the psql prompt can help reduce the likelihood of this happening.

The command prompt has default values that distinguish between the first line typed in a command and subsequent ones.

By default, PROMPT2 differs from PROMPT1 by invisible characters: = And - . It's worth paying attention to them.

PROMPT1, PROMPT2 and PROMPT3 define the appearance of the invitation.

PROMPT1 is issued when psql is waiting for a new command.

PROMPT2 if there is a string in the buffer, for example because the command was not terminated by a semicolon or the quotes were not closed.

A typical question is: what is the third prompt responsible for?

PROMPT3 is issued when executing the COPY command name FROM stdin , when data is entered into the terminal to be inserted into a table. This mode is terminated by \. <ENTER>

This mode is rarely used, so the third prompt is not changed and people forget what it is responsible for.

postgres=# copy t from stdin;

Enter data to be copied followed by a newline.

End with a backslash and a period on a line by itself, or an EOF signal.

>> \.

COPY 0

It is convenient to change these prompts in the ~\.psqlrc file. to see which cluster database you are connected to.

Example of installing a color prompt:

\set PROMPT1 '%[%033[0;31m%] %n %[%033[0m%] @ %[%033[0;36m%] %/ %[%033[0m%] %[%033[0;33m%]%[%033[5m%] %x %[%033[0m%] %[%033[0m%] %R%# '

\set PROMPT2 '%[%033[0;31m%] %n %[%033[0m%] @ %[%033[0;36m%] %/ %[%033[0m%] %[%033[0;33m%]%[%033[5m%] %x %[%033[0m%] %[%033[0m%] %R%# '

user1 @ db01 =>

Environment variables that psql responds to

In the "Environment" section of the documentation
: https://docs.tantorlabs.ru/tdb/en/18_3/se/app-psql.html#APP-PSQL-ENVIRONMENT

The operating system environment variables that psql responds to are specified.

Popular variables: PGUSER PGDATABASE PGHOST PGPORT . These allow you to configure psql to connect to any database without specifying parameters.

Operating system environment variables can be set using the \setenv command in the ~/.psqlrc file or the global /opt/tantor/db/18/etc/postgresql/psqlrc file . Environment variables are not set using the \set , \pset , and \! export commands .

By default, the vi editor is used for editing commands \ef \ev \e .

You can override the editor by setting an environment variable. Example:

export PSQL_EDITOR=/usr/bin/mcedit

Instead of the name PLQL_EDITOR , you can use the names EDITOR or VISUAL .

You can also run the following command interactively in psql:

\setenv PSQL_EDITOR /usr/bin/mcedit

You can put this command in the ~/.psqlrc file or the global /opt/tantor/db/18/etc/postgresql/psqlrc file.

psql variables \set

Psql variables are set with the \set name value command .

You can view psql variables (internal variables) using the \set command .

names are case-sensitive . Some variables control psql's operation and have default values. They expire until psql exits or until the \unset command is executed. name. You can set your own variables and use them as macros.

You can refer to variables by prefixing them with a colon , for example:

postgres=# \set test1 'select user'

postgres=# : test1 ;

user

----------

postgres

(1 row)

postgres=# select * from (: test1 );

Executing commands in psql

Commands beginning with a backslash " \ " are processed by psql. Help for such commands can be found using the \? command.

It is worth distinguishing between the commands \set , \pset , set .

\pset - sets predefined output formatting options for the psql utility.

\set command . These variables are case-sensitive. Some variables control psql's operation and have default values. You can set your own variables while psql is running and use them as macros.

Other commands are sent as text to the server process. To send a command, enter " ; " and a carriage return (the <ENTER> key on the keyboard).

set command sets the value of a configuration parameter in the server process's memory (not the psql utility's) at the session ( set session ) or transaction ( set local ) level. You can also store an arbitrary variable in the server process's memory; such a variable must have a period in its name. The parameter's value can be set using the set_config() function . The value can be read using the show command or the current_setting() function . You can reset it to the default value with the reset command .

In psql there are commands \g , \gx , \gexec , \gset , \g which can replace " ; ", but these commands only work in psql.

If you don't type " ; ", but simply type a carriage return (the <ENTER> key ), psql assumes the command is multi-line and accumulates previous lines in the buffer. The psql prompt will change: the " = " sign in the prompt will be replaced by " - ". To clear the buffer, you can type \r (short for \reset ), but only if psql doesn't expect a closing apostrophe. ` ' ` or double quotes ` " `:

postgres=# select '

postgres ' # '

postgres - # \r

postgres=# select "

postgres " # "

postgres - # \r

postgres=#

View the contents of the buffer or the last command if the buffer is empty \p (short for \print )

ON_ERROR_ROLLBACK parameter

There is a parameter in psql:

postgres=# \set ON_ERROR_ROLLBACK interactive 

By default, this setting is set to off . If a command in a transaction returns an error, the transaction is rolled back and cannot be committed. The transaction commands have already changed the storage structures, but this cannot be committed; the commands must be repeated in a new transaction. If an error occurs, the transaction goes into a failed state, indicated by the " ! " icon in the psql prompt. Any command to complete the transaction (commit, end, rollback) returns a ROLLBACK message .