Что в себе хранит системный каталог postgresql

Sysadminium

База знаний системного администратора

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

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

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

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

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

То есть, если мы создадим новую базу данных, то её описание можно будет получить из системного каталога. Если мы в базе данных создадим таблицу, то её описание будет в системном каталоге.

Системный каталог PostgreSQL это набор таблиц, представлений и функций и все они располагаются в схеме pg_catalog.

Посмотреть таблицы или представления из системного каталога можно как с помощью SQL запросов, так и с помощью команд psql.

Схема pg_catalog есть в каждой базе данных. Таблички в pg_catalog будут описывать объекты для своей базы. Но есть и общие объекты кластера, которые не принадлежать какой-либо базе данных. Например список баз данных (pg_database), список табличных пространств, список пользователей – это общие объекты кластера. К общим объектам кластера можно обращаться из любой базы.

Можно обратить внимание, что все таблички в системном каталоге начинаются на pg_, а все столбцы начинаются с префикса связанного с таблицей. Например так: pg_database.datname.

Все таблички системного каталога имеют специальный столбец с уникальным идентификатором: OID. Именно OID используется для идентификации объектов. Поэтому любую базу данных легко переименовать, так как кластер её знает по OID, а не по имени. Кроме баз данных по OID идентифицируются имена пользователей, схемы, таблицы и так далее.

Практика

Создание базы и тестовых объектов

Создадим базу данных и подключимся к ней:

Создадим табличку employees (сотрудники), со столбцами:

Создадим представление (CREATE VIEW … AS …), которое будет показывать топ менеджеров. Это сотрудники у которых нет своего менеджера, то есть в столбце manager записан NULL:

Смотрим информацию о созданных объектах

Воспользуемся представлением pg_database из системного каталога и посмотрим на нашу базу data_catalog, которую мы только что сделали:

Вот так мы посмотрели описание нашей новой базы данных. Её oid = 24633, а oid владельца datdba = 10. Тут видна и другая информация.

Теперь из представления pg_namespace (список схем) посмотрим записи связанные со схемой public:

Уникальный идентификатор этой схемы = 2200, а владелец схемы имеет OID = 10.

Таблица системного каталога pg_class

Теперь посмотрим на табличку pg_class, в которой хранятся таблички и представления, индексы и последовательности. Все эти объекты в SQL называются relation (отношения), отсюда и префикс “rel“.

Выше мы видим в столбце relkind типы объектов:

Представление pg_tables и имена вместо OID

Теперь посмотрим на представление pg_tables и найдем все записи, где имя схемы public:

Тут мы видим табличку employees и её владельца postgres. То есть представление смогло нам показать имена вместо OID. Чуть позже разберем как сработало это представление.

Просмотр информации из системного каталога с помощью psql

Утилита psql может упростить работу с системным каталогом. Есть следующие команды:

К каждой команде можно добавить +, например \dt+ для получения дополнительной информации.

В качестве дополнительной информации у представления показан запрос, который это представление формирует!

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

Чтобы посмотреть на саму функцию, можно использовать \sf :

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

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

Источник

Русские Блоги

Структура хранилища Postgresql

1 логическая организационная структура

Определение понятия объектов базы данных в СУБД:

any defined object in a database that is used to store or reference data

Объекты базы данных в PG включают, например: таблицы кучи, индексы, последовательности, функции и т. Д.

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

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

2 Физическая структура организации

2.1 Файловая структура

Теперь, чтобы инициализировать кластер, используйте спецификацию initdb, чтобы указать путь генерации.

После указания tree наблюдает за структурой каталогов файлов PGDATA

2.2 Организационная структура обычного табличного файла

Давайте создадим таблицу ниже. Файл таблицы можно найти двумя способами:

Выполните запрос pg_class.relfilenode, чтобы получить relfilenode = 16384 таблицы tbl1. Для обычных файлов таблиц укажите номер файлового узла таблицы или индекса.

Таблица создается в библиотеке postgres, мы можем войти в каталог base / 13158, чтобы увидеть файл таблицы:

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

2.3 Организационная структура файла системной таблицы

После инициализации системы таблицы не создаются, но многие файлы таблиц были созданы в base / 13158 /. Эти файлы являются текущими системными таблицами базы данных, такими как pg_class. Обратите внимание, что relfilenode системной таблицы равен 0. Для поиска таблицы можно использовать функцию oid скрытого столбца или pg_relation_filepath. файл.

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

2.4 Табличное пространство

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

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

Создать табличное пространство

Где PG_10_201707211 определяется в соответствии с номером версии:

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

3 Организационная структура табличного файла

Табличный файл по умолчанию состоит из блока размером 8 КБ, а 8 КБ является базовой единицей чтения и записи базы данных. «Анализ ядра базы данных PostgreSQL» описывается так:

Каждая страница состоит из пяти частей.

Информация о текущей странице определяется в PageHeaderData:

После заголовка ItemIdData из кода видно, что это 4-байтовая структура, используемая для побитовой разборки. Он записывает смещение, биты атрибутов и длину кортежа.

Кортеж обычно состоит из нескольких частей:

The overall structure of a heap tuple looks like:

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

Информация, хранящаяся в HeapTupleHeaderData, является основой для реализации механизма MVCC.

4 Считанные данные таблицы

Источник

Sysadminium

База знаний системного администратора

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

Базы данных и схемы – это логическое распределение данных в кластере. А табличные пространства в PostgreSQL относится к физическому расположению данных – то есть, в каких каталогах хранятся файлы базы данных. Объекты базы данных могут хранится в разных табличных пространствах, другими словами в разных каталогах.

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

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

Каталог $PGDATA/base разбит на подкаталоги по идентификаторам баз данных.

Когда мы создаём своё табличное пространство, мы сами указываем нужный каталог. При этом PostgreSQL создает символическую ссылку $PGDATA/pg_tblspc/ на указанный каталог (/путь/ver/dboid).

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

Табличное пространство по умолчанию для базы определяется шаблоном из которого клонируется новая база. Таким образом если мы поменяли табличное пространство по умолчанию в шаблоне template1, то и новые базы созданные из этого шаблона получат новое табличное пространство по умолчанию.

Практика

Существует общая табличка pg_tablespace, в которой хранится список табличных пространств:

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

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

Список табличных пространств можно получить командой \db :

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

Подключимся к этой базе и создадим табличку. А затем создадим ещё одну табличку, но в другом табличном пространстве:

Используя табличку pg_tables, посмотрим на таблицы в этой базе, нас будут интересовать столбцы tablename и tablespace. Чтобы исключить схему

Видим 2 таблички, вторая в пространстве pg_default, а у первой пространство не указано. Если табличное пространство не указано, значит она находится в пространстве по умолчанию для данной базы данных.

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

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

Занимаемый объём табличного пространства можно посмотреть с помощью функции pg_tablespace_size():

Табличное пространство ts является пространством по умолчанию для базы appdb, в нем сейчас 2 пустые таблицы и объекты системного каталога для базы appdb.

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

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

Выясним OID табличного пространства из pg_tablespace:

Этот OID запишем в переменную tsoid с помощью \gset:

С помощью функции pg_tablespace_databases( ) можем узнать oid баз данных в табличном пространстве:

Следующим запросом полученные OID баз превратим в имена баз:

Теперь нужно подключиться к каждой базе и получить список объектов из pg_class, где табличное пространство равно ts:

Можем удалить табличку t:

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

При смене табличного пространства по умолчанию все объекты системного каталога переезжают в новое табличное пространство.

Вот теперь мы можем удалить табличное пространство ts:

Источник

Как определить каким файлам на диске соответствуют PostgreSQL таблицы

Иногда вам нужно определить какому файлу на диске соответствует таблица. У вас имеется путь, полный цифр, такой как base/16499/19401 и вы хотите разобраться в нем. Вы можете смотреть на сообщение об ошибке, которое упоминает имя файла, например:

В поисках пути отношения

Вы можете увидеть путь до таблицы используя:

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

Структура пути до файла

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

Имеется 3 основных варианта пути:

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

Обратите внимание, я употребил формулировку «filenode id отношения«, а не «oid отношения«. Это связано с тем, что PostgreSQL имеет карту relfilenode в файле с именем pg_relfilenode.map для каждой базы данных/табличного пространства. Имена файлов таблиц вовсе не обязательно совпадают с их oid’ами из pg_class, и они могут измениться после запуска VACUUM FULL, TRUNCATE и прочих. К примеру:

Итак. Как превратить этот путь обратно в имя отношения?

Oid’ы базы данных и filenode ids отношения

Предположим, Вы получили ошибку из начала этой статьи. Ее можно разбить на несколько частей:

Определение базы данных по oid

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

(или любой другой oid базы, который вы имеете). Это вернет Вам имя базы данных.

После этого необходимо подключиться к этой базе.

Обратное преобразование relfilenodes на 9.4 версии

Если Вы используете версию 9.4, или более свежую, то для Вас следующая часть проста:

(0 означает «табличное пространство по умолчанию»)

Эта функция выполняет обратное преобразование relfilenode за вас. Таким образом, она просто покажет Вам имя таблицы. Для него не будет показана связь с какой-то схемой, если полученное имя таблицы принадлежит текущему search_path; Можно использовать SET search_path = »; перед выполнением функции, для того, чтобы был указан путь вплоть до схемы.

Вы должны быть подключены к правильной базе данных, или будет получен неправильный ответ, либо вообще ответ не будет получен.

Обратное преобразование relfilenodes на 9.3 версии

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

(или любой другой полученный relfilenode id таблицы).

Это расскажет Вам о том, к какой таблице относится эта ошибка.

Нет результатов?

Что ж, обычно это помогает.

Relfilenode также может быть нулем, это в свою очередь означает, что файл расположен посредством pg_relfilenode.map. Это является типичным сценарием для общих и некоторых системных каталогов, их индексов, TOAST таблиц и т.д. К примеру, это могут быть pg_database, pg_class и pg_proc.

Что насчет схемы?

Вы обратили внимание, что схема (пространство имен) не фигурирует в пути?

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

Другие пути табличных пространств

Недавний случай, с которым я столкнулся, был следующей ошибкой:

Это не табличное пространство по умолчанию, так как путь начинается с pg_tblspc.

Сам процесс нахождения таблицы на самом деле тот же. Можно проигнорировать pg_tblspc/nnn/PG_n.n_nnnnnn/ часть и сфокусироваться сразу на database_oid/relation_oid, как описано выше для случаев с табличным пространством по умолчанию. Для этого стоит понимать что означает путь.

Таким образом текст ошибки разбивается на следующие части:

Так что насчет части с табличным пространством?

pg_tblspc является директорией в директории данных PostgreSQL, которая содержит в себе символьные ссылки ко всем положениям табличных пространств (или на NTFS, точки соединения для них). Каждая символьная ссылка названа в честь oid табличного пространства. Именно так PostgreSQL находит табличные пространства. SQL команды к табличным пространствам оперируют этими ссылками.

Oid относится к pg_tablespace записи для табличного пространства, как видно из:

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

В целом же, структура та же как и для base/ путей — сначала oid базы данных, потом oid отношения.

Глобальные (общие) таблицы

Имеется еще третья категория ошибок, в случае если Вы ее наблюдаете, то Вы определенно в беде. PostgreSQL имеет общие каталоги — таблицы, которые имеют одинаковое содержимое в каждой базе данных. Они обитают в специальном табличном пространстве с relfilenode id 16709.

Пути к ним начинаются с global вместо base и у них отсутствует компонент с oid’ом базы данных.

Общие каталоги не отмечены relfilenode в pg_class. То есть Вы не сможете посмотреть, к примеру, pg_database из pg_class. pg_filenode_relation возвращает null, независимо от того, вызывать ли его с oid’ом табличного пространства по умолчанию, или же с oid’ом глобального табличного пространства 1664.

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

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

Имея дело с повреждениями

Повреждения базы данных не должно случаться. Но оно может произойти в любом случае. Это могут быть проблемы с железом, баги ядра, или файловой системы, ССД, которые врут о совершении надежных дисковых приливах, глючные сети хранения данных, ну и конечно же баги самого PostgreSQL. Если Вы подозреваете повреждение базы данных, перед тем как что либо предпринять, прочтите и действуйте по советам с вики странички о повреждениях.

Внутренности

Чтобы увидеть как все это работает, запустите макрос relpathbackend в src/include/common/relpath.h. Он вызывает GetRelationPath в src/common/relpath.c.

В мануале рассматривается структура базы данных на диске. Ссылка.

Источник

Sysadminium

База знаний системного администратора

Общее устройство PostgreSQL

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

Клиент серверная работа PostgreSQL

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

Клиент – это какое-то приложение, например psql. Клиент с сервером общается по определённому протоколу. Протокол у PostgreSQL открытый, но для каждого приложения его не нужно реализовывать. Обычно используют стандартные библиотеки и драйверы. Основная библиотека это libpq, её использует psql и все штатные утилиты PostgreSQL. Для многих языков программирования есть свои библиотеки, которые основаны на libpq.

Алгоритм работы с сервером примерно такой:

Транзакции SQL

Транзакция – это группа последовательных операций с базой данных, которые выполняются как одна операция.

Транзакция имеет определённые требования:

За выполнение атомарности, согласованности и изолированности отвечает много-версионность. За долговечностью следит другой механизм – журнал.

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

Запрос в процессе своего выполнения проходит 4 стадии:

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

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

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

Процессов на сервере PostgreSQL много, но выделяют три вида:

Память выделяемая для работы PostgreSQL тоже бывает разных типов:

Пулы соединений

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

Встроенного пула в PostgreSQL нет, но клиент сам может его реализовать.

Двойная буферизация PostgreSQL

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

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

Надежность PostgreSQL при сбоях

Чтобы гарантировать восстановление после сбоя PostgreSQL использует журнал предварительной записи – WAL. Этот журнал позволяет после сбоя восстановить согласованность данных. Подробнее разберу его позже, а пока просто запомните, что такой механизм присутствует.

Расширяемость PostgreSQL

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

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

Источник