ServerAdmin.ru

Запуская СУБД 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

👍140

13.2K views06:12

ServerAdmin.ru

Для тестирования производительности 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

👍110👎3

9.83K views06:12

ServerAdmin.ru

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

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

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

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

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

👍123👎7

12.4K views06:11

ServerAdmin.ru

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

Сразу покажу на примере, как это работает. Существует популярная open source утилита для этих целей - migrate. Она поддерживает все наиболее распространённые СУБД. Миграции можно выполнять с помощью готовой библиотеки на Go, либо в консоли через CLI. Я буду использовать CLI, СУБД PostgreSQL и ОС Debian 12.

Для Migrate собран deb пакет, хотя по своей сути это одиночный бинарник. Можно скачать только его. Для порядка ставим из пакета, который берём в репозитории:

# wget https://github.com/golang-migrate/migrate/releases/download/v4.18.1/migrate.linux-amd64.deb
# dpkg -i migrate.linux-amd64.deb

Для удобства добавим строку на подключение к локальной базе данных в переменную:

# export POSTGRESQL_URL='postgres://postgres:pass123@localhost:5432/test_migrations?sslmode=disable'

Я подключаюсь без ssl к базе данных test_migrations под учёткой postgres с паролем pass123. С рабочей базой так делать не надо, чтобы пароль не улетел в history.

Принцип работы migrate в том, что создаются 2 файла с sql командами. Первый файл выполняется, когда мы применяем обновление, а второй - когда мы откатываем. В своём примере я добавлю таблицу test01 с простой структурой, а потом удалю её в случае отката.

# cd ~ && mkdir migrations
# migrate create -ext sql -dir migrations -seq create_test01_table
~/migrations/000001_create_test01_table.up.sql
~/migrations/000001_create_test01_table.down.sql

В директории были созданы 2 файла. В первый файл с up в имени добавим sql код создания таблицы test01:

CREATE TABLE IF NOT EXISTS test01(
  user_id serial PRIMARY KEY,
  username VARCHAR (50) UNIQUE NOT NULL,
  password VARCHAR (50) NOT NULL,
  email VARCHAR (300) UNIQUE NOT NULL
);

А во второй с down - удаление:

DROP TABLE IF EXISTS test01;

Проверим, как это работает:

# migrate -database ${POSTGRESQL_URL} -path migrations up
1/u create_test01_table (24.160815ms)

Смотрим, появилась ли таблица:

# psql ${POSTGRESQL_URL} -c "\d test01"

Вы должны увидеть структуру таблицы test01. Теперь откатим наше изменение:

# migrate -database ${POSTGRESQL_URL} -path migrations down
Are you sure you want to apply all down migrations? [y/N]
y
Applying all down migrations
1/d create_test01_table (15.851045ms)

Проверяем:

# psql ${POSTGRESQL_URL} -c "\d test01"
Did not find any relation named "test01".

Таблицы нет. Принцип тут простой - пишем SQL код, который исполняем. На деле, конечно, изменения бывают сложные, особенно когда не добавляются или удаляются таблицы, а меняется структура существующих таблиц с данными. Инструменты типа migrate позволяют описать все изменения и проработать процесс обновления/отката в тестовых средах. В простых случаях можно обойтись и своими bash скриптами, но migrate упрощает эту задачу, так как, во-первых поддерживает множество СУБД. Во-вторых, автоматически нумерует миграции и исполняет последовательно. В-третьих, может, к примеру, забирать миграции напрямую из git репозитория.

Для каждой СУБД в репозитории Migrate есть примеры настройки миграций.

❗️Если заметка вам полезна, не забудьте 👍 и забрать в закладки.

⇨ Исходники

#postgresql #mysql

2👍96👎4

10.2K views06:11

ServerAdmin.ru

Для тех, кто много работает с базами данных, есть простой и удобный инструмент для их проектирования – dbdiagram.io. Мы не разработчики, тем не менее, покажу, как этот сервис может быть полезен. У него удобная визуализация структуры базы данных. Намного удобнее, чем, к примеру, в привычном phpmyadmin.

