Postgres в ретроспективе

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

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


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

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

Такое разделение клиентов и сервера позволяет построить распределенную систему. Можно отделить клиентов от сервера посредством сети и разрабатывать клиентские приложения в среде, удобной для пользователя. Например, можно реализовать базу данных под UNIX и создать клиентские приложения, которые будут работать в системе Microsoft Windows.

Приведенная ниже схема (рис. 1) показывает типичную модель распределенного приложения PostgreSQL:

Рис. 1. Работа типичного приложения PostgreSQL  

Несколько клиентов подсоединяются к серверу по сети. PostgreSQL ориентирован на протокол TCP/IP — это может быть локальная сеть или Интернет. Каждый клиент соединяется с основным серверным процессом базы данных (на схеме — Postmaster), который создает новый серверный процесс специально для обслуживания запросов на доступ к данным конкретного клиента.

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

Клиентские приложения соединяются с базой по специальному протоколу PostgreSQL. Однако можно установить на стороне клиента программное обеспечение, предоставляющее стандартный интерфейс для работы с нужным приложением, например, по стандарту ODBC или JDBC. Доступность ODBC-драйвера позволяет применять PostgreSQL в качестве базы данных для многих существующих приложений, включая такие продукты Microsoft Office, как Excel и Access. Способность взаимодействия PostgreSQL с приложениями будет подробнее рассмотрена в следующих главах.

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

Our users us

PostgreSQL 9.1 provides some of the most advanced enterprise capabilities of any open source database, and is backed by a vibrant and innovative community with proven customer success.

Charles Fan, Sr. VP R&D, VMware

… mission-critical technology tasks can continue to depend on the power, flexibility and robustness of PostgreSQL.

Ram Mohan, CTO, Afilias

There was quite a bit of debate when we were deciding what tools would best serve as the foundation of FlightAware’s ambitious goals. For the underlying database, however, the choice to use PostgreSQL was quite clear from the very beginning.

David McNett, Chief Information Officer, FlightAware.com

In our experience the TCO for PostgreSQL is significantly lower compared not only to commercial rivals but also to competing open source products such as MySQL.

Maksym Sobolyev, SippySoft Inc.

NewsBlur, a social news reader, relies on Postgres for storing millions of sites and subscriptions. Solid and reliable for years.

Samuel Clay, NewsBlur

«Окно» в 60 минут и полчаса на подготовку

решилось закрытием pgAdmin’ав случае с Убунтой

# su - postgres
~ vacuumdb --full --analyze --username postgres --dbname mydatabase
~ pg_dump mydatabase -Ft -v -U postgres -f /tmp/mydatabase.tar
~ dropdb mydatabase --username postgres
~ initdb --locale=ru_RU.utf8 data/

Damn! Error…

