Настройка и тонкости эксплуатации PostgreSQL для 1С: Полное руководство для DevOps

# Установка PostgreSQL 15 из репозитория EDB на Ubuntu/Debian
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
sudo apt-get update
sudo apt-get install -y postgresql-15 postgresql-15-postgis-3

# Основные настройки для сервера с 32GB RAM
# =========================================

# Память
shared_buffers = 8GB                 # 25% от RAM для 1С
effective_cache_size = 24GB          # 75% от RAM
work_mem = 16MB                      # Для сложных сортировок 1С
maintenance_work_mem = 1GB           # Для обслуживания

# Настройки WAL (журнал транзакций)
wal_level = replica
max_wal_size = 8GB                   # Зависит от нагрузки
min_wal_size = 2GB
wal_buffers = 16MB
wal_compression = on                 # Экономия места для 1С

# Настройки производительности
max_connections = 200                # Для 50-100 пользователей 1С
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4

# Автовакуум - критично для 1С!
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.02
autovacuum_max_workers = 4
autovacuum_naptime = 30s

# Настройки запросов
effective_io_concurrency = 200       # Для SSD
random_page_cost = 1.1               # Для SSD
seq_page_cost = 1

# Мониторинг и логирование
log_min_duration_statement = 1000    # Логировать медленные запросы >1s
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0                   # Логировать все временные файлы

# Типовые настройки для 1С-сервера
# ===============================

# Локальные подключения для администрирования
local   all             postgres                                peer
local   all             all                                     md5

# Подключение с сервера 1С
host    all             all             192.168.1.100/32        md5

# Подключение из подсети офиса
host    all             all             192.168.1.0/24          md5

# Replication для резервного сервера
host    replication     replicator      192.168.1.200/32        md5

-- Индекс для типовых фильтров по дате
CREATE INDEX IF NOT EXISTS idx_1c_date_filter 
ON _1SJOURN (DATE_TIME) 
WHERE _1SJOURN IS NOT NULL;

-- Частичный индекс для активных документов
CREATE INDEX IF NOT EXISTS idx_1c_active_docs 
ON _1SJOURN (DATE_TIME) 
WHERE CLOSED = 0 AND ISMARK = 0;

-- Индекс для поиска по ссылкам (частая операция в 1С)
CREATE INDEX IF NOT EXISTS idx_1c_reference 
ON _1SJOURN (REFERENCE) 
INCLUDE (DATE_TIME, DOCUMENTTYPE);

-- Подключаем необходимые расширения
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;  -- Для анализа запросов
CREATE EXTENSION IF NOT EXISTS pg_prewarm;          -- Для прогрева кэша
CREATE EXTENSION IF NOT EXISTS pg_buffercache;      -- Для анализа кэша

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

#!/bin/bash
# daily_postgres_check.sh
# Проверка состояния PostgreSQL для 1С

PGUSER="postgres"
DBNAME="your_1c_database"
LOGFILE="/var/log/postgresql/daily_check_$(date +%Y%m%d).log"

{
  echo "=== DAILY POSTGRES CHECK $(date) ==="
  echo ""
  
  # 1. Проверка подключения
  echo "1. Connection test:"
  psql -U "$PGUSER" -d "$DBNAME" -c "SELECT 1;" || echo "CONNECTION FAILED!"
  echo ""
  
  # 2. Самые медленные запросы за день
  echo "2. Top 10 slow queries:"
  psql -U "$PGUSER" -d "$DBNAME" -c "\n    SELECT query, calls, total_time, mean_time, rows \n    FROM pg_stat_statements \n    WHERE dbid = (SELECT oid FROM pg_database WHERE datname = '$DBNAME') \n    ORDER BY mean_time DESC LIMIT 10;"
  echo ""
  
  # 3. Размер БД и таблиц
  echo "3. Database size:"
  psql -U "$PGUSER" -d "$DBNAME" -c "\n    SELECT pg_size_pretty(pg_database_size('$DBNAME'));"
  echo ""
  
  # 4. Автовакуум статус
  echo "4. Autovacuum status:"
  psql -U "$PGUSER" -d "$DBNAME" -c "\n    SELECT schemaname, relname, \n           n_dead_tup, \n           last_autovacuum, \n           last_autoanalyze \n    FROM pg_stat_user_tables \n    WHERE n_dead_tup > 1000 \n    ORDER BY n_dead_tup DESC LIMIT 5;"
  
} > "$LOGFILE" 2>&1