Можно выгрузить обычный дам из СУБД без данных и загрузить его в dbdiagram. Получите наглядную схему базы. При желании, её можно отредактировать, или просто экспортнуть в виде картинки. Покажу на примере MySQL. Выгружаем дамп базы postfix без данных, только структуру:

# mysqldump --no-data -u dbuser -p postfix > ~/schema.sql

Передаём к себе на комп полученный дамп. В dbdiagram.io открываем Import ⇨ From MySQL ⇨ Upload .sql ⇨ Submit. Наблюдаем схему базы данных со всеми связями. Причём она сразу отформатирована по таблицам так, что все их наглядно видно.

При желании можно что-то изменить в структуре и выгрузить новый sql файл уже с изменениями. Если выбрать Export ⇨ To PNG, то сразу же получите аккуратную картинку со схемой, без лишних вопросов. Очень быстро и удобно, если нужно куда-то приложить схему базы данных.

⇨ 🌐 Сайт

❗️Если заметка вам полезна, не забудьте 👍 и забрать в закладки.

#mysql #postgresql

3👍173👎5

11.7K views15:32

ServerAdmin.ru

Расскажу про парочку инструментов, которые упростят обслуживание сервера PostgreSQL. Начну с наиболее простого - pgBadger. Это анализатор лога, который на его основе генерирует отчёты в формате html. На выходе получаются одиночные html файлы, которые можно просто открыть в браузере. Сделано всё аккуратно и добротно, легко настраивается, отчёты наглядные и информационные.

🔹Чтобы было что анализировать, необходимо включить логирование интересующих вас событий. Для разовой отладки это всё можно включать на ходу, либо постоянно через файл конфигурации postgresql.conf и перезапуск сервера. Он обычно хорошо прокомментирован. Вас будут интересовать параметры, начинающие с log_*. Они собраны все в отдельном блоке. Для примера я включил почти всё:

log_min_duration_statement = 0
log_checkpoints = on
log_connections = on
log_disconnections = on
log_duration = on
log_line_prefix = '%m [%p] %q%u@%d '
log_lock_waits = on
log_temp_files = 0
log_timezone = 'Europe/Moscow'

Вся включенная статистика стала писаться в общий лог-файл /var/log/postgresql/postgresql-17-main.log. С ним и будем работать. Устанавливаем pgBadger:

# wget https://github.com/darold/pgbadger/archive/refs/tags/v13.1.tar.gz
# tar xzvf v13.1.tar.gz
# cd pgbadger-*
# apt install make
# make && make install

Анализируем лог файл:

# pgbadger /var/log/postgresql/postgresql-17-main.log

Тут же в директории, где его запускали, увидите файл out.html. Забирайте его к себе и смотрите. Там будет информация с общей статистикой сервера, информация по запросам и их типам, времени исполнения, подключениям, по пользователям, базам и хостам откуда подключались и много всего остального.

PgBadger удобен тем, что по сути это одиночный скрипт на Perl. Можно включить логирование в конфигурации, применить её через SELECT pg_reload_conf(); без перезапуска сервера СУБД. Пособирать некоторое время данные, забрать лог и анализировать его. Логирование отключить и снова перечитать конфиг. В итоге всё будет сделано без перезапуска сервера.

🔹Второй инструмент - PgHero, он показывает примерно то же самое, только в режиме реального времени и работает в виде веб сервиса. Для него уже надо создавать пользователя, настраивать доступ, отдельную базу. Немного другой подход. Надо будет дёргать сервер с СУБД.

Надо перейти в консоль и создать необходимые сущности:

# su postgres
# psql
> CREATE USER pghero WITH PASSWORD 'pgheropass';
> CREATE DATABASE pgherodb OWNER pghero;
> \q

Разрешаем этому пользователю подключаться. Добавляем в pg_hba.conf строку:

host pgherodb pghero 172.17.0.0/24 md5

172.17.0.0/24 - подсеть, из которой будет подключаться PgHero. В данном случае это Docker контейнер, запущенный на этом же хосте. PostgreSQL должен принимать запросы с локального IP адреса, к которому будет доступ из Docker сети. Можно добавить в конфиг postgresql.conf параметр:

