Технический пост, который уже давно нужно было сделать, но всё руки не доходили. На канале много содержательных заметок по различным темам. Иногда сам через поиск ищу то, о чём писал. Ниже набор наиболее популярных тэгов по которым можно найти что-то полезное (и не очень).

#remote - все, что касается удалённого управления компьютерами
#helpdesk - обзор helpdesk систем
#backup - софт для бэкапа и некоторые мои заметки по теме
#zabbix - всё, что касается системы мониторинга Zabbix
#мониторинг - в этот тэг иногда попадает Zabbix, но помимо него перечислено много различных систем мониторинга
#управление #ITSM - инструменты для управления инфраструктурой
#devops - в основном софт, который так или иначе связан с методологией devops
#kuber - небольшой цикл постов про работу с kubernetes
#chat - мои обзоры на популярные чат платформы, которые можно развернуть у себя
#бесплатно - в основном подборка всяких бесплатностей, немного бесплатных курсов
#сервис - сервисы, которые мне показались интересными и полезными
#security - заметки, так или иначе связанные с безопасностью
#webserver - всё, что касается веб серверов
#gateway - заметки на тему шлюзов
#mailserver - всё, что касается почтовых серверов
#elk - заметки по ELK Stack
#mikrotik - очень много заметок про Mikrotik
#proxmox - заметки о популярном гипервизоре Proxmox
#terminal - всё, что связано с работой в терминале
#bash - заметки с примерами полезных и не очень bash скриптов или каких-то команд. По просмотрам, комментариям, сохранениям самая популярная тематика канала.
#windows - всё, что касается системы Windows
#хостинг - немного информации и хостерах, в том числе о тех, кого использую сам
#vpn - заметки на тему VPN
#perfomance - анализ производительности сервера и профилирование нагрузки
#курсы - под этим тэгом заметки на тему курсов, которые я сам проходил, которые могу порекомендовать, а также некоторые бесплатные курсы
#игра - игры исключительно IT тематики, за редким исключением
#совет - мои советы на различные темы, в основном IT
#подборка - посты с компиляцией нескольких продуктов, объединённых одной тематикой
#отечественное - обзор софта из реестра отечественного ПО
#юмор - большое количество каких-то смешных вещей на тему IT, которые я скрупулезно выбирал, чтобы показать вам самое интересное. В самом начале есть шутки, которые придумывал сам, проводил конкурсы.
#мысли - мои рассуждения на различные темы, не только IT
#разное - этим тэгом маркирую то, что не подошло ни под какие другие, но при этом не хочется, чтобы материал терялся, так как я посчитал его полезным
#дети - информация на тему обучения и вовлечения в IT детей
#развитие_канала - серия постов на тему развития данного telegram канала

Остальные тэги публикую общим списком без комментариев, так как они про конкретный софт, понятный из названия тэга:
#docker #nginx #mysql #postgresql #gitlab #asterisk #openvpn #lxc #postfix #bitrix #икс #debian #hyperv #rsync #wordpress #zfs #grafana #iptables #prometheus #1с #waf #logs #netflow

​​Решил написать небольшую шпаргалку по работе в консоли с PostgreSQL. Последнее время всё чаще и чаще приходится иметь с ней дело по двум причинам:
1️⃣ Все новые установки Zabbix Server делаю на postgresql.
2️⃣ Намного чаще стали использовать postgresql в связке с 1С.

PostgreSQL создаёт отдельного пользователя Linux postgres, так что все команды буду делать с указанием этого пользователя. Я обычно работаю так, хотя никто не мешает сразу авторизоваться под ним и запускать команды напрямую. Второй момент - в зависимости от дистрибутива и пакета установки, бинарники postgresql могут находиться в разных местах и не всегда переменная path будет применена. Так что может понадобиться полный путь к исполняемому файлу (pg_dump, psql и т.д.).

📌 Просмотр списка баз:
# sudo -u postgres psql -U postgres -l

