!	Последнее обновление: 30 мая 2024 г. По всем вопросам и предложениям касательно обучения, пожалуйста, обращайтесь: [email protected]

Раздел 1. Установка СУБД Tantor

Инсталляция СУБД Tantor
Деинсталляция

Часть 1. Инсталляция СУБД Tantor

В виртуальной машине курса предустановлена версия Tantor SE для целей обучения.

Продемонстрируем установку СУБД Tantor.

1) Откроем терминал с правами root:

astra@tantor:~$ sudo bash

2) Выполним предварительные проверки.

Число ядер процессора (результат может отличаться от приведенных как пример значений):

root@tantor:/home/astra# cat /proc/cpuinfo | grep cores

cpu cores : 2

Оперативной памяти:

root@tantor:/home/astra# cat /proc/meminfo | grep Mem

MemTotal: 8130152 kB

MemFree: 3288668 kB

MemAvailable: 4781360 kB

Свободное место в точке монтирования "/":

root@tantor:/home/astra# df -HT | grep /$

/dev/sda1 ext4 41G 22G 18G 56% /

Свободно 18Гб.

При промышленной эксплуатации рекомендуется иметь 4 ядра;

Оперативной памяти: по крайней мере 4ГБ;

Свободного места на системе хранения («диске»): 40ГБ.

3) Скачаем инсталлятор:

root@tantor:/home/astra# wget https://public.tantorlabs.ru/db_installer.sh

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

Resolving public.tantorlabs.ru (public.tantorlabs.ru)... 84.201.157.208

Connecting to public.tantorlabs.ru (public.tantorlabs.ru)|84.201.157.208|:443... connected.

HTTP request sent, awaiting response... 200 OK

Length: 18312 (18K) [application/octet-stream]

Saving to: ‘db_installer.sh’

db_installer.sh 100%[==================================>] 17,88K --.-KB/s in 0s

‘db_installer.sh’ saved [18312/18312]

4) Посмотрим разрешение на исполнение инсталлятора:

root@tantor:/home/astra# ls -al db_installer.sh

-rw-r--r-- 1 root root 18353 db_installer.sh

5) Установка разрешения на исполнение инсталлятора:

root@tantor:/home/astra# chmod +x db_installer.sh

6) Остановим основной кластер:

root@tantor:/home/astra# systemctl stop tantor-se-server-16

7) Остановим реплику:

root@tantor:/home/astra# systemctl stop tantor-se-server-16-replica.service

8) Проверим версию инсталлятора и обратим внимание слушателей на выделенные параметры:

root@tantor:/home/astra# ./db_installer.sh --help

======================================

Usage: db_installer.sh [OPTIONS]

Installer version: 24.04.12

This script will perform installation of the Tantor DB on current host.

If the Tantor DB is already installed, no actions will be taken.

Available options:

--help Show this help message.

--------------------------------------------------------------------

--edition= Set edition (be, se, se-1c, se-certified). "se" is default.

--major-version= Set major version (14, 15)

--maintenance-version= Set maintenance version (15.2.4).

By default latest version will be installed.

--do-initdb After installation run initdb with checksums.

--package= Set specific package (all, client, libpq5).

"all" is default.

--------------------------------------------------------------------

--from-file= Install package from local file (rpm, deb)

May be used with --do-initdb option

====================================

Example for commercial use

======================================

export NEXUS_USER="user_name"

export NEXUS_USER_PASSWORD="user_password"

export NEXUS_URL="nexus.tantorlabs.ru"

./db_installer.sh \

--do-initdb \

--major-version=15 \

--edition=se

==============================================

Example for evaluation use (without login and password)

Only for Basic Edition

====================================

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

./db_installer.sh \

--do-initdb \

--major-version=15 \

--edition=be

==========================================

Examples how to install from file

==========================================

./db_installer.sh \

--from-file=./packages/tantor-be-server-15_15.4.1.jammy_amd64.deb

./db_installer.sh \

--do-initdb \

--from-file=/tmp/tantor-be-server-15_15.4.1.jammy_amd64.deb

При создании кластера инсталлятором включается подсчет контрольных сумм для блоков данных.

9) Установка адреса расположения дистрибутивов:

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

10) Проверим переменные окружения:

root@tantor:~# cat /var/lib/postgresql/.bash_profile

#export PATH=/opt/tantor/db/16/bin:$PATH

export PGDATA=/var/lib/postgresql/tantor-se-16/data

export LC_MESSAGES=ru_RU.utf8

unset LANGUAGE

Если нужно, чтобы сообщения утилит был локализован, то нужно отредактировать файл профиля: командой unset убрать переменную окружения LANGUAGE и установить LC_MESSAGES.

Если не закомментировать строку PATH, то кластер может быть создан и запущен из-под той сборки, которая присутствует в пути.

Инсталлятор скачан, порт по умолчанию 5432 свободен, адрес репозитория с дистрибутивами установлен. Можно приступить к инсталляции. Можно приступить к установке.

Удалим ссылку на репозитарий pgAdmin4, будет мешать установке с ошибкой no longer has a Release file:

root@tantor:/home/astra# rm /etc/apt/sources.list.d/pgadmin4.list

11) Установка со скачиванием дистрибутива и созданием базы данных:

root@tantor:/home/astra# ./db_installer.sh --edition=be --major-version=15 --do-initdb

Hit:1 http://dl.astralinux.ru/astra/stable/1.7_x86-64/repository-base 1.7_x86-64 InRelease

Get:2 https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/buster pgadmin4 InRelease [4,217 B]

Get:3 https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/buster pgadmin4/main amd64 Packages [9,948 B]

Get:4 https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/buster pgadmin4/main all Packages [6,483 B]

Fetched 20.6 kB in 0s (41.4 kB/s)

Reading package lists... Done

deb [arch=amd64] https://nexus-public.tantorlabs.ru/repository/astra-smolensk-1.7 smolensk main

Hit:1 http://dl.astralinux.ru/astra/stable/1.7_x86-64/repository-base 1.7_x86-64 InRelease

Get:2 https://nexus-public.tantorlabs.ru/repository/astra-smolensk-1.7 smolensk InRelease [1,556 B]

Hit:3 https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/buster pgadmin4 InRelease

Get:4 https://nexus-public.tantorlabs.ru/repository/astra-smolensk-1.7 smolensk/main amd64 Packages [3,491 B]

Fetched 5,047 B in 0s (10.4 kB/s)

Reading package lists... Done

Building dependency tree

Reading state information... Done

The following NEW packages will be installed:

tantor-be-server-15

0 upgraded, 1 newly installed, 0 to remove and 2 not upgraded.

Need to get 18.0 MB of archives.

After this operation, 0 B of additional disk space will be used.

Get:1 https://nexus-public.tantorlabs.ru/repository/astra-smolensk-1.7 smolensk/main amd64 tantor-be-server-15 amd64 15.6.0 [18.0 MB]

Fetched 18.0 MB in 1s (19.6 MB/s)

Selecting previously unselected package tantor-be-server-15.

(Reading database ... 210711 files and directories currently installed.)

Preparing to unpack .../tantor-be-server-15_15.6.0_amd64.deb ...

+ echo ---------------------------------------------

---------------------------------------------

+ echo 'tantor-be-server-15 is getting installed'

tantor-be-server-15 is getting installed

+ echo ---------------------------------------------

---------------------------------------------

+ getent group postgres

+ getent passwd postgres

++ getent passwd postgres

++ awk -F: '{print $6}'

+ current_home=/var/lib/postgresql

+ '[' /var/lib/postgresql '!=' /var/lib/postgresql ']'

+ mkdir -p /var/lib/postgresql

+ chown postgres:postgres /var/lib/postgresql

+ chmod 700 /var/lib/postgresql

+ mkdir -p /var/run/postgresql

+ chown postgres:postgres /var/run/postgresql

+ '[' '!' -d /usr/lib/tmpfiles.d ']'

+ echo 'D /run/postgresql 0755 postgres postgres - -'

+ tee /usr/lib/tmpfiles.d/tantor-db.conf

+ mkdir -p /etc/ld.so.conf.d

+ tee /etc/ld.so.conf.d/tantor-be-15.conf

+ echo /opt/tantor/db/15/lib

+ cat /etc/ld.so.conf.d/tantor-be-15.conf

/opt/tantor/db/15/lib

+ echo ---------------------------------------------

---------------------------------------------

+ set +vx

Unpacking tantor-be-server-15 (15.6.0) ...

Setting up tantor-be-server-15 (15.6.0) ...

+ echo ---------------------------------------------

---------------------------------------------

+ echo 'tantor-be-server-15 is getting installed'

tantor-be-server-15 is getting installed

+ echo ---------------------------------------------

---------------------------------------------

+ /usr/sbin/ldconfig

+ /bin/systemctl daemon-reload

+ '[' '!' -f /var/lib/postgresql/.bash_profile ']'

+ '[' -f /var/lib/postgresql/.bash_profile ']'

++ grep /opt/tantor/db/15/bin /var/lib/postgresql/.bash_profile

+ '[' -z '' ']'

+ echo 'export PATH=/opt/tantor/db/15/bin:$PATH'

+ chown postgres:postgres /var/lib/postgresql/.bash_profile

+ '[' '!' -d /var/lib/postgresql/tantor-be-15/data ']'

+ '[' '!' -d /var/lib/postgresql/data ']'

+ mkdir -p /var/lib/postgresql/tantor-be-15/data

+ chown postgres:postgres /var/lib/postgresql/tantor-be-15/data

+ chmod 700 /var/lib/postgresql/tantor-be-15/data

+ echo ---------------------------------------------

---------------------------------------------

+ set +vx

Файлы, относящиеся к этой СУБД, будут принадлежать пользователю «postgres».

От его имени также будет запускаться процесс сервера.

Кластер баз данных будет инициализирован со следующими параметрами локали:

провайдер: libc

LC_COLLATE: en_US.UTF-8

LC_CTYPE: en_US.UTF-8

LC_MESSAGES: ru_RU.utf8

LC_MONETARY: en_US.UTF-8

LC_NUMERIC: en_US.UTF-8

LC_TIME: en_US.UTF-8

Кодировка БД по умолчанию, выбранная в соответствии с настройками: "UTF8".

Выбрана конфигурация текстового поиска по умолчанию "english".

Контроль целостности страниц данных включён.

исправление прав для существующего каталога

/var/lib/postgresql/tantor-be-15/data... ок

создание подкаталогов... ок

выбирается реализация динамической разделяемой памяти... posix

выбирается значение max_connections по умолчанию... 100

выбирается значение shared_buffers по умолчанию... 128MB

выбирается часовой пояс по умолчанию... Europe/Moscow

создание конфигурационных файлов... ок

выполняется подготовительный скрипт... ок

выполняется заключительная инициализация... ок

сохранение данных на диске... ок

initdb: предупреждение: включение метода аутентификации "trust" для локальных подключений.

initdb: подсказка: Другой метод можно выбрать, отредактировав pg_hba.conf или ещё раз запустив initdb с ключом -A, --auth-local или --auth-host.

Готово. Теперь вы можете запустить сервер баз данных:

/opt/tantor/db/15/bin/pg_ctl -D /var/lib/postgresql/tantor-be-15/data -l файл_журнала start

Created symlink

/etc/systemd/system/multi-user.target.wants/tantor-be-server-15.service → /lib/systemd/system/tantor-be-server-15.service.

? tantor-be-server-15.service - Tantor Basic database server 15

Loaded: loaded (/lib/systemd/system/tantor-be-server-15.service; enabled; vendor preset: enabled)

Active: active (running) since 10:38:36 MSK; 26ms ago

Docs: https://www.postgresql.org/docs/15/static/

Process: 10564 ExecStartPre=/opt/tantor/db/15/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)

Process: 10566 ExecStart=/opt/tantor/db/15/bin/pg_ctl start -D ${PGDATA} -s -w -t ${PGSTARTTIMEOUT} (code=exited, status=0/SUCCESS)

Main PID: 10568 (postgres)

Tasks: 6 (limit: 4915)

Memory: 16.9M

CPU: 55ms

CGroup: /system.slice/tantor-be-server-15.service

+-10568 /opt/tantor/db/15/bin/postgres -D /var/lib/postgresql/tantor-be-15/data

+-10569 postgres: checkpointer

+-10570 postgres: background writer

+-10572 postgres: walwriter

+-10573 postgres: autovacuum launcher

L-10574 postgres: logical replication launcher

10:38:36 tantor systemd[1]: Starting Tantor Basic database server 15...

10:38:36 tantor pg_ctl[10566]: 2024-04-17 10:38:36.248 MSK [10568] LOG: starting PostgreSQL 15.6 on x86_64-pc-linux-gnu, compiled by gcc (AstraLinuxSE 8.3.0-6) 8.3.0, 64-bit

10:38:36 tantor pg_ctl[10566]: 2024-04-17 10:38:36.249 MSK [10568] LOG: listening on IPv6 address "::1", port 5434

10:38:36 tantor pg_ctl[10566]: 2024-04-17 10:38:36.250 MSK [10568] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5434"

10:38:36 tantor pg_ctl[10566]: 2024-04-17 10:38:36.254 MSK [10571] LOG: database system was shut down at 2024-04-17 10:38:35 MSK

10:38:36 tantor pg_ctl[10566]: 2024-04-17 10:38:36.258 MSK [10568] LOG: database system is ready to accept connections

10:38:36 tantor systemd[1]: Started Tantor Basic database server 15.

tantor_version

-----------------------------

Tantor Basic Edition 15.6.0

(1 row)

Installation successfully completed.

Если в переменной окружения PATH пользователя postgres (файлы профиля /var/lib/postgresql/.bash_profile) присутствовала директория другой сборки (export PATH=/opt/tantor/db/16/bin:$PATH), то кластер будет создан и запущен из под этой сборки (Tantor Special Edition 16.1.0.

12) Переключимся в пользователя postgres:

root@tantor:/home/astra# su - postgres

13) Проверяем, что путь к исполняемым файлам был добавлен в файл профиля пользователя postgres в конце файла:

postgres@tantor:~$ cat .bash_profile

#export PATH=/opt/tantor/db/16/bin:$PATH

export PGDATA=/var/lib/postgresql/tantor-se-16/data

export LC_MESSAGES=ru_RU.utf8

unset LANGUAGE

export PATH=/opt/tantor/db/15/bin:$PATH

14) Проверка, что кластер работает:

postgres@tantor:~$ psql

psql (15.6)

Введите "help", чтобы получить справку.

postgres=# select version();

version

---------------------------------------------------------------------------------------

PostgreSQL 15.6 on x86_64-pc-linux-gnu, compiled by gcc (AstraLinuxSE 8.3.0-6) 8.3.0, 64-bit

(1 строка)

postgres=# \q

15) Вернемся в терминал root:

postgres@tantor:~$ exit

выход

root@tantor:/home/astra#

Демонстрация установки выполнена.

Часть 2. Деинсталляция

1) Остановим экземпляр кластера Tantor BE:

root@tantor:/home/astra# systemctl stop tantor-be-server-15

2) Запретим автоматический запуск службы:

root@tantor:/home/astra# systemctl disable tantor-be-server-15

Removed /etc/systemd/system/multi-user.target.wants/tantor-be-server-15.service.

3) Посмотрим список установленного программного обеспечения Tantor:

root@tantor:/home/astra# apt list | grep tantor

WARNING: apt does not have a stable CLI interface. Use with caution in scripts.

tantor-be-client-15/smolensk 15.6.0 amd64

tantor-be-libpq5-15/smolensk 15.6.0 amd64

tantor-be-server-14/smolensk 14.11.0 amd64

tantor-be-server-15/smolensk,now 15.6.0 amd64 [installed]

tantor-se-server-16/now 16.1.0 amd64 [installed,local]

4) Деинсталлируем то, что установили:

root@tantor:/home/astra# apt remove tantor-be-server-15/smolensk

Reading package lists... Done

Building dependency tree

Reading state information... Done

Selected version '15.6.0' (smolensk [amd64]) for 'tantor-be-server-15'

The following packages will be REMOVED:

tantor-be-server-15

0 upgraded, 0 newly installed, 1 to remove and 2 not upgraded.

After this operation, 0 B of additional disk space will be used.

Do you want to continue? [Y/n] Y

(Reading database ... 213906 files and directories currently installed.)

Removing tantor-be-server-15 (15.6.0) ...

+ echo ---------------------------------------------

+ echo 'tantor-be-server-15 is getting removed'

tantor-be-server-15 is getting removed

+ echo ---------------------------------------------

+ /bin/systemctl --no-reload disable tantor-be-server-15

+ /bin/systemctl stop tantor-be-server-15

+ echo ---------------------------------------------

---------------------------------------------

+ set +vx

+ echo ---------------------------------------------

+ echo 'tantor-be-server-15 is getting removed'

tantor-be-server-15 is getting removed

+ echo ---------------------------------------------

+ /usr/sbin/ldconfig

+ /bin/systemctl daemon-reload

