PostgreSQL: большая памятка по командам для админов, DevOps и тех, кто «не ДБА, но опять чинит прод»

PostgreSQL - штука прекрасная. Пока не приходит момент:

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

Именно в такие моменты начинается археология:

«Так… где же была та команда с ALTER DEFAULT PRIVILEGES?..»

Ниже - большая практическая памятка по PostgreSQL. Без академической воды. С примерами из реальной инфраструктуры.

Подключение и базовые команды

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

psql -h HOST -p 5432 -U USERNAME -d DATABASE

Пример:

psql -h 10.10.10.15 -U postgres -d app_db

Полезные команды внутри psql

Команда	Описание
\l	список баз
\du	список ролей
\dt	список таблиц
\dn	список схем
\conninfo	информация о подключении
\x	красивый вертикальный вывод
\q	выход
\d table_name	описание таблицы
\df	список функций
\dv	список view
\timing	показать время выполнения запросов

Пользователи, роли и права

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

Создать пользователя

CREATE USER app_user WITH PASSWORD 'SUPER_PASSWORD';

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

CREATE USER app_user
WITH PASSWORD 'SUPER_PASSWORD'
VALID UNTIL '2027-01-01';

Создать пользователя только для чтения

CREATE USER readonly_user
WITH PASSWORD 'PASSWORD';

Роли и группы

В PostgreSQL роль и пользователь - почти одно и то же.

Обычно делают так:

создают роль,
выдают ей права,
потом добавляют пользователей в эту роль.

Это сильно упрощает жизнь.

Потому что:

«Выдать доступ 40 аналитикам»
звучит намного лучше, чем
«40 раз руками делать GRANT».

Создаем роль readonly

CREATE ROLE readonly;

Создаем роль readwrite

CREATE ROLE readwrite;

Выдача RO-доступа

Доступ к существующим таблицам

GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT
ON ALL TABLES IN SCHEMA public
TO readonly;

Доступ к будущим таблицам

Очень важный момент.

Без этого:

новые таблицы создались,
а доступов нет,
аналитики уже пишут в чат:

«У нас опять ничего не работает».

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

Доступ к sequence

Если используются SERIAL или BIGSERIAL.

GRANT SELECT
ON ALL SEQUENCES IN SCHEMA public
TO readonly;

Привязка пользователя к роли

GRANT readonly TO analyst_user;

Полный RW-доступ

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

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

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;

Доступ только к конкретной таблице

GRANT SELECT ON TABLE users TO analyst_user;

Отозвать права

REVOKE INSERT, UPDATE, DELETE ON TABLE users FROM analyst_user;

Сделать пользователя суперпользователем

Обычно делать так не надо. Но иногда ночью в проде:

«Да выдайте уже ему superuser и спать пойдем».

ALTER USER app_user WITH SUPERUSER;

Удаление пользователей и ролей

Удалить пользователя

DROP USER app_user;

Удалить роль

DROP ROLE readonly;

Базы данных

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

CREATE DATABASE app_db;

Создание базы с владельцем

CREATE DATABASE app_db OWNER app_user;

Смена владельца базы

ALTER DATABASE app_db OWNER TO new_owner;

Переименование базы

ALTER DATABASE old_name RENAME TO new_name;

Просмотр размеров БД

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

Таблицы и схемы

Создать схему

CREATE SCHEMA analytics;

Выдать права на схему

GRANT USAGE ON SCHEMA analytics TO readonly;

Размер таблиц

SELECT
    relname AS table_name,
    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
    schemaname,
    relname,
    pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 20;

Дамп и восстановление

Дамп базы

Обычный dump

pg_dump \
-h HOST \
-p 5432 \
-U USER \
-d DATABASE \
-f dump.sql

Dump в custom формате

Самый удобный вариант.

pg_dump \
--format=custom \
--compress=5 \
--verbose \
--file=backup.dump \
--dbname=app_db

Dump directory format

Очень полезен для больших баз. Поддерживает parallel restore.

pg_dump \
--clean \
--encoding=utf8 \
--format=directory \
--jobs=5 \
--verbose \
--compress=5 \
--file=backup_dir \
--dbname=app_db

Исключение таблиц

pg_dump \
--exclude-table=logs \
--exclude-table=events \
...

Исключение данных таблицы

Структура будет, данных - нет.

pg_dump \
--exclude-table-data=logs

Архивирование

tar -cvf backup.tar.gz backup_dir/

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

Из SQL dump

psql \
-h HOST \
-U USER \
-d DATABASE \
-f dump.sql

Из custom dump

pg_restore \
--dbname=app_db \
--jobs=5 \
--verbose \
backup.dump

Из directory dump

pg_restore \
--dbname=app_db \
--format=directory \
--jobs=5 \
--verbose \
backup_dir

Восстановление только схемы

pg_restore --schema-only

Восстановление только данных

pg_restore --data-only

Репликация и Patroni

Проверка реплик

Лаг репликации

