Как стать автором
Обновить

Комментарии 82

А есть софт, который в каком-нибудь гуе позволяет натыкать бэкапы - расписание, откуда забирать, куда класть и т.д.?

GUI

К сожалению не могу сказать есть ли такой софт, сам не искал.
Однако в статье написано как можно изменить расписание создания бэкапов под свои нужды.
Также в статья я объяснил где указать папку, в которую складываются бэкапы и как эту папку синхронизировать с облаком 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 файле, это относительно безопасно, хотя далеко не самый безопасный вариант.

Это вообще небезопасно. Недопустимо хранить какие-либо пароли на сервере.

теоретически я согласен с вами, но какие будут предложения на практике ?

один метод я знаю - Hashicorp Vault и брать секреты для доступа в БД из него.

а где хранить или откуда брать секреты для доступа к Vault ?

Я же писал выше. Либо работайте с PostgreSQL локально под его же аккаунтом (postgres), либо удаленно через keytab и kerberos авторизацию.

Как решается удаление и очистка корзины на Яндекс диске? Раньше 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 могу порекомендовать установить два модуля

  1. https://github.com/SAGSA/PostgresCmdlets

  2. https://github.com/SAGSA/DbBackupControl

    Скрипт для powershell в самом простом случае будет выглядеть так

    #Делаем копию всех баз в каталог C:\SQLBakup

    Get-PgDatabase | Backup-PgDatabase -Destination "C:\SQLBakup"

    #Оставляем только 7 последних версий

    Remove-DbBackup -Path "C:\SQLBakup" -KeepVersions 7

Спасибо за работу, за скрипт.

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

Поэтому, рекомендовал бы в скрипте сразу решить несколько вопросов:

  1. Восстановление копий из архива

  2. Чистка старых копий, например по схеме с понедельника по субботу по старше двух месяцев удаляем, оставляем воскресенье , старше трёх месяцев оставляем только на 21 число каждого месяца

  3. Бекап накопленных бекапов за год в один файл.

Восстановление подразумевается до предыдущего дампа с корректными данными, на случае какой-либо ошибки сломавшей БД. К сожалению насчёт более сложных задач не могу точно сказать.

Я вот смотрю там tar вызывается. А постгре умеет сразу писать компрессированный бэкап? Это очень важно когда база например 80 терабайт (с ужасом думает о том, что нас заставят переезжать с MSSQL на Постгре)

А такую БД надо в --format=directory бекапить, причем в несколько потоков (--jobs=...). 3 ТБ у меня в 16 потоков бекапятся в GZIP (по умолчанию) за полчаса. С Вашими объемами, лучше будет все же --compress=lz4, чтобы резервное копирование успело завершится за адекватное время.

Может. Я решаю иначе-клонирую файлы базы данных на zfs, там же и сжимаю. Из плюсов: не грузит основной сервер. Резервный сервер подхватывает бекапированнве файлы при старте. Быстрое восстановление копированием на случай сбоя основного сервера. (Это к вопросу ежечасовых или еже10минутных дампов)

С mssql другие проблемы, связанные с текущей оптимизацией запросов mssql, которая начала появляться в 14 версии postgres по-моему. В предыдущих релизах были проблемы с 1С точно на закрытии месяца

Переходите смело, у меня 170 Гб mssql превращается в девушку контуре в 140 Гб без потери

Ужас там в другом

Красивая девушка? И в чем ужас?

Да, смартфон отжег. Под дождем дописывал, видимо глядя на девушек. Читать так:

"Переходите смело, у меня 170 Гб mssql превращается в 140 Гб без потери на postgres 15
Ужас там в другом: для 1С это медленное закрытие месяца в версиях, по-моему ниже 14, нет оптимизации запросов"

А почему такой маленький коэффициент упаковки? Сиквель жмёт свои бэкапы на лету быстро и плохо, но это как правило 2-3 раза по сравнению с непожатым

Это не жатый, это развернутый. На Postgres после autovacuum происходит магия и база становится меньше

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

А постгре умеет сразу писать компрессированный бэкап?

нет. это не UNIX way. через пайп в xz или lz4 или zstd и на удаленную файловую систму (nfs, sshfs, smbfs).

а на MS SQL вы занимались компрессией данных ?

learn.microsoft.com/en-us/sql/relational-databases/data-compression/data-compression?view=sql-server-ver16

dba.stackexchange.com/questions/8132/compact-large-objects-in-reorganize-index-task

последнее мне помогало ускорить бэкап и сократить его размеры. возможно и первое поможет.

Да, у меня это уменьшает размер где то в два раза. Правда есть подводные камни с переключении разделов и длительностью rebuild, хоть это и делается с опциями ONLINE и RESUMABLE

тогда даже и не знаю. остается только старые данные удалять. или переносить на спец. сервер для старых данных.

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

на сервере БД может не быть свободного места для откладывания временного файла, что и происходит при --format=directory

А с чего вы взяли, что ему нужен временный файл? Даже для toc он не нужен. А уж для таблиц и blob - тем более.

Умеет. И степень сжатия выставить можно и многое другое.

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, в ней есть все функции из статьи + инкрементные бекапы. Для продакшена я бы лучше её использовал.

Зарегистрируйтесь на Хабре, чтобы оставить комментарий

Публикации

Истории