+ '[' -f /var/lib/postgresql/.bash_profile ']'

++ grep /opt/tantor/db/15/bin /var/lib/postgresql/.bash_profile

+ '[' '!' -z 'export PATH=/opt/tantor/db/15/bin:$PATH' ']'

+ sed -i 's|/opt/tantor/db/15/bin:*||g' /var/lib/postgresql/.bash_profile

+ sed -i '/^PATH=:*$$PATH$*:*$/d' /var/lib/postgresql/.bash_profile

+ /usr/sbin/ldconfig

+ echo ---------------------------------------------

+ set +vx

5) Проверим, как изменился список установленного программного обеспечения Tantor:

root@tantor:/home/astra# apt list | grep tantor

WARNING: apt does not have a stable CLI interface. Use with caution in scripts.

tantor-be-client-15/smolensk 15.6.0 amd64

tantor-be-libpq5-15/smolensk 15.6.0 amd64

tantor-be-server-14/smolensk 14.11.0 amd64

tantor-be-server-15/smolensk,now 15.6.0 amd64 [residual-config]

tantor-se-server-16/now 16.1.0 amd64 [installed,local]

Пакет деинсталлирован, но конфигурационные файлы были оставлены.

6) Посмотрим, есть ли ещё пакеты residual config:

root@tantor:/home/astra# aptitude search ~c

c tantor-be-server-15 - Tantor Basic database server installation package

7) Удалим эти пакеты:

root@tantor:/home/astra# aptitude purge ~c

The following packages will be REMOVED:

tantor-be-server-15{p}

0 packages upgraded, 0 newly installed, 1 to remove and 2 not upgraded.

Need to get 0 B of archives. After unpacking 0 B will be used.

Do you want to continue? [Y/n/?] Y

(Reading database ... 210713 files and directories currently installed.)

Purging configuration files for tantor-be-server-15 (15.6.0) ...

+ echo ---------------------------------------------

---------------------------------------------

+ echo 'tantor-be-server-15 is getting removed'

tantor-be-server-15 is getting removed

+ echo ---------------------------------------------

+ /usr/sbin/ldconfig

+ /bin/systemctl daemon-reload

+ '[' -f /var/lib/postgresql/.bash_profile ']'

++ grep /opt/tantor/db/15/bin /var/lib/postgresql/.bash_profile

+ '[' '!' -z '' ']'

+ /usr/sbin/ldconfig

+ echo ---------------------------------------------

+ set +vx

dpkg: warning: while removing tantor-be-server-15, directory '/opt/tantor/db/15/lib' not empty so not removed

8) В директории /opt/tantor/db/15/lib лежит осиротевшая символическая ссылка:

root@tantor:/home/astra# ls -l --color /opt/tantor/db/15/lib/

total 0

lrwxrwxrwx 1 root root libzstd.so.1 -> libzstd.so.1.5.5

9) Удалим директорию:

root@tantor:/home/astra# rm -rf /opt/tantor/db/15

10) Директория созданного при инсталляции кластера «BE» не была удалена. Удалим её:

root@tantor:/home/astra# rm -rf /var/lib/postgresql/tantor-be-15

11) Проверим ещё раз список пакетов:

root@tantor:/home/astra# apt list | grep tantor

WARNING: apt does not have a stable CLI interface. Use with caution in scripts.

tantor-be-client-15/smolensk 15.6.0 amd64

tantor-be-libpq5-15/smolensk 15.6.0 amd64

tantor-be-server-14/smolensk 14.11.0 amd64

tantor-be-server-15/smolensk 15.6.0 amd64

tantor-se-server-16/now 16.1.0 amd64 [installed,local]

12) Файл /var/lib/postgresql/.bash_profile выглядит так:

root@tantor:/home/astra# cat /var/lib/postgresql/.bash_profile

#export PATH=/opt/tantor/db/16/bin:$PATH

export PGDATA=/var/lib/postgresql/tantor-se-16/data

export LC_MESSAGES=ru_RU.utf8

unset LANGUAGE

export PATH=$PATH

13) Уберите из файла комментарий (знак #) и бесполезную строку. Файл должен выглядеть так:

root@tantor:/home/astra# cat /var/lib/postgresql/.bash_profile

export PATH=/opt/tantor/db/16/bin:$PATH

export PGDATA=/var/lib/postgresql/tantor-se-16/data

export LC_MESSAGES=ru_RU.utf8

unset LANGUAGE

14) Запустим кластера 16 версии, которые останавливали:

root@tantor:/home/astra# systemctl start tantor-se-server-16

root@tantor:/home/astra# systemctl start tantor-se-server-16-replica

15) Переключимся в пользователя postgres и проверим, что кластер работоспособен:

root@tantor:/home/astra# su - postgres

postgres@tantor:~$ psql

psql (16.1)

Введите "help", чтобы получить справку.

postgres=# select tantor_version();

tantor_version

-------------------------------

Tantor Special Edition 16.1.0

(1 строка)

postgres=# \q

Раздел 2. Архитектура

Структура памяти

Транзакция в psql
Список фоновых процессов
Буферный кэш, команда EXPLAIN
Журнал предзаписи. Где хранится?
Контрольная точка
Восстановление после сбоя

Часть 1. Транзакция в psql

1) Откроем терминал «Fly» на рабочем столе:

astra@tantor:~$ psql

psql (16.1)

2) Введите "help", чтобы получить справку.

postgres=#

3) Создадим произвольную таблицу:

postgres=# CREATE TABLE a(id integer);

CREATE TABLE

4) Посмотрим что получилось:

postgres=# \dt a

Список отношений

Схема | Имя | Тип | Владелец

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

public | a | таблица | postgres

строка)

5) Откроем транзакцию:

postgres=# BEGIN;

BEGIN

6) Вставим первую строчку. Обратите внимание, что с помощью табуляции можно дописывать ключевые слова и даже сложные конструкции.

postgres=*# INSERT INTO a VALUES (1);

INSERT 0 1

Обратите внимание на появление звездочки в строке - это означает что идет транзакция.

7) Попробуем во втором терминале увидеть первую строчку таблицы. Откроем второй терминал:

8) Загрузим psql.

astra@tantor:~$ psql

psql (16.1)

Введите "help", чтобы получить справку.

postgres=#

9) Обратимся к таблице:

postgres=# SELECT * FROM a;

----

(0 строк)

Убедились - пока мы не видим первой строчки. Видны только зафиксированные данные. Грязное чтение не допускается.

-------------------------------------------------

10) В первом терминале зафиксируем транзакцию.

postgres=*# COMMIT;

COMMIT

11) Во втором терминале обратимся к таблице еще раз.

postgres=# SELECT * FROM a;

----

(1 строка)

Теперь изменения таблицы зафиксированы.

Вывод - видны только те изменения, которые успешно зафиксированы.

Часть 2. Список фоновых процессов

1) Посмотрим где находится директория PGDATA, где находятся файлы кластера БД.

postgres=# SHOW data_directory;

data_directory

---------------------------------------

/var/lib/postgresql/tantor-se-16/data

(1 строка)

2) Выйдите в первом терминале из psql.

postgres=# \q

3) Чтобы посмотреть список процессов воспользуемся утилитой ps

astra@tantor:~$ sudo cat /var/lib/postgresql/tantor-se-16/data/postmaster.pid

466

/var/lib/postgresql/tantor-se-16/data

1713847705

5432

/var/run/postgresql

1048641 0

ready

4) Возьмем PID = 466

astra@tantor:~$ sudo ps -o command --ppid 466

COMMAND

postgres: logger

postgres: checkpointer

postgres: background writer

postgres: walwriter

postgres: autovacuum launcher

postgres: logical replication launcher

postgres: walsender replicator ::1(34460) streaming 0/6DA71ED8

postgres: postgres postgres [local] idle

Жирным шрифтом показаны системные фоновые процессы, остальные пользовательские.

Список процессов можно увидеть также через представление pg_stat_activity.

5) Сделайте во втором терминале.

postgres=# SELECT pid, backend_type, backend_start

FROM pg_stat_activity;

pid | backend_type | backend_start

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

527 | autovacuum launcher | 2024-04-23 07:48:25.435889+03

528 | logical replication launcher | 2024-04-23 07:48:25.441432+03

533 | walsender | 2024-04-23 07:48:25.472863+03

25072 | client backend | 2024-04-23 07:48:51.242631+03

10977 | client backend | 2024-04-23 08:06:17.871119+03

520 | background writer | 2024-04-23 07:48:25.403365+03

519 | checkpointer | 2024-04-23 07:48:25.402941+03

526 | walwriter | 2024-04-23 07:48:25.425135+03

(8 строк)

Часть 3. Буферный кэш, команда EXPLAIN

1) Во втором терминале добавим строки в таблицу «a».

postgres=#INSERT INTO a SELECT id FROM generate_series(1,10000) AS id;

INSERT 0 10000

2) В первом терминале перезагрузите сервер:

astra@education:~$ sudo systemctl restart tantor-se-server-16

3) Во втором терминале сделать реконнект:

postgres=# \c

Вы подключены к базе данных «postgres», как пользователь «postgres».

4) С помощью команды Explain посмотрите, откуда берется информация:

postgres=# EXPLAIN (analyze, buffers)

SELECT * FROM a;

QUERY PLAN

-----------------------------------------------------------------------------------------------------

Seq Scan on a (cost=0.00..145.00 rows=10000 width=4) (actual time=0.035..1.952 rows=10000 loops=1)

Buffers: shared read=45

Planning:

Buffers: shared hit=16 read=6 dirtied=3

Planning Time: 0.428 ms

Execution Time: 2.948 ms

(6 строк)

Обратите внимание на строку Buffers. Информация была взята с диска или через кэш операционной системы.

5) Сделайте эксперимент еще раз.

postgres=# EXPLAIN (analyze, buffers)

SELECT * FROM a;

QUERY PLAN

-------------------------------------------------------------------------------------------------

Seq Scan on a (cost=0.00..145.00 rows=10000 width=4) (actual time=0.016..1.383 rows=10000 loops=1)

Buffers: shared hit=45

Planning Time: 0.063 ms

Execution Time: 2.355 ms

(4 строки)

Информация изменилась. Теперь информация найдена в буферном кэше

Часть 4. Журнал предзаписи. Где хранится?

В первом терминале выполните команду:

astra@education:~$ sudo ls -l /var/lib/postgresql/tantor-se-16/data/pg_wal

итого 360452

-rw------- 1 postgres postgres 16777216 Apr 23 08:10 00000001000000000000006D

-rw-r----- 1 postgres postgres 16777216 Apr 3 14:28 00000001000000000000006E

-rw-r----- 1 postgres postgres 16777216 Apr 3 14:28 00000001000000000000006F

-rw------- 1 postgres postgres 16777216 Apr 3 14:41 000000010000000000000070

-rw------- 1 postgres postgres 16777216 Apr 3 14:41 000000010000000000000071

-rw------- 1 postgres postgres 16777216 Apr 3 14:41 000000010000000000000072

-rw------- 1 postgres postgres 16777216 Apr 3 14:41 000000010000000000000073

-rw------- 1 postgres postgres 16777216 Apr 3 14:42 000000010000000000000074

-rw------- 1 postgres postgres 16777216 Apr 3 14:41 000000010000000000000075

-rw------- 1 postgres postgres 16777216 Apr 3 14:41 000000010000000000000076

-rw------- 1 postgres postgres 16777216 Apr 3 14:41 000000010000000000000077

-rw------- 1 postgres postgres 16777216 Apr 3 14:41 000000010000000000000078

-rw------- 1 postgres postgres 16777216 Apr 3 14:41 000000010000000000000079

-rw------- 1 postgres postgres 16777216 Apr 3 14:41 00000001000000000000007A

-rw------- 1 postgres postgres 16777216 Apr 3 14:41 00000001000000000000007B

-rw------- 1 postgres postgres 16777216 Apr 3 14:42 00000001000000000000007C

-rw------- 1 postgres postgres 16777216 Apr 3 14:42 00000001000000000000007D

-rw------- 1 postgres postgres 16777216 Apr 3 14:41 00000001000000000000007E

-rw------- 1 postgres postgres 16777216 Apr 3 14:41 00000001000000000000007F

-rw------- 1 postgres postgres 16777216 Apr 3 14:42 000000010000000000000080

-rw-r----- 1 postgres postgres 16777216 Apr 3 14:42 000000010000000000000081

-rw------- 1 postgres postgres 16777216 Apr 3 14:42 000000010000000000000082

drwx------ 2 postgres postgres 4096 Apr 2 12:09 archive_status

Файлы журнала предзаписи находятся в директории pg_wal. Сегментами по 16 мегабайт.

Часть 5. Контрольная точка

1) Контрольная точка выполняется периодически, посмотрим во втором терминале, какой интервал установлен.

postgres=# SHOW checkpoint_timeout;

checkpoint_timeout

--------------------

5min

(1 строка)

2) Контрольную точку можно запустить вручную.

postgres=# CHECKPOINT;

CHECKPOINT

3) В первом терминале посмотрим на файлы журнала предзаписи. Ненужные файлы удалены.

astra@education:~$ sudo ls -l /var/lib/postgresql/tantor-se-16/data/pg_wal

итого 802820

-rw------- 1 postgres postgres 16777216 Feb 14 07:40 0000000100000001000000A4

-rw------- 1 postgres postgres 16777216 Feb 14 07:40 0000000100000001000000A5

-rw------- 1 postgres postgres 16777216 Feb 14 07:40 0000000100000001000000A6

-rw------- 1 postgres postgres 16777216 Feb 14 07:40 0000000100000001000000A7

-rw------- 1 postgres postgres 16777216 Feb 14 07:40 0000000100000001000000A8

Часть 6. Восстановление после сбоя

1) Добавим во втором терминале новые строчки:

postgres=# INSERT INTO a SELECT id FROM generate_series(1,10000) AS id;

INSERT 0 10000

2) Остановите кластер БД в режиме системного сбоя. Для начала определим PID процесса postmaster.

astra@education:~$ sudo cat /var/lib/postgresql/tantor-se-16/data/postmaster.pid

12563

/var/lib/postgresql/tantor-se-16/data

1713849023

5432

/var/run/postgresql

1048641 24

ready

astra@education:~$ sudo kill -9 12563

3) Запустим экземпляр сервера.

astra@education:~$ sudo systemctl start tantor-se-server-16

Немного тормозит. Идет восстановление.

4) Во втором окне посмотрим, сохранились ли вставленные строки.

postgres=# \c

Вы подключены к базе данных «postgres» как пользователь «postgres».

postgres=# SELECT count(*) FROM a;

count

-------

20000

(1 строка)

5) Очистим объекты во втором терминале.

postgres=# DROP TABLE a;

DROP TABLE

postgres=# \dt

Отношения не найдены.

Многоверсионность

Вставка, обновление и удаление строки
Видимость версии строки на различных уровнях изоляции
Состояние транзакции по CLOG
Блокировка таблицы
Блокировка строки

Часть 1. Вставка, обновление и удаление строки

1) Загрузим psql:

astra@tantor:~$ psql

psql (16.1)

Введите "help", чтобы получить справку.

postgres=#

2) Создадим произвольную таблицу.

postgres=# CREATE TABLE a(id integer);

CREATE TABLE

3) Посмотрим что получилось.

postgres=# \dt a

Список отношений

Схема | Имя | Тип | Владелец

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

public | a | таблица | postgres

(1 строка)

4) Вставим первую строку в таблицу.

postgres=# INSERT INTO a VALUES(100);

INSERT 0 1

5) Посмотрим какой номер транзакции xmin.

postgres=# SELECT xmin, xmax, * FROM a;

xmin | xmax | id

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

1567 | 0 | 100

(1 строка)

Получился 1567 это номер транзакции в которой была создана первая версия строки.
6) Начнем явную транзакцию.

postgres=# BEGIN;

BEGIN

7) Обновим первую строчку.

postgres=*# UPDATE a SET id = 200;

UPDATE 1

8) Обратимся и посмотрим что получилось.

postgres=*# SELECT xmin, xmax, * FROM a;

xmin | xmax | id

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

1569 | 0 | 200

(1 строка)

9) Убедились в том, что транзакция видит свои изменения.

Как вы думаете, что будет если обратиться в параллельной транзакции?
id=100 или 200?
Во втором терминале обращаемся к таблице.
10) Загрузим psql.

astra@tantor:~$ psql

psql (16.1)

Введите "help", чтобы получить справку.

postgres=#

postgres=# SELECT xmin, xmax, * FROM a;

xmin | xmax | id

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

1568 | 1569 | 100

(1 строка)

Обратите внимание, что xmax изменился, это значит что уже существует вторая версия строки но она еще не зафиксирована.
11) В первом терминале фиксируем транзакцию.

postgres=*# COMMIT;

COMMIT

12) Во втором терминале теперь видим вторую строку.

postgres=# SELECT xmin, xmax, * FROM a;

xmin | xmax | id

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

