Тема 01 «Установка и управление сервером»

TLDR: Тут какая то жесть, ребята ставят все из исходников, в обычной ситуации это просто:

apt update && apt install -y postgresql postgresql-contrib sudo
pg_ctlcluster 12 main start
sudo -u postgres psql -c 'select 1'

Примечания:

  • проверял в docker run -it --rm ubuntu bash
  • в postgresql-contrib всякие доп модули которые можно будет включать (аля uuid, crypto, etc)

Установка из исходников

# dependencies
apt update && apt install -y build-essential libreadline-dev zlib1g-dev flex bison libxml2-dev libxslt-dev libssl-dev libxml2-utils xsltproc git sudo
# sources
git clone git://git.postgresql.org/git/postgresql.git
cd postgresql
# optional to cleanup previous run: make distclean
./configure
make
make install
# postgres user
adduser postgres
# data folder
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
# switch to postgres user
su - postgres
# initialize
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
# start
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
# create database
/usr/local/pgsql/bin/createdb test
# test
/usr/local/pgsql/bin/psql test -c 'select 1'
# stop (fast|smart|immediate)
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data stop -m fast

Примечания:

Тема 02 «Использование psql»

Подключение

  • psql -d база -U роль -h узел -p порт - снаружи
  • => \c[onnect] база роль узел порт - изнутри
  • => \conninfo - информация о текущем подключении

Получение справки

  • psql --help опции запуска

  • в самом psql

    • => \? - список доступных команд
    • => \? variables - список переменных psq
    • => \h[elp] - список команд SQL
    • => \h команда - синтаксис команды SQL, напр.: \h CREATE TABLE
    • => \q - выход из psql

Файлы

  • psqlrc - общий системный файл
  • ~/.psqlrc - пользовательский файл
  • ~/.psql_history - история команд

Формат вывода запросов

  • \t - вывод названия колонок и кол-ва строк
  • \a - align
  • \x - аля Format-List из PowerShell когда каждая строка будет выведена в виде отдельной pivot таблички
  • \timing - вывод времени
  • \pset fieldsep ' ' - разделитель, например если выполнить \t, \a, \pset fieldsep ',' мы по сути получим csv

Вызов системных команд из psql

=> \! pwd
=> \! uptime
=> \setenv TEST Hello
=> \! echo $TEST

Перенаправление вывода

последняя команда выключит перенаправление

=> \o test.txt
=> select * from users;
=> \! cat test.txt
=> \o

Запуск скрипта из файла

  • psql < filename
  • psql -f filename
  • => \i filename

Переменные

  • Базовые операции

    • => \set TEST Hello
    • => \echo :TEST
    • => \unset TEST
    • => \set - выведет список всех переменных (аналог env)

  • Использование в запросах

    • select now() as curr_time \gset - примечание: запрос должен возвращать только одну запись
    • => \echo :curr_time - можем переиспользовать

Тема 03 «Конфигурирование»

postgresql.conf

Основной файл конфигурации

  • читается один раз при старте сервера
  • если праметр указан несколько раз, применяется последнее значение

Расположение

  • SHOW config_file;
  • при сборке по умолчанию - в каталоге с данными PGDATA

Дайствия при изменении

Файл надо перечитать одним из способов:

  • pg_ctl reload
  • kill -HUP - это нужно отрпавлять главному процессу
  • select pg_reload_conf();

Примечание: изменения некоторых из параметро требует перезапуск сервера

postgres.auto.conf

Файл конфигурации, управляемый SQL

  • ALTER SYSTEM SET параметр TO значение; - добавляет или изменяет строку
  • ALTER SYSTEM RESET параметр; - удаляет строку
  • ALTER SYSTEM RESET ALL; - удаляет все строки
  • Считывается после postgresql.conf
  • Распологается в каталоге с данными PGDATA
  • Даействия при изменении аналогичто postgresq.conf

Текущие настройки

  • pg_settings - системная вьюха со всеми настройками
  • pg_file_settings - то что было вычитанно с файлов конфигураций

Пример запроса к pg_file_settings:

select sourceline, name, setting, applied from pg_file_settings where sourcefile like '%postgresql.conf';

Примечание: столбец applied говорит не о текущем статусе настройки, а о том может ли быть параметр изменен без перезагрузки сервера, посмтреть текущее (актуальное) значение можно вот так:

select name, setting, unit, boot_val, reset_val, source, sourcefile, sourceline, pending_restart, context from pg_settings where name = 'work_mem'\gx

Примечания:

  • setting, unit - применимо для настроек имеющих unit например объем памяти или время, для строк, unit остается пустым

  • boot_val - значение по умолчанию

  • reset_val - значение которое мы можем поменять, будет применино при вызове ALTER SYSTEM RESET

  • source, sourcefile, sourceline - будут заполнены если конфигурация была вычитанна с файла, если source - default - то это значение по умолчанию

  • pending_restart - требуется ли перезапуск сервера для применения настройки

  • context

    • internal - зменить нельзя, задано при установке
    • postmaster - требуется перезапус сервера
    • sighup - требуется перечитать файлы конфиграции
    • superuser - суперпользователь может изменить для своего сеанса
    • user - любой пользователь может на ходу менять значения этого параметра

Представление pg_file_settings прям на лету перечитывает файл:

\! echo work_mem=12MB >> /usr/local/pgsql/data/postgresql.conf
\! echo work_mem=8MB >> /usr/local/pgsql/data/postgresql.conf
select sourceline, name, setting, applied from pg_file_settings where name = 'work_mem';
select pg_reload_conf();
select name, setting, unit, boot_val, reset_val, source, sourcefile, sourceline, pending_restart, context from pg_settings where name = 'work_mem'\gx

В примере мы дописываем две опции, запрос, выведет обе, но у первой applied будет false.

После применения, boot_val останеться 4мб, а вот reset_val уже станет 8мб, а так же обновиться source.

Пример через ALTER SYSTEM:

ALTER SYSTEM SET work_mem TO '16MB';
\! cat /usr/local/pgsql/data/postgresql.auto.conf
SHOW work_mem;
select pg_reload_conf();
SHOW work_mem;
select name, setting, unit, boot_val, reset_val, source, sourcefile, sourceline, pending_restart, context from pg_settings where name = 'work_mem'\gx

Откат, у далением строки из файла:

ALTER SYSTEM RESET work_mem;
select pg_reload_conf();
SHOW work_mem;

По скольку work_mem это пользовательская переменная можно устанавливать еще вот так:

  • SET work_mem TO '24MB';
  • SELECT set_config('work_mem', '32MB', false); - примечание - последний параметр переключает настройку на весь сеанс или только для следующего запроса

Посмотреть значение параметра:

  • SHOW work_mem;
  • SELECT current_setting('work_mem');
  • SELECT name, setting, unit FROM pg_settings WHERE name = 'work_mem';

Пользовательские параметры

Этот же механизм может быть использован для пользовательских параметров используемых в приложении, например:

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

Примечания:

  • в название переменной обязательно должна быть точка, что бы postgres мог отличить их от системных
  • такие переменные можно сложить прямо в postgres.conf
  • set_config - не сохраняет на файл, настройка только в текущем сеансе

Практика

Получить список параметров (и их значений), для изменения которых требуется перезапуск сервера

select name, setting from pg_settings where context = 'postmaster';

В файле postgresql.conf устанвить значения для параметра listen_address в * и применить

ALTER SYSTEM SET listen_addresses TO '*';
select pg_reload_conf();
SHOW listen_addresses;
\q
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data restart

Тема 04 «Общее устройство PostgreSQL»

Транзакции

  • атомарность - все или ничего
  • согласованность - ограничения целостности и пользовательские ограничения
  • изоляция - влияние параллельных процессов
  • долговечность - сохранность данных дажепосле сбоя

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

  • разбор - синтаксический (нет ли очепяток) и семантический (существуют ли запрашиваемые таблички) анализ (последний использует системный каталог)
  • трансформация - правила, используются для трансформации полученного AST
  • планирование - статистика, используется для построения эфективного плана выполнения
  • выполнение - данные

