PostgreSQL настройка и оптимизация

Полное руководство по настройке и оптимизации PostgreSQL для максимальной производительности.

Базовая настройка

postgresql.conf

# Память
shared_buffers = 256MB                  # 25% от RAM
effective_cache_size = 1GB             # 75% от RAM
work_mem = 4MB                         # Память для сортировки
maintenance_work_mem = 64MB            # Память для VACUUM, CREATE INDEX

# Контрольные точки
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100

# Логирование
log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_min_duration_statement = 1000      # Логировать медленные запросы
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on

# Автовакуум
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50

pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Local connections
local   all             postgres                                peer
local   all             all                                     md5

# IPv4 local connections
host    all             all             127.0.0.1/32            md5
host    all             all             10.0.0.0/8              md5

# IPv6 local connections
host    all             all             ::1/128                 md5

# SSL connections
hostssl all             all             0.0.0.0/0               md5

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

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

-- Статистика по базам данных
SELECT
    datname,
    numbackends,
    xact_commit,
    xact_rollback,
    blks_read,
    blks_hit,
    temp_files,
    temp_bytes,
    deadlocks
FROM pg_stat_database;

-- Статистика по таблицам
SELECT
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch,
    n_tup_ins,
    n_tup_upd,
    n_tup_del
FROM pg_stat_user_tables;

-- Использование индексов
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes;

Анализ блокировок

-- Текущие блокировки
SELECT
    pl.pid,
    pa.usename,
    pa.application_name,
    pa.client_addr,
    pl.mode,
    pl.locktype,
    pl.relation::regclass,
    pa.query,
    pa.state,
    pa.query_start
FROM pg_locks pl
LEFT JOIN pg_stat_activity pa ON pl.pid = pa.pid
WHERE NOT pl.granted;

-- Ожидающие запросы
SELECT
    waiting.pid AS waiting_pid,
    waiting.query AS waiting_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query
FROM pg_stat_activity waiting
JOIN pg_locks waiting_locks ON waiting.pid = waiting_locks.pid
JOIN pg_locks blocking_locks ON waiting_locks.locktype = blocking_locks.locktype
    AND waiting_locks.DATABASE IS NOT DISTINCT FROM blocking_locks.DATABASE
    AND waiting_locks.relation IS NOT DISTINCT FROM blocking_locks.relation
    AND waiting_locks.page IS NOT DISTINCT FROM blocking_locks.page
    AND waiting_locks.tuple IS NOT DISTINCT FROM blocking_locks.tuple
    AND waiting_locks.virtualxid IS NOT DISTINCT FROM blocking_locks.virtualxid
    AND waiting_locks.transactionid IS NOT DISTINCT FROM blocking_locks.transactionid
    AND waiting_locks.classid IS NOT DISTINCT FROM blocking_locks.classid
    AND waiting_locks.objid IS NOT DISTINCT FROM blocking_locks.objid
    AND waiting_locks.objsubid IS NOT DISTINCT FROM blocking_locks.objsubid
    AND waiting_locks.pid != blocking_locks.pid
JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid
WHERE NOT waiting_locks.granted;

Оптимизация запросов

EXPLAIN ANALYZE

-- Анализ плана выполнения
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2023-01-01';

-- Анализ с дополнительными параметрами
EXPLAIN (
    ANALYZE true,
    VERBOSE true,
    COSTS true,
    BUFFERS true,
    TIMING true,
    SUMMARY true,
    FORMAT JSON
) SELECT * FROM large_table WHERE indexed_column = 'value';

Оптимизация индексов

-- Создание частичного индекса
CREATE INDEX idx_active_users ON users (email) WHERE active = true;

-- Составной индекс
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at);

-- Индекс по выражению
CREATE INDEX idx_users_lower_email ON users (lower(email));

-- GIN индекс для полнотекстового поиска
CREATE INDEX idx_posts_search ON posts USING gin(to_tsvector('russian', title || ' ' || content));

-- Анализ размера индексов
SELECT
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

Статистика и планировщик

-- Обновление статистики
ANALYZE;
ANALYZE table_name;