1569 | 0 | 200

(1 строка)

13) Теперь посмотрим, как выглядит удаление. Откроем транзакцию в первом терминале.

postgres=# BEGIN;

BEGIN

14) Удаляем строчку.

postgres=*# DELETE FROM a;

DELETE 1

postgres=*# SELECT xmin, xmax, * FROM a;

xmin | xmax | id

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

(0 rows)

Первая транзакция не видит строчку, она удалена, но изменение пока не зафиксировано.

15) Во втором терминале:

postgres=# SELECT xmin, xmax, * FROM a;

xmin | xmax | id

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

1569 | 1570 | 200

(1 строка)

Строка еще видна, но xmax опять изменился.

16) В первом терминале фиксируем транзакцию:

postgres=*# COMMIT;

COMMIT

17) Во втором терминале теперь видим изменение:

postgres=# SELECT xmin, xmax, * FROM a;

xmin | xmax | id

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

(0 rows)

Часть 2. Видимость версии строки на различных уровнях изоляции

1) Откроем первую транзакцию и вставим строку.

postgres=# BEGIN;

BEGIN

2) Посмотрим уровень изоляции.

postgres=*# SHOW transaction_isolation;

transaction_isolation

-----------------------

read committed

(1 строка)

postgres=*# INSERT INTO a VALUES(100);

INSERT 0 1

postgres=*# SELECT xmin, xmax, * FROM a;

xmin | xmax | id

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

1571 | 0 | 100

(1 строка)

3) Начнем вторую транзакцию во втором терминале и обратимся к таблице.

postgres=# BEGIN;

BEGIN

postgres=*# SELECT xmin, xmax, * FROM a;

xmin | xmax | id

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

(0 строк)

4) Посмотрим уровень изоляции.
postgres=*# SHOW transaction_isolation;

transaction_isolation

-----------------------

read committed

(1 строка)

5) Пока новая строка не видна. Зафиксируем первую транзакцию

postgres=*# COMMIT;

COMMIT

6) Во втором окне повторно обратимся к таблице. Что увидим?

postgres=*# SELECT xmin, xmax, * FROM a;

xmin | xmax | id

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

1571 | 0 | 100

(1 строка)

7) Зафиксируем вторую транзакцию.

postgres=*# COMMIT;

COMMIT

Изменения стали видны. Это и есть аномалия неповторяющегося чтения.

Теперь в первом окне начнем транзакцию на уровне repeatable read.

8) Вставим еще одну строку.

postgres=# BEGIN ISOLATION LEVEL REPEATABLE READ;

BEGIN

postgres=*# INSERT INTO a VALUES(200);

INSERT 0 1

postgres=*# SELECT xmin, xmax, * FROM a;

xmin | xmax | id

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

1571 | 0 | 100

1572 | 0 | 200

(2 строки)

9) Во второй транзакции обратимся к таблице в новой транзакции на том же уровне.

postgres=# BEGIN ISOLATION LEVEL REPEATABLE READ;

BEGIN

postgres=*# SELECT xmin, xmax, * FROM a;

xmin | xmax | id

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

1571 | 0 | 100

(1 строка)

10) Теперь фиксируем первую транзакцию.

postgres=*# COMMIT;

COMMIT

11) Обратимся во второй транзакции еще раз.

postgres=*# SELECT xmin, xmax, * FROM a;

xmin | xmax | id

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

1571 | 0 | 100

(1 строка)

Изменения не видны. на этом уровне операторы транзакции работают только с одним снимком данных.

12) Зафиксируем вторую транзакцию.

postgres=*# COMMIT;

COMMIT

Часть 3. Состояние транзакции по CLOG

1) Откроем первую транзакцию и посмотрим после вставки состояние.

postgres=# BEGIN;

BEGIN

postgres=*# INSERT INTO a VALUES(300);

INSERT 0 1

postgres=*# SELECT xmin, xmax, * FROM a;

xmin | xmax | id

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

1571 | 0 | 100

1572 | 0 | 200

1573 | 0 | 300

(3 строки)

2) Видим вставку третьей строки. посмотрим статус транзакции:

postgres=*# SELECT pg_xact_status('1573');

pg_xact_status

----------------

in progress

(1 строка)

3) Зафиксируем транзакцию и посмотрим статус.

postgres=*# COMMIT;

COMMIT

postgres=# SELECT pg_xact_status('1573');

pg_xact_status

----------------

committed

(1 строка)

4) Теперь посмотрим как поведет себя CLOG при откате транзакции.

postgres=# BEGIN;

BEGIN

postgres=*# INSERT INTO a VALUES(400);

INSERT 0 1

postgres=*# SELECT xmin, xmax, * FROM a;

xmin | xmax | id

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

1571 | 0 | 100

1572 | 0 | 200

1573 | 0 | 300

1574 | 0 | 400

(4 строки)

postgres=*# SELECT pg_xact_status('1574');

pg_xact_status

----------------

in progress

(1 строка)

postgres=*# ROLLBACK;

ROLLBACK

postgres=# SELECT pg_xact_status('1574');

pg_xact_status

----------------

aborted

(1 строка)

postgres=*# SELECT xmin, xmax, * FROM a;

xmin | xmax | id

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

1571 | 0 | 100

1572 | 0 | 200

1573 | 0 | 300

(3 строки)

Часть 4. Блокировки таблицы

1) В первой транзакции вставим новую строку и посмотрим блокировки с помощью pg_locks, для этого нам нужен pid обслуживающего процесса.

postgres=# SELECT pg_backend_pid();

pg_backend_pid

----------------

12193

(1 строка)

2) Откроем транзакцию и обратимся к таблице.

postgres=# BEGIN;

BEGIN

postgres=*# UPDATE a SET id = id + 1;

UPDATE 3

postgres=*# SELECT locktype, transactionid, mode, relation::regclass as obj FROM pg_locks where pid = 12193;

locktype | transactionid | mode | obj

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

relation | | AccessShareLock | pg_locks

relation | | RowExclusiveLock | a

virtualxid | | ExclusiveLock |

transactionid | 1577 | ExclusiveLock |

(4 строки)

Появилась блокировка на уровне таблицы RowExclusiveLock - накладывается в случае обновления строк.

3) Во втором окне построим индекс по таблице, предварительно посмотрим pid процесса.

postgres=# SELECT pg_backend_pid();

pg_backend_pid

----------------

17210

(1 строка)

postgres=# CREATE INDEX ON a (id);

4) Транзакция подвисла. В первом терминале посмотрим, что происходит во втором процессе.

postgres=*# SELECT locktype, transactionid, mode, relation::regclass as obj FROM pg_locks where pid = 17210;

locktype | transactionid | mode | obj

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

virtualxid | | ExclusiveLock |

relation | | ShareLock | a

(2 строки)

Появилась блокировка ShareLock она не совместима RowExclusiveLock возникла блокировочная ситуация.

5) Зафиксируем первую транзакцию.

postgres=*# COMMIT;

COMMIT

6) Тут же срабатывает команда во втором окне.

CREATE INDEX

Часть 5. Блокировка строки

1) Начнем первую транзакцию:

postgres=# BEGIN;

BEGIN

postgres=*# UPDATE a SET id = id + 1 WHERE id=101;

UPDATE 1

2) Начнем вторую транзакцию:

postgres=# BEGIN;

BEGIN

postgres=*# UPDATE a SET id = id + 1 WHERE id=101;

Транзакция подвисла, сработала блокировка.

3) Зафиксируем первую транзакцию:

postgres=*# COMMIT;

COMMIT

Тут же срабатывает вторая.

UPDATE 0

postgres=*# COMMIT;

COMMIT

Обратите внимание обновление не произошло, теперь такой версии строки нет для обновления.

4) В первом терминале обратимся к таблице

postgres=# SELECT xmin, xmax, * FROM a;

xmin | xmax | id

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

1577 | 0 | 201

1577 | 0 | 301

1579 | 1580 | 102

(3 строки)

5) Удалим таблицу.

postgres=# DROP TABLE a;

DROP TABLE

Задание выполнено.

Регламентные работы

Обычная очистка таблицы
Анализ таблицы
Перестройка индекса
Полная очистка
Расширение HypoPG

Часть 1. Обычная очистка таблицы

1) Загрузим psql

astra@tantor:~$ psql

psql (16.1)

Введите "help", чтобы получить справку.

postgres=#

2) Создадим произвольную таблицу:

postgres=# CREATE TABLE a
(id integer primary key generated always as identity,
t char(2000)) WITH (autovacuum_enabled = off);

CREATE TABLE

postgres=# INSERT INTO a(t) SELECT to_char(generate_series(1,10000),'9999');

INSERT 0 10000

3) Посмотрим что получилось.

postgres=# \d a

Table "public.a"

Column | Type | Collation | Nullable | Default

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

id | integer | | not null | generated always as identity

t | character(2000) | | |

Indexes:

"a_pkey" PRIMARY KEY, btree (id)

Обратите внимание создан первичный ключ и индекс.

4) Узнаем размер таблицы и индекса в байтах:

postgres=# SELECT pg_table_size('a');

pg_table_size

---------------

20512768

(1 строка)

postgres=# SELECT pg_table_size('a_pkey');

pg_table_size

---------------

245760

(1 строка)

5) Обновим 50% строк:

postgres=# UPDATE a set t= t || 'a' where id > 5000;

UPDATE 5000

6) Посмотрим размеры объектов.

postgres=# SELECT pg_table_size('a');

pg_table_size

---------------

30752768

(1 строка)

postgres=# SELECT pg_table_size('a_pkey');

pg_table_size

---------------

360448

(1 строка)

7) Они также увеличились. Очистим таблицу и индекс:

postgres=# VACUUM a;

VACUUM

postgres=# SELECT pg_table_size('a'); SELECT pg_table_size('a_pkey');

pg_table_size

---------------

30760960

(1 строка)

pg_table_size

---------------

360448

(1 строка)

8) Размер остался таким же. Еще раз обновим строки.

postgres=# UPDATE a set t= t || 'a' where id > 5000;

UPDATE 5000

postgres=# SELECT pg_table_size('a'); SELECT pg_table_size('a_pkey');

pg_table_size

---------------

30760960

(1 строка)

pg_table_size

---------------

360448

(1 строка)

Опять размер не изменился. Произошло это потому что было использовано очищенное пространство.

9) К примеру предположим, что пропущен цикл очистки.

postgres=# UPDATE a set t= t || 'a' where id > 5000;

UPDATE 5000

postgres=# UPDATE a set t= t || 'a' where id > 5000;

UPDATE 5000

postgres=# SELECT pg_table_size('a'); SELECT pg_table_size('a_pkey');

pg_table_size

---------------

51249152

(1 строка)

pg_table_size

---------------

466944

(1 строка)

10) Размер объектов опять вырос.

postgres=# VACUUM a;

VACUUM

postgres=# SELECT pg_table_size('a'); SELECT pg_table_size('a_pkey');

pg_table_size

---------------

51249152

(1 строка)

pg_table_size

---------------

466944

(1 строка)

Даже после очистки размер не уменьшается.

Часть 2. Анализ таблицы

1) Так как произошло несколько циклов обновлений, посмотрим насколько актуальна осталась статистика. Сначала обратимся к системному каталогу.

postgres=# SELECT reltuples FROM pg_class WHERE relname='a';

reltuples

-----------

8333

(1 строка)

Получили, что в таблице у нас содержится 8333 строки.

2) Теперь обратимся к таблице.

postgres=# SELECT count(*) FROM a;

count

-------

10000

(1 строка)

3) Оказалось что строк больше. Статистика всегда приблизительна. Вызовем вторую фазу анализа.

postgres=# ANALYZE a;

ANALYZE

4) Теперь статистика стала более точной.

postgres=# SELECT reltuples FROM pg_class WHERE relname='a';

reltuples

-----------

10000

(1 строка)

Часть 3. Перестройка индекса

1) Посмотрим какой размер объектов:

postgres=# SELECT pg_table_size('a'); SELECT pg_table_size('a_pkey');

pg_table_size

---------------

51249152

(1 строка)

pg_table_size

---------------

466944

(1 строка)

2) Сейчас в таблице а один только индекс. Перестроим его.

postgres=# REINDEX TABLE a;

REINDEX

postgres=# SELECT pg_table_size('a'); SELECT pg_table_size('a_pkey');

pg_table_size

---------------

51249152

(1 строка)

pg_table_size

---------------

245760

(1 строка)

3) Размер индекса уменьшился, размер таблицы остался неизменным

Часть 4. Полная очистка

postgres=# VACUUM FULL a;

VACUUM

1) Посмотрим размер объектов.

postgres=# SELECT pg_table_size('a'); SELECT pg_table_size('a_pkey');

pg_table_size

---------------

20488192

(1 строка)

pg_table_size

---------------

245760

(1 строка)

Размер таблицы уменьшился.

2) Удалим таблицу.

postgres=# DROP TABLE a;

DROP TABLE

Задание выполнено.

Часть 5. Расширение HypoPG

1) Установите расширение hypopg:

postgres=# CREATE EXTENSION hypopg;

CREATE EXTENSION

2) Создайте таблицу с тестовыми данными:

postgres=# CREATE TABLE hypo AS SELECT id, 'line ' || id AS val FROM generate_series(1,10000) id;

SELECT 10000

3) План выполнения выборки одной строки - последовательное сканирование (Seq Scan). Индексных методов доступа нет, так как нет индексов:

postgres=# EXPLAIN SELECT * FROM hypo WHERE id = 1;

QUERY PLAN

--------------------------------------------------------

Seq Scan on hypo (cost=0.00..165.60 rows=41 width=36)

Filter: (id = 1)

(2 строки)

Почему ожидаемое количество строк 41, а не 1? Нет статистики.

4) Соберите статистику:

postgres=# vacuum analyze hypo;

VACUUM

postgres=# EXPLAIN SELECT * FROM hypo WHERE id = 1;

QUERY PLAN

-------------------------------------------------------

Seq Scan on hypo (cost=0.00..180.00 rows=1 width=13)

Filter: (id = 1)

(2 строки)

Ожидаемое количество строк 1.

Дана задача оптимизировать выполнение этого запроса. Предполагаем, что индекс по столбцу id ускорит выполнение запроса. Нужно убедиться, что планировщик будет использовать индекс. Если планировщик не будет использовать индекс, то предположение неверно и индекс создавать не нужно. Создание индекса трудоемко и долго, он занимает место. Перед созданием индекса мы хотим проверить гипотезу о том, что планировщик его будет использовать при выполнении оптимизируемого запроса.

5) Для проверки гипотезы создайте гипотетический индекс:

postgres=# SELECT * FROM hypopg_create_index('CREATE INDEX hypo_idx ON hypo (id)');

indexrelid | indexname

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

13495 | <13495>btree_hypo_id

(1 строка)

Имя гипотетического индекса генерируется автоматически, это нормально.

Реальный индекс не создаётся, команда выполняется моментально.

6) Посмотрите список гипотетических индексов:

postgres=# SELECT * FROM hypopg_list_indexes;

indexrelid | index_name | schema_name | table_name | am_name

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

13495 | <13495>btree_hypo_id | public | hypo | btree

(1 строка)

Какой план выполнения сейчас?

7) Выполните команду:

postgres=# EXPLAIN SELECT * FROM hypo WHERE id = 1;

QUERY PLAN

--------------------------------------------------------------------------

Index Scan using "<13495>btree_hypo_id" on hypo (cost=0.04..8.05 rows=1 width=13)

Index Cond: (id = 1)

(2 строки)

План показывает, что индекс будет использоваться.

Реального индекса нет, поэтому план реального выполнения использует сканирование таблицы:

postgres=# EXPLAIN (analyze) SELECT * FROM hypo WHERE id = 1;

QUERY PLAN

----------------------------------------------------------------------------

Seq Scan on hypo (cost=0.00..180.00 rows=1 width=13) (actual time=0.025..0.875 rows=1 loops=1)

Filter: (id = 1)

Rows Removed by Filter: 9999

Planning Time: 0.077 ms

Execution Time: 1.074 ms

(5 строк)

8) Создайте реальный индекс:

postgres=# CREATE UNIQUE INDEX hypo_id ON hypo(id);

CREATE INDEX

План выполнения остался прежним:

postgres=# EXPLAIN SELECT * FROM hypo WHERE id = 1;

QUERY PLAN

------------------------------------------------------------------------

Index Scan using "<13495>btree_hypo_id" on hypo (cost=0.04..8.05 rows=1 width=13)

Index Cond: (id = 1)

(2 строки)

9) Уберите побочные эффекты:

postgres=# SELECT hypopg_reset();

hypopg_reset

--------------

(1 строка)

Планировщик стал использовать созданный индекс:

postgres=# EXPLAIN SELECT * FROM hypo WHERE id = 1;

QUERY PLAN

---------------------------------------------------------------------

Index Scan using hypo_id on hypo (cost=0.29..8.30 rows=1 width=13)

Index Cond: (id = 1)

(2 строки)

