Postgresql структура базы данных

Postgresql структура базы данных

  • Переводы, 1 сентября 2017 в 20:57
  • Александр Наливайко

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

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

Получение информации о базе данных

Размер базы данных

Чтобы получить физический размер файлов (хранилища) базы данных, используем следующий запрос:

Результат будет представлен как число вида 41809016 .

current_database() — функция, которая возвращает имя текущей базы данных. Вместо неё можно ввести имя текстом:

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

В результате получим информацию вида 40 Mb .

Перечень таблиц

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

information_schema — стандартная схема базы данных, которая содержит коллекции представлений (views), таких как таблицы, поля и т.д. Представления таблиц содержат информацию обо всех таблицах баз данных.

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

В последнем условии IN можно указать имя определенной схемы.

Размер таблицы

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

Функция pg_relation_size возвращает объём, который занимает на диске указанный слой заданной таблицы или индекса.

Имя самой большой таблицы

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

Для того, чтобы вывести информацию о самой большой таблице, ограничим запрос с помощью LIMIT :

relname — имя таблицы, индекса, представления и т.п.
relpages — размер представления этой таблицы на диске в количествах страниц (по умолчанию одна страницы равна 8 Кб).
pg_class — системная таблица, которая содержит информацию о связях таблиц базы данных.

Перечень подключенных пользователей

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

Активность пользователя

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

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

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

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

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

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

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

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

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

3–5 февраля, Москва, 11 000–25 000 ₽

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

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

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

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

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

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

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

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

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

ERROR: column “customer_id” cannot be cast automatically to type integer
SQL state: 42804
Hint: Specify a USING expression to perform the conversion.

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

Читайте также:  Trojan win32 zpevdo a что это

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Работа с конфигурацией

Поиск и изменение расположения экземпляра кластера

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

Изменим расположение на другое с помощью команды:

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

Получение перечня доступных типов данных

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

typname — имя типа данных.
typlen — размер типа данных.

Изменение настроек СУБД без перезагрузки

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

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

Мы рассмотрели команды, которые помогут упростить работу разработчикам и администраторам баз данных, использующим PostgreSQL. Но это далеко не все возможные приёмы. Если вы сталкивались с интересными задачами, напишите о них в комментариях. Поделимся полезным опытом!

СУБД PostgreSQL в разных операционных системах имеет практически идентичную структуру каталогов. В данной статье рассматривается стандартная структура каталогов для ОС Ubuntu 12.04.

Читайте также:  Canon lbp 2900 не включается

По умолчанию PostgreSQL устанавливается в папку /var/lib/postgresql/ /main. Основной каталог СУБД содержит подкаталоги с пользовательскими данными и служебной информацией.

    postmaster.opts — файл, в котором сдержится командная строка с параметрами, с помощью которой была запущена СУБД.

На моем компьютере в этом файле хранится следующая строка

PG_VERSION — файл, содержащий основной номер версии СУБД (к примеру, 9.3)

base — каталог, содержащий каталоги баз данных (по каталогу на каждую БД)

Имена подкаталогов соответствуют OID’ам баз данных.

global — каталог, в котором хранятся глобальные таблицы (к примеру, pg_database)

pg_clog — каталог, в котором хранится информация о статусах транзакций.

На каждую транзакцию выделяется 2 бита. Статус транзакции может иметь следующие значения:

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

