Я обновил и актуализировал популярную на сайте статью по настройке сервера 1С:

Установка и настройка 1С на Debian с PostgreSQL
⇨ https://serveradmin.ru/ustanovka-i-nastrojka-1s-na-debian-s-postgresql/

Статья подробная. Позволяет простым копированием и ставкой настроить указанную связку. В ней показаны:

✅ Установка и настройка свежей версии сервера 1С и PosgtreSQL 15 на Debian 11.
✅ Пример создания баз данных на этом сервере и подключение к ним.
✅ Бэкап и обслуживание postgresql баз утилитами сервера бд. Рассказываю про свой подход к этому процессу и привожу скрипты автоматизации. 
✅ Как я тестирую восстановление из sql дампов и делаю контрольную проверку в виде выгрузки баз в .dt файлы в консоли Linux. Всё это автоматизируется скриптами. 
✅ Рассказываю про свои подходы к мониторингу этих бэкапов и выгрузок, чтобы всегда быть уверенным в том, что у тебя есть гарантированно рабочие бэкапы.

Всё описанное основано на личном опыте настройки и эксплуатации подобных систем. Ни у кого ничего не смотрел, лучших практик не знаю. Придумал всё сам и сделал, как посчитал нужным. Так что не нужно это воспринимать как оптимальное решение.

#1с #postgresql

​​Очень простой в установке и использовании инструмент для анализа работы СУБД Postgres — pgCenter. Это open source разработка нашего соотечественника Алексея Лесовского, который сейчас трудится в PostgresPro. Он давно известен своим проектом zabbix-extensions (проект давно заброшен и неактуален), где собрана куча скриптов для мониторинга всего и вся в Linux.

PgCenter — набор утилит командной строки, которые позволяют анализировать состояние СУБД в режиме реального времени, наподобие программы top и других подобных инструментов.

PgCenter пригодится как обычным админам, чтобы быстро оценить состояние СУБД (использование ресурсов, просмотр ошибок и т.д.), так и DBA для детального изучения (встроенная статистика) и решения проблем (долгие транзакции, конфликты репликации и т.д.).

В репозитории автоматически собираются rpm и deb пакеты для локальной установки, а также Docker образы. Попробовать проще всего вот так:
# docker run -it --rm lesovsky/pgcenter:latest pgcenter top \
-h 1.2.3.4 -U user -d dbname

У автора есть ещё несколько полезных утилит. Скорее всего напишу о них отдельно:
◽Noisia — генератор нагрузки PostgreSQL.
◽pgSCV — экспортёр метрик для Prometheus и Victoriametrics.
◽pgstats.dev — веб проект с описанием метрик Postgres для мониторинга.

⇨ Исходники

#postgresql

​​Если хотите посмотреть, попробовать, изучить работу кластера PostgreSQL, можно воспользоваться готовым плейбуком ansible — postgresql_cluster. Это production ready решение, которое просто и легко устанавливается. У меня получилось базовую версию без балансировки на haproxy и consul развернуть сходу.

Всё описание есть в репозитории. Если нет опыта в этом хозяйстве, то развернуть лучше Type B: один мастер и две реплики. Это будет обычный HA кластер на базе Patroni. Если не ошибаюсь, на текущий момент это самое популярное решение для построения кластеров PostgreSQL.

Для установки желательно знать Ansible, но в целом можно и копипастом развернуть, но без понимания основ скорее всего не получится по инструкции правильно заполнить инвентарь и переменные. Это как раз пример того, почему важно знать Ansible, даже если у вас под управлением нет десятков хостов, для которых будет актуальна автоматизация процессов, и вы сами не пишите плейбуки. Очень много готовых продуктов устанавливаются через Ansible.

Patroni известный продукт, по которому много статей и руководств в интернете. Как пример, вот мастер-класс по нему от самих авторов. Предложенный репозиторий тоже распространён. Можно нагуглить статьи с его участием. Например, вот тут тестируют отставание реплики на кластере, развёрнутом из этого репозитория.

После установки статус кластера смотрите командой:
# patronictl -c /etc/patroni/patroni.yml list

Можете отключать ноды и смотреть, как кластер будет на это реагировать.

#postgresql

​​Когда настраиваете сервер или кластер PostgreSQL, перед тем, как нагрузить его рабочей нагрузкой, хочется как-то протестировать его и посмотреть, как он себя поведёт под нагрузкой или в случае каких-то ошибок. Особенно это актуально, если у вас ещё и мониторинг настроен. Хочется на деле посмотреть, как он отработает.

Сделать подобное тестирование с имитацией рабочей нагрузки и ошибок можно с помощью утилиты Noisia. Она написала на Gо, есть в виде бинарника или rpm, deb пакета. Забрать можно из репозитория. Либо запустить через Docker.

Автор утилиты подробно рассказывает про принцип работы и функциональность на онлайн вебинаре — Noisia - генератор аварийных и нештатных ситуаций в PostgreSQL (текстовая расшифровка). Там описаны основные возможности и параметры для использования.

Вот простой пример исчерпания всех доступных подключений к базе, которые обязательно должны мониториться, чтобы своевременно увеличить лимит подключений, или разобраться, куда они все ушли.

# docker run --rm -ti lesovsky/noisia:latest noisia \
--conninfo="postgres://postgres:pass@10.20.1.56:5432/dbname" \
--failconns

Не забудьте настроить удалённые подключения к базе, либо запускайте нагрузку локально, не через docker.

Запуск транзакций, которые завершаются откатом (ROLLBACK), то есть заканчиваются с ошибкой. За ними обычно тоже следят (pg_stat_database.xact_rollback).

# docker run --rm -ti lesovsky/noisia:latest noisia \
--conninfo="postgres://postgres:pass@10.20.1.56:5432/dbname" \
--rollbacks \
--rollbacks.min-rate=10 \
--jobs=3 \
--duration=20

Ну и так далее. Все параметры можно посмотреть в help:

# docker run --rm -ti lesovsky/noisia:latest noisia --help

#postgresql

​​Пока занимался с PostgreSQL, вспомнил про простой и быстрый способ посмотреть статистику по запросам, который я использовал очень давно. Ещё во времена, когда не пользовался централизованными системами по сбору и анализу логов. Проверил методику, на удивление всё работает до сих пор, так что расскажу вам.

Речь пойдёт про анализатор запросов pgFouine. Продукт старый, последняя версия от 2010-го года. Обновление есть только для совместимости с версией php 7. Сам анализатор - это одиночный скрипт на php, который на входе берёт лог с запросами, а на выходе формирует одну html страницу со статистикой, которая включает в себя:

◽общую статистику по запросам, в том числе по их типам
◽запросы, которые занимают больше всего времени СУБД
◽топ медленных запросов
◽счётчик повторяющихся запросов

Для того, чтобы включить сбор логов, в конфигурационный файл PostgreSQL нужно добавить следующие параметры:

log_destination = 'syslog'
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'
log_min_duration_statement = 3000 # 3000 мс = 3 секунды
log_duration = off
log_statement = 'none'

Это мы собираем только медленные запросы, дольше трех секунд. Если указать log_min_duration_statement = 0, будут логироваться все запросы. Логи будут писаться в syslog. Имеет смысл поместить их в отдельный файл. Для этого добавляем в конфигурационный файл rsyslog:

LOCAL0.*                -/var/log/postgresql/sql.log

Имеет смысл также отключить запись этих логов в общий лог, добавив LOCAL0.none:

*.*;auth,authpriv.none;LOCAL0.none              -/var/log/syslog
*.=info;*.=notice;*.=warn;\
auth,authpriv.none;\
cron,daemon.none;\
mail,news.none;\
LOCAL0.none -/var/log/messages

Перезапускаем сервисы:
# systemctl restart postgresql
# systemctl restart rsyslog

Ждём, когда заполнится лог и отправляем его в pgFouine. Для этого достаточно скопировать себе файл pgfouine.php или весь репозиторий:

# git clone https://github.com/milo/pgFouine
# cd pgFouine
# php pgfouine.php -file /var/log/postgresql/sql.log > report.html

Теперь файл report.html можно открыть в браузере и посмотреть статистику. Предварительно нужно установить php, либо передать лог с запросами на машину, где php установлен. У меня нормально отработал на версии php 7.4.

Такой вот олдскул. Сейчас не знаю, кто так статистику смотрит. Есть парсеры логов для ELK или Graylog. Но для этого у вас должны быть эти системы. Надо туда отправить логи, распарсить, собрать дашборды. Это пуд соли съесть. А подобный разовый анализ можно сделать за 10 минут.

#postgresql

​​Существует удобный веб интерфейс для управления и мониторинга СУБД Postgresql - temBoard. Он по смыслу напоминает специализированный мониторинг Percona Monitoring and Management (PMM). Но почему-то не очень популярен, хотя написан людьми, которые контрибьютят в PostgreSQL. Есть новость на сайте postgresql от них с анонсом очередного релиза.

У меня есть несколько одиночных серверов PostgreSQL для работы с 1С, так что я решил проверить работу сразу с ними. В сети нет готовых инструкций по настройке temBoard, а с учётом того, что сборка PostgreSQL для 1С не совсем типовая, пришлось немного повозиться с настройкой. В итоге всё получилось.

С помощью temBoard можно:
◽подключить множество экземпляров СУБД в единую веб панель;
◽смотреть основные метрики мониторинга серверов;
◽управлять активными сеансами пользователей;
◽запускать операции vacuum, reindex, analyze;
◽отслеживать запросы к СУБД;
◽выполнять некоторые настройки СУБД.

С учётом перечисленных возможностей понятно, что у панели есть почти полный доступ к СУБД, так что использовать её надо аккуратно. Это может быть точкой отказа или утечки данных.

Для установки temBoard необходимо установить веб интерфейс и настроить хранение данных в одной из баз данных PostgreSQL, существующей или отдельной. На хосты с СУБД ставится небольшой агент. Для temBoard есть репозитории разработчиков, так что установка не представляет особых сложностей, но есть нюансы.

Я развернул всё на Debian 11. В моём случае наблюдаемый сервер PostgreSQL будет стоять на этом же хосте. Нужно убедиться, что все хосты имеют FQDN имена. Без них ничего не заработает, так как будут выпущены сертификаты. А скрипты генерации сертификатов ожидают FQDN имена, без них будут ошибки.

Для установки использовал официальную инструкцию. Ставим утилиты, которые пригодятся:
# apt install gnupg curl sudo
Подключаем репозиторий и устанавливаем temBoard:
# echo deb http://apt.dalibo.org/labs $(lsb_release -cs)-dalibo main \
> /etc/apt/sources.list.d/dalibo-labs.list
# curl https://apt.dalibo.org/labs/debian-dalibo.asc | apt-key add -
# apt update
# apt install temboard

Теперь нужно запустить скрипт настройки. Он берёт все значения PostgreSQL по умолчанию. Если используете сборку от Postgrespro для 1С, то сокет для подключения она открывает в /tmp, а не в /var/run. Нужно это передать скрипту. Сразу покажу и переменную для tcp порта postgresql, если у вас используется нестандартный.
# PGPORT=5432 PGHOST=/tmp /usr/share/temboard/auto_configure.sh
Скрипт генерирует сертификаты, конфиги, службу systemd, создаёт себе базу данных в СУБД и что-то ещё, соответственно, в pg_hba.conf нужно настроить доступ для юзера postgres. После того, как скрипт отработает, запускаем службу:
# systemctl enable --now temboard

Если всё прошло без ошибок, то можно открывать веб интерфейс https://temboard.local:8888, учётка - admin / admin. Там будет пусто, так как нет ни одного подключенного хоста.

Теперь нужно установить агента. Если это одна и та же машина, то пакет ставится из того же репозитория. Если хост другой, то подключите туда репозиторий:
# apt install temboard-agent