Расширение позволяет скрывать от планировщика реальные индексы:

postgres=# SELECT hypopg_hide_index('hypo_id'::regclass);

hypopg_hide_index

-------------------

(1 строка)

Скрытие действует только в пределах сессии и на работу других сессий не оказывает влияние.

Гипотетические индексы также существуют только в рамках сессии.

Гипотетические индексы при этом исчезают:

postgres=# SELECT * FROM hypopg_list_indexes;

indexrelid | index_name | schema_name | table_name | am_name

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

(0 строк)

План выполнения будет использовать последовательное сканирование:

postgres=# EXPLAIN SELECT * FROM hypo WHERE id = 1;

QUERY PLAN

-------------------------------------------------------

Seq Scan on hypo (cost=0.00..180.00 rows=1 width=13)

Filter: (id = 1)

(2 строки)

Есть представление со списком скрытых в данной сессии индексов:

postgres=# SELECT * FROM hypopg_hidden_indexes;

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

(1 строка)

10) Убедитесь, что скрытие индексов и гипотетические индексы существуют только в рамках сессии:

postgres=# SELECT * FROM hypopg_create_index('CREATE INDEX hypo_idx ON hypo (id)');

indexrelid | indexname

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

13495 | <13495>btree_hypo_id

(1 строка)

postgres=# SELECT * FROM hypopg_list_indexes;

indexrelid | index_name | schema_name | table_name | am_name

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

13495 | <13495>btree_hypo_id | public | hypo | btree

(1 строка)

postgres=# \q

postgres@tantor:~$ psql

psql (16.1)

11) Введите «help», чтобы получить справку.

postgres=# SELECT * FROM hypopg_list_indexes;

indexrelid | index_name | schema_name | table_name | am_name

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

(0 строк)

postgres=# SELECT * FROM hypopg_hidden_indexes;

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

(0 строк)

Выполнение запросов

Создание объектов для запросов
Извлечение данных последовательно
Возвращение данных по индексу
Низкая селективность
Использование статистики
Представление pg_stat_statements

Часть 1. Создание объектов для запросов

1) Загрузим psql:

astra@tantor:~$ psql

psql (16.1)

Введите "help", чтобы получить справку.

postgres=#

2) Создадим новую таблицу и заполним данными.

postgres=# CREATE TABLE test (col1 integer, col2 integer, name text);

CREATE TABLE

postgres=# INSERT INTO test VALUES (1,2,'test1');

INSERT 0 1

postgres=# INSERT INTO test VALUES (3,4,'test2');

INSERT 0 1

3) Создадим представление над таблицей.

postgres=# CREATE VIEW v_table AS

SELECT * FROM test;

CREATE VIEW

postgres=# SELECT col1, col2 FROM v_table WHERE name='test1'::text ;

col1 | col2

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

1 | 2

(1 строка)

Часть 2. Извлечение данных последовательно

1) C помощью команды Explain посмотрим план выполнения запроса.

postgres=# EXPLAIN

SELECT col1, col2 FROM v_table WHERE name='test1'::text

QUERY PLAN

-----------------------------------------------------

Seq Scan on test (cost=0.00..25.00 rows=6 width=8)

Filter: (name = 'test1'::text)

(2 строки)

Видим, что использовалось последовательное чтение таблицы test. то есть представление было раскрыто, данные извлечены непосредственно с таблицы.

2) Применим параметры analyze и buffers. Они показывают что запрос был выполнен реально и какое количество страниц было затронуто.

postgres=# EXPLAIN(analyze, buffers, costs off, timing off)

SELECT col1, col2 FROM v_table WHERE name='test1'::text ;

QUERY PLAN

------------------------------------------

Seq Scan on test (actual rows=1 loops=1)

Filter: (name = 'test1'::text)

Rows Removed by Filter: 1

Buffers: shared read=1

Planning Time: 0.063 ms

Execution Time: 9.569 ms

(6 строк)

Часть 3. Возвращение данных по индексу

1) Создадим индекс по столбцу col1.

postgres=# CREATE INDEX ON test (col1);

CREATE INDEX

postgres=# \d test

Таблица "public.test"

Столбец | Тип | Правило сортировки | Допустимость NULL | По умолчанию

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

col1 | integer | | |

col2 | integer | | |

name | text | | |

Индексы:

"test_col1_idx" btree (col1)

2) Можно убедится, что формирование имени индекса производится автоматически,

добавим информации к таблице.

postgres=# INSERT INTO test(col1,col2)

SELECT generate_series(3,1003), generate_series(4,1004);

INSERT 0 1001

3) Посмотрим, что получится если будем выбирать малое количество строк. То есть, случай когда будет высокая селективность и маленькая кардинальность.

postgres=# EXPLAIN(analyze, buffers, costs off, timing off)

SELECT col1, col2 FROM test WHERE col1<20;

QUERY PLAN

-----------------------------------------------------------------

Index Scan using test_col1_idx on test (actual rows=19 loops=1)

Index Cond: (col1 < 20)

Buffers: shared hit=3

Planning:

Buffers: shared hit=17

Planning Time: 0.179 ms

Execution Time: 0.117 ms

(7 строк)

Убедились, что используется индексный доступ.

Часть 4. Низкая селективность

Теперь отберем большое количество строк.

postgres=# SELECT count(*) FROM test;

count

-------

1003

(1 строка)

Всего строк 1003

postgres=# EXPLAIN(analyze, buffers, costs off, timing off)

SELECT col1, col2 FROM test WHERE col1>20;

QUERY PLAN

--------------------------------------------

Seq Scan on test (actual rows=983 loops=1)

Filter: (col1 > 20)

Rows Removed by Filter: 20

Buffers: shared hit=5

Planning:

Buffers: shared hit=3

Planning Time: 0.157 ms

Execution Time: 0.201 ms

(8 строк)

Отобрано 983 строки. что означает низкая селективность и высокая кардинальность.

Убедились, что в этом случае индексный доступ становится дорогим и СУБД переходит к последовательному доступу.

Часть 5. Использование статистики

К примеру, при заполнении таблицы test третий столбец был не заполнен. Давайте посмотрим какой процент будет иметь значение NULL

Пересоберем статистику.

postgres=# ANALYZE test;

ANALYZE

postgres=# SELECT stanullfrac FROM pg_statistic WHERE starelid = 'test'::regclass AND staattnum = 3;

stanullfrac

-------------

0.9981884

(1 row)

Как видно из таблицы pg_statistic 99%

Часть 6. Представление pg_stat_statements

1) Убедимся что представление установлено.

postgres=# \dx pg_stat_statements

Список установленных расширений

Имя | Версия | Схема | Описание

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

pg_stat_statements| 1.10 | public | track planning and execution statistics of all SQL statements executed

(1 строка)

2) Посмотрим, какие столбцы есть в представлении.

postgres=# \d pg_stat_statements

Представление "public.pg_stat_statements"

Столбец | Тип | Правило сортировки | Допустимость NULL | По умолчанию

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

userid | oid | | |

dbid | oid | | |

toplevel | boolean | | |

queryid | bigint | | |

query | text | | |

plans | bigint | | |

total_plan_time | double precision | | |

min_plan_time | double precision | | |

max_plan_time | double precision | | |

mean_plan_time | double precision | | |

stddev_plan_time | double precision | | |

calls | bigint | | |

total_exec_time | double precision | | |

min_exec_time | double precision | | |

max_exec_time | double precision | | |

mean_exec_time | double precision | | |

stddev_exec_time | double precision | | |

rows | bigint | | |

shared_blks_hit | bigint | | |

shared_blks_read | bigint | | |

shared_blks_dirtied | bigint | | |

shared_blks_written | bigint | | |

local_blks_hit | bigint | | |

local_blks_read | bigint | | |

local_blks_dirtied | bigint | | |

local_blks_written | bigint | | |

temp_blks_read | bigint | | |

temp_blks_written | bigint | | |

blk_read_time | double precision | | |

blk_write_time | double precision | | |

temp_blk_read_time | double precision | | |

temp_blk_write_time | double precision | | |

wal_records | bigint | | |

wal_fpi | bigint | | |

wal_bytes | numeric | | |

jit_functions | bigint | | |

jit_generation_time | double precision | | |

jit_inlining_count | bigint | | |

jit_inlining_time | double precision | | |

jit_optimization_count | bigint | | |

jit_optimization_time | double precision | | |

jit_emission_count | bigint | | |

jit_emission_time | double precision | | |

3) Сбросим статистику применения представления.

postgres=# SELECT pg_stat_statements_reset();

pg_stat_statements_reset

--------------------------

(1 строка)

4) Обратимся к таблице test.

postgres=# EXPLAIN (analyze)

SELECT col1, col2 FROM test WHERE col1>20;

QUERY PLAN

--------------------------------------------------------------------------------------------

Seq Scan on test (cost=0.00..17.54 rows=984 width=8) (actual time=0.022..0.132 rows=983 loops=1)

Filter: (col1 > 20)

Rows Removed by Filter: 20

Planning Time: 0.190 ms

Execution Time: 0.234 ms

(5 строк)

5) С помощью представления pg_stat_statements посмотрим сколько времени заняло выполнение запроса и сколько страниц было задействовано.

postgres=# SELECT queryid, substring(query FOR 100) as query, total_exec_time as ms, shared_blks_hit as blocks

from pg_stat_statements

WHERE query LIKE '%col1, col2%';

queryid | query | ms | blocks

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

-3250261183448805182 | EXPLAIN (analyze) +| 0.491265 | 11

| SELECT col1, col2 FROM test WHERE col1>$1 | |

(1 строка)

Расширяемость

Определение директории с файлами расширения
Просмотр установленных расширений
Просмотр доступных расширений
Установка и удаление произвольного обновления
Просмотр доступных версий расширений. Обновление до актуальной версии
Обертки внешних данных

Часть 1. Определение директории с файлами расширения

1) Перейдем под пользователя postgres.

astra@education:~$ sudo su - postgres

2) В командной строке воспользуемся утилитой pg_config.

postgres@education:~$ pg_config --sharedir

/opt/tantor/db/16/share/postgresql

3) Добавим к получившемуся пути extension.

postgres@education:~$ ls -l /opt/tantor/db/16/share/postgresql/extension/

итого 9768

-rw-r--r-- 1 root root 274 Apr 18 2023 adminpack--1.0--1.1.sql

-rw-r--r-- 1 root root 1535 Apr 18 2023 adminpack--1.0.sql

-rw-r--r-- 1 root root 1682 Apr 18 2023 adminpack--1.1--2.0.sql

-rw-r--r-- 1 root root 595 Apr 18 2023 adminpack--2.0--2.1.sql

-rw-r--r-- 1 root root 176 Apr 18 2023 adminpack.control

…………

………

4) Загрузим psql.

postgres@tantor:~$ psql

psql (16.1)

Введите "help", чтобы получить справку.

postgres=#

5) Определим путь расширения с помощью функции pg_config().

postgres=# SELECT setting FROM pg_config()

where name = 'SHAREDIR';

setting

------------------------------------

/opt/tantor/db/16/share/postgresql

(1 row)

Часть 2. Просмотр установленных расширений

postgres=# \dx

Список установленных расширений

Имя | Версия | Схема | Описание

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

pg_stat_statements | 1.10 | public | track planning and execution statistics of all SQL statements executed

pg_store_plans | 1.6.4 | public | track plan statistics of all SQL statements executed

plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language

(3 строки)

Часть 3. Просмотр доступных расширений

Воспользуемся расширением pg_available_extensions.

postgres=# SELECT * from pg_available_extensions;

name | default_version | installed_version | comment

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

plpgsql | 1.0 | 1.0 | PL/pgSQL procedural language

page_repair | 1.0 | | Individual page reparing

pg_hint_plan | 1.6.0 | |

dblink | 1.2 | | connect to other PostgreSQL databases from within a database

tcn | 1.0 | | Triggered change notifications

pg_trgm | 1.6 | | text similarity measurement and index searching based on trigrams

pg_buffercache | 1.4 | | examine the shared buffer cache

-----------------

dict_xsyn | 1.0 | | text search dictionary template for extended synonym processing

pg_variables | 1.2 | | session variables with various types

old_snapshot | 1.0 | | utilities in support of old_snapshot_threshold

pgcrypto | 1.3 | | cryptographic functions

file_fdw | 1.0 | | foreign-data wrapper for flat file access

amcheck | 1.3 | | functions for verifying relation integrity

seg | 1.4 | | data type for representing line segments or floating-point intervals

pg_background | 1.2 | | Run SQL queries in the background

(69 строк)

На данный момент установлено 69 расширений в текущем кластере БД. Их можно установить в любой БД.

Часть 4. Установка и удаление произвольного обновления

1) Например, установим расширение pg_surgery.

postgres=# CREATE EXTENSION pg_surgery;

CREATE EXTENSION

postgres=# \dx

Список установленных расширений

Имя | Версия | Схема | Описание

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

pg_stat_statements | 1.10 | public | track planning and execution statistics of all SQL statements executed

pg_store_plans | 1.6.4 | public | track plan statistics of all SQL statements executed

pg_surgery | 1.0 | public | extension to perform surgery on a damaged relation

plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language

(4 строки)

2) Посмотрим содержимое расширения.

postgres=# \dx+ pg_surgery

Объекты в расширении "pg_surgery"

Описание объекта

-------------------------------------------

функция heap_force_freeze(regclass,tid[])

функция heap_force_kill(regclass,tid[])

(2 строки)

3) Удалим расширение.

postgres=# DROP EXTENSION pg_surgery;

DROP EXTENSION

Часть 5. Просмотр доступных версий расширений. Обновление до актуальной версии

1) Воспользуемся представлением pg_available_extension_versions

postgres=# SELECT name, version FROM pg_available_extension_versions WHERE name = 'adminpack';

name | version

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

adminpack | 1.0

adminpack | 1.1

adminpack | 2.0

adminpack | 2.1

(4 строки)

2) Для начала установим версию 1.0

postgres=# CREATE EXTENSION adminpack VERSION '1.0';

CREATE EXTENSION

postgres=# \dx adminpack

Список установленных расширений

Имя | Версия | Схема | Описание

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

adminpack | 1.0 | pg_catalog | administrative functions for PostgreSQL

(1 строка)

3) Посмотрим содержимое расширения.

postgres=# \dx+ adminpack

Объекты в расширении "adminpack"

Описание объекта

------------------------------------------

функция pg_file_length(text)

функция pg_file_read(text,bigint,bigint)

функция pg_file_rename(text,text)

функция pg_file_rename(text,text,text)

функция pg_file_unlink(text)

функция pg_file_write(text,text,boolean)

функция pg_logdir_ls()

функция pg_logfile_rotate()

(8 строк)

4) Посмотрим, можно ли расширение обновить до версии 2.1. Воспользуемся функцией pg_extension_update_paths.

postgres=# SELECT * FROM pg_extension_update_paths('adminpack');

source | target | path

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

1.0 | 1.1 | 1.0--1.1

1.0 | 2.0 | 1.0--1.1--2.0

1.0 | 2.1 | 1.0--1.1--2.0--2.1

1.1 | 1.0 |

1.1 | 2.0 | 1.1--2.0

1.1 | 2.1 | 1.1--2.0--2.1

2.0 | 1.0 |

2.0 | 1.1 |

2.0 | 2.1 | 2.0--2.1

2.1 | 1.0 |

2.1 | 1.1 |

2.1 | 2.0 |

(12 строк)

5) Обновим расширение до версии 2.1.

postgres=# ALTER EXTENSION adminpack UPDATE;

ALTER EXTENSION

postgres=# \dx adminpack

Список установленных расширений

Имя | Версия | Схема | Описание

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

adminpack | 2.1 | pg_catalog | administrative functions for PostgreSQL

(1 строка)

postgres=# \dx+ adminpack

Объекты в расширении "adminpack"

Описание объекта

------------------------------------------

функция pg_file_rename(text,text)

функция pg_file_rename(text,text,text)

функция pg_file_sync(text)

функция pg_file_unlink(text)

функция pg_file_write(text,text,boolean)

функция pg_logdir_ls()

(6 строк)

Как видите, содержимое расширения изменилось.

6) Удалим расширение.

postgres=# DROP EXTENSION adminpack;

DROP EXTENSION

Часть 6. Обертки внешних данных

1) Посмотрим, какие есть обертки внешних данных (FDW).

postgres=# SELECT * FROM pg_available_extensions

WHERE name LIKE '%fdw%';

name | default_version | installed_version | comment

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

postgres_fdw | 1.1 | | foreign-data wrapper for remote PostgreSQL servers

file_fdw | 1.0 | | foreign-data wrapper for flat file access

(2 строки)

2) Воспользуемся оберткой внешней данных для подключения к СУБД PostgreSQL.

postgres=# CREATE EXTENSION postgres_fdw;

CREATE EXTENSION

postgres=# \dx postgres_fdw

Список установленных расширений

Имя | Версия | Схема | Описание

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

