Первоначальная настройка сервера PostgreSQL

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

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

Первоначальная настройка

Приступим к первоначальной настройке сервера PostgreSQL.

Настройка возможности внешних подключений

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

Сначала включим прослушивание подключения на всех доступных IP-адресах сервера. Для Ubuntu Server 22.04 конфигурационный файл следующий:

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

Конфигурационный файл для Rocky Linux 9.3:

sudo nano /var/lib/pgsql/data/postgresql.conf

В конфигурационном файле postgresql.conf необходимо скорректировать параметр listen_addresses:

listen_addresses = '*'

Параметр со звёздочкой указывает на то, что сервер должен прослушивать подключение по всем адресам. При необходимости можно указать какой-то конкретный IP-адрес.

Теперь скорректируем конфигурационный файл pg_hba.conf с параметрами аутентификации. Для Ubuntu Server 22.04 конфигурационный файл следующий:

sudo nano /etc/postgresql/14/main/pg_hba.conf

Конфигурационный файл для Rocky Linux 9.3:

sudo nano /var/lib/pgsql/data/pg_hba.conf

Добавим в конфигурационный файл строчку, которая разрешит подключаться всем пользователя ко всем базам данных по всем доступным IP-адресам сервера. Но только для тем пользователям, у которых установлен пароль с надежным методом шифрования (scram-sha-256):

host all all 0.0.0.0/0 scram-sha-256

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

sudo systemctl restart postgresql

Убедимся, что сервер postgres прослушивает подключения по всем доступным IP-адресам:

sudo ss -tulpn

По умолчанию на Rocky Linux включен брандмауэр. Соответственно, разрешим подключение к порту TCP/5432:

sudo firewall-cmd --add-port 5432/tcp --permanent
sudo firewall-cmd --reload

Создание отдельного нового администратора сервера PostgreSQL

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

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

sudo -u postgres psql

Создадим новую роль с разрешениями администратора сервера PostgreSQL:

CREATE ROLE roman WITH LOGIN SUPERUSER;

Теперь необходимо установить пароль для нового администратора:

SET password_encryption = 'scram-sha-256';
\password roman

Создание нового администратора и установка его пароля завершены.

Перенос директории с данными

Очень частая задача – это перенос директории с данными PostgreSQL на отдельный физический диск. Я покажу пример этой процедуры для Ubuntu Server 22.04 и Rocky Linux 9.3.

Я буду переносить директорию с данными в следующую директорию – /data/pg_data

Процедура переноса директории с данными следующая:

1. Сначала необходимо определить – где расположена текущая директория.

sudo -u postgres psql -c 'SHOW data_directory'

Расположение на Ubuntu Server 22.04:

Расположение на Rocky Linux 9.3:

2. Останавливаем службу сервера:

sudo systemctl stop postgresql

3. Копируем текущую директорию с данными в новое расположение. Пример переноса содержимого директории с данными для Ubuntu Server 22.04:

sudo rsync -av /var/lib/postgresql/14/main/ /data/pg_data

Пример переноса содержимого директории с данными для Rocky Linux 9.3:

sudo dnf -y install rsync
sudo rsync -av /var/lib/pgsql/data/ /data/pg_data

4. Переименуем старую директорию с данными. Для Ubuntu:

sudo mv /var/lib/postgresql/14/main/ /var/lib/postgresql/14/main.bkp

Для Rocky:

sudo mv /var/lib/pgsql/data /var/lib/pgsql/data.bkp

5. Теперь необходимо указать серверу, где расположена новая директория с данными. Для Ubuntu и Rocky это выполняется различным образом.

Для этого в конфигурационном файле postgresql.conf Ubuntu Server 22.04 есть соответствующий параметр – data_directory. Скорректируем параметр, которые отвечает за указание расположения директории с данными :

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

Укажем новое расположение директории с данными:

data_directory = '/data/pg_data'

Для Rocky Linux 9.3 необходимо скорректировать unit файл сервиса и указать новое значение для переменной PG_DATA:

sudo nano /lib/systemd/system/postgresql.service

Environment=PGDATA=/data/pg_data

Также для Rocky Linux выполним актуализацию конфигурационных файлов сервисов:

sudo systemctl daemon-reload

6. Скорректируем разрешения для директории с данными:

sudo chown -R postgres:postgres /data/pg_data
sudo chmod -R 750 /data/pg_data

Дополнительно для Rocky Linux необходимо скорректировать разрешения SELinux:

sudo dnf install policycoreutils-python-utils
sudo semanage fcontext --add --equal /var/lib/pgsql/data.bkp /data/pg_data
sudo restorecon -rv /data/pg_data

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

sudo systemctl start postgresql

8. Проверяем статус сервиса:

sudo systemctl status postgresql

Сервис активен и запущен.

9. Проверяем подключение к серверу:

sudo -u postgres psql

Подключение выполнено успешно.