📌 Создание текстового дампа базы данных:
# sudo -u postgres pg_dump -U postgres basa01 \
> ~/basa01.sql

📌 Сжимаем дамп на лету с помощью pigz (умеет жать всеми ядрами):
# sudo -u postgres pg_dump -U postgres basa01 \
| pigz > ~/basa01.sql.gz

📌 Восстановление базы данных в новую базу (сначала создаём её):
# sudo -u postgres createdb -U postgres \
-T template0 basa02
# sudo -u postgres psql -U postgres basa02 \
< ~/basa01.sql

Для автоматических бэкапов могу порекомендовать бесплатную программу SQLBackupAndFTP.

📌 Выход из консоли psql (часто забываю):
$ \q

📌 Создать пользователя:
# sudo -u postgres createuser -U postgres zabbix
Задать пароль:
# sudo -u postgres psql -U postgres -c \
"ALTER USER zabbix PASSWORD 'secpasswd'"

📌 Посмотреть список пользователей:
# sudo -u postgres psql -U postgres -c \
"select * from pg_user"

📌 Дать полные права на базу:
# sudo -u postgres psql -U postgres -c \
"GRANT ALL PRIVILEGES ON DATABASE zabbixdb to zabbix"

📌 Назначить пользователя владельцем базы:
# sudo -u postgres psql -U postgres -c \
"ALTER DATABASE zabbixdb OWNER TO zabbix"

📌 Выполнить очистку (-f) и анализ (-z) базы данных Postgres Pro:
# sudo -u postgres vacuumdb -U postgres -f -z -d basa01

📌 Переиндексировать базу:
# sudo -u postgres reindexdb -U postgres -d basa01

📌 Удалить базу данных:
# sudo -u postgres psql -U postgres -c \
"DROP DATABASE basa01"

#postgresql #bash

Вчера в комментариях к заметке про PostgreSQL посоветовали утилиту для бэкапа - pg_probackup. Я не знал про неё и никогда не использовал, но решил посмотреть. Оказалось, это очень удобный и надёжный вариант бэкапа баз данных PostgreSQL. Расскажу про особенности и варианты использования этой утилиты.

Авторами pg_probackup является небезызвестная компания Postgres Professional - российская компания, разработчик систем управления базами данных. Утилита поддерживает как ванильные версии postgres, так и собственные платные сборки postgres pro.

📌Pg_probackup умеет:

▪ Выполнять полные и инкрементные бэкапы как отдельных серверов, так и кластеров.
▪ Делать инкрементные бэкапы разными способами: разностное копирование, страничное копирование или копирование изменений. Разные способы дают разную нагрузку на систему и занимают разное количество времени.
▪ Выполнять контроль целостности данных и проверку резервных копий без восстановления самих данных.
▪ Управлять политиками хранения резервных копий в соответствии с заданными параметрами.
▪ Самостоятельно сжимать данные без внешних архиваторов.
▪ Работать в режиме клиент-сервер, то есть настраивается сервер хранения pg_probackup, который сам подключается к хостам с postgresql и забирает бэкапы.
▪ Отдельно поддерживает возможность бэкапа произвольных директорий и файлов. Например, директорию с конфигурацией кластера, с логами или какими-то самописными скриптами.
▪ Вести каталог резервных копий с метаданными архивов, данные о которых может отдавать в формате json.

Продукт многофункциональный, который закрывает полностью вопрос с бэкапами, политикой хранения и мониторинга. Получая информацию об архивах в json, очень легко прикрутить мониторинг к тому же Zabbix Server или Prometheus.

Как можно понять из описания, pg_probackup - универсальный инструмент, который подойдёт как для полного бэкапа одиночных баз и серверов, так и кластеров в различных режимах инкрементов. Архивы можно хранить локально или передавать по сети в единый каталог.

У утилиты есть подробное описание на русском языке. Для установки созданы репозитории под все популярные системы, в том числе отечественные.

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

❓Напрашивается важный вопрос. А когда стоит переходить к подобным бэкапам от простых дампов. Однозначного ответа тут нет. Ориентироваться нужно по следующим признакам:
1️⃣ Вам нужна возможность откатить состояние базы на любой момент в прошлом. Тогда дампы вообще не подходят.
2️⃣ Размер базы данных такой, что выполнение дампа занимает значительное время и снижает производительность сервера.
3️⃣ Хочется иметь большую глубину хранения резервных копий, но в полных дампах она будет занимать слишком много места.

По моим прикидкам, если сжатый дамп начинает занимать более 5 Гб, стоит думать о других способах создания и хранения резервных копий БД.

Исходники / Документация

#postgresql #backup

​​Существуют разные подходы к мониторингу. Можно настраивать одну универсальную систему и всё замыкать на неё. Это удобно в том плане, что всё в одном месте. Но мониторинг каждого отдельного элемента будет не самым лучшим.

Другой подход - для каждого продукта использовать тот мониторинг, который заточен именно под него. Например, такой мониторинг есть для Nginx - NGINX Amplify. Или мониторинг для баз данных от Percona - Percona Monitoring and Management (PMM). Про последний я и хочу сегодня рассказать.

Percona Monitoring and Management - open source мониторинг для баз данных MySQL, PostgreSQL и MongoDB. Построен на базе своего PMM Server и PMM Agent. Визуализация метрик реализована через Grafana.

Благодаря тому, что это узкоспециализированный продукт, его очень легко установить и настроить. По сути и настраивать то нечего. Достаточно установить сервер, агенты на хосты с БД и дальше смотреть метрики в готовых дашбордах Графаны.

Это не сравнится с тем, что предлагает Zabbix по мониторингу баз данных. Да, все те же метрики в него тоже можно передать. Но настроить всё это в готовую систему с метриками, триггерами, оповещениями, графиками и дашбордами очень хлопотно. А в PMM всё это работает сразу после установки. Времени нужно минимум, чтобы запустить мониторинг.

В принципе, если для вас важны базы данных, подобные мониторинги можно и отдельно разворачивать рядом с основным, а потом события со всех мониторингов собирать в одном месте. Например, с помощью OnCall.

Как я уже сказал, установить PMM очень просто. Можете сами оценить сложность и трудозатраты - Install Percona Monitoring and Management. Буквально 10 минут копипасты: ставим сервер, ставим клиент, соединяем клиента с сервером, добавляем в базу пользователя для сбора метрик. Если я правильно понял, то PMM построен на базе prometheus и использует метрики с его exporters.

Как всё это выглядит, можете посмотреть в публичном Demo. Там даже аутентификация не нужна. Помимо метрик баз данных PMM может собирать типовые метрики сервера Linux, HAProxy, выполнять внешние проверки tcp портов или забирать метрики по http.

Проект относительно свежий (2019 год) и очень активно развивается.

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

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

Те, кто постоянно работают с Postgresql наверняка знают про такой параметр, как stats_temp_directory. В самой документации по СУБД сказано, что его перенос в ОЗУ снизит нагрузку на файловое хранилище и увеличит быстродействие.

Я в обязательном порядке переносил это хранилище для временных данных статистики в tmpfs, потому что при использовании SSD этот каталог очень быстро съедал его ресурс. И это не пустые опасения, как часто бывает с SSD. Я реально видел по мониторингу, как утекал ресурс. Там идёт постоянная активная перезапись.

Хорошая новость в том, что в Postgres 15 больше не потребуется это делать. Там все подсчёты статистики выполняются в памяти по умолчанию. Такого параметра, как stats_temp_directory больше нет.

Сейчас уже активно обновляют или ставят 1С вместе с Postgres 15, так что информирую. Сборку postgresql для 1С можно скачать тут - https://1c.postgres.ru. 15-я уже в наличии.

#postgresql

Я обновил и актуализировал популярную на сайте статью по настройке сервера 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

​​Для мониторинга СУБД PostgreSQL существует много вариантов настройки. Собственно, как и для всего остального. Тема мониторинга очень хорошо развита в IT. Есть из чего выбирать, чему отдать предпочтение.

Одним из наиболее простых и быстрых для внедрения инструментов именно для psql является Pgwatch2. Это известная штука, для которой много инструкций и руководств. Есть обзоры на youtube. Отдельно отмечу, что если вы используете систему мониторинга Zabbix, то искать что-то ещё, большого смысла нет. У неё есть хороший встроенный шаблон, который собирает много различных метрик. В общем случае этого мониторинга будет за глаза.

Если вы не знаете Zabbix, у вас его нет и ставить не планируете, то закрыть вопрос с мониторингом PostgreSQL можно с помощью Pgwatch2. Этот продукт состоит из следующих компонентов:

▪ Хранилище метрик. В его качестве может выступать сама PostgreSQL, в том числе с расширением TimescaleDB. Также метрики можно хранить в InfluxDB. Либо их можно отправить в Prometheus, а он положит куда-то у себя.
▪ Сборщик метрик, написанный на GO.
▪ Веб интерфейс для управления, написанный на Python
▪ Grafana для просмотра дашбордов с метриками.

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

Посмотреть, как выглядит итоговый мониторинг, можно в публичном DEMO. Там из коробки настроено много дашбордов, не забудьте их посмотреть. Информацию по конкретной базе удобно смотреть в дашборде DB overview.

⇨ Исходники / Документация / Demo / Видеобозор (на русском)

#postgresql #monitoring

🎓 У компании Postgres Professional есть очень качественные бесплатные курсы по СУБД PostgreSQL. Не припоминаю, чтобы у каких-то других коммерческих компаний были бы курсы в таком же формате. Вы можете пройти обучение в авторизованных учебных центрах с помощью преподавателей, либо изучать материалы курса самостоятельно. Они полностью бесплатны:

⇨ https://postgrespro.ru/education/courses

Доступны следующие курсы для администраторов PostgreSQL:

▪ DBA1. Администрирование PostgreSQL. Базовый курс
▪ DBA2. Администрирование PostgreSQL. Настройка и мониторинг
▪ DBA3. Администрирование PostgreSQL. Резервное копирование и репликация
▪ QPT. Оптимизация запросов
▪ PGPRO. Возможности Postgres Pro Enterprise

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

Например, вам надо обновить сервер или кластер серверов на новую ветку. Идём на курс Администрирование PostgreSQL 13. Настройка и мониторинг, смотрим тему 17. Обновление сервера. Для экономии времени достаточно посмотреть презентацию. Там будет и теория по теме, и точные команды в консоли для выполнения тех или иных действий. Если хочется более подробную информацию с комментариями преподавателя, то можно посмотреть видео.

То же самое про бэкап. Хотите разобраться - открываете курс Администрирование PostgreSQL 13. Резервное копирование и репликация, тема 2. Базовая резервная копия. Там вся теория и примеры по холодным, горячим копиям, плюсы и минусы разных подходов, инструменты для бэкапа, как их проверять. Не пересказы каких-то блогеров или спикеров конференций, а первичка от разработчиков.

Сейчас почти все сервера 1С, да и многое другое, использует PostgreSQL, так что тема актуальна. Я и Zabbix Server все уже года два поднимаю с PostgreSQL, а не MySQL, как раньше.

#обучение #postgresql

​​Запуская СУБД PostgreSQL в работу, необходимо в обязательном порядке выполнить хотя бы базовую начальную настройку, которая будет соответствовать количеству ядер и оперативной памяти сервера, а также профилю нагрузки. Это нетривиальная задача, так как там очень много нюансов, а тюнингом баз данных обычно занимаются отдельные специалисты - DBA (Database administrator).

Если у вас такого специалиста нет, а сами вы не особо разбираетесь в этой теме, то можно воспользоваться готовым конфигуратором, который в целом выдаст адекватную настройку для типового использования. Я знаю два таких конфигуратора:

⇨ https://pgconfigurator.cybertec.at
⇨ https://www.pgconfig.org

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

Отдельно отмечу, что если вы устанавливаете сборку СУБД PostgreSQL для 1C от компании PostgresPro, то там базовые оптимизации уже выполнены. Вы можете увидеть их в самом конце файла postgresql.conf. Там более 30-ти изменённых параметров. В этом случае использовать какие-то сторонние конфигураторы не стоит, так как настройки могут отличаться принципиально.

Например, для 1С в сборках PostgresPro неизменно установлен параметр:

max_parallel_workers_per_gather = 0

Я это проверил и на старом сервере 13-й версии и на более свежем 15-й. В общем случае в 0 его ставить не рекомендуется. Он и дефолтный не 0, и все оптимизаторы его не ставят в 0. Но для 1С рекомендуется именно 0. Я не знаю, в чём тут нюанс, а параметр это важный. Так что для 1С надо быть аккуратным при настройке PostgreSQL. Я из-за этого всегда использую сборки от PostgresPro. Там умные люди уже подумали над оптимизацией.

#postgresql

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

Тест выполняет на существующей базе данных. В ней создаются служебные таблицы и наполняются данными. Покажу, как это выглядит на практике. Ставим postgresql:

# apt install postgresql

Проверяем, что работает:

# systemctl status postgresql

Создаём базу данных:

# su - postgres
$ psql
$ create database test_db;
$ \q

Наполняем тестовую базу данными, увеличив стандартный набор в 10 раз:

$ pgbench -i test_db -s 10

Запускаем тест в 5 клиентов, в 2 рабочих потока, на 60 секунд с отсечкой результата каждые 5 секунд, чтобы в консоли интересно наблюдать было. Если будете в файл выводить результаты, то это делать не надо.

$ pgbench test_db -c 5 -j 2 -P 5 -T 60

transaction type: <builtin: TPC-B (sort of)>
scaling factor: 10
query mode: simple
number of clients: 5
number of threads: 2
maximum number of tries: 1
duration: 60 s
number of transactions actually processed: 219950
number of failed transactions: 0 (0.000%)
latency average = 1.362 ms
latency stddev = 0.661 ms
initial connection time = 8.368 ms
tps = 3665.930847 (without initial connection time)

СУБД обработала 219950 транзакций со средней скоростью 3665.930847 транзакций в секунду. Эти данные и стоит сравнивать.

По умолчанию pgbench запускает смешанный TPC-B (Transaction Processing Performance Council) тест, который состоит из пяти команд SELECT, UPDATE и INSERT в одной транзакции. Сценарий можно менять, создавая собственные скрипты для тестирования. Все возможности pgbench подробно описаны на русском языке в документации от postgrespro.

Из любопытства перекинул тестовую виртуалку с обычного одиночного SSD на RAID10 из 4-х HDD. Настройки в Proxmox те же, кэширование гипервизора отключено (Default (No cache)). Прогнал этот же тест.

tps = 2262.544160

Получилось на ~40% медленнее при идентичных настройках.

#postgresql

​​🎓 У хостера Selectel есть небольшая "академия", где в открытом доступе есть набор курсов. Они неплохого качества. Где-то по верхам в основном теория, а где-то полезные практические вещи. Я бы обратил внимание на два курса, которые показались наиболее полезными:

⇨ PostgreSQL для новичков
⇨ MySQL для новичков

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

Вообще, мне нравится такой маркетинг, когда даётся реальная польза, а в материал вставлены уместные ссылки на свои сервисы, на базе которых написана статья. Это ситуация, которая понятна и не раздражает навязчивостью или заманухой. Хостера могу порекомендовать. У меня никто не просил писать этот пост и рефку свою не оставляю. Я давно с ним работаю, поэтому пишу искренне.

#обучение #mysql #postgresql