postgres_fdw | 1.1 | public | foreign-data wrapper for remote PostgreSQL servers

(1 строка)

3) Посмотрим какие есть базы данных.

postgres=# \l

Список баз данных

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

(4 строки)

4) Подключимся и вернем информацию с БД test_db. Для начала создадим объект удаленного сервера.

postgres=# CREATE SERVER test FOREIGN DATA WRAPPER postgres_fdw

OPTIONS (host 'localhost', port '5432', dbname 'test_db');

CREATE SERVER

postgres=# \des

Список сторонних серверов

Имя | Владелец | Обёртка сторонних данных

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

test | postgres | postgres_fdw

(1 строка)

5) После этого создадим пользователя, под которым будет происходить подключение. Отображений на пользователя может быть несколько.

postgres=# CREATE USER MAPPING FOR postgres SERVER test

OPTIONS ( user 'postgres', password 'postgres' );

CREATE USER MAPPING

postgres=# \deu

Список сопоставлений пользователей

Сервер | Имя пользователя

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

test | postgres

(1 строка)

6) После, создадим таблицу, к которой можно будет подключится.

postgres=# CREATE FOREIGN TABLE order_remote

( id bigint, name varchar(32))

server test

OPTIONS ( schema_name 'public', table_name 'order_items_1'

);

CREATE FOREIGN TABLE

postgres=# \det

Список сторонних таблиц

Схема | Таблица | Сервер

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

public | order_remote | test

(1 строка)

7) Обращаемся к этой таблице, как к обычной таблице.

postgres=# SELECT * FROM order_remote LIMIT 10;

id | name

----+------

0 |

1 |

2 |

3 |

4 |

5 |

6 |

7 |

8 |

9 |

(10 строк)

8) Описание удаленной таблицы можно получить, как обычной.

postgres=# \d order_remote

Сторонняя таблица "public.order_remote"

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

id | bigint | | | |

Сервер: test

Параметр ОСД: (schema_name 'public', table_name 'order_items_1')

9) Посмотрим откуда приходят данные.

postgres=# EXPALIN SELECT * FROM order_remote LIMIT 10;

QUERY PLAN

----------------------------------------------------------------------

Foreign Scan on order_remote (cost=100.00..100.42 rows=10 width=90)

(1 строка)

10) Очистим базу данных.

postgres=# DROP FOREIGN TABLE order_remote;

DROP FOREIGN TABLE

postgres=# DROP USER MAPPING FOR postgres server test;

DROP USER MAPPING

postgres=# DROP SERVER test;

DROP SERVER

postgres=# DROP EXTENSION postgres_fdw;

DROP EXTENSION

Раздел 3. Конфигурирование

Просмотр параметров конфигурации

1) Посмотрим названия столбцов в представление со списком конфигурационных параметров:

postgres=# \d pg_settings

Представление "pg_catalog.pg_settings"

Столбец | Тип | Правило сортировки | Допустимость NULL | По

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

name | text | | |

setting | text | | |

unit | text | | |

category | text | | |

short_desc | text | | |

extra_desc | text | | |

context | text | | |

vartype | text | | |

source | text | | |

min_val | text | | |

max_val | text | | |

enumvals | text[] | | |

boot_val | text | | |

reset_val | text | | |

sourcefile | text | | |

sourceline | integer | | |

pending_restart | boolean | | |

2) Количество параметров в текущей версии:

postgres=# select count(*) from pg_settings;

count

-------

404

(1 строка)

Около 400 параметров, включая параметры загруженных (параметр shared_preload_libraries) библиотек («модулей»).

3) Посмотрим какие типы значений параметров есть:

postgres=# select distinct unit, vartype from pg_settings order by unit;

unit | vartype

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

8kB | int64

8kB | integer

B | integer

kB | integer

MB | integer

min | integer

ms | integer

ms | real

s | integer

| string

| bool

| enum

| int64

| integer

| real

(15 строк)

4) Есть параметры с единицами измерения и без.

Посмотрим сколько параметров каждого типа есть:

postgres=# select unit, vartype, count(*) from pg_settings group by unit, vartype order by 3;

unit | vartype | count

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

8kB | int64 | 1

ms | real | 2

min | integer | 3

MB | integer | 6

B | integer | 6

| int64 | 8

s | integer | 10

kB | integer | 12

8kB | integer | 17

ms | integer | 23

| real | 24

| enum | 44

| integer | 58

| string | 68

| bool | 122

(15 строк)

5) Посмотрим какие два параметра измеряются по умолчанию в долях миллисекунд:

postgres=# select name, setting from pg_settings where unit='ms' and vartype='real';

name | setting

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

autovacuum_vacuum_cost_delay | 2

vacuum_cost_delay | 0

(2 строки)

Это параметры, настраивающие задержку в работе процессов вакуумирования

6) Есть параметры типа enum. Посмотрим какие значения бывают для параметров этого типа:

postgres=# select distinct enumvals from pg_settings;

enumvals

-----------------------------------------------------------

{local,remote_write,remote_apply,on,off}

{md5,scram-sha-256}

{none,pl,all}

{pause,promote,shutdown}

{none,top,all}

{postgres,postgres_verbose,sql_standard,iso_8601}

{debug5 ... debug1,log,notice,warning,error}

{sysv,mmap}

{origin,replica,local}

{always,on,off}

{TLSv1,TLSv1.1,TLSv1.2,TLSv1.3}

{serializable,"repeatable read","read committed","read uncommitted"}

{disabled,debug5..debug1,log,notice,warning,error}

{auto,force_generic_plan,force_custom_plan}

{content,document}

{pglz,lz4}

{local0,local1,local2,local3,local4,local5,local6,local7}

{none,ddl,mod,all}

{none,top,all,verbose}

{text,xml,json,yaml}

{none,cache,snapshot}

{off,on,try}

{"",TLSv1,TLSv1.1,TLSv1.2,TLSv1.3}

{minimal,replica,logical}

{fsync,syncfs}

{auto,regress,on,off}

{shmem,file}

{safe_encoding,on,off}

{buffered,immediate}

{debug5 ... 1,info,notice,warning,error,log,fatal,panic}

{terse,default,verbose}

{debug5..debug1,info,notice,warning,log}

{base64,hex}

{posix,sysv,mmap}

{raw,text,json,yaml,xml}

{partition,on,off}

{fsync,fdatasync,open_sync,open_datasync}

{off,on,regress}

{pglz,lz4,zstd,on,off}

{escape,hex}

(41 строка)

Используются, в основном, английские слова, обозначающие названия технологий и алгоритмов. Например, алгоритмов сжатия pglz,lz4,zstd.

7) Какие контексты параметров есть:

postgres=# select distinct context from pg_settings;

context

-------------------

postmaster

superuser-backend

user

internal

backend

sighup

superuser

(7 строк)

Контекст указывает можно ли изменить значение параметра, если можно, то каким образом.

8) Параметры расширений и библиотек имеют в названии точку.

Посмотрим параметр plpgsql.variable_conflict:

postgres=# show plpgsql.variable_conflict;

ERROR: unrecognized configuration parameter "plpgsql.variable_conflict"

Параметр неизвестен. Неизвестные параметры можно устанавливать в postgresql.conf, но не командой ALTER SYSTEM.

9) Загрузим библиотеку расширения («модуль»). Апострофы в строковых параметрах обязательны:

postgres=# load 'plpgsql';

LOAD

postgres=# show plpgsql.variable_conflict;

plpgsql.variable_conflict

---------------------------

error

(1 строка)

10) Посмотрим, какие параметры конфигурации были зарегистрированы при загрузке модуля:

postgres=# show plpgsql.<TAB><TAB>

plpgsql.check_asserts plpgsql.extra_errors plpgsql.extra_warnings plpgsql.print_strict_params plpgsql.variable_conflict

также можно посмотреть командой:

postgres=# \dconfig plpgsql.*

Список параметров конфигурации

Параметр | Значение

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

plpgsql.check_asserts | on

plpgsql.extra_errors | none

plpgsql.extra_warnings | none

plpgsql.print_strict_params | off

plpgsql.variable_conflict | error

(5 строк)

Раздел 4. Базы данных

Логическая структура кластера

Просмотр списка баз данных кластера
Создание базы данных
Переименование базы данных
Ограничение на соединение с базой
Форматирование вывода psql

Часть1. Просмотр списка баз данных кластера

1) Запустим стандартно поставляемую утилиту oid2name:

postgres@tantor:~$ oid2name

All databases:

Oid Database Name Tablespace

----------------------------------

5 postgres pg_default

4 template0 pg_default

1 template1 pg_default

16439 test_db pg_default

Утилита, запущенная без параметров выдаёт список баз данных; название табличного пространства по умолчанию для каждой из баз данных; oid базы данных, который соответствует поддиректории в директории табличного пространства.

2) Подключимся к экземпляру:

postgres@tantor:~$ psql

psql (16.1)

Введите "help", чтобы получить справку.

3) Посмотрим список баз командой \l:

postgres=# \l

List of databases

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

(4 строки)

4) Посмотрим что нам выдаст команда \l если добавить символ "+" означающий дополнительные данные:

postgres=# \l+

List of databases

| Tablespace | Description

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

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

B | pg_default | default administrative connection database

B | pg_default | unmodifiable empty database

| |

B | pg_default | default template for new databases

| |

| pg_default |

(4 rows)

Что добавилось?

Добавились столбцы с размером, табличным пространством по умолчанию, описанием.

5) Посмотрим список баз данных командой SELECT:

postgres=# SELECT datname FROM pg_database;

datname

-----------

postgres

test_db

template1

template0

(4 rows)

Часть 2. Создание базы данных

6) Создадим базу данных командой SQL:

postgres=# CREATE DATABASE db01;

CREATE DATABASE

Показать, что можно стрелкой вверх на клавиатуре повторить предыдущие команды и убедиться, что новая база данных выводится.

На основе какой шаблонной базы была создана база данных db01?

На основе template1.

Часть 3. Переименование базы данных

7) Переименуем базу:

postgres=# ALTER DATABASE db01 RENAME TO db02;

ALTER DATABASE

8) Убедимся что можем подсоединиться к базе db02:

postgres=# \c db02

Вы подключены к базе данных "db02" как пользователь "postgres".

db02=# \c postgres

Вы подключены к базе данных "postgres" как пользователь "postgres".

Помните о том, что нажимая клавишу табуляции <TAB> можно завешать команды.

Часть 4. Ограничение на соединение с базой

9) Установим максимальное число подсоединений в ноль:

postgres=# ALTER DATABASE db02 CONNECTION LIMIT 0;

ALTER DATABASE

10) Как пользователь с атрибутом SUPERUSER мы можем подсоединиться:

postgres=# \c db02

Вы подключены к базе данных "db02" как пользователь "postgres".

db02=# \c postgres

Вы подключены к базе данных "postgres" как пользователь "postgres".

11) Воспользуемся свойством базы данных ALLOW_CONNECTIONS:

postgres=# ALTER DATABASE db02 ALLOW_CONNECTIONS false;

ALTER DATABASE

postgres=# \c db02

подключиться к серверу через сокет "/var/run/postgresql/.s.PGSQL.5432" не удалось: FATAL: database "db02" is not currently accepting connections

Сохранено предыдущее подключение

теперь мы не можем подсоединиться.

Часть 5. Форматирование вывода psql

12) Посмотрим содержимое файла параметров psql:

postgres=# \! cat .psqlrc

\setenv PAGER 'less -XS'

\setenv PSQL_EDITOR /usr/bin/mcedit

13) В виртуальной машине для курса установили эти параметры. По умолчанию файл отсутсвует. Установим значение переменной в значение по умолчанию и посмотрим как будет выдаваться результат:

postgres=# \setenv PAGER 'more'

14) посмотрим список встроенных функций, которые полезны для администрирования:

postgres=# \dfS pg*

результат нечитаемый

15) настроим вывод и повторим:

postgres=# \pset format wrapped

postgres=# \dfS pg*

List of functions

Schema | Name | Result data type | Argument data types | Type

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

pg_catalog | pg_advisory_lock | void | bigint | func

pg_catalog | pg_advisory_lock | void | integer, integer | func

pg_catalog | pg_advisory_lock_shared | void | bigint | func

pg_catalog | pg_advisory_lock_shared | void | integer, integer | func

Отображение поменялось. При использовании утилиты more клавишами <PgUp> и <PgDown> нельзя пользоваться.

16) Вернем формат в значение по умолчанию:

postgres=# \pset format aligned

Формат вывода: aligned.

Вернем переменную, задающую программу постраничного вывода вместо использовавшейся утилиты "more":

postgres=# \setenv PAGER 'less -XS'

17) Повторим и убедимся что вывод стал читаемым можно использовать клавиши <PgUp> и <PgDown>:

postgres=# \dfS pg*

Нажмите клавиши <PgDn> клавишу <h> обратите внимание на то что высветилась подсказка по команде less, прочтите что для выхода из режима помощи можно нажать клавишу "q" и нажмите ее два раза <q><q>

18) Удалите созданную базу данных:

postgres=# drop database db02;

DROP DATABASE

Физическая структура кластера

Директория для временных файлов
Перемещение директории табличного пространства

Часть 1. Директория для временных файлов

1) Запустим стандартно поставляемую утилиту oid2name:

postgres@tantor:~$ oid2name

All databases:

Oid Database Name Tablespace

----------------------------------

5 postgres pg_default

4 template0 pg_default

1 template1 pg_default

16439 test_db pg_default

Утилита, запущенная без параметров выдаёт список баз данных, название табличного пространства по умолчанию, oid базы данных, который соответствует поддиректории в директории табличного пространства.

Посмотрим какие директории есть в табличном пространстве pg_default:

postgres@tantor:~$ ls --color -w 1 $PGDATA/base

16439

pgsql_tmp

2) Зачем нужна директория pgsql_tmp?

Это поддиректория для временных файлов, которая создаётся в директории табличного пространства.

Для временных файлов лучше использовать отдельное табличное пространство, которое стоит отдельно создать.

Как устанавливается табличное пространство для временных файлов?

Параметром конфигурации temp_tablespaces

Дожидаться ответа слушателей не нужно, достаточно чтобы они задались вопросом в целях запоминания.

3) Перейдите в директорию $PGDATA и удобными средствами (mc) покажите директории и поддиректории и дайте короткий обзор что хранится в директориях и файлах

Часть 2. Перемещение директории табличного пространства

1) Создадим табличное пространство. Для этого создадим директорию:

postgres@tantor:~$ mkdir $PGDATA/u01

2) Проверим что пользователь postgres может читать-писать в неё:

postgres@tantor:~$ ls -al $PGDATA/u01

total 8

drwxr-xr-x 2 postgres postgres 4096 Mar 10 10:37 .

drwxr-x--- 20 postgres postgres 4096 Mar 10 10:37 ..

3) Запустим psql:

postgres@tantor:~$ psql

psql (16.1)

Введите "help", чтобы получить справку.

4) Попытаемся создать табличное пространство:

postgres=# CREATE TABLESPACE u01tbs LOCATION 'u01';

ERROR: tablespace location must be an absolute path

Относительный путь не подходит, нужно указать абсолютный.

5) Укажем:

postgres=# CREATE TABLESPACE u01tbs LOCATION '/var/lib/postgresql/tantor-se-16/data/u01';

WARNING: tablespace location should not be inside the data directory

CREATE TABLESPACE

Табличное пространство создано, но выдано предупреждение, что не стоит директорию u01 располагать в PGDATA. Также не стоит располагать и другие директории (например, логирования), чтобы они с большим количеством ненужных файлов не попали в бэкап.

6) Создадим в табличном пространстве таблицу:

postgres=# CREATE TABLE t (id bigserial, t text) TABLESPACE u01tbs;

CREATE TABLE

7) Во втором окне терминала покажите, что появилось три файла, один из них размером 8192 байт, другие нулевого размера:

Перейдите в директорию табличного пространства и поддиректорию с oid базы данных:

postgres@tantor:~$ cd $PGDATA/u01/PG_16_202307071/5

postgres@tantor:~/tantor-se-16/data/u01/PG_16_202307071/5$ ls -l -w 1

итого 8

-rw-r----- 1 postgres postgres 0 374240

-rw-r----- 1 postgres postgres 0 374244

-rw-r----- 1 postgres postgres 8192 374245

Что это за файлы?

Это файл основного слоя таблицы t, основной слой её TOAST таблицы и TOAST индекса.

TOAST таблица и индекс были созданы автоматически, так как есть столбец типа text.

8) Проверим к чему относится какой файл:

postgres@tantor:~/tantor-se-16/data/u01/PG_16_202307071/5$ oid2name -f 374240

From database "postgres":

Filenode Table Name

----------------------

374240 t

postgres@tantor:~/tantor-se-16/data/u01/PG_16_202307071/5$ oid2name -f 374244

From database "postgres":

Filenode Table Name

---------------------------

374244 pg_toast_374240

postgres@tantor:~/tantor-se-16/data/u01/PG_16_202307071/5$ oid2name -f 374245

From database "postgres":

Filenode Table Name

---------------------------------

374245 pg_toast_374240_index

8-килобайтный файл относится к индексу.

9) Перенесем директорию с остановкой экземпляра:

postgres@tantor:~/tantor-se-16/data/u01/PG_16_202307071/5$ cd $PGDATA

postgres@tantor:~/tantor-se-16/data$ pg_ctl stop

waiting for server to shut down.... done

server stopped

postgres@tantor:~/tantor-se-16/data$ mv u01 ..

10) Посмотрим список символических ссылок на табличные пространства:

postgres@tantor:~/tantor-se-16$ ls -al $PGDATA/pg_tblspc

total 8

drwx------ 2 postgres postgres 4096 Mar 10 10:40 .

drwxr-x--- 19 postgres postgres 4096 Mar 10 13:30 ..

lrwxrwxrwx 1 postgres postgres 41 Mar 10 10:40 32913 -> /var/lib/postgresql/tantor-se-16/data/u01

На директорию u01 указывает ссылка с названием 32913. В вашем случае название файла ссылки будет другое.

11) Пересоздадим ссылку, чтобы указывала на уже перемещенную директорию:

postgres@tantor:~/tantor-se-16$ ln -fs $PGDATA/../u01 $PGDATA/pg_tblspc/32913

12) Убедимся, что символическая ссылка указывает на содержимое директории табличного пространства:

postgres@tantor:~/tantor-se-16/data$ ls $PGDATA/pg_tblspc/32913

PG_16_202307071

13) Запустим экземпляр:

postgres@tantor:~/tantor-se-16/data$ sudo systemctl start tantor-se-server-16.service

14) Переподсоединимся в окне psql и проверим, что содержимое таблицы доступно:

postgres=# \c

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

postgres=# select count(*) from t;

count

-------

(1 row)

Директория табличного пространства успешно перенесена.

Раздел 5. Журналирование

Журналирование

Какая информация попадает в журнал
Расположение журналов сервера
Как информация попадает в журнал
Добавление формата csv
Включение коллектора сообщений

Часть 1. Какая информация попадает в журнал

Загрузим psql:

astra@tantor:~$ psql

psql (16.1)

Введите "help", чтобы получить справку.

postgres=#

postgres=# SHOW log_line_prefix;

log_line_prefix

------------------------

%m [%p:%v] [%d] %r %a

(1 row)

%m: Уровень сообщения (DEBUG5, DEBUG4, INFO, WARNING, ERROR, и так далее).

[%p:%v]: Идентификатор процесса PostgreSQL и номер версии протокола.

[%d]: Имя базы данных.

%r: Идентификатор транзакции.

%a: IP-адрес и порт клиента.

Часть 2. Расположение журналов сервера

1) Посмотрим путь до журналов

postgres=# SHOW log_directory;

log_directory

---------------

log

(1 row)

Какая маска у файлов журнала?

postgres=# SHOW log_filename;

log_filename

--------------------------------

postgresql-%Y-%m-%d_%H%M%S.log

(1 row)

Где находятся данных кластера БД?

postgres=# SHOW data_directory;

data_directory

---------------------------------------

/var/lib/postgresql/tantor-se-14/data

(1 row)

2) Посмотрим содержимое папки журнала.

postgres=#\! ls -l /var/lib/postgresql/tantor-se-14/data/log

total 148228

-rw------- 1 postgres postgres 1115 Jul 3 2023 postgresql-2023-07-03_130021.log

-rw------- 1 postgres postgres 1112 Jul 3 2023 postgresql-2023-07-03_130033.log

-rw------- 1 postgres postgres 6545 Jul 3 2023 postgresql-2023-07-03_162937.log

-------------------------------

3) Посмотрим содержимое любого журнала.

postgres=# \! tail -n 10 /var/lib/postgresql/tantor-se-14/data/log/postgresql-2023-07-03_130021.log

2023-07-03 13:00:21.009 MSK [23506] LOG: listening on IPv6 address "::1", port 5432

2023-07-03 13:00:21.012 MSK [23506] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"

2023-07-03 13:00:21.017 MSK [23506] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"

2023-07-03 13:00:21.021 MSK [23508] LOG: database system was shut down at 2023-07-03 13:00:19 MSK

2023-07-03 13:00:21.027 MSK [23506] LOG: database system is ready to accept connections

2023-07-03 13:00:33.293 MSK [23506] LOG: received fast shutdown request

2023-07-03 13:00:33.297 MSK [23506] LOG: aborting any active transactions

2023-07-03 13:00:33.299 MSK [23506] LOG: background worker "logical replication launcher" (PID 23514) exited with exit code 1

2023-07-03 13:00:33.300 MSK [23509] LOG: shutting down

2023-07-03 13:00:33.328 MSK [23506] LOG: database system is shut down

Убедились, что маска соответствует.

Часть 3. Как информация попадает в журнал

postgres=# CREATE TABLE t (id integer);

CREATE TABLE

postgres=# \! tail -n 10 /var/lib/postgresql/tantor-se-14/data/log/postgresql-2024-02-29_100938.log

2024-02-29 07:49:54.753 MSK [5289:8/30] [postgres] [local] psql LOG: statement: create table t (id integer);

Часть 4. Добавление формата csv

1) Посмотрим параметр.

postgres=# SHOW log_destination;

log_destination

-----------------

stderr

(1 row)

2) Изменим параметр и перечитаем конфигурацию.

postgres=# ALTER SYSTEM SET log_destination = 'stderr,csvlog';

ALTER SYSTEM

postgres=# SELECT pg_reload_conf();

pg_reload_conf

----------------

(1 row)

3) Посмотрим что параметр успешно применяется.

postgres=# SHOW log_destination;

log_destination

-----------------

stderr,csvlog

(1 row

4) Вставим новое значение в таблицу t.

postgres=# INSERT INTO t VALUES(1);

INSERT 0 1

5) Посмотрим содержимое файла.

postgres=# \! ls -l /var/lib/postgresql/tantor-se-14/data/log/*csv

-rw------- 1 postgres postgres 30749 Feb 29 08:02 /var/lib/postgresql/tantor-se-14/data/log/postgresql-2024-02-29_080122.csv

6) Добавился формат данных csv.

postgres=#\! tail -n 1 /var/lib/postgresql/tantor-se-14/data/log/postgresql-2024-02-29_080122.csv

2024-02-29 08:08:54.580 MSK,"postgres","postgres",9199,"[local]",65e01024.23ef,3,"idle",2024-02-29 08:03:32 MSK,5/325,0,LOG,00000,"statement: insert into t values(1);",,,,,,,,,"psql","client backend",,0

7) Сравним с содержимым обычного журнала.

postgres=# INSERT INTO t VALUES(1);

INSERT 0 1

postgres=# \! tail -n 1 /var/lib/postgresql/tantor-se-14/data/log/postgresql-2024-02-29_080122.log

2024-02-29 08:12:02.631 MSK [9199:5/326] [postgres] [local] psql LOG: statement: insert into t values(1);

postgres=#

Часть 5. Включение коллектора сообщений

postgres=# show logging_collector;

logging_collector

-------------------

(1 row)

Удалим ненужные объекты:

postgres=# DROP TABLE t;

DROP TABLE

postgres=# ALTER SYSTEM SET log_destination = 'stderr';

ALTER SYSTEM

postgres=# SELECT pg_reload_conf();

pg_reload_conf

----------------

(1 row)

Раздел 6. Безопасность

Ролевая модель безопасности

Создание новой роли
Установка атрибутов
Создание групповой роли
Создание схемы и таблицы
Выдача роли доступа к таблице
Удаление созданных объектов

Часть 1. Создание новой роли

1) Загрузим инструмент psql

astra@alse-vanilla-gui:~$ sudo su - postgres

postgres@alse-vanilla-gui:~$ psql

psql (16.1)

Введите "help", чтобы получить справку.

postgres=#

2) Создадим новую роль:

postgres=# CREATE ROLE user1;

CREATE ROLE

3) Посмотрим, какие есть роли в СУБД:

postgres=# \du

Список ролей

Имя роли | Атрибуты

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

anon_test_user | Суперпользователь

pma_user | Суперпользователь, Создаёт роли

postgres | Суперпользователь, Создаёт роли, Создаёт БД, Репликация, Пропускать RLS

replicator | Репликация

user1 | Вход запрещён

Часть 2. Установка атрибутов

postgres=# ALTER ROLE user1 LOGIN CREATEDB;

ALTER ROLE

postgres=# \du

Список ролей

Имя роли | Атрибуты

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

anon_test_user | Суперпользователь

pma_user | Суперпользователь, Создаёт роли

postgres | Суперпользователь, Создаёт роли, Создаёт БД, Репликация, Пропускать RLS

replicator | Репликация

user1 | Создаёт БД

Часть 3. Создание групповой роли

Предположим что нам нужна роль, под которой можно только подключаться к кластеру, а под второй создавать БД но нельзя делать соединения к БД.

1) Создадим вторую роль

postgres=# CREATE ROLE user2;

CREATE ROLE

postgres=# ALTER ROLE user2 LOGIN;

ALTER ROLE

2) Отзовем лишний атрибут:

postgres=# ALTER ROLE user1 NOLOGIN;

ALTER ROLE

postgres=# \du

Список ролей

Имя роли | Атрибуты

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

anon_test_user | Суперпользователь

pma_user | Суперпользователь, Создаёт роли

postgres | Суперпользователь, Создаёт роли, Создаёт БД, Репликация, Пропускать RLS

replicator | Репликация

user1 | Создаёт БД, Вход запрещён

user2 |

3) Дадим право вхождения в группу user1 роли user2.

postgres=# GRANT user1 TO user2;

GRANT ROLE

4) Проверим условия задания:

5) Первая роль не может входить в кластер БД:

postgres=# \c - user1

подключиться к серверу через сокет "/var/run/postgresql/.s.PGSQL.5432" не удалось: ВАЖНО: для роли "user1" вход запрещён

Сохранено предыдущее подключение

6) Входим под второй ролью:

postgres=# \c - user2

Вы подключены к базе данных "postgres" как пользователь "user2"

7) Пытаемся создать базу данных под второй ролью

postgres=> CREATE DATABASE dat1;

ОШИБКА: нет прав на создание базы данных

8) Переключаем роль на первую

postgres=> SET ROLE user1;

SET

9) Теперь создать базу данных можно

postgres=> CREATE DATABASE dat1;

CREATE DATABASE

10) Вернемся к роли user2

postgres=> RESET ROLE;

RESET

11) Подключаемся к БД dat1

dat1=> \c dat1

Вы подключены к базе данных "dat1" как пользователь "user2".

Часть 4. Создание схемы и таблицы

dat1=> CREATE SCHEMA sch1;

CREATE SCHEMA

Посмотрим кто владелец схемы

dat1=> \dn+

List of schemas

Name | Owner | Access privileges | Description

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

public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema

| | =U/pg_database_owner |

sch1 | user2 | |

(2 строки)

dat1=> CREATE TABLE sch1.a1 (id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY, str text);

CREATE TABLE

Посмотрим описание таблицы

dat1=>\d sch1.a1

Таблица "sch1.a1"

Столбец | Тип | Правило сортировки | Допустимость NULL | По умолчанию

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

id | integer | | not null | generated always as identity

str | text | | |

Индексы:

"a1_pkey" PRIMARY KEY, btree (id)

Посмотрим разрешения на таблицу

dat1=>\dp sch1.a1

Права доступа

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

sch1 | a1 | таблица | | |

(1 строка)

Пока нет ни у какой роли, кроме суперпользовательской.

Часть 5. Выдача роли доступа к таблице

1) Создадим еще одну роль:

dat1=> \c - postgres

Вы подключены к базе данных "dat1" как пользователь "postgres"

dat1=# CREATE ROLE user3 LOGIN;

CREATE ROLE

2) Попробуем получить доступ к таблице a1.

dat1=# \c - user3

Вы подключены к базе данных "dat1" как пользователь "user3".

dat1=> \dn

Список схем

Имя | Владелец

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

public | pg_database_owner

sch1 | user2

(2 rows)

dat1=> SELECT * FROM sch1.a1;

ОШИБКА: нет доступа к схеме sch1

СТРОКА 1: SELECT * FROM sch1.a1;

3) В доступе отказано нет привилегий на схему.

dat1=> \c - postgres

Вы подключены к базе данных "dat1" как пользователь "postgres"

dat1=> GRANT USAGE on SCHEMA sch1 TO user3;

GRANT

dat1=> \dn+ sch1

Список схем

Имя | Владелец | Права доступа | Описание

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

sch1 | user2 | user2=UC/user2+|

| | user3=U/user2 |

(1 строка)

dat1=> \c - user3

You are now connected to database "dat1" as user "user3".

dat1=> SELECT * FROM sch1.a1;

ОШИБКА: нет доступа к таблице a1

Теперь отказ из-за отсутствия привилегий на таблице a1.

dat1=> \c - postgres

Вы подключены к базе данных "dat1" как пользователь "postgres"

dat1=> GRANT SELECT, INSERT (str) ON TABLE sch1.a1 to user3;

GRANT

dat1=> \dp sch1.a1

Права доступа

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

sch1 | a1 | таблица | user2=arwdDxt/user2+| str: +|

(1 строка)

dat1=> \c - user3

Вы подключены к базе данных "dat1" как пользователь "user3"

dat1=> SELECT * FROM sch1.a1;

id | str

----+-----

(0 строк)

Теперь все в порядке. Доступ предоставлен в рамках выданных привилегий.

Проверим вставку в столбец.

dat1=> INSERT INTO sch1.a1 (str) VALUES ('первая запись');

INSERT 0 1

dat1=> SELECT * FROM sch1.a1;

id | str

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

1 | первая запись

(1 row)

Проверим вставку в первый столбец.

dat1=> INSERT INTO sch1.a1 OVERRIDING SYSTEM VALUE values (2);

ОШИБКА: нет доступа к таблице a1

Не хватает привилегий

Удаление также строк и объекта невозможно - нужно быть владельцем или суперпользователем.

dat1=> DELETE FROM sch1.a1;

ОШИБКА: нет доступа к таблице a1

dat1=> DROP TABLE sch1.a1;

ОШИБКА: нужно быть владельцем таблицы a1

Часть 6. Удаление созданных объектов

Удалим схему.

dat1=> \c - user2

Вы подключены к базе данных "dat1" как пользователь "user2".

dat1=> DROP SCHEMA sch1;

ОШИБКА: удалить объект схема sch1 нельзя, так как от него зависят другие объекты

ПОДРОБНОСТИ: таблица sch1.a1 зависит от объекта схема sch1

ПОДСКАЗКА: Для удаления зависимых объектов используйте DROP ... CASCADE.

Схема не пуста, можно удалить каскадом.

dat1=> DROP SCHEMA sch1 CASCADE;

ЗАМЕЧАНИЕ: удаление распространяется на объект таблица sch1.a1

DROP SCHEMA

Переключимся на другую базу данных и и удалим dat1.

dat1=> \c postgres

Вы подключены к базе данных "postgres" как пользователь "user2".

postgres=> DROP DATABASE dat1 (force);

DROP DATABASE

Для удаления ролей воспользуемся суперпользовательской ролью.

postgres=> \c - postgres

Вы подключены к базе данных "postgres" как пользователь "postgres".

postgres=# DROP ROLE user1, user2, user3;

DROP ROLE

Подключение и аутентификация

Расположение файлов конфигурации
Просмотр правил аутентификации
Локальные изменения для аутентификации
Проверка корректности настройки
Очистка ненужных объектов

Часть 1. Расположение файлов конфигурации

1) Загрузим инструмент psql.

astra@alse-vanilla-gui:~$ sudo su - postgres

postgres@alse-vanilla-gui:~$ psql

psql (16.1)

Введите "help", чтобы получить справку.

2) Посмотрим место расположение конфигурационного файла.

postgres=# SHOW hba_file;

hba_file

---------------------------------------------------

/var/lib/postgresql/tantor-se-16/data/pg_hba.conf

(1 строка)

3) Можно посмотреть правила подключения с помощью представления pg_hba_file_rules

postgres=# \d pg_hba_file_rules;

Представление "pg_catalog.pg_hba_file_rules"

Столбец | Тип | Правило сортировки | Допустимость NULL | По умолчанию

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

rule_number | integer | | |

file_name | text | | |

line_number | integer | | |

type | text | | |

database | text[] | | |

user_name | text[] | | |

address | text | | |

netmask | text | | |

auth_method | text | | |

options | text[] | | |

error | text | | |

Часть 2. Просмотр правил аутентификации

postgres=# SELECT rule_number, type, database, user_name, auth_method FROM pg_hba_file_rules();

rule_number | type | database | user_name | auth_method

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

1 | local | {all} | {pma_user} | md5

2 | local | {all} | {all} | trust

3 | host | {all} | {all} | trust

4 | host | {all} | {all} | trust