Запускаем скрипт для конфигурирования агента:
# /usr/share/temboard-agent/auto_configure.sh https://temboard.local:8888
Тут он у меня постоянно вываливался с неинформативной ошибкой. Анализируя скрипт понял, что идёт проверка доступности ключа /etc/ssl/private/ssl-cert-snakeoil.key пользователем postgresql. Проверить так:
# sudo -u postgres cat /etc/ssl/private/ssl-cert-snakeoil.key
Если доступа нет, настройте. После этого всё получится. Далее забираем ключ с сервера, запускаем агента и регистрируемся на сервере:
# sudo -u postgres temboard-agent -c \
/etc/temboard-agent/15/pg5432/temboard-agent.conf fetch-key
# systemctl enable --now temboard-agent@15-pg5432
# sudo -u postgres temboard-agent -c \
/etc/temboard-agent/15/pg5432/temboard-agent.conf register --groups default

Идём в веб интерфейс и наблюдаем там свой хост.

⇨ Сайт / Исходники

#монитроинг #postgresql

​​Для бэкапа баз PostgreSQL существует много различных подходов и решений. Я вам хочу предложить ещё одно, отметив его особенности и преимущества. Да и в целом это одна из самых известных программ для этих целей. А в конце приведу список того, чем ещё можно бэкапить PostgreSQL.

Сейчас речь пойдёт об open source продукте pgBackRest. Сразу перечислю основные возможности:
◽умеет бэкапить как локально, так и удалённо, подключаясь по SSH
◽умеет параллелить свою работу и сжимать на ходу с помощью lz4 и zstd, что обеспечивает максимальное быстродействие
◽умеет полные, инкрементные, разностные бэкапы
◽поддерживает локальное и удалённое (в том числе S3) размещение архивов с разными политиками хранения
◽умеет проверять консистентность данных
◽может докачивать бэкапы на том месте, где остановился, а не начинать заново при разрывах связи

Несмотря на то, что продукт довольно старый (написан на C и Perl), он активно поддерживается и обновляется. Плохо только то, что в репозитории нет ни бинарников, ни пакетов. Только исходники, которые предлагается собрать самостоятельно. В целом, это не проблема, так как в Debian и Ubuntu есть уже собранные пакеты в репозиториях, но не самых свежих версий. Свежие придётся самим собирать.

# apt install pgbackrest

Дальше настройка стандартная для подобных приложений. Рисуете конфиг, где описываете хранилища, указываете объекты для бэкапа, параметры бэкапа и куда складывать логи. Они информативные, можно анализировать при желании.

Подробное описание работы pgBackRest, а так же подходы к созданию резервных копий PostgreSQL и их проверке подробно описаны в ▶️ выступлении Дэвид Стили на PGConf.Online.

❓Чем ещё можно бэкапить PostgreSQL?

🔹pg_dump - встроенная утилита для создания логической копии базы. Подходит только для небольших малонагруженных баз

🔹pg_basebackup - встроенная утилита для создания бинарных бэкапов на уровне файлов всего сервера или кластера. Нельзя делать выборочный бэкап отдельных баз или таблиц.

🔹Barman - наиболее известный продукт для бэкапа PostgreSQL. Тут я могу ошибаться, но по моим представлениям это большой продукт для крупных компаний и нагруженных серверов. Barman размещают на отдельное железо и бэкапаят весь парк своих кластеров. Его часто сравнивают с pgBackrest и выбирают, что лучше.

🔹WAL-G - более молодой продукт по сравнению с Barman и pgBackrest. Написан на GO и поддерживает в том числе MySQL/MariaDB и MS SQL Server. Возможности сопоставимы с первыми двумя, но есть и свои особенности.

Если перед вами стоит задача по бэкапу PostgreSQL, а вы не знаете с чего начать, так как для вас это новая тема, посмотрите выступление с HighLoad++:
▶️ Инструменты создания бэкапов PostgreSQL / Андрей Сальников (Data Egret)

#backup #postgresql

