Postgres где хранятся базы linux

Где PostgreSQL хранит базу данных?

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

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

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

/ Library / Application Support / Postgres [ver] / var.

В Windows7 все базы данных относятся к числу в файле с именем pg_database под C:\Program Files (x86)\PostgreSQL\8.2\data\global . Затем вы должны искать имя папки по этому номеру в C:\Program Files (x86)\PostgreSQL\8.2\data\base . Это содержимое базы данных.

Откройте pgAdmin и перейдите в «Свойства» для конкретной базы данных. Найдите OID, а затем откройте каталог

Должны быть ваши файлы DB.

Под моей установкой Linux он находится здесь: /var/lib/postgresql/8.x/

Вы можете изменить его с помощью initdb -D «c:/mydb/»

Как указано в «местоположении по умолчанию базы данных PostgreSQL в Linux, под Linux вы можете узнать, используя следующую команду:

Расположение определенных таблиц/индексов можно настроить с помощью TABLESPACE:

Postgres хранит данные в файлах в своем каталоге данных. Выполните следующие шаги, чтобы перейти к базе данных и ее файлам:

База данных, соответствующая файлу таблицы postgresql, является каталогом. Местоположение всего каталога данных можно получить, запустив SHOW data_directory . в операционной системе UNIX (например: Mac) /Library/PostgreSQL/9.4/data Перейдите в базовую папку в каталоге данных, где есть все папки базы данных: /Library/PostgreSQL/9.4/data/base

Найдите имя папки базы данных, запустив (дает целое число. Это имя папки базы данных):

Найдите имя файла таблицы, запустив (дает целое число. Это имя файла):

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

How to change PostgreSQL's data directory on Linux

There comes a time when you have to restore a relatively large database locally. Most likely, you’ve partitioned your disk, and your root partition got the thick end of it, 50 GB if you were generous. Let’s assume that that’s not nearly enough for the database you’re about to restore. At the same time, your /home partition got the rest of the disk space you had available.

You can always resize these two partitions, but then you would have back up your /home directory, unmount it or find a Live USB and tamper with them. If you don’t have the time, or the desire, or even a backup disc, you can always change the location where postgresql stores its data.

The following instructions are a love letter to all those lost souls who find themselves in this situation and forget to check the status of SELinux, as well as to my future self who’ll most likely have to do it again. Considering this is mostly a dump of my bash history that has everything and anything, I hope this exact procedure works for you. If not, feel free to contact me and we’ll update it together.

Procedure

A fresh install is the easiest to change, but let’s assume you have some databases locally you don’t want to lose, just move them and restore the large database next to them. The steps are more or less the same anyway.

data_directory and config_file

Before you start anything, locate the postgresql ’s configuration file and its data directory:

Stop the systemd service

Stop the postgresql systemd service:

New location

Create a directory where you have enough disk space available (in this case, it’s the /home directory), grant the postgres user ownership and permissions over it and copy the original data directory to the new location (the key is to preserve the same ownership and permissions structure):

postgresql configuration

Open the postgresql.conf file in the new location and update the data_directory variable, setting it to the new location where your data was moved:

systemd configuration

Do the same thing with the postgresql.service ’s systemd configuration file:

Once you’re done editing the systemd configuration, reload it and start the service:

SELinux

If you’re receiving some vague Permission denied errors, check whether or not you have SELinux enabled:

If the result is 1 , then SELinux is in enforcing mode. To temporarily set it to permissive mode ( 0 ), run:

You can try starting the postgresql.service again. If the process has started successfully, stop it, and tell SELinux to apply the same context to the new location. Then you can return SELinux to enforcing mode

Then you should be able to start the postgresql.service without any errors

Confirmation

To confirm the new location and configuration is used, rerun the first step:

PostgreSQL (Русский)

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

Установка

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Обновление PostgreSQL

alt=»Tango-view-fullscreen.png» width=»48″ height=»48″ />This article or section needs expansion. alt=»Tango-view-fullscreen.png» width=»48″ height=»48″ />

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

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

Чтобы случайно не обновиться до несовместимой версии, рекомендуется запретить обновления пакетов 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 , которые использовались при создании базы. Команды для просмотра текущих настроек кластера описаны в разделе #Начальная настройка. службу postgresql.service . (Проверьте статус юнита, чтобы убедиться, что PostgreSQL завершился корректно, иначе pg_upgrade не сможет отработать корректно.) пакеты postgresql , postgresql-libs и postgresql-old-upgrade .
2. Переименуйте каталог со старым кластером и создайте каталог для нового кластера и временный каталог:
3. Инициализируйте новый кластер командой initdb с теми же аргументами, которые использовались для старого кластера:
4. Обновите кластер, выполнив эту команду (замените PG_VERSION на номер старой версии, например 13 ):

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

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

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

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

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

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

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

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:

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

Перемещение каталога данных PostgreSQL в Ubuntu 16.04

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

Требования

• Сервер Ubuntu 16.04 (инструкции по настройке сервера можно найти здесь).
• Пользователь с доступом к sudo.
• Сервер PostgreSQL (чтобы установить PostgreSQL, следуйте этому руководству).

В данном мануале показано, как переместить данные в блочное хранилище, смонтированное в mnt/volume-nyc1-01. Это поможет вам переместить каталог данных в новое место независимо от того, какое хранилище вы используете.

1: Перемещение каталога данных PostgreSQL

Чтобы подготовиться к перемещению каталога данных PostgreSQL, нужно уточнить его текущее местонахождение. Для этого откройте интерактивную сессию PostgreSQL; флаг -u postgres откроет сессию пользователя postgres.

sudo -u postgres psql

Запросите каталог данных:

SHOW data_directory;
data_directory
——————————
/var/lib/postgresql/9.5/main
(1 row)

Данный вывод подтверждает, что PostgreSQL использует каталог данных по умолчанию, /var/lib/postgresql/9.5/main. Чтобы закрыть сессию, введите \q.

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

sudo systemctl stop postgresql

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

sudo systemctl status postgresql

Последняя строка вывода должна сообщать:

. . .
Jul 22 16:22:44 ubuntu-512mb-nyc1-01 systemd[1]: Stopped PostgreSQL RDBMS.

Теперь, когда сервер остановлен, скопируйте текущий каталог данных в новое место с помощью rsync. Флаг –a сохраняет привилегии и другие свойства каталога. Флаг –v предоставляет подробный вывод.

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

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

Каталог, указывающий версию (9.5), не является строго необходимым, но он не помешает, если вы хотите следовать соглашениям проекта и особенно если у вас есть необходимость в будущем использовать несколько версий PostgreSQL.

sudo rsync -av /var/lib/postgresql /mnt/volume-nyc1-01

После выполнения rsync переименуйте текущую папку, добавив расширение .bak. Оставьте расширение до тех пор, пока не убедитесь, что данные перемещены успешно. Это поможет не спутать файлы.

sudo mv /var/lib/postgresql/9.5/main /var/lib/postgresql/9.5/main.bak

2: Настройка нового каталога данных

PostgreSQL предлагает несколько способов отмены значений конфигурации. По умолчанию data_directory находится в /var/lib/postgresql/9.5/main согласно файлу /etc/postgresql/9.5/main/postgresql.conf. Откройте этот файл:

sudo nano /etc/postgresql/9.5/main/postgresql.conf

Найдите строку data_directory и укажите путь к новому каталогу данных.

3: Перезапуск PostgreSQL

Теперь нужно запустить сервер PostgreSQL.

sudo systemctl start postgresql
sudo systemctl status postgresql

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

sudo -u postgres psql

Запросите каталог данных:

postgres=# SHOW data_directory;
data_directory
——————————————
/mnt/volume-nyc1-01/postgresql/9.5/main
(1 row)

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

sudo rm -Rf /var/lib/postgresql.bak

Перезапустите PostgreSQL ещё раз, чтобы обновить все настройки:

sudo systemctl restart postgresql
sudo systemctl status postgresql

Заключение

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