Настройка базы PostgreSQL: от установки до оптимизации для продакшена

# Обновляем пакеты
sudo apt update

# Устанавливаем PostgreSQL и дополнительные утилиты
sudo apt install postgresql postgresql-contrib -y

# Проверяем статус службы
sudo systemctl status postgresql

# Включаем автозагрузку
sudo systemctl enable postgresql

version: '3.8'
services:
  postgres:
    image: postgres:15-alpine
    container_name: postgres_db
    environment:
      POSTGRES_USER: admin
      POSTGRES_PASSWORD: secure_password
      POSTGRES_DB: myapp_db
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./postgresql.conf:/etc/postgresql/postgresql.conf:ro
      - ./pg_hba.conf:/etc/postgresql/pg_hba.conf:ro
    command: postgres -c config_file=/etc/postgresql/postgresql.conf

volumes:
  postgres_data:

# Слушаем все интерфейсы (для продакшена ограничь конкретными IP)
listen_addresses = '*'  

# Порт по умолчанию
port = 5432

# Максимальное количество соединений
max_connections = 100

# Суперпользовательские резервные соединения
superuser_reserved_connections = 3

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# Локальные соединения через Unix-сокет
local   all             all                                     peer

# IPv4 локальные соединения (только для доверенных сетей)
host    all             all             127.0.0.1/32            md5

# IPv6 локальные соединения
host    all             all             ::1/128                 md5

# Доступ из внутренней сети приложения
host    myapp_db        app_user        10.0.0.0/8              md5

# Запрещаем все остальные подключения
host    all             all             0.0.0.0/0               reject

# Расчет для 16GB RAM
shared_buffers = 4GB           # 25% от 16GB
work_mem = 64MB                # (16GB-4GB) / (100*2) ≈ 64MB
maintenance_work_mem = 1GB     # 10% от 16GB, но ограничиваем 1GB
effective_cache_size = 12GB    # 75% от 16GB

# Режим надежности WAL
wal_level = replica            # Для репликации и point-in-time recovery

# Метод записи WAL
wal_sync_method = fdatasync    # Баланс производительности и надежности

# Размер WAL сегмента
wal_segment_size = 16MB        # По умолчанию с PostgreSQL 11+

# Количество WAL файлов для хранения
max_wal_size = 4GB             # Автоматическая проверка точки
min_wal_size = 1GB             # Минимальный размер для переиспользования

# Контрольные точки
checkpoint_timeout = 15min     # Максимальное время между контрольными точками
checkpoint_completion_target = 0.9  # Цель завершения контрольной точки

# Включаем параллельные запросы
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4

# Настройки планировщика
random_page_cost = 1.1         # Для SSD дисков (по умолчанию 4.0 для HDD)
effective_io_concurrency = 200 # Для SSD/NVMe

# Статистика
default_statistics_target = 100

-- Подключаемся как суперпользователь
sudo -u postgres psql

-- Создаем базу данных с правильной кодировкой
CREATE DATABASE myapp_db 
    ENCODING 'UTF8'
    LC_COLLATE 'en_US.UTF-8'
    LC_CTYPE 'en_US.UTF-8'
    TEMPLATE template0;

-- Создаем пользователя для приложения
CREATE USER app_user WITH PASSWORD 'strong_password_here';

-- Даем привилегии
GRANT CONNECT ON DATABASE myapp_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 ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- Настраиваем привилегии по умолчанию для будущих таблиц
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
    GRANT USAGE ON SEQUENCES TO app_user;

-- Создаем пользователя для бэкапов
CREATE USER backup_user WITH PASSWORD 'backup_password';
GRANT CONNECT ON DATABASE myapp_db TO backup_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user;

-- Активные соединения
SELECT 
    pid,
    usename,
    application_name,
    client_addr,
    state,
    query
FROM pg_stat_activity 
WHERE state = 'active';

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

-- Статистика по индексам
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan as index_scans
FROM pg_stat_user_indexes 
ORDER BY idx_scan DESC;

-- Проверка bloat (раздутие таблиц и индексов)
SELECT 
    schemaname,
    tablename,
    ROUND(100 * (n_dead_tup / (n_live_tup + n_dead_tup + 1)::float), 2) as dead_tup_percent
FROM pg_stat_user_tables 
WHERE (n_live_tup + n_dead_tup) > 10000 
ORDER BY dead_tup_percent DESC 
LIMIT 10;

-- Устанавливаем расширение
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Ежедневный VACUUM в непиковое время
SELECT cron.schedule('daily-vacuum', '0 2 * * *', 'VACUUM ANALYZE');

-- Еженедельный reindex критических таблиц
SELECT cron.schedule('weekly-reindex', '0 3 * * 0', 'REINDEX TABLE CONCURRENTLY large_table');

-- Ежечасная проверка статистики
SELECT cron.schedule('hourly-stats', '0 * * * *', 'ANALYZE');