Процессы и память

  • postmaster (сейчас называется просто postgres) - основной процесс, основной задачей которого является запуск и контроль всех остальных процессов, так же именно он обрабатывает входящие подключения от клиентов, при поступлении нового запроса, он порождает новый backend и передает подключение ново созданному backend'у.
  • backend
  • фоновые процессы

Тема 05 «Изоляция и многоверсионность»

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

Наличие нескольких версий одной и той же строки

  • версии различаются временем действия
  • время = номер транзакции (номера выдаются по возврастанию)

Снимок данных

  • номер транзакции - определяет момент времени
  • список активных странзакций - что бы не смотреть на еще не зафиксированные изменения

Блокировки

Блокировки строк

  • чтение никогда не блокирует строк
  • изменение строки блокирует ее для изменений, но не для чтений

Блокировки таблиц

  • запрещают изменение или удаление таблицы, пока с ней идет работа
  • запрещают чтение таблицы при перестроении или перемещении и т.п.

Время жизни блокировок

  • устанавливаются по мере необходимости или вручную
  • снимаются автоматически при завершении транзакции

Статус транзакций (xact)

Статус транзакций

  • служебная информация, два бита
  • специальные файлы на диске
  • буферы в общей памяти

Фиксация

  • устанавливается бит “транзакция зафиксирована”

Откат

  • устанавливается бит “транзакция прервана”
  • выполняется так же быстро, как и фиксация (не нужен откат данных)

Очистка

Старые версии строк храняться вместе с актуальными

  • со временем размер таблиц и индексов увеличивается
  • Процесс очистки (vacuum)
  • удалет версии строк, которые уже не нужны (то есть не видны ни в одном снимке данных)
  • работает параллельно с остальными процессами
  • удаленные версии отставляют в файла данных “дыры”, которые затем используются для новых версий строк

Полная очистка

  • полностью перестраивает файлы данных, делая их компактными блокирует таблицу на время работы

Примечание: важно что бы auto vacuum постоянно подчищал файлы, иначе, если файл сильно вырос и нужно его пожать, необходима полная очистка со всеми вытекающими

Автоочистка

  • Autovacuum launcher - фоновый процесс реагирует на активность изменения данных
  • Autovacuum worker - запускается по необходимости, выполняет очистку

Уровни изоляции

  • Read uncommitted - не поддерживается postgre sql - позволяет читать не зафиксированные данные
  • Read commited - используется по умолчанию - снимок строиться на момент начала оператора, одинаковые запросы могут каждый раз получать разные данные
  • Repeatable read - снимок строиться на момент начала первого оператора транзакции, транзакция может завершиться ошибкой сериализации
  • Serializable - полная изоляция, но дополнительные накладные расходы, транзакция может завершиться ошибкой сериализации

Демо

CREATE TABLE t(s text);
INSERT INTO T VALUES ('Первая версия');

Начянет транзакцию в выведем ее номер:

BEGIN;
SELECT txid_current();

Транзакция видит первую (и пока единственную) версию строки:

SELECT *, xmin, xmax FROM t;

Примечания:

  • xmin, xmax - служебные поля, есть везде, не выводяться звездочкой
  • xmin - номер транзакции которая создала версию этой строки
  • xmax - номер транзакции которая удалила версию
  • в примере, xmin меньше чем мой номер, т.к. вставка произошла ранее, в предыдущей транзакции.

Запускаем второе подключение:

BEGIN;
SELECT txid_current();
SELECT *, xmin, xmax FROM t;
UPDATE t SET s = 'Вторая версия';
SELECT *, xmin, xmax FROM t;

При этом в первом сеансе, версия все еще старая, но зато появился xmax. Дальше если сделаем commit то первый сеанс увидит новую версию. Если и первый сеанс сделает commit, то первая версия останеться “мусорной” и не будет доступна никому.

Так же при коммите, в обоих сеансах max обнулилось.

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

Итоги

  • В файлах данных могут храниться несколько версий каждой строки
  • Транзакции работают со снимком данных - согласованным срезом на определенный момент времени
  • Писатели не блоикруют читателей, читатели не блокируют никого
  • Время создания снимка виляет на уровень изоляции
  • Версии строк накапливаются, поэтому нужна периодическая очистка

Тема 06 «Буферный кэш и журнал»

Буферный кэш

речь об оперативной памяти

  • массив буферов - страница данных (8кб), доп. информация
  • блокировки в памяти - для совместного доступа

Вытеснение

Вытеснение редко используемых страниц - “гразный” буфер записывается на диск, на освободившееся место читается другая страница

Журнал предзаписи (WAL)

Проблема: при сбое теряются данные из оперативной памяти, не записанные на диск

Журнал

  • поток информации о выполняемых действиях, позволяющей повторно выполнить потерянные при сбое операции
  • запись попадает на диск раньше, чем измененные данные

Журнал защищает

  • страницы таблиц, индексов и других объектов
  • статус транзакций (xact)

Журнал не защищает

  • временные и нежурналируемые таблицы

Производительность

Синхронный режим - запись при фиксации обслуживаеющий процесс

Асинхронный режим - фоновая запись, walwriter

Контрольная точка

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

Востановление при сбое - начинается с последней контрольной точки, последовательно проигрываются записи, если изменений нет на диске

Основные процессы

  • walwriter - запись журнала
  • checkpointer - контрольная точка (сброс всех грязных буферов)
  • bgwriter - фоновая запись (сброс части грязных буферов)
  • обслуживающие процессы - сброс вытесняемого грязного буфера

Демо

Журнал WAL - непрерывный поток записей, каждая имеет номер LSN (Log Sequence Number). 64 разрядное число, смещение записи в байтах относительно начала журнала.

Текущая позиция

select pg_current_wal_lsn(); -- 0/16BF818
CREATE TABLE t(n integer);
INSERT INTO t SELECT gen.id FROM generate_series(1,1000) AS gen(id);
select pg_current_wal_lsn(); -- 0/16E5E90
SELECT '0/16E5E90'::pg_lsn - '0/16BF818'::pg_lsn; -- 157304 - 157kb журнала ушло в логе на все про все

Физически журнал храниться в файлах по 16mb в отдельном каталоге (PGDATA/pg_wal). Так же можно посмотреть прямо из базы:

SELECT * FROM pg_ls_waldir() ORDER BY name;

Вернет список файлов с логами, размером и датой изменения, база будет их подчищать в фоне по мере надобности

Уровни журнала

  • minimal - гарантия востановления после сбоя
  • replica (по умолчанию) - резервное копирование, репликация: передача и проигрывание журнала на другом сервере
  • logical - логическая репликация: информация о добавлении, изменении и удалении табличных строк

Итоги

  • Буферный кэш существенно ускоряет работу, уменьшая число дисковых операций

  • Надежность обеспечивается журналированием

  • Размер журнала ограничен благодаря контрольным точкам

  • Журнал удобен и ипользуется во многих случая

    • для востановления после сбоя
    • при резервном копировании
    • для репликации между серверами

Тема 07 «Базы данных и схемы»

Кластер баз данных

Инициализация кластера создает три базы данных.

Новая база всегда клонируется из существующей.

  • postgres - подключение по умолчанию, служебная.
  • template0 - не меняется
  • template1 - общие изменения (копируется от сюда)

Примечание: например мы всегда используем uuid, есть смысл включить его в template1 что бы он был доступен во всех последующих базах.

Схемы

Пространство имен для объектов внутри базы данных - каждый объект принадлежит какой-либо схеме

Задачи:

  • разделение объектов на логические группы
  • предотвращение конфликта имен между приложениями

Схема и пользователь - разные сущности

Базы и схемы кластера

  • pg_catalog - системный каталог (есть в каждой базе данных)
  • public - общая схема по умолчанию (ака dbo)

Путь поиска

Обределение схемы объекта

  • квалифицированно имя (схема.имя) явно определеяет схему
  • имя без квалификатора проверяется в схемах, указанных в пути поиска

Путь поиска

  • задаеться параметром search_path (перечисление схем через запятую)
  • исключаются несуществующие схемы и схемы, к которым нет доступа
  • подставляются неявно подразумеваемые схемы
  • реальное значение показывает функцися current_schemas
  • первая явно указанная в пути схема используется для создания объектов

