PostgreSQL

В данном разделе приведены особенности настройки СУБД, необходимые для корректной работы приложения.

Официальная документация по установке PostgreSQL доступна по сайте postgresql.org.

Особенности установки PostgreSQL на Linux

Перед началом установки PostgreSQL удостоверьтесь, что в интерпретаторе установлена локаль ru_RU.UTF-8: 

# locale
LANG=ru_RU.UTF-8
LANGUAGE=
LC_CTYPE="ru_RU.UTF-8"
LC_NUMERIC="ru_RU.UTF-8"
LC_TIME="ru_RU.UTF-8"
LC_COLLATE="ru_RU.UTF-8"
LC_MONETARY="ru_RU.UTF-8"
LC_MESSAGES="ru_RU.UTF-8"
LC_PAPER="ru_RU.UTF-8"
LC_NAME="ru_RU.UTF-8"
LC_ADDRESS="ru_RU.UTF-8"
LC_TELEPHONE="ru_RU.UTF-8"
LC_MEASUREMENT="ru_RU.UTF-8"
LC_IDENTIFICATION="ru_RU.UTF-8"
LC_ALL=

Установить PostgreSQL можно из репозиториев (deb или rpm пакета в зависимости от типа дистрибутива Linux) или из исходных кодов.

Дистрибутивы на базе Debian, использующие формат пакетов DEB

Для установки актуальной версии рекомендуется использовать официальный репозиторий Postgres.

Команды для подключения репозитория Postgres:

os_version=$(lsb_release -sc)
sudo sh -c "echo deb http://apt.postgresql.org/pub/repos/apt/ ${os_version}-pgdg main > /etc/apt/sources.list.d/pgdg.list"
wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add -
sudo apt update

Команда установки Postgresql:

# apt-get install postgresql-<pg_version> postgresql-contrib-<pg_version>

   где <pg_version> — версия Postgresql.

В дистрибутивах, основанных на Debian, инициализация СУБД и включение службы выполняется автоматически, как часть процесса установки пакета.

Если инициализация СУБД была выполнена с неправильной локалью (не ru_RU.UTF-8), то следует остановить службу postgresql, удалить /переместить все содержимое из директории "/var/lib/postgresql/<pg_version>/main", настроить правильную локаль и выполнить повторную инициализацию.

Дистрибутивы на базе RedHat, использующие формат пакетов RPM

Для установки актуальной версии рекомендуется использовать официальный репозиторий Postgres. Для его подключения необходимо установить PGDG RPM файл (актуальный список таких файлов для разных архитектур, операционных систем и версий Postgresql можно найти на официальном сайте: https://yum.postgresql.org/repopackages.php).

Пример команды для подключения репозитория Centos 7 и Postgresql 11:

# yum install

https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Команда для установки PostgreSQL:

# yum install postgresql<pg_version>-server postgresql<version>-contrib

   где <pg_version> — версия Postgresql.

В RedHat дистрибутивах необходимо вручную:

  • выполнить инициализацию СУБД:

    # /usr/pgsql-<version>/bin/postgresql-<pg_version>-setup initdb

  • включить автоматический запуск службы СУБД:

    # systemctl enable postgresql-<pg_version>.service

       где <pg_version> — версия Postgresql.

Команда для запуска и остановки сервиса:

systemctl {start|stop} postgresql-<pg_version>

   где <pg_version> — версия Postgresql.

Если инициализация СУБД была выполнена с неправильной локалью (не ru_RU.UTF-8), то следует остановить службу postgresql-<pg_version>, удалить /переместить все содержимое из директории "/var/lib/pgsql/<version>/data", настроить правильную локаль и выполнить повторную инициализацию.

Конфигурирование PostgreSQL

Основные настройки PostgreSQL выполняются в файлах postgresql.conf и pg_hba.conf.

Файлы по умолчанию расположены в каталоге:

  • Linux:

    deb-based /etc/postgresql/<pg_version>/main

    rpm-based /var/lib/pgsql/<pg_version>/data

       где <pg_version> — версия Postgresql.

Порт и прослушиваемые системой сетевые интерфейсы указываются в файле postgresql.conf:

listen_addresses = 'localhost' # указать список прослушиваемых адресов через запятую или '*' для прослушивания всех сетевых интерфейсов port = 5432 - порт

Типы аутентификации и адреса, с которых разрешен доступ к базам данных настраиваются в файле pg_hba.conf.

Пример. Разрешена локальная аутентификация через сокеты, аутентификация через TCP с 127.0.0.1 и аутентификация через TCP компьютеров подсети 10.0.0.1/24 по паролям, зашифрованным md5:

local all all peer
host all all 127.0.0.1/32 md5
host all all 10.0.0.1/24 md5

Периодическое удаление неиспользуемых больших объектов

В СУБД PostgreSQL используются большие объекты (large objects). Запись в таблице базы данных ссылается на большой объект по OID. В таблице может быть несколько записей, относящихся к одному большому объекту. Удаление отдельной записи, содержащей ссылку на большой объект, не всегда приводит к удалению самого большого объекта, так как в базе данных могут оставаться другие записи, использующие данный объект. Неиспользуемые большие объекты накапливаются в системе, что приводит к росту объема базы данных.

Рекомендуется периодически выполнять удаление неиспользуемых больших объектов при помощи утилиты vacuumlo:

  • Linux: необходимо установить комплект утилит postgresql-contrib (версия должна совпадать с версией СУБД)

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

Создание пользователя и базы данных

Описание создания пользователя и базы данных: