Установка и настройка PostgreSQL

PostgreSQL — объектно-реляционная СУБД с открытым исходным кодом. Она поддерживает транзакции, ограничения целостности, индексы, представления, расширения, JSON/JSONB, полнотекстовый поиск и другие возможности.

Основные компоненты:

Проверка клиента и сервера:

psql --version
pg_isready -h localhost -p 5432

Подключение:

psql -h localhost -p 5432 -U postgres -d postgres

Содержание

Выбор способа установки

PostgreSQL можно установить из системного или официального репозитория, через графический установщик, Homebrew либо Docker. Для локальной разработки удобны пакетный менеджер и Docker. Для рабочего сервера важны контролируемые обновления, отдельный каталог данных, резервное копирование и ограниченный сетевой доступ.

Перед установкой определяют версию PostgreSQL, каталог данных, порт, требования к доступности, объём ресурсов, правила доступа и способ восстановления.

Ubuntu и Debian

Обновление пакетов и установка:

sudo apt update
sudo apt install postgresql postgresql-contrib

Управление службой:

sudo systemctl status postgresql
sudo systemctl start postgresql
sudo systemctl enable postgresql
sudo systemctl restart postgresql
sudo systemctl reload postgresql

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

sudo -u postgres psql

Просмотр кластеров в Debian-подобных системах:

pg_lsclusters

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

sudo pg_ctlcluster <версия> main start
sudo pg_ctlcluster <версия> main restart
sudo pg_ctlcluster <версия> main stop

Вместо <версия> указывается установленная основная версия.

Fedora, Rocky Linux и AlmaLinux

Установка и первичная инициализация:

sudo dnf install postgresql-server postgresql-contrib
sudo postgresql-setup --initdb

Запуск и автозапуск:

sudo systemctl start postgresql
sudo systemctl enable postgresql
sudo systemctl status postgresql

Название пакета, команды и службы может зависеть от версии и выбранного репозитория.

Подключение:

sudo -u postgres psql

Docker

Запуск контейнера:

docker run --name postgres-local \
  -e POSTGRES_USER=app_user \
  -e POSTGRES_PASSWORD=change_me \
  -e POSTGRES_DB=app_db \
  -p 5432:5432 \
  -v postgres_data:/var/lib/postgresql/data \
  -d postgres

Проверка и управление:

docker ps
docker logs postgres-local
docker exec -it postgres-local psql -U app_user -d app_db
docker stop postgres-local
docker start postgres-local

Удаление контейнера не удаляет именованный том:

docker rm -f postgres-local

Удаление тома уничтожает данные:

docker volume rm postgres_data

Docker Compose

Файл compose.yaml:

services:
  db:
    image: postgres
    container_name: postgres-local
    restart: unless-stopped
    environment:
      POSTGRES_USER: app_user
      POSTGRES_PASSWORD: change_me
      POSTGRES_DB: app_db
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U app_user -d app_db"]
      interval: 5s
      timeout: 5s
      retries: 5

volumes:
  postgres_data:

Команды:

docker compose up -d
docker compose logs -f db
docker compose down

Команда docker compose down -v дополнительно удаляет том и данные. Рабочие пароли нельзя хранить открытым текстом в репозитории — применяйте секреты или переменные окружения.

Инициализация кластера вручную

Если установщик не создал кластер автоматически:

mkdir -p /path/to/postgres-data
initdb -D /path/to/postgres-data --encoding=UTF8

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

pg_ctl -D /path/to/postgres-data -l postgres.log start
pg_ctl -D /path/to/postgres-data reload
pg_ctl -D /path/to/postgres-data restart
pg_ctl -D /path/to/postgres-data stop

Каталог данных должен принадлежать системному пользователю PostgreSQL. Сервер нельзя запускать от root.

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

psql --version
pg_isready -h localhost -p 5432
psql -h localhost -U postgres -d postgres

Проверка внутри psql:

SELECT version();
SHOW server_version;

SELECT
    current_database(),
    current_user,
    inet_server_addr(),
    inet_server_port();

Успешный ответ pg_isready:

localhost:5432 - accepting connections

Основы psql

Команда Назначение
\l Список баз данных
\c database_name Подключиться к базе
\dn Список схем
\dt Список таблиц
\d table_name Описание таблицы
\du Список ролей
\dx Список расширений
\conninfo Сведения о подключении
\timing Измерение времени запросов
\x Расширенный вывод
\i file.sql Выполнить SQL-файл
\q Выход

Справка:

\h CREATE TABLE
\?

Выполнение файла и одной команды:

psql -h localhost -U app_user -d app_db -f schema.sql
psql -h localhost -U app_user -d app_db -c "SELECT current_date;"

Создание роли и базы

Для приложения создают отдельную роль и не используют суперпользователя postgres.

CREATE ROLE app_user
WITH LOGIN
PASSWORD 'replace_with_strong_password';

CREATE DATABASE app_db
WITH OWNER = app_user
ENCODING = 'UTF8';

Подключение:

psql -h localhost -U app_user -d app_db

Безопасная интерактивная смена пароля:

\password app_user

Запрет и разрешение входа:

ALTER ROLE app_user NOLOGIN;
ALTER ROLE app_user LOGIN;

Роли и права

Роль с атрибутом LOGIN может подключаться. Роль без LOGIN удобно использовать как группу прав.

CREATE ROLE app_readonly NOLOGIN;
GRANT app_readonly TO analyst_user;

Права на базу, схему и существующие таблицы:

GRANT CONNECT ON DATABASE app_db TO app_readonly;
GRANT USAGE ON SCHEMA public TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;

Права на будущие таблицы:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO app_readonly;

Права рабочего приложения:

GRANT CONNECT ON DATABASE app_db TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA public TO app_user;

GRANT USAGE, SELECT
ON ALL SEQUENCES IN SCHEMA public TO app_user;

Приложению не следует выдавать SUPERUSER, CREATEDB или CREATEROLE, если эти полномочия не нужны.

Основные файлы конфигурации

Точные пути:

SHOW config_file;
SHOW hba_file;
SHOW ident_file;
SHOW data_directory;

Параметры и источники значений:

SELECT name, setting, unit, source, pending_restart
FROM pg_settings
ORDER BY name;

pending_restart = true означает, что изменение ждёт перезапуска.

Настройка postgresql.conf

Адрес и порт

Только локальные подключения:

listen_addresses = 'localhost'
port = 5432

Все сетевые интерфейсы:

listen_addresses = '*'

Значение * не выдаёт доступ автоматически: нужны правила pg_hba.conf, права роли и настройка сетевого экрана.

Подключения и память

max_connections = 100
shared_buffers = '1GB'
work_mem = '16MB'
maintenance_work_mem = '256MB'
effective_cache_size = '3GB'

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

Часовой пояс и WAL

timezone = 'UTC'
log_timezone = 'UTC'
checkpoint_timeout = '15min'
max_wal_size = '4GB'
min_wal_size = '1GB'

WAL нужен для восстановления после сбоя, репликации и архивирования. Значения настраивают под нагрузку, диски и требования к восстановлению.

Применение перезагружаемых параметров:

SELECT pg_reload_conf();

Или:

sudo systemctl reload postgresql

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

SELECT name, context, pending_restart
FROM pg_settings
WHERE name = 'max_connections';

Изменение параметров через SQL

Системное значение:

ALTER SYSTEM SET log_min_duration_statement = '500ms';
SELECT pg_reload_conf();

Сброс:

ALTER SYSTEM RESET log_min_duration_statement;

Для текущего сеанса:

SET statement_timeout = '30s';

Для роли или сочетания роли и базы:

ALTER ROLE app_user SET statement_timeout = '30s';
ALTER ROLE app_user IN DATABASE app_db
SET statement_timeout = '30s';

ALTER SYSTEM записывает значения в postgresql.auto.conf. Перед применением следует выяснить, нужна перезагрузка или полный перезапуск.

Настройка pg_hba.conf

HBA (Host-Based Authentication) определяет, кто, откуда, к какой базе и каким способом может подключаться.

Формат:

local  database  user  method
host   database  user  address  method

Пример:

# Unix-сокет
local   all       postgres                      peer
local   all       all                           scram-sha-256

# Локальный TCP/IP
host    all       all       127.0.0.1/32        scram-sha-256
host    all       all       ::1/128             scram-sha-256

# Приложение из частной подсети
host    app_db    app_user  10.20.0.0/24        scram-sha-256

Основные методы:

Метод Назначение
scram-sha-256 Безопасная парольная аутентификация
peer Сопоставление локального пользователя ОС
cert Клиентский TLS-сертификат
reject Явный запрет
trust Доступ без пароля; не подходит для обычного сетевого доступа

Правила проверяются сверху вниз, применяется первое совпавшее. Узкие правила размещают раньше широких.

Проверка файла и ошибок:

SELECT line_number, type, database, user_name,
       address, auth_method, error
FROM pg_hba_file_rules
ORDER BY line_number;

После изменения:

SELECT pg_reload_conf();

Удалённое подключение

В postgresql.conf:

listen_addresses = '*'

В pg_hba.conf:

host    app_db    app_user    10.20.0.0/24    scram-sha-256

После этого перезапускают или перезагружают сервер, разрешают порт только доверенным адресам, проверяют роль и право CONNECT. Не следует открывать PostgreSQL для всего интернета через 0.0.0.0/0.

Пароли и строка подключения

Рекомендуемое хранение новых паролей:

password_encryption = 'scram-sha-256'

После изменения пароль роли задают заново:

\password app_user

URI:

postgresql://app_user:password@localhost:5432/app_db

Формат параметров:

host=localhost port=5432 dbname=app_db user=app_user sslmode=prefer

Специальные символы в URI должны быть percent-encoded. Секрет лучше передавать отдельно через защищённую конфигурацию.

Файл ~/.pgpass:

hostname:port:database:username:password

Права в Linux и macOS:

chmod 600 ~/.pgpass

Настройка TLS

Для удалённых подключений рекомендуется шифрование TLS. В postgresql.conf:

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

Закрытый ключ должен принадлежать пользователю PostgreSQL и иметь ограниченные права. Правило только для TLS:

hostssl app_db app_user 10.20.0.0/24 scram-sha-256

Режимы клиента:

sslmode Поведение
disable Не использовать TLS
prefer Предпочитать TLS
require Требовать TLS без полной проверки имени сервера
verify-ca Проверять центр сертификации
verify-full Проверять сертификат и имя сервера

Для рабочей среды предпочтителен verify-full с доверенным сертификатом.

Проверка текущего соединения:

SELECT ssl, version, cipher
FROM pg_stat_ssl
WHERE pid = pg_backend_pid();

Схемы и search_path

Схема — пространство имён внутри базы.

CREATE SCHEMA app AUTHORIZATION app_user;

ALTER ROLE app_user IN DATABASE app_db
SET search_path = app, public;

Проверка:

SHOW search_path;
SELECT current_schema();

В важных запросах полезно явно указывать схему:

SELECT * FROM app.customers;

Если создание объектов в public не требуется:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

Перед отзывом права проверьте требования приложений и миграций.

Расширения

Доступные расширения:

SELECT name, default_version, installed_version
FROM pg_available_extensions
ORDER BY name;

Установка в текущую базу:

CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

Просмотр установленных расширений:

\dx

Некоторые расширения требуют предварительной установки системного пакета или изменения конфигурации сервера.

Журналирование

Пример базовых параметров:

logging_collector = on
log_destination = 'stderr'
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d.log'
log_rotation_age = '1d'
log_truncate_on_rotation = on
log_line_prefix = '%m [%p] %u@%d %r '
log_connections = on
log_disconnections = on
log_lock_waits = on
log_min_duration_statement = '500ms'