Специальные схемы

Схема public

  • по умолчанию входит в путь поиска
  • если ничего не менять, все объекты будут в этой схеме

Схема, совпадающая по имени с пользователем

  • по умолчанию входит в путь поиска, но не существует
  • если создать, объекты пользователя будут в этой схеме

Схема pg_catalog

  • схема для объектов системного каталога
  • если pg_catalog нет в пути, она неявно подразумевается первой

Временные таблицы

  • существуют на время сеанса или транзакции
  • не журналируются (невозможно восстановление после сбоя)
  • не попадают в общий буферный кэш

Схема pg_temp_N

  • автоматически создается для временных таблиц
  • pg_temp - ссылка на конкретную временную схему данного сеанса
  • если pg_temp нет в пути, она неявно подразумевается самой первой
  • по окончании сеанса все объекты временной схемы удаляются, а сама схема остается и повторно используется для других сеансов

Демо

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

psql -l
=> \l
=> select datname, datistemplate, datallowconn, datconnlimit from pg_database;

Примечание:

  • datistemplate - является ли база данных шаблоном
  • datallowconn - разрешены ли соединения с базой данных
  • datconnlimit - максимально количество соединений (-1 - без ограничений)

Создание базы из шаблона

Подключимся к шаблонной базе template1

\c template1

Проверим что функция digest не включена

select digest('hello', 'md5');

Включим расширение

create extension pgcrypto;

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

\c postgres

И создадим базу

create database acme;

После чего можно переключиться на нее и проверить что md5 работает

Дополнительные примеры

ALTER DATABASE acme TO foo; -- переименование базы
ALTER DATABASE acme CONNECTION LIMIT 10; -- выставляем лимит подключений
SELECT pg_database_size('acme'); -- размер базы данных
SELECT pg_size_pretty(pg_database_size('test')); -- размер базы данных
=> \dn -- выведет списох схем
CREATE SCHEMA demo;
SHOW search_path; -- выводит путь поиска
SELECT current_schemas(true); -- выводит актуальный путь поиска
=> \dt -- выведет список табличек
ALTER TABLE test SET SCHEMA demo; -- переместить табличку в другую схему
=> SET search_path = public, app; -- поменять путь поиска (на текущий сеанс)
ALTER DATABASE test SET search_path = public, app; -- поменять путь поиска для базы (персистентно)
CREATE TEMP TABLE t(s text); -- создание времянки, после чего \dt - выведет ее, схема будет pg_temp_4, POI - pg_temp_4 добавилась в начало пути поиска и как следствие может перекрыть существующую табличку, что бы достучаться до оригинальной, нужно явно указзывать схему
CREATE VIEW v AS SELECT * FROM pg_temp.t; -- создание временной вьюхи (по сути та же временная табличка)
DROP SCHEMA app; -- удаление пустой схемы
DROP SCHEMA app CASCADE; -- удаление схемы и всех ее объектьов
DROP DATABASE acme; -- удаление базы

Итоги

  • Логически

    • кластер содержит базы данных
    • базы данных - схемы
    • схемы - конкретные объекты (таблицы, индексы и т.п.)

  • Базы данных создаются клонированием существующих

  • Схема указывается явно или определяется по пукти поиска

  • Некоторые схемы имеют специальное назначение

Тема 08 «Системный каталог»

Системный каталог

Набор таблиц и представлений, описывающий все объекты кластера баз данных

Схемы

  • основная схема - pg_catalog
  • альтернативное представление - information_schema (стандарт SQL)

SQL-доступ

  • просмотр: SELECT
  • изменение: CREATE, ALTER, DROP

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

Специальные типы данных

OID - тип для идентификатора объекта

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

Reg-типы

  • псевдонимы OID для некоторых таблиц системного каталога (regclass для pg_class и т.п.)
  • приведение текстового имени объекта к типу OID и обратно

Демо

SELECT * FROM pg_database WHERE datname = 'test' \gx -- инфо о базе
SELECT * FROM pg_namespace WHERE nspname = 'public' \gx -- инфо о схеме
SELECT relname, relkind, relnamespace, relfilenode, relowner, reltablespace FROM pg_class WHERE relname = 'users'; -- инфо об relation, в постгрес, все все в этой табличке, а именно: таблицы, представления (вклюючая материализированные), индексы, последовательности
SELECT schemaname, tablename, tableowner, tablespace FROM pg_tables WHERE schemaname = 'public';

Примечания:

  • по сути pg_tables, pg_views это представление поверх pg_class джойнящее данные
  • сокращения \dt, \dv, \di - это сокращения обращающиеся в те же представления
  • у сокращений можно подставить плюсик что бы получить доп инфо - \dt+
  • можно вывести инфо о конкретном объекте \dt+ users
  • \df pg*size - выведет список доступных ф-ий, добавили pgsize что бы хоть что то вывело, т.к. в базе нет пользовательских ф-ий
  • \sf pg_catalog.pg_database_size(oid) - вывод исходников
  • \set ECHO_HIDDEN on - трюк, после него вызов всяких \dt будет выводить запрос которым он был получен

Получение ифы о колонках

SELECT a.attname, a.atttypid FROM pg_attribute a WHERE a.attrelid = (
  SELECT oid FROM pg_class WHERE relname = 'users'
) AND a.attnum > 0;

SELECT attname, atttypid::regtype FROM pg_attribute WHERE attrelid = 'users'::regclass AND attnum > 0;

Примечания:

  • если убрать attnum получим xmin, xmax и т.п.
  • за вместо того что бы ходить за oid можно сделать 'users'::regclass
  • то же самое касается и типов a.attypid::regtype

Итоги

  • Системный каталог - метаинформация о кластере в самом кластере
  • SQL-доступ и дополнительные команды psql
  • Часть таблиц системого каталога храниться в базах данных, часть - общая для всего кластера
  • Системный каталог использует специальные типы данных

Тема 09 «Табличные пространства»

  • Табличные пространства позволяют размещать таблички на разных дисках
  • Существуют pg_global, pg_default
  • В одной базе, объекты могут храниться в разных пространствах
  • pg_global - хранит объекты общие для всех баз данных (читай системные вьюхи)
  • pg_global - $PGDATA/global/
  • pg_default - $PGDATA/base/dboid/, где dboid - идентификатор базы
  • свое пространство - /путь-к-каталогу/ver/dboid/ - будет ссылка в $PGDATA/pg_tblspc/tsoid
  • select * from pg_tablespace; - вывести список пространств

Создания своего пространства

\! mkdir /home/postgres/test -- be sure that it is owned by postgres
CREATE TABLESPACE test LOCATION '/home/postgres/test';
\db -- выведет созданное пространство
CREATE DATABASE bar TABLESPACE test; -- создаст базу с пространством по умолчанию
CREATE TABLE t2(n numeric) TABLESPACE pg_default; -- все еще можно указывать для таблички
ALTER TABLE t1 SET TABLESPACE pg_default; -- важно - это физическое перемещение файлов с блокировкой таблички
ALTER TABLE ALL IN TABLESPACE pg_default SET TABLESPACE test; -- переместит все таблички из pg_default в test
SELECT pg_size_pretty(pg_tablespace_size('test')); -- размер пространства
DROP TABLESPACE ts; -- удалить пустое пространство, нет возможности сделать CASCADE, т.к. в пространтсве могут быть объекты из разных баз

Удаление пространства

select oid from pg_tablespace WHERE spcname = 'test'; -- получили id пространства
select datname from pg_database where oid in (select pg_tablespace_databases(234)); -- список баз использующих это пространство
select relnamespace::regnamespace, relname, relkind from pg_class where reltablespace = 123; -- список объектов живующих в пространстве
select count(*) from pg_class where reltablespace = 0; -- для случаев когда пространство по умолчанию
ALTER DATABASE acme SET TABLESPACE pg_default; -- переедет все объекты в новое пространство

Вопросы

  • Если поломается диск на котором есть какое либо пространство - свалиться весь постгрес
  • Табличное пространство может быть сетевой файловой системой, но это считается плохой идеей

Тема 10 «Низкий уровень»

