​​Решил написать небольшую шпаргалку по работе в консоли с 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