listen_addresses = 'localhost,172.17.0.1'

Перезапускаем PotgreSQL:

# systemctl restart postgresql

Запускаем PgHero в Docker контейнере:

# docker run -ti -e DATABASE_URL=postgres://pghero:pgheropass@172.17.0.1:5432/pgherodb -p 8080:8080 ankane/pghero

Идём на порт севера 8080, где запущен контейнер и смотрим информацию о PostgreSQL. Если у вас не настроено расширение pg_stat_statements, которое использует PgHero для сбора статистики, то установите его. Для этого в конфигурацию postgresql.conf добавьте параметры:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000
track_activity_query_size = 2048

Перезапустите Postgresql и выполните в консоли СУБД:

> CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
> GRANT pg_read_all_stats TO pghero;

Теперь можно возвращаться в веб интерфейс и смотреть информацию. По умолчанию, пользователь pghero не будет видеть запросы других пользователей, если ему не дать права superuser. Это можно исправить, выдав ему набор прав и ролей из этой инструкции.

❗️Если заметка вам полезна, не забудьте 👍 и забрать в закладки.

#postgresql

👍107👎4

11.4K views06:11

ServerAdmin.ru

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

Речь пойдёт о Postgresus. Основные возможности:

◽️Веб интерфейс для управления заданиями и отчётами
◽️Расписание бэкапов
◽️Сжатие, автоочистка старых бэкапов
◽️Хранение локально или в S3, Google Drive
◽️Уведомления по Email, Telegram
◽️Запускается в Docker Compose

По сути это замена самописным bash скриптам в кроне. Именно их я и использую для таких задач 🤖 Решил упростить себе задачу. Забегая вперёд, скажу, что эта панель реально заменяет эти скрипты, не более.

Postgresus состоит из двух Docker контейнеров: непосредственно сама панель и локальный экземпляр postgresql сервера для хранения собственных настроек. Запустить можно вручную из предложенного в репозитории файла docker-compose.yml, либо воспользоваться скриптом от разработчика:

# curl -sSL https://raw.githubusercontent.com/RostislavDugin/postgresus/refs/heads/main/install-postgresus.sh | bash

Скрипт ставит докер и стартует с этим же docker-compose.yml. Я вручную запустил. После запуска можно сразу идти в веб интерфейс на порт сервера 4005 и там всё настраивать. Не рекомендую запускать подобные вещи непосредственно на сервере с СУБД. Я сделал отдельную небольшую виртуалку и открыл для неё доступ к базам.

Дальнейшее управление осуществляется через веб интерфейс. Там всё максимально просто и наглядно:
1️⃣ Настраиваем хранилище для бэкапов. Я использовал локальную директорию. После тестов примонтирую туда отдельно большой диск.
2️⃣ Добавляем приёмник для уведомлений. Telegram сразу заработал, нужно указать токен своего бота и ID чата. С почтой не получилось, так как отправка по умолчанию настраивается по TLS, а у меня там локальный сервер стоял без него. Перенастраивать не захотелось. Жаль, что автор не оставил такой возможности.
3️⃣ Добавляем подключение к базе данных и настраиваем параметры её бэкапа.

Бэкап выполняется не особо быстро. Скриптами локально я базу на 5ГБ бэкаплю секунд 30. Из соседней виртуалки с помощью Postgresus бэкапится 12 минут. В целом мне некритично, так как для этого сервера бэкапы выполняются раз в сутки по ночам. Времени достаточно, хотя с такой скоростью это растянется часа на полтора.

Архивы хранятся локально в виде дампов со случайными именами вида a8bc8814-c548-4f9a-ba0c-97d3b1e107fc. Не очень удобно, если захочется куда-то дублировать их. Скачать дамп можно через веб интерфейс и вручную восстановить с помощью pg_restore. Я заглянул внутрь дампа. Он отличается от того, что делаю я с помощью pg_dump. Скорее всего используются какие-то дополнительные ключи. Я обычно делаю чистый дамп только данных.

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

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

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