# Отправка отчета на email (если настроено)
# mail -s "Daily PostgreSQL Check" admin@company.com < "$LOGFILE"

#!/bin/bash
# backup_postgres_1c.sh
# Полный физический бэкап с WAL архивированием

DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backup/postgres/full"
WAL_DIR="/backup/postgres/wal"
DBNAME="your_1c_database"
RETENTION_DAYS=14

# 1. Создание точки восстановления (для 1С важно!)
echo "Creating restore point for 1C..."
psql -U postgres -d "$DBNAME" -c "SELECT pg_create_restore_point('before_backup_${DATE}');"

# 2. Физический бэкап
echo "Starting physical backup..."
pg_basebackup -D "${BACKUP_DIR}/${DATE}" \
  -U postgres \
  -v \
  -P \
  -F tar \
  -z \
  -X stream \
  --wal-method=stream

# 3. Архивирование WAL
echo "Archiving WAL files..."
find "$WAL_DIR" -name "*.backup" -mtime +$RETENTION_DAYS -delete

# 4. Очистка старых бэкапов
find "$BACKUP_DIR" -type d -mtime +$RETENTION_DAYS -exec rm -rf {} \;

echo "Backup completed: ${BACKUP_DIR}/${DATE}"

-- 1. Найти самые медленные запросы
SELECT query, calls, total_time, mean_time, rows 
FROM pg_stat_statements 
WHERE mean_time > 1000  -- > 1 секунда
ORDER BY mean_time DESC 
LIMIT 20;

-- 2. Проверить наличие индексов для медленных запросов
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM _1SJOURN 
WHERE DATE_TIME BETWEEN '2024-01-01' AND '2024-12-31' 
AND CLOSED = 0;

-- 3. Создать недостающие индексы
CREATE INDEX CONCURRENTLY idx_1c_journal_date_closed 
ON _1SJOURN (DATE_TIME) 
WHERE CLOSED = 0;

-- Проверка уровня раздувания
SELECT schemaname, tablename,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) as total_size,
  pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) as table_size,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename) - pg_relation_size(schemaname || '.' || tablename)) as bloat_size,
  n_dead_tup
FROM pg_stat_user_tables
WHERE pg_total_relation_size(schemaname || '.' || tablename) > 100000000  -- > 100MB
ORDER BY bloat_size DESC
LIMIT 10;

-- Принудительный VACUUM для самых раздутых таблиц
VACUUM (VERBOSE, ANALYZE) _1SJOURN;
VACUUM (VERBOSE, ANALYZE) _1SJOURN;

-- Для критического раздувания (только в maintenance window!)
VACUUM FULL VERBOSE ANALYZE _1SJOURN;

-- Текущие подключения
SELECT count(*) as total_connections,
       count(*) filter (WHERE state = 'active') as active,
       count(*) filter (WHERE state = 'idle') as idle,
       count(*) filter (WHERE state = 'idle in transaction') as idle_in_transaction
FROM pg_stat_activity
WHERE datname = 'your_1c_database';

-- Кто подключен и что делает
SELECT pid, usename, application_name, client_addr, 
       state, query_start, query 
FROM pg_stat_activity 
WHERE datname = 'your_1c_database' 
AND state = 'active'
ORDER BY query_start;

-- Убить зависшие подключения (осторожно!)
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = 'your_1c_database' 
AND state = 'idle' 
AND (now() - state_change) > interval '1 hour';