Ansible ч.3

На прошлом занятии мы рассмотрели основные конструкции Ansible и написали роль для настройки Nginx в качестве L7 балансировщика.

Сегодня мы рассмотрим оставшиеся темы по Ansible и обзорно рассмотрим PostgreSQL, роль для установки которой мы пишем в рамках ДЗ.

Подключение внешнего кода

Ранее мы самостоятельно писали Ansible роли и плеи для настройки ВМ, но довольно часто встречаются ситуация, когда автоматизация уже сделала нашими коллегами или open-source сообществом. Также бывают ситуации, когда авторы ПО сами предоставляют средства автоматизации. В таких случаях лучше воспользоваться уже готовыми сторонними решениями.

Для подключения внешних зависимостей в Ansible встроен пакетный менеджер ansible-galaxy, который позволяет ставить пакеты двух видов:

Роли — обычные роли, вынесенные в отдельный пакет;
Коллекции — наборы ролей, плейбуков и различных плагинов, объединенные в одно пространство имен.

Распространение готовых Ansible сценариев в виде ролей — более старый, традиционный подход, но он имеет огромный минус: если роли требуются пользовательские модули или различные плагины, то не существует способа упаковать их вместе с ролью. Чтобы решить эту проблему и распространять другие Ansible-сущности придумали коллекции.

Примеры отдельных Ansible-ролей:

geerlingguy.postgresql — установка PostgreSQL;
elastic.elasticsearch — установка Elasticsearch (устаревшая роль).

Другие роли можно искать в центральном репозитории Ansible Galaxy — https://galaxy.ansible.com/ui/.

Примеры коллекций:

prometheus.prometheus — Ansible-роли для настройки мониторинга в экосистеме Prometheus. Включены роли для установки многих популярных экспортеров, Prometheus, Grafana;
confluent.platform — установка компонентов Confluent платформы — Zookeeper, Kafka, Kafka Connect, Schema Registry;
kubernetes_sigs.kubespray — настройка Kubernetes;
Установка ролей или коллекций производится при помощи файла requirements. yml, в котором указываются зависимости текущей рабочей области.

Добавим в requirements. yml коллекцию prometheus. prometheus:

---
collections:
  - name: prometheus.prometheus
    version: ">=0.14.0"

Теперь установим все указанные зависимости при помощи ansible-galaxy:

$ ansible-galaxy install -r requirements.yml
Starting galaxy collection install process
Process install dependency map
Starting collection install process
Downloading https://galaxy.ansible.com/api/v3/plugin/ansible/content/published/collections/artifacts/prometheus-prometheus-0.14.0.tar.gz to /home/user/.ansible/tmp/ansible-local-36920oy7ggm60/tmpgtprtbxy/prometheus-prometheus-0.14.0-7y219qxx
Installing 'prometheus.prometheus:0.14.0' to '/home/user/.ansible/collections/ansible_collections/prometheus/prometheus'
prometheus.prometheus:0.14.0 was installed successfully
'community.general:8.3.0' is already installed, skipping.

Полное описание requirements.yml смотри в документации.

Чтобы использовать роли из коллекции в своих плеях вызовите их по полному имени, состоящему из имени коллекции и локального имени роли.

Например, добавим установку node_exporter в плей настройки Nginx.

Изменим nginx_simple.play.yml:

---
- name: Install Nginx on vm2
  hosts: vm2
  roles:
    - prometheus.prometheus.node_exporter
    - nginx

Также доступна установка ролей и коллекций не из центрального репозитория Galaxy. Для внутренних коллекций удобно использовать установку напрямую из Git репозитория, заменим в requirements.yml установку с Galaxy на скачивание в GitHub:

collections:
  - name: https://github.com/prometheus-community/ansible.git
    type: git
    version: "0.14.0"

Шифрование переменных

Часто переменными инвентаря включают в себя секреты: пароли пользователей БД, серверные ключи шифрования для Nginx, токены доступа к внешним сервисам и т. п. Хранение таких переменных в репозиториях инфрастуруктуры представляет очевидный риск, что любой человек с доступом на чтение репозитория имеет доступ к инфраструктуре.

