Postgresql где лежат базы

Postgresql где лежат базы

This section describes the storage format at the level of files and directories.

Traditionally, the configuration and data files used by a database cluster are stored together within the cluster’s data directory, commonly referred to as PGDATA (after the name of the environment variable that can be used to define it). A common location for PGDATA is /var/lib/pgsql/data . Multiple clusters, managed by different server instances, can exist on the same machine.

The PGDATA directory contains several subdirectories and control files, as shown in Table 73.1. In addition to these required items, the cluster configuration files postgresql.conf , pg_hba.conf , and pg_ident.conf are traditionally stored in PGDATA , although it is possible to place them elsewhere.

Table 73.1. Contents of PGDATA

For each database in the cluster there is a subdirectory within PGDATA /base , named after the database’s OID in pg_database . This subdirectory is the default location for the database’s files; in particular, its system catalogs are stored there.

Note that the following sections describe the behavior of the builtin heap table access method, and the builtin index access methods. Due to the extensible nature of PostgreSQL , other access methods might work differently.

Each table and index is stored in a separate file. For ordinary relations, these files are named after the table or index’s filenode number, which can be found in pg_class . relfilenode . But for temporary relations, the file name is of the form t BBB _ FFF , where BBB is the backend ID of the backend which created the file, and FFF is the filenode number. In either case, in addition to the main file (a/k/a main fork), each table and index has a free space map (see Section 73.3), which stores information about free space available in the relation. The free space map is stored in a file named with the filenode number plus the suffix _fsm . Tables also have a visibility map, stored in a fork with the suffix _vm , to track which pages are known to have no dead tuples. The visibility map is described further in Section 73.4. Unlogged tables and indexes have a third fork, known as the initialization fork, which is stored in a fork with the suffix _init (see Section 73.5).

Caution

Note that while a table’s filenode often matches its OID, this is not necessarily the case; some operations, like TRUNCATE , REINDEX , CLUSTER and some forms of ALTER TABLE , can change the filenode while preserving the OID. Avoid assuming that filenode and table OID are the same. Also, for certain system catalogs including pg_class itself, pg_class . relfilenode contains zero. The actual filenode number of these catalogs is stored in a lower-level data structure, and can be obtained using the pg_relation_filenode() function.

When a table or index exceeds 1 GB, it is divided into gigabyte-sized segments. The first segment’s file name is the same as the filenode; subsequent segments are named filenode.1, filenode.2, etc. This arrangement avoids problems on platforms that have file size limitations. (Actually, 1 GB is just the default segment size. The segment size can be adjusted using the configuration option —with-segsize when building PostgreSQL .) In principle, free space map and visibility map forks could require multiple segments as well, though this is unlikely to happen in practice.

A table that has columns with potentially large entries will have an associated TOAST table, which is used for out-of-line storage of field values that are too large to keep in the table rows proper. pg_class . reltoastrelid links from a table to its TOAST table, if any. See Section 73.2 for more information.

The contents of tables and indexes are discussed further in Section 73.6.

Tablespaces make the scenario more complicated. Each user-defined tablespace has a symbolic link inside the PGDATA /pg_tblspc directory, which points to the physical tablespace directory (i.e., the location specified in the tablespace’s CREATE TABLESPACE command). This symbolic link is named after the tablespace’s OID. Inside the physical tablespace directory there is a subdirectory with a name that depends on the PostgreSQL server version, such as PG_9.0_201008051 . (The reason for using this subdirectory is so that successive versions of the database can use the same CREATE TABLESPACE location value without conflicts.) Within the version-specific subdirectory, there is a subdirectory for each database that has elements in the tablespace, named after the database’s OID. Tables and indexes are stored within that directory, using the filenode naming scheme. The pg_default tablespace is not accessed through pg_tblspc , but corresponds to PGDATA /base . Similarly, the pg_global tablespace is not accessed through pg_tblspc , but corresponds to PGDATA /global .

The pg_relation_filepath() function shows the entire path (relative to PGDATA ) of any relation. It is often useful as a substitute for remembering many of the above rules. But keep in mind that this function just gives the name of the first segment of the main fork of the relation — you may need to append a segment number and/or _fsm , _vm , or _init to find all the files associated with the relation.

Temporary files (for operations such as sorting more data than can fit in memory) are created within PGDATA /base/pgsql_tmp , or within a pgsql_tmp subdirectory of a tablespace directory if a tablespace other than pg_default is specified for them. The name of a temporary file has the form pgsql_tmp PPP . NNN , where PPP is the PID of the owning backend and NNN distinguishes different temporary files of that backend.

Submit correction

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.

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

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

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

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

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

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

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

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

• Для файлов в табличном пространстве по умолчанию, base/database_oid/filenode id for the relation
• Для файлов из прочих табличных пространств: pg_tblspc / tablespace_oid / tablespace_version_subdir / database_oid / filenode id отношения
• Для общих отношений: global/filenode id отношения

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

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

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

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

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

• base: в табличном пространстве по умолчанию
• 16396: в базе данных с oid’ом 16396
• 3720450 filenode id для таблицы с oid’ом 3720450

Определение базы данных по 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: это не табличное пространство по умолчанию
• 16709: это табличное пространство с oid’ом 16709
• PG_9.3_201306121: используется PostgreSQL 9.3 с версией каталога 201306121.
• 16499: база данных с oid’ом 16499
• 19401 таблица с relfilenode id 19401

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

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. Если Вы подозреваете повреждение базы данных, перед тем как что либо предпринять, прочтите и действуйте по советам с вики странички о повреждениях.

Структура каталогов и файлов PostgreSQL¶

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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