Установка и базовая настройка PostgreSQL на Ubuntu

Реляционные системы управления базами данных (RDBMS) — ключевая составляющая многих сайтов и приложений. Они дают структуру для хранения, организации и доступа к данным.

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

Коротко о PostgreSQL

PostgreSQL — это свободная объектно-реляционная система управления базами данных с поддержкой SQL. Она обеспечивает высокий уровень гибкости и надёжности для хранилищ данных. Ключевые свойства:

• ACID-согласованность и транзакции.
• Поддержка расширений и хранимых процедур на нескольких языках.
• Горизонтальная и вертикальная масштабируемость.
• Богатая модель ролей и прав доступа.

Определение: PostgreSQL — СУБД с открытым исходным кодом, ориентированная на расширяемость и корректность данных.

Что нужно знать перед установкой

Перед установкой проверьте:

• Версию Ubuntu (линия LTS предпочтительна). Запрос: lsb_release -cs.
• Доступ к sudo или root.
• Наличие свободного места на диске и резервного копирования данных, если ставите поверх существующей установки.

Важно: На продуктивных серверах рекомендуется устанавливать PostgreSQL из официального репозитория PostgreSQL для доступа к актуальным версиям и исправлениям безопасности.

Шаг 1: Установка PostgreSQL на Ubuntu

Некоторые пакеты PostgreSQL содержатся в репозитории Ubuntu по умолчанию. Чтобы установить PostgreSQL через командную строку, выполните:

sudo apt update
sudo apt install postgresql postgresql-contrib

Если вы хотите получить самую свежую версию, добавьте официальный репозиторий PostgreSQL (см. раздел «Альтернативные подходы» ниже).

Проверка установки

Найдите расположение конфигурационных файлов (пример):

ls /etc/postgresql/12/main/

Число 12 — версия пакета. У вас может быть другое значение в зависимости от того, какой пакет был установлен.

Проверка статуса службы

После установки проверьте статус службы PostgreSQL:

service postgresql status

Если в выводе указано active (running), служба запущена. Если inactive или failed, запустите её:

service postgresql start

Другие полезные команды управления службой:

service postgresql stop
service postgresql restart
service postgresql reload
service postgresql force-reload

Шаг 2: Вход под суперпользователем PostgreSQL

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

Вход в psql как системный пользователь

Откройте интерактивную оболочку psql от имени системного пользователя postgres:

sudo -u postgres psql

Чтобы задать пароль для роли postgres, выполните в psql:

ALTER USER postgres PASSWORD 'newpassword';

Замените newpassword на надёжный пароль. Введите \q или exit, чтобы выйти из psql.

Для подключения к серверу PostgreSQL с указанием пользователя используйте:

psql -U postgres -h localhost

Введите пароль, который вы задали.

Шаг 3: Подключение к серверу PostgreSQL

Чтобы переключиться на системного пользователя postgres и получить shell этого пользователя:

sudo su postgres

Промпт изменится, например:

postgres@ubuntu: /home/you$ 

Это означает, что вы авторизованы как системный пользователь PostgreSQL.

Управление пользователями PostgreSQL

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

Создание новой роли

В psql выполните:

CREATE USER user1 WITH PASSWORD 'test123';

Замените user1 и test123 на имя и пароль по вашему выбору.

Просмотреть список ролей:

\du

В выводе роль только что созданного пользователя пока не имеет привилегий.

Назначение прав суперпользователя

Чтобы сделать роль суперпользователем:

ALTER USER user1 WITH SUPERUSER;

После этого \du покажет соответствующие флаги роли.

Удаление роли

Чтобы удалить роль:

DROP USER user1;

Проверьте изменения командой \du.

Управление базами данных PostgreSQL

PostgreSQL предоставляет простые команды для создания и удаления баз.

Создание и удаление базы данных

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

CREATE DATABASE db1;

Список баз данных:

\l

Удалить базу:

DROP DATABASE db1;

Назначение доступа к базе

Чтобы дать пользователю полный доступ к базе:

GRANT ALL PRIVILEGES ON DATABASE db1 TO user1;

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

Вы можете прочитать системную справку по клиенту psql:

man psql

Внутри psql используйте \? для списка команд клиента и \h для справки по SQL-командам.

Рекомендация: установить pgAdmin

pgAdmin — популярный графический инструмент для управления PostgreSQL. Он упрощает создание пользователей, баз, бэкапов и мониторинг.

Добавьте репозиторий и ключ, затем установите pgAdmin:

curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add -
sudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'

Установка десктоп-версии:

sudo apt install pgadmin4-desktop

Установка веб-версии:

sudo apt install pgadmin4-web

Конфигурирование веб-режима:

sudo /usr/pgadmin4/bin/setup-web.sh

Следуйте подсказкам на экране. Настройка выполняется однократно.

Быстрый чек-лист после установки

• Проверьте статус службы: service postgresql status.
• Установите пароль для роли postgres.
• Отключите доступ по паролю к порту 5432 на внешних интерфейсах при отсутствии необходимости.
• Настройте файлы /etc/postgresql//main/pg_hba.conf и postgresql.conf.
• Создайте роль и базу для приложения.
• Настройте регулярные бэкапы (pg_dump / pg_basebackup).

Важно: При изменении конфигурации перезапустите службу: service postgresql restart.

Команды — шпаргалка

# Управление службой
service postgresql start|stop|restart|status

# Подключение
sudo -u postgres psql
psql -U postgres -h localhost -d dbname

# Роли и базы
CREATE USER name WITH PASSWORD 'pwd';
ALTER USER name WITH SUPERUSER;
DROP USER name;
CREATE DATABASE dbname;
DROP DATABASE dbname;
GRANT ALL PRIVILEGES ON DATABASE dbname TO name;