Слои объекта

  • main - NNN, NNN.1, NNN.2
  • fsm - NNN_fsm, NNN_fsm.1
  • vm - NNN_vm

Каждый файл занимает не более 1гб (для совместимости с файловыми системами которые не умеют файлы больше)

Страницы файцлов

Каждый файл разбит на так называемые странички, по умолчанию 8кб, каждая имеет заголовок и данные

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

Слои

Основной слой (main)

  • собственно данные (версии строк)
  • существует для всех объектов

Инициализации (init)

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

Карта с вободного пространтсва (fsm)

  • отмечает свободное пространство в страницах после очистки
  • используется при вставке новый хверсий строк
  • существует для всех объектов

Карта видимости (vm)

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

Поля xmin, xmax храняться в таблице, а не в индексе, соотв даже если запрос только на индекс, все равно нужно сходить в табличку, что бы понять показывать это или нет, вот тут visibility map позволяет это обойти, что позволяет выполнять index only scan

TOAST

  • Версия строки должна помещаться на одну страницу (8kb)

    • можно сжать часть атрибутов
    • или вынести в отдельную TOAST-таблицу
    • или сжать и вынести одновременно

  • TOAST-таблица

    • схемы pg_toast, pg_toast_temp_N
    • табличца поддержана собственным индексом
    • “длинные” атрибуты разделены на части размером меньше страницы
    • читается только при обращении к “длинному” атрибуту
    • собственная версионность
    • работает прозрачно для приложения

TOAST - the oversized attribute storage technique

Примечание - если поменяли версию строки в малой табличке, но новая и старая версии просто ссылаются на одну и ту же версию аттрибута(ов) в TOAST табличке

Расположение файлов

CREATE DATABASE data_lowlevel;
\c data_lowlevel
CREATE TABLE t(id serial PRIMARY KEY, n numeric);
INSERT INTO T(n) SELECT g.id FROM generate_series(1,10000) AS g(id);
SELECT pg_relation_filepath('t'); -- путь где храняться файлы таблички, base/16431/16433, первая цифра - oid базы, второе - идентификатор таблички
SELECT OID FROM pg_database WHERE datname = 'data_lowlevel'; -- oid базы
SELECT relfilenode FROM pg_class WHERE relname = 't'; -- идентификатор для таблички
\! ls -lah /usr/local/pgsql/data/base/16431/16433* -- выведет список файлов
VACUUM t; -- ручной запуск авто очистки для таблички
SELECT pg_relation_filepath('t_pkey'); -- путь к индексу, base/16431/16439
\! ls -lah /usr/local/pgsql/data/base/16431/16439* -- fsm у индекса может быть, а vm - нет
SELECT pg_relation_filepath('t_id_seq'); -- последовательность base/16431/16432
\! ls -lah /usr/local/pgsql/data/base/16431/16432*
SELECT pg_relation_size('users', 'main') main, pg_relation_size('users', 'fsm') fsm, pg_relation_size('users', 'vm') vm; -- размер
select pg_table_size('users'); -- сумма всех слоев таблички
select pg_indexes_size('users'); -- размер индексов на табличке
select pg_total_relation_size('users'); -- размер таблички + индексов

oid2name

  • oid2name - выведет базы и их oid
  • oid2name -d data_lowlevel - выведет таблички в базе и их filenode
  • oid2name -d data_lowlevel -s - вывеедт пространства в базе и их oid
  • oid2name -d data_lowlevel -t t - выведет filenode таблички
  • oid2name -d data_lowlevel -f 16514 - выведет табличку по filenode

Пример TOAST

CREATE DATABASE toastdemo;
\c toastdemo
CREATE TABLE demo (s text);
SELECT pg_total_relation_size('demo'); -- 8192
INSERT INTO demo VALUES ('hello');
SELECT pg_total_relation_size('demo'); -- 16384
SELECT length( (123456789::numeric ^ 12345::numeric)::text ); -- демо, вот такую длинную строку мы будем вставлять, она не влезет в страницу, так что будет использована TOAST табличка
INSERT INTO demo(s) SELECT 123456789::numeric ^ 12345::numeric;
SELECT pg_total_relation_size('demo'); -- 131072 -- хм, у меня не сработало,
select relname, relfilenode from pg_class where oid = (select reltoastrelid from pg_class where relname = 'demo'); -- TOAST таблички, 16445
SELECT OID FROM pg_database WHERE datname = 'toastdemo'; -- 16441
\! ls -lah /usr/local/pgsql/data/base/16441/16445* -- main файл на 152кб, вот тут живет TOAST
SELECT relfilenode FROM pg_class WHERE relname = 'demo'; -- 16442
\! ls -lah /usr/local/pgsql/data/base/16441/16442* -- main файл таблички 8кб, остался маленьким, т.к. большие строки уехали в TOAST
\d+ demo -- выведет инфо о табличке, в частности стратегии хранения (storage)
ALTER TABLE demo ALTER COLUMN s SET STORAGE extended; -- изменить стратегию хранения (будет применяться только для новых данных, старые остаются как есть)

Стратегии хранения

  • plain - TOAST не применяется (тип имеет фиксированную длину)
  • main - приоритет сжатия
  • extended - применяется как сжатие, как и отдельное хранение
  • external - только отдельное хранение без сжатия (например если собираемся хранить jpeg - они уже пожаты, нет смысл за зря гонять CPU)

Итоги

  • объект представлен несколькими слоями
  • слой состоит из одного или нескольких файлов-сегментов
  • каждый файл разбит на страницы
  • для “длинных” версий строк используется TOAST

Примечание: вроде как TOAST подключается после превышения 2кб

Тема 11 «Мониторинг»

Средства ОС

Процессы

  • ps (grep postgres)
  • парааметр update_process_title для обновление статус процессов

Использование ресурсов

  • iostat, vmstat, sar, top…

Дисковое пространство

  • df, du, quota…

Статистика внутри базы

  • текущие активности системы

    • статистика - текущая активность и ожидания обслуживающих и фоновых процессов
    • параметр track_activities - включен по умолчанию

  • процесс сбора статистики

  • дополнительные расширения

Настройки процесса stats collector

  • статистика - обращения к таблицам и индексам (доступы, затронутые строки) - track_counts - включен по умолчанию и нужен для автоочистки
  • обращения к страницам - track_io_timing - выключен по умолчанию
  • вызовы пользовательских функций - track_functions - выключен по умолчанию

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

Для сброса статистики есть специальные системные ф-ии.

Дополнительная статистика

Расширения в поставке

  • pg_stat_statements - статистика по запросам (например топ тяжелых запросов)
  • pgstattuple - статистика по версиям строк (насколько распухли таблички и не пора ли их чистить)
  • pg_buffercache - состояние буферного кэша (используем ли мы целиком кэш, какие объекты храним)

Другие расширения

  • pg_stat_plans - статистика по планам запросов
  • pg_stat_kcache - статистика по процессору и вводу-выводу
  • pg_qualstats - статистика по предикатам

Журнал сообщений

  • настройка журнальных записей
  • ротация файлов журнала
  • анализ журнала

Приемник сообщений (log_destination = список) - может быть несколько

  • stderr - поток ошибок
  • csvlog - формат CSV (только с коллектором)
  • syslog - демон syslog
  • eventlog - журнал событий Windows

Коллектор сообщений (logging_collector = on)

  • позволяет собирать дополнительную информацию
  • никогда не теряет сообщения (в отличие от syslog)
  • записывает stderr и csvlog в log_directory/log_filename

Примечание: если не будет успевать записывать - может притормаживать остальные процессы

Настройки журнала

  • log_min_messages - сообщения определенного уровня
  • log_min_duration_statement - время выполнения длинных комманд (логирует долгие запросы)
  • log_duration - время выполнения команд
  • application_name - имя приложения
  • log_checkpoints - контрольные точки
  • log_(dis)connections - подключения и отключения
  • log_lock_waits - длинные ожидания (отслеживать блокировки)
  • log_statement - текст выполняемых команд
  • log_temp_files - использование временных файлов

TODO: можно ли менять эти настройки налету без перезагрузки

Ротация файлов журнала

С помощью коллектора сообщений

  • log_filename - маска имени файла
  • log_rotation_age - время ротации, мин
  • log_rotation_size - размер файла для ротации, кб - лучше не использовать, если ротация по времени
  • log_truncate_on_rotation = on - очищает файл при ротации (иначе будет делать append)

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

  • 'postgresql-%H.log', '1h' - 24 файла в сутки
  • 'postgresql-%a.log', '1d' - 7 файлов в неделю

Внешние средства

например 23 файлв в сутки с apache rotatelogs:

pgctl start | rotatelogs имяфайла 3600 -n 24

Анализ журнала

  • Сресдтва операционной системы - grep, awk,…
  • Специальные средства анализа - pgBadger - требует определенных настроек журнала

Внешний мониторинг

Универсальные системы мониторинга

  • zabbix, munin, cacti, …
  • в облаке - okmeter, newrelic, datadog, …

Системы мониторинга postgresql

  • PGObserver
  • PostgreSQL Workload Analyzer (PoWA)
  • Open PostgreSQL Monitoring (OPM)

Демо

ALTER SYSTEM SET track_io_timing = on;
ALTER SYSTEM SET track_functions='all';
SELECT pg_reload_conf();

В отдельном терминале эмулируем нагрузку

pgbench -i admin_monitoring
SELECT pg_stat_reset(); -- сброс статистики по таблицам
SELECT pg_stat_reset_shared('bgwriter'); -- сброс статистики
pgbench -T 10 admin_monitoring
VACUUM pgbench_accounts; -- подчисщаем табличку

Статистика

SELECT * FROM pg_stat_all_tables WHERE relid='users'::regclass \gx -- статистика по табличке, сколько строк вставленно, удалено, обновлено, когда срабатывала автоочистка, sec_scan - полное чтение, idx_scan - скаинрование индекса, можно смотреть как табличкой пользуются
SELECT * FROM pg_statio_all_tables WHERE relid='users'::regclass \gx -- сколько было операций io, heap_blks_read - чтений с диска, heap_blks_hit - попадание в кэш
SELECT * FROM pg_stat_all_indexes WHERE relid='users'::regclass \gx
SELECT * FROM pg_statio_all_indexes WHERE relid='users'::regclass \gx -- то же самое только про индексы
SELECT * FROM pg_stat_database WHERE datname='test' \gx -- статистика по базе целиком, например раз в секунду собирать xact_commit + xact_rollback это будет TPS
SELECT * FROM pg_stat_bgwriter \gx -- статистика по сбросу кэша, может триггернуться командой CHECKPOINT,

Примечания по bgwriter:

  • buffers_clean - количество страниц, записанных фоновой записью
  • buffers_checkpoint - количество страниц, записанных контрольной точкой
  • buffers_backend - количество страниц, записанных серверными процессами
  • checkpoints_timed - сколько раз срабатывал процесс контрольной точки в соотв с расписнием
  • checkpoint_req - вынужденный чекпоинт (напр большая активность, wal переполнен)

Текущие активности

Пример с расследованием блокировок

Предположим первый процесс

BEGIN;
UPDATE t SET n = n + 1;

В это же время второй:

UPDATE t SET n = n + 2; -- заблокируется, ожидает первый процесс

Разбор

SELECT pid, query, state, wait_event, wait_event_type, pg_blocking_pids(pid) FROM pg_stat_activity WHERE backend_type = 'client backend' \gx
select pid as blocked_pid from pg_stat_activity where backend_type = 'client backend' and cardinality(pg_blocking_pids(pid)) > 0; -- по сути список pid'ов которые заблокированны
-- если бы не было ф-ии pg_blocking_pids
select locktype, transactionid, pid, mode, granted from pg_locks; -- по полю granted понятно кто залочил, а кто ждет
SELECT pg_terminate_backend(p.pid) FROM unnest(pg_blocking_pids(:blocked_pid)) AS b(pid); -- аля KILL 23;
  • У первого процесса state будет idle in transaction - то есть он как бы ничего не делает и в транзакции
  • У второго state будет active, wait_event_type - Lock - то есть мы заблокированны, pg_blocking_pids - pid первого процесса
  • cardinality - кол-во элементов в массиве
  • pg_blocking_pids - это массив, остображается как {234}, процесс может быть заблокирован несколькими процессами
  • после прибивания первого процесса, state второго станет просто idle (без транзакции)

Примечание: начиная с 9.6 появился параметр idle_in_transaction_session_timeout - принудительно завершает сеансы в которых транзакция простаивает больше указанного времени

Если докапываться, вот так будет выглядет блокировка, первая транзакция залочила вторую, в представлении вторая транзакция будет иметь тот же trasnactionid, и false в granted, но опять таки что бы со всем этим не возиться есть pg_blocking_pids

Начиная с верси 10 можно смотреть не только пользовательские но и системные процесс

SELECT pid, backend_type, backend_start, state FROM pg_stat_activity;

Трюк - показать все порожденные процессы (по сути то же что и в пред примере)

ps -o pid,command --ppid \`head -n1 $PGDATA/postmaster.pid\`grep FATAL /hosm

Пару примеров с логами

ALTER SYSTEM SET log_min_duration_statement=0; -- начнет писать вообще все запросы
ALTER SYSTEM SET log_line_prefix='(pid=%p) '; -- меняет формат строки, в этом примере добавилии pid
SELECT pg_reload_conf();
ALTER DATABASE test SET log_min_duration_statement=0; -- можно на конретную базу, а так же на конкретного пользователя

Тема 12 «Сопровождение»

Процедура очистки

Запуск

  • VACUUM таблица - очистка таблицы
  • VACUUM - очистка все базы
  • vacuumdb - консольная утилита

Примечания

  • выполняется параллельно с другими транзакциями
  • часты запуск нагружает подсистему ввода-вывода
  • редкий запуск приводит к росту размера файлов
  • не блокирует табличку

Фоновый процесс autovacuum

  • частота обработки таблицы динамически меняется в зависимости от частоты изменений
  • настраивается конфигурационными параметрами
  • процесс autovacuum launcher, запускает процессы autovacuum worker которые приходят чистить конретные таблички

Очистка

  • VACUUM FULL табличка - очистка таблицы
  • VACUUM FULL - очистка все базы
  • vacuumdb --full - консольный аналог

Примечания

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

Обновление статистики

Фоновый процесс autovacuum - автоматически обновляет статистику при значительных изменениях, использует случайная выборка данных (размер настраивается), используется планировщиком запросов

Обновление статистики (анализ) вручную

  • ANALYZE [таблика]
  • vacuumdb --analyze-only

Очистка и обновление

  • VACUUM ANALYZE [таблица]
  • vacuumdb --analyze

Обновление карт

Карта видимости - битовая карта страниц с видмыми всем версиями строк (отмечает “давно не менявшиеся” страницы), признак видимости сбрасывается при любом изменении, а устанавливается автоматически в процессе очистки, используется для отпимизации доступ

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

Переполнение и заморозка

Номер транзакции 32-битный, пространство номеров закольцовано, старые версии строк “замораживаются” автоочисткой

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

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

Счетчик 32 битный потому что в каждой версии строки есть заголовок (в том числе xmin, xmax), если счетчик 64 бита, то это еще 64 бита на каждую строку, получиться что накладные расходы на служебную информацию будут превышать сами данные

Индексы

Лишние индексы

  • не используемые (мониторинг pg_stat_all_indexes.idx_scan - если стабильный 0 на продолжительном времени - скорее всего не нужен) дублирующеся или пересекающиеся (запросы к pg_index - тут можно попытаться найти индексы которые очееень похожи)
  • проблемы - накладные расходы на изменения, место на диске
  • решение - удаление лишних индексов (аккуратное)

Разрастание индексов

  • мониторинг pg_relation_size()
  • проблемы - место на диске, уменьшение эффективности
  • решение - перестроение индексов

Перестроение индексов

REINDEX

  • REINDEX INDEX индекс - перестроить индекс
  • REINDEX TABLE таблица - перестроить все индексы таблицы
  • REINDEX DATABASE база - перстроить все индексы базы
  • REINDEX SYSTEM - пререстроить все индексы

Примечания

  • перестраивает индекс, минимизируя занимаемое место
  • устанавливает экслюзивную блокировку

VACUUM FULL

  • всесте с таблицей перестраивает и индексы
  • также устанавливает экслюзивную блокировку

Пересоздание без экслюзивной блокировки

CREATE INDEX новый ON … CONCURRENTLY; DROP INDEX старый;

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

Индекс с ограничением целостности

Эжакий drop create в один заход

CREATE UNIQUE INDEX новый ON ... CONCURRENTLY; ALTER TABLE ... DROP CONSTRAINT старое, ADD CONSTRAINT новое [UNIQUE|PRIMARY KEY] USING INDEX новый;

Демо

Оценить степень разростания объектов, что бы принять решение о полной очистке можно разными способами:

  • запросы к системному каталогу
  • используя расширение pgstattuple
CREATE EXTENSION pgstattuple;
-- создаем и заполняем табличку
CREATE TABLE bloat(id serial, s text);
INSER INTO bloat(s) SELECT g.id::text FROM generate_series(1,100000) AS g(id);
CREATE INDEX ON bloat(s);

SELECT * FROM pgstattuple('bloat') \gx -- больше всего нас интересует tuple_percent - процент заполнения таблицы реальными данными (не будет 100% т.к. есть мета данные, плюс иногда записи между страницами)
SELECT * FROM pgstatindex('bloat_s_idx') \gx -- avg_leaf_density - это та же инфа про индекс, leaf_fragmentation - фрагментация

UPDATE bloat SET s = s || '!' WHERE id % 2 = 0;

SELECT * FROM pgstattuple('bloat') \gx -- tuple_percent станет 50% (мы ж по сути обновлии половину таблички, соотв для половины строк есть 2 версии, а как следвстие только половина места занята реальными данными)
SELECT * FROM pgstattuple_approx('bloat') \gx -- примерная инфа, из случайно выборки, может быть оправданее чем вызов полноценной
SELECT * FROM pgstatindex('bloat_s_idx') \gx -- с индексом та же история - avg_leaf_density стал около 50%, а leaf_fragmentation так же 50%

Итоги

  • Процесс очистки решает много задачи помимо очистки

    • сбор статистики для планировщика
    • обновление карти видимости и свободного пространства
    • заморозка старых версий строк

  • Автоочистка должна работать, но требует настройки

  • Индексы требуют мониторинга и (иногда) пересоздания

  • Другие задачи сопровождения рассматриваются отдельно

Примечание: постргес не занимается дефрагментацией файлов (имеется в виду на низком уровне файловой системы)

Тема 13 «Роли и атрибуты»

Роль

  • пользователь субд
  • может включать в себя другие роли - быть “групповой ролью”
  • никак не связана с пользователем ОС (хотя некоторые программы берут имя пользователя ОС как имя роли по умолчанию)
  • определяется уровне кластера (то есть не на уровне базы, а на уровне сервера)
  • псевдороль public - неявно включает в себя все остальные роли (когда надо выдать права всем пользователям)

Атрибуты

Атрибуты определяют свойства роли

CREATE ROLE роль [WITH] атрибут [, атрибут...]
  • LOGIN - возможность подключения
  • SUPERUSER - суперпользователь
  • CREATEDB - возможность создания базы данных
  • CREATEROLE - возможность создавать роли
  • REPLICATION - использование протокола репликации
  • и другие

Если не указывать роли, применяются их противовесы, NOLOGIN, NOSUPERUSER, NOCREATEDB, …

Участие в групповой роли

Включение роли в группу - роль1: GRANT группа TO роль2;

Исключение роли из группы - роль1: REVOKE группы FROM роль2;

Право управления участем в групповой роли имеют:

  • любая роль в самой себе
  • роль с атрибутом SUPERUSER - в любой роли
  • роль с атрибутом CREATEROLE - в любой, кроме суперпользовательской

Передача права управления

Включение в группу с передачей права управления

роль1: GRANT группа TO роль2 WITH ADMIN OPTION; -- теперь роль2 управляет группой, включая передачу права управления и может выполнить роль2: GRANT группа TO роль3 WITH ADMIN OPTION;

-- отзыв права передачи управления роль1: REVOKE ADMIN OPTION FOR группа FROM роль2;

Владельцы

Владелец объекта

  • роль, создавшая объект (а также роли, включенные в нее)
  • может быть изменен командой ALTER … OWNER TO роль

Демо

Примечание, в постгре для обратной совместимости остались users и groups, но по сути это role

CREATE ROLE alice LOGIN CREATEROLE; -- создаем пользователя alice который может покдлючаться и создавать другие роли \c - alice -- переподключаемся под пользователем \du -- вывести роли SELECT usename FROM pg_user; -- вьюха ALTER ROLE bob NOLOGIN; -- забираем права ALTER ROLE alice NOCREATEROLE; -- забираем права сами у себя \c - postgres -- переключаенмся в суперпользователя GRANT postgres TO alice; -- добавляем пользователя alice в группу супер пользователя \du -- в третьем столбце покажет что alice является членом группы postgres ALTER ROLE alice SET log_min_duration_statement=0; -- трюк - следим за алисой ALTER ROLE alice RESET log_min_duration_statement; -- сбрасываем назад ALTER ROLE alice IN DATABASE test SET log_min_duration_statement=0; -- то же самое только для конретной базы

-- alice самаа по себе не стала супер пользователем на что это похоже \c - alice -- переключаемся под пользователя alice SET ROLE postgres; -- аля sudo su - postgres - перключаемся в пользователя SELECT session_user, current_user; -- session_user - выведет пользователя которым подключились и не меняется, current_user - текущий пользовател, изменяектся после set role RESET ROLE; -- сбросить роль DROP ROLE alice; -- грохнет роль, но не должно быть объектов которыми он владеет в любой из баз на сервере REASSIGN OWNED BY alice TO bob; -- переназначаем владельца, может пригодиться при необходимости грохнуть роль DROP OWNED BY bob; -- удаляет объекты которым владеет роль

Тема 14 «Привилегии»

Виды привилегий

  • таблицы

    • select - чтение данных (можно задавать на уровне столбцов)
    • insert - вставка строк (можно задавать на уровне столбцов)
    • update - изменение строк (можно задавать на уровне столбцов)
    • references - внешний ключ (можно задавать на уровне столбцов)
    • delete - удаление строк
    • truncate - очистка таблицы
    • trigger - создание триггеров

  • представления

    • select (можно задавать на уровне столбцов?)
    • trigger

  • последовательности

    • select - currval
    • update - nextval, setval
    • usage - currval, nextval - текущее значение и следующее

  • табличные пространства

    • create

  • база данных

    • create
    • connect
    • temporary - право на создание временных табличек

  • схемы

    • create
    • usage - право пользоватся объектами в схеме

  • функции

    • execute - выполнение с правами security invoker - вызвавшего (по умолчанию), security definer - создавшего

Категории ролей

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

Управление привилегиями

-- выдача привилегии (одна и та же привилегия может быть независимо выдана разными ролями)
роль1: GRANT привилегии ON объект TO роль2;
-- отзыв привилегии
роль1: REVOKE привилегии ON объект TO роль2;

-- выдача привилегии с правом передачи
роль1: GRANT привилегии ON объект TO роль2 WITH GRANT OPTION;
-- отзыв (cascade необходим если привелегии успели быть переданы еще кому либо)
роль1: REVOKE привилегии ON объект TO роль2 CASCADE;
роль1: REVOKE GRANT OPTION FOR привилегии ON объект TO роль2 CASCADE;

Групповые привилегии

Роль получает привилегии своих групповых ролей

Поведение зависит от атрибута роли

INHERIT (по умолчанию) - автоматически наследует привилегии группы

NOINHERIT - требуется явное переключение с помощью SET ROLE

Преднастроенные роли

  • pg_signal_backend - сигналы обсуживающими процессам (напр позволяет делать pg_reload_conf)
  • pg_read_all_settings - чтение конфигурационных параметров
  • pg_read_all_stats - доступ к статистике
  • pg_stat_scan_tables - статистика, вызывающая блокировки

Привилегии public

По умолчанию роль public получает ряд привилегий, удобно, но не безопасно

  • для базы данных

    • connect - подключение
    • temporary - создание временных таблиц

  • для схемы public

    • create - создание объектов
    • usage - доступ к объектам

  • для схем pg_catalog и information_schema

    • usage - доступ к объектам

  • для функций

    • execute - выполнение

Привилегии по умолчанию

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

ALTER DEFAULT PRIVILEGES [IN SCHEMA схема] GRANT привилегии ON класс_объектов TO роль;
-- отзыв
ALTER DEFAULT PRIVILEGES [IN SCHEMA схема] REVOKE привилегии ON класс_объектов TO роль;
-- пример REVOKE EXECUTE ON FUNCTIONS FROM public

Демо

\c - postgres

CREATE ROLE alice LOGIN;
CREATE SCHEMA alice;
GRANT CREATE, USAGE ON SCHEMA alice to alice;

\c - alice

CREATE TABLE t1(n integer);
CREATE TABLE t2(n integer, m integer);

\c - postgres

CREATE ROLE bob LOGIN;

\c - bob

select * from alice.t1; -- выдаст ошибку

-- посмотреть какие привелегии у кого есть
-- роль=привилегии/кем_выданы
-- U - usage, C - create
\db+ alice

\c - alice

GRANT CREATE, USAGE ON SCHEMA alice to bob; -- выдаст ошибку, т.к. alice не владелец схемы и ей не выдали привилегии на передачу прав

\c - postgres

ALTER SCHEMA alice OWNER TO alice;

\c - alice

GRANT CREATE, USAGE ON SCHEMA alice to bob;

-- посмотреть привилегии на табличку (по умолчанию пусто)
\dp alice.t1
-- сразу после этого появиться две записи, оригинальная для алисы и новая для боба
GRANT SELECT ON t1 TO bob;
-- a - insert
-- r - select
-- w - update
-- d - delete
-- D - truncate
-- x - reference
-- t - trigger

-- разрешаем вставку в обе колонки
GRANT INSERT(n,m) ON t2 TO bob;
-- разрешаем чтение только на одну колонку (select * from t2 будет завершаться с ошибкой)
GRANT SELECT(m) ON t2 TO bob;

GRANT ALL ON t1 TO bob; -- что бы не перечислять, можно выдать все привелегии

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

Привилегия update без select работать не будет, т.к. в начале нужно вычитать, что бы затем обновить

Знак звездочки рямдо с привилегиями говорит о том что пользователь может их передавать дальше

Отнимать можно только те привилегии которые были выданы текущим пользователем (то есть я не могу отнять роли, выданные другим пользователем)

Тема 15 «Политики защиты строк»

  • политика ограничивает видимость строк в таблца - видимост определяется предикатами, которые вычисляются для каждой строки с правами вызывающего, клиенту доступны только те строки, для которых предикаты истинны
  • предиткат для существующих строк - используется операторами sleect, update, delete, при наррушении политикеи не возникает ошибка (если только не сброшен параметр row_security)
  • предикат для новых строк - используется операторами insert, update, если не зада, используется первый предикат, при нарушении политики возникает ошибка

TODO

Тема 16 «Подключение и аутентификация»

Задачи при подключении

  • Идентификация - определение имени пользователя базы, имя может отличаться от указанного (при внешней аутентификации)
  • Аутентификация - действительно ли пользователь тот, за кого себя выдает? обычно требуется подтверждение (например, пароль)
  • Авторизация - имеет ли право данный пользователь подключаться к серверу? частично пересекается с функционалом привилегий

Основные настройки

pg_hba.conf - конфигурационный файл, при изменение нужно перечитать

Его настройки можно перевычитать точно так же как и обычные (pg_reload_conf, etc)

Поля

  • тип подключения

    • local - локально подключение через unix socket

    • host - подключение по tcp/ip (обычно требуется изменение параметра listen_addresses)

      • hostssl - шифрованное ssl-подключение по tcp/ip (сервер должен быть собран с поддежкой ssl, также требуется установить параметр ssl)
      • hostnossl - нешифрованное подключение по tcp/ip

  • имя базы данных

    • all - подключение к любой базе
    • sameuser - базы, совпадающие по имени с ролью
    • samerole - базы, совпадающие по имени с ролью или гшрппой, в которую она входит
    • replication - специальное разрешения для протокола репликации
    • база - конретная база
    • имя[, имя…] - несколько баз

  • имя пользователя

    • all - любая роль
    • роль - роль с указанным именем
    • +роль - роль, входящая в указанную роль
    • имя[, имя…] - несколько имен из вышеперечисленного

  • адрес узла

    • all - любой ip адрес
    • ip-адрес/длина_маски - укананный диапазо ip адресов
    • samehost - ip адрес сервера
    • samenet - любой ip из любой подсети, к которой подключен сервер
    • доменное_имя - ip адрес, соотв указанному имени (например domain.com ), допускается указание части имени, начиная с точки (.com), под капотом резолвиться адрес dns

  • метод аутентификации

    • простые

      • trust - допустить без аутентификации
      • reject - отказать без аутентификации

    • пароль

      • md5 - пароль храниться в субд и шифруется md5
      • scram-sha-256 - пароль храниться в субд, используется протокол scram
      • ldap [параметры] - пароль храниться на сервере LDAP
      • radius [параметры] - пароль храниться на сервере RADIUS
      • pam [параметры] - пароль хранится в подключаемом модуле PAM

    • внешнаяя

      • peer [map=…] - запрос имени пользоватекля у ядра ОС (для локальных подключений)
      • cert [map=…] - аутентификация с использованием клиентского ssl сертификата
      • gss [map=… и другие параметры] - аутентификация Kerberos по протоколу GSS API
      • sspi [map=… и другие параметры] - аутентификация Kerberos/NTLM для Windows

  • необязательные дополнительные параметры в виду имя=значения

Записи просматриваются сверху вниз, выбирается первая подходящая запись, причем если с ней не получилось, дальше проверки все равно не идут

Установить пароль пользователя

[CREATE | ALTER] ROLE ...
PASSWORD 'пароль'
[ VALID UNTIL дата_время ];

Пароли храняться в системно каталоге pg_authid

метод шифрования определяеться параметром password_encryption, должны совпадать с конфигом

Ввод пароля

  • установить переменную $PGPASSWORD - неудобно при подключении к разным базам и не безопасно в целом

  • файл с паролями ~/.pgpass на узле клиента

    • строки в формате host:port:database:username:password
    • в качестве значения можно использовать звездочку (любое значение)
    • строки просматриваются сверху вниз, выбирается первая подходящая
    • файл должен иметь права доступа 600 (rw- --- ---)

Соответствие имен

pg_ident.conf

  • название соответствия - указывается в параметре map в pg_hba.conf
  • внешнее имя - считается регулярным выражением, если начинается с косой черты
  • внутренее имя пользователя БД
SHOW hba_file;
\! egrep '^[^#]' /usr/local/pgsql/data/pg_hba.conf
select line_number, type, database, user_name, address, auth_method from pg_hba_file_rules;
\! echo 'local all all trast' >> /usr/local/pgsql/data/pg_hba.conf
select line_number, error from pg_hba_file_rules where error is not null;

Тема 17 «Резервное копирование»

Виды:

  • логическое - аля просто sql файлики с create table и insert into table
  • физическое

Логическая копия

Команды SQL для восстановления с нуля

  • можно сделать копию отдельного объекта или базы
  • можно восстановиться на кластере другой основной версии
  • можно восстановиться на другой архитектуре
  • невысокая скорость
  • нет возможности востановиться на точку во времени

COPY - копия таблицы

  • Резервное копирование - вывод таблицы или результатов запроса в файла, на консоль или в программу
  • Восстановление - добавление строк из файла или с консоли к существующей таблице
  • Серверный вариант - команда SQL COPY - файл будет на сервере
  • Клиентский вариант - команда psql \COPY - файл будет на клиенте

pg_dump - копия базы

  • резервное копирование

    • выдает на консоль или в файли либо sql-скрипт либо архив в специальном формате с оглавлением
    • поддерживает параллельное выполнение
    • позволяет ограничить набор выгружаемых объектов (таблицы, схемы, только DML или только DDL и т.п.)

  • востановление

    • SQL-скрипт - psql
    • формат с оглавлением - pg_restore
    • поддерживает паралелльное выполнение
    • новая база должна быть создана из шаблона template0
    • заранее должны быть созданы роли и табличные пространства
    • после восстановления имеет смысл выполнить сбор статистики

pg_dumpall - копия кластера

  • резервирование

    • сохраняет весь кластер, включая роли и табличные пространства
    • выдает на консоль или в файл SQL-скирпт
    • паралельное выполнение не поддерживается, но можно выгрузить только глобальные объекты и воспользоваться pg_dump

  • востановление

    • с помощью psql

Физическая копия

Используется механизм восстановления после сбоя - копия данных и журналы предзаписи (по сути это просто копия файловой системы)

  • скорость востановления выше (грубо говоря упирается в скорость дисков)
  • можно восстановить кластер на определенный момент времени
  • нельзя восстановить отдельную базу данных, только весь кластер
  • восстановление только на той же основной версии и архитектуре

Холодное резервирование

Горячее резервирование

файловая система копируется при…

выключенном сервере

неаккуратно выключенном серврее

работающем сервере (нужно специальные средства)

журналы предварительной записи…

не нужны

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

нужны за время копирования файловой системы (сервер не должен удалить WAL раньше времени)

Автономная копия

  • Автономная копия содержит и файлы данных и WAL

  • Резервное копирование - утилита - pg_basebackup

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

  • Восстановление

    • разворачиваем созданную автономную копию
    • запускаем сервер

Протокол репликации

  • протокол - получение потока журнальных записей, команды управления резервным копированием и репликацией

  • обсулживается процессом wal_sender - создается для каждого клиента

  • параметр wal_level = replica

  • слот репликации - мы как бы говорим “использовать” слот такой то, тем самым постгрес, перед удалением не нужных wal свериться с слотами репликации

    • серверный объект для получения журнальных записей
    • помнит, какая запись была считана последней
    • сегмент wal не удаляется, пока он полностью не прочитан через слот

Архив журналов

  • Файловый архив

    • сегменты wal копируются в архив по мере заполнения

    • механизм работает под управлением сервера

    • неизбежны задержки попадания данных в архив (пока до конца не заполнился текущий лог, он не попадет в архи)

    • процесс archiver

      • archive_mode = on
      • archive_command - команда shell для копирования только что освободившегося wal файла куда мы укажем
      • archive_timeout - максимально время для переключения на новый сегмент wal
      • алгоритм - при заполнении сегмента wal вызывается archive_command, если команда завершается со статусом 0, сегмент удлаляется, иначе - сегмент остается до тех пор, пока попытка не будет успешной

  • Потоковый архив

    • в архив постоянно записывается поток журнальных записей

    • требуются внешние средства

    • задержки минимальны

    • утилита pg_receivewal

      • подключается по протоколу репликации (можно использовать слот) и направляет поток записей wal в файлы-сегменты
      • стартовая позиция - начало сегмента, следующего за последним заполненным сегментом, найденным в каталоге, или начало текущего сегмента сервере, если каталог пустой
      • в отличие от файлового архива, записи пишутся постоянно при переходе на новый сервер надо перенастраивать параметры

Резервная копия + архив

  • настроенное непрерывное арихвирование журналов

  • резервное копирование - pg_basebackup

    • подключаемся к серверу по протоколу репликации
    • выполняем контрольую точку
    • переключается на следующий сегнмент WAL
    • копирует файловую систему в указанные каталог (сегменты wal в копии не нужны)
    • переключается на следующий сегмент wal

  • восстановление

    • разворачиваем резервную копию
    • создаем управляющий файла $PGDATA/recovery.conf (чтение wal из архива, указание целевой точки восстановыление)
    • запускаем сервер

Демо

select name, setting from pg_settings where name in ('wal_level', 'max_wal_senders');
-- wal_level - replica по умолчанию
-- max_wal_senders != 0 - а как следствие можно подключаться
select type, database, user_name, address, auth_method from pg_hba_file_rules() where database ='{replication}';
-- по умолчанию включены и разрешены для лоокальных подключения=

Тема 18 «Репликация»

Виды

  • физическая

    • мастер-реплка - поток данных только в одну сторону
    • трансляция потока журнальных записей или файлов журнала
    • требуется двоичная совместимость серверов
    • возможно репликация только всего кластера

  • логическая

    • поставщик-подписчик - поток данных возможен в обе стороны
    • информация о строках (уровеень журнала logical)
    • требуется совместимость на уровне протокола
    • возможна выборочная репликация отдельных таблиц

Задачи

  • репликация - процесс синхронизации нескольких копий кластера баз данных на разных серверах

    • отказоустойчивость - при выходе из строя одного из серверов система должна сохранить доступность (возможна деградация производительности)
    • масштабируемость - распределение нагрузки между серверами

Вопросы автоматизации переключения и перенаправления запросов решаются внешними средствами

Физическая репликация

Резервная копия:

  • базовая резервная копия - pg_basebackup
  • журналы упреждающей записи - непрерывное архивирование

Непрерывное восстановление

  • разворачиваем резервную копию
  • создаем управляющий файл recovery.conf (standby_mode = on) и запускаем сервер
  • сервер восстанавливает согласованность и продолжает применять поступающие журналы
  • доставка - поток по протоколу репликации или архив wal
  • подключаения (только для чтения) разрешаются сразу после восстановления согласованности

Использование реплики

  • Допускается

    • запросы на чтение данных (select, copy to, курсоры)
    • установка парметров сервера (set, reset)
    • управление транзакциями (begin, commit, rollback…)
    • создание реззервной копии (pg_basebackup)

  • Не допускаются

    • любые изменения (insert, update, delete, truncate, nextval…)
    • блокировки, пдепологающие изменения (select for update…)
    • команды DDL (create, drop…) в том числе создание временных таблиц
    • команды сопровождения (vacuum, analyze, reindex…)
    • управление доступом (grant, revoke…)
    • не срабатывают триггеры и пользовательские (advisory) блокировки

Переключение на реплику

  • плановое переключение - останов основного сервера для технических работ без прерывания обслуживание , ручной режим
  • аварийное переключение - переход на реплику из-за сбоя основного сервера, ручной режим, но в принципе можно автоматизировать с помощью дополнительного ПО

Восстановление мастера

  • простое подключение бывшего мастера - не работает - проблема потери записей wal, не попавших на реплику из-за задержки
  • востановление “с нуля” из резервной копии - на месте бывшего мастера разворачивается абсолютно новая реплика, процесс занимает много времени (отчасти можно ускорить rsync)
  • утилита pg_rewind - “откатывает” потерянные записи wal, заменяя соответствующие страницы на диске страницами с нового мастера, есть ряд ограничений, ограничивающий применение

Логическая репликация

  • Поставщик

    • выдает изменения данных построчно в порядке их фиксации (реплицируются команды insert, update, delete)
    • возможна начальная синхронизация
    • всегда используется слот логической репликации
    • параметр wal_level = logical

  • Подписчик

    • получает и применяет изменения
    • без разбора, трансформаций и планирование - сразу выполнение
    • возможны конфликты с локальными данными

Конфликты

  • режимы идентификации для изменения и удаления

    • столбцы первичного ключа (по умолчанию)
    • столбцы указанного уникального индекса с ограничением not null
    • все столбцы
    • без идентификации (по умолчанию для системного каталога)

  • конфликты - нарушение ограничений целостности

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

На основном сервере

SELECT * FROM pg_stat_replication \gx
  • state - streaming
  • sync_state - async
  • sent_lsn - то что послали
  • werite_lsn - подтверждение от реплики
  • flush_lsn - реплика записала на диск
  • replay_lsn - реплика проиграла
`\dRs` -- выведет список подписчиков
SELECT * FROM pg_stat_subscription \gx

Если репликация больше не нужна, надо аккуаратно удалить подписку - иначе публикующем сервере останеться открытым репликационный слот

DROP SUBSCRIPTION demo;