5 | local | {replication} | {all} | trust

6 | host | {replication} | {all} | trust

7 | host | {replication} | {all} | trust

8 | host | {all} | {all} | md5

(8 строк)

(7 rows)

Часть 3. Локальные изменения для аутентификации

1) Любым редактором внесем две строки.

Файл pg_hba.conf

# TYPE DATABASE USER ADDRESS METHOD

local all all ident map=map1

Файл pg_ident.conf

# MAPNAME SYSTEM-USERNAME PG-USERNAME

map1 astra user1

postgres@alse-vanilla-gui:~$ psql

psql (16.1)

Введите "help", чтобы получить справку

postgres=# SELECT pg_reload_conf();

pg_reload_conf

----------------

(1 row)

2) Создадим двух пользователей user1 и 2.

postgres=# CREATE ROLE user1 LOGIN;

CREATE ROLE

postgres=# CREATE ROLE user2 LOGIN;

CREATE ROLE

postgres=# \du

Список ролей

Имя роли | Атрибуты

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

anon_test_user | Суперпользователь

pma_user | Суперпользователь, Создаёт роли

postgres | Суперпользователь, Создаёт роли, Создаёт БД, Репликация, Пропускать RLS

replicator | Репликация

3) Посмотрим есть ли ошибки в конфигурации:

postgres=# \d pg_ident_file_mappings;

Представление "pg_catalog.pg_ident_file_mappings"

Столбец | Тип | Правило сортировки | Допустимость NULL | По умолчанию

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

map_number | integer | | |

file_name | text | | |

line_number | integer | | |

map_name | text | | |

sys_name | text | | |

pg_username | text | | |

error | text | | |

postgres=# SELECT * FROM pg_ident_file_mappings;

map_number | file_name | line_number | map_name

| sys_name | pg_username | error

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

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

1 | /var/lib/postgresql/tantor-se-16/data/pg_ident.conf | 74 | map1

| astra | user1 |

(1 строка)

postgres=# SELECT rule_number, type, database, user_name, auth_method, address, options, error FROM pg_hba_file_rules();

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

1 | local | {all} | {all} | peer | | {map=m1} |

2 | local | {all} | {pma_user} | md5 | | |

3 | local | {all} | {all} | trust | | |

4 | host | {all} | {all} | trust | 127.0.0.1 | |

5 | host | {all} | {all} | trust | ::1 | |

6 | local | {replication} | {all} | trust | | |

7 | host | {replication} | {all} | trust | 127.0.0.1 | |

8 | host | {replication} | {all} | trust | ::1 | |

9 | host | {all} | {all} | md5 | 0.0.0.0 | |

(9 строк)

Часть 4. Проверка корректности настройки

astra@alse-vanilla-gui:~$ psql -U user2 -d postgres

psql: ошибка: подключиться к серверу через сокет "/var/run/postgresql/.s.PGSQL.5432" не удалось: ВАЖНО: пользователь "user2" не прошёл проверку подлинности (Peer)

astra@alse-vanilla-gui:~$ psql -U user1 -d postgres

psql (16.1)

Введите "help", чтобы получить справку.

postgres=> \q

Часть 5. Очистка ненужных объектов

postgres=# DROP ROLE user1, user2;

DROP ROLE

Удалить строки.
Файл pg_hba.conf

# TYPE DATABASE USER ADDRESS METHOD

local all all ident map=map1

Файл pg_ident.conf

# MAPNAME SYSTEM-USERNAME PG-USERNAME

map1 astra user1

postgres=# SELECT pg_reload_conf();

pg_reload_conf

----------------

(1 row)

postgres=# SELECT rule_number, type, database, user_name, auth_method, address, options, error FROM pg_hba_file_rules();

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

1 | local | {all} | {pma_user} | md5 | | |

2 | local | {all} | {all} | trust | | |

3 | host | {all} | {all} | trust | 127.0.0.1 | |

4 | host | {all} | {all} | trust | ::1 | |

5 | local | {replication} | {all} | trust | | |

6 | host | {replication} | {all} | trust | 127.0.0.1 | |

7 | host | {replication} | {all} | trust | ::1 | |

(7 строк)

postgres=# select * from pg_ident_file_mappings;

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

(0 строк)

Раздел 7. Резервное копирование

Физическое копирование

Изменение размера WAL файлов

1) Корректно остановим экземпляр кластера:

postgres@tantor:~$ pg_ctl stop

ожидание завершения работы сервера.... готово

сервер остановлен

2) Проверим, что остановка выполнена корректно:

postgres@tantor:~$ pg_controldata | grep state

postgres@tantor:~$ pg_controldata | grep Состояние

Состояние кластера БД: выключен

Переключение раскладки: справа внизу окна виртуальной машины кликнуть мышкой на Eng.

3) Сохраним значения из управляющего файла для последующего сравнения со значениями, которые изменятся:

postgres@tantor:~$ pg_controldata > 16MB.txt

4) Меняем размер WAL-сегментов с 16Мб на 25Мб:

postgres@tantor:~$

pg_resetwal --wal-segsize=256 /var/lib/postgresql/tantor-se-16/data

Журнал предзаписи сброшен (Write-ahead log reset)

5) Сохраним значения из управляющего файла для сравнения:

postgres@tantor:~$ pg_controldata > 256MB.txt

Сравним:

postgres@tantor:~$ diff 16MB.txt 256MB.txt

5,8c5,8

< Последнее обновление pg_control: 02:29:38 PM MSK

< Положение последней конт. точки: 9/1199D828

< Положение REDO последней конт. точки: 9/1199D828

< Файл WAL c REDO последней к. т.: 000000010000000900000011

---

> Последнее обновление pg_control: 02:34:53 PM MSK

> Положение последней конт. точки: 9/30000028

> Положение REDO последней конт. точки: 9/30000028

> Файл WAL c REDO последней к. т.: 000000010000000900000003

23c23

< Время последней контрольной точки: 02:29:38 PM MSK

---

> Время последней контрольной точки: 02:34:53 PM MSK

30c30

< Значение wal_level: replica

---

> Значение wal_level: minimal

42c42

< Байт в сегменте WAL: 16777216

---

> Байт в сегменте WAL: 268435456

Пример на английском языке:

< pg_control last modified: 12:43:57 AM MSK

< Latest checkpoint location: 115/BE000F70

< Latest checkpoint's REDO location: 115/BE000F70

< Latest checkpoint's REDO WAL file: 0000000100000115000000BE

---

> pg_control last modified: 12:48:17 AM MSK

> Latest checkpoint location: 115/D0000028

> Latest checkpoint's REDO location: 115/D0000028

> Latest checkpoint's REDO WAL file: 00000001000001150000000D

23c23

< Time of latest checkpoint: 12:43:57 AM MSK

---

> Time of latest checkpoint: 12:48:17 AM MSK

30c30

< wal_level setting: replica

---

> wal_level setting: minimal

42c42

< Bytes per WAL segment: 16777216

---

> Bytes per WAL segment: 268435456

значение minimal поменяет своё значение после запуска экземпляра.

6) Попытаемся запустить экземпляр:

postgres@tantor:~$ pg_ctl start

ожидание запуска сервера....

[10094] ВАЖНО: "min_wal_size" должен быть минимум вдвое больше "wal_segment_size"

[10094] СООБЩЕНИЕ: система БД выключена

прекращение ожидания

pg_ctl: не удалось запустить сервер

Изучите протокол выполнения.

waiting for server to start....

[10094] FATAL: "min_wal_size" must be at least twice "wal_segment_size"

[10094] LOG: database system is shut down

stopped waiting

pg_ctl: could not start server

Examine the log output

Мы не учли, что от размера WAL сегментов может что-то зависеть.

7) Установим значение параметра:

postgres@tantor:~$ echo "min_wal_size=512MB" >> $PGDATA/postgresql.auto.conf

8) Запустим экземпляр:

postgres@tantor:~$ pg_ctl start

ожидание запуска сервера....

[10962] СООБЩЕНИЕ: передача вывода в протокол процессу сбора протоколов

2024-04-27 14:40:57.726 MSK [10962] ПОДСКАЗКА: В дальнейшем протоколы будут выводиться в каталог "log".

готово

сервер запущен

Экземпляр запустился.

9) В psql переключим файл журнала:

postgres@tantor:~$ psql

postgres=# select pg_switch_wal();

pg_switch_wal

---------------

115/D000015A

(1 row)

postgres=# select pg_switch_wal();

pg_switch_wal

---------------

115/E000008A

(1 row)

[8505] LOG: checkpoint starting: wal

Теперь после слэша меняется не два символа, а один. Остальные символы укажут на смещение в 256-мегабайтном файле

LOG: checkpoint complete: wrote 4 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.001 s, total=0.270 s; sync files=3, longest=0.001 s, average=0.001 s; distance=524288 kB, estimate=524288 kB; lsn=115/F00000B8, redo lsn=115/F0000070

10) Выйдем из psql, остановим кластер и вернем обратно размер журнала:

postgres=# \q

postgres@tantor:~$ pg_ctl stop

[8504] LOG: received fast shutdown request

ожидание завершения работы сервера....

[8504] LOG: aborting any active transactions

[8504] LOG: background worker "logical replication launcher" (PID 8510) exited with exit code 1

[8505] LOG: shutting down

[8505] LOG: checkpoint starting: shutdown immediate

[8505] LOG: checkpoint complete: wrote 0 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.001 s, total=0.008 s; sync files=0, longest=0.000 s, average=0.000 s; distance=0 kB, estimate=471859 kB; lsn=115/F0000198, redo lsn=115/F0000198

[8504] LOG: database system is shut down

готово

сервер остановлен

11) Проверяем корректность остановки:

postgres@tantor:~$ pg_controldata | grep state

postgres@tantor:~$ pg_controldata | grep Состояние

Состояние кластера БД: выключен

12) Меняем размер обратно на 16Мб:

postgres@tantor:~$ pg_resetwal --wal-segsize=16 /var/lib/postgresql/tantor-se-16/data

Журнал предзаписи сброшен

13) Запустим экземпляр через службы:

postgres@tantor:~$ sudo systemctl start tantor-se-server-16

14) Проверяем как изменилось содержимое выдаваемых LSN:

postgres@tantor:~$ psql

psql (16.1)

Type "help" for help.

postgres=# select pg_switch_wal();

pg_switch_wal

---------------

116/15A

(1 row)

postgres=# select pg_switch_wal();

pg_switch_wal

---------------

116/100008A

(1 row)

15) LSN может выводиться коротким, как в данном примере. Почему LSN был с виду «коротким» 116/15A? И в 116/100008A после слэша 7 символов, а не 8.

Потому, что название WAL-сегмента приняло значение ноль в конце.

Реальное значение: 116/0000015A и 116/0100008A

postgres@tantor:~$ ls $PGDATA/pg_wal

000000010000011600000000 000000010000011600000001 000000010000011600000002 000000010000011600000003 archive_status

16) Посмотрим какие записи есть в файлах журнала (выберите несколько):

postgres@tantor:~$ pg_waldump 000000010000011600000000

rmgr: XLOG len (rec/tot): 148/ 148, tx: 0, lsn: 116/00000028, prev 0/00000000, desc: CHECKPOINT_SHUTDOWN redo 116/28; tli 1; prev tli 1; fpw true; xid 35741; oid 390998; multi 502936; offset 2034077; oldest xid 723 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 0; shutdown

rmgr: XLOG len (rec/tot): 56/ 56, tx: 0, lsn: 116/000000C0, prev 116/00000028, desc: PARAMETER_CHANGE max_connections=100 max_worker_processes=8 max_wal_senders=10 max_prepared_xacts=0 max_locks_per_xact=64 wal_level=replica wal_log_hints=off track_commit_timestamp=off

rmgr: Standby len (rec/tot): 68/ 68, tx: 0, lsn: 116/000000F8, prev 116/000000C0, desc: RUNNING_XACTS nextXid 35741 latestCompletedXid 35740 oldestRunningXid 35741

rmgr: XLOG len (rec/tot): 26/ 26, tx: 0, lsn: 116/00000140, prev 116/000000F8, desc: SWITCH

postgres@tantor:~$ pg_waldump 000000010000011600000001

rmgr: Standby len (rec/tot): 68/ 68, tx: 0, lsn: 116/01000028, prev 116/00000140, desc: RUNNING_XACTS nextXid 35741 latestCompletedXid 35740 oldestRunningXid 35741

rmgr: XLOG len (rec/tot): 26/ 26, tx: 0, lsn: 116/01000070, prev 116/01000028, desc: SWITCH

postgres@tantor:~$ pg_waldump 000000010000011600000002

rmgr: Standby len (rec/tot): 68/ 68, tx: 0, lsn: 116/02000028, prev 116/01000070, desc: RUNNING_XACTS nextXid 35741 latestCompletedXid 35740 oldestRunningXid 35741

rmgr: XLOG len (rec/tot): 26/ 26, tx: 0, lsn: 116/02000070, prev 116/02000028, desc: SWITCH

Текущий файл журнала (03):

postgres@tantor:~$ pg_waldump 000000010000011600000003

rmgr: Standby len (rec/tot): 68/ 68, tx: 0, lsn: 116/03000028, prev 116/02000070, desc: RUNNING_XACTS nextXid 35741 latestCompletedXid 35740 oldestRunningXid 35741

rmgr: Standby len (rec/tot): 68/ 68, tx: 0, lsn: 116/03000070, prev 116/03000028, desc: RUNNING_XACTS nextXid 35741 latestCompletedXid 35740 oldestRunningXid 35741

rmgr: XLOG len (rec/tot): 148/ 148, tx: 0, lsn: 116/030000B8, prev 116/03000070, desc: CHECKPOINT_ONLINE redo 116/3000070; tli 1; prev tli 1; fpw true; xid 35741; oid 390998; multi 502936; offset 2034077; oldest xid 723 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 35741; online

rmgr: Standby len (rec/tot): 68/ 68, tx: 0, lsn: 116/03000150, prev 116/030000B8, desc: RUNNING_XACTS nextXid 35741 latestCompletedXid 35740 oldestRunningXid 35741

pg_waldump: error: error in WAL record at 116/3000150: invalid record length at 116/3000198: expected at least 26, got 0

или на русском языке:

pg_waldump: ошибка: ошибка в записи WAL в позиции 116/3000150: неверная длина записи в позиции 9/A3000198: ожидалось минимум 26, получено 0

Логическое копирование

Обработка строк большого размера

1) Выполните команды:

drop table if exists t2;

create table t2 (c1 text, c2 text);

insert into t2 (c1)

VALUES (repeat('a', 1024*1024*512));

update t2 set c2 = c1;

select * from t2;

При выполнении команды select появится ошибка:

ERROR: out of memory

DETAIL: Cannot enlarge string buffer containing 536870922 bytes by 536870912 more bytes.

[31089] ERROR: out of memory

[31089] DETAIL: Cannot enlarge string buffer containing 536870922 bytes by 536870912 more bytes.

[31089] STATEMENT: select * from t2;

При выборке в строковый буфер выбиралось значение поля c1 плюс 10 байт. Для выборки значения второго поля c2 буфер пытался увеличиться на его размер поля c2.

2) Попробуем с меньшими полями:

drop table if exists t1;

create table t1 (c1 text, c2 text, c3 text, c4 text);

insert into t1 (c1) VALUES (repeat('a', 1024*1024*256));

update t1 SET c2=c1;

update t1 SET c3=c1;

update t1 SET c4=c1;

select * from t1;

Появится ошибка:

ERROR: out of memory

DETAIL: Cannot enlarge string buffer containing 805306386 bytes by 268435456 more bytes.

При выборке в строковый буфер выбирались значения полей c1, c2, c3. Буфер достиг размера трёх полей плюс 18 байт. При увеличении размера буфера на размер поля c4 возникла ошибка превышения границы 1Гб.

3) Выполните команду:

postgres=# COPY t2 TO '/tmp/test';

ERROR: out of memory

DETAIL: Cannot enlarge string buffer containing 536870913 bytes by 536870912 more bytes.

В логе кластера будут сообщения:

20:17:50.015 MSK [31089] ERROR: out of memory

20:17:50.015 MSK [31089] DETAIL: Cannot enlarge string buffer containing 536870913 bytes by 536870912 more bytes.

20:17:50.015 MSK [31089] STATEMENT: COPY t2 TO '/tmp/test';

Возникла та же самая ошибка.

4) Строки больше 1Гб можно выгрузить по отдельным полям:

postgres=# COPY t2 (c1) TO '/tmp/test';

COPY 1

postgres=# \! rm /tmp/test

5) Выполните:

drop table if exists t2;

create table t2 (c1 text);

insert into t2 (c1) VALUES (repeat(E'a\n', 357913941));

COPY t2 TO '/tmp/test';

Появится ошибка:

postgres=# COPY t2 TO '/tmp/test';

ERROR: out of memory

DETAIL: Cannot enlarge string buffer containing 1073741822 bytes by 1 more bytes.

