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

PostgreSQL (Русский)

Состояние перевода: На этой странице представлен перевод статьи PostgreSQL. Дата последней синхронизации: 21 ноября 2022. Вы можете помочь синхронизировать перевод, если в английской версии произошли изменения.

PostgreSQL — это поддерживаемая сообществом система управления базами данных с открытым исходным кодом.

Установка

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

Установите пакет postgresql . Он также создаст системного пользователя postgres.

Для переключения в пользователя postgres можно использовать программу для повышения привилегий.

Примечание: Команды, которые нужно запускать от имени пользователя postgres, в данной статье обозначены префиксом [postgres]$ .

Для переключения в пользователя postgres можно использовать одну из следующих команд:

Если у вас есть sudo и ваш пользователь прописан в sudoers:

$ sudo -iu postgres

$ su # su -l postgres

Смотрите также документацию sudo(8) или su(1) .

Начальная настройка

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

[postgres]$ initdb -D /var/lib/postgres/data

Где опция -D указывает на стандартное расположение данных кластера (если вы хотите использовать другой каталог, смотрите раздел #Изменение стандартного каталога данных). initdb принимает дополнительные аргументы:

• По умолчанию локаль и кодировка для кластера баз данных наследуются из вашего текущего окружения (используется значение $LANG). Если вас это не устраивает, вы можете прописать нужные параметры вручную с помощью опций —locale=локаль (где локаль должна быть одной из доступных системных локалей) и —encoding=кодировка для выбора кодировки (должна соответствовать выбранной локали). (После настройки базы данных вы сможете посмотреть используемые значения командой [postgres]$ psql -l .)
• Если каталог с данными расположен на файловой системе без контроля целостности данных, вы можете включить встроенный в PostgreSQL подсчёт контрольных сумм для повышения гарантий целостности — для этого добавьте аргумент —data-checksums . Дополнительная информация описана в разделе #Включение подсчёта контрольных сумм. (После настройки базы данных вы сможете посмотреть. включена ли эта функция, командой [postgres]$ psql -c «SHOW data_checksums» .)
• Другие доступные опции можно посмотреть в initdb —help или официальной документации.

Пример для русской локали:

[postgres]$ initdb --locale=ru_RU.UTF-8 --encoding=UTF8 -D /var/lib/postgres/data --data-checksums

После инициализации на экране появится много строчек, некоторых из которых оканчиваются на . ок :

Файлы, относящиеся к этой СУБД, будут принадлежать пользователю "postgres". От его имени также будет запускаться процесс сервера. Кластер баз данных будет инициализирован с локалью "ru_RU.UTF-8". Кодировка БД по умолчанию, выбранная в соответствии с настройками: "UTF8". Выбрана конфигурация текстового поиска по умолчанию "russian". Контроль целостности страниц данных отключён. исправление прав для существующего каталога /var/lib/postgres/data. ок создание подкаталогов. ок выбирается реализация динамической разделяемой памяти. posix выбирается значение max_connections по умолчанию. 100 выбирается значение shared_buffers по умолчанию. 128MB выбирается часовой пояс по умолчанию. Europe/Moscow создание конфигурационных файлов. ок выполняется подготовительный скрипт. ок выполняется заключительная инициализация. ок сохранение данных на диске. ок initdb: предупреждение: включение метода аутентификации "trust" для локальных подключений Другой метод можно выбрать, отредактировав pg_hba.conf или используя ключи -A, --auth-local или --auth-host при следующем выполнении initdb. Готово. Теперь вы можете запустить сервер баз данных: pg_ctl -D /var/lib/postgres/data -l файл_журнала start

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

Примечание: Подробнее об этом предупреждении читайте в разделе #Ограничение доступа к суперпользователю по умолчанию.

Совет: Если вы хотите использовать путь отличный от /var/lib/postgres , нужно отредактировать файл службы systemd. Если вы помещаете его в /home , не забудьте отключить ProtectHome .

• Если база данных располагается на файловой системе Btrfs, стоит отключить копирование при записи для каталога перед созданием любых баз данных.
• Если база данных располагается на файловой системе ZFS, прочтите ZFS#Databases перед созданием любых баз данных.

Наконец, запустите и включите службу postgresql.service .

Создание первой базы данных

Совет: Если имя роли/пользователя совпадает с именем вашего пользователя в Linux, вы сможете получить доступ к оболочке PostgreSQL без явного указания имени пользователя (что весьма удобно).

Становимся пользователем postgres. Добавляем нового пользователя базы данных с помощью команды createuser:

[postgres]$ createuser --interactive

Создаём новую базу данных от имени пользователя, имеющего доступ на чтение-запись, с помощью команды createdb (выполните эту команду в оболочке вашего обычного пользователя, если имя будущего владельца базы данных совпадает с вашим именем пользователя в Linux, в ином случае добавьте опцию -O имя-пользователя )

$ createdb имяМоейБазы

Совет: Если вы не выдали разрешение на создание баз данных вашему свежесозданному пользователю, добавьте опцию -U postgres к этой команде.

Знакомство с PostgreSQL

Доступ к оболочке базы данных

Становимся postgres пользователем. Запускаем основную оболочку базы данных psql, в которой мы сможем создавать, удалять базы данных/таблицы, задавать права и запускать команды SQL. Используйте опцию -d , чтобы указать название базы данных, которую вы создали (если опцию не указать, то psql попытается подключиться к базе, имя которой совпадает с именем пользователя).

[postgres]$ psql -d имяМоейБазы

Некоторые полезные команды:

=> \help

Подключение к определённой базе данных:

Список всех пользователей и их уровни доступа:

Краткая информация о всех таблицах в текущей базе данных:

Выход из оболочки psql :

=> \q или CTRL+d

Есть, конечно, много других мета-команд, но именно эти должны помочь вам начать работу. Для просмотра всех мета-команд введите:

Дополнительные настройки

Файл настроек сервера баз данных PostgreSQL — postgresql.conf . Этот файл находится в папке данных сервера, обычно /var/lib/postgres/data . В этой же папке находятся основные файлы настроек включая и pg_hba.conf , который определяет параметры аутентификации, как для локальных пользователей, так и для пользователей с других хостов.

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

Ограничение доступа к суперпользователю по умолчанию

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

/var/lib/postgres/data/pg_hba.conf

# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust

/var/lib/postgres/data/pg_hba.conf

# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all postgres peer

Можно добавить дополнительные строки в зависимости от ваших потребностей.

Требование пароля при входе

Измените /var/lib/postgres/data/pg_hba.conf , прописав метод аутентификации для каждого пользователя (или «all» для всех пользователей) на scram-sha-256 (предпочтительно) или md5 (менее безопасно; по возможности стоит его избегать):

/var/lib/postgres/data/pg_hba.conf

# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all пользователь scram-sha-256

Если вы выбрали scram-sha-256 , также нужно изменить /var/lib/postgres/data/postgresql.conf :

/var/lib/postgres/data/postgresql.conf

password_encryption = scram-sha-256

Перезапустите службу postgresql.service и заново пропишите пароли для пользователей с помощью SQL-запроса ALTER USER пользователь WITH ENCRYPTED PASSWORD ‘пароль‘; .

Доступ только через Unix-сокет

В разделе «connections and authentication» пропишите:

/var/lib/postgres/data/postgresql.conf

listen_addresses = ''

Это полностью отключит доступ через сеть. Не забудьте перезапустить службу postgresql.service для применения изменений.

Доступ с удалённых хостов

В разделе «connections and authentication» раскомментируйте или исправьте строку listen_addresses по вашему желанию, например:

/var/lib/postgres/data/postgresql.conf

listen_addresses = 'localhost,мой_локальный_ip'

Можно использовать ‘*’ для прослушивания всех доступных сетевых интерфейсов.

Примечание: PostgreSQL по умолчанию использует TCP-порт 5432 для удалённого доступа. Убедитесь, что этот порт открыт в вашем межсетевом экране и может принимать входящие соединения. Изменить порт можно в этом же файле настроек, под строкой listen_addresses .

Затем измените настройки аутентификации:

/var/lib/postgres/data/pg_hba.conf

# TYPE DATABASE USER ADDRESS METHOD # IPv4 local connections: host all all ip_адрес/32 md5

где ip_адрес — IP-адрес удалённого клиента.

Смотрите также документацию по pg_hba.conf.

Примечание: Ни отправка простого пароля, ни отправка md5-хэша (использованный в приведённом выше примере) через интернет не являются безопасными, если это не выполняется через защищенное SSL-соединение. Смотрите Secure TCP/IP Connections with SSL, чтобы узнать, как настроить PostgreSQL с использованием SSL.

Перезапустите службу postgresql.service для применения изменений.

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

# journalctl -u postgresql.service

Настройка аутентификации через PAM

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

Например, та же конфигурация, что и выше, но с включенным PAM:

/var/lib/postgres/data/pg_hba.conf

# IPv4 local connections: host all all ip_адрес/32 pam

Однако сервер PostgreSQL работает без прав root и не сможет получить доступ к файлу /etc/shadow . Мы можем обойти это, разрешив группе postgres доступ к этому файлу:

# setfacl -m g:postgres:r /etc/shadow

Изменение стандартного каталога данных

По умолчанию PostgreSQL настроен на использование каталога /var/lib/postgres/data для хранения всех баз данных. Для его изменения выполните следующие шаги:

Создайте новый каталог и сделайте пользователя postgres его владельцем:

# mkdir -p /путь/к/pgroot/data # chown -R postgres:postgres /путь/к/pgroot

Войдите в пользователя postgres и выполните инициализацию кластера:

[postgres]$ initdb -D /путь/к/pgroot/data

Отредактируйте службу postgresql.service , создав drop-in файл и переопределив настройки Environment и PIDFile . Например:

/etc/systemd/system/postgresql.service.d/PGROOT.conf

[Service] Environment=PGROOT=/путь/к/pgroot PIDFile=/путь/к/pgroot/data/postmaster.pid

Если вы хотите использовать каталог в /home , добавьте ещё одну строку:

ProtectHome=false

Изменение кодировки новых баз данных на UTF-8

Примечание: Если вы выполнили initdb с опцией —encoding=UTF8 или с использованием локали UTF-8, выполнять эти шаги не нужно.

Когда создаётся новая база данных (например, командой createdb blog ), PostgreSQL просто копирует шаблон базы данных. Есть два стандартных шаблона: template0 — ванильный, и template1 , который используется по умолчанию и предназначен для редактирования администратором. Один из вариантов изменения кодировки новой базы данных — изменить шаблон template1 . Для этого зайдите в оболочку PostgreSQL ( psql ) и выполните следующее:

Сперва нужно удалить template1 . Шаблоны нельзя удалять, так что сперва нужно преобразовать его в обычную базу данных:

UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';

Теперь можно удалить:

DROP DATABASE template1;

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

CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UNICODE';

Теперь снова сделайте template1 шаблоном:

UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';

По желанию, если вы не хотите, чтобы кто-либо подключался к этому шаблону, присвойте параметру datallowconn значение FALSE :

UPDATE pg_database SET datallowconn = FALSE WHERE datname = 'template1';

Примечание: Этот шаг может привести к проблемам при обновлении через pg_upgrade .

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

[postgres]$ createdb blog

Если снова войти в psql и проверить базу данных, вы должны увидеть правильную кодировку новой базы данных:

List of databases Name | Owner | Encoding | Collation | Ctype | Access privileges -----------+----------+-----------+-----------+-------+---------------------- blog | postgres | UTF8 | C | C | postgres | postgres | SQL_ASCII | C | C | template0 | postgres | SQL_ASCII | C | C | =c/postgres : postgres=CTc/postgres template1 | postgres | UTF8 | C | C |

Включение подсчёта контрольных сумм

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

• Есть небольшое влияние на производительность, особенно при чтении больших массивов данных с диска. На операции в памяти это не влияет.
• PostgreSQL не может исправить повреждённые данные — он только прервёт транзакции, читающие с повреждённых страниц, чтобы предотвратить дальнейшее повреждение или получение некорректных результатов выполнения.
• Контрольные суммы охватывают только страницы данных (строк) на диске, но не метаданные или управляющие структуры. Страницы в памяти не проверяются. Хранилища с коррекцией ошибок и память с ECC по-прежнему полезны.

• Чтобы включить подсчёт контрольных сумм при создании кластера, добавьте аргумент —data-checksums к команде initdb .
• Чтобы проверить, включен ли подсчёт контрольных сумм, выполните [postgres]$ psql -c «SHOW data_checksums» (выведется off или on ).
• Чтобы включить подсчёт контрольных сумм на существующем кластере:

1. Остановите службу postgresql.service .
2. Выполните команду [postgres]$ pg_checksums —pgdata /var/lib/postgres/data —enable (или —disable , если вы хотите наоборот выключить эту функцию). Включение подсчёта контрольных сумм приведёт к перезаписи всех страниц базы данных, что займет некоторое время в больших базах данных.
3. Запустите службу postgresql.service .

Графические инструменты

• phpPgAdmin — Веб-интерфейс для администрирования PostgreSQL.

• pgAdmin — Комплексный графический интерфейс для управления PostgreSQL.

• pgModeler — Инструмент для моделирования баз данных PostgreSQL.

Список инструментов, поддерживающих несколько разных СУБД, можно посмотреть в статье List of applications/Documents#Database tools.

Обновление PostgreSQL

This article or section needs expansion.

Reason: How to upgrade when using third party extensions? (Discuss in Talk:PostgreSQL#pg_upgrade problem if extensions (like postgis) are used)

Для обновления до новой мажорной версии PostgreSQL (например, с версии 13.x на версию 14.y) необходима специальная процедура.

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

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

Посмотреть текущую версию базы данных можно так:

# cat /var/lib/postgres/data/PG_VERSION

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

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

Есть два основных способа обновить базу данных PostgreSQL. Подробности читайте в официальной документации.

pg_upgrade

Утилита pg_upgrade пытается скопировать как можно больше совместимых данных между кластерами и обновить всё остальное. Как правило, это самый быстрый метод обновления большинства экземпляров, хотя он требует доступа к бинарным файлам исходной и целевой версий PostgreSQL. Прочтите справочную страницу pg_upgrade(1) , чтобы понять, какие действия он выполняет. Для нетривиальных экземпляров (например, с потоковой репликацией или трансляцией журналов) сперва ознакомьтесь с официальной документацией.

Для тех, кто хочет использовать pg_upgrade , доступен пакет postgresql-old-upgrade , который всегда отстаёт на одну мажорную версию от основного пакета PostgreSQL. Его можно установить параллельно с новой версией PostgreSQL. Для обновления более старых версий PostgreSQL доступны пакеты AUR, например postgresql-12-upgrade AUR . (Нужно использовать команду pg_upgrade из той версии PostgreSQL, на которую вы хотите обновиться.)

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

Когда вы будете готовы к обновлению, выполните следующие шаги:

1. Пока старая база данных всё ещё доступна, соберите аргументы для команды initdb , которые использовались при создании базы. Команды для просмотра текущих настроек кластера описаны в разделе #Начальная настройка.
2. Остановите службу postgresql.service . (Проверьте статус юнита, чтобы убедиться, что PostgreSQL завершился корректно, иначе pg_upgrade не сможет отработать корректно.)
3. Обновите пакеты postgresql , postgresql-libs и postgresql-old-upgrade .
4. Переименуйте каталог со старым кластером и создайте каталог для нового кластера и временный каталог:

# mv /var/lib/postgres/data /var/lib/postgres/olddata # mkdir /var/lib/postgres/data /var/lib/postgres/tmp # chown postgres:postgres /var/lib/postgres/data /var/lib/postgres/tmp [postgres]$ cd /var/lib/postgres/tmp

[postgres]$ initdb -D /var/lib/postgres/data --locale=ru_RU.UTF-8 --encoding=UTF8 --data-checksums

[postgres]$ pg_upgrade -b /opt/pgsql-PG_VERSION/bin -B /usr/bin -d /var/lib/postgres/olddata -D /var/lib/postgres/data

Примечание: Не забудьте обновить файлы конфигурации (например, pg_hba.conf и postgresql.conf ) для соответствия старому кластеру.

Примечание: Если pg_upgrade завершается с ошибкой The source cluster was not shut down cleanly , значит PostgreSQL не был остановлен перед запуском обновления. Остановите его, затем перезапустите кластер со старыми бинарными файлами, чтобы восстановить старые файлы кластера:

[postgres]$ /opt/pgsql-PG_VERSION/bin/pg_ctl start -D /var/lib/postgres/olddata && /opt/pgsql-PG_VERSION/bin/pg_ctl stop -D /var/lib/postgres/olddata

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

Выгрузка и загрузка вручную

Ещё можно сделать что-то вроде такого (после обновления и установки postgresql-old-upgrade ):

• В примере показано обновление с PostgreSQL 13; посмотрите в /opt/ установленную у вас версию postgresql-old-upgrade и исправьте команды по необходимости.
• Если вы меняли файл pg_hba.conf , вам может понадобиться временно разрешить полный доступ к старому кластеру с локальной системы. После обновления не забудьте прописать нужные вам настройки в новом кластере и перезапустить службу postgresql.service .

# mv /var/lib/postgres/data /var/lib/postgres/olddata # mkdir /var/lib/postgres/data # chown postgres:postgres /var/lib/postgres/data [postgres]$ initdb -D /var/lib/postgres/data [postgres]$ /opt/pgsql-13/bin/pg_ctl -D /var/lib/postgres/olddata/ start # cp /usr/lib/postgresql/postgis-3.so /opt/pgsql-13/lib/ # Только если установлен postgis [postgres]$ pg_dumpall -h /tmp -f /tmp/old_backup.sql [postgres]$ /opt/pgsql-13/bin/pg_ctl -D /var/lib/postgres/olddata/ stop

[postgres]$ psql -f /tmp/old_backup.sql postgres

Решение проблем

Ускорение мелких транзакций

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

/var/lib/postgres/data/postgresql.conf

synchronous_commit = off

Запретить запись на диск во время бездействия

PostgreSQL периодически обновляет свою статистику, лежащую в файле. По умолчанию этот файл находится на диске, что не даёт отдыхать жёсткому диску (и изнашивает его), заставляя его шуметь. Однако можно легко и безопасно переместить статистику в ОЗУ с помощью такой настройки:

/var/lib/postgres/data/postgresql.conf

stats_temp_directory = '/run/postgresql'

Проблемы с pgAdmin 4 после обновления до PostgreSQL 12

Если вы видите ошибки вроде string indices must be integers при навигации по дереву слева или column rel.relhasoids does not exist при просмотре данных, удалите сервер из списка соединений в pgAdmin и добавьте его заново. Без этого pgAdmin продолжает считать его сервером PostgreSQL 11, что и приводит к таким ошибкам.

PostgreSQL не может запуститься после обновления пакета при использовании расширений

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

Не удаётся запустить PostgreSQL со старой версией базы данных при обновлении до новой версии с расширениями

Это происходит потому, что старая версия postgres из пакета postgresql-old-upgrade не имеет необходимых расширений (.so файлов) в своём каталоге lib. Предлагаемое здесь решение грязное и может вызвать много проблем, поэтому сохраните резервную копию базы данных на всякий случай. В целом, скопируйте необходимые .so файлы расширений из /usr/lib/postgresql/ в /opt/pgsql-XX/lib/ (не забудьте заменить XX на мажорную версию пакета postgresql-old-upgrade ).

Например, для timescaledb:

# cp /usr/lib/postgresql/timescaledb*.so /opt/pgsql-13/lib/

Важно: Хотя копирования .so файлов обычно достаточно, может понадобиться скопировать больше файлов в правильные расположения в /opt/pgsql-XX/ .

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

$ pacman -Ql имя_пакета 

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

Retrieved from «https://wiki.archlinux.org/index.php?title=PostgreSQL_(Русский)&oldid=786739»

• Pages with broken package links
• Pages or sections flagged with Template:Expansion

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

Собссно сабж. Столкнулся с задачей переноса базы на другой раздел диска. Ваще непонятно как указать PostGRE где должна лежать база. Или это нужно указывать серверу 1С 8.1? ХЕЛП!

Версии:
Postgre — win 8.1.5-11.1C
1С — 8.1.6.

В Postgre есть в настройках переменная data_directory. Может это оно?
А в 8-ке тоже лежат файлики в папке установки 1С/Server.

Так где же все ж лежит база и как указать к ней другой путь?

Если у тебя винда, то база хранится
C:/Program Files/PostgreSQL//data/

в linux /var/lib/pgsql/data/

В винде другой путь для хранения баз можно указать при установке PostgreSQL.
Тут на сколько я знаю есть один ньанс: База и сами файлы постгри хранятся в одной папке.

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

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

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

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

/usr/lib/postgresql/9.3/bin/postgres "-D" "/var/lib/postgresql/9.3/main" "-c" "config_file=/etc/postgresql/9.3/main/postgresql.conf" 

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

root@server: /var/lib/postgresql/9.3/main/base# ls 1/ 12065/ 12070/ 16384/ 24580/

denis=# select oid, datname from pg_database; oid | datname -------+----------- 1 | template1 12065 | template0 12070 | postgres 16384 | denis 24580 | t1 (5 rows) 

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

Количество хранимых транзакций ограничено параметром autovacuum_freeze_max_age (максимальное значение ~ 2 миллиарда), который в свою очередь регулирует работу автовакума. Поэтому максимальный размер файла может быть ~ 0.5 Гб максимум.

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

BEGIN denis=# SELECT pg_export_snapshot(); pg_export_snapshot -------------------- 00000312-1 (1 row) 

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

root@server: /var/lib/postgresql/9.3/main/pg_snapshots# ls 00000312-1 root@server: /var/lib/postgresql/9.3/main/pg_snapshots# cat 00000312-1 xid:786 dbid:16384 iso:1 ro:0 xmin:786 xmax:786 xcnt:0 sof:0 sxcnt:0 rec:0

Отмена транзакции

denis=# rollback; ROLLBACK 

Файл удален

root@server: /var/lib/postgresql/9.3/main/pg_snapshots# l ----

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

denis=# CREATE TABLESPACE tmp LOCATION '/home/denis/1'; CREATE TABLESPACE 

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

denis=# select oid, spcname from pg_tablespace; oid | spcname -------+------------ 1663 | pg_default 1664 | pg_global 27936 | tmp (3 rows) 

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

/var/lib/postgresql/9.3/main/pg_tblspc# ll total 8 drwx------ 2 postgres postgres 4096 Nov 4 09:51 ./ drwx------ 15 postgres postgres 4096 Nov 4 07:05 ../ lrwxrwxrwx 1 postgres postgres 13 Nov 4 09:51 27936 -> /home/denis/1/

Как Postgres хранит строки

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

Мы будем рассматривать только файлы кучи (heap). Heap-файл — это просто файл записей. Не путайте heap-файл с heap-памятью. Хотя их использование очень похоже: хранение динамических данных.

Как строки организованы в файлах

PostgreSQL разбивает файлы данных на сегменты. Размер сегмента фиксирован и обычно составляет 1 ГБ, но его можно изменить при компиляции, используя параметр —with-segsize . Когда размер таблицы или индекса превышает 1 ГБ, они разбиваются на сегменты по гигабайту. Такой подход позволяет избежать проблем на платформах с ограничением размера файла, 1 ГБ — очень консервативный выбор для любой современной платформы.

Данные хранятся на страницах фиксированного размера. Размер страницы обычно 8 КБ, который можно изменить при компиляции через параметр —with-blocksize . Хотя изменяют его редко. Страница в 8 КБ — компромисс между производительностью и надежностью. Если размер страницы слишком мал, строки не поместятся внутри страницы, а если слишком большой, существует риск сбоя записи, поскольку оборудование обычно гарантирует атомарность только для блоков фиксированного размера, который может отличаться от диска к диску (обычно в диапазоне от 512 до 4096 байт).

Страницы выглядят следующим образом:

lp(1…N) — массив указателей на строки (line pointer array). Указатель содержит логическое смещение на странице. Поскольку это массив, то все элементы имеют фиксированный размер, но количество элементов на странице может изменяться.

row(1. N) — непосредственно данные. Обычно они переменной длины и для доступа к определенному кортежу используются указатели строк. Строки добавляются от конца к началу страницы, а указатели строк — от начала к концу.

Область special space обычно используется при хранении индексов, например, для ссылок на соседние узлы в B-Tree. Для табличных данных не используется.

Указатели lower и upper указывают занятое пространство на странице. Таким образом, по разности (upper — lower) можно определить, используется страница или нет.

Заголовок страницы (page header) сам по себе не так уж и интересен, он содержит информацию о странице, включая указатели lower и upper. Его структура выглядит следующим образом:

Поле

Размер

Описание

LSN: следующий байт после последнего байта записи xlog для последнего изменения на этой странице

Контрольная сумма страницы

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

Смещение до конца свободного пространства

Смещение до начала специального пространства (special space)

Информация о размере страницы и номере версии компоновки

Самый старый неочищенный идентификатор XMAX на странице или ноль при отсутствии такового

У такого способа хранения данных есть несколько особенностей:

• Во-первых, кортежи не могут занимать несколько страниц. Невозможно напрямую хранить очень большие значения полей. Значения больших полей сжимаются и/или разбиваются на несколько физических строк.
• Во-вторых, максимальное количество столбцов ограничено от 250 до 1600 в зависимости от типа столбца.
• В-третьих, если вы полагаете, что указав в select только нужные столбцы для таблицы с несколькими столбцами, запрос будет выполняться быстрее из-за меньшего количества дисковых операций ввода-вывода, то это не так.
• Такой тип хранилища не оптимизирован для аналитических рабочих нагрузок, потому что необходимо считывать с диска данные, не имеющие отношения к запросу. Поэтому большинство аналитических баз данных (OLAP), таких как Vertica, используют колоночное хранилище.

Заглянем внутрь страниц

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

postgres=# \c hail_mary ; You are now connected to database "hail_mary" as user "postgres". hail_mary=# hail_mary=# create table users(id int, name text); CREATE TABLE hail_mary=# insert into users(id, name) select i,md5(i::text)::text from generate_series(1, 50000, 1) as i; INSERT 0 50000 hail_mary=# select * from users limit 5; id | name ----+---------------------------------- 1 | c4ca4238a0b923820dcc509a6f75849b 2 | c81e728d9d4c2f636f067f89cc14862c 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 4 | a87ff679a2f3e71d9181a67b7542122c 5 | e4da3b7fbbce2345d7772b0674a318d5 (5 rows)

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

hail_mary=# select ctid, * from users limit 5; ctid | id | name -------+----+---------------------------------- (0,1) | 1 | c4ca4238a0b923820dcc509a6f75849b (0,2) | 2 | c81e728d9d4c2f636f067f89cc14862c (0,3) | 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 (0,4) | 4 | a87ff679a2f3e71d9181a67b7542122c (0,5) | 5 | e4da3b7fbbce2345d7772b0674a318d5 (5 rows)

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

hail_mary=# select ctid, * from users OFFSET 49995 limit 5; ctid | id | name ----------+-------+---------------------------------- (416,76) | 49996 | 2acfa04df8cc1e5b051866c32f9eb072 (416,77) | 49997 | 87aa98d07ec242cc4d8f685f0299257b (416,78) | 49998 | 12775d2a4498f0ec748a4beed90e5ad2 (416,79) | 49999 | c703af5c89b1d0bc2e99f540f553f182 (416,80) | 50000 | 1017bfd4673955ffee4641ad3d481b1c (5 rows) hail_mary=# hail_mary=# delete from users where name = 'c703af5c89b1d0bc2e99f540f553f182'; DELETE 1 hail_mary=# insert into users VALUES(9999999, 'ryland grace'); INSERT 0 1 hail_mary=# select ctid, * from users OFFSET 49995 limit 5; ctid | id | name ----------+---------+---------------------------------- (416,76) | 49996 | 2acfa04df8cc1e5b051866c32f9eb072 (416,77) | 49997 | 87aa98d07ec242cc4d8f685f0299257b (416,78) | 49998 | 12775d2a4498f0ec748a4beed90e5ad2 (416,80) | 50000 | 1017bfd4673955ffee4641ad3d481b1c (416,81) | 9999999 | ryland grace (5 rows)

Здесь мы видим, что PostgreSQL оставил физическую строку с name равным c703af5c89b1d0bc2e99f540f553f182 , которая была удалена, и добавил данные на новую физическую строку. Давайте запустим VACCUM .

hail_mary=# VACUUM FULL; hail_mary=# select ctid, * from users OFFSET 49995 limit 5; ctid | id | name ----------+---------+---------------------------------- (416,76) | 49996 | 2acfa04df8cc1e5b051866c32f9eb072 (416,77) | 49997 | 87aa98d07ec242cc4d8f685f0299257b (416,78) | 49998 | 12775d2a4498f0ec748a4beed90e5ad2 (416,79) | 50000 | 1017bfd4673955ffee4641ad3d481b1c (416,80) | 9999999 | ryland grace (5 rows)

VACCUM, как и ожидалось, дефрагментировал страницу, переместив кортежи.

Выглядит это следующим образом:

Мы также можем заглянуть внутрь страниц с помощью расширения pageinspect .

hail_mary=# create extension pageinspect; CREATE EXTENSION

Взглянем на последнюю страницу (416).

hail_mary=# select * from page_header(get_raw_page('users', 416)); lsn | checksum | flags | lower | upper | special | pagesize | version | prune_xid -----------+----------+-------+-------+-------+---------+----------+---------+----------- 0/428E4F0 | 0 | 0 | 344 | 3088 | 8192 | 8192 | 4 | 0 

Указатель lower равен 344 , а upper 3088 , то есть свободного места на этой странице 2744 байт.

Указатель special указывает на 8192 — это конец страницы. Можно сделать вывод, что данных в special space нет.

Посмотрим еще на предыдущую страницу — мы ожидаем, что на этой странице недостаточно свободного места.

hail_mary=# select * from page_header(get_raw_page('users', 415)); lsn | checksum | flags | lower | upper | special | pagesize | version | prune_xid -----------+----------+-------+-------+-------+---------+----------+---------+----------- 0/428CF58 | 0 | 0 | 504 | 512 | 8192 | 8192 | 4 | 0 (1 row)

Как и ожидалось, 8 байт недостаточно для хранения id + name .

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

hail_mary=# select * from heap_page_items(get_raw_page('users', 416)) offset 78 limit 2; lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data ----+--------+----------+--------+--------+--------+----------+----------+-------------+------------+--------+--------+-------+------------------------------------------------------------------------------ 79 | 3136 | 1 | 61 | 11780 | 0 | 0 | (416,79) | 2 | 2818 | 24 | | | \x50c30000433130313762666434363733393535666665653436343161643364343831623163 80 | 3088 | 1 | 41 | 11783 | 0 | 0 | (416,80) | 2 | 2818 | 24 | | | \x7f9698001b72796c616e64206772616365 

Ссылки

• Database Physical Storage
• Расширение PageInspect
• Row storage in PostgreSQL, heap file page layout
• Database Storage I (CMU Databases Systems / Fall 2019)

Материал подготовлен в преддверии старта онлайн-курса «Базы данных».