log_min_duration_statement записывает запросы, выполняющиеся дольше указанного времени. Постоянный log_statement = 'all' может быстро заполнить диск и сохранить конфиденциальные параметры запросов.

Необходимо настроить ротацию, срок хранения журналов и контроль свободного места.

Тайм-ауты

statement_timeout = '30s'
lock_timeout = '5s'
idle_in_transaction_session_timeout = '60s'

Для миграции можно временно изменить значение внутри транзакции:

BEGIN;
SET LOCAL statement_timeout = '5min';
-- Команды миграции
COMMIT;

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

Пул соединений

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

Размер пула выбирают с учётом количества экземпляров приложения, числа ядер, характера запросов и max_connections. Необходимо оставить резерв для администрирования и мониторинга.

Подключения по базам:

SELECT datname, count(*) AS connections
FROM pg_stat_activity
GROUP BY datname
ORDER BY connections DESC;

Состояния соединений:

SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state
ORDER BY state;

autovacuum, VACUUM и ANALYZE

PostgreSQL использует MVCC. Старые версии изменённых строк некоторое время остаются в таблице. VACUUM освобождает их пространство для повторного использования, а ANALYZE обновляет статистику планировщика.

autovacuum = on
track_counts = on

Ручное обслуживание:

ANALYZE;
VACUUM ANALYZE app.orders;
VACUUM (VERBOSE, ANALYZE) app.orders;

VACUUM FULL переписывает таблицу и блокирует её:

VACUUM FULL app.orders;

Его не применяют как регулярную замену autovacuum.

Статистика таблиц:

SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    last_autovacuum,
    last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

Статистика запросов

Расширение pg_stat_statements агрегирует статистику запросов. Для предварительной загрузки в postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'

Изменение требует перезапуска. Затем в нужной базе:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

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

SELECT
    calls,
    total_exec_time,
    mean_exec_time,
    rows,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Набор столбцов зависит от основной версии. Проверка:

\d pg_stat_statements

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

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

Одна база в custom-формате

pg_dump -h localhost -U app_user -d app_db \
  -F c -f app_db.dump

Просмотр содержимого:

pg_restore --list app_db.dump

Восстановление:

createdb -h localhost -U postgres restored_db
pg_restore -h localhost -U postgres \
  -d restored_db --clean --if-exists app_db.dump

Параллельное восстановление:

pg_restore -h localhost -U postgres \
  -d restored_db -j 4 app_db.dump

Текстовый SQL-дамп

pg_dump -h localhost -U app_user -d app_db \
  -F p -f app_db.sql

psql -h localhost -U postgres \
  -d restored_db -f app_db.sql

Глобальные объекты и весь кластер

pg_dumpall -h localhost -U postgres \
  --globals-only -f globals.sql

pg_dumpall -h localhost -U postgres -f cluster.sql

Физическая копия

pg_basebackup -h db.example.local -U replication_user \
  -D /backup/base -Fp -Xs -P

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

Проверка восстановления

  1. Подготовьте отдельный тестовый сервер или базу.
  2. Восстановите роли и необходимые глобальные объекты.
  3. Восстановите дамп.
  4. Проверьте сообщения pg_restore или psql.
  5. Выполните контрольные запросы и проверки целостности.
  6. Зафиксируйте продолжительность восстановления.

Обновление PostgreSQL

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

Переход между основными версиями выполняют через:

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

Мониторинг

Активные запросы

SELECT
    pid,
    usename,
    datname,
    client_addr,
    state,
    wait_event_type,
    wait_event,
    query_start,
    query
FROM pg_stat_activity
ORDER BY query_start NULLS LAST;

Длительные запросы

SELECT
    pid,
    now() - query_start AS duration,
    usename,
    datname,
    state,
    query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY duration DESC;

Размер баз

SELECT
    datname,
    pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

Размер таблиц с индексами

SELECT
    schemaname,
    relname,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

Блокировки

SELECT
    locktype,
    relation::regclass,
    pid,
    mode,
    granted
FROM pg_locks
ORDER BY granted, pid;

Отмена запроса и завершение сеанса:

SELECT pg_cancel_backend(<pid>);
SELECT pg_terminate_backend(<pid>);

Завершать сеанс следует только после оценки последствий.

Типичные ошибки

psql: command not found

Клиент не установлен или каталог bin отсутствует в PATH.

which psql

В Windows:

where.exe psql

connection refused

Обычно сервер не запущен, указан неверный адрес или порт, listen_addresses не включает интерфейс либо соединение блокирует сетевой экран.

pg_isready -h localhost -p 5432
SHOW listen_addresses;
SHOW port;

no pg_hba.conf entry

Нет подходящего правила. Проверьте тип подключения, базу, роль, адрес клиента, TLS и порядок строк. После исправления выполните:

SELECT pg_reload_conf();

password authentication failed

Проверьте роль, пароль и совпавшее правило pg_hba.conf. Смена пароля:

\password app_user

permission denied

Проверьте CONNECT к базе, USAGE схемы, права таблиц и последовательностей, а также default privileges для будущих объектов.

Порт занят

# Linux
ss -ltnp | grep 5432

# macOS
lsof -nP -iTCP:5432 -sTCP:LISTEN
# Windows
Get-NetTCPConnection -LocalPort 5432

Заканчивается место

df -h
SELECT datname, pg_size_pretty(pg_database_size(datname))
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

Нельзя вручную удалять файлы из каталога данных или pg_wal. Сначала найдите источник роста: таблицы, индексы, WAL, журналы, временные файлы или слоты репликации.

Пример локального проекта

От имени администратора:

CREATE ROLE shop_user
WITH LOGIN
PASSWORD 'replace_with_strong_password';

CREATE DATABASE shop_db
WITH OWNER = shop_user
ENCODING = 'UTF8';

Подключение и создание схемы:

psql -h localhost -U shop_user -d shop_db
CREATE SCHEMA shop AUTHORIZATION shop_user;

ALTER ROLE shop_user IN DATABASE shop_db
SET search_path = shop, public;

Создание таблицы:

CREATE TABLE shop.products (
    product_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    price numeric(12, 2) NOT NULL CHECK (price >= 0),
    created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO shop.products (name, price)
VALUES
    ('Клавиатура', 4500.00),
    ('Мышь', 2200.00);

SELECT * FROM shop.products ORDER BY product_id;

Строка подключения:

postgresql://shop_user:password@localhost:5432/shop_db

Базовая безопасность

Чек-лист после установки

  1. Проверить версии клиента и сервера.
  2. Убедиться, что служба запущена и включён автозапуск.
  3. Создать отдельные роль и базу приложения.
  4. Настроить listen_addresses только для нужных интерфейсов.
  5. Настроить узкие правила pg_hba.conf.
  6. Ограничить порт сетевым экраном.
  7. Включить TLS для удалённых подключений.
  8. Настроить часовой пояс, тайм-ауты и журналирование.
  9. Проверить autovacuum и статистику.
  10. Настроить резервное копирование и выполнить тестовое восстановление.
  11. Настроить мониторинг диска, подключений, блокировок и долгих запросов.
  12. Задокументировать версию, каталоги, порт, роли и процедуру восстановления.

Краткая памятка

psql --version
pg_isready -h localhost -p 5432
psql -h localhost -p 5432 -U app_user -d app_db
psql -h localhost -U app_user -d app_db -f migration.sql
pg_dump -h localhost -U app_user -d app_db -F c -f app_db.dump
pg_restore -h localhost -U postgres -d restored_db app_db.dump
SELECT version();
SHOW config_file;
SHOW hba_file;
SHOW data_directory;
SELECT pg_reload_conf();
SELECT pg_size_pretty(pg_database_size(current_database()));
\l                 список баз
\c app_db          подключение к базе
\dn                список схем
\dt app.*          таблицы схемы app
\d app.products    описание таблицы
\du                список ролей
\dx                список расширений
\conninfo          сведения о подключении
\q                 выход