Подходит ли платформа для начинающих без опыта работы?

Да, курсы разбиты по уровням: Junior, Middle, Senior. Начинающие могут стартовать с базовых тем Python, Docker и алгоритмов и постепенно двигаться к более сложным темам.

Как быстро можно подготовиться к собеседованию на позицию Junior разработчика?

При занятиях 1–2 часа в день — от 2 до 4 недель на основные темы. Платформа анализирует слабые места по результатам квизов и строит персональный план подготовки.

Какие технологии охватывает платформа?

Python, FastAPI, Django, Docker, алгоритмы и структуры данных, Agile/Scrum, SQL, CI/CD, системный дизайн, код-ревью и более 50 других тем для разработчиков.

Платформа бесплатная?

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

Как платформа помогает найти работу программистом?

Платформа даёт фундаментальные знания, которые проверяют на технических собеседованиях: алгоритмы, архитектура, фреймворки. Мок-интервью имитирует реальное собеседование. Система прогресса показывает, какие темы нужно подтянуть перед собеседованием.

administration

Администрирование PostgreSQL

Пользователи, привилегии, pg_dump, мониторинг, autovacuum

Администрирование PostgreSQL

📋 Полное руководство по администрированию PostgreSQL

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

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

В чём разница?

В PostgreSQL роли и пользователи — это по сути одно и то же. Разница лишь в семантике:

CREATE USER = CREATE ROLE с флагом LOGIN по умолчанию
CREATE ROLE = роль без права входа (используется для группировки прав)

Почему это важно: Разделение на пользователей и роли позволяет реализовать ролевую модель доступа (RBAC). Вы создаёте роли с определёнными правами, а затем назначаете эти роли пользователям. Это упрощает управление правами в больших проектах.

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

-- Создание пользователя с паролем
-- LOGIN добавляется автоматически при использовании CREATE USER
CREATE USER app_user WITH PASSWORD 'secret123';

-- Создание роли для группировки прав
-- NOLOGIN означает, что под этой ролью нельзя войти напрямую
CREATE ROLE read_only NOLOGIN;

-- Роль с правами на чтение всех таблиц в схеме public
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;

-- Назначение роли пользователю
-- Теперь app_user наследует все права read_only
GRANT read_only TO app_user;

-- Создание администратора с расширенными правами
-- CREATEDB — может создавать базы данных
-- CREATEROLE — может создавать других пользователей/роли
CREATE USER admin_user WITH PASSWORD 'admin123' CREATEDB CREATEROLE;

Атрибуты ролей

Атрибут	Описание
`LOGIN`	Может подключаться к серверу
`SUPERUSER`	Полные права (обходить все проверки прав)
`CREATEDB`	Может создавать базы данных
`CREATEROLE`	Может создавать и изменять другие роли
`INHERIT`	Наследовать права от других ролей (по умолчанию включено)
`REPLICATION`	Может создавать репликационные подключения
`BYPASSRLS`	Игнорировать политики Row Level Security
`NOLOGIN`, `NOSUPERUSER`, и т.д.	Отрицание соответствующего атрибута

Просмотр существующих ролей

-- Все роли в системе
SELECT 
    rolname AS role_name,
    rolsuper AS is_superuser,
    rolcreaterole AS can_create_role,
    rolcreatedb AS can_create_db,
    rolcanlogin AS can_login,
    rolvaliduntil AS password_expires
FROM pg_roles
ORDER BY rolname;

-- Права конкретной роли
\du+ app_user

-- Кто кому назначен (иерархия ролей)
SELECT 
    r.rolname AS role,
    m.rolname AS member,
    a.admin_option AS has_admin_option
FROM pg_auth_members a
JOIN pg_roles r ON r.oid = a.roleid
JOIN pg_roles m ON m.oid = a.member
ORDER BY r.rolname, m.rolname;

Изменение и удаление

-- Изменить пароль
ALTER USER app_user WITH PASSWORD 'new_secret456';

-- Добавить право на создание БД
ALTER USER app_user CREATEDB;

-- Установить срок действия пароля
ALTER USER app_user VALID UNTIL '2026-12-31';

-- Ограничить количество одновременных подключений
ALTER USER app_user CONNECTION LIMIT 5;

-- Удалить пользователя
-- Сначала нужно отозвать все права и завершить сессии
DROP USER app_user;

-- Удалить с каскадом (удалит все зависимые объекты)
DROP USER app_user CASCADE;

⚠️ Важно: Нельзя удалить роль, если она владеет объектами или ей предоставлены права. Сначала передайте владение или используйте CASCADE.

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

Уровни привилегий

PostgreSQL имеет многоуровневую систему прав:

┌─────────────────────────────────────┐
│         SERVER (сервер)             │
│    pg_hba.conf, pg_ident.conf       │
└─────────────────────────────────────┘
              ↓
┌─────────────────────────────────────┐
│       DATABASE (база данных)        │
│    CONNECT, CREATE, TEMPORARY       │
└─────────────────────────────────────┘
              ↓
┌─────────────────────────────────────┐
│        SCHEMA (схема)               │
│    USAGE, CREATE                    │
└─────────────────────────────────────┘
              ↓
┌─────────────────────────────────────┐
│    TABLE/VIEW (таблица/представление)│
│    SELECT, INSERT, UPDATE, DELETE   │
│    TRUNCATE, REFERENCES, TRIGGER    │
└─────────────────────────────────────┘
              ↓
┌─────────────────────────────────────┐
│      COLUMN (отдельный столбец)     │
│    SELECT, INSERT, UPDATE           │
└─────────────────────────────────────┘

Предоставление прав (GRANT)

-- Права на уровне базы данных
-- CONNECT — может подключаться к БД
-- CREATE — может создавать схемы
-- TEMPORARY — может создавать временные таблицы
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT CREATE ON DATABASE mydb TO app_user;
GRANT ALL PRIVILEGES ON DATABASE mydb TO admin_user;

-- Права на уровне схемы
-- USAGE — может использовать схему (искать в ней объекты)
-- CREATE — может создавать объекты в схеме
GRANT USAGE ON SCHEMA public TO app_user;
GRANT CREATE ON SCHEMA public TO app_user;

-- Права на уровне таблиц
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;
GRANT SELECT, INSERT, UPDATE, DELETE ON users TO app_user;
GRANT ALL PRIVILEGES ON TABLE users TO admin_user;

-- Права на отдельные колонки
-- Полезно, когда нужно скрыть чувствительные данные (например, пароли)
GRANT SELECT (id, name, email) ON users TO app_user;
GRANT UPDATE (name, email) ON users TO app_user;

-- Права на последовательности (для автоинкрементных ID)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- Права на функции
GRANT EXECUTE ON FUNCTION get_user_by_id TO app_user;

Отозвать права (REVOKE)

-- Отозвать конкретный право
REVOKE DELETE ON users FROM app_user;

-- Отозвать все права на таблицу
REVOKE ALL ON TABLE users FROM app_user;

-- Отозвать права на базу данных
REVOKE CREATE ON DATABASE mydb FROM app_user;

-- Отозвать роль у пользователя
REVOKE read_only FROM app_user;

-- Каскадное отзывание (отозвать у всех, кто получил через эту роль)
REVOKE GRANT OPTION FOR read_only FROM app_user CASCADE;

Права по умолчанию (ALTER DEFAULT PRIVILEGES)

Проблема: Когда вы создаёте новую таблицу, только вы (владелец) имеете к ней доступ. Другие пользователи не смогут её видеть, даже если у них были права на другие таблицы.

Решение: ALTER DEFAULT PRIVILEGES автоматически выдаёт права на новые объекты.

-- Все новые таблицы в схеме public будут доступны для чтения app_user
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO app_user;

-- Новые таблицы будут доступны для полного доступа admin_user
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON TABLES TO admin_user;

-- Применить к конкретному пользователю (чья сессия создаёт объекты)
ALTER DEFAULT PRIVILEGES FOR ROLE developer
IN SCHEMA public
GRANT ALL ON TABLES TO app_user;

-- Сбросить настройки по умолчанию
ALTER DEFAULT PRIVILEGES IN SCHEMA public
REVOKE ALL ON TABLES FROM app_user;

💡 Best Practice: Настройте ALTER DEFAULT PRIVILEGES сразу после создания проекта. Это избавит от проблем с доступом к новым таблицам в будущем.

Проверка текущих прав

-- Права на таблицы
SELECT 
    table_schema,
    table_name,
    privilege_type,
    grantee
FROM information_schema.table_privileges
WHERE table_schema = 'public'
ORDER BY table_name, privilege_type;

-- Права на колонки
SELECT 
    table_name,
    column_name,
    privilege_type,
    grantee
FROM information_schema.column_privileges
WHERE table_schema = 'public';

-- Кто владеет таблицами
SELECT 
    tablename,
    tableowner
FROM pg_tables
WHERE schemaname = 'public';

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

Почему резервное копирование критически важно?

📊 Статистика: 60% компаний, потерявших данные, закрываются в течение 6 месяцев.

Типы резервного копирования:

Полное (Full) — копия всей базы данных
Инкрементальное — только изменения с последнего бэкапа
Дифференциальное — изменения с последнего полного бэкапа
Point-in-Time (PITR) — восстановление на конкретный момент времени

pg_dump — логическое резервное копирование

pg_dump создаёт логическую копию базы данных (SQL-команды или кастомный формат).

Форматы вывода

Формат	Флаг	Описание
Plain SQL	`-F p`	Обычный SQL-файл, можно открыть в текстовом редакторе
Custom	`-F c`	Сжатый бинарный формат PostgreSQL (требует pg_restore)
Directory	`-F d`	Папка с файлами (поддерживает параллельное восстановление)
Tar	`-F t`	TAR-архив

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

# Полный дамп в кастомном формате (рекомендуется)
# -F c — custom format (сжатый, поддерживает параллелизм)
# -Z 9 — максимальное сжатие (0-9)
pg_dump -U postgres -d mydb -F c -Z 5 -f mydb.dump

# Дамп в SQL-формате (универсальный)
pg_dump -U postgres -d mydb -F p -f mydb.sql

# Только схема (без данных)
# Полезно для развёртывания структуры на другом сервере
pg_dump -U postgres -d mydb --schema-only -f schema.sql

# Только данные (без структуры)
pg_dump -U postgres -d mydb --data-only -f data.sql

# Дамп конкретных таблиц
pg_dump -U postgres -d mydb --table=users --table=orders -f tables.dump

# Исключить таблицы
pg_dump -U postgres -d mydb --exclude-table=logs -f mydb.dump

# Дамп конкретной схемы
pg_dump -U postgres -d mydb --schema=public -f public_schema.dump

# Дамп с владельцем и правами
pg_dump -U postgres -d mydb --no-owner --no-privileges -f mydb.sql

# Параллельный дамп (только для формата directory)
pg_dump -U postgres -d mydb -F d -j 4 -f dump_directory

Дополнительные опции

# Включить большие объекты (BLOB)
pg_dump -U postgres -d mydb --blobs -f mydb.dump

# Не сохранять значения последовательностей (SERIAL)
pg_dump -U postgres -d mydb --no-sequence -f mydb.dump

# Сжать данные
pg_dump -U postgres -d mydb -F c -Z 9 -f mydb.dump

# Показать прогресс
pg_dump -U postgres -d mydb -F c -f mydb.dump --verbose

# Дамп с указанием хоста и порта
pg_dump -h localhost -p 5432 -U postgres -d mydb -F c -f mydb.dump

pg_restore — восстановление из дампа

pg_restore работает только с кастомными форматами (-F c, -F d, -F t).

# Полное восстановление
pg_restore -U postgres -d mydb mydb.dump

# Восстановление только схемы
pg_restore -U postgres -d mydb --schema-only mydb.dump

# Восстановление только данных
pg_restore -U postgres -d mydb --data-only mydb.dump

# Восстановление конкретной таблицы
pg_restore -U postgres -d mydb --table=users mydb.dump

# Восстановление нескольких таблиц
pg_restore -U postgres -d mydb --table=users --table=orders mydb.dump

# Параллельное восстановление (ускоряет процесс)
# -j 4 — использовать 4 потока
pg_restore -U postgres -d mydb -j 4 mydb.dump

# Восстановление в новую базу данных
pg_restore -U postgres -d newdb mydb.dump

# Показать список содержимого дампа
pg_restore -l mydb.dump

# Восстановление с выбором объектов (по списку)
# Создайте файл list.txt с нужными объектами из pg_restore -l
pg_restore -U postgres -d mydb -L list.txt mydb.dump

# Пересоздать базу перед восстановлением
pg_restore -U postgres -d mydb --clean --if-exists mydb.dump

# Восстановление с пропуском ошибок
pg_restore -U postgres -d mydb --exit-on-error=false mydb.dump

⚠️ Важно: Для pg_restore -d база данных должна существовать. Если нет — создайте её сначала:

createdb -U postgres mydb pg_restore -U postgres -d mydb mydb.dump

pg_dumpall — дамп всего кластера

# Дамп всех баз данных и глобальных объектов (роли, настройки)
pg_dumpall -U postgres > cluster.sql

# Только глобальные объекты (роли, табличные пространства)
pg_dumpall -U postgres --globals-only > globals.sql

# Восстановление всего кластера
psql -U postgres -f cluster.sql

# Восстановление только глобальных объектов
psql -U postgres -f globals.sql

Point-in-Time Recovery (PITR)

WAL-архивирование позволяет восстановить базу на любой момент времени.

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

# Включить архивирование WAL
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'

# Уровень чекпоинтов (влияет на производительность)
checkpoint_timeout = 5min
max_wal_size = 2GB

# Количество WAL-файлов для ретеншена
wal_keep_size = 1GB

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

# 1. Остановить PostgreSQL
systemctl stop postgresql

# 2. Очистить data directory
rm -rf /var/lib/postgresql/data/*

# 3. Восстановить базовый бэкап
tar -xzf base_backup.tar.gz -C /var/lib/postgresql/data/

# 4. Создать файл восстановления
cat > /var/lib/postgresql/data/recovery.signal << EOF
EOF

# 5. Настроить точку восстановления
cat >> /var/lib/postgresql/data/postgresql.auto.conf << EOF
restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2026-03-23 14:30:00'
EOF

# 6. Запустить PostgreSQL
systemctl start postgresql

Стратегии резервного копирования

#!/bin/bash
# production_backup.sh — пример скрипта для продакшена

set -e

DB_NAME="mydb"
DB_USER="postgres"
BACKUP_DIR="/backup/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7

# Создать директорию
mkdir -p "$BACKUP_DIR"

# Полный дамп с сжатием
echo "[$(date)] Starting backup of $DB_NAME..."
pg_dump -U "$DB_USER" -d "$DB_NAME" -F c -Z 5 \
    -f "$BACKUP_DIR/${DB_NAME}_${DATE}.dump"

# Проверка целостности
if [ $? -eq 0 ]; then
    echo "[$(date)] Backup completed successfully"
    # Удалить старые бэкапы
    find "$BACKUP_DIR" -name "${DB_NAME}_*.dump" -mtime +$RETENTION_DAYS -delete
    echo "[$(date)] Cleaned up backups older than $RETENTION_DAYS days"
else
    echo "[$(date)] Backup FAILED!" >&2
    exit 1
fi

# Cron-задача (запускать ежедневно в 2:00)
# crontab -e
0 2 * * * /path/to/production_backup.sh >> /var/log/pg_backup.log 2>&1

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

pg_stat_activity — текущая активность

Системное представление, показывающее все активные подключения.

-- Все активные подключения
SELECT 
    pid,                    -- ID процесса
    usename,                -- Имя пользователя
    datname,                -- База данных
    client_addr,            -- IP клиента
    application_name,       -- Название приложения
    state,                  -- Состояние (active, idle, idle in transaction)
    wait_event_type,        -- Тип ожидания (Lock, IO, CPU)
    wait_event,             -- Конкретное событие ожидания
    query,                  -- Текущий запрос
    now() - query_start AS duration,  -- Длительность выполнения
    backend_start,          -- Когда подключился
    xact_start              -- Когда началась транзакция
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY duration DESC;

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

-- Долгие запросы (выполняются более 5 минут)
SELECT 
    pid,
    usename,
    state,
    now() - query_start AS duration,
    query
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - query_start > interval '5 minutes'
ORDER BY duration DESC;

-- Зависшие транзакции (idle in transaction более 1 часа)
-- Опасно! Такие транзакции блокируют vacuum и могут вызвать раздувание таблиц
SELECT 
    pid,
    usename,
    now() - xact_start AS transaction_duration,
    query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - xact_start > interval '1 hour'
ORDER BY transaction_duration DESC;

-- Блокировки (кто кого блокирует)
SELECT 
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocked_activity.query AS blocked_query,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocking_activity.query AS blocking_query
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
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

Завершение проблемных сессий

-- Завершить конкретный процесс
SELECT pg_terminate_backend(12345);

-- Завершить все зависшие запросы определённого типа
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE query LIKE '%long_running_query%'
  AND state = 'active'
  AND now() - query_start > interval '10 minutes';

-- Завершить все idle-сессии старше 1 часа
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND now() - query_start > interval '1 hour';

⚠️ Осторожно: pg_terminate_backend() принудительно завершает сессию. Убедитесь, что это не навредит данным.

pg_stat_statements — статистика запросов

Лучшее расширение для анализа производительности! Показывает статистику по всем выполненным запросам.

Установка

-- Добавить в shared_preload_libraries в postgresql.conf
-- shared_preload_libraries = 'pg_stat_statements'

-- Перезагрузить PostgreSQL
SELECT pg_reload_conf();

-- Создать расширение в базе данных
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Анализ запросов

-- Топ самых медленных запросов (по среднему времени)
SELECT 
    query,
    calls,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round(total_exec_time::numeric, 2) AS total_ms,
    rows,
    round(mean_exec_time * calls / 1000, 2) AS total_seconds
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

-- Топ самых частых запросов
SELECT 
    query,
    calls,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    rows
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 20;

-- Запросы с наибольшим общим временем выполнения
SELECT 
    query,
    calls,
    round(total_exec_time::numeric, 2) AS total_ms,
    round(mean_exec_time::numeric, 2) AS mean_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- Запросы с большим количеством строк
SELECT 
    query,
    calls,
    rows,
    round(rows::numeric / calls, 2) AS avg_rows
FROM pg_stat_statements
ORDER BY rows DESC
LIMIT 20;

-- Найти запросы с полным сканированием таблиц
SELECT 
    query,
    calls,
    round(mean_exec_time::numeric, 2) AS mean_ms
FROM pg_stat_statements
WHERE query ILIKE '%seq scan%'
   OR query ILIKE '%sequential scan%'
ORDER BY mean_exec_time DESC;

Сброс статистики

-- Сбросить всю статистику
SELECT pg_stat_statements_reset();

-- Сбросить статистику для конкретного запроса
SELECT pg_stat_statements_reset(userid, dbid, queryid);

pg_stat_user_tables — статистика по таблицам

-- Общая статистика по таблицам
SELECT 
    schemaname,
    relname AS table_name,
    seq_scan,                    -- Количество полных сканирований
    seq_tup_read,                -- Прочитано строк при full scan
    idx_scan,                    -- Количество сканирований через индекс
    idx_tup_fetch,               -- Прочитано строк через индекс
    n_tup_ins AS inserts,        -- Вставлено строк
    n_tup_upd AS updates,        -- Обновлено строк
    n_tup_del AS deletes,        -- Удалено строк
    n_live_tup AS live_rows,     -- Живые строки (после последнего vacuum)
    n_dead_tup AS dead_rows,     -- Мёртвые строки (требуют vacuum)
    last_vacuum,                 -- Последний ручной vacuum
    last_autovacuum,             -- Последний автоматический vacuum
    last_analyze,                -- Последний ручной analyze
    last_autoanalyze,            -- Последний автоматический analyze
    vacuum_count,                -- Количество vacuum
    autovacuum_count,            -- Количество autovacuum
    analyze_count,               -- Количество analyze
    autoanalyze_count            -- Количество autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

Поиск таблиц, требующих обслуживания

-- Таблицы с большим количеством мёртвых строк
SELECT 
    relname AS table_name,
    n_live_tup AS live_rows,
    n_dead_tup AS dead_rows,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_percent,
    last_autovacuum,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- Таблицы, которые давно не анализировались
SELECT 
    relname AS table_name,
    n_live_tup,
    last_autoanalyze,
    now() - last_autoanalyze AS time_since_analyze
FROM pg_stat_user_tables
WHERE last_autoanalyze IS NULL
   OR now() - last_autoanalyze > interval '7 days'
ORDER BY last_autoanalyze NULLS FIRST;

-- Таблицы с частыми полными сканированиями (возможно, нужны индексы)
SELECT 
    relname AS table_name,
    seq_scan,
    idx_scan,
    round(100.0 * seq_scan / NULLIF(seq_scan + idx_scan, 0), 2) AS seq_scan_percent
FROM pg_stat_user_tables
WHERE seq_scan + idx_scan > 100
ORDER BY seq_scan DESC;

pg_stat_indexes — статистика по индексам

-- Статистика использования индексов
SELECT 
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan,                    -- Количество сканирований индекса
    idx_tup_read,                -- Прочитано строк
    idx_tup_fetch,               -- Получено строк
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- Неиспользуемые индексы (кандидаты на удаление)
SELECT 
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'  -- Не удалять первичные ключи
ORDER BY pg_relation_size(indexrelid) DESC;

Системные представления для мониторинга

-- Размер баз данных
SELECT 
    datname AS database_name,
    pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_stat_database
ORDER BY pg_database_size(datname) DESC;

-- Статистика по базе данных
SELECT 
    datname,
    numbackends AS active_connections,
    xact_commit AS transactions_committed,
    xact_rollback AS transactions_rolled_back,
    blks_read AS blocks_read,
    blks_hit AS blocks_hit,
    round(100.0 * blks_hit / NULLIF(blks_read + blks_hit, 0), 2) AS cache_hit_ratio,
    tup_returned AS rows_returned,
    tup_fetched AS rows_fetched,
    tup_inserted AS rows_inserted,
    tup_updated AS rows_updated,
    tup_deleted AS rows_deleted,
    conflicts,
    temp_files,
    temp_bytes
FROM pg_stat_database
WHERE datname = current_database();

💡 Важно: Cache hit ratio ниже 99% указывает на нехватку оперативной памяти или неправильную настройку shared_buffers.

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

Что такое Vacuum и зачем он нужен?

PostgreSQL использует MVCC (Multi-Version Concurrency Control) — каждая транзакция видит свою версию данных. При обновлении/удалении строк старые версии не удаляются сразу, а помечаются как «мёртвые».

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

Типы Vacuum

Тип	Описание
`VACUUM`	Обычный vacuum (не блокирует таблицу)
`VACUUM FULL`	Полное переупорядочивание таблицы (требует блокировку)
`VACUUM ANALYZE`	Vacuum + обновление статистики
`AUTOVACUUM`	Автоматический фоновый процесс

Настройка Autovacuum

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

-- Глобальные настройки
SHOW autovacuum;
SHOW autovacuum_max_workers;
SHOW autovacuum_naptime;
SHOW autovacuum_vacuum_threshold;
SHOW autovacuum_vacuum_scale_factor;
SHOW autovacuum_analyze_threshold;
SHOW autovacuum_analyze_scale_factor;
SHOW autovacuum_vacuum_cost_limit;

-- Статистика по таблицам
SELECT 
    schemaname,
    relname,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze,
    vacuum_count,
    autovacuum_count,
    analyze_count,
    autoanalyze_count
FROM pg_stat_user_tables;

Индивидуальные настройки для таблиц

-- Для часто изменяемых таблиц настроить более агрессивный vacuum
ALTER TABLE users SET (
    autovacuum_vacuum_threshold = 100,
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_analyze_threshold = 100,
    autovacuum_analyze_scale_factor = 0.02
);

-- Для больших таблиц настроить менее частый vacuum
ALTER TABLE logs SET (
    autovacuum_vacuum_threshold = 1000,
    autovacuum_vacuum_scale_factor = 0.2
);

-- Для таблиц только для чтения отключить autovacuum
ALTER TABLE reference_data SET (
    autovacuum_enabled = false
);

-- Для критически важных таблиц увеличить приоритет
ALTER TABLE orders SET (
    autovacuum_vacuum_cost_limit = 2000
);

Ручной Vacuum

-- Обычный vacuum (не блокирует)
VACUUM users;

-- Vacuum с анализом статистики
VACUUM ANALYZE users;

-- Vacuum всех таблиц в базе
VACUUM;

-- Vacuum конкретной таблицы с подробным выводом
VACUUM VERBOSE users;

-- Полное переупорядочивание таблицы (ТРЕБУЕТ БЛОКИРОВКУ!)
-- Используйте только когда таблица сильно раздута
VACUUM FULL users;

-- Полное переупорядочивание с анализом
VACUUM FULL ANALYZE users;

⚠️ Важно: VACUUM FULL требует эксклюзивной блокировки таблицы — все запросы к таблице будут заблокированы. Используйте в maintenance window.

Когда нужен VACUUM FULL?

-- Проверить раздувание таблиц
SELECT 
    schemaname,
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    n_dead_tup AS dead_tuples,
    n_live_tup AS live_tuples,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup, 0), 2) AS dead_percent
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC;

-- Таблицы с раздуванием > 20%
SELECT 
    relname,
    pg_size_pretty(pg_relation_size(relid)) AS current_size,
    n_dead_tup,
    n_live_tup,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup, 0), 2) AS dead_percent
FROM pg_stat_user_tables
WHERE n_live_tup > 0
  AND 100.0 * n_dead_tup / n_live_tup > 20
ORDER BY n_dead_tup DESC;

Обслуживание индексов

-- Перестроить индекс (требует блокировку)
REINDEX INDEX users_email_idx;

-- Перестроить все индексы таблицы
REINDEX TABLE users;

-- Перестроить всю базу данных
REINDEX DATABASE mydb;

-- Перестроить индекс без блокировки (PostgreSQL 12+)
REINDEX INDEX CONCURRENTLY users_email_idx;

-- Проверить размер индексов
SELECT 
    indexname,
    tablename,
    pg_size_pretty(pg_relation_size(indexname::regclass)) AS index_size
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexname::regclass) DESC;

Настройка аутентификации (pg_hba.conf)

Что такое pg_hba.conf?

pg_hba.conf (Host-Based Authentication) контролирует, кто может подключаться к PostgreSQL, с каких адресов и каким методом аутентифицироваться.

Файл обычно находится в $PGDATA/pg_hba.conf.

Формат записи

TYPE  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]

Поле	Описание
`TYPE`	`local`, `host`, `hostssl`, `hostnossl`
`DATABASE`	Имя базы данных, `all`, `sameuser`, `samerole`
`USER`	Имя пользователя, `all`, `+groupname`
`ADDRESS`	CIDR-адрес (для host-записей)
`METHOD`	Метод аутентификации
`OPTIONS`	Дополнительные параметры

Методы аутентификации

Метод	Описание	Безопасность
`trust`	Без пароля, любой может войти	❌ Не используйте в продакшене
`reject`	Отклонить подключение	—
`md5`	Пароль с хешированием MD5	⚠️ Устаревший
`scram-sha-256`	Пароль с хешированием SCRAM-SHA-256	✅ Рекомендуется
`peer`	Проверка по имени ОС-пользователя (Unix)	✅ Для локальных подключений
`ident`	Проверка через ident-сервер (порт 113)	⚠️ Редко используется
`cert`	Аутентификация по SSL-сертификату	✅ Для сервер-сервер
`ldap`	Аутентификация через LDAP	✅ Для корпоративных сред
`radius`	Аутентификация через RADIUS	✅ Для централизованного управления

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

# =========================================
# PostgreSQL Client Authentication Configuration File
# =========================================

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# -----------------------------------------
# Локальные подключения (Unix socket)
# -----------------------------------------
# peer — имя пользователя ОС должно совпадать с именем роли PostgreSQL
local   all             postgres                                peer
local   all             all                                     peer

# -----------------------------------------
# IPv4 локальные подключения
# -----------------------------------------
# md5/scram-sha-256 — требуется пароль
host    all             all             127.0.0.1/32            scram-sha-256

# -----------------------------------------
# IPv6 локальные подключения
# -----------------------------------------
host    all             all             ::1/128                 scram-sha-256

# -----------------------------------------
# Подключения из локальной сети
# -----------------------------------------
# Разрешить приложению подключаться к конкретной БД
host    mydb            app_user        192.168.1.0/24          scram-sha-256

# Разрешить администраторам подключаться ко всем БД
host    all             +admins         192.168.1.0/24          scram-sha-256

# -----------------------------------------
# Удалённые подключения (интернет)
# -----------------------------------------
# Только SSL-подключения
hostssl all             all             0.0.0.0/0               scram-sha-256

# -----------------------------------------
# Репликация
# -----------------------------------------
host    replication     replicator      192.168.1.100/32        scram-sha-256
host    replication     replicator      192.168.1.101/32        scram-sha-256

Применение изменений

-- Перезагрузить конфигурацию (без перезапуска сервера)
SELECT pg_reload_conf();

-- Или через командную строку
# systemctl reload postgresql
# pg_ctl reload -D /var/lib/postgresql/data

Отладка подключений

# Проверить текущие настройки аутентификации
SHOW hba_file;
SHOW ident_file;

# Посмотреть активные подключения
SELECT 
    pid,
    usename,
    client_addr,
    client_port,
    application_name,
    backend_type
FROM pg_stat_activity;

# Логирование подключений (в postgresql.conf)
# log_connections = on
# log_disconnections = on

⚠️ Важно: Порядок записей в pg_hba.conf важен! PostgreSQL использует первую подходящую запись. Более специфичные записи размещайте выше.

Практические примеры и Best Practices

Шаблон ролевой модели для продакшена

-- ============================================
-- 1. Создать роли для разных уровней доступа
-- ============================================

-- Только чтение (для отчётности, аналитики)
CREATE ROLE read_only NOLOGIN;
GRANT CONNECT ON DATABASE mydb TO read_only;
GRANT USAGE ON SCHEMA public TO read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;

-- Чтение + запись (для приложения)
CREATE ROLE app_read_write NOLOGIN;
GRANT CONNECT ON DATABASE mydb TO app_read_write;
GRANT USAGE ON SCHEMA public TO app_read_write;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_read_write;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_read_write;

-- Администратор схемы (для миграций)
CREATE ROLE schema_admin NOLOGIN;
GRANT CONNECT ON DATABASE mydb TO schema_admin;
GRANT USAGE ON SCHEMA public TO schema_admin;
GRANT ALL ON ALL TABLES IN SCHEMA public TO schema_admin;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO schema_admin;
GRANT CREATE ON SCHEMA public TO schema_admin;

-- ============================================
-- 2. Создать пользователей и назначить роли
-- ============================================

-- Пользователь приложения (только чтение)
CREATE USER analytics_user WITH PASSWORD 'secure_password_1';
GRANT read_only TO analytics_user;

-- Пользователь приложения (чтение + запись)
CREATE USER app_user WITH PASSWORD 'secure_password_2';
GRANT app_read_write TO app_user;

-- Пользователь для миграций
CREATE USER migration_user WITH PASSWORD 'secure_password_3';
GRANT schema_admin TO migration_user;

-- Администратор БД
CREATE USER db_admin WITH PASSWORD 'secure_admin_password' CREATEDB CREATEROLE;

-- ============================================
-- 3. Настроить права по умолчанию
-- ============================================

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

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

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON TABLES TO schema_admin;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON SEQUENCES TO schema_admin;

Автоматизация резервного копирования

#!/bin/bash
# /usr/local/bin/pg_backup_production.sh

set -euo pipefail

# ============================================
# Конфигурация
# ============================================
DB_NAME="${PGDATABASE:-mydb}"
DB_USER="${PGUSER:-postgres}"
DB_HOST="${PGHOST:-localhost}"
BACKUP_DIR="/backup/postgresql/${DB_NAME}"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=14
LOG_FILE="/var/log/pg_backup_${DB_NAME}.log"

# ============================================
# Функции
# ============================================
log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $*" | tee -a "$LOG_FILE"
}

error_exit() {
    log "ERROR: $*" >&2
    exit 1
}

# ============================================
# Основная логика
# ============================================
log "Starting backup of database: ${DB_NAME}"

# Создать директорию
mkdir -p "$BACKUP_DIR" || error_exit "Failed to create backup directory"

# Имя файла бэкапа
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${DATE}.dump"

# Выполнить дамп
log "Running pg_dump..."
pg_dump \
    -h "$DB_HOST" \
    -U "$DB_USER" \
    -d "$DB_NAME" \
    -F c \
    -Z 5 \
    -f "$BACKUP_FILE" \
    --verbose \
    2>&1 | tee -a "$LOG_FILE"

# Проверить результат
if [ ${PIPESTATUS[0]} -eq 0 ]; then
    BACKUP_SIZE=$(du -h "$BACKUP_FILE" | cut -f1)
    log "Backup completed successfully: ${BACKUP_FILE} (${BACKUP_SIZE})"
else
    error_exit "pg_dump failed!"
fi

# Удалить старые бэкапы
log "Cleaning up backups older than ${RETENTION_DAYS} days..."
find "$BACKUP_DIR" -name "*.dump" -type f -mtime +$RETENTION_DAYS -delete -print | while read -r file; do
    log "Deleted old backup: $file"
done

# Проверить место на диске
DISK_USAGE=$(df -h "$BACKUP_DIR" | awk 'NR==2 {print $5}')
log "Disk usage: ${DISK_USAGE}"

log "Backup process finished"

Мониторинг с Prometheus + Grafana

# docker-compose.yml для мониторинга
version: '3.8'

services:
  postgres:
    image: postgres:15
    environment:
      POSTGRES_DB: mydb
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres

  postgres_exporter:
    image: prometheuscommunity/postgres-exporter
    environment:
      DATA_SOURCE_NAME: "postgresql://postgres:postgres@postgres:5432/mydb?sslmode=disable"
    ports:
      - "9187:9187"

  prometheus:
    image: prom/prometheus:latest
    volumes:
      - ./prometheus.yml:/etc/prometheus/prometheus.yml
    ports:
      - "9090:9090"

  grafana:
    image: grafana/grafana:latest
    environment:
      GF_SECURITY_ADMIN_PASSWORD: admin
    ports:
      - "3000:3000"
    volumes:
      - grafana_data:/var/lib/grafana

volumes:
  grafana_data:

# prometheus.yml
global:
  scrape_interval: 15s

scrape_configs:
  - job_name: 'postgres'
    static_configs:
      - targets: ['postgres_exporter:9187']

💡 Совет: Используйте готовый Grafana dashboard PostgreSQL Overview (ID 455)

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

## Ежедневные проверки

- [ ] Проверить логи на ошибки
- [ ] Проверить длительность запросов (> 1 мин)
- [ ] Проверить количество подключений
- [ ] Проверить место на диске
- [ ] Проверить работу autovacuum

## Еженедельные проверки

- [ ] Проверить размер баз данных (тренд роста)
- [ ] Проверить неиспользуемые индексы
- [ ] Проверить таблицы с большим количеством dead tuples
- [ ] Проверить актуальность бэкапов (тестовое восстановление)

## Ежемесячные проверки

- [ ] Аудит пользователей и прав доступа
- [ ] Проверка версий PostgreSQL (обновления безопасности)
- [ ] Тестирование disaster recovery плана
- [ ] Анализ производительности (pg_stat_statements)

Распространённые проблемы и решения

Проблема	Причина	Решение
Медленные запросы	Отсутствуют индексы	Проанализировать через `EXPLAIN ANALYZE`, добавить индексы
Блокировки	Долгие транзакции	Найти через `pg_locks`, завершить `pg_terminate_backend()`
Раздувание таблиц	Autovacuum не справляется	Увеличить `autovacuum_max_workers`, уменьшить `scale_factor`
Cache hit ratio < 99%	Мало памяти	Увеличить `shared_buffers` до 25% RAM
Много подключений	Нет connection pooler	Использовать PgBouncer
Бэкапы не помещаются	Нет ротации	Настроить `find -mtime +N -delete`

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

Официальная документация

Полезные расширения

pg_stat_statements — статистика запросов
pg_buffercache — анализ кэша буферов
pg_wait_sampling — детализация ожиданий
pg_squeeze — автоматическое сжатие старых данных
pg_partman — автоматическое партиционирование

Инструменты

PgBouncer — connection pooler
pgAdmin — GUI для администрирования
DBeaver — универсальный SQL-клиент
pg_top — мониторинг в терминале
Patroni — high availability

Готовы проверить знания? Переходите к вопросам! 🚀

Проверьте свои знания

Вопросы ещё не добавлены

Вопросы для этой подтемы ещё не добавлены.

Атрибут

Описание

LOGIN

Может подключаться к серверу

SUPERUSER

Полные права (обходить все проверки прав)

CREATEDB

Может создавать базы данных

CREATEROLE

Может создавать и изменять другие роли

INHERIT

Наследовать права от других ролей (по умолчанию включено)

REPLICATION

Может создавать репликационные подключения

BYPASSRLS

Игнорировать политики Row Level Security

NOLOGIN, NOSUPERUSER, и т.д.

Отрицание соответствующего атрибута

┌─────────────────────────────────────┐ │ SERVER (сервер) │ │ pg_hba.conf, pg_ident.conf │ └─────────────────────────────────────┘ ↓ ┌─────────────────────────────────────┐ │ DATABASE (база данных) │ │ CONNECT, CREATE, TEMPORARY │ └─────────────────────────────────────┘ ↓ ┌─────────────────────────────────────┐ │ SCHEMA (схема) │ │ USAGE, CREATE │ └─────────────────────────────────────┘ ↓ ┌─────────────────────────────────────┐ │ TABLE/VIEW (таблица/представление)│ │ SELECT, INSERT, UPDATE, DELETE │ │ TRUNCATE, REFERENCES, TRIGGER │ └─────────────────────────────────────┘ ↓ ┌─────────────────────────────────────┐ │ COLUMN (отдельный столбец) │ │ SELECT, INSERT, UPDATE │ └─────────────────────────────────────┘

Формат

Флаг

Описание

Plain SQL

-F p

Обычный SQL-файл, можно открыть в текстовом редакторе

Custom

-F c

Сжатый бинарный формат PostgreSQL (требует pg_restore)

Администрирование PostgreSQL

Администрирование PostgreSQL

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

В чём разница?

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

Атрибуты ролей

Просмотр существующих ролей

Изменение и удаление

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

Уровни привилегий

Предоставление прав (GRANT)

Отозвать права (REVOKE)

Права по умолчанию (ALTER DEFAULT PRIVILEGES)

Проверка текущих прав

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

Почему резервное копирование критически важно?

pg_dump — логическое резервное копирование

Форматы вывода

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

Дополнительные опции

pg_restore — восстановление из дампа

pg_dumpall — дамп всего кластера

Point-in-Time Recovery (PITR)

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

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

Стратегии резервного копирования

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

pg_stat_activity — текущая активность

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

Завершение проблемных сессий

pg_stat_statements — статистика запросов

Установка

Анализ запросов

Сброс статистики

pg_stat_user_tables — статистика по таблицам

Поиск таблиц, требующих обслуживания

pg_stat_indexes — статистика по индексам

Системные представления для мониторинга

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

Что такое Vacuum и зачем он нужен?

Типы Vacuum

Настройка Autovacuum

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

Рекомендуемые настройки для продакшена

Индивидуальные настройки для таблиц

Ручной Vacuum

Когда нужен VACUUM FULL?

Обслуживание индексов

Настройка аутентификации (pg_hba.conf)

Что такое pg_hba.conf?

Формат записи

Методы аутентификации

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

Применение изменений

Отладка подключений

Практические примеры и Best Practices

Шаблон ролевой модели для продакшена

Автоматизация резервного копирования

Мониторинг с Prometheus + Grafana

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

Распространённые проблемы и решения

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

Официальная документация

Полезные расширения

Инструменты

Проверьте свои знания

Администрирование PostgreSQL

Администрирование PostgreSQL

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

В чём разница?

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

Атрибуты ролей

Просмотр существующих ролей

Изменение и удаление

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

Уровни привилегий

Предоставление прав (GRANT)

Отозвать права (REVOKE)

Права по умолчанию (ALTER DEFAULT PRIVILEGES)

Проверка текущих прав