Количество хранимых транзакций ограничено параметром autovacuum_freeze_max_age (максимальное значение

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

pg_multixact — каталог, содержащий информацию, необходимую для координации работы параллельных транзакций (используется для хранения SHARED ROW LOCKS)

pg_notify — каталог, в котором хранится информация для поддержки работы LISTEN/NOTIFY

pg_serial — каталог, содержащий информацию о завершенных последовательных (serializable) транзакциях

pg_snapshots — каталог, в котором хранятся экспортированные снапшоты

Файлы создаются при вызове процедуры pg_export_snapshot() и существуют до окончания транзакции.

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

Экспорт снапшота в транзакции

В каталоге появился файл, в котором содержится информация о снапшоте

Этот раздел описывает формат хранения на уровне файлов и каталогов.

Файлы конфигурации и файлы данных, используемые кластером базы данных, традиционно хранятся вместе в каталоге данных кластера, который обычно называют PGDATA (по имени переменной среды, которую можно использовать для его определения). Обычно PGDATA находится в /var/lib/pgsql/data . На одной и той же машине может находиться множество кластеров, управляемых различными экземплярами сервера.

В каталоге PGDATA содержится несколько подкаталогов и управляющих файлов, как показано в Таблице 63.1. В дополнение к этим обязательным элементам конфигурационные файлы кластера postgresql.conf , pg_hba.conf и pg_ident.conf традиционно хранятся в PGDATA , хотя их можно разместить и в другом месте.

Таблица 63.1. Содержание PGDATA

Элемент Описание
PG_VERSION Файл, содержащий номер основной версии Postgres Pro
base Подкаталог, содержащий подкаталоги для каждой базы данных
global Подкаталог, содержащий общие таблицы кластера, такие как pg_database
pg_commit_ts Подкаталог, содержащий данные о времени фиксации транзакций
pg_clog Подкаталог, содержащий данные о состоянии транзакции
pg_dynshmem Подкаталог, содержащий файлы, используемые подсистемой динамически разделяемой памяти
pg_logical Подкаталог, содержащий данные о состоянии для логического декодирования
pg_multixact Подкаталог, содержащий данные о состоянии мультитранзакций (используемые для разделяемой блокировки строк)
pg_notify Подкаталог, содержащий данные состояния прослушивания и нотификации (LISTEN/NOTIFY)
pg_replslot Подкаталог, содержащий данные слота репликации
pg_serial Подкаталог, содержащий информацию о выполненных сериализуемых транзакциях.
pg_snapshots Подкаталог, содержащий экспортированные снимки (snapshots)
pg_stat Подкаталог, содержащий постоянные файлы для подсистемы статистики.
pg_stat_tmp Подкаталог, содержащий временные файлы для подсистемы статистики
pg_subtrans Подкаталог, содержащий данные о состоянии подтранзакций
pg_tblspc Подкаталог, содержащий символические ссылки на табличные пространства
pg_twophase Подкаталог, содержащий файлы состояний для подготовленных транзакций
pg_xlog Подкаталог, содержащий файлы WAL (журнал предзаписи)
postgresql.auto.conf Файл, используемый для хранения параметров конфигурации, которые устанавливаются при помощи ALTER SYSTEM
postmaster.opts Файл, содержащий параметры командной строки, с которыми сервер был запущен в последний раз
postmaster.pid Файл блокировки, содержащий идентификатор ( >* , либо пустое значение в случае отсутствия прослушивания по TCP), и ID сегмента разделяемой памяти (этот файл отсутствует после остановки сервера).

Для каждой базы данных в кластере существует подкаталог внутри PGDATA /base , названный по O >pg_database . Этот подкаталог по умолчанию является местом хранения файлов базы данных; в частности, там хранятся её системные каталоги.

Каждая таблица и индекс хранятся в отдельном файле. Для обычных отношений, эти файлы получают имя по номеру файлового узла таблицы или индекса, который содержится в pg_class . relfilenode . Но для временных отношений, имя файла имеет форму t BBB _ FFF , где BBB — идентификатор серверного процесса сервера, который создал данный файл, а FFF — номер файлового узла. В обоих случаях, помимо главного файла (также называемого основным слоем), у каждой таблицы и индекса есть карта свободного пространства (см. Раздел 63.3), в которой хранится информация о свободном пространстве в данном отношении. Имя файла карты свободного пространства образуется из номера файлового узла с суффиксом _fsm . Также таблицы имеют карту видимости, хранящуюся в слое с суффиксом _vm , для отслеживания страниц, не содержащих мёртвых записей. Карта видимости подробнее описана в Разделе 63.4. Нежурналируемые таблицы и индексы имеют третий слой, так называемый слой инициализации, имя которого содержит суффикс _init (см. Раздел 63.5).

Читайте также:  Canon powershot g9x mark ii черный

Внимание

Заметьте, что хотя номер файла таблицы часто совпадает с её O >не всегда; некоторые операции, например, TRUNCATE , REINDEX , CLUSTER и некоторые формы команды ALTER TABLE могут изменить номер файла, но при этом сохранят O >pg_class , в pg_class . relfilenode содержится ноль. Фактический номер файлового узла для них хранится в низкоуровневой структуре данных, и его можно получить при помощи функции pg_relation_filenode() .

Когда объём таблицы или индекса превышает 1 GB, они делятся на сегменты размером в один гигабайт. Файл первого сегмента называется по номеру файлового узла (filenode); последующие сегменты получают имена filenode.1, filenode.2 и т. д. При такой организации хранения не возникает проблем на платформах, имеющих ограничения по размеру файлов. (На самом деле, 1 ГБ — лишь размер по умолчанию. Размер сегмента можно изменить при сборке Postgres Pro , используя параметр конфигурации —with-segsize .) В принципе, карты свободного пространства и карты видимости также могут занимать нескольких сегментов, хотя на практике это маловероятно.

У таблицы, столбцы которой могут содержать данные большого объёма, будет иметься собственная таблица TOAST, предназначенная для отдельного хранения значений, которые слишком велики для хранения в строках самой таблицы. Основная таблица связывается с её таблицей TOAST (если таковая имеется) через pg_class . reltoastrelid . За подробной информацией обратитесь к Разделу 63.2.

Содержание таблиц и индексов рассматривается ниже (см. Раздел 63.6).

Табличное пространство делает сценарий более сложным. Каждое пользовательское табличное пространство имеет символическую ссылку внутри каталога PGDATA /pg_tblspc , указывающую на физический каталог табличного пространства (т. е., положение, указанное в команде табличного пространства CREATE TABLESPACE ). Эта символическая ссылка получает имя по O >Postgres Pro , как например PG_9.0_201008051 . (Этот подкаталог используется для того, чтобы последующие версии базы данных могли свободно использовать одно и то же местоположение, заданное в CREATE TABLESPACE .) Внутри каталога конкретной версии находится подкаталог для каждой базы данных, которая имеет элементы в табличном пространстве, названный по O >pg_default недоступно через pg_tblspc , но соответствует PGDATA /base . Подобным же образом, табличное пространство pg_global недоступно через pg_tblspc , но соответствует PGDATA /global .

Функция pg_relation_filepath() показывает полный путь (относительно PGDATA ) для любого отношения. Часто это избавляет от необходимости запоминать многие из приведённых выше правил. Но следует помнить, что эта функция выдаёт лишь имя первого сегмента основного слоя отношения, т. е. возможно, понадобится добавить номер сегмента и/или _fsm , _vm или _init , чтобы найти все файлы, связанные с отношением.

Временные файлы (для таких операций, как сортировка объёма данных большего, чем может уместиться в памяти) создаются внутри PGDATA /base/pgsql_tmp или внутри подкаталога pgsql_tmp каталога табличного пространства, если для них определено табличное пространство, отличное от pg_default . Имя временного файла имеет форму pgsql_tmp PPP . NNN , где PPP — P > NNN служит для разделения различных временных файлов этого серверного процесса.

Ссылка на основную публикацию
Adblock detector