Установка PostgreSQL и pgAdmin на FreeBSD 11

Кому полезна эта инструкция

Системным администраторам, разворачивающим БД на FreeBSD 11.
Разработчикам, кто хочет управлять PostgreSQL через web-интерфейс pgAdmin.
Тем, кто собирает тестовую или небольшую продуктивную среду на одной машине.

Предварительные требования

Минимальная установка FreeBSD 11; рекомендуется 8 ГБ ОЗУ для продуктивных нагрузок.
Пользователь с правами sudo или root.
Доступ к консоли с правами установки пакетов.

Краткое описание архитектуры (одно предложение)

PostgreSQL — объектно-реляционная СУБД, которая хранит данные в кластере баз данных, управляемом одним экземпляром сервера.

Установка PostgreSQL

Сначала обновите информацию о репозиториях пакетов:

sudo pkg update

pkg — встроенный менеджер пакетов в FreeBSD. Далее установите сервер и клиент PostgreSQL 9.6:

sudo pkg install postgresql96-server postgresql96-client

Нажмите y для подтверждения установки. pkg установит пакеты и их зависимости.

Включите автоматический запуск PostgreSQL при загрузке:

sudo sysrc postgresql_enable=yes

Альтернативно можно добавить строку postgresql_enable=”YES” в конец /etc/rc.conf через любимый текстовый редактор.

Инициализируйте кластер баз данных:

sudo service postgresql initdb

initdb создаёт новый кластер PostgreSQL — набор баз данных под управлением одного сервера.

Запустите сервер:

sudo service postgresql start

После этого сервер PostgreSQL установлен и запущен.

Включение удалённых подключений и аутентификации по паролю

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

Если у вас нет nano, установите его:

sudo pkg install nano

Откройте основной конфиг:

sudo nano /var/db/postgres/data96/postgresql.conf

Найдите и измените параметры подключения. Раскомментируйте listen_addresses и установите ‘*’ для прослушивания на всех интерфейсах. При необходимости поменяйте порт.

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
#port = 5432                            # (change requires restart)
max_connections = 100                   # (change requires restart)
#superuser_reserved_connections = 3     # (change requires restart)
#unix_socket_directories = '/tmp'       # comma-separated list of directories

По умолчанию локальные подключения через unix-сокет настроены как trust. Для публично доступного сервера рекомендуем включить md5 (шифрованные пароли) и явно указать разрешённые адреса клиента.

Откройте pg_hba.conf:

sudo nano /var/db/postgres/data96/pg_hba.conf

Пример замены trust на md5 и разрешения всех IPv4 клиентов (0.0.0.0/0 — осторожно в проде):

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             0.0.0.0/0               md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

Важно: оставлять local all all trust разумно, если на сервере есть ненадёжные пользователи, лучше сменить и локальные socket-подключения на md5.

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

sudo service postgresql restart

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

По умолчанию создаются UNIX-пользователь postgres и группа postgres. Установите пароль для системного пользователя postgres:

sudo passwd postgres

Затем войдите под postgres:

su - postgres

Откройте psql:

psql

Пример приглашения:

psql (9.6.3)
Type "help" for help.

postgres=#

Выйти из psql можно командой \q.

Создать роль интерактивно:

createuser --interactive

Пример работы:

$ createuser --interactive
Enter name of role to add: new_user
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) y

Или одной командой с параметрами:

createuser -sdrP new_user

Параметры:

s: роль суперпользователя.
d: может создавать базы.
r: может создавать роли.
P: запросить пароль для роли.

Создать базу:

createdb new_db

В psql можно установить пароль для роли и выдать привилегии:

ALTER USER new_user WITH ENCRYPTED PASSWORD 'password';
GRANT ALL PRIVILEGES ON DATABASE new_db TO new_user;

Установка pgAdmin 4 в режиме сервера

pgAdmin — популярный GUI для управления PostgreSQL. В этой инструкции мы установим pgAdmin4 в серверном режиме на ту же машину.

Если вы под пользователем postgres, переключитесь на обычного sudo-пользователя:

su - sudo_user

Установите pip (менеджер пакетов Python):

sudo python -m ensurepip

Рекомендуется виртуальное окружение (virtualenv):

sudo pkg install py27-virtualenv
virtualenv pgadmin4
. pgadmin4/bin/activate

Установите криптографические зависимости:

sudo pip install cryptography pyopenssl ndg-httpsclient pyasn1

Установите SQLite для Python:

sudo pkg install py27-sqlite3

Загрузите wheel-пакет pgAdmin (в примере v1.5):

wget --no-check-certificate https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v1.5/pip/pgadmin4-1.5-py2.py3-none-any.whl

Установите его:

pip install pgadmin4-1.5-py2.py3-none-any.whl

Запустите приложение (путь к модулю):

python ./pgadmin4/lib/python2.7/site-packages/pgadmin4/pgAdmin4.py

При первом запуске вас попросят указать email и пароль для начальной учётной записи pgAdmin.

Пример вывода и указания порта:

NOTE: Configuring authentication for SERVER mode.

Enter the email address and password to use for the initial pgAdmin user account:

Email address: your_email
Password: your_new_password
Retype password:
Starting pgAdmin 4. Please navigate to http://localhost:5050 in your browser.

По умолчанию сервер слушает только localhost. Для доступа извне создайте локальную копию конфигурации и измените DEFAULT_SERVER:

sudo cp ./pgadmin4/lib/python2.7/site-packages/pgadmin4/config.py ./pgadmin4/lib/python2.7/site-packages/pgadmin4/config_local.py
sudo nano ./pgadmin4/lib/python2.7/site-packages/pgadmin4/config_local.py

Измените:

DEFAULT_SERVER = '0.0.0.0'
DEFAULT_SERVER_PORT = 5050

Перезапустите скрипт запуска. После этого интерфейс будет доступен по адресу http://your_server_IP:5050.

Безопасность и развёртывание в продакшн

Important: Не открывайте PostgreSQL или pgAdmin в интернет без защиты. Ниже — практические рекомендации.

Ограничьте доступ по IP через файрволл (pf, ipfw) и выдавайте доступ только доверенным подсетям.
Используйте md5 или лучше scram-sha-256 (если версия PostgreSQL поддерживает) для аутентификации.
Для pgAdmin включите HTTPS (обратный прокси nginx/letsencrypt) и не используйте 5050 без TLS.
Не храните пароли в открытом виде в конфигурационных файлах; пользуйтесь менеджерами секретов.
Регулярно обновляйте пакеты: pkg upgrade и pip upgrade в виртуальном окружении.

Пример минимальной записи pf для разрешения доступа к pgAdmin только с подсети 192.0.2.0/24:

pass in on em0 proto tcp from 192.0.2.0/24 to any port 5050 keep state
block in on em0 proto tcp from any to any port 5050

Резервное копирование и откат

Регулярно делайте бэкапы с помощью pg_dump/pg_dumpall или pg_basebackup.
Тестируйте восстановление на отдельной машине.

Пример: дамп одной базы в файл:

pg_dump -U new_user -h localhost -Fc new_db > new_db.dump

Восстановление из дампа:

pg_restore -U new_user -h localhost -d new_db -C new_db.dump

Откат версии pgAdmin или PostgreSQL: сохраните конфигурации и бэкапы, остановите сервис и замените пакеты на нужную версию из архива пакетов.

Резервные проверки и критерии приёмки

Сервер PostgreSQL запускается и отвечает на psql локально.
Удалённое подключение к порту 5432 доступно только с разрешённых IP.
Пользователь new_user может подключаться и выполнять DDL на новой базе.
pgAdmin доступен по HTTPS (или HTTP в тестовой сети) и авторизация работает.

Проверочные тесты (acceptance)

Подключение по psql: psql -h server_ip -U new_user -d new_db
Добавить и прочитать запись в тестовой таблице.
Выполнить бэкап и восстановление на тестовом экземпляре.

Роли и чек-листы

Администратор СУБД (DBA):

Проверил настройки listen_addresses и pg_hba.conf.
Настроил бэкапы и автоматизацию.
Тестировал восстановление.

Системный администратор:

Обновил систему и установил пакеты.
Настроил файрволл и TLS для pgAdmin.
Развернул мониторинг (например, pg_stat_statements, внешние SLM).

Разработчик:

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

Мини‑методология развёртывания (коротко)

Подготовьте ОС и сделайте snapshot/снимок или бэкап диска.
Установите PostgreSQL, инициализируйте кластер.
Настройте аутентификацию и файрволл.
Создайте роли и базы.
Установите pgAdmin в виртуальном окружении и настройте доступ.
Тестируйте подключение и бэкапы.
Переводите на мониторинг и регулярные обновления.

Миграция и совместимость

PostgreSQL 9.6 поддерживает md5; для scram-sha-256 нужен PostgreSQL 10+.
При миграции на более новую версию используйте pg_upgrade или полноценный дамп/восстановление.
pgAdmin старых версий может не поддерживать новые функции PostgreSQL; проверяйте совместимость версий на странице загрузки pgAdmin.

Когда этот подход не подходит

Если вам нужна высокая доступность и репликация — настройте репликацию (streaming replication) или используйте кластерный менеджер (Patroni, repmgr).
Если нужно enterprise-решение с поддержкой вендора — рассмотрите коммерческие варианты PostgreSQL.

Краткий словарь (1 строка)

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

Часто задаваемые вопросы

Q: Можно ли запускать pgAdmin и PostgreSQL на одном сервере в проде? A: Технически — да, но в продакшн окружении лучше разнести роли на отдельные серверы или контейнеры и включить TLS.

Q: Как безопасно открыть pgAdmin для внешнего доступа? A: Используйте обратный прокси (nginx) с TLS, ограничьте IP через файрволл и добавьте сильные пароли.

Q: Нужно ли использовать virtualenv для pgAdmin? A: Рекомендуется, чтобы изолировать Python-зависимости от системного Python и других приложений.

Заключение

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

Важно: перед открытием сервисов в сеть всегда протестируйте бэкапы и настройте ограничение доступа по IP и TLS.