❗️Если заметка вам полезна, не забудьте 👍 и забрать в закладки.

#postgresql #backup

Please open Telegram to view this post

VIEW IN TELEGRAM

Please open Telegram to view this post

VIEW IN TELEGRAM

👍85👎7

9.75K views06:11

ServerAdmin.ru

Вчера рассмотрел Postgresus - веб панель для бэкапов баз Postgresql. У неё есть очень близкий и более старый аналог – PG Back Web. Панели похожи по функциональности и архитектуре. Автор Postgresus наверняка знаком с ней, потому что многие вещи реализованы схожим образом. Но есть и отличия. Каждый, судя по всему, реализовал настройки и управление так, как ему казалось более удобным. Расскажу по порядку.

1️⃣ Установка выполняется так же, как и у Postgresus – готовый docker-compose.yaml в репозитории для запуска двух контейнеров: сама веб панель и локальная Postgresql для сохранения настроек и состояния. Достаточно запустить docker compose и можно идти на порт 8085 сервера и пользоваться панелью. В консоли больше делать нечего.

2️⃣ В Pgbackweb сущности в виде баз данных и заданий для бэкапа разделены. Отдельно добавляется база, отдельно для неё создаётся задание для бэкапа. Соответственно, для одной и той же базы могут быть созданы разные задачи с бэкапом и параметрами хранения. В Postgresus настройки базы и бэкапа для неё объединены в одну сущность.

3️⃣ Создаваемый в Pgbackweb дамп тут же на лету жмётся в zip. А в самом архиве лежит чистый дамп в текстовом формате, который можно открыть, посмотреть, изменить. Создание дампа в Pgbackweb примерно в 2-2,5 раза дольше, чем у Postgresus. Последний жмёт средствами самого pg_dump, если я правильно понял из исходников, то есть использует ключи -Fc (custom-format) -Z 6 (compression level).

К сожалению, обе панели не дают возможность самому задавать эти параметры. А иногда бывает нужно либо так, либо эдак. Например, если тебе важна скорость, то лучше жать встроенными средствами. Но из такого дампа потом неудобно извлекать отдельную таблицу, если она понадобится. А из текстового дампа это сделать очень просто. Лично я, когда создаю дампы баз данных, выгружаю их в обычном текстовом формате и жму сам с помощью pigz в многопоточном режиме. Получается и быстро, и удобно. Потом распаковал и делай с ним, что хочешь.

4️⃣ Pgbackweb предлагает сохранять бэкапы либо локально, либо в S3. Больше у неё ничего нет для хранения. Формат путей вида /backups/dbase01/2025/07/21. Это удобно, если захочется забирать готовые дампы куда-то ещё и складывать их в архивы по каким-то датам. В Postgresus такой возможности нет. Там ни в пути, ни в имени файла нет никаких временных меток и упоминания имени базы.

5️⃣ Сохранённый дамп можно самому скачать и восстановить вручную, либо воспользоваться веб интерфейсом. Базу можно восстановить как в исходную, так и в другую, в том числе на другом сервере. Здесь возможности обеих панелей совпадают.

6️⃣ В качестве уведомлений Pgbackweb предлагает только вебхуки, что слабовато по сравнению с готовой интеграцией с Telegram, Email, Slack, Discord в Postgresus.

В общем и целом панели сильно похожи и выполняют одни и те же задачи. Разница в некоторых мелочах, которые кому-то могут быть критичны, кому-то нет. Мне лично больше понравилась Postgresus за её внешний вид и внутреннюю логику. Сжатые дампы делает намного быстрее, что уже на базах в 5-10 ГБ становится критично. Так как, к примеру, 15 минут против 35 для 10 ГБ – это существенная разница.

⇨ 🌐 Исходники / ▶️ Видеобзор

❗️Если заметка вам полезна, не забудьте 👍 и забрать в закладки.

#postgresql #backup

Please open Telegram to view this post

VIEW IN TELEGRAM

Please open Telegram to view this post

VIEW IN TELEGRAM

👍63👎3

9.59K views06:11

ServerAdmin.ru

Раз уж я рассмотрел панели для управления бэкапами (Postgresus и Pgbackweb) в виде дампов в Postgresql, было бы логично рассмотреть и вариант со своими велосипедами на bash. Поделюсь тем, что использую я.

Сначала полный текст скрипта для бэкапа:

#!/bin/bash

BASES=`/opt/pgpro/1c-16/bin/psql -U postgres -l | grep "_buh\|_zup" | awk '{print $1}'`
DATA=`date +"%Y-%m-%d_%H-%M"`
LOGDIR=/var/lib/pgpro/service_logs
BACKUPDIR=/var/lib/pgpro/backup

for i in ${BASES};
    do
    echo "`date +"%Y-%m-%d_%H-%M-%S"` Start backup $i" >> $LOGDIR/backup.log
    /opt/pgpro/1c-16/bin/pg_dump -U postgres $i > $BACKUPDIR/$DATA-$i.sql 2>> $LOGDIR/dump.log
    BEGIN=`head -n 2 $BACKUPDIR/$DATA-$i.sql | grep ^'-- PostgreSQL database dump' | wc -l`
    END=`tail -n 3 $BACKUPDIR/$DATA-$i.sql | grep ^'-- PostgreSQL database dump complete' | wc -l`
    if [ "$BEGIN" == "1" ];then
      if [ "$END" == "1" ];then
          echo "Backup ${i} is OK" >> $LOGDIR/backup.log
          /usr/bin/pigz -c $BACKUPDIR/$DATA-$i.sql > $BACKUPDIR/$DATA-$i.sql.gz
          /usr/bin/rm $BACKUPDIR/$DATA-$i.sql
      else
          echo "Backup ${i} is corrupted" >> $LOGDIR/backup.log
      fi
    else
      echo "Backup ${i} is corrupted" >> $LOGDIR/backup.log
    fi
      echo "`date +"%Y-%m-%d_%H-%M-%S"` End backup $i" >> $LOGDIR/backup.log
      echo "=========================================" >> $LOGDIR/backup.log
done

Рассказываю, что тут происходит:

1️⃣ Завожу в переменную BASES список баз, которые мы будем бэкапить. Их можно указывать вручную, примерно так: BASES=("db01_zup" "db02_buh"). Можно брать список всех баз. В данном случае на сервере я маркирую базы метками _buh и _zup в названиях баз, чтобы по ним делать выборку. Если базу не нужно бэкапить, то эта метка не ставится. Можно придумать любую свою метку, например _back. Это позволяет автоматически бэкапить все нужные базы, не следя за их составом. Очень актуально, если создаёте и удаляете базы не вы, но вам нужны все бэкапы баз.

Можно использовать обратный подход. Бэкапить все базы, но вручную или по маске задавать исключения. Примерно так:

/opt/pgpro/1c-15/bin/psql -U postgres -l | grep -wv 'template0\|template1\|test-base\|_test' | sed -e '1,3d' | head -n -2 | awk '{print $1}'`

2️⃣ База дампится с помощью pg_dump без каких-либо дополнительных параметров. На выходе имеем обычный несжатый текстовый дамп.

3️⃣ В дампе проверяем наличие строки -- PostgreSQL database dump в начале дампа и -- PostgreSQL database dump complete в конце. Поэтому нам важно иметь дамп в текстовом формате. Если процесс снятия дампа прошёл без ошибок и в дампе присутствуют эти строки, то с большой долей вероятности с ним всё в порядке. Я ни разу не сталкивался с обратной ситуацией.

Соответственно, если строки присутствуют, пишем в лог файл, что Backup is OK, если есть проблемы, то Backup is corrupted. Дальше этот файл забирает либо мониторинг, либо хранилка логов. И если они видят фразу corrupted, то срабатывает триггер.

4️⃣ Если с дампом всё в порядке, то он жмётся архиватором pigz. Он работает многопоточно всеми доступными ядрами процессора. Если не хотите нагружать так плотно сервер, то количество потоков можно ограничить ключами. Я обычно снимаю дампы ночью и жму на всю катушку, чтобы побыстрее завершилось. Дампы потом ещё передавать надо. Это уже отдельная процедура и запускается с сервера бэкапов. С самого сервера СУБД к бэкапам доступа нет.

Получается автоматическая максимально простая и эффективная схема работы. Я вчера добавлял базы в веб панель. Честно говоря, меня это утомило. Если баз 1-2, то не проблема. А 15 штук добавлять уже надоедает. Бывает и больше. Плюс, надо вручную следить за ними, добавлять, отключать ненужные и т.д. Так что панель вроде и выглядит удобно, но если баз много, то удобства уже сомнительные.

Дальше у меня работает ещё один простой скрипт, который забирает эти дампы на тестовый сервер, распаковывает и заливает в СУБД. Расскажу о нём в следующей заметке.

❗️Если заметка вам полезна, не забудьте 👍 и забрать в закладки.

#postgresql #backup #script

Please open Telegram to view this post

VIEW IN TELEGRAM

4👍161👎3

8.31K views06:11

ServerAdmin.ru

Утром рассказал, как делаю бэкапы баз Postgresql с помощью скриптов и pg_dump. Сейчас покажу, как я их проверяю. Для этого обычно держу небольшой тестовый сервер с той же версией Postgresql. В идеале это должна быть копия прода, но не всегда это возможно. На нём же зачастую проверяю обновления.

Первым делом надо забрать дампы с исходного сервера. Для этого беру простой скрипт, который забирает по SSH только свежие файлы за прошлый день. Если забрать сразу только нужные файлы, то потом не придётся делать в них выборку:

#!/bin/bash

/usr/bin/rsync -av --progress --files-from=<(ssh root@10.20.5.10 '/usr/bin/find /var/lib/pgpro/backup -type f -mtime -1 -exec basename {} \; | egrep -v timestamp') root@10.20.5.10:/var/lib/pgpro/backup/ /data/backup/

Использую rsync, а список файлов для копирования формирую сразу в выражении ключа, подключившись к нужному хосту по SSH и отсортировав файлы по дате, взяв не старше суток. В данном случае файл timestamp через egrep -v добавлен в исключения. У меня там метка времени последнего бэкапа хранится для нужд мониторинга.

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

#!/bin/bash

BASES=`/opt/pgpro/1c-16/bin/psql -U postgres -l | grep "_buh\|_zup" | awk '{print $1}'`
BACKUP_DIR="/data/backup"

for i in ${BASES};
  do
    echo "`date +"%Y-%m-%d_%H-%M-%S"` Drop database ${i}"
    /opt/pgpro/1c-16/bin/dropdb -U postgres ${i}
    echo "`date +"%Y-%m-%d_%H-%M-%S"` Create database ${i}"
    /opt/pgpro/1c-16/bin/createdb -U postgres -T template0 ${i}
    echo "`date +"%Y-%m-%d_%H-%M-%S"` Start extract $i"
    /usr/bin/find /data/backup -type f -name *${i}* -exec /usr/bin/unpigz '{}' \;
    echo "`date +"%Y-%m-%d_%H-%M-%S"` End extract $i"
    echo "`date +"%Y-%m-%d_%H-%M-%S"` Start restore $i"
    /opt/pgpro/1c-16/bin/psql -U postgres ${i} < ${BACKUP_DIR}/`ls ${BACKUP_DIR} | grep ${i}` 1>/dev/null
    echo "`date +"%Y-%m-%d_%H-%M-%S"` End restore $i"
    echo "`date +"%Y-%m-%d_%H-%M-%S"` rm dump ${i}"
    /usr/bin/rm ${BACKUP_DIR}/`ls ${BACKUP_DIR} | grep ${i}`
done

Сразу говорю, что я тут ничего не оптимизировал, никаких проверок не делал. Если просто взять и запустить, то скорее всего у вас вывалятся какие-то ошибки или что-то пойдёт не так. Я просто показываю идею. У меня уже давно и структура каталогов одна и та же, и подход к именованию баз, бэкапов и т.д. Это всё сделано под меня и работает так уже много лет. Меня устраивает.

В данном скрипте нет никаких проверок успешной работы и оповещений, потому что это не последний этап проверок. Далее в зависимости от того, где эта база используется, запускаются свои проверки. Для баз 1С выполняется автоматическая выгрузка в dt с помощью отдельного скрипта. Если она проходит успешно, то считается, что дамп базы живой и нормально восстановился.

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

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

❗️Если заметка вам полезна, не забудьте 👍 и забрать в закладки.

#backup #postgresql #script

ServerAdmin.ru

👍108👎4

7.89K viewsedited 15:30

ServerAdmin.ru

backup-postgresql-restic.sh

2.1 KB

🔥 Бэкап баз postgresql с помощью restic!

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

⇨ Restic: эффективное резервное копирование из Stdin

Автор с помощью restic дампит базы данных в Kubernetes. Я немного переделал его решение для своих локальных бэкапов. Основная проблема, которая там решена - корректная отработка ошибок снятия дампа. Я до этого сам не догадался, правда не сильно и пытался, поэтому дампил по старинке сначала в файл, проверял, а потом уже куда-то на хранение отправлял.

Но можно всё это сделать на лету и с проверкой ошибок. То есть делается дамп и тут же через pipe отправляется в restic. Этот способ удобен по нескольким причинам:

1️⃣ Самое основное - сырые дампы в restic очень хорошо дедуплицируются и экономят место. Если между дампами небольшие изменения, то даже хранение большого количества файлов за разные дни не съедает место на хранилище в отличие от обычного хранения в исходном виде на файловой системе.

2️⃣ Прямая отправка данных с pg_dump в restic исключает промежуточное сохранение, что существенно экономит ресурс SSD дисков, если у вас много баз приличного размера. Да и банально быстрее получается.

3️⃣ Если во время снятия дампа будет ошибка, то такой бэкап помечается отдельным тэгом и по этому тэгу вы сможете понять, что у вас какая-то проблема. Я специально проверил этот момент. Отрабатывает корректно. Если дамп прерывается по какой-то причине и не завершается без ошибок, то этот бэкап помечается отдельным тэгом.

В статье у автора подробно всё рассказано, не буду на этом останавливаться. Покажу свой итоговый скрипт, который бэкапит дампы условно локально, а не в S3. Если надо в S3 или куда-то ещё, то просто измените переменную REPO_PREFIX в скрипте и добавьте переменных для доступа к бакетам. Плюс, я поменял формат хранения данных. Вместо tar выгружаю в текстовый sql файл. Мне так удобнее. Ещё исправил там небольшую ошибку, которую выявил во время тестов. В строке:

restic forget -r "${REPO_PREFIX}/$db" --group-by=tags --keep-tag "completed"

Надо убрать ключ --group-by=tags, иначе команда на очистку будет возвращать ошибку:

refusing to delete last snapshot of snapshot group "tags [job-8dd4f544]"

Это, как я понял, связано с тем, что ключ --keep-tag не позволяет удалить при группировке по тэгам полную группу тэгов, если в ней не останется хотя бы одного снепшота. А так как у нас все тэги с job уникальны, все проблемные снепшоты будут с уникальными тэгами и по сути одни в группе. Поэтому группировку --group-by=tags надо убрать. По крайней мере у меня, когда я убрал, ошибка ушла и очистка стала нормально отрабатывать. В доках это так объясняется: "The forget command will exit with an error if all snapshots in a snapshot group would be removed as none of them have the specified tags."

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

Сам скрипт прикрепил к сообщению. Также продублировал его в публичный репозиторий. Не забудьте поменять переменные под свои нужды и окружение.

Полезные материалы по теме:

▪️Описание Restic с примерами
▪️Мониторинг Restic с помощью Zabbix и Prometheus
▪️Бэкап с помощью Restic в S3 на примере Selectel

Restic очень удобный инструмент для бэкапов. Рекомендую присмотреться.

❗️Если заметка вам полезна, не забудьте 👍 и забрать в закладки.

#backup #postgresql #restic

Please open Telegram to view this post

VIEW IN TELEGRAM

2👍110👎1

9.44K views06:11

About

Blog

Apps

Platform