PostgreSQL: установка и администрирование на Ubuntu

PostgreSQL устанавливается на Ubuntu одной командой, но сразу после установки большинство новичков упирается в непонятную ошибку при первом подключении. Причина — особая система аутентификации PostgreSQL, которая отличается от привычной по MySQL логики «логин плюс пароль». Разберём установку, настройку доступа и повседневное администрирование по порядку, с объяснением именно тех мест, где обычно возникает путаница.

Установка PostgreSQL

Обновить список пакетов и установить PostgreSQL вместе с дополнительными утилитами:

sudo apt update
sudo apt install postgresql postgresql-contrib -y

Пакет postgresql-contrib добавляет дополнительные расширения и утилиты, которые пригодятся при более сложном администрировании — устанавливать его вместе с основным пакетом сразу удобнее, чем потом искать поштучно.

Проверить, что сервис запустился и работает:

sudo systemctl status postgresql

В выводе должна быть строка Active: active (running). PostgreSQL также автоматически добавляется в автозапуск при установке.

Первое подключение и peer authentication

Вот момент, на котором спотыкается почти каждый, кто впервые работает с PostgreSQL.

При установке создаётся системный пользователь Linux с именем postgres, а вместе с ним — одноимённый пользователь внутри самого PostgreSQL с правами суперпользователя базы данных. По умолчанию локальные подключения используют метод аутентификации peer: PostgreSQL проверяет, что имя системного пользователя Linux, от которого выполняется подключение, совпадает с именем пользователя базы данных. Никакой пароль при этом не спрашивается и не проверяется.

Из этого следует практическое правило. Подключиться к PostgreSQL под пользователем postgres можно только предварительно переключившись на одноимённого системного пользователя:

sudo -u postgres psql

Эта команда сработает без каких-либо дополнительных настроек сразу после установки.

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

psql -U postgres

Если выполнить эту команду от имени обычного пользователя (например, ubuntu), PostgreSQL вернёт ошибку Peer authentication failed for user "postgres" — и дело здесь не в неправильном пароле, а именно в несовпадении системного и PostgreSQL-пользователя, для которого метод peer требует полного совпадения имён.

Оказавшись внутри psql через sudo -u postgres psql, вы увидите приглашение командной строки PostgreSQL:

postgres=#

Выйти из этого режима можно командой:

\q

Настройка аутентификации по паролю

Метод peer удобен для локального администрирования с самого сервера, но неудобен если приложению нужно подключаться к базе с логином и паролем — например, стандартная связка веб-приложения с PostgreSQL так не работает.

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

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

Если версия PostgreSQL другая, замените 16 в пути на нужный номер — узнать установленную версию можно командой psql --version.

Внутри файла нужно найти строки похожие на эти:

local   all   postgres   peer
local   all   all        peer

Чтобы разрешить вход по паролю для локальных подключений, замените peer на scram-sha-256 — это современный рекомендованный метод шифрования пароля, начиная с PostgreSQL 14:

local   all   postgres   scram-sha-256
local   all   all        scram-sha-256

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

sudo systemctl reload postgresql

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

psql -U postgres -W

Флаг -W заставляет psql запросить пароль явно перед подключением.

Смена пароля пользователя postgres

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

Подключитесь через peer-метод:

sudo -u postgres psql

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

ALTER USER postgres PASSWORD 'надёжный-пароль';

Выйдите командой \q.

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

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

Подключитесь как postgres:

sudo -u postgres psql

Создайте нового пользователя с паролем:

CREATE USER myapp_user WITH PASSWORD 'надёжный-пароль';

Создайте базу данных с указанием владельца:

CREATE DATABASE myapp_db OWNER myapp_user;

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

GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myapp_user;

Такая связка — отдельный пользователь под конкретное приложение с доступом только к своей базе — стандартная практика, ограничивающая ущерб в случае компрометации самого приложения.

Основные команды psql

Оказавшись внутри интерактивной консоли psql, пригодятся несколько базовых команд для навигации.

Посмотреть список всех баз данных на сервере:

\l

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

\du