​​Stolon и Patroni — два наиболее известных решения для построения кластера PostgreSQL типа Leader-Followers. Про Patroni я уже как-то рассказывал. Для него есть готовый плейбук ansible — postgresql_cluster, с помощью которого можно легко и быстро развернуть нужную конфигурацию кластера.

Для Stolon я не знаю какого-то известного плейбука, хотя они и гуглятся в том или ином исполнении. В общем случае поднять кластер не трудно. В документации есть отдельная инструкция для поднятия Simple Cluster.

Для этого необходимо предварительно установить и настроить на узлах etcd. Так как его использует Kubernetes, инструкций в инете море. Настраивается легко и быстро. Потом надо закинуть бинарники Stolon на ноды. Готовых официальных пакетов нет. Дальше инициализируется кластер, запускается sentinel (агент-арбитр), затем запускается keeper (управляет postgres'ом), потом proxy (управляет соединениями). Дальше можно добавить ещё одну ноду, запустив на ней keeper с параметрами подключения к первому. Получится простейший кластер. Расширяется он для отказоустойчивости добавлением ещё арбитров, прокси и самих киперов с postgresql.

На тему кластеров Stolon и Patroni есть очень масштабное выступление от 2020 года на PgConf.Russia. Там разобрано очень много всего: теория, архитектура кластеров, практические примеры разворачивания и обработки отказа мастера, различия Stolon и Patroni, их плюсы и минусы:
▶️ Patroni и stolon: инсталляция и отработка падений
⇨ Текстовая расшифровка с картинками

Вот ещё одно выступление, где прямо и подробно разбирают различия Patroni и Stolon:
▶️ Обзор решений для PostgreSQL High Availability

Если выбирать какое-то решение, то я бы остановился на Patroini. Я его разворачивал, пробовал. Всё довольно просто и понятно. Про него и материалов больше в русскоязычном сегменте.

#postgresql

Попереживал тут на днях из-за своей неаккуратности. Настроил сервер 1С примерно так же, как описано у меня в статье:
⇨ Установка и настройка 1С на Debian с PostgreSQL

Сразу настроил бэкапы в виде обычных дампов sql, сделанных с помощью pg_dump. Положил их в два разных места. Сделал проверки. Всё, как описано в статье. Потом случилось то, что 1С убрали возможность запускать сервер на Linux без серверной лицензии. И вся моя схема проверки бэкапов сломалась, когда я восстанавливал дампы на копии сервера и там делал выгрузку в dt. И считал, что всё в порядке, если дамп восстановился, dt выгрузился и нигде не было ошибок. На основном сервере я не хочу делать такие проверочные восстановления.

Я всё откладывал проработку этого вопроса, так как надо разобраться с получением лицензии для разработчиков 1С, изучить нюансы, проработать новую схему и т.д. Всё никак время не находил.

И тут меня попросили восстановить одну базу, откатившись на несколько дней назад. Я понимаю, что дампы я не проверяю и на самом деле хз, реально ли они рабочие. По идее да, так как дампы делались и ошибок не было. Но если ты не восстанавливался из них, то не факт, что всё пройдёт успешно, тем более с 1С, где есть свои нюансы.

В итоге всё прошло успешно. Восстановиться из бэкапов в виде дампов довольно просто. За это их люблю и если размер позволяет, то использую именно их. Последовательность такая:

1️⃣ Находим и распаковываем нужный дамп.

2️⃣ Создаём новую базу в PostgreSQL. Я всегда в новую восстанавливаю, а не в текущую. Если всё ОК и старая база не нужна, то просто удаляю её, а новую делаю основной.
# sudo -u postgres /usr/bin/createdb -U postgres -T template0 base1C-restored

3️⃣ Проверяю, создалась ли база:
# sudo -u postgres /usr/bin/psql -U postgres -l

4️⃣ Восстанавливаю базу из дампа в только что созданную. ❗️Не ошибитесь с именем базы.
# sudo -u postgres /usr/bin/psql -U postgres base1C-restored < /tmp/backup.sql

5️⃣ Иду в консоль 1С и добавляю восстановленную базу base1C-restored.

Если сначала создать базу через консоль 1С и восстановить в неё бэкап, будут какие-то проблемы. Не помню точно, какие, но у меня так не работало. Сначала создаём и восстанавливаем базу, потом подключаем её к 1С.

Мораль какая. Бэкапы надо восстанавливать и проверять, чтобы лишний раз не дёргаться. Я без этого немного переживаю. Поэтому люблю бэкапы в виде сырых файлов или дампов. По ним сразу видно, что всё в порядке, всё на месте. А если у тебя бинарные бэкапы, сделанные каким-то софтом, то проверяешь ты их тоже каким-то софтом и надеешься, что он нормально всё проверил. Либо делать полное восстановление, на что не всегда есть ресурсы и возможности, если речь идёт о больших виртуалках.

#1С #postgresql

​​Я уже делал серию заметок про CIS (Center for Internet Security). Это некоммерческая организация, которая разрабатывает собственные рекомендации по обеспечению безопасности различных систем. Я проработал рекомендации для:

- Nginx
- MySQL 5.7
- Apache 2.4
- Debian 11
- Docker
- Ubuntu 22.04 LTS

Основная проблема этих рекомендаций - они составлены в огромные pdf книги, иногда до 800 страниц и покрывают очень широкий вектор атак. Выбрать из них то, что вам нужно, довольно хлопотно. Я выделяю основные рекомендации, которые стоит учесть при базовом использовании стреднестатиcтической системы. В этот раз разобрал рекомендации для PostgreSQL 16.

🔹Убедитесь, что настроено логирование ошибок. Задаётся параметром log_destination. По умолчанию пишет в системный поток stderr. Считается, что это ненадёжный способ, поэтому рекомендуется отдельно настроить logging_collector и отправлять логи через него. По умолчанию он отключен. Если настроено сохранение логов в файлы, то не забыть закрыть доступ к ним и настроить ротацию средствами postgresql (log_truncate_on_rotation, log_rotation_age и т.д.)

🔹Для повышения возможностей аудита имеет смысл включить логирование подключений и отключений клиентов. Параметры log_connections и log_disconnections. По умолчанию отключено.

🔹Если вам необходимо отслеживать активность в базе данных, то имеет смысл настроить параметр log_statement. Значение ddl позволит собирать информацию о действиях CREATE, ALTER, DROP.

🔹Для расширенного аудита используйте отдельный модуль pgAudit. Обычно ставится в виде отдельного пакета. Для расширенного контроля за действиями superuser используйте расширение set_user.

🔹Если есть необходимость работать с postgresql в консоли, установите и настройте утилиту sudo, чтобы можно было контролировать и отслеживать действия различных пользователей.

🔹Рекомендованным методом аутентификации соединения является scram-sha-256, а не популярный md5, у которого есть уязвимость (it is vulnerable to packet replay attacks). Настраивается в pg_hba.conf.

🔹Настройте работу СУБД на том сетевом интерфейсе, на котором она будет принимать соединения. Параметр listen_addresses, по умолчанию указан только localhost. Если используется внешний сетевой интерфейс, не забудьте ограничить к нему доступ средствами firewall.

🔹Если есть необходимость шифровать TCP трафик от и к серверу, то не забудьте настроить TLS. За это отвечает параметр hostssl в pg_hba.conf и параметры ssl, ssl_cert_file, ssl_key_file в postgresql.conf. Поддерживается работа с self-signed сертификатами.

🔹Для репликации создавайте отдельных пользователей. Не используйте существующих или superuser.

🔹Не забудьте проверить и настроить создание бэкапов. Используйте pg_basebackup для создания полных бэкапов и копии WAL журналов для бэкапа транзакций.

Остальные рекомендации были в основном связаны с ролями, правами доступа и т.д. Это уже особенности конкретной эксплуатации. Не стал о них писать.

Сам файл с рекомендациями живёт тут. Для доступа нужна регистрация.

#cis #postgresql