Стратегии работы с такими секретами сводятся к двум подходам:

Использование внешнего хранилища секретов с получением доступа во время запуска сценариев;
Хранение секретов в зашифрованном виде вместе с остальной конфигурацией.

Первый подход более сложный и требует дополнительной инфраструктуры — мы рассмотрим его на будущих занятиях. Второй же подход прост в первом приближении и реализуется встроенным в Ansible инструментом ansible-vault.

На самом деле второй подход также требует внешнего хранилища секретов — для хранения и разграничения доступа к ключам шифрования. Несмотря на это его стоит рассматривать отдельно, т.к. он не создает второго источника истины о конфигурации целей. Чтобы обновить пароль БД, нужно обновить файлы в рабочей области Ansible, а не в хранилище секретов.

Vault в базовой конфигурации позволяет шифровать файлы с переменными/обычные файлы/значения переменных при помощи пароля, задаваемого при запуске плейбука или указанного в файле.

Настроим в рабочей области шифрование при помощи чтения пароля из файла: создадим файл vault-pass и сохраним в него супер-секретный пароль (ilovefish занят, выберите другой):

echo ilovefish >vault-pass

Теперь укажем в ansible.cfg путь к файлу с паролем:

[defaults]
host_key_checking = false
inventory = inventories/main
vault_password_file = vault-pass

И добавим в файл .gitignore путь к vault-pass, чтобы случайно не загрузить его на GitLab:

.venv/
__pycache__/
vault-pass

После этого создадим папку для переменных группы all нашего инвентаря main:

$ mkdir -p inventories/main/group_vars/all

И при помощи ansible-vault — зашифрованный файл с переменными encrypted.yml:

$ ansible-vault create inventories/main/group_vars/all/encrypted.yml

Со следующим содержимым:

---
encrypted_var: test

Теперь напишем тестовый плейбук vault_test.play.yml:

---
- name: Show ansible-vault variable
  hosts: all
  tasks:
    - name: Show var
      ansible.builtin.debug:
        var: encrypted_var

И запустим его:

$ ansible-playbook -v vault_test.play.yml

Ansible расшифрует файл автоматически и подставит значение переменной. Также при помощи команды ansible-vault encrypt мы можем шифровать обычные файлы для использования их, например, в модуле copy.

Теперь обзорно рассмотрим язык запросов SQL и базовые административные операции в СУБД PostgreSQL, которые будем выполнять используя его.

Эта информация пригодится для понимания последних пунктов ДЗ и для расширения кругозора, т.к. большинство систем используют реляционные СУБД и PostgreSQL в частности.

SQL

Язык структурированных запросов (SQL) — это язык программирования для хранения и обработки информации в реляционной базе данных.

Реляционная база данных хранит информацию в табличной форме со строками и столбцами, представляющими различные атрибуты данных и различные связи между значениями данных.

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

Для нас основная ценность SQL это получение выборок данных из СУБД. Потому что это напрямую связано с получением информации о состоянии базы данных.

Для начала немножко о подключении к базе. Поскольку мы рассматриваем Postgres то будем говорить про него. Реализации способов подключения к другим базам из консольного режима могут отличатся и стоит это учесть.

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

Установим недостающий пакет, если он не был установлен до этого:

sudo apt install postgresql-client-common

В Postgres используется psql. Если вы находитесь на машине с локальной базой данных то достаточно просто использовать следующую команду:

sudo --login --user=postgres

Почему sudo и postgres? По умолчанию современные версии PostgreSQL настроены на так называемую одноранговую аутентификацию.

Одноранговая аутентификация автоматически аутентифицирует пользователей, если существует действительный пользователь PostgreSQL, соответствующий имени пользователя в операционной системе.

Однако маловероятно, что с вашим обычным именем пользователя в операционной системе уже связано имя пользователя PostgreSQL.
Поэтому обычно вам необходимо войти в PostgreSQL, используя имя пользователя операционной системы, с которым уже связана роль PostgreSQL.

По умолчанию учетная запись суперпользователя или администратора PostgreSQL называется postgres. После установки в операционной системе также создается пользователь с именем postgres.

Итак, чтобы войти в PostgreSQL как пользователь postgres, вам необходимо подключиться как пользователь операционной системы postgres.

Если нет необходимости запускать дополнительные shell, команды можно сделать так:

sudo --login --user=postgres psql

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

psql -h <hostname> -p <port> -U <username> -d <database>

Для сервисов обычно используется формат connection string

postgresql://<username>:<password>@<hostname>:<port>/<database>

И так мы подключились к базе данных. Что же внутри и как получить необходимую информацию?

Для начала поговорим о структуре SQL запросов и командах.
Общая структура запроса выглядит в большинстве случаев так:

SELECT ('столбцы или * для выбора всех столбцов; обязательно')
FROM ('таблица; обязательно')
WHERE ('условие/фильтрация, например, city = 'Moscow'; необязательно')
GROUP BY ('столбец, по которому хотим сгруппировать данные; необязательно')
HAVING ('условие/фильтрация на уровне сгруппированных данных; необязательно')
ORDER BY ('столбец, по которому хотим отсортировать вывод; необязательно')
JOIN — ('необязательный элемент, используется для объединения таблиц по ключу, 
который присутствует в обеих таблицах. Перед ключом ставится оператор ON.')

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

SELECT * FROM pg_stat_activity;

Здесь можно увидеть информацию о происходящим внутри базы. В неотфильтрованном виде информация черезмерна, но мы можем ее отсортировать/отфильтровать под необходимые нам задачи.

Расшифровка состояний процессов в pg_stat_activity

Столбец	Тип	Описание
datid	oid	OID базы данных, к которой подключён этот серверный процесс
datname	name	Имя базы данных, к которой подключён этот серверный процесс
pid	integer	Идентификатор процесса этого серверного процесса
usesysid	oid	OID пользователя, подключённого к этому серверному процессу
usename	name	Имя пользователя, подключённого к этому серверному процессу
application_name	text	Название приложения, подключённого к этому серверному процессу
client_addr	inet	IP-адрес клиента, подключённого к этому серверному процессу. Значение null в этом поле означает, что клиент подключён через сокет Unix на стороне сервера или что это внутренний процесс, например, автоочистка.
client_hostname	text	Имя компьютера для подключённого клиента, получаемое в результате обратного поиска в DNS по client_addr. Это поле будет отлично от null только в случае соединений по IP и только при включённом режиме log_hostname.
client_port	integer	Номер TCP-порта, который используется клиентом для соединения с этим серверным процессом, или -1, если используется сокет Unix
backend_start	timestamp with time zone	Время запуска процесса, т. е. время, когда клиент подсоединился к серверу
xact_start	timestamp with time zone	Время начала текущей транзакции в этом процессе или null при отсутствии активной транзакции. Если текущий запрос был первым в своей транзакции, то значение в этом столбце совпадает со значением столбца query_start.
query_start	timestamp with time zone	Время начала выполнения активного в данный момент запроса, или, если state не active, то время начала выполнения последнего запроса
state_change	timestamp with time zone	Время последнего изменения состояния (поля state)
waiting	boolean	True, если этот серверный процесс ожидает освобождения блокировки
state	text	Общее текущее состояние этого серверного процесса. Возможные значения:
		active: серверный процесс выполняет запрос.
		idle: серверный процесс ожидает новой команды от клиента.
		idle in transaction: серверный процесс находится внутри транзакции, но в настоящее время не выполняет никакой запрос.
		idle in transaction (aborted): Это состояние подобно idle in transaction, за исключением того, что один из операторов в транзакции вызывал ошибку.
		fastpath function call: серверный процесс выполняет fast-path функцию.
		disabled: Это состояние отображается для серверных процессов, у которых параметр track_activities отключён.
backend_xid	xid	Идентификатор верхнего уровня транзакции этого серверного процесса или любой другой.
backend_xmin	xid	текущая граница xmin для серверного процесса.
query	text	Текст последнего запроса этого серверного процесса. Если state имеет значение active, то в этом поле отображается запрос, который выполняется в настоящий момент. Если процесс находится в любом другом состоянии, то в этом поле отображается последний выполненный запрос.

Попробуем получить информацию о запущенных запросах из таблицы pg_stat_activity:

###
### Список сессий на БД только с активными запросами 
###
SELECT pid, age(clock_timestamp(), query_start), usename, query 
FROM pg_stat_activity 
WHERE state != 'idle' AND pid != pg_backend_pid()
ORDER BY query_start desc;

###
### Все сессии
###
SELECT pid, age(clock_timestamp(), query_start), usename, query
FROM pg_stat_activity
WHERE pid != pg_backend_pid()
ORDER BY query_start desc;

Как видим их не так много поскольку база не нагружена.

В дальнейшем мы еще вернемся к запросам.
А пока давайте поговорим о ролях внутри PostgreSQL.

Роли PostgreSQL

Роли базы данных концептуально полностью отличаются от пользователей операционной системы. На практике поддержание соответствия между ними может быть удобным, но не является обязательным.

Роли базы данных являются глобальными для всей установки кластера базы данных (не для отдельной базы данных). Для создания роли используется команда SQL CREATE ROLE:

CREATE ROLE имя;

Здесь имя соответствует правилам именования идентификаторов SQL: либо обычное, без специальных символов, либо в двойных кавычках.

На практике, к команде обычно добавляются другие указания, такие как LOGIN. Подробнее об этом ниже.

Для удаления роли используется команда DROP ROLE:

DROP ROLE имя;

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

Имя роли для конкретного подключения к базе данных указывается клиентской программой характерным для неё способом, таким образом инициируя запрос на подключение.

Например, программа psql для указания роли использует аргумент командной строки -U.

Многие приложения предполагают, что по умолчанию нужно использовать имя пользователя операционной системы (включая createuser и psql).

Поэтому часто бывает удобным поддерживать соответствие между именами ролей и именами пользователей операционной системы.
Можно посмотреть под какой ролью мы сейчас залогинены:

SELECT current_user, session_user;

session_user обычно будет пользователем, от имени которого вы подключились к базе данных (хотя его можно изменить, установив SET SESSION AUTHORIZATION).

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

current_user изменяется с помощью SET ROLE и RESET ROLE (или запуска функций SECURITY DEFINER).

Немножко про SECURITY DEFINER.

SECURITY DEFINER позволяет пользователям «заимствовать» некоторые повышенные привилегии только во время выполнения функции.

Допустим, у меня есть функция, которая вставляет данные, и я хочу, чтобы пользователь с ограниченными правами мог ее выполнить.

Я бы предпочел, чтобы пользователь временно эмулировал «владельца» функции, который может выполнить только SELECT и INSERT, а не суперпользователя, который может создавать функции, а также DELETE, TRUNCATE, UPDATE и т. д. Для этого используется SECURITY DEFINER.

Чтобы посмотреть списки ролей присутствующих в базе можно воспользоваться метакомандой:

\du

Сильно углубляться в использование ролей мы не будем, вся необходимая информация есть в https://www.postgresql.org/docs/current/role-membership.html

Привилегии

Права доступа на работу с объектами базы данных контролируются привилегиями, управляемыми с помощью команд GRANT и REVOKE.

Мы можем проверить привилегии в psql с помощью команды:

\dp

При создании объекта ему назначается владелец. Владельцем обычно является роль, выполнившая оператор создания.

Для большинства типов объектов исходное состояние таково, что только владелец (или суперпользователь) может что-либо делать с объектом.

Чтобы разрешить другим ролям использовать его, необходимо предоставить привилегии.

Существуют различные виды привилегий: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, USAGE, SET and ALTER SYSTEM.

Привилегии, применимые к конкретному объекту, различаются в зависимости от типа объекта (таблица, функция и т. д.).

Право на изменение или уничтожение объекта присуще владельцу объекта и не может быть предоставлено или отозвано само по себе.
(Однако, как и все привилегии, это право может быть унаследовано членами роли владельца).

Объекту можно назначить нового владельца с помощью команды ALTER.

ALTER TABLE table_name OWNER TO new_owner;

Привилегии, предоставленные для конкретного объекта, отображаются в виде списка записей aclitem, где каждый aclitem описывает разрешения одного получателя прав, предоставленные конкретным лицом, предоставляющим право.

Список привилегий

Privilege	Abbreviation	Applicaple Object Type
SELECT	r (“read”)	LARGE OBJECT, SEQUENCE, TABLE (and table-like objects), table column
INSERT	a (“append”)	TABLE, table column
UPDATE	w (“write”)	LARGE OBJECT, SEQUENCE, TABLE, table column
DELETE	d	TABLE
TRUNCATE	D	TABLE
REFERENCES	x	TABLE, table column
TRIGGER	t	TABLE
CREATE	C	DATABASE, SCHEMA, TABLESPACE
CONNECT	c	DATABASE
TEMPORARY	T	DATABASE
EXECUTE	X	FUNCTION, PROCEDURE
USAGE	U	DOMAIN, FOREIGN DATA WRAPPER, FOREIGN SERVER, LANGUAGE, SCHEMA, SEQUENCE, TYPE
SET	s	PARAMETER
ALTER SYSTEM	A	PARAMETER

Команды для проверки привилегий каждого объекта

Object Type	All Privileges	Default Public Privileges	psql Command
DATABASE	CTc	Tc	\l
DOMAIN	U	U	\dD+
FUNCTION or PROCEDURE	X	X	\df+
FOREIGN DATA WRAPPER	U	none	\dew+
FOREIGN SERVER	U	none	\des+
LANGUAGE	U	U	\dL+
LARGE OBJECT	rw	none	\dl+
PARAMETER	sA	none	\dconfig+
SCHEMA	UC	none	\dn+
SEQUENCE	rwU	none	\dp
TABLE (and table-like objects)	arwdDxt	none	\dp
Table column	arwx	none	\dp
TABLESPACE	C	none	\db+
TYPE	U	U	\dT+

Пример выдачи привилегий:

GRANT SELECT ON mytable TO PUBLIC;
GRANT SELECT, UPDATE, INSERT ON mytable TO admin;
GRANT SELECT (col1), UPDATE (col1) ON mytable TO <user>;

WAL PostgreSQL

Давайте теперь поговорил о логах транзакций. Они же Write-Ahead Log.
Что это для чего нужно и как посмотреть что там происходит.

Основная задача логов транзакций это помощь в восстановлении после сбоев. Особенно если информация хранится в оперативной памяти.
Увы, чудес не бывает: чтобы пережить потерю информации в оперативной памяти, все необходимое должно быть своевременно записано на диск (или другое энергонезависимое устройство).

Поэтому вместе с изменением данных ведется еще и журнал этих изменений. Когда мы что-то меняем на странице в буферном кэше, мы создаем в журнале запись об этом изменении.

Запись содержит минимальную информацию, достаточную для того, чтобы при необходимости изменение можно было повторить.
Чтобы это работало, журнальная запись в обязательном порядке должна попасть на диск до того, как туда попадет измененная страница. Отсюда и название: журнал предзаписи (write-ahead log).

Если происходит сбой, данные на диске оказываются в рассогласованном состоянии: какие-то страницы были записаны раньше, какие-то — позже.
Но остается и журнал, который можно прочитать и выполнить повторно те операции, которые уже были выполнены до сбоя, но результат которых не успел дойти до диска.

Журналировать нужно все операции, при выполнении которых есть риск получить несогласованность на диске в случае сбоя.

В частности, в журнал записываются следующие действия:

изменение страниц в буферном кеше (как правило, это страницы таблиц и индексов) — так как измененная страница попадает на диск не сразу;
фиксация и отмена транзакций — изменение статуса происходит в буферах XACT и тоже попадает на диск не сразу;
файловые операции (создание и удаление файлов и каталогов, например, создание файлов при создании таблицы) — так как эти операции должны происходить синхронно с изменением данных.

В журнал НЕ записываются:

операции с нежурналируемыми (unlogged) таблицами — их название говорит само за себя;
операции с временными таблицами — нет смысла, поскольку время жизни таких таблиц не превышает времени жизни создавшего их сеанса.

Логически журнал можно представить себе как последовательность записей различной длины. Каждая запись содержит данные о некоторой операции, предваренные стандартным заголовком.

В заголовке, в числе прочего, указаны:

номер транзакции, к которой относится запись;
менеджер ресурсов — компонент системы, ответственный за запись;
контрольная сумма (CRC) — позволяет определить повреждение данных;
длина записи и ссылка на предыдущую запись.

На диске журнал хранится в виде файлов в каталоге $PGDATA/pg_wal.
Каждый файл по умолчанию занимает 16 Мб. Размер можно увеличить, чтобы избежать большого числа файлов в одном каталоге.

Журнальные записи попадают в текущий использующийся файл и когда он заканчивается — начинает использоваться следующий.

В разделяемой памяти сервера для журнала выделены специальные буферы. Размер журнального кэша задается параметром wal_buffers (значение по умолчанию подразумевает автоматическую настройку: выделяется 1/32 часть буферного кеша).

Журнальный кэш устроен наподобие буферного кэша, но работает преимущественно в режиме кольцевого буфера: записи добавляются в «голову», а записываются на диск с «хвоста».

Для того, чтобы сослаться на определенную запись, используется тип данных pg_lsn (LSN = log sequence number) — это 64-битное число, представляющее собой байтовое смещение до записи относительно начала журнала.

LSN выводится как два 32-битных числа в шестнадцатеричной системе через косую черту.

Можно узнать, в каком файле мы найдем нужную позицию, и с каким смещением от начала файла:

SELECT file_name, upper(to_hex(file_offset)) file_offset
FROM pg_walfile_name_offset('0/331E4E64');

Журнальные файлы можно увидеть специальной функцией:

SELECT * FROM pg_ls_waldir() WHERE name = '<number>';

Когда мы стартуем сервер, первым делом запускается процесс postmaster, а он, в свою очередь, запускает процесс startup, задача которого — обеспечить восстановление, если произошел сбой.

Чтобы определить, требуется ли восстановление, startup заглядывает в специальный управляющий файл $PGDATA/global/pg_control и смотрит на статус кластера. Мы можем и сами проверить статус с помощью утилиты pg_controldata:

sudo /usr/lib/postgresql/14/bin/pg_controldata -D /var/lib/postgresql/14/main/ | grep state

У аккуратно остановленного сервера статус будет «shut down». Если сервер не работает, а статус остался «in production», это означает, что СУБД упала и тогда автоматически будет выполнено восстановление.

Для восстановления процесс startup будет последовательно читать журнал и применять записи к страницам, если в этом есть необходимость. Необходимость можно проверить, сравнив LSN страницы на диске с LSN журнальной записи.

Если LSN страницы оказался больше, то запись применять не нужно. А на самом деле — даже и нельзя, потому что записи рассчитаны на строго последовательное применение.

Изменение страниц при восстановлении происходит в буферном кэше, как при обычной работе — для этого postmaster запускает необходимые фоновые процессы.

Аналогично журнальные записи применяются и к файлам: например, если запись говорит о том, что файл должен существовать, а его нет — файл создается.

Ну и в самом конце процесса восстановления все не журналируемые таблицы перезаписываются «пустышками» из своих init-слоев.
«По классике» процесс восстановления состоит из двух фаз. На первой фазе (roll forward) накатываются журнальные записи, при этом сервер повторяет всю потерянную при сбое работу.

На второй (roll back) — откатываются транзакции, которые не были зафиксированы на момент сбоя.

Но PostgreSQL не нуждается во второй фазе. Благодаря особенностям реализации многоверсионности транзакции не надо откатывать физически; достаточно того, что в XACT не будет установлен бит фиксации.

Конечно это достаточно сильное упрощение происходящего. Поэтому если интересно остальное можно прочитать в документации.

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

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

Для начала метакоманды:

\l+

\d+

Селекты
Размер структур PostgreSQL

SELECT pg_database.datname, 
pg_database_size(pg_database.datname) AS size 
FROM pg_database;

SELECT pg_database.datname, 
pg_size_pretty(pg_database_size(pg_database.datname)) AS size 
 FROM pg_database;

SELECT pg_size_pretty(pg_total_relation_size('"<schema>"."<table>"'));

SELECT pg_size_pretty(pg_database_size('example'));

SELECT
    table_name,
    pg_size_pretty(table_size) AS table_size,
    pg_size_pretty(indexes_size) AS indexes_size,
    pg_size_pretty(total_size) AS total_size
FROM (
    SELECT
        table_name,
        pg_table_size(table_name) AS table_size,
        pg_indexes_size(table_name) AS indexes_size,
        pg_total_relation_size(table_name) AS total_size
    FROM (
        SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
        FROM information_schema.tables
    ) AS all_tables
    ORDER BY total_size DESC
) AS pretty_sizes;

Проверка на блокировки:

SELECT
  t.relname,
  l.locktype,
  page,
  virtualtransaction,
  pid,
  mode,
  granted
FROM
  pg_locks l,
  pg_stat_all_tables t
WHERE
  l.relation = t.relid
ORDER BY
  relation asc;

SELECT
  S.pid,
  age(clock_timestamp(), query_start),
  usename,
  query,
  L.mode,
  L.locktype,
  L.granted
FROM
  pg_stat_activity S
JOIN
  pg_locks L ON S.pid = L.pid
ORDER BY
  L.granted,
  L.pid DESC

Домашнее задание

Напишите роль для установки PostgreSQL и плейбук, устанавливающий СУБД на ВМ2.

Роль должна выполнять следующие операции:

Установку пакета PostgreSQL из стандартных репозиториев Ubuntu (подключать deb-репозитории PostgreSQL не нужно);
Настройку директорий с данными PostgreSQL (роль должна принимать директорию с данными как параметр, значение по умолчанию — /data/postgres/data);
Первичную инициализацию БД при помощи команды pg_ctl initdb -D $PGDATA;
Конфигурация PostgreSQL при помощи файла $PGDATA/postgresql.conf (роль должна принимать параметры конфигурации PostgreSQL в виде словаря);
Конфигурация параметров авторизации при помощи файла $PGDATA/pg_hba.conf (роль должна принимать последовательность правил авторизации в виде списка словарей);
Запуск PostgreSQL как systemd-службы;
Проверку работоспособности PostgreSQL при помощи выполнения в СУБД запроса SELECT 1;
Создание пользовательских БД;
Создание пользователей;
Установку postgres-exporter на цели;
Запуск postgres-exporter как systemd-службы.

Для проверки предоставьте

Ссылку на исходный код роли в репозитории оформленном в соответствии структуры из занятия 3;
Ссылку на исходный код плейбука в том же репозитории.

Пояснения

Права директорий PostgreSQL
В пункте 2 убедитесь, что создаете папку /data с владельцем root: root и правами 0755, чтобы избежать ограничений доступа при просмотре корня файловой системы. Директорию /data/postgres создавайте с владельцем postgres: postgres и правами 0700.

Директива creates для отслеживания изменений в модулях command и shell
В третьей практике мы обсуждали один из способов выполнить модули command и shell идемпотентно — задать условие changed_when вручную. В третьем пункте такое решение неоптимально, т.к. оно полагается на парсинг вывода команды pg_ctl initdb.

Вместо него используйте директиву creates, которая принимает как аргумент путь на цели и считает, что указанный модуль должен создать этот путь. Т. е. задача считается changed, если до запуска этого пути не было, а после — появился. Если же путь существовал до запуска, то задача пропускается со статусом ok.

Пример:

- name: Create directory via command
  ansible.builtin.command:
    cmd: mkdir /data
  creates: /data

Не делайте так, используйте модуль file со state: directory.

Сохранение значений по умолчанию в конфиге PostgreSQL

Обратите внимание, что initdb создает непустой конфиг postgresql. conf и вам нужно позаботиться о сохранении значений по-умолчанию. Вы можете подойти к этой задаче двумя способами:

Генерация полного конфига при помощи template. Значения по-умолчанию подставляются из приватных переменных роли путем слияния с пользовательскими параметрами;
Точечная замена конфигурационных опций при помощи модуля lineinfile;

Первый способ выглядит так:
Объявляем пользовательские переменные в defaults/main.yml:

postgres__opts:
  param1: value1
  param2: value2

И параметры по-умолчанию в vars/main.yml (скопируйте их из сгенерированного конфига):

__opts_default:
  param1: value1_default
  param3: value3_default

Затем сливаем их при помощи фильтра combine в шаблоне templates/postgresql.conf.j2:

{\% set opts = __opts_default | combine(postgres__opts) \%}

И копируем конфиг при помощи модуля template.

Во втором способе будем использовать модуль lineinfile, который использует регулярное выражение, чтобы найти строку с нашей опцией и заменить ее на пользовательское значение.

Аналогично объявим пользовательские переменные в defaults/main.yml:

postgres__opts:
  param1: value1
  param2: value2

Теперь напишем цикл по параметрам с поиском каждого из них:

- name: Edit postgresql.conf
  ansible.builtin.lineinfile:
    path: "{\{ postgres__data_dir \}}/postgresql.conf"
    regexp: "^#?{\{ item.key | regex_escape() \}}\s*="
    line: "{\{ item.key \}} = {\{ item.value \}}"
    create: false
  loop: "{\{ postgres__opts | dict2items \}}"
  loop_control:
    label: "{\{ item.key \}}"

Здесь мы использовали следующие фильтры:

dict2items — конвертирует словарь в список словарей с ключами key и value, нужен для использования словарей как параметров циклов;

regex_escape () — экранирование спец-символов регулярных выражений в строке, нужно чтобы все символы в item. key воспринимались буквально.

Коллекция community.postrgesql

Для выполнения пунктов 5, 7, 8, 9 можете воспользоваться коллекцией community.postgresql, установку которой обсудили на пятой практике. Обратите внимание, что коллекция требует установки на цели Python пакета psycopg2. Установите его в первом пункте вместе с пакетами PostgreSQL, если планируете использовать. Название пакета в дистрибутиве Ubuntu: python3-psycopg2.

Значения по умолчанию в модулях community. postgresql

Довольно часто мы предоставляем в ролях интерфейсы к модулям со своими значениями по-умолчанию. Чтобы не дублировать эти значения в фильтре default так:

- name: Create Databases
  community.postgresql.postgresql_db:
    name: "{\{ item.name \}}"
    encoding: "{\{ item.encoding | default('') \}}"
    state: "{\{ item.state | default('present') \}}"
  loop: "{\{ postgres__databases \}}"

используйте специальную константу omit — если эта константа является результатом шаблона в параметре модуля, то Ansible игнорирует этот параметр будто он не был написан вовсе.

Получим:

- name: Create Databases
  community.postgresql.postgresql_db:
    name: "{\{ item.name \}}"
    encoding: "{\{ item.encoding | default(omit) \}}"
    state: "{\{ item.state | default(omit) \}}"
  loop: "{\{ postgres__databases \}}"

Таким образом, если элементом цикла будет {"name": «database"}, то после подстановки шаблона получим такую задачу:

- name: Create Databases
  community.postgresql.postgresql_db:
    name: "database"