Установка PostgreSQL на выделенном сервере

В этом разделе описан процесс установки СУБД PostgreSQL на выделенном сервере.

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

Процесс распределенной установки PostgreSQL описан в разделе Установка PostgreSQL в выделенном кластере.

Требования к PostgreSQL

Для работы системы требуется реляционная СУБД:

  • рекомендуется PostgreSQL версии 15 или выше;

  • допустимо использование совместимых аналогов: Postgres Pro или Jatoba J4.

Установка PostgreSQL

Загрузите и установите PostgreSQL согласно инструкции с официального сайта.

Настройка PostgreSQL на выделенном сервере

Настройка конфигурационного файла postgresql.conf

Для корректной работы системы в конфигурационный файл postgresql.conf следует добавить следующие параметры:

  • сетевой интерфейс и порт, прослушиваемые сервером PostgreSQL;

  • параметры обработки строк.

Расположение файла зависит от используемой операционной системы:

  • В дистрибутивах Linux, основанных на Debian, файл postgresql.conf находится в директории /etc/postgresql/<version>/main/.

  • В дистрибутивах Linux, основанных на Red Hat, файл postgresql.conf находится в директории /var/lib/pgsql/<version>/data/.

Если вы используете иную операционную систему, расположение файла postgresql.conf может быть другим.

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

  1. Откройте файл postgresql.conf для редактирования.

  2. Добавьте в файл следующие строки:

    listen_addresses = '<postgresql_ip>'
port = <port>
standard_conforming_strings = 'on'
escape_string_warning = 'on'
backslash_quote = 'safe_encoding'

    Здесь:

    • <postgresql_ip> — IP-адрес сервера PostgreSQL, который система будет использовать для подключения к базе данных.

    • <port> — порт, который будет прослушивать сервер PostgreSQL в ожидании подключений. По умолчанию — 5432.

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

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

    • мегабайты — MB;

    • гигабайты — GB.
    Параметр Описание Значение по умолчанию Рекомендуемое значение

    shared_buffers

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

    128MB

    20—​30% от объема ОЗУ на сервере PostgreSQL.

    Выделение 40% ОЗУ и более неоправданно, поскольку PostgreSQL также использует файловый кэш ОС.

    work_mem

    Объем оперативной памяти, выделяемый для одной операции сортировки или хэширования.

    Указанный объем памяти выделяется для каждой операции, а не для соединения в целом.

    4MB

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

    work_mem = (<ram_amount> - <shared_buffers>) / (<max_connections> * <avg_sort_hash>)

    Здесь:

    • <ram_amount> — объем ОЗУ на сервере PostgreSQL.

    • <shared_buffers> — значение параметра shared_buffers.

    • <max_connections> — значение параметра max_connections (предельное количество одновременных подключений к серверу PostgreSQL).

    • <avg_sort_hash> — среднее количество одновременных операций сортировки и хэширования.

    Например:

    work_mem = (16 GB - 4 GB) / (3 * 100) = 40.96MB

    В этом случае 40.96 МБ — максимальное значение параметра.

    maintenance_work_mem

    Объем оперативной памяти, выделяемый для одной операции обслуживания:

    • VACUUM;

    • CREATE INDEX;

    • REINDEX;

    • CLUSTER;

    • ALTER TABLE ADD FOREIGN KEY.

    Также, если параметр autovacuum_work_mem имеет значение -1, запросы autovacuum будут использовать значение maintenance_work_mem.

    64MB

    5% от объема ОЗУ на сервере PostgreSQL.

    Пример настройки лимитов при общем объеме ОЗУ, выделенном для сервера PostgreSQL, в 12 ГБ:

    shared_buffers = '3GB'
work_mem = '30MB'
maintenance_work_mem = '600MB'

  4. Сохраните и закройте файл postgresql.conf.

Настройка сетевого доступа кластера к PostgreSQL

Разрешения сетевого доступа клиентов к серверу PostgreSQL задаются в конфигурационном файле pg_hba.conf. Этот файл расположен в той же директории, что и файл postgresql.conf.

Чтобы настроить разрешения сетевого доступа:

  1. Откройте файл pg_hba.conf для редактирования.

  2. Разрешите подключение worker-узлов кластера Kubernetes, добавив их адреса в виде строк:

    host all all <worker_node_ip_1>/32 scram-sha-256
host all all <worker_node_ip_2>/32 scram-sha-256
host all all <worker-node-ip_N>/32 scram-sha-256

    Здесь:

    • <worker_node_ip_K> — IP-адреса worker-узла с номером K кластера системы.

    В зависимости от настроек PostgreSQL может использоваться алгоритм хэширования md5 вместо приведенного в примере scram-sha-256.

  3. Сохраните и закройте файл pg_hba.conf.

Проверка локали, используемой PostgreSQL

Чтобы система работала корректно, базы данных PostgreSQL должны использовать локаль en_US.UTF-8. Чтобы проверить используемую локаль и при необходимости изменить ее, выполните следующие действия:

  1. Выполните следующий запрос к PostgreSQL:

    SELECT datname, datcollate, datctype
FROM pg_database;
    Пример фрагмента вывода запроса
    datname     | datcollate  |  datctype
------------+-------------+------------
 postgres   | en_US.UTF-8 | en_US.UTF-8
 template1  | en_US.UTF-8 | en_US.UTF-8
 template0  | en_US.UTF-8 | en_US.UTF-8
 trigger    | en_US.UTF-8 | en_US.UTF-8
 auth       | en_US.UTF-8 | en_US.UTF-8

  2. Если для баз данных template0 и/или template1 локаль отличается от en_US.UTF-8, выполните следующие действия:

    1. Остановите сервис postgresql и очистите созданные им данные:

      systemctl disable --now postgresql
rm -rf <postgresql_data_path>

      Здесь:

      • <postgresql_data_path> — путь к каталогу данных PostgreSQL, например, /var/lib/postgresql/15/main.

    2. Выполните повторную инициализацию PostgreSQL с помощью следующей команды:

      initdb -D <postgresql_data_path> --locale=en_US.UTF-8

      Здесь:

      • <postgresql_data_path> — путь к каталогу данных PostgreSQL, например, /var/lib/postgresql/15/main.

Применение параметров

Перезапустите PostgreSQL, выполнив следующую команду:

service postgresql restart

или

systemctl restart postgresql

Добавление пользователя PostgreSQL

При установке PostgreSQL создает суперпользователя postgres. По умолчанию пароль для его учетной записи не задан. Рекомендуется создавать для системы отдельного пользователя с надежным паролем. Пользователь должен обладать привилегией CREATEDB, которая позволяет создавать новые базы данных и становиться их владельцем.

Чтобы добавить пользователя:

  1. Переключитесь на пользователя postgres в командной строке:

    su postgres

    или

    sudo -i -u postgres

  2. Добавьте пользователя, выполнив следующую команду:

    createuser --createdb --pwprompt system-user

    или

    psql -c "CREATE USER \"system-user\" WITH CREATEDB PASSWORD 'yourpassword';"

    где system-user — пример имени нового пользователя (может быть другим), yourpassword — пример пароля.

  3. Пользователь добавлен. Завершите сеанс пользователя postgres командой exit.

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

Создание TLS-сертификатов для PostgreSQL

Если необходимо включить шифрование трафика между системой и вынесенным экземпляром PostgreSQL (режим SSL/TLS), сгенерируйте следующие файлы:

  • Сертификат доверенного центра сертификации с расширением .crt.

  • При необходимости:

    • Клиентский TLS-сертификат для PostgreSQL, выпущенный доверенным центром сертификации, с расширением .crt.

    • Приватный ключ клиентского сертификата PostgreSQL с расширением .key.

Вместо клиентского сертификата и ключа можно использовать автоматически сгенерированный самоподписанный сертификат и ключ. Их можно сгенерировать на этапах Platform: PostgreSQL setup и Streams: PostgreSQL setup установки системы.

Установка расширения uuid-ossp

Для работы PostgreSQL с данными типа UUID требуется установить расширение uuid-ossp. Для этого выполните следующие действия:

  1. Получите список доступных расширений, выполнив следующую команду:

    psql -c "SELECT * FROM pg_available_extensions;"
    Пример вывода списка доступных расширений 
         name           | default_version | installed_version |                     comment
--------------------+-----------------+-------------------+-------------------------------------------------------------------
 plpgsql            | 1.0             | 1.0               | PL/pgSQL procedural language
 pg_stat_statements | 1.9             |                   | track execution statistics of all SQL statements executed
 hstore             | 1.8             |                   | data type for storing sets of key/value pairs
 uuid-ossp          | 1.1             |                   | generate universally unique identifiers (UUIDs)
 pg_trgm            | 1.6             |                   | text similarity measurement and index searching based on trigrams

    Если в столбце name присутствует значение uuid-ossp и в строке uuid-ossp в столбце installed_version есть значение, дополнительных действий не требуется. Иначе переходите к следующим шагам.

  2. Если в столбце name значение uuid-ossp отсутствует, значит, расширение не входит в установленный вами дистрибутив PostgreSQL. Его можно скачать вместе с пакетом postgresql-contrib.

    Пример команды для скачивания postgresql-contrib для PostgreSQL 15 с помощью пакетного менеджера apt:

    sudo apt install postgresql-contrib-15

  3. Установите расширение uuid-ossp, выполнив следующую команду:

    psql -c "CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\";"

Была ли полезна эта страница?

+7 (499) 755 55 70 sales@rvision.ru

О компании Пользовательское соглашение Политика cookies

Обратная связь