SELECT
    r.client_addr,
    r.usename,
    r.application_name,
    r.state,
    r.sync_state,
    (pg_wal_lsn_diff(pg_current_wal_lsn(), r.sent_lsn) / 1024)::int AS pending,
    (pg_wal_lsn_diff(r.sent_lsn, r.write_lsn) / 1024)::int AS write,
    (pg_wal_lsn_diff(r.write_lsn, r.flush_lsn) / 1024)::int AS flush,
    (pg_wal_lsn_diff(r.flush_lsn, r.replay_lsn) / 1024)::int AS replay,
    ((pg_wal_lsn_diff(pg_current_wal_lsn(), r.replay_lsn)) / 1024)::int AS total_lag
FROM pg_catalog.pg_stat_replication r;

Как интерпретировать lag

Поле	Что означает
pending	WAL не отправлен - сеть
write	WAL не записан - проблемы с диском
flush	WAL не flushed - IO
replay	реплика не успевает воспроизводить
total_lag	суммарное отставание

Проверка статуса реплики

На replica:

SELECT pg_is_in_recovery();

true → replica
false → master

Текущий WAL

SELECT pg_current_wal_lsn();

Проверка replication slots

SELECT * FROM pg_replication_slots;

Проверка archive status

SELECT * FROM pg_stat_archiver;

Активные подключения

Кто подключен к базе

SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    query
FROM pg_stat_activity;

Долгие запросы

SELECT
    pid,
    now() - query_start AS duration,
    query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

Завершить запрос

SELECT pg_cancel_backend(PID);

Убить подключение

Когда кто-то держит миграцию уже 3 часа

SELECT pg_terminate_backend(PID);

Блокировки

Кто кого блокирует

SELECT
    blocked_locks.pid     AS blocked_pid,
    blocked_activity.usename  AS blocked_user,
    blocking_locks.pid     AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity
    ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
WHERE NOT blocked_locks.granted;

VACUUM и обслуживание

Обычный vacuum

VACUUM;

Анализ статистики

ANALYZE;

Полный vacuum

Осторожно: блокирует таблицы.

VACUUM FULL;

Reindex

REINDEX DATABASE app_db;

Проверка bloat

SELECT
    schemaname,
    relname,
    n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

Индексы

Проверка неиспользуемых индексов

SELECT
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan;

Размер индексов

SELECT
    indexrelname,
    pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

Мониторинг производительности

Самые медленные запросы ( pg_stat_statements )

Сначала включаем расширение:

CREATE EXTENSION pg_stat_statements;

Топ запросов

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

Cache hit ratio

SELECT
    sum(heap_blks_read) AS heap_read,
    sum(heap_blks_hit)  AS heap_hit,
    (sum(heap_blks_hit) - sum(heap_blks_read))
        / sum(heap_blks_hit) AS ratio
FROM pg_statio_user_tables;

Полезные команды для DevOps

Проверка версий

SELECT version();

Проверка конфигурации

SHOW shared_buffers;
SHOW work_mem;
SHOW maintenance_work_mem;
SHOW max_connections;

Где лежит конфиг

SHOW config_file;

Перезагрузка конфига

Без рестарта PostgreSQL.

SELECT pg_reload_conf();

Проверка checkpoint

SELECT * FROM pg_stat_bgwriter;

Проверка autovacuum

SELECT
    relname,
    last_autovacuum,
    vacuum_count
FROM pg_stat_user_tables;

WAL и архивы

Размер WAL

SELECT
    pg_size_pretty(
        sum(size)
    )
FROM pg_ls_waldir();

Принудительный switch WAL

SELECT pg_switch_wal();

Создать checkpoint

CHECKPOINT;

Полезные shell-команды

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

pg_isready

Подключение без ввода пароля

Файл:

~/.pgpass

Содержимое:

HOST:PORT:DATABASE:USER:PASSWORD

Права:

chmod 600 ~/.pgpass

Экспорт переменных

export PGHOST=127.0.0.1
export PGPORT=5432
export PGUSER=postgres
export PGPASSWORD=secret

Полезные советы из боли и страданий.

Никогда не выдавайте SUPERUSER приложению

Потому что однажды ORM решит:

«А давай удалим всё».

Всегда используйте роли

Не выдавайте права напрямую пользователям.

Иначе через год:

кто что может,
кто откуда унаследовал права,
кто вообще жив -

будет выясняться через спиритический сеанс.

Используйте ALTER DEFAULT PRIVILEGES

Это одна из самых забываемых вещей в PostgreSQL. И одна из самых важных.

Не делайте VACUUM FULL

без причины

Это не:

«почистить немного».

Это:

«остановить жизнь таблицы на время операции».

Для больших БД используйте

directory
dump

Потому что:

parallel restore,
меньше боли,
быстрее восстановление,
удобнее миграции.

Следите за replication slots

Иначе WAL может внезапно занять:

300 ГБ,
800 ГБ,
«почему диск умер».

Мини-чеклист для продакшена

Что проверять	Почему
replication lag	чтобы replica не жила в прошлом
autovacuum	иначе bloat
pg_stat_statements	поиск медленных запросов
WAL size	чтобы не закончился диск
locks	чтобы миграции не висели
checkpoints	чтобы не убивать IO
unused indexes	лишняя нагрузка
long transactions	источник боли

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

Особенно в два часа ночи, когда: