Установка, настройка и подключение сервера баз данных PostgreSQL к операционной системе Linux.
Введение.
В семействе управления базами данных существует продукт PostgreSQL, это некоммерческий СУБД, рекомендованный для установки и эксплуатации Единым реестром Российских программ для ЭВМ и баз данных. В этой статье блога будут приведены и расписаны практические рекомендации по общим вопросам установки, запуску и эксплуатации экземпляра кластера сервера PostgreSQL в среде ALT Linux. В дальнейшем, при описании программ, использующих для своей работы БД PostgreSQL, будут делаться ссылки на эту статью, для базовой подготовки и первоначальной настройки сервера баз данных PqSQL.
Содержание.
Подготовка.
проверяем поддержку кодировки
# locale -a|grep ru_RU
ru_RU
ru_RU.cp1251
ru_RU.ibm866
ru_RU.iso88595
ru_RU.koi8r
ru_RU.utf8
# apt-get update
//смотрим какие есть версии PgSQL
# apt-cache show postgresql
# apt-get install postgresqlXX-server
//XX версия сервера pgsql
КАТАЛОГИ: кластер БД располагается в /var/lib/pgsql, пакеты postgresql, при установке копируются в каталог /usr/share/pgsql
Создание кластера.
Пользователь postgres, пользователь который будет регистрировать запись postgres в среде BASH должен быть членом группы whell, группа whell должна иметь доступ к командам sudo:
# visudo
WHEEL_USERS ALL=(ALL) ALL
:wq
листинг инициализации кластера postgresql
$ sudo su - postgres -s /bin/bash
-bash-3.2$ initdb -k
Файлы, относящиеся к этой СУБД, будут принадлежать пользователю "postgres". От его имени также будет запускаться процесс сервера. Кластер баз данных будет инициализирован с локалью "ru_RU.UTF-8". Кодировка БД по умолчанию, выбранная в соответствии с настройками: "UTF8". Выбрана конфигурация текстового поиска по умолчанию "russian". Контроль целостности страниц данных включён. исправление прав для существующего каталога /var/lib/pgsql/data... ок создание подкаталогов... ок выбирается значение max_connections... 100 выбирается значение shared_buffers... 128MB выбор реализации динамической разделяемой памяти... posix создание конфигурационных файлов... ок выполняется подготовительный скрипт... ок выполняется заключительная инициализация... ок сохранение данных на диске... ок ПРЕДУПРЕЖДЕНИЕ: используется проверка подлинности "trust" для локальных подключений. Другой метод можно выбрать, отредактировав pg_hba.conf или используя ключи -A, --auth-local или --auth-host при следующем выполнении initdb. Success. You can now start the database server using: service postgresql start or pg_ctl -D /var/lib/pgsql/data -l файл_журнала start
включаем службу юнита postgresql в автозагрузку
# systemctl enable postgresql
//для SysV
# chkconfig postgresql on
для управления кластером postgresql из сеанса пользователя postgres предназначена утилитой pg_ctl
-bash-3.2$ pg_ctl start | stop | restart| reload| status
-bash-3.2$ pg_ctl start
ожидание запуска сервера....2019-06-11 12:47:34.816 MSK [1337] СООБЩЕНИЕ: для приёма подключений по адресу IPv4 "0.0.0.0" открыт порт 5432
2019-06-11 12:47:34.816 MSK [1337] СООБЩЕНИЕ: для приёма подключений по адресу IPv6 "::" открыт порт 5432
2019-06-11 12:47:34.821 MSK [1337] СООБЩЕНИЕ: для приёма подключений открыт Unix-сокет "/tmp/.s.PGSQL.5432"
2019-06-11 12:47:34.846 MSK [1337] СООБЩЕНИЕ: передача вывода в протокол процессу сбора протоколов
2019-06-11 12:47:34.846 MSK [1337] ПОДСКАЗКА: В дальнейшем протоколы будут выводиться в каталог "log".
готово
сервер запущен
При запуске postgresql может возникнуть сообщение см. следующий листинг, это нормально, стартовый PID автозагрузки запустил кластер postgresq все команды pg_ctl , кроме start доступны
-bash-3.2$ pg_ctl start
pg_ctl: возможно, уже работает другой сервер; всё же пробуем запустить этот сервер
ожидание запуска сервера....ВАЖНО: файл блокировки "postmaster.pid" уже существует
ПОДСКАЗКА: Другой экземпляр postmaster (PID 1118) работает с каталогом данных "/var/lib/pgsql/data"?
прекращение ожидания
pg_ctl: не удалось запустить сервер
Включаем журнал работы сервера postgresql, по умолчанию отключен, с помощью клиента psql создаём SQL запрос и вносим изменения в файл postgresql.conf, теперь в структуре каталога /var/lib/pgsq/data создаётся дополнительная папка log, для файлов журналов.
-bash-3.2$ psql
postgres=# \! sed "s/.*logging_collector =.*/logging_collector = 'on'/" -i /var/lib/pgsql/data/postgresql.conf
перегружаем сервер для перечитывания новых значений postgresql
-bash-3.2$ pg_ctl restart
Настраиваем параметр listen_addresses — позволяет клиентам подключатся по протоколу TCP/IP, к базам данных pgsql, по умолчанию указанно значение, localhost - только для локальных подключений к серверу postgresql через unix-domain socket, вероятно это значение можно оставить без изменений, при условии, что сервер postgresql и приложения, например Vibe или Zabbix работают на том же сервере где и pgsql , мы же рассмотрим вариант где требуется доступ к БД postgresql из сетей разных диапазонов.
//параметр listen_addresses из коробки
-bash-3.2$ psql
postgres=# SHOW listen_addresses;
listen_addresses
------------------
localhost
(1 строка)
\q
меняем значение параметра listen_addresses с localhot на *, подключение к серверу postgresql со всех сетей по TCP/IP при желание параметр * можно сузить до нужного диапазона сети
-bash-3.2$ psql
postgres=# \! sed "s/.*listen_addresses =.*/listen_addresses = '*'/" -i /var/lib/pgsql/data/postgresql.conf
\q
перегружаем сервер для перечитывания новых значений postgresql
-bash-3.2$ pg_ctl restart
смотрим фиксацию значение listen_addresses
-bash-3.2$ psql
postgres=# SHOW listen_addresses;
listen_addresses
------------------
*
(1 row)
\q
Пользователь postgres.
При установке сервера postgresql создаётся единственный пользователь, который регистрируется в операционной системы и имеет обычный для Linux аккаунт - postgres. Пользователь обладает привилегиями суперпользователя для кластера postgresql и методом аутентификации из "коробки" trust - означает, что при подключение к серверу, для ввода команд пароль требоваться не будет.
//атрибуты пользователя postgres после развёртывания pgsql
$ id postgres
uid=46(postgres) gid=46(postgres) группы=46(postgres)
Многие сервисные приложения требуют при конфигурации своих служб устанавливать пароль к той базе данных для обслуживания которой необходим кластер pqsql. Сервер postgresql в своих настройках предлагает несколько вариантов аутентификации, мы остановимся на методе аутентификации md5, что бы этот метод работал необходимо внести изменения в файл /var/lib/pgsql/data/pg_hba.conf , но в начале создадим резервную копию файла pg_hba.conf
//создадим резервную копию файла
-bash-3.2$ cp -n /var/lib/pgsql/data/pg_hba.conf pg_hba.conf.orig
//возврат старой копии
-bash-3.2$ cp pg_hba.conf.orig /var/lib/pgsql/data/pg_hba.conf
смотрим запись файла /var/lib/pgsql/data/pg_hba.conf до редактирования:
-bash-3.2$ psql
postgres=# SHOW hba_file;
hba_file
---------------------------------
/var/lib/pgsql/data/pg_hba.conf
(1 строка)
postgres=# SELECT line_number, type, database, user_name, address, auth_method FROM pg_hba_file_rules;
line_number | type | database | user_name| address | auth_method
-------------+-------+---------------+-----------+-----------+-------------
84 | local | {all} | {all} | | trust
86 | host | {all} | {all} | 127.0.0.1 | trust
88 | host | {all} | {all} | ::1 | trust
91 | local | {replication} | {all} | | trust
92 | host | {replication} | {all} | 127.0.0.1 | trust
93 | host | {replication} | {all} | ::1 | trust
(6 строк)
\q
Предварительно, до всех действий с файлом pg_hba.conf, установим пароль для суперпользователя postgres, если пароль не задан, вместо него хранится NULL, пройти регистрацию по паролю этот пользователь не сможет.
Пароли пользователей БД pgsql находятся в системном каталоге /var/lib/pgsql/data/pg_authid, управлять паролями можно либо используя SQL-команды CREATE ROLE и ALTER ROLE или командой password входящей в сборник команд утилиты pqsl
-bash-3.2$ psql
postgres=# \password postgres
Введите новый пароль:
Повторите его:
postgres=# \q
вносим изменения в столбец database, заметим, что базы данных replication нас сейчас не интересуют, все базы находятся на одном сервере, реплик нет
-bash-3.2$ echo 'local all all md5' > /var/lib/pgsql/data/pg_hba.conf
-bash-3.2$ echo 'host all all all md5' >> /var/lib/pgsql/data/pg_hba.conf
перегружаем сервер для перечитывания новых значений postgresql
-bash-3.2$ psql
postgres=# SELECT pg_reload_conf();
pg_reload_conf
----------------
t
(1 строка)
\q
проверяем наши действия на ошибки
-bash-3.2$ psql
postgres=# SELECT line_number, error FROM pg_hba_file_rules WHERE error IS NOT NULL;
line_number | error
-------------+-------
(0 строк)
\q
смотрим какой метод аутентификации представлен после наших манипуляций
-bash-3.2$ psql
postgres=# SELECT line_number, type, database, user_name, address, auth_method FROM pg_hba_file_rules;
line_number | type | database | user_name | address | auth_method
-------------+-------+----------+-----------+---------+-------------
1 | local | {all} | {all} | | md5
2 | host | {all} | {all} | all | md5
(2 строки)
\q
смотрим алгоритм хеширования пароля выполнив SQL запрос параметра password_encryption
-bash-3.2$ psql
postgres=# SHOW password_encryption;
password_encryption
md5
(1 строка)
\q
Справочная информация.
Всё вышесказанное относилось непосредственно к системному пользователю и по совместительству суперпользователю сервера баз данных postgresql под именем postgres , на практике необходимо будет создавать других пользователей назначать для них роли и права, в качестве примеров приведём несколько команд показывающих как это происходит в жизни:
создадим роль и пароль для пользователя user, установим метод хеширования
-bash-3.2$ psql
CREATE ROLE user LOGIN PASSWORD 'secret';
CREATE ROLE
SET password_encryption='md5';
SET
\q
вносиим изменения в уже созданные роли и права
-bash-3.2$ psql
//изменение пароля роли
ALTER ROLE user WITH PASSWORD 'crumplet';
//удаление пароля роли
ALTER ROLE user WITH PASSWORD NULL;
//права на создание других ролей и новых баз данных
ALTER ROLE user CREATEROLE CREATEDB;
\q
Заключение.
Сервер базы данных PostgreSQL зарекомендовал себя, как надёжный и устойчивый при длительной эксплуатации СУБД. Возможности PostgreSQL позволяют конкурировать с такими известными и хорошо себя зарекомендовавшими базами, как Microsoft SQL и Oracle. Конечно, те кто вложился деньгами и знаниями в изучение и эксплуатацию Microsoft SQL и Oracle желания переходить или мигрировать на PostgreSQL не испытывают, слишком высоки риски, да и годами устоявшиеся система управления вполне всех устраивает. Но жизнь и время требует новых подходов и решений, создать с нуля БД и запустить в эксплуатацию это одно, а вот подготовить и выполнить план по миграции другое, начинать надо с малого и прежде всего это обучение и развёртывания стенда, что бы на практике набирать навыки и опыт работы с PostgreSQL. Если для организации стоит вопрос перехода на PostgreSQL, то прежде всего надо приступить к решению вопроса оборудования и коннектора для миграции, по оборудованию для PostgreSQL можно обратиться к официальной документации, а миграционный коннектор под конкретную прикладную программу надо искать их достаточно, платных и бесплатных условие одно — тестирование на стенде предприятия. Есть ещё одно решение по миграции заказать услугу по переходу на PostgreSQL и его сопровождение, но это прежде всего финансовый вопрос, придут специалисты проведут миграцию. а может и нет в любом случае это время, оборудование и деньги.
Полезные ссылки.
- Сайт PostgreSQL
- Форум PostgreSQL
- Документация PostgreSQL
Более подробную информацию о работе PostgreSQL смотри...