10. В заключении, например, можем попробовать вывести список баз данных:

\l

Перенос директории с данными выполнен уcпешно.

Перенос журнала WAL на отдельный диск

Первоначальная настройка сервера PostgreSQL также может включать в себя шаг по переносу транзакционного журнала сервера на отдельный физический диск. В терминах PostgreSQL транзакционные журналы называются WAL – Write-Ahead Logging. Перед тем, как внести изменения для объетов в оперативной памяти PostgreSQL предварительно фиксирует эти изменения на диске, чтобы можно было выполнить восстановление в случае аварий. Именно журналы WAL мы с вами и будет переносить на отдельный диск.

Проще всего выполнить эту процедуру через символические ссылки. Если кто-то знает более правильный или оптимальный способ – напишите в комментариях.

Процедура переноса журналов WAL на отдельный физический диски следующая:

1. Создадим отдельную директорию на новом диске:

sudo mkdir -p /data/pg_log

2. Теперь остановим службу сервиса PostgreSQL:

sudo systemctl stop postgresql

3. Переместим текущую директорию с WAL в новое расположение. В моем случае директория с данными PostreSQL находится в следующем расположении – /data/pg_data. В директории с данными есть директория pg_wal. Вот её и нужно перенести:

sudo mv /data/pg_data/pg_wal /data/pg_log/pg_wal

4. Скорректируем разрешения на директории для WAL.

Для Ubuntu Server 22.04:

sudo chown -R postgres:postgres /data/pg_log
sudo chmod -R 750 /data/pg_log

Для Rocky Linux 9.3:

sudo chown -R postgres:postgres /data/pg_log
sudo chmod -R 750 /data/pg_log
sudo semanage fcontext --add --equal /data/pg_data /data/pg_log
sudo restorecon -rv /data/pg_log

5. Создадим символическую ссылку для pg_wal в директории с данными:

sudo ln -s /data/pg_log/pg_wal /data/pg_data/pg_wal
sudo chown postgres:postgres /data/pg_data/pg_wal
sudo chmod 750 /data/pg_data/pg_wal

Для Rocky Linux дополнительно необходимо указать конект selinux:

sudo chcon -Rt postgresql_db_t /data/pg_data/
sudo chcon -Rt postgresql_db_t /data/pg_log/

6. Запускаем сервис:

sudo systemctl start postgresql

7. Проверяем статус сервиса:

sudo systemctl status postgresql

Сервис активен и запущен.

8. Проверяем подключение к серверу:

sudo -u postgres psql

Подключение выполнено успешно.

9. В заключении, например, можем попробовать вывести список баз данных:

\l

Перенос директории WAL выполнен уcпешно.

Подключение к серверу через psql

Локальные подключения через psql работают успешно. Теперь попробуем выполнить внешнее подключение. Я попробую подключиться к серверу PG02 с сервера PG01 под отдельной УЗ администратора. Подключение будет осуществляться к БД postgres:

psql -U roman -h 10.10.10.33 -W -d postgres

root@pg01:/home/roman# psql -U roman -h 10.10.10.33 -W -d postgres
Password: 
psql (14.10 (Ubuntu 14.10-0ubuntu0.22.04.1), server 13.13)
Type "help" for help.

postgres=#

Теперь проверю аналогичное подключение. Только с сервера PG02 к серверу PG01:

psql -U roman -h 10.10.10.34 -W -d postgres

[root@pg02 roman]# psql -U roman -h 10.10.10.34 -W -d postgres
Password: 
psql (13.13, server 14.10 (Ubuntu 14.10-0ubuntu0.22.04.1))
WARNING: psql major version 13, server major version 14.
         Some psql features might not work.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=#

Подключение также завершается успешно. Это значит, что настройку внешних подключений мы с вами выполнили успешно.

Подключение к серверу через pgAdmin

Последнее о чем я расскажу в этой публикации – это подключение к серверу через pgAdmin. pgAdmin является утилитой, которая предоставляет GUI для управления серверов PostgreSQL.

Настройка подключения через pgAdmin выполняется идентично для сервера Ubuntu Server 22.04 и Rocky Linux 9.3.

Настроим подключение через pgAdmin:

1. В окне pgAdmin в дереве слева в контекстном меню объекта “Servers” выберите пункты “Register > Server…”.

2. В появившемся диалоговом окне регистрации сервера на вкладке “General” заполните поле “Name”. В этом поле указывается имя, под которым сервер будет отображаться в консоле pgAdmin.

3. На вкладке “Connection” окна регистрации сервера укажите информацию для подключения к серверу PostgreSQL – имя хоста или адрес, порт, имя базы данных, имя пользователя и пароль.

4. После завершения указания данных для подключения нажмите кнопку “Save”.

В случае успешного подключения к серверу вы увидите сводные экран с отображение наиболее важной статистики и статусов работы сервера PostgreSQL.

Настройка подключения через pgAdmin завершена.