-- Настройка статистики для колонки
ALTER TABLE table_name ALTER COLUMN column_name SET STATISTICS 1000;

-- Проверка статистики
SELECT
    attname,
    n_distinct,
    most_common_vals,
    most_common_freqs,
    histogram_bounds
FROM pg_stats
WHERE tablename = 'your_table';

Партиционирование

Партиционирование по диапазону

-- Создание родительской таблицы
CREATE TABLE measurement (
    id SERIAL,
    city_id INT NOT NULL,
    logdate DATE NOT NULL,
    peaktemp INT,
    unitsales INT
) PARTITION BY RANGE (logdate);

-- Создание партиций
CREATE TABLE measurement_y2023m01 PARTITION OF measurement
    FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

CREATE TABLE measurement_y2023m02 PARTITION OF measurement
    FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');

-- Автоматическое создание партиций
CREATE EXTENSION pg_partman;

SELECT partman.create_parent(
    p_parent_table => 'public.measurement',
    p_control => 'logdate',
    p_type => 'range',
    p_interval => 'monthly'
);

Партиционирование по хешу

-- Создание хеш-партиций
CREATE TABLE orders (
    id SERIAL,
    customer_id INT,
    order_date DATE,
    amount DECIMAL
) PARTITION BY HASH (customer_id);

CREATE TABLE orders_p0 PARTITION OF orders FOR VALUES WITH (modulus 4, remainder 0);
CREATE TABLE orders_p1 PARTITION OF orders FOR VALUES WITH (modulus 4, remainder 1);
CREATE TABLE orders_p2 PARTITION OF orders FOR VALUES WITH (modulus 4, remainder 2);
CREATE TABLE orders_p3 PARTITION OF orders FOR VALUES WITH (modulus 4, remainder 3);

Репликация

Streaming Replication

-- На мастере
CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'password';

-- postgresql.conf (master)
wal_level = replica
max_wal_senders = 3
wal_keep_size = 64

-- pg_hba.conf (master)
host replication replicator slave_ip/32 md5

# На слейве
pg_basebackup -h master_ip -D /var/lib/postgresql/13/replica -U replicator -P -v -W

# standby.signal
touch /var/lib/postgresql/13/replica/standby.signal

# postgresql.conf (slave)
primary_conninfo = 'host=master_ip port=5432 user=replicator password=password'

Логическая репликация

-- На publisher
CREATE PUBLICATION my_publication FOR ALL TABLES;
CREATE PUBLICATION specific_tables FOR TABLE table1, table2;

-- На subscriber
CREATE SUBSCRIPTION my_subscription
    CONNECTION 'host=publisher_ip dbname=mydb user=repuser password=password'
    PUBLICATION my_publication;

-- Мониторинг репликации
SELECT * FROM pg_stat_replication;
SELECT * FROM pg_stat_subscription;

Резервное копирование

pg_dump

# Полный бэкап
pg_dump -h localhost -U postgres -d database_name > backup.sql

# Бэкап с сжатием
pg_dump -h localhost -U postgres -d database_name | gzip > backup.sql.gz

# Кастомный формат (рекомендуется)
pg_dump -h localhost -U postgres -Fc database_name > backup.custom

# Только схема
pg_dump -h localhost -U postgres -s database_name > schema.sql

# Только данные
pg_dump -h localhost -U postgres -a database_name > data.sql

# Восстановление
pg_restore -h localhost -U postgres -d database_name backup.custom

Автоматизация бэкапов

#!/bin/bash
# postgresql_backup.sh

PGUSER="postgres"
PGDB="your_database"
BACKUP_DIR="/var/backups/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=30

mkdir -p $BACKUP_DIR

# Создание бэкапа
pg_dump -h localhost -U $PGUSER -Fc $PGDB > $BACKUP_DIR/${PGDB}_${DATE}.custom

# Проверка успешности
if [ $? -eq 0 ]; then
    echo "Backup completed successfully: ${PGDB}_${DATE}.custom"

    # Удаление старых бэкапов
    find $BACKUP_DIR -name "${PGDB}_*.custom" -mtime +$RETENTION_DAYS -delete