# В psql: вспомогательные
\du   -- список ролей
\l    -- список баз
\c dbname  -- подключиться к базе
\q   -- выход

# Бэкап и восстановление
pg_dump -U user -F c -b -v -f dbname.dump dbname
pg_restore -U user -d dbname dbname.dump

Альтернативные подходы к установке

1. Официальный репозиторий PostgreSQL

   • Рекомендовано для получения новых версий и патчей безопасности.
   • Добавьте apt.postgresql.org, затем установите нужную версию.

2. Контейнеры (Docker)

   • Подходит для тестовых окружений и изоляции зависимостей.
   • Пример запуска:

docker run --name postgres -e POSTGRES_PASSWORD=mysecret -p 5432:5432 -d postgres:15

3. Пакеты от дистрибутива
   • Проще для быстрой установки, но версии могут быть старее.

Когда контейнеры подходят: быстрые тесты, CI. Когда не подходят: требования к длительному хранению данных без настройки томов.

Безопасность и твики после установки

• Отключите вход по паролю в pg_hba.conf для доверенных сетей и используйте md5 или scram-sha-256 для паролей.
• Включите шифрование соединений TLS, если соединяетесь по сети.
• Ограничьте доступ к порту 5432 с помощью брандмауэра (ufw/iptables).
• Регулярно применяйте обновления безопасности.

Пример строки в pg_hba.conf для локальной аутентификации через scram:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             127.0.0.1/32            scram-sha-256

Бэкапы и восстановление — краткая методика

• Регулярные логические бэкапы: pg_dump (удобно для миграций и восстановления отдельных баз).
• Репликация и физические бэкапы: pg_basebackup + wal-архивирование для полного восстановления всей кластера.
• Тестируйте восстановление на отдельной машине.

Тонкости эксплуатации и масштабирования

• Вертикальное масштабирование: добавление CPU/памяти/диска.
• Горизонтальное масштабирование: выбор подхода (репликация, шардинг через сторонние решения).
• Мониторинг: используйте инструменты статистики (pg_stat_activity, pg_stat_statements) и внешние системы (Prometheus + Grafana).

Отладка частых проблем

1. Служба не запускается — проверьте логи: /var/log/postgresql/postgresql--main.log.
2. Ошибка подключения — проверьте pg_hba.conf и порт 5432, брандмауэр.
3. Нехватка места — проверьте размер каталогов данных и WAL; настройте ротацию и архивацию.
4. Права доступа — проверьте роль и привилегии через \du и \l.

Ролевые чек-листы (быстрое руководство по задачам)

Для системного администратора:

• Установить пакет и зависимости.
• Настроить и протестировать бэкапы.
• Настроить мониторинг и оповещения.
• Прописать политику обновлений.

Для разработчика:

• Создать отдельную роль для приложения.
• Создать базу данных и назначить права.
• Настроить переменные окружения и строки подключения.
• Тестировать миграции на копии базы.

Для DevOps:

• Автоматизировать деплой PostgreSQL (Ansible/Terraform).
• Настроить репликацию и failover.
• Проверить восстановление из резервных копий.

Критерии приёмки

• Служба PostgreSQL запущена и отвечает на порту 5432 на localhost.
• Есть роль postgres с установленным паролем.
• Создана тестовая база db1 и пользователь user1 с назначенными правами.
• Настроен хотя бы один метод бэкапа и протестировано восстановление.

Когда PostgreSQL может не подойти

• Требуется сверхнизкая латентность на миллисекунды в распределённой системе — возможно, подойдут специализированные NewSQL‑решения.
• Нужен встроенный горизонтальный шардинг «из коробки» — PostgreSQL требует сторонних решений или архитектурных паттернов.

Короткая инструкция по миграции с другой СУБД

1. Оцените несовместимости типов данных.
2. Экспортируйте схему и данные (pg_dump для PostgreSQL, инструмент миграции для источника).
3. Тестируйте миграцию на копии данных.
4. Перенастройте приложения на новую строку подключения.

Мини-процедура восстановления после ошибки конфигурации

1. Остановите службу: service postgresql stop.
2. Верните резервную копию конфигурации postgresql.conf и pg_hba.conf.
3. Запустите службу: service postgresql start.
4. Проверьте логи и статус: service postgresql status.

Решение: Как выбрать между apt, репозиторием PostgreSQL и Docker

• apt (дистрибутивный пакет): быстро и просто, стабильные версии.
• Официальный репозиторий PostgreSQL: свежие версии и патчи безопасности.
• Docker: удобен для изолированных окружений и CI.

Mermaid-диаграмма простой decision-tree:

graph LR
  A{Нужна ли самая новая версия?} -->|Да| B[Использовать репозиторий PostgreSQL]
  A -->|Нет| C[Установить через apt]
  B --> D{Постоянное хранение данных?}
  C --> D
  D -->|Да| E[Установить на хост или настроить тома Docker]
  D -->|Нет| F[Может подойти Docker контейнер]

Краткое резюме

PostgreSQL — надёжная и расширяемая СУБД. Установка на Ubuntu занимает несколько минут. После установки важно настроить безопасность, бэкапы и мониторинг. Для управления удобно использовать pgAdmin или автоматизированные инструменты.

Итоговые советы:

• Используйте официальный репозиторий для актуальных версий в проде.
• Настройте бэкапы и проверяйте восстановление регулярно.
• Ограничьте доступ к базе и включите шифрование для сетевых подключений.

Если вы не уверены в выборе СУБД для проекта, можно протестировать другие движки, например Microsoft SQL Server или MongoDB, в соответствии с требованиями к структуре данных и масштабированию.