PostgreSQL 1C Database Health Check - Complete monitoring SQL script

postgres_health_checklist.sql

-- checklist_1c_fixed.sql
WITH checks AS (
    -- 1. Проверка распухания таблиц (BLOAT)
    SELECT 
        'BLOAT' as category,
        'Распухание таблиц (мёртвые строки > 20%)' as check_name,
        COUNT(*) as problems_count,
        ARRAY_AGG(n.nspname || '.' || s.relname) as problem_items,
        CASE WHEN COUNT(*) > 0 THEN 'ЕСТЬ ПРОБЛЕМЫ' ELSE 'OK' END as status,
        'VACUUM/автоочистка' as solution
    FROM pg_stat_user_tables s
    JOIN pg_class c ON c.oid = s.relid
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE s.n_dead_tup > 1000 -- хотя бы 1000 мертвых строк
        AND s.n_live_tup > 0
        AND s.n_dead_tup::numeric / (s.n_live_tup + s.n_dead_tup) > 0.2 -- >20%
    
    UNION ALL
    
    -- 2. Проверка распухания индексов (индексы > таблицы)
    SELECT 
        'INDEX_BLOAT' as category,
        'Индексы больше таблиц (>150%)' as check_name,
        COUNT(*) as problems_count,
        ARRAY_AGG(nspname || '.' || relname) as problem_items,
        CASE WHEN COUNT(*) > 0 THEN 'ЕСТЬ ПРОБЛЕМЫ' ELSE 'OK' END as status,
        'REINDEX/pg_repack' as solution
    FROM (
        SELECT 
            n.nspname,
            c.relname,
            pg_relation_size(c.oid) as table_size,
            pg_indexes_size(c.oid) as index_size
        FROM pg_class c
        JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relkind = 'r' -- только обычные таблицы
            AND n.nspname NOT IN ('pg_catalog', 'information_schema')
            AND pg_relation_size(c.oid) > 10 * 1024 * 1024 -- таблицы >10MB
    ) t
    WHERE index_size::numeric / NULLIF(table_size, 0) > 1.5 -- индексы >150% от таблицы
    
    UNION ALL
    
    -- 3. Проверка WAL накопления
    SELECT 
        'WAL' as category,
        'Слишком много WAL файлов (>64)' as check_name,
        CASE 
            WHEN (SELECT COUNT(*) FROM pg_ls_waldir() WHERE name ~ '^[0-9A-F]{24}') > 64 
            THEN 1 
            ELSE 0 
        END as problems_count,
        ARRAY[(
            SELECT 'WAL файлов: ' || COUNT(*)::text || ', размер: ' || 
                   COALESCE(pg_size_pretty(SUM(size)), '0 bytes')
            FROM pg_ls_waldir()
            WHERE name ~ '^[0-9A-F]{24}'
        )] as problem_items,
        CASE 
            WHEN (SELECT COUNT(*) FROM pg_ls_waldir() WHERE name ~ '^[0-9A-F]{24}') > 64 
            THEN 'ЕСТЬ ПРОБЛЕМЫ' 
            ELSE 'OK' 
        END as status,
        'CHECKPOINT/настройка wal_keep_size' as solution
    
    UNION ALL
    
    -- 4. Проверка слотов репликации
    SELECT 
        'REPLICATION' as category,
        'Неактивные слоты репликации' as check_name,
        COUNT(*) as problems_count,
        ARRAY_AGG(slot_name || ' (lag: ' || 
                  COALESCE(pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)), '0 bytes') || ')') as problem_items,
        CASE WHEN COUNT(*) > 0 THEN 'ЕСТЬ ПРОБЛЕМЫ' ELSE 'OK' END as status,
        'Удаление неиспользуемых слотов' as solution
    FROM pg_replication_slots
    WHERE active = false
    
    UNION ALL
    
    -- 5. Проверка настроек автоочистки
    SELECT 
        'AUTOVACUUM' as category,
        'Ненастроенная автоочистка' as check_name,
        CASE 
            WHEN (SELECT setting FROM pg_settings WHERE name = 'autovacuum') = 'off' THEN 1
            WHEN (SELECT setting FROM pg_settings WHERE name = 'autovacuum_max_workers')::int < 2 THEN 1
            ELSE 0 
        END as problems_count,
        ARRAY[
            'autovacuum: ' || (SELECT setting FROM pg_settings WHERE name = 'autovacuum'),
            'autovacuum_max_workers: ' || (SELECT setting FROM pg_settings WHERE name = 'autovacuum_max_workers'),
            'autovacuum_vacuum_scale_factor: ' || (SELECT setting FROM pg_settings WHERE name = 'autovacuum_vacuum_scale_factor')
        ] as problem_items,
        CASE 
            WHEN (SELECT setting FROM pg_settings WHERE name = 'autovacuum') = 'off' 
                OR (SELECT setting FROM pg_settings WHERE name = 'autovacuum_max_workers')::int < 2 
            THEN 'ТРЕБУЕТ НАСТРОЙКИ' 
            ELSE 'OK' 
        END as status,
        'Настройка параметров autovacuum' as solution
    
    UNION ALL
    
    -- 6. Проверка долгих транзакций (>1 часа)
    SELECT 
        'LONG_TRANSACTIONS' as category,
        'Долгие транзакции (>1 часа)' as check_name,
        COUNT(*) as problems_count,
        ARRAY_AGG(
            'PID: ' || pid || ', длительность: ' || AGE(now(), xact_start) || 
            ', запрос: ' || COALESCE(SUBSTRING(query FROM 1 FOR 50), 'NULL') || '...'
        ) as problem_items,
        CASE WHEN COUNT(*) > 0 THEN 'ЕСТЬ ПРОБЛЕМЫ' ELSE 'OK' END as status,
        'Завершение/откат транзакций' as solution
    FROM pg_stat_activity
    WHERE state IN ('idle in transaction', 'active')
        AND pid <> pg_backend_pid()
        AND xact_start IS NOT NULL
        AND now() - xact_start > interval '1 hour'
    
    UNION ALL
    
    -- 7. Проверка забитой памяти (work_mem)
    SELECT 
        'MEMORY' as category,
        'Неоптимальные настройки памяти' as check_name,
        CASE 
            WHEN (SELECT setting FROM pg_settings WHERE name = 'work_mem')::int < 4096 THEN 1
            WHEN (SELECT setting FROM pg_settings WHERE name = 'maintenance_work_mem')::int < 65536 THEN 1
            ELSE 0 
        END as problems_count,
        ARRAY[
            'work_mem: ' || (SELECT setting FROM pg_settings WHERE name = 'work_mem') || ' kB',
            'maintenance_work_mem: ' || (SELECT setting FROM pg_settings WHERE name = 'maintenance_work_mem') || ' kB',
            'shared_buffers: ' || (SELECT setting FROM pg_settings WHERE name = 'shared_buffers')
        ] as problem_items,
        CASE 
            WHEN (SELECT setting FROM pg_settings WHERE name = 'work_mem')::int < 4096 
                OR (SELECT setting FROM pg_settings WHERE name = 'maintenance_work_mem')::int < 65536 
            THEN 'ТРЕБУЕТ НАСТРОЙКИ' 
            ELSE 'OK' 
        END as status,
        'Увеличение work_mem/maintenance_work_mem' as solution
    
    UNION ALL
    
    -- 8. Проверка больших таблиц без автоочистки
    SELECT 
        'NO_AUTOVACUUM' as category,
        'Большие таблицы без автоочистки' as check_name,
        COUNT(*) as problems_count,
        ARRAY_AGG(n.nspname || '.' || c.relname || ' (' || pg_size_pretty(pg_relation_size(c.oid)) || ')') as problem_items,
        CASE WHEN COUNT(*) > 0 THEN 'ЕСТЬ ПРОБЛЕМЫ' ELSE 'OK' END as status,
        'ALTER TABLE ... SET (autovacuum_enabled = true)' as solution
    FROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relkind = 'r'
        AND n.nspname NOT IN ('pg_catalog', 'information_schema')
        AND c.reloptions IS NOT NULL
        AND NOT c.reloptions @> ARRAY['autovacuum_enabled=true']
        AND pg_relation_size(c.oid) > 100 * 1024 * 1024 -- >100MB
)
SELECT 
    category as "Категория",
    check_name as "Проверка",
    problems_count as "Кол-во проблем",
    CASE 
        WHEN problems_count = 0 THEN ARRAY['-']
        WHEN array_length(problem_items, 1) > 3 THEN 
            problem_items[1:3] || ARRAY['... и еще ' || (problems_count - 3) || ' объектов']
        ELSE problem_items 
    END as "Проблемные объекты",
    status as "Статус",
    solution as "Решение"
FROM checks
ORDER BY 
    CASE status 
        WHEN 'ЕСТЬ ПРОБЛЕМЫ' THEN 1 
        WHEN 'ТРЕБУЕТ НАСТРОЙКИ' THEN 2 
        ELSE 3 
    END,
    category;