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

-- Применить изменения без рестарта
SELECT pg_reload_conf();

-- Проверить текущее значение
SHOW shared_buffers;
SHOW work_mem;

-- Временно для текущей сессии
SET work_mem = '512MB';

-- Пользователь для приложения (минимальные права)
CREATE ROLE app_user LOGIN PASSWORD 'strong_password';

-- Групповая роль для аналитиков
CREATE ROLE analytics NOLOGIN;

-- Назначить групповую роль пользователю
GRANT analytics TO data_engineer;

-- Отозвать все права по умолчанию
REVOKE ALL ON DATABASE etl_db FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;

-- Приложению — только CRUD
GRANT CONNECT ON DATABASE etl_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;

-- Аналитикам — только чтение
GRANT CONNECT ON DATABASE etl_db TO analytics;
GRANT USAGE ON SCHEMA public TO analytics;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analytics;

-- Для будущих таблиц
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO analytics;

-- Кто имеет доступ к таблице?
SELECT grantee, privilege_type
FROM information_schema.table_privileges
WHERE table_name = 'customers';

-- Все роли и их атрибуты
SELECT rolname, rolsuper, rolcreatedb, rolcanlogin
FROM pg_roles
ORDER BY rolname;

-- Обычный VACUUM — освобождает место для переиспользования (не уменьшает файл)
VACUUM orders;

-- VACUUM ANALYZE — VACUUM + обновление статистики для планировщика
VACUUM ANALYZE orders;

-- VACUUM FULL — полная перезапись таблицы (уменьшает файл, но блокирует таблицу!)
VACUUM FULL orders;

-- Текущие autovacuum-процессы
SELECT pid, datname, relid::regclass, phase, heap_blks_scanned, heap_blks_total
FROM pg_stat_progress_vacuum;

-- Когда был последний vacuum?
SELECT relname, last_vacuum, last_autovacuum, n_dead_tup, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- Настройка autovacuum для конкретной таблицы
ALTER TABLE events SET (
    autovacuum_vacuum_threshold = 1000,
    autovacuum_vacuum_scale_factor = 0.05,  -- запускать при 5% мёртвых (дефолт 20%)
    autovacuum_analyze_threshold = 500
);

-- Проверить возраст XID
SELECT datname, age(datfrozenxid), datfrozenxid
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

-- Опасно если age > 200_000_000
-- Критично если age > 1_000_000_000

-- Активные запросы
SELECT pid, usename, state, query_start,
       now() - query_start AS duration,
       LEFT(query, 80) AS query
FROM pg_stat_activity
WHERE state = 'active' AND pid != pg_backend_pid()
ORDER BY duration DESC;

-- Размер таблиц и индексов
SELECT relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total,
       pg_size_pretty(pg_relation_size(relid)) AS table_only,
       pg_size_pretty(pg_indexes_size(relid)) AS indexes
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

-- Кеш-хит rate (должен быть > 99%)
SELECT datname,
       blks_hit::float / NULLIF(blks_hit + blks_read, 0) * 100 AS cache_hit_pct
FROM pg_stat_database
WHERE datname = current_database();

-- Включить
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements'

-- Самые медленные запросы
SELECT LEFT(query, 80) AS query,
       calls,
       round(total_exec_time::numeric / 1000, 2) AS total_sec,
       round(mean_exec_time::numeric, 2) AS avg_ms,
       rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Текущие блокировки
SELECT blocked.pid AS blocked_pid,
       blocked.query AS blocked_query,
       blocking.pid AS blocking_pid,
       blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid
JOIN pg_locks lock ON lock.locktype = bl.locktype
     AND lock.relation = bl.relation
     AND lock.pid != bl.pid
JOIN pg_stat_activity blocking ON blocking.pid = lock.pid
WHERE NOT bl.granted;

-- Подключиться к PgBouncer admin
-- psql -p 6432 pgbouncer

SHOW POOLS;    -- состояние пулов
SHOW CLIENTS;  -- клиентские подключения
SHOW STATS;    -- статистика