~ cp data/pg_hba.conf /home/cr0t/pg_hba.2009.03.24_1654.conf
~ exit
# /etc/init.d/postgresql stop
# su - postgres
~ rm -rf data/*
~ initdb --locale=ru_RU.utf8 data/
~ exit
# /etc/init.d/postgresql start
# su - postgres
~ createdb --encoding UNICODE mydatabase --username postgres
~ pg_restore /tmp/mydatabase.tar | psql --dbname mydatabase --username postgres
~ vacuumdb --full --analyze --username postgres --dbname mydatabase

Основные операции с БД

Чтобы выполнять базовые действия в СУБД, нужно знать язык запросов к базе данных SQL.

Создание базы данных

Для создания базы данных используется команда:

create database

В приведенном ниже примере создается база данных с именем proglib_db.

Рисунок 3 — Создание базы данных с именем proglib_db

Если забыть точку с запятой в конце запроса, знак «=» в приглашении postgres заменяется на «-». Это зачастую указывает на то, что необходимо завершить (дописать) запрос.

Рисунок 4 — вывод ошибки при создании базы данных

На рисунке 4 видно сообщение об ошибке из-за того, что в нашем случае база уже создана.

Создание нового юзера

Для создания пользователя существует команда:

create user

В приведенном ниже примере создается пользователь с именем author.

При создании пользователя отобразится сообщение CREATE ROLE. Каждый пользователь имеет свои права (доступ к базам, редактирование, создание БД / пользователей и т. д.). Вы могли заметить, что столбец Attributes для пользователя author пуст. Это означает, что пользователь author не имеет прав администратора. Он может только читать данные и не может создать другого пользователя или базу.

Можно установить пароль для существующего пользователя.

С этой задачей справится команда :

postgres=#\password author

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

postgres=#create user author with login password 'qwerty';

Удаление базы или пользователя

Для этой операции используется команда : она умеет удалять как пользователя, так и БД.

drop database <database_name>
drop user <user_name>

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

Если вы укажете psql postgres (без имени пользователя), то postgreSQL пустит вас под стандартным суперюзером (postgres). Чтобы войти в базу данных под определенным пользователем, можно использовать следующую команду:

psql  

Войдем в базу proglib_db под пользователем author. Выполним команду , чтобы выйти из текущей БД, а затем выполните следующую команду:

Рисунок 6 — Вход в базу данных proglib_db

Работа с данными и полями таблиц

Удаление одинаковых строк

Если так получилось, что в таблице нет первичного ключа (primary key), то наверняка среди записей найдутся дубликаты. Если для такой таблицы, особенно большого размера, необходимо поставить ограничения (constraint) для проверки целостности, то удалим следующие элементы:

  • дублирующиеся строки,
  • ситуации, когда одна или более колонок дублируются (если эти колонки предполагается использовать в качестве первичного ключа).

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

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


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

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

Вебинар «Производительность 1С на PostgreSQL в Яндекс.Облаке»

27 августа в 12:00 в 12:00, онлайн, беcплатно

tproger.ru

События и курсы на tproger.ru

Теперь рассмотрим случай, когда повторяются значения полей.

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

Если данные важны, то сначала нужно найти записи с дубликатами:

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

Общая форма запроса на удаление описанных выше записей выглядит следующим образом:

Безопасное изменение типа поля

Может возникнуть вопрос о включении в этот список такой задачи. Ведь в PostgreSQL изменить тип поля очень просто с помощью команды . Давайте для примера снова рассмотрим таблицу с покупателями.

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

Но в результате выполнения получим ошибку:

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

В результате всё прошло без ошибок:

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

Например, преобразуем поле обратно в , но с преобразованием формата данных:

В результате таблица примет следующий вид:

Поиск «потерянных» значений

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

Рассмотрим два варианта поиска.

Первый способ Выполним следующий запрос, чтобы найти начало интервала с «потерянным» значением:

В результате получим значения: , и .

Если нужно найти не только первое вхождение, а все пропущенные значения, используем следующий (ресурсоёмкий!) запрос:

В результате видим следующий результат: , и .

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

И находим все пропущенные идентификаторы:

Подсчёт количества строк в таблице

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

Общее количество строк в таблице:

Количество строк при условии, что указанное поле не содержит :

Количество уникальных строк по указанному полю:

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

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

Начнём транзакцию с помощью команды .

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

А чтобы применить — команду .

Просмотр и завершение исполняемых запросов

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

Для того, чтобы остановить конкретный запрос, выполним следующую команду, с указанием id процесса (pid):


Для того, чтобы прекратить работу запроса, выполним:

Настройка WAL-G

Для примера хранения бэкапов будет использоваться Amazon S3 (потому что он ближе к моим серверам и его использование обходится очень дёшево). Для работы с ним нужен «s3-бакет» и ключи доступа.

Во всех предыдущих статьях о WAL-G использовалось конфигурирование с помощью переменных окружения, но с этого релиза настройки можно расположить в в домашней директории пользователя postgres. Для его создания выполним следующий bash-скрипт:

Немного поясню по всем параметрам:

  • WALG_S3_PREFIX – путь к вашему S3-бакету куда будут заливаться бэкапы (можно как в корень, так и в папку);
  • AWS_ACCESS_KEY_ID – ключ доступа в S3 (в случае восстановления на тестовом сервере – данные ключи должны иметь ReadOnly Policy! Об этом подробнее написано в разделе про восстановление);
  • AWS_SECRET_ACCESS_KEY – секретный ключ в хранилище S3;
  • WALG_COMPRESSION_METHOD – метод компрессии, лучше использовать Brotli (так как это золотая середина между итоговым размером и скоростью сжатия/разжатия);
  • WALG_DELTA_MAX_STEPS – количество «дельт» до создания полного бэкапа (позволяют экономить время и размер загружаемых данных, но могут чуть замедлить процесс восстановления, поэтому не желательно использовать большие значения);
  • PGDATA – путь к директории с данными вашей базы (можно узнать, выполнив команду pg_lsclusters);
  • PGHOST – подключение к базе, при локальном бэкапе лучше делать через unix-socket как в этом примере.

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

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

Может понадобиться создать базу данных. Это можно сделать SQL-запросом:

=# CREATE DATABASE users WITH ENCODING=’UTF-8′;

* где users — имя базы; UTF-8 — используемая кодировка.

Синтаксис:

psql <имя базы> < <файл с дампом>

Пример:

psql users < /tmp/users.dump

С авторизацией

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

psql -U dmosk -W users < /tmp/users.dump

* где dmosk — имя учетной записи; опция W потребует ввода пароля.

Из файла gz

Сначала распаковываем файл, затем запускаем восстановление:

gunzip users.dump.gz

psql users < users.dump

Или одной командой:

zcat users.dump.gz | psql users

Определенную базу

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

psql users < /tmp/database.dump

Если делался полный дамп (всех баз), восстановить определенную можно при помощи утилиты pg_restore с параметром -d:

pg_restore -d users cluster.bak

Определенную таблицу

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

psql users < /tmp/students.dump

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

pg_restore -a -t students users.dump

С помощью pgAdmin

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

Выбираем наш файл с дампом:

И кликаем по Восстановить:

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

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

Из бинарника:

pg_restore -Fc users.bak


Из тарбола:

pg_restore -Ft users.tar

С создание новой базы:

pg_restore -Ft -C users.tar

Разработка

PostgreSQL развивается силами международной группы разработчиков (PGDG), в которую входят как непосредственно программисты, так и те, кто отвечают за продвижение PostgreSQL (Public Relation), за поддержание серверов и сервисов, написание и перевод документации, всего на 2005 год насчитывается около 200 человек. Другими словами, PGDG — это сложившийся коллектив, который полностью самодостаточен и устойчив. Проект развивается по общепринятой среди открытых проектов схеме, когда приоритеты определяются реальными нуждами и возможностями. При этом, практикуется публичное обсуждение всех вопросов в списке рассылке, что практически исключает возможность неправильных и несогласованных решений.

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

Цикл работой над новой версией обычно длится 10-12 месяцев (сейчас ведется дискуссия о более коротком цикле 2-3 месяца) и состоит из нескольких этапов.

Наивно оптимизируем поиск

Играться с боевой базой мы не будем, конечно — создадим тестовую базу. В ней ~12 тысяч документов. Запрос из примера там выполняется ~35 секунд. Непростительно долго!

Индекс

В первую очередь, конечно, надо добавить индекс. Самый простой способ: функциональный индекс.

Создаваться такой индекс будет долго — на тестовой базе ему понадобилось ~26 секунд. Ему надо пройтись по базе и вызвать функцию to_tsvector для каждой записи. Хотя поиск он всё же ускоряет до 12 секунд, это всё ещё непростительно долго!

Многократный вызов

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

Сделать это можно двумя способами:

  1. Добавить колонку типа в таблицу с документами.
  2. Создать отдельную таблицу с one-to-one связью с таблицей документов, и хранить там вектора.

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

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

Я для себя выбрал второй подход, его преимущества для меня весомей.

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

А во сколько раз ускорился сам поиск?

Невероятно! И это несмотря на join и . Уже вполне приемлемый результат, большую часть времени отнимет не поиск, а вычисление для каждой из строк.

Многократный вызов

Кажется, мы успешно решили все наши проблемы, кроме этой. 44 миллисекунды — вполне достойное время выполнения. Кажется, хэппи-энд близок? Не тут-то было!

Запустим тот же самый запрос без и сравним результаты.

1,7 мс! В тридцать раз быстрее! Для боевой базы результаты ~150 мс и 1,5 секунды. Разница в любом случае на порядок, и 1,5 секунды — не то время, которое хочется ждать ответа от базы. Что же делать?

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

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

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

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

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

Утилиты (программы) PosgreSQL:

  • createdb и dropdb – создание и удаление базы данных (соответственно)
  • createuser и dropuser – создание и пользователя (соответственно)
  • pg_ctl – программа предназначенная для решения общих задач управления (запуск, останов, настройка параметров и т.д.)
  • postmaster – многопользовательский серверный модуль PostgreSQL (настройка уровней отладки, портов, каталогов данных)
  • initdb – создание новых кластеров PostgreSQL
  • initlocation – программа для создания каталогов для вторичного хранения баз данных
  • vacuumdb – физическое и аналитическое сопровождение БД
  • pg_dump – архивация и восстановление данных
  • pg_dumpall – резервное копирование всего кластера PostgreSQL
  • pg_restore – восстановление БД из архивов (.tar, .tar.gz)

Примеры создания резервных копий:

Создание бекапа базы mydb, в сжатом виде

pg_dump -h localhost -p 5440 -U someuser -F c -b -v -f mydb.backup mydb

Создание бекапа базы mydb, в виде обычного текстового файла, включая команду для создания БД

pg_dump -h localhost -p 5432 -U someuser -C -F p -b -v -f mydb.backup mydb

Создание бекапа базы mydb, в сжатом виде, с таблицами которые содержат в имени payments

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t *payments* -f payment_tables.backup mydb

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

pg_dump -a -t table_name -f file_name database_name

Создание резервной копии с сжатием в gz

pg_dump -h localhost -O -F p -c -U postgres mydb | gzip -c > mydb.gz

Список наиболее часто используемых опций:

  • -h host — хост, если не указан то используется localhost или значение из переменной окружения PGHOST.
  • -p port — порт, если не указан то используется 5432 или значение из переменной окружения PGPORT.
  • -u — пользователь, если не указан то используется текущий пользователь, также значение можно указать в переменной окружения PGUSER.
  • -a, —data-only — дамп только данных, по-умолчанию сохраняются данные и схема.
  • -b — включать в дамп большие объекты (blog’и).
  • -s, —schema-only — дамп только схемы.
  • -C, —create — добавляет команду для создания БД.
  • -c — добавляет команды для удаления (drop) объектов (таблиц, видов и т.д.).
  • -O — не добавлять команды для установки владельца объекта (таблиц, видов и т.д.).
  • -F, —format {c|t|p} — выходной формат дампа, custom, tar, или plain text.
  • -t, —table=TABLE — указываем определенную таблицу для дампа.
  • -v, —verbose — вывод подробной информации.
  • -D, —attribute-inserts — дамп используя команду INSERT с списком имен свойств.

Бекап всех баз данных используя команду pg_dumpall.

pg_dumpall > all.sql

Восстановление таблиц из резервных копий (бэкапов):

psql — восстановление бекапов, которые хранятся в обычном текстовом файле (plain text); pg_restore — восстановление сжатых бекапов (tar);

Восстановление всего бекапа с игнорированием ошибок

psql -h localhost -U someuser -d dbname -f mydb.sql

Восстановление всего бекапа с остановкой на первой ошибке

psql -h localhost -U someuser —set ON_ERROR_STOP=on -f mydb.sql

Для восстановления из tar-арихива нам понадобиться сначала создать базу с помощью CREATE DATABASE mydb; (если при создании бекапа не была указана опция -C) и восстановить

pg_restore —dbname=mydb —jobs=4 —verbose mydb.backup

Восстановление резервной копии БД, сжатой gz

gunzip mydb.gz
psql -U postgres -d mydb -f mydb

С этим читают