else
    echo "Backup failed!"
    exit 1
fi

# Уведомление (опционально)
# echo "PostgreSQL backup completed" | mail -s "Backup Status" admin@example.com

Point-in-Time Recovery (PITR)

# Настройка архивирования WAL
# postgresql.conf
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'
wal_level = replica

# Базовый бэкап
pg_basebackup -D /var/backups/base -Ft -z -P

# Восстановление на определенный момент времени
# recovery.conf
restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2023-12-01 12:00:00'

Безопасность

Настройка SSL

# Генерация сертификатов
openssl req -new -x509 -days 365 -nodes -text -out server.crt \
    -keyout server.key -subj "/CN=dbserver.example.com"

chmod 400 server.key
chown postgres:postgres server.key server.crt

# postgresql.conf
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'
ssl_prefer_server_ciphers = on

Аудит и мониторинг

-- Включение логирования подключений
ALTER SYSTEM SET log_connections = 'on';
ALTER SYSTEM SET log_disconnections = 'on';
ALTER SYSTEM SET log_statement = 'all';

-- Создание роли только для чтения
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE mydb TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

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

Расширения

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

-- Полнотекстовый поиск
CREATE EXTENSION pg_trgm;
CREATE EXTENSION unaccent;

-- UUID генерация
CREATE EXTENSION "uuid-ossp";

-- Работа с JSON
CREATE EXTENSION jsonb_plperl;

-- Статистика
CREATE EXTENSION pg_stat_statements;

-- Партиционирование
CREATE EXTENSION pg_partman;

-- Соединения
CREATE EXTENSION postgres_fdw;

pg_stat_statements

-- Настройка в postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

-- Создание расширения
CREATE EXTENSION pg_stat_statements;

-- Анализ медленных запросов
SELECT
    query,
    calls,
    total_time,
    mean_time,
    rows,
    100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

-- Сброс статистики
SELECT pg_stat_statements_reset();

Мониторинг и алерты

Скрипт мониторинга

#!/bin/bash
# postgresql_monitor.sh

PGUSER="postgres"
PGDB="postgres"
LOG_FILE="/var/log/postgresql_monitor.log"

# Проверка подключений
CONNECTIONS=$(psql -h localhost -U $PGUSER -d $PGDB -t -c "SELECT count(*) FROM pg_stat_activity;")
MAX_CONNECTIONS=$(psql -h localhost -U $PGUSER -d $PGDB -t -c "SHOW max_connections;" | tr -d ' ')

CONNECTION_PERCENT=$(( $CONNECTIONS * 100 / $MAX_CONNECTIONS ))

if [ $CONNECTION_PERCENT -gt 80 ]; then
    echo "$(date): WARNING: High connection usage: $CONNECTION_PERCENT%" >> $LOG_FILE
fi

# Проверка размера базы данных
DB_SIZE=$(psql -h localhost -U $PGUSER -d $PGDB -t -c "SELECT pg_size_pretty(pg_database_size('$PGDB'));" | tr -d ' ')
echo "$(date): Database size: $DB_SIZE" >> $LOG_FILE

# Проверка репликации (если настроена)
REPLICATION_LAG=$(psql -h localhost -U $PGUSER -d $PGDB -t -c "SELECT extract(epoch from now() - pg_last_xact_replay_timestamp());" 2>/dev/null)

if [ ! -z "$REPLICATION_LAG" ] && [ $(echo "$REPLICATION_LAG > 60" | bc) -eq 1 ]; then
    echo "$(date): WARNING: Replication lag: ${REPLICATION_LAG}s" >> $LOG_FILE
fi

Prometheus мониторинг

# docker-compose.yml для postgres_exporter
version: '3.8'
services:
  postgres_exporter:
    image: prometheuscommunity/postgres-exporter
    environment:
      DATA_SOURCE_NAME: "postgresql://monitoring_user:password@postgres:5432/postgres?sslmode=disable"
    ports:
      - "9187:9187"
    depends_on:
      - postgres