При обновлении кода сайта или веб сервиса легко сделать проверку изменений или выполнить откат в случае каких-то проблем. Задача сильно усложняется, когда обновление затрагивает изменение в структуре базы данных. Если вы накатили изменение, которое затрагивает базу данных, не получится просто откатить всё назад на уровне кода. Нужно откатывать и состояние базы. Для таких ситуация придумали миграции базы данных.
Сразу покажу на примере, как это работает. Существует популярная open source утилита для этих целей - migrate. Она поддерживает все наиболее распространённые СУБД. Миграции можно выполнять с помощью готовой библиотеки на Go, либо в консоли через CLI. Я буду использовать CLI, СУБД PostgreSQL и ОС Debian 12.
Для Migrate собран deb пакет, хотя по своей сути это одиночный бинарник. Можно скачать только его. Для порядка ставим из пакета, который берём в репозитории:
Для удобства добавим строку на подключение к локальной базе данных в переменную:
Я подключаюсь без ssl к базе данных test_migrations под учёткой postgres с паролем pass123. С рабочей базой так делать не надо, чтобы пароль не улетел в history.
Принцип работы migrate в том, что создаются 2 файла с sql командами. Первый файл выполняется, когда мы применяем обновление, а второй - когда мы откатываем. В своём примере я добавлю таблицу test01 с простой структурой, а потом удалю её в случае отката.
В директории были созданы 2 файла. В первый файл с up в имени добавим sql код создания таблицы test01:
А во второй с down - удаление:
Проверим, как это работает:
Смотрим, появилась ли таблица:
Вы должны увидеть структуру таблицы test01. Теперь откатим наше изменение:
Проверяем:
Таблицы нет. Принцип тут простой - пишем SQL код, который исполняем. На деле, конечно, изменения бывают сложные, особенно когда не добавляются или удаляются таблицы, а меняется структура существующих таблиц с данными. Инструменты типа migrate позволяют описать все изменения и проработать процесс обновления/отката в тестовых средах. В простых случаях можно обойтись и своими bash скриптами, но migrate упрощает эту задачу, так как, во-первых поддерживает множество СУБД. Во-вторых, автоматически нумерует миграции и исполняет последовательно. В-третьих, может, к примеру, забирать миграции напрямую из git репозитория.
Для каждой СУБД в репозитории Migrate есть примеры настройки миграций.
❗️Если заметка вам полезна, не забудьте 👍 и забрать в закладки.
⇨ Исходники
#postgresql #mysql
Сразу покажу на примере, как это работает. Существует популярная 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 up1/u create_test01_table (24.160815ms)Смотрим, появилась ли таблица:
# psql ${POSTGRESQL_URL} -c "\d test01"Вы должны увидеть структуру таблицы test01. Теперь откатим наше изменение:
# migrate -database ${POSTGRESQL_URL} -path migrations downAre you sure you want to apply all down migrations? [y/N]yApplying all down migrations1/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
Для тех, кто много работает с базами данных, есть простой и удобный инструмент для их проектирования – dbdiagram.io. Мы не разработчики, тем не менее, покажу, как этот сервис может быть полезен. У него удобная визуализация структуры базы данных. Намного удобнее, чем, к примеру, в привычном phpmyadmin.
Можно выгрузить обычный дам из СУБД без данных и загрузить его в dbdiagram. Получите наглядную схему базы. При желании, её можно отредактировать, или просто экспортнуть в виде картинки. Покажу на примере MySQL. Выгружаем дамп базы postfix без данных, только структуру:
Передаём к себе на комп полученный дамп. В dbdiagram.io открываем Import ⇨ From MySQL ⇨ Upload .sql ⇨ Submit. Наблюдаем схему базы данных со всеми связями. Причём она сразу отформатирована по таблицам так, что все их наглядно видно.
При желании можно что-то изменить в структуре и выгрузить новый sql файл уже с изменениями. Если выбрать Export ⇨ To PNG, то сразу же получите аккуратную картинку со схемой, без лишних вопросов. Очень быстро и удобно, если нужно куда-то приложить схему базы данных.
⇨ 🌐 Сайт
❗️Если заметка вам полезна, не забудьте 👍 и забрать в закладки.
#mysql #postgresql
Можно выгрузить обычный дам из СУБД без данных и загрузить его в 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
Расскажу про парочку инструментов, которые упростят обслуживание сервера PostgreSQL. Начну с наиболее простого - pgBadger. Это анализатор лога, который на его основе генерирует отчёты в формате html. На выходе получаются одиночные html файлы, которые можно просто открыть в браузере. Сделано всё аккуратно и добротно, легко настраивается, отчёты наглядные и информационные.
🔹Чтобы было что анализировать, необходимо включить логирование интересующих вас событий. Для разовой отладки это всё можно включать на ходу, либо постоянно через файл конфигурации
Вся включенная статистика стала писаться в общий лог-файл
Анализируем лог файл:
Тут же в директории, где его запускали, увидите файл out.html. Забирайте его к себе и смотрите. Там будет информация с общей статистикой сервера, информация по запросам и их типам, времени исполнения, подключениям, по пользователям, базам и хостам откуда подключались и много всего остального.
PgBadger удобен тем, что по сути это одиночный скрипт на Perl. Можно включить логирование в конфигурации, применить её через
🔹Второй инструмент - PgHero, он показывает примерно то же самое, только в режиме реального времени и работает в виде веб сервиса. Для него уже надо создавать пользователя, настраивать доступ, отдельную базу. Немного другой подход. Надо будет дёргать сервер с СУБД.
Надо перейти в консоль и создать необходимые сущности:
Разрешаем этому пользователю подключаться. Добавляем в
172.17.0.0/24 - подсеть, из которой будет подключаться PgHero. В данном случае это Docker контейнер, запущенный на этом же хосте. PostgreSQL должен принимать запросы с локального IP адреса, к которому будет доступ из Docker сети. Можно добавить в конфиг
Перезапускаем PotgreSQL:
Запускаем PgHero в Docker контейнере:
Идём на порт севера 8080, где запущен контейнер и смотрим информацию о PostgreSQL. Если у вас не настроено расширение pg_stat_statements, которое использует PgHero для сбора статистики, то установите его. Для этого в конфигурацию
Перезапустите Postgresql и выполните в консоли СУБД:
Теперь можно возвращаться в веб интерфейс и смотреть информацию. По умолчанию, пользователь pghero не будет видеть запросы других пользователей, если ему не дать права superuser. Это можно исправить, выдав ему набор прав и ролей из этой инструкции.
❗️Если заметка вам полезна, не забудьте 👍 и забрать в закладки.
#postgresql
🔹Чтобы было что анализировать, необходимо включить логирование интересующих вас событий. Для разовой отладки это всё можно включать на ходу, либо постоянно через файл конфигурации
postgresql.conf и перезапуск сервера. Он обычно хорошо прокомментирован. Вас будут интересовать параметры, начинающие с log_*. Они собраны все в отдельном блоке. Для примера я включил почти всё:log_min_duration_statement = 0log_checkpoints = onlog_connections = onlog_disconnections = onlog_duration = onlog_line_prefix = '%m [%p] %q%u@%d 'log_lock_waits = onlog_temp_files = 0log_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 md5172.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 = allpg_stat_statements.max = 10000track_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
На прошлой неделе смотрел видео и читал статью про веб панель управления бэкапами PostgreSQL. А если точнее, то только бэкапами в виде дампов. Сразу понятно, что это инструмент для небольших баз данных. Большие базы дампами бэкапить неудобно, так как это, во-первых, может длиться непрогнозируемо долго, во-вторых, это всегда только полные бэкапы, а для больших баз хочется инкрементных копий.
Речь пойдёт о Postgresus. Основные возможности:
◽️Веб интерфейс для управления заданиями и отчётами
◽️Расписание бэкапов
◽️Сжатие, автоочистка старых бэкапов
◽️Хранение локально или в S3, Google Drive
◽️Уведомления по Email, Telegram
◽️Запускается в Docker Compose
По сути это замена самописным bash скриптам в кроне. Именно их я и использую для таких задач 🤖 Решил упростить себе задачу. Забегая вперёд, скажу, что эта панель реально заменяет эти скрипты, не более.
Postgresus состоит из двух Docker контейнеров: непосредственно сама панель и локальный экземпляр postgresql сервера для хранения собственных настроек. Запустить можно вручную из предложенного в репозитории файла
Скрипт ставит докер и стартует с этим же docker-compose.yml. Я вручную запустил. После запуска можно сразу идти в веб интерфейс на порт сервера 4005 и там всё настраивать. Не рекомендую запускать подобные вещи непосредственно на сервере с СУБД. Я сделал отдельную небольшую виртуалку и открыл для неё доступ к базам.
Дальнейшее управление осуществляется через веб интерфейс. Там всё максимально просто и наглядно:
1️⃣ Настраиваем хранилище для бэкапов. Я использовал локальную директорию. После тестов примонтирую туда отдельно большой диск.
2️⃣ Добавляем приёмник для уведомлений. Telegram сразу заработал, нужно указать токен своего бота и ID чата. С почтой не получилось, так как отправка по умолчанию настраивается по TLS, а у меня там локальный сервер стоял без него. Перенастраивать не захотелось. Жаль, что автор не оставил такой возможности.
3️⃣ Добавляем подключение к базе данных и настраиваем параметры её бэкапа.
Бэкап выполняется не особо быстро. Скриптами локально я базу на 5ГБ бэкаплю секунд 30. Из соседней виртуалки с помощью Postgresus бэкапится 12 минут. В целом мне некритично, так как для этого сервера бэкапы выполняются раз в сутки по ночам. Времени достаточно, хотя с такой скоростью это растянется часа на полтора.
Архивы хранятся локально в виде дампов со случайными именами вида
Восстановление возможно тут же через веб интерфейс, причём на любой сервер, а не только исходный. Это удобно для ручного тестирования бэкапов. Можно создать тестовый сервер и там периодически проверять дампы или выгружать их для каких-то других задач, не нагружая основной сервер.
В общем и целом панель нормальная. Для небольших серверов будет удобным решением в качестве замены самописных скриптов. Плюс, тут дополнительно работает мониторинг доступности баз данных и уведомления и для бэкапов, и для этого мониторинга. Сделано всё аккуратно и красиво. Посмотрим, как покажет себя в реальной эксплуатации. Мне возможно не будет удобным это решение, так как на скриптах я обычно сразу и на другие сервера передаю бэкапы, и формирую дневные, недельные, месячные наборы для долгосрочного хранения. На базе этой панели это делать неудобно. Возможно, останется как дополнительный инструмент, в котором можно быстро сделать бэкап и восстановить где-то в другом месте.
⇨ 🌐 Сайт / Исходники
❗️Если заметка вам полезна, не забудьте 👍 и забрать в закладки.
#postgresql #backup
Речь пойдёт о 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 и там всё настраивать. Не рекомендую запускать подобные вещи непосредственно на сервере с СУБД. Я сделал отдельную небольшую виртуалку и открыл для неё доступ к базам.
Дальнейшее управление осуществляется через веб интерфейс. Там всё максимально просто и наглядно:
Бэкап выполняется не особо быстро. Скриптами локально я базу на 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
Вчера рассмотрел Postgresus - веб панель для бэкапов баз Postgresql. У неё есть очень близкий и более старый аналог – PG Back Web. Панели похожи по функциональности и архитектуре. Автор Postgresus наверняка знаком с ней, потому что многие вещи реализованы схожим образом. Но есть и отличия. Каждый, судя по всему, реализовал настройки и управление так, как ему казалось более удобным. Расскажу по порядку.
1️⃣ Установка выполняется так же, как и у Postgresus – готовый
2️⃣ В Pgbackweb сущности в виде баз данных и заданий для бэкапа разделены. Отдельно добавляется база, отдельно для неё создаётся задание для бэкапа. Соответственно, для одной и той же базы могут быть созданы разные задачи с бэкапом и параметрами хранения. В Postgresus настройки базы и бэкапа для неё объединены в одну сущность.
3️⃣ Создаваемый в Pgbackweb дамп тут же на лету жмётся в zip. А в самом архиве лежит чистый дамп в текстовом формате, который можно открыть, посмотреть, изменить. Создание дампа в Pgbackweb примерно в 2-2,5 раза дольше, чем у Postgresus. Последний жмёт средствами самого pg_dump, если я правильно понял из исходников, то есть использует ключи
К сожалению, обе панели не дают возможность самому задавать эти параметры. А иногда бывает нужно либо так, либо эдак. Например, если тебе важна скорость, то лучше жать встроенными средствами. Но из такого дампа потом неудобно извлекать отдельную таблицу, если она понадобится. А из текстового дампа это сделать очень просто. Лично я, когда создаю дампы баз данных, выгружаю их в обычном текстовом формате и жму сам с помощью pigz в многопоточном режиме. Получается и быстро, и удобно. Потом распаковал и делай с ним, что хочешь.
4️⃣ Pgbackweb предлагает сохранять бэкапы либо локально, либо в S3. Больше у неё ничего нет для хранения. Формат путей вида
5️⃣ Сохранённый дамп можно самому скачать и восстановить вручную, либо воспользоваться веб интерфейсом. Базу можно восстановить как в исходную, так и в другую, в том числе на другом сервере. Здесь возможности обеих панелей совпадают.
6️⃣ В качестве уведомлений Pgbackweb предлагает только вебхуки, что слабовато по сравнению с готовой интеграцией с Telegram, Email, Slack, Discord в Postgresus.
В общем и целом панели сильно похожи и выполняют одни и те же задачи. Разница в некоторых мелочах, которые кому-то могут быть критичны, кому-то нет. Мне лично больше понравилась Postgresus за её внешний вид и внутреннюю логику. Сжатые дампы делает намного быстрее, что уже на базах в 5-10 ГБ становится критично. Так как, к примеру, 15 минут против 35 для 10 ГБ – это существенная разница.
⇨🌐 Исходники / ▶️ Видеобзор
❗️Если заметка вам полезна, не забудьте 👍 и забрать в закладки.
#postgresql #backup
docker-compose.yaml в репозитории для запуска двух контейнеров: сама веб панель и локальная Postgresql для сохранения настроек и состояния. Достаточно запустить docker compose и можно идти на порт 8085 сервера и пользоваться панелью. В консоли больше делать нечего.-Fc (custom-format) -Z 6 (compression level). К сожалению, обе панели не дают возможность самому задавать эти параметры. А иногда бывает нужно либо так, либо эдак. Например, если тебе важна скорость, то лучше жать встроенными средствами. Но из такого дампа потом неудобно извлекать отдельную таблицу, если она понадобится. А из текстового дампа это сделать очень просто. Лично я, когда создаю дампы баз данных, выгружаю их в обычном текстовом формате и жму сам с помощью pigz в многопоточном режиме. Получается и быстро, и удобно. Потом распаковал и делай с ним, что хочешь.
/backups/dbase01/2025/07/21. Это удобно, если захочется забирать готовые дампы куда-то ещё и складывать их в архивы по каким-то датам. В 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
Раз уж я рассмотрел панели для управления бэкапами (Postgresus и Pgbackweb) в виде дампов в Postgresql, было бы логично рассмотреть и вариант со своими велосипедами на bash. Поделюсь тем, что использую я.
Сначала полный текст скрипта для бэкапа:
Рассказываю, что тут происходит:
1️⃣ Завожу в переменную BASES список баз, которые мы будем бэкапить. Их можно указывать вручную, примерно так:
Можно использовать обратный подход. Бэкапить все базы, но вручную или по маске задавать исключения. Примерно так:
2️⃣ База дампится с помощью
3️⃣ В дампе проверяем наличие строки
Соответственно, если строки присутствуют, пишем в лог файл, что Backup is OK, если есть проблемы, то Backup is corrupted. Дальше этот файл забирает либо мониторинг, либо хранилка логов. И если они видят фразу corrupted, то срабатывает триггер.
4️⃣ Если с дампом всё в порядке, то он жмётся архиватором
Получается автоматическая максимально простая и эффективная схема работы. Я вчера добавлял базы в веб панель. Честно говоря, меня это утомило. Если баз 1-2, то не проблема. А 15 штук добавлять уже надоедает. Бывает и больше. Плюс, надо вручную следить за ними, добавлять, отключать ненужные и т.д. Так что панель вроде и выглядит удобно, но если баз много, то удобства уже сомнительные.
Дальше у меня работает ещё один простой скрипт, который забирает эти дампы на тестовый сервер, распаковывает и заливает в СУБД. Расскажу о нём в следующей заметке.
❗️Если заметка вам полезна, не забудьте 👍 и забрать в закладки.
#postgresql #backup #script
Сначала полный текст скрипта для бэкапа:
#!/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
Рассказываю, что тут происходит:
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}'`pg_dump без каких-либо дополнительных параметров. На выходе имеем обычный несжатый текстовый дамп.-- PostgreSQL database dump в начале дампа и -- PostgreSQL database dump complete в конце. Поэтому нам важно иметь дамп в текстовом формате. Если процесс снятия дампа прошёл без ошибок и в дампе присутствуют эти строки, то с большой долей вероятности с ним всё в порядке. Я ни разу не сталкивался с обратной ситуацией.Соответственно, если строки присутствуют, пишем в лог файл, что Backup is OK, если есть проблемы, то Backup is corrupted. Дальше этот файл забирает либо мониторинг, либо хранилка логов. И если они видят фразу corrupted, то срабатывает триггер.
pigz. Он работает многопоточно всеми доступными ядрами процессора. Если не хотите нагружать так плотно сервер, то количество потоков можно ограничить ключами. Я обычно снимаю дампы ночью и жму на всю катушку, чтобы побыстрее завершилось. Дампы потом ещё передавать надо. Это уже отдельная процедура и запускается с сервера бэкапов. С самого сервера СУБД к бэкапам доступа нет. Получается автоматическая максимально простая и эффективная схема работы. Я вчера добавлял базы в веб панель. Честно говоря, меня это утомило. Если баз 1-2, то не проблема. А 15 штук добавлять уже надоедает. Бывает и больше. Плюс, надо вручную следить за ними, добавлять, отключать ненужные и т.д. Так что панель вроде и выглядит удобно, но если баз много, то удобства уже сомнительные.
Дальше у меня работает ещё один простой скрипт, который забирает эти дампы на тестовый сервер, распаковывает и заливает в СУБД. Расскажу о нём в следующей заметке.
❗️Если заметка вам полезна, не забудьте 👍 и забрать в закладки.
#postgresql #backup #script
Please open Telegram to view this post
VIEW IN TELEGRAM
4👍161👎3
Утром рассказал, как делаю бэкапы баз Postgresql с помощью скриптов и
Первым делом надо забрать дампы с исходного сервера. Для этого беру простой скрипт, который забирает по SSH только свежие файлы за прошлый день. Если забрать сразу только нужные файлы, то потом не придётся делать в них выборку:
Использую rsync, а список файлов для копирования формирую сразу в выражении ключа, подключившись к нужному хосту по SSH и отсортировав файлы по дате, взяв не старше суток. В данном случае файл timestamp через
Ну а дальше дело техники. По очереди выполняю набор команд для удаления старой базы, создания новой, распаковки и заливки дампа. Перед первым запуском этого скрипта вручную создаю все восстанавливаемые базы и выборочно проверяю восстановление. Потом формирую итоговый список команд:
Сразу говорю, что я тут ничего не оптимизировал, никаких проверок не делал. Если просто взять и запустить, то скорее всего у вас вывалятся какие-то ошибки или что-то пойдёт не так. Я просто показываю идею. У меня уже давно и структура каталогов одна и та же, и подход к именованию баз, бэкапов и т.д. Это всё сделано под меня и работает так уже много лет. Меня устраивает.
В данном скрипте нет никаких проверок успешной работы и оповещений, потому что это не последний этап проверок. Далее в зависимости от того, где эта база используется, запускаются свои проверки. Для баз 1С выполняется автоматическая выгрузка в dt с помощью отдельного скрипта. Если она проходит успешно, то считается, что дамп базы живой и нормально восстановился.
Если же это база от веб проекта, то параллельно из другого бэкапа восстанавливаются исходники в тестовый веб сервер, подключается восстановленная база и работает стандартная веб проверка в Zabbix Server, которая ищет какие-то данные на странице, которые без работающей базы не будут показаны. Если веб проверки проходят успешно, то считается, что база восстановилась, да и в целом бэкап живой.
Более сложные проверки в восстановленных базах я не делаю. Мой опыт в обслуживаемых инфраструктурах показывает, что этого достаточно. Ни разу не сталкивался, что всё прошло успешно, а с базой проблемы. Обычно проблемы возникают сразу в снятии дампа. Если он корректно выполнен, то дальше уже все отрабатывает нормально.
❗️Если заметка вам полезна, не забудьте 👍 и забрать в закладки.
#backup #postgresql #script
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
Telegram
ServerAdmin.ru
Раз уж я рассмотрел панели для управления бэкапами (Postgresus и Pgbackweb) в виде дампов в Postgresql, было бы логично рассмотреть и вариант со своими велосипедами на bash. Поделюсь тем, что использую я.
Сначала полный текст скрипта для бэкапа:
#!/bin/bash…
Сначала полный текст скрипта для бэкапа:
#!/bin/bash…
👍108👎4
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 или куда-то ещё, то просто измените переменную
Надо убрать ключ
Это, как я понял, связано с тем, что ключ
И ещё важный момент. Не забудьте сохранить пароль от репозитория, который тоже задаётся в скрипте. Так как пароль передаётся в виде переменной окружения, его можно хранить где-то отдельно и подгружать перед запуском основного скрипта. Я в предыдущей заметке про restic упоминал об этом и показывал пример в конце.
Сам скрипт прикрепил к сообщению. Также продублировал его в публичный репозиторий. Не забудьте поменять переменные под свои нужды и окружение.
Полезные материалы по теме:
▪️Описание Restic с примерами
▪️Мониторинг Restic с помощью Zabbix и Prometheus
▪️Бэкап с помощью Restic в S3 на примере Selectel
Restic очень удобный инструмент для бэкапов. Рекомендую присмотреться.
❗️Если заметка вам полезна, не забудьте 👍 и забрать в закладки.
#backup #postgresql #restic
Вновь возвращаюсь к очень удобному инструменту для бэкапов restic. Последнее время часто стал его использовать. Дальше вы поймёте, почему. На создание бэкапов баз данных с его помощью меня навела вот эта статья:
⇨ Restic: эффективное резервное копирование из Stdin
Автор с помощью restic дампит базы данных в Kubernetes. Я немного переделал его решение для своих локальных бэкапов. Основная проблема, которая там решена - корректная отработка ошибок снятия дампа. Я до этого сам не догадался, правда не сильно и пытался, поэтому дампил по старинке сначала в файл, проверял, а потом уже куда-то на хранение отправлял.
Но можно всё это сделать на лету и с проверкой ошибок. То есть делается дамп и тут же через pipe отправляется в restic. Этот способ удобен по нескольким причинам:
В статье у автора подробно всё рассказано, не буду на этом останавливаться. Покажу свой итоговый скрипт, который бэкапит дампы условно локально, а не в 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👍109👎1