Первоначальная настройка PostgreSQL: Полное руководство для разработчиков

# Для инициализации кластера с указанием локали
sudo -u postgres initdb --locale=ru_RU.UTF-8 -D /var/lib/postgresql/data

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

# Запуск PostgreSQL
sudo systemctl start postgresql
sudo systemctl enable postgresql

# Слушаем все интерфейсы (или только localhost для разработки)
listen_addresses = 'localhost'          # для продакшена: '*' или конкретный IP
port = 5432                            # стандартный порт PostgreSQL

# Настройки аутентификации
password_encryption = scram-sha-256    # современный метод шифрования паролей

# Настройки памяти
shared_buffers = 128MB                 # 25% от доступной RAM для начала
work_mem = 4MB                         # память для операций сортировки
maintenance_work_mem = 64MB            # память для операций обслуживания

# Настройки журналирования (WAL)
wal_level = replica                    # для репликации и point-in-time recovery
max_wal_size = 1GB
min_wal_size = 80MB

# Настройки производительности
max_connections = 100                  # адаптируй под свои нужды
random_page_cost = 1.1                 # для SSD дисков

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Локальные подключения через Unix-сокет
local   all             all                                     peer

# IPv4 локальные подключения
host    all             all             127.0.0.1/32            scram-sha-256

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

# Разрешить подключение из определенной сети
# host    all             all             192.168.1.0/24          scram-sha-256

# Пример для конкретного пользователя и базы
# host    mydb           app_user        10.0.0.0/8              scram-sha-256

-- Подключаемся к PostgreSQL
sudo -u postgres psql

-- Создаем нового пользователя (роль) с паролем
CREATE ROLE app_user WITH LOGIN PASSWORD 'StrongPassword123!';

-- Создаем базу данных
CREATE DATABASE myapp_db;

-- Даем пользователю права на базу данных
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO app_user;

-- Создаем тестовую таблицу для проверки
\c myapp_db
CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Проверяем подключение от имени нового пользователя
\q
psql -U app_user -d myapp_db -h localhost

-- Активируем расширение pg_stat_statements для анализа запросов
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Добавляем в postgresql.conf:
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000

-- Перезагружаем конфигурацию
SELECT pg_reload_conf();

-- Просмотр статистики запросов
SELECT query, calls, total_time, mean_time 
FROM pg_stat_statements 
ORDER BY total_time DESC 
LIMIT 10;

-- Включаем autovacuum (должен быть включен по умолчанию)
-- В postgresql.conf убедись, что:
autovacuum = on
log_autovacuum_min_duration = 0  # логируем все операции autovacuum

-- Настраиваем более агрессивный autovacuum для часто изменяемых таблиц
ALTER TABLE large_table SET (
    autovacuum_vacuum_scale_factor = 0.05,
    autovacuum_analyze_scale_factor = 0.02
);

-- Проверка текущих настроек
SELECT name, setting, unit, context 
FROM pg_settings 
WHERE name IN ('shared_buffers', 'work_mem', 'effective_cache_size');

-- Расчет рекомендуемых значений (пример для 8GB RAM)
/*
shared_buffers = 2GB          # 25% от 8GB
effective_cache_size = 6GB    # 75% от 8GB
work_mem = 16MB               # (8GB / 100 коннектов) / 2
maintenance_work_mem = 512MB  # ~6% от 8GB
*/

# Простое резервное копирование с помощью pg_dump
pg_dump -U postgres -Fc myapp_db > myapp_backup.dump

# Резервное копирование всех баз данных
pg_dumpall -U postgres > all_databases.sql

# Восстановление из backup
pg_restore -U postgres -d myapp_db myapp_backup.dump

# Создание cron-задания для ежедневного бэкапа
# Добавь в crontab -e:
0 2 * * * pg_dump -U postgres -Fc myapp_db > /backups/myapp_$(date +%Y%m%d).dump