Переключиться на конкретную базу данных:

\c имя_базы

Посмотреть список таблиц в текущей базе:

\dt

Посмотреть структуру конкретной таблицы — её колонки, типы данных и индексы:

\d имя_таблицы

Выйти из psql:

\q

Разрешение удалённых подключений

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

Первое место — файл postgresql.conf, отвечающий за общие параметры сервера:

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

Найдите строку с параметром listen_addresses и измените её значение:

listen_addresses = '*'

Звёздочка означает, что PostgreSQL будет слушать входящие подключения на всех сетевых интерфейсах сервера, а не только на локальном.

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

host   all   all   ВАШ_IP/32   scram-sha-256

Замените ВАШ_IP на конкретный IP-адрес, с которого будут идти подключения, или укажите диапазон подсети. Указывать 0.0.0.0/0 — то есть разрешить подключение с любого адреса в интернете — не рекомендуется по соображениям безопасности: это открывает базу данных для попыток перебора пароля со всего мира.

После изменения postgresql.conf требуется полный перезапуск сервиса, а не просто перечитывание конфигурации:

sudo systemctl restart postgresql

Наконец, порт PostgreSQL по умолчанию — 5432 — нужно открыть в файрволе сервера, разрешив подключения только с нужного IP-адреса, а не для всех подряд.

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

Создать текстовый SQL-бэкап конкретной базы данных:

pg_dump -U postgres myapp_db > backup.sql

Восстановить базу данных из такого бэкапа:

psql -U postgres myapp_db < backup.sql

Для больших баз данных удобнее использовать сжатый формат custom вместо обычного текстового SQL — он компактнее и восстанавливается быстрее:

pg_dump -U postgres -Fc myapp_db > backup.dump

Восстановление из custom-формата выполняется отдельной утилитой:

pg_restore -U postgres -d myapp_db backup.dump

Управление сервисом

Проверить статус сервиса:

sudo systemctl status postgresql

Перезапустить сервис полностью — необходимо после изменений в postgresql.conf:

sudo systemctl restart postgresql

Перечитать конфигурацию без полного перезапуска — достаточно после изменений в pg_hba.conf:

sudo systemctl reload postgresql

Включить автозапуск при загрузке сервера, если он почему-то был отключён:

sudo systemctl enable postgresql

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

Почему psql -U postgres выдаёт ошибку Peer authentication failed?

Потому что метод аутентификации peer, установленный по умолчанию для локальных подключений, требует полного совпадения имени системного пользователя Linux и имени пользователя PostgreSQL. Команда psql -U postgres, выполненная от имени любого другого пользователя системы, эту проверку не проходит. Правильный способ подключиться под postgres без смены метода аутентификации — команда sudo -u postgres psql.

Как разрешить вход в PostgreSQL по паролю вместо peer?

Откройте файл pg_hba.conf, найдите строки с методом peer для нужных подключений и замените значение на scram-sha-256. После изменения выполните sudo systemctl reload postgresql, чтобы конфигурация применилась без полного перезапуска сервиса.

Где находится файл конфигурации PostgreSQL?

Путь зависит от установленной версии: /etc/postgresql/ВЕРСИЯ/main/postgresql.conf для общих настроек сервера и /etc/postgresql/ВЕРСИЯ/main/pg_hba.conf для настроек аутентификации. Узнать установленную версию можно командой psql --version.

Нужно ли перезапускать PostgreSQL после каждого изменения конфигурации?

Не всегда. Изменения в pg_hba.conf, отвечающем за правила аутентификации, применяются командой reload без разрыва текущих подключений. Изменения в postgresql.conf, в частности параметр listen_addresses, требуют полного restart.

Как посмотреть какая версия PostgreSQL установлена?

Команда psql --version покажет версию клиента psql. Внутри самой консоли psql версию сервера покажет команда SELECT version();.

PostgreSQL комфортно работает на VPS с 1-2 ГБ RAM для небольших и средних проектов. На UFO.Hosting сервер с Ubuntu разворачивается за минуты — PostgreSQL можно установить сразу после подключения по SSH.