Skip to content

PostgreSQL

PostgreSQL (или просто Postgres) - это объектно-реляционная система управления базами данных (СУБД) с открытым исходным кодом, разрабатываемая и поддерживаемая сообществом разработчиков.

Архитектура PostgreSQL

При подключении к серверу клиент соединяется с процессом postmaster. В задачи этого процесса входит порождение других процессов и присмотр за ними. Таким образом, postmaster порождает серверный процесс и дальше клиент работает уже с ним. На каждое соединение создается по серверному процессу, поэтому при большом числе соединений следует использовать пул (нпример, с помощью расширения pgbouncer).

Что такое пулер соединений и зачем он нужен?

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

Кроме того, само по себе создание подключения — довольно долгая операция. Открывается соединение с БД, выполняется аутентификация, открытие сокета и так далее. А если при каждом запросе от приложения будет создаваться новое подключение — приложение будет работать медленнее, чем могло бы.

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

Существует несколько реализаций пулеров, например, pgBouncer, pgPool, odissey. PgBouncer — один из самых популярных пулеров.

У PgBouncer есть три способа управления пулом подключений:

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

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

У экземпляра СУБД имеется общая для всех серверных процессов память. Большую ее часть занимает буферный кэш (shared buffers), необходимый для ускорения работы с данными на диске. Обращение к дискам происходит через операционную систему (которая тоже кэширует данные в оперативной памяти). PostgreSQL полностью полагается на операционную систему и сам не управляет устройствами. В частности, он считает, что вызов fsync() гарантирует попадание данных из памяти на диск.

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

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

Организация данных

Экземпляр СУБД работает с несколькими базами данных. Эти базы данных называются кластером. Это не должно вводить в заблуждение — термин «кластер» имеет здесь не тот смысл, который в него обычно вкладывается: это не несколько экземпляров, работающих над одними данными, а именно несколько баз данных, обрабатываемых одним экземпляром.

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

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

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

Объекты (таблицы и индексы) хранятся в файлах; каждый объект занимает один или несколько (2-3) файлов внутри каталога табличного пространства. Кроме того, файлы разбиваются на части по 1 ГБ. Необходимо учитывать влияние потенциально большого количества файлов на используемую файловую систему.

Схемы

Схемы представляют собой пространства имен для всех объектов, хранящихся в базе данных. Схемы существуют внутри базы данных, и все объекты базы принадлежат каким-либо схемам. Если при обращении к объекту схема не указана явно, выбирается первая подходящая схема из перечисленных в пути поиска.

Страницы

Для удобства организации ввода-вывода файлы логически поделены на страницы (или блоки) — это минимальный объем данных, который считывается или записывается. Соответственно, и многие внутренние алгоритмы PostgreSQL ориентированы на работу со страницами. Обычно страница имеет размер 8 Кбайт.

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

Буферный кэш

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

Размер страницы обычно составляет 8 КБ, хотя может устанавливаться при сборке.

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

Любая страница, с которой работает СУБД, попадает в кэш. Часто используемые страницы остаются в кэше надолго; редко используемые — вытесняются и заменяются другими страницами.

Буфер, содержащий измененную страницу, называется «грязным». Процесс Background Writer постепенно записывает их на диск в фоновом режиме — это позволяет снизить нагрузку на диски и увеличить производительность. Если Background Writer не успевает записать вытесняемый серверным процессом грязный буфер, то процесс записывает его сам. С точки зрения производительности этого лучше не допускать.

Обработка запросов

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

  • Анализатор (parser) выполняет первоначальный синтаксический и семантический разбор текста запроса. Для этого он использует системный каталог, в котором хранится информация обо всех объектах базы данных.
  • Результатом работы анализатора является дерево разбора.
  • Далее запрос переписывается (rewriter) с помощью системы правил. Правила — открытый механизм, с помощью которого можно изменить исходный запрос. В частности, на этом этапе происходит замена представлений на текст запроса. Результатом является измененное дерево разбора.
  • Планировщик (planner) выбирает для запроса лучший (в смысле минимизация стоимости выполнения) план. Стоимость вычисляется на основе статистики, которую собирает процесс Stats Collector, а также Vacuum. План определяет, в каком порядке будут соединяться таблицы, какие методы доступа (полное сканирование таблиц, использование индексов) и соединений (вложенные циклы, соединение слиянием или с помощью хэширования) будут использоваться. Ключевое требование для корректной работы оптимизатора — наличие аккуратной и актуальной статистики.
  • Затем управление передается исполнителю (executor). Запрос выполняется в соответствии с планом. В случае, если это был оператор select, клиенту возвращается набор строк, удовлетворяющий сформулированным условиям.

Транзакции

Транзакции представляют собой последовательности операций, которые должны удовлетворять требованиям ACID: атомарность (atomicity), согласованность (consistency), изоляция (isolation) и долговечность (durability).

  • Атомарность означает, что при фиксации выполняются все операции, составляющие транзакцию, при откате — не выполняется ни одна.
  • Согласованность — поддержание целостности данных. Транзакция начинает работу в согласованном (целостном) состоянии и по окончании своей работы также оставляет данные согласованными.

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

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

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

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

Поддерживаемые уровни изоляции в Postgresql:

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

  • Read uncommited - чтение незафиксированных данных;
  • Read committed - чтение зафиксированных данных;
  • Repeatable read - повторяемое чтение;
  • Serializable - сериализуемость.

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

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

  • Аномалия потерянного обновления (lost update) возникает, когда две транзакции читают одну и ту же строку таблицы, затем одна из них обновляет эту строку, после чего вторая обновляет эту же строку, не учитывая изменений, сделанных первой транзакцией.

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

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

    Грязное чтение допускается стандартом на уровне Read Uncommitted.

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

    Неповторяющееся чтение допускается стандартом на уровнях Read Uncommitted и Read Committed.

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

    Фантомное чтение допускается стандартом на уровнях Read Uncommitted, Read Committed и Repeatable Read.

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

Изоляция должна быть построена на блокировках. Идея широко применявшегося протокола двухфазного блокирования (2PL) состоит в том, что в процессе выполнения транзакция блокирует затронутые строки, а при завершении — освобождает блокировки. Сильно упрощая: чем больше блокировок захватывает транзакция, тем лучше она изолирована от других транзакций. Но и тем сильнее страдает производительность системы, поскольку вместо совместной работы транзакции начинают выстраиваться в очередь за одними и теми же строками.

  • Если транзакция блокирует изменяемые строки для изменений, но не для чтения, получаем уровень Read Uncommitted с возможностью прочитать незафиксированные данные.
  • Если изменяемые строки блокируются и для чтения, и для изменений, получаем уровень Read Committed: незафиксированные данные прочитать нельзя, но при повторном обращении к строке можно получить другое значение (неповторяющееся чтение).
  • Если для всех операций блокируются и читаемые, и изменяемые строки, получаем уровень Repeatable Read: повторное чтение строки будет выдавать то же значение.
  • Но с Serializable проблема: невозможно заблокировать строку, которой еще нет. Из-за этого остается возможность фантомного чтения: другая транзакция может добавить строку, попадающую под условия выполненного ранее запроса, и эта строка окажется в повторной выборке. Поэтому для полной изоляции обычных блокировок не хватает — нужно блокировать не строки, а условия (предикаты).

Со временем на смену блокировочным протоколам управления транзакциями пришел протокол изоляции на основе снимков (Snapshot Isolation, SI). Его идея состоит в том, что каждая транзакция работает с согласованным снимком данных на определенный момент времени. В снимок попадают все актуальные изменения, зафиксированные до момента его создания. Изоляция на основе снимков позволяет обходиться минимумом блокировок. Фактически блокируется только повторное изменение одной и той же строки. Все остальные операции могут выполняться одновременно: пишущие транзакции никогда не блокируют читающие транзакции, а читающие вообще никогда никого не блокируют.

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

Подробнее про уровни изоляции

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

Физически в страницах данных могут находиться несколько версий одной и той же строки, хотя каждая транзакция должна видеть максимум одну из них. Все вместе версии разных строк, наблюдаемые транзакцией, образуют снимок данных (snapshot). Снимок обеспечивает согласованную в ACID-смысле картину данных на определенный момент времени и содержит только самые актуальные данные, зафиксированные к моменту его создания.

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

На уровне изоляции Read Committed снимок создается в начале каждого оператора транзакции и остается активным все время работы этого оператора.

