Раздел 6 — Структура, пользователи и администрирование

Схема — пространство имён внутри базы данных. Таблицы, представления, функции и типы живут в схемах. Полное имя объекта состоит из двух частей: схема.таблица.

CREATE SCHEMA reports;
CREATE SCHEMA staging AUTHORIZATION etl_user;

-- Полное имя объекта
CREATE TABLE reports.monthly_activity (
    month      date    PRIMARY KEY,
    active_users integer NOT NULL
);

-- Список схем
\dn

search_path — список схем, в которых сервер ищет объект, если имя указано без схемы. Работает как PATH в командной строке: побеждает первое совпадение.

SHOW search_path;
-- "$user", public

-- Изменить на время сессии
SET search_path TO reports, public;

-- Закрепить за ролью навсегда
ALTER ROLE analyst SET search_path TO reports, public;

-- Закрепить за базой
ALTER DATABASE socialnet SET search_path TO app, public;

Табличное пространство — привязка объектов к каталогу на диске. Применяется, когда нужно разнести данные по разным носителям: горячие таблицы на быстрый SSD, архив на медленный большой диск.

CREATE TABLESPACE archive LOCATION '/mnt/hdd/pgdata';
CREATE TABLE old_logs (...) TABLESPACE archive;

-- Что есть в кластере
\db

В PostgreSQL нет отдельных сущностей «пользователь» и «группа» — есть единое понятие роль. Роль с правом входа ведёт себя как пользователь, роль без него — как группа.

-- Роль для входа = пользователь
CREATE ROLE ivanov LOGIN PASSWORD '…';

-- Роль без входа = группа
CREATE ROLE analysts;

-- Включить пользователя в группу
GRANT analysts TO ivanov;

-- CREATE USER — это синоним CREATE ROLE ... LOGIN

-- Посмотреть роли и их атрибуты
\du

-- Изменить атрибуты
ALTER ROLE ivanov CONNECTION LIMIT 5;
ALTER ROLE ivanov VALID UNTIL '2027-01-01';
ALTER ROLE ivanov NOLOGIN;   -- заблокировать вход, не удаляя роль

У каждого объекта есть владелец — по умолчанию тот, кто его создал. Владелец может всё: изменять структуру, выдавать права другим, удалять объект. Остальные получают доступ только через явно выданные привилегии.

-- Выдать права
GRANT SELECT ON users TO analysts;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_role;
GRANT USAGE ON SCHEMA app TO app_role;

-- Права на отдельные столбцы
GRANT SELECT (id, name, city) ON users TO analysts;

-- Отозвать
REVOKE DELETE ON users FROM app_role;

-- Посмотреть выданные права
\dp users

Права по умолчанию — то, о чём забывают

GRANT ... ON ALL TABLES действует только на таблицы, существующие в момент выполнения. Созданная завтра таблица прав не получит, и приложение упадёт после ближайшей миграции.

-- Права на всё, что БУДЕТ создано ролью owner в схеме app
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_role;

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
    GRANT USAGE, SELECT ON SEQUENCES TO app_role;

-- Посмотреть настроенные умолчания
\ddp

Отдельная тонкость — роль PUBLIC, означающая «все роли». По умолчанию она имеет CONNECT к новым базам и USAGE, а в старых версиях имела и CREATE на схему public. Начиная с PostgreSQL 15 право CREATE у PUBLIC отобрано — это изменение ломало множество инструкций из интернета.

-- Закрыть базу от посторонних подключений
REVOKE CONNECT ON DATABASE socialnet FROM PUBLIC;
GRANT CONNECT ON DATABASE socialnet TO app_role;

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

Смысл разделения простой: ошибка или уязвимость в коде приложения не должна приводить к DROP TABLE. При SQL-инъекции злоумышленник получает права той роли, под которой работает приложение — и разница между «прочитал данные» и «удалил базу» определяется здесь.

-- 1. Роли
CREATE ROLE app_owner LOGIN PASSWORD '…';
CREATE ROLE app_user  LOGIN PASSWORD '…';

-- 2. База и схема принадлежат владельцу
CREATE DATABASE socialnet OWNER app_owner;
\c socialnet
CREATE SCHEMA app AUTHORIZATION app_owner;

-- 3. Закрываем всё лишнее
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE CONNECT ON DATABASE socialnet FROM PUBLIC;

-- 4. Приложению — доступ к схеме и данным, но не к структуре
GRANT CONNECT ON DATABASE socialnet TO app_user;
GRANT USAGE ON SCHEMA app TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE
    ON ALL TABLES IN SCHEMA app TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_user;

-- 5. И на всё, что появится в будущих миграциях
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
    GRANT USAGE, SELECT ON SEQUENCES TO app_user;

-- 6. Чтобы приложение не писало схему в запросах
ALTER ROLE app_user SET search_path TO app;

Когда разграничения на уровне таблиц недостаточно, применяют защиту на уровне строк: пользователь видит только свои записи.

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY orders_own ON orders
    FOR ALL
    TO app_user
    USING (manager_id = current_setting('app.manager_id')::integer);

Два принципиально разных подхода: логическая выгрузка и физическая копия файлов.

Логическая копия: pg_dump

Выгружает содержимое базы в набор SQL-команд или в специальный формат. Работает на живой базе, не блокирует её, даёт согласованный снимок на момент запуска.

# Простой текстовый SQL — читаемо, восстанавливается через psql
pg_dump -U postgres -d socialnet -f socialnet.sql

# Custom-формат — сжатый, позволяет выборочное восстановление
pg_dump -U postgres -d socialnet -F c -f socialnet.dump

# Каталог, параллельно в 4 потока — быстрее всего на больших базах
pg_dump -U postgres -d socialnet -F d -j 4 -f socialnet_dir/

# Только схема без данных / только данные
pg_dump -U postgres -d socialnet --schema-only -f schema.sql
pg_dump -U postgres -d socialnet --data-only   -f data.sql

# Отдельная таблица
pg_dump -U postgres -d socialnet -t app.users -f users.sql

# Роли и настройки кластера — их pg_dump НЕ включает
pg_dumpall -U postgres --globals-only -f globals.sql

# Восстановление текстового дампа
psql -U postgres -d socialnet_new -f socialnet.sql

# Восстановление custom-формата, параллельно
pg_restore -U postgres -d socialnet_new -j 4 socialnet.dump

# Только одна таблица из полного дампа
pg_restore -U postgres -d socialnet_new -t users socialnet.dump

# Посмотреть содержимое дампа, ничего не восстанавливая
pg_restore --list socialnet.dump

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

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

pg_basebackup -U replicator -D /backup/base -F t -z -P

PostgreSQL использует многоверсионность: UPDATE не изменяет строку на месте, а создаёт новую версию и помечает старую как устаревшую. DELETE тоже не освобождает место немедленно — он лишь помечает версию мёртвой.

Так реализована изолированность транзакций: старую версию всё ещё могут читать транзакции, начавшиеся раньше. Плата — раздувание (bloat): файл таблицы растёт, хотя строк в ней не прибавилось.

-- Ручной запуск
VACUUM ANALYZE users;
VACUUM (VERBOSE, ANALYZE) users;

-- Когда таблицу последний раз обслуживали и сколько в ней мёртвых строк
SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum,
       last_autoanalyze
FROM   pg_stat_user_tables
ORDER BY n_dead_tup DESC;

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

-- Кто сейчас подключён и чем занят
SELECT pid, usename, datname, state,
       now() - xact_start AS xact_age,
       now() - query_start AS query_age,
       wait_event_type, left(query, 60) AS query
FROM   pg_stat_activity
WHERE  state <> 'idle'
ORDER BY xact_start;

-- Зависшие транзакции: idle in transaction дольше 5 минут
SELECT pid, usename, now() - xact_start AS age, left(query, 80)
FROM   pg_stat_activity
WHERE  state = 'idle in transaction'
  AND  now() - xact_start > interval '5 minutes';

-- Аварийно прервать конкретный запрос или соединение
SELECT pg_cancel_backend(12345);     -- мягко: отменить запрос
SELECT pg_terminate_backend(12345);  -- жёстко: разорвать соединение

-- Размеры баз и таблиц
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,
       pg_size_pretty(pg_relation_size(relid))       AS table_only
FROM   pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

-- Эффективность кэша: доля попаданий должна быть выше 0.95
SELECT datname,
       blks_hit, blks_read,
       round(blks_hit * 1.0 / nullif(blks_hit + blks_read, 0), 3) AS hit_ratio,
       xact_commit, xact_rollback, deadlocks
FROM   pg_stat_database
WHERE  datname IS NOT NULL;

-- Неиспользуемые индексы: занимают место и замедляют запись
SELECT schemaname, relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM   pg_stat_user_indexes
WHERE  idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Для поиска медленных запросов ставят расширение pg_stat_statements — оно накапливает статистику по всем выполненным запросам.

-- В postgresql.conf: shared_preload_libraries = 'pg_stat_statements'
-- затем перезапуск сервера и:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Самые дорогие запросы по суммарному времени
SELECT round(total_exec_time::numeric, 1) AS total_ms,
       calls,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       left(query, 80) AS query
FROM   pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Настройки журнала, которые стоит включить сразу — они ничего не стоят и однажды спасут расследование:

log_min_duration_statement = 500      # писать запросы дольше 500 мс
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on                   # ожидания блокировок
log_temp_files = 0                    # все временные файлы: признак нехватки work_mem
log_autovacuum_min_duration = 0
log_line_prefix = '%m [%p] %u@%d '    # время, pid, пользователь, база

Обновление на новую мажорную версию в Debian выполняется штатным инструментом:

# Установить новую версию рядом со старой
sudo apt install postgresql-18

# Новая версия создаст свой кластер — его надо убрать перед переносом
sudo pg_dropcluster 18 main --stop

# Перенести кластер 17/main в версию 18
sudo pg_upgradecluster 17 main

# Проверить, что всё работает, и только потом удалить старый
pg_lsclusters
sudo pg_dropcluster 17 main