Comments 82
А есть софт, который в каком-нибудь гуе позволяет натыкать бэкапы - расписание, откуда забирать, куда класть и т.д.?
Хотел бы ответить на вопрос, но не совсем понимаю, что такое "гуе".
К сожалению не могу сказать есть ли такой софт, сам не искал.
Однако в статье написано как можно изменить расписание создания бэкапов под свои нужды.
Также в статья я объяснил где указать папку, в которую складываются бэкапы и как эту папку синхронизировать с облаком Yandex.
Конечно есть софт, в котором всё уже продумано и сделано вместо костылей на коленке, в том числе бэкапы БД: AWS, GCP, Azure
Рубекап вроде допилили функционал, там и гуя и выбор куда складывать и все что хочешь
Честно, не знаю многое ли поменялось - но года 2 назад использовал связку PGBarman (https://pgbarman.org/) и какой-то WEB-GUI с Github для него. Устраивало
А вы пробовали проверять, восстанавливается ли из него база? У pg_dump много ключиков, от которых зависит, что будет забекаплено.
Продолжая тему, рекомендую сделать скрипт по разворачиванию базы из бекапа. Чтобы, если что-то случится, не тратить время и развернуть запуском одного скрипта.
Вот тоже об этом подумал: "если у Вас настроены бэкапы, но нет регулярной проверки разворачивания БД из бэкапов - то у Вас нет бэкапов"
Да, восстановливать пробовал, - получается. Но автоматического восстановления не настроено, хотя действительно неплохо бы было проверять возможность восстановления из бэкапа, время от времени. Просто я старался сделать предельно простую систему, при этом справляющуюся со своей задачей, и база данных с которой делают dump, в моем случае была минимальных размеров ~ 100 Mb.
На какой предельный размер базы рассчитан данный скрипт, чтобы получить приемлемую скорость бекапа и выгрузки в облако? Не убьет ли производительность бд запуск бекапирования? Не ляжет ли сеть при копировании в облако? ну и тд.
Возможно лучше подобрать свое решение тут 10 способов сделать резервную копию в PostgreSQL.
В моем случае база данных умещалась в ~ 100 Mb, и с этим размером скрипт работает прекрасно, к сожалению на больших БД, скрипт не тестировал.
Про этот скрипт не скажу, у меня самописный скрипт ежедневно бэкапит базы по 10ГБ и жмет их pgzip. На выходе файл весом примерно 1ГБ. На бекап такой базы, судя по логам, уходит примерно полторы минуты.
Я извиняюсь что прочел не всё, но по моему для всего более чем хватает стандартного pg_dumpall + немного bash кода.
Да, все верно. вот мой код для бэкапа на bash, пользуйтесь если нужно, но только пути поменяйте и имя базы данных:
#!/bin/bash
# Создание backup файла для SQL базы данных для сайта
#
# Потом добавить этот файл в cron для root пользователя, вот эти 2 строки:
# # в полночь 00:01 создается backup файл для SQL базы данных для сайта
# 1 0 * * * /var/lib/postgresql/backup/backupSQL.sh
#
cd /var/lib/postgresql/backup # в этой папке лежит этот скрипт
dateBackup="`eval date +%Y.%m.%d_%H-%M`" # Дата создания бэкапа будет в названии файла
sudo -u postgres pg_dump name_DB_need_change | gzip -9 > backup_DB_$dateBackup.sql.gz # Делаем бэкап для базы данных, имя базы данных поменять
chmod -R g-rwX,o-rwX backup_DB_$dateBackup.sql.gz
# папка и пользователь должны быть созданы заранее
mv backup_DB_$dateBackup.sql.gz /home/sftp/sftp-Share-File/backup_DB_$dateBackup.sql.gz
chown -R sftp:users /home/sftp/sftp-Share-File/backup_DB_$dateBackup.sql.gz
chmod -R u+r,g+r,o-rwX /home/sftp/sftp-Share-File/backup_DB_$dateBackup.sql.gz
Код делает копию базы данных в специальную папку для "sftp" пользователя, потом из этой папки я забираю файлы на домашний компьютер используя pytty, вот .bat файл добавленный в автозагрузку:
@echo OFF
cd c:\soft\PuTTY\pSFTP\
rem "------ Начинаем скачивание данных с сервера ------"
rem -batch -2 -i - параметры запуска, описание можно посмотреть тут: http://the.earth.li/~sgtatham/putty/0.78/htmldoc/Chapter6.html
rem -l - Имя пользователя
rem -P - Это порт ssh
rem -b - файл, содержащий пакетные команды
rem -b auto_Backup.scr - файл с набором команд для Unix сервера
psftp -batch -2 -i ..\ssh_key\sftp_VPS_priv.ppk -b auto_Backup.scr -P 22 -l sftp 192.168.100.100
и файл auto_Backup.scr:
# Переход в удаленную папку на VPS'e
cd /home/sftp/sftp-Share-File/
# Копирование файлов к себе в папку \Share-File\SQL\ на домашнем компьютере
lcd .\Share-File\SQL\
mget backup_DB*
# Удаление скопированных SQL файлов на удаленном сервере
rm backup_DB*
# Завершение сессии
bye
Спасибо, попробую протестировать!
Дополню сюда так же маленькой полезной строчкой, чтобы не гуглить и не переходить по первой ссылке.
Удаление старых файлов бэкапов. В данном примере удаляет файлы старше 2х дней. В параметр -name поподставить свою маску, я просто упаковываю несколько бэкапов, т.к. в кластере несколько БД.
find . -name "*.tar.gz" -type f -mtime +2 | xargs rm;
В данном примере удаляет файлы старше 2х дней.
потом время глюкнет (или скрипт бэкапов перестанет выполняться) и останетесь без ничего. поэтому файлы удалять только по счету.
Стало даже интересно, у вас бывали такие случаи? Из-за чего?
Какой вариант автоматизации скрипта "по счету" вы предлагаете?
да, бывали.
глюк со временем - 1 раз, описал в https://habr.com/ru/news/779588/comments/#comment_26250354
остановка скрипта - 1 раз. причина - утечка памяти "где-то", общее торможение сервера. решение - останов всего что можно, перезагрузка, обновление всего что можно, перезагрузка.
вариант по счету такой:
1) все файлы бекапов называются так, чтобы при простой сортировке самые старые были внизу. в этом нам поможет
DATEFMT="+%Y%m%d_%H%M%S"
BACKUP_START_DTTM=$( date ${DATEFMT} )
2) скрипт-удалятор работает отдельно и желательно на самой хранилке, он же может откидывать важные бэкапы (месячные-квартальные-годовые) в другое место чтобы не удалять их никогда. он же может откидывать вообще все в другое место чтобы туда не было доступа у скрипта резервного копирования.
3) само удаление:
LIMIT=$(( 366 *3 ))
# три года срок исковой давности по гражданским делам
cd $BACKUP_STORE_DIR
NFILES=$( ls -1 | grep -i $DBNAME | wc -l )
if [ $NFILES -le $LIMIT ]; then
# do nothing
else
cd $BACKUP_STORE_DIR
N_RM=$(( $NFILES - $LIMIT ))
RM_THIS=$( ls -1 | grep -i $DBNAME | sort | tail -$N_RM ))
rm -- $RM_THIS
fi
Хм... да, кейс интересный конечно со временем у вас и вполне реален, у нас тоже периодически рубят доступ извне и т.п.
Но я пожалуй безответственно оставлю это на сисадминов, которые что-то шатают :))))
Тоже был похожий кейс - crontab, по времени бекапа называются файлы, нужно сохранять только последние по времени 12 файлов. Решил следующим пайплайном:
ls $BACKUP_PATH | grep ".tar.gz" | sort -r | awk 'NR > 12' | xargs rm
Да, к сожалению я не нашёл готового решения своей задачи, поэтому решил сделать простенький велосипед.
Тут вы или лукавите, или вам стоит прокачать скилл гугления. Смотрите, делаете вот так: https://www.google.com/search?q=postgresql+backup+script
И в первых же строчках получаете инструкцию от самого postgresql:
https://wiki.postgresql.org/wiki/Automated_Backup_on_Linux
От себя хочу добавить - в вашей профессии очень важно уметь сначала гуглить, а потом писать свой велосипед. Обычно куча людей уже получили такую же задачу, как вы, и выложили решение в интернет. Да еще и протестировали. А такая вещь, как бекап БД - попробуйте сами оценить, сколько людей в последние 20-30 лет с этим сталкивались)
в нагугленом не будет телеграм-бота хехе.
лично я начинал от 1Сников: https://infostart.ru/1c/articles/956734/
(внимание на отдельный дамп самой большой таблицы).
и по ссылкам оттуда https://www.sinyawskiy.ru/relation_not_exist.html
(полезно дамп схемы сделать отдельно).
С точки зрения безопасности, чтобы вообще не указывать пароли доступа к БД в конфигурационных файлах, резервное копирование лучше запускать под postgres из его cron файла.
Они там все из переменных среды берутся
Какая разница? В переменную пароль все равно нужно положить. В Вашем случае он вообще там в открытом виде. А если запускать такие задания под postgres, то у него даже пароля может не быть. Как и возможности войти под ним интерактивно.
Если уже совсем вариантов нет (необходим доступ с удаленного компьютера), то пользуйтесь хотя бы keytab и kerberos авторизацией.
Все секреты указываются в .env файле, это относительно безопасно, хотя далеко не самый безопасный вариант.
Как решается удаление и очистка корзины на Яндекс диске? Раньше cli этого не умел.
Да умеет через curl
Удаление прописано в скрипте, а yandex disk лишь копирует содержание папки.
YsDisk не удаляет заменённые файлы, а переносит их в корзину, и место очень быстро кончается. Речь об этом.
Понял, пока не рассматривал это как проблему, - наоборот есть возможность восстановить удаленный файл, + за пару кликов всегда можно почистить БД, если место кончается.
Хотя у меня и БД небольшая, поэтому в облако помещается 50-100 бэкапов, которые нужно раз в 50-100 дней чистить, - для меня приемлимый вариант.
Помимо того что есть готовые инструменты или простые скрипты с pgdumpall или pgdump данный инструмент ничем не лучше.
Если брать за условие слабую СХД, на которой постгря крутится или вообще кластер, где требуется снять влияние с мастера, а то и вовсе убрать фриз таблиц, то решение слабое.
В таком важном деле я бы не доверял самописным скриптам. Нисколько не умаляю вашу работу.
но лучше проверенные коммерческие решения. Там же потом и мониторинг, и удаление старых, и failback - скопировать на другое хранилище, если первое не отвечает.
Свои небольшие но важные БД я бэкаплю через sqlbackupandftp (виндоус) и sqlbak (линукс)
Лично я не очень люблю схемы бекапов, когда отдающая сторона имеет права записи в каталог с предыдущими бекапами. Конкретно в статье этот единый скрипт и запускает pg_dump, и ротацию делает.
И тогда, если у вас подбирают пароль к ОС отдающей стороны, то заодно можете и все бекапы разом потерять.
Имхо, гораздо лучше работает схема, когда забирает бекапы именно принимающая сторона, она же и делает ротацию. Либо как вариант append-only хранилища.
И яндекс-диск в режиме синхронизации тут, мне кажется, слабо поможет. Он также отсинхронизирует и удаление ваших бекапов злоумышленником.
Справедливо, действительно при удалении файлов на сервере они удаляются и в Yandex disk, такая проблема с хранением бэков присутствует, вероятно мне ещё стоит поискать способы её решения.
В качестве альтернативы может и сервер сам отдавать бекапы, а вот на принимающей стороне уже скрипт по ротации и по блокированию доступа к записанным ранее бекапам.
Pgbackrest отличный инструмент для бекапов. Полный или инкрементальный делаю периодически через cron
Хорошая статья, но к сожалению пока не наткнешься на команду sudo... Не поймёшь, на операционной системе которой всю эту процессию выполняешь...
Хотелось бы так же для оболочек Windows, в powershell или CMD.
Понял, добавлю упоминание Linux в статье, может когда нибудь руки дойдут написать для Windows.
А смысл есть? PostgreSQL под Windows, по объективным причинам, для продуктивного использования мало пригоден. А поиграться можно и под WSL2.
в этом питоновском доброкоде еще и sudo есть ?? прекрасно.
для винды любые нужные шеллы вам даст cygwin итли msys2.
cygwin с некоторыми фокусами позволит запустить скрипт от имени LocalSystem (S-1-5-18), msys2 я не проверял. а пользователя круче S-1-5-18 на винде нет.
впрочем, есть и sudo для Windows от китайских хакеров.
Для windows могу порекомендовать установить два модуля
https://github.com/SAGSA/DbBackupControl
Скрипт для powershell в самом простом случае будет выглядеть так
#Делаем копию всех баз в каталог C:\SQLBakup
Get-PgDatabase | Backup-PgDatabase -Destination "C:\SQLBakup"
#Оставляем только 7 последних версий
Remove-DbBackup -Path "C:\SQLBakup" -KeepVersions 7
Спасибо за работу, за скрипт.
Восстановление бекапа куда подразумевается? Поясню мысль: есть оперативный бекап, например раз в час. Есть ежедневный, еженедельный. Вот приходит к Вам тикет на восстановление двухчасовой копии для анализа ошибки, в стороне от работающей базы, но с возможностью перезаливки трех таблиц из восстановленной в прод. Как решите?
Поэтому, рекомендовал бы в скрипте сразу решить несколько вопросов:
Восстановление копий из архива
Чистка старых копий, например по схеме с понедельника по субботу по старше двух месяцев удаляем, оставляем воскресенье , старше трёх месяцев оставляем только на 21 число каждого месяца
Бекап накопленных бекапов за год в один файл.
Я вот смотрю там tar вызывается. А постгре умеет сразу писать компрессированный бэкап? Это очень важно когда база например 80 терабайт (с ужасом думает о том, что нас заставят переезжать с MSSQL на Постгре)
А такую БД надо в --format=directory бекапить, причем в несколько потоков (--jobs=...). 3 ТБ у меня в 16 потоков бекапятся в GZIP (по умолчанию) за полчаса. С Вашими объемами, лучше будет все же --compress=lz4, чтобы резервное копирование успело завершится за адекватное время.
Может. Я решаю иначе-клонирую файлы базы данных на zfs, там же и сжимаю. Из плюсов: не грузит основной сервер. Резервный сервер подхватывает бекапированнве файлы при старте. Быстрое восстановление копированием на случай сбоя основного сервера. (Это к вопросу ежечасовых или еже10минутных дампов)
С mssql другие проблемы, связанные с текущей оптимизацией запросов mssql, которая начала появляться в 14 версии postgres по-моему. В предыдущих релизах были проблемы с 1С точно на закрытии месяца
Переходите смело, у меня 170 Гб mssql превращается в девушку контуре в 140 Гб без потери
Ужас там в другом
Красивая девушка? И в чем ужас?
Минусов в таком решении все же больше, чем плюсов. Если невозможность выборочного восстановления баз ещё можно пережить, то деградация производительности из-за расположения WAL на той же файловой системе, что и данные - это уже печально.
А постгре умеет сразу писать компрессированный бэкап?
нет. это не UNIX way. через пайп в xz или lz4 или zstd и на удаленную файловую систму (nfs, sshfs, smbfs).
а на MS SQL вы занимались компрессией данных ?
dba.stackexchange.com/questions/8132/compact-large-objects-in-reorganize-index-task
последнее мне помогало ускорить бэкап и сократить его размеры. возможно и первое поможет.
Да, у меня это уменьшает размер где то в два раза. Правда есть подводные камни с переключении разделов и длительностью rebuild, хоть это и делается с опциями ONLINE и RESUMABLE
Через пайп будет в один поток, что приемлемо только для БД до терабайта размером. Поэтому лучше делать резервное копирование в формат директории в несколько потоков и со встроенным сжатием
Умеет. И степень сжатия выставить можно и многое другое.
1) непонятен выбор питона как скриптового языка (больше него жрет только руби). или все приложение строилось как враппер для телеграм-бота ?
2) непонятно, почему автор буквально понял требование заказчика: "1, 2, 3 дня, 1 неделя, 1 месяц, 6 месяцев" и бросился его реализовывать. дамп (будем откровенны сами с собой - это именно дамп а не бэкап) всех баз один раз в сутки с хранением 3 года перекрывает все потребности с тройным запасом и значительно упрощает логику.
3) непонятен выбор работы с яндекс-диском через мутный фирменный бинарник вместо WebDAV. проблемы с WebDAV ? если яндекс-диск оплачен, то пусть уже техподдержка напряжется. есди яндекс-диск бесплатный, то тем хуже для него.
4) не приняты меры против bit rot. это может быть как просчет контрольной суммы файла (и переименования файла), так и что-то поинтереснее , например par2 (по следам duplicity).
непонятен выбор работы с яндекс-диском через мутный фирменный бинарник вместо WebDAV. проблемы с WebDAV ?
Насколько я знаю, у Яндекс-диска WebDAV только на чтение, на запись там что-то около некольких мегабайт можно
Я хотел сделать простое решение для небольшой БД и насчёт оптимизации не особо беспокоился, для БД ~ 100Mb скрипт проходит почти мгновенно, за 1-2 секунды.
Кроме дампа БД psql, также сохраняется папка с media контентом, а затем 2 файла архивируются в 1 бэкап.
Остальные замечания принял к сведению.
Какая разница на чем писать скрипт, если издержки на сам интерпретатор этого скрипта - доли процента? Кому то удобней Bash, кому-то Perl (я иногда такой), кому-то PowerShell, автору - Python. Тут явно не та задача, где выбор языка играет роль. А вот если в перспективе переводить это на AirFlow, то Python окажется к месту.
Лить бэкапы в (чужое) облако без шифрования - дурной тон
Классная статья. Спасибо
Ох, да((( Сейчас бы в 23 году создавать бэкапы дампом, да ещё и запускать из крона и хранить все креды в открытом виде. Бегите из этой компании.!
А какая альтернатива?
Просто предположим есть у меня сервер, как мне безопасно хранить на нём свои креды?
Ну, я могу просто положить их в файл, а могу, например, развернуть Vault и хранить там, но я же всё равно должен положить api ключик для доступа к Vault.
Я могу, конечно шифровать файл с кредами или api ключ, но я все равно где-то должен хранить ключ для расшифровки.
И получается, что использование Vault не делает наши пароли более защищёнными, только косвенно, потому что мы можем быстрее их ротировать, быстрее обновлять и предоставлять в едином виде для сервисов.
P.S. В данном конкретном случае, конечно, есть вариант получше - запускать скрипт от пользователя, который авторизуется через "peer" метод.
А какая альтернатива?
industry-level enterprise-grade production-ready решения (т.е. те которые про себя на своем сайте так написали) за много денег.
Варианты? Да их масса, сперва не плохо бы использовать готовые решения типа wal-g например, а не городить свои скрипты. И складывать бэкапы в s3. Вызывать wal-g из ci в контейнере например. Креды или в vault, или можно через ansible vault если не хочется возится с контейнерами. В целом вариантов масса.
Я лишь хотел сказать, что предложенное решение совсем не продпкшен реди для 2023 года.
Есть тула от разработчиков Postgres Pro - pg_probackup, в ней есть все функции из статьи + инкрементные бекапы. Для продакшена я бы лучше её использовал.
Автоматические бэкапы БД PostgreSQL по расписанию