На уровнях Repeatable Read и Serializable снимок создается один раз в начале первого оператора транзакции и остается активным до самого конца транзакции.

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

Журнал упреждающей записи

В журал упреждающей записи (Write Ahead Log, WAL) записывается информация, достаточная для повторного выполнения всех действия с базой данных.

Записи этого журнала обязаны попасть на диск раньше, чем изменения в соответствующей странице. Тогда при восстановлении можно прочитать страницу с диска, посмотреть в ней номер последней записи WAL, и применить к странице все записи WAL, которые еще не были применены.

Записью журнала занимается процесс WAL Writer. Можно сохранять записи журнала непосредственно при фиксации изменений. В таком случае гарантируется, что зафиксированные данные не пропадут при сбое. Второй вариант — сохранять записи асинхронно, что более эффективно. В этом случае некоторые зафиксированные данные могут пропасть, но согласованность все равно гарантируется.

Журнал состоит из нескольких файлов (обычно по 16 МБ), которые циклически перезаписываются. Старые файлы могут сохраняться и архивироваться процессом WAL Archiver.

Поскольку страница может долго не попадать на диск, возникает необходимость хранить неопределенно много журнальных записей. Чтобы избежать этого, периодически происходит так называемая контрольная точка, при которой все грязные буферы принудительно сбрасываются на диск. Этим занимается процесс Checkpointer.

Журнальные файлы можно посмотреть в файловой системе в каталоге $PGDATA/pg_wal/

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

Идея многоверсионности (Muliversion Concurrency Control, MVCC) состоит в том, чтобы разделить два уровня представления данных.

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

  • При удалении строки она не удаляется физически из страницы, а помечается номером удалившей его транзакции.
  • Новая строка помечается номером создавшей его транзакции.
  • Обновление реализуется как удаление старой строки и вставка новой.

Таким образом, в каждой версии строки хранится информация о начале и конце ее действия.

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

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

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

Старые версии строк, которые не видны ни одной из активных транзакций, должны быть физически удалены, чтобы освободить занимаемое ими место. Этим занимается процесс Autovacuum Launcher, запускающий для выполнения работы процессы Autovacuum Worker. Также очистку можно запустить вручную командой VACUUM.

Репликация данных

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

Репликация используется для достижения двух целей:

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

В PostgreSQL есть несколько видов репликации:

  • Потоковая репликация (Streaming Replication). Это репликация, при которой от основного сервера PostgreSQL на реплики передается WAL. И каждая реплика затем по этому журналу изменяет свои данные. Для настройки такой репликации все серверы должны быть одной версии, работать на одной ОС и архитектуре. Потоковая репликация в Postgres бывает двух видов — асинхронная и синхронная.

    • Асинхронная репликация. В этом случае PostgreSQL сначала применит изменения на основном узле и только потом отправит записи из WAL на реплики. Преимущество такого способа — быстрое подтверждение транзакции, т.к. не нужно ждать пока все реплики применят изменения. Недостаток в том, что при падении основного сервера часть данных на репликах может потеряться, так как изменения не успели продублироваться.
    • Синхронная репликация. В этом случае изменения сначала записываются в WAL хотя бы одной реплики и только после этого фиксируются на основном сервере. Преимущество — более надежный способ, при котором сложнее потерять данные. Недостаток — операции выполняются медленнее, потому что прежде чем подтвердить транзакцию, нужно сначала продублировать ее на реплике.

  • Логическая репликация (Logical Replication). Логическая репликация оперирует записями в таблицах PostgreSQL. Этим она отличается от потоковой репликации, которая оперирует физическим уровнем данных: биты, байты, и адреса блоков на диске. Возможность настройки логической репликации появилась в PostgreSQL 10.

    Этот вид репликации построен на механизме публикации/подписки: один сервер публикует изменения, другой подписывается на них. При этом подписываться можно не на все изменения, а выборочно. Например, на основном сервере 50 таблиц: 25 из них могут копироваться на одну реплику, а 25 — на другую.

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

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

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

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

Утилита pg_dump

pg_dump
# Утилита имеет простой синтаксис:
pg_dump <параметры> <имя базы> > <файл для сохранения копии> 

# Простая резервная копия всей базы данных:
pg_dump -U username -h hostname -d dbname > backup.sql
# Этот пример создает резервную копию всей базы данных dbname на сервере hostname с использованием пользователя username и выводит ее в файл backup.sql

# Резервная копия сжатая gzip:
pg_dump -U username -h hostname -d dbname | gzip > backup.sql.gz

# Резервная копия с пользовательскими настройками:
pg_dump -U username -h hostname -d dbname -Fc -f backup.dump
# Этот пример создает резервную копию базы данных в собственном формате (-Fc) и сохраняет ее в файл backup.dump.

# Резервная копия только определения (без данных):
pg_dump -U username -h hostname -d dbname -s > schema_only.sql
# Этот пример создает резервную копию только определений объектов (таблиц, представлений, функций и т. д.) базы данных без данных.

# Резервная копия с использованием параллельных процессов:
pg_dump -U username -h hostname -d dbname -j 4 -f backup.sql
# Этот пример создает резервную копию базы данных с использованием 4 параллельных процессов (-j 4) для увеличения производительности.

Утилита pg_dumpall

Утилита pg_dumpall реализует резервное копирование всего экземпляра (кластера или инстанса) базы данных без указания конкретной базы данных на инстансе. По принципу схожа с pg_dump.

Утилита pg_restore

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

pg_restore
# Восстановление из файла в формате SQL:
pg_restore -U username -h hostname -d dbname backup.sql

# Восстановление из сжатого файла gzip:
gzip -dc backup.sql.gz | pg_restore -U username -h hostname -d dbname

# Восстановление из файла в собственном формате pg_dump:
pg_restore -U username -h hostname -d dbname backup.dump

# Восстановление только определений (без данных):
pg_restore -U username -h hostname -d dbname --schema-only backup.sql

# Восстановление с параллельной обработкой:
pg_restore -U username -h hostname -d dbname --jobs=4 backup.sql

# Восстановление с замещением существующих данных:
pg_restore -U username -h hostname -d dbname --clean backup.sql
psql
# Восстановить данные из дампа также возможно при помощи psql:
psql dbname < /tmp/dbname.dump

Утилита pg_basebackup

Утилитой pg_basebackup можно выполнять резервное копирования работающего кластера баз данных PostgreSQL. Результирующий бинарный файл можно использовать для репликации или восстановления на определенный момент в прошлом. Утилита создает резервную копию всего экземпляра базы данных и не дает возможности создавать слепки данных отдельных сущностей. Подключение pg_basebackup к PostgreSQL выполняется при помощи протокола репликации с полномочиями суперпользователя или с правом REPLICATION.

pg_basebackup
# Для выполнения резервного копирования локальной базы данных достаточно передать утилите pg_basebackup 
# параметр -D, обозначающий директорию, в которой будет сохранена резервная копия:
pg_basebackup -D /tmp

Утилита wal-g

Wal-g — утилита для резервного копирования и восстановления базы данных PostgreSQL. При помощи wal-g можно выполнять сохранение резервных копий на хранилищах S3 или просто на файловой системе.

Команды для работы с Postgres

версия Postgres
# Посмотреть версия Postgres
sudo -u postgres psql -c "SELECT version();"
Подключение к инстансу
# Подключение к инстансу на локалхосте под пользователем postgres:
sudo -i -u postgres
psql -U postgres -h localhost
Подключение к БД
# Для подключения к базе данных PostgreSQL можно использовать команду:
psql -U<USERNAME> -h<HOSTNAME> -d<DB_NAME>

psql -h 127.0.0.1 -U user_name -d bd_name
Создаем пользователя
# Создаем пользователя с паролем
sudo postgres createuser student -P
Создаем БД
# Создаем БД с правами
sudo postgres createdb my_db -O student
Поиск долгих запросов
-- Данная команда отображает список отсортированных работающих запросов (исключены запросы находящиеся в режиме ожидания). 
-- Можно применять для поиска долгих запросов.
SELECT user, pid, client_addr, query, query_start, NOW() - query_start AS elapsed
FROM pg_stat_activity
WHERE query != '<IDLE>'
AND client_addr IS NOT NULL
ORDER BY elapsed DESC;

Шпаргалка PostgreSQL