Было превышено на 1 байт ограничение на память строкового буфера.

В логе кластера будут сообщения:

20:23:51.783 MSK [31089] ERROR: out of memory

20:23:51.783 MSK [31089] DETAIL: Cannot enlarge string buffer containing 1073741822 bytes by 1 more bytes.

20:23:51.783 MSK [31089] STATEMENT: COPY t2 TO '/tmp/test';

Размер поля - треть гигабайта с округлением в меньшую сторону.

При выгрузке в текстовом виде содержимое поля будет выглядеть так:

a\na\na\na\n и размер поля увеличится в три раза до 1073741823 байт, что на 1 байт превышает максимальную границу.

6) При использовании формата binary поле можно выгрузить:

postgres=# COPY t2 TO '/tmp/test' WITH BINARY;

COPY 1

postgres=# \! rm /tmp/test

7) удалите таблицы:

drop table t1;

drop table t2;

Примечание:

Если на виртуальной машине не хватает физической памяти для выделения буфера обработки строк, то экземпляр может аварийно остановиться.

Следующий пример можно не выполнять:

drop table if exists t2;

create table t2 (c1 text, c2 text);

insert into t2 (c1) values (repeat('a', 1024*1024*1024-69));

В процессе выполнения команды insert, если успеть, то можно во втором окне показать как менялся объем памяти:

postgres@tantor:~$ free -b -w

postgres@tantor:~/tantor-se-16/data/base/5$ free -b -w

total used free shared buffers cache available

Mem: 8325275648 3656470528 2537848832 1463402496 77914112 2053042176 2886438912

Swap: 0 0 0

total used free shared buffers cache available

Mem: 8325275648 5789761536 412213248 1463402496 80195584 2043105280 761610240

Swap: 0 0 0

Использование памяти увеличилось примерно на 2Гб (2125635584 байт). Свободной памяти осталось 400Мб.

update t2 set c2 = c1;

select * from t2;

сервер неожиданно закрыл соединение

Скорее всего сервер прекратил работу из-за сбоя

до или в процессе выполнения запроса.

Подключение к серверу потеряно. Попытка восстановления неудачна.

!?> \q

postgres@tantor:~$ psql

psql (16.1)

Введите "help", чтобы получить справку.

postgres=# drop table t2;

DROP TABLE

Такая ошибка возникнет при нехватке физической памяти. Серверный процесс пытается выделить чуть меньше 4Гб памяти, а свободной памяти в данном примере 2.5Гб. oom-kill (out of memory killer) убил серверный процесс. Процесс postgres остановил все процессы и запустил фоновые процессы.

Сообщения в логе кластера:

[31030] LOG: server process (PID 31038) was terminated by signal 9: Killed

[31030] DETAIL: Failed process was running: COPY t1 TO '/tmp/test' WITH BINARY;

[31030] LOG: terminating any other active server processes

[31030] LOG: all server processes terminated; reinitializing

[31039] LOG: database system was interrupted; last known up at 19:58:59 MSK

[31042] FATAL: the database system is in recovery mode

Failed.

[31039] LOG: database system was not properly shut down; automatic recovery in progress

[31039] LOG: redo starts at 116/CE344C0

[31039] LOG: invalid record length at 116/DF34798: expected at least 26, got 0

[31039] LOG: redo done at 116/DF34770 system usage: CPU: user: 0.02 s, system: 0.12 s, elapsed: 0.15 s

[31040] LOG: checkpoint starting: end-of-recovery immediate wait

[31040] LOG: checkpoint complete: wrote 2105 buffers (12.8%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.025 s, sync=0.003 s, total=0.031 s; sync files=25, longest=0.001 s, average=0.001 s; distance=17408 kB, estimate=17408 kB; lsn=116/DF34798, redo lsn=116/DF34798

[31030] LOG: database system is ready to accept connections

Сообщение в журнале операционной системы:

tantor kernel: oom-kill:constraint=CONSTRAINT_NONE,nodemask=(null), cpuset=user.slice, mems_allowed=0,global_oom,task_memcg=/system.slice/tantor-se-server-16.service, task=postgres,pid=4647,uid=997

В примере oom-kill посылает сигнал 9 (SIGKILL) серверному процессу, но он может послать этот сигнал и другим процессам, которые выделили много памяти. Процесс postgres останавливает все процессы и снова запускает процессы, как при запуске экземпляра.

Раздел 8. Репликация

Физическая репликация

До выполнения демонстрации проверьте есть ли табличные пространства:

postgres=# \db

List of tablespaces

Name | Owner | Location

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

pg_default | postgres |

pg_global | postgres |

u01tbs | postgres | /var/lib/postgresql/tantor-se-16/data/../u01

(3 rows)

Если есть созданные ранее табличные пространства, то удалите их. Если табличное пространство не содержит объектов, то оно удалится командой:

postgres=# drop tablespace u01tbs;

DROP TABLESPACE

Если не удалится, так как есть объекты, то список отношений в текущей базе данных можно получить командой:

SELECT n.nspname, relname

FROM pg_class c

LEFT JOIN pg_namespace n ON n.oid = c.relnamespace,

pg_tablespace t

WHERE relkind IN ('r','m','i','S','t') AND

n.nspname <> 'pg_toast' AND t.oid = reltablespace AND

t.spcname = 'u01tbs';

Удалить объекты и потом удалить табличное пространство.

Создание физической реплики

1) Сделаем бэкап с параметрами

-P показывает прогресс резервирования;

-C или --slot создает слот;

-R создает файлы конфигурации для реплики:

postgres@tantor:~$ pg_basebackup -D /var/lib/postgresql/tantor-se-16-replica/data1 -P -R -C --slot=replica1

Если резервирование прервать, то нужно будет удалить директорию:
rm -rf /var/lib/postgresql/tantor-se-16-replica/data1

и слот на мастере:
select pg_drop_replication_slot('replica1');

2) После успешного создания бэкапа нужно установить порт для экземпляра реплики. Обязательно две угловые скобки, если будет одна, то файл затрётся:

echo "port=5433" >> /var/lib/postgresql/tantor-se-16-replica/data1/postgresql.auto.conf

3) Можно запустить реплику:

pg_ctl start -D /var/lib/postgresql/tantor-se-16-replica/data1

ожидание запуска сервера....

[446] СООБЩЕНИЕ: передача вывода в протокол процессу сбора протоколов

[446] ПОДСКАЗКА: В дальнейшем протоколы будут выводиться в каталог "log".

готово

сервер запущен

4) На мастере посмотрим, что физический слот репликации создан и активен:

postgres@tantor:~$ psql

postgres=# select * from pg_replication_slots;

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

replica1 | | physical | | | f | t |

(1 строка)

5) Посмотрим ещё одно представление для мониторинга репликации:

postgres=# select * from pg_stat_replication \gx

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

pid | 12236

usesysid | 10

usename | postgres

application_name | walreceiver

client_addr |

client_hostname |

client_port | -1

backend_start | 12:00:59.907801+03

backend_xmin |

state | streaming

sent_lsn | 116/E1000070

write_lsn | 116/E1000070

flush_lsn | 116/E1000070

replay_lsn | 116/E1000070

write_lag |

flush_lag |

replay_lag |

sync_priority | 0

sync_state | async

reply_time | 12:07:11.962288+03

Имя приложения по умолчанию walreceiver.

6) Подключиться к реплике:

postgres=# \q

postgres@tantor:~$ psql -p 5433

7) Проверим название слота:

postgres=# \dconfig primary_slot_name

List of configuration parameters

Parameter | Value

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

primary_slot_name | replica1

(1 строка)

8) Посмотрим значение параметра cluster_name:

postgres=# \dconfig cluster_name

List of configuration parameters

Parameter | Value

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

cluster_name |

Значение параметра пусто, поэтому application_name=walreceiver

9) Посмотрим значение параметра primary_conninfo:

postgres=# show primary_conninfo \gx

-[ RECORD 1 ]-

primary_conninfo | user=postgres passfile='/var/lib/postgresql/.pgpass' channel_binding=prefer port=5432 sslmode=prefer sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 compression=off target_session_attrs=any load_balance_hosts=disable

Значение было сгенерировано автоматически утилитой pg_basebackup при использовании параметра -R на основе параметров, с которыми утилита подсоединялась к экземпляру с которого создавала бэкап.

10) Удалим реплику и слот репликации:

postgres=# \q

postgres@tantor:~$ pg_ctl stop -D /var/lib/postgresql/tantor-se-16-replica/data1

ожидание завершения работы сервера....

готово

сервер остановлен

postgres@tantor:~$ rm -rf /var/lib/postgresql/tantor-se-16-replica/data1

postgres@tantor:~$ psql -c "select pg_drop_replication_slot('replica1')"

pg_drop_replication_slot

--------------------------

(1 строка)

Логическая репликация

Однонаправленная репликация
Двунаправленная репликация

Часть 1. Однонаправленная репликация

1) Подсоединимся к базе данных мастера и создадим таблицу, которую будем реплицировать:

postgres@tantor:~$ psql

postgres=# create table t (t text);

CREATE TABLE

2) Посмотрим список таблиц, для которых не задан способ идентификации строк:

postgres=# SELECT relnamespace::regnamespace||'.'||relname "table"

FROM pg_class

WHERE relreplident IN ('d','n') -- d первичный ключ, n никакие

AND relkind IN ('r','p') -- r таблица, p секционированная

AND oid NOT IN (SELECT indrelid FROM pg_index WHERE indisprimary)

AND relnamespace <> 'pg_catalog'::regnamespace

AND relnamespace <> 'information_schema'::regnamespace

ORDER BY 1;

table

----------

public.demo2

public.hypo

public.t

utl_file.utl_file_dir

(4 строки)

По этим таблицам могут реплицироваться только вставки строк (INSERT) и TRUNCATE

3) Установим параметр конфигурации wal_level=logical. Изменение параметра требует перезапуск экземпляра:

postgres=# alter system set wal_level=logical;

ALTER SYSTEM

postgres=# \q

postgres@tantor:~$ pg_ctl stop -D /var/lib/postgresql/tantor-se-16/data

postgres@tantor:~$ sudo systemctl start tantor-se-server-16

4) Создадим публикацию:

postgres@tantor:~$ psql

postgres=# CREATE PUBLICATION t for TABLE t WITH (publish= 'insert,truncate');

Репликация UPDATE и DELETE рассматривается в практике.

5) Создадим определение таблицы t в какой-нибудь базе данных этого же кластера:

postgres=# \! pg_dump -t t --schema-only | psql -d test_db

Список баз можно посмотреть командой \l

6) Создадим слот логической репликации в базе источника

postgres=# select pg_create_logical_replication_slot('s','pgoutput');

pg_create_logical_replication_slot

------------------------------------

(s,9/BC0739E8)

(1 строка)

7) В базе-приёмнике создадим подписку и укажем имя слота

postgres=# \q

postgres@tantor:~$ psql -d test_db

psql (16.1)

Введите "help", чтобы получить справку.

test_db=# CREATE SUBSCRIPTION t CONNECTION 'dbname=postgres user=postgres' PUBLICATION t WITH (origin=none, create_slot=false, slot_name=s);

CREATE SUBSCRIPTION

Слот создали отдельно, потому что, если публикация и подписка в одном и том же кластере, то создание подписки подвиснет на создании слота. Можно создать репликацию даже между таблицами той же самой базы данных, но в разных схемах, так как имена таблиц должны быть одинаковыми.

8) Можно проверить, что вставка строк из одной базы в другую реплицируется.

Запустите другой тепминал или переключитесь в другое окно терминала.

postgres@tantor:~$ psql

postgres=# insert into t values ('a');

9) В первом окне терминала проверьте, что строка реплицировалась:

test_db=# select * from t;

---

(1 строка)

10) Аналогично проверьте, что реплицируется команда TRUNCATE:

postgres=# truncate t;

TRUNCATE TABLE

test_db=# select * from t;

---

(0 строк)

Часть 2. Двунаправленная репликация

1) Создадим репликацию в обратном направлении с зеркальными настройками.

Единственно имя слота должно быть уникальным в конфигурации.

test_db=# select pg_create_logical_replication_slot('reverses','pgoutput');

pg_create_logical_replication_slot

------------------------------------

(reverses,9/BC0817D8)

(1 строка)

test_db=# CREATE PUBLICATION t for TABLE t WITH (publish= 'insert,truncate');

CREATE PUBLICATION

2) В другом окне:

postgres=# CREATE SUBSCRIPTION t CONNECTION 'dbname=test_db user=postgres' PUBLICATION t WITH (origin=none, create_slot=false, slot_name=reverses);

WARNING: subscription "t" requested copy_data with origin = NONE but might copy data that had a different origin

ПОДРОБНОСТИ: The subscription being created subscribes to a publication ("t") that contains tables that are written to by other subscriptions.

ПОДСКАЗКА: Verify that initial data copied from the publisher tables did not come from other origins.

CREATE SUBSCRIPTION

Предупреждение говорит о том, что при создании подписки данные будут скопированы из таблиц публикующей базы данных. Если в таблицах подписчика уже есть эти строки и строки синхронизированы, то стоило бы создавать подписку с параметром copy_data=off.

В обоих таблицах нет ни одной строки, поэтому разницы нет.

Использование параметра copy_data=off рассматривается в практике. В демонстрации показывается пример предупреждения.

3) Проверим, что репликация работает в обе стороны:

test_db=# insert into t values ('b');

postgres=# insert into t values ('a');

INSERT 0 1

postgres=# select * from t;

---

(2 строки)

4) Удалим все строки:

postgres=# delete from t;

DELETE 2

5) Удаление не реплицируется, потому что в публикации указали publish= 'insert,truncate'

test_db=# select * from t;

---

(2 строки)

6) Вставим строку:

postgres=# insert into t values ('a');

INSERT 0 1

7) Проверим, что строка вставилась:

postgres=# select * from t;

---

(1 строка)

8) Проверим какие строки есть в таблице второй базе:

test_db=# select * from t;

---

(3 строки)

Возникла рассинхронизация. Строки на второй таблице не удаляются, но при этом новые строки вставляются. На первой таблице удалили 2 строки, потом вставили одну и получилась одна строка. На второй таблице две строки осталось и добавилась еще одна строка, получилось три строки.

9) Удалим объекты:

test_db=# drop subscription t;

NOTICE: dropped replication slot "s" on publisher

DROP SUBSCRIPTION

test_db=# drop publication t;

DROP PUBLICATION

test_db=# drop table t;

DROP TABLE

test_db=# \c postgres postgres /var/run/postgresql 5432

Вы подключены к базе данных "postgres" как пользователь "postgres".

postgres=# drop publication t;

DROP PUBLICATION

postgres=# drop subscription t;

NOTICE: dropped replication slot "reverses" on publisher

DROP SUBSCRIPTION

postgres=# drop table t;

DROP TABLE

Платформа «Tantor»

Обзор

Рабочие пространства
Обзор экземпляра
Настройка экземпляра
Профайлинг запросов
Текущие активности
Регламентные работы

Часть 1. Рабочие пространства

1) Войти в Платформу по локальной ссылке https://education.tantorlabs.ru/platform/login

2) Ввести учетные данные: [email protected] пароль Student123!

3) Открыть рабочее пространство «Tantor»

4) Открыть экземпляр «demo».

5) На странице «Обзор» продемонстрировать индикаторы.

Часть 2. Обзор экземпляра

Показать выпадающее окно «Сессии», «Нагрузка ЦПУ», «Доступно ОЗУ», «Сеть», «Блок из буфера».

Часть 3. Настройка экземпляра

1) Открыть страницу «Настройки» → «Страницы настройки кластера» → «ПАРАМЕТРЫ POSTGRESQL».

2) Изменить параметр autovacuum_analyze_scale_factor.

3) Нажать на кнопку «Применить новые настройки».

4) Показать возможные параметры фильтра.

Часть 4. Профайлинг запросов

1) Открыть страницу «Профилировщик запросов».

2) Выбрать запрос с самым большим значением «Записано временных блоков».

3) Нажать на поле «Хэш запроса» → посмотреть основную статистику запроса.

4) Перейти на закладку «Планы».

5) Выбрать план → нажать на любое поле правее «Хэш запроса».

6) Продемонстрировать графический план запроса.

Часть 5. Текущие активности

1) Открыть страницу «Текущая активность».

2) Выбрать БД postgres.

3) Продемонстрировать текущие подключения на вкладке «Выполнение».

Шаг 6. Регламентные работы

1) Открыть страницу «Обслуживание».

2) Выбрать БД test_db.

3) Выбрать «Раздутие индексов».

4) Отсортировать по убыванию колонку «КОЭФФ. РАЗДУТИЯ %»

5) Выбрать самую первую таблицу, Действие «Reindex».

6) Нажать кнопку «Запустить обслуживание».

7) Нажать на кнопку «Запустить обслуживание».

8) Нажать на ссылку «История».

9) Посмотреть результаты запуска.

tantorlabs.ru

страница из