В этой статье я поделюсь скриптом для создания бэкапов БД PostgreSQL за определенный период (например: 1, 2, 3 дня, 1 неделя, 1 месяц, 6 месяцев, каждый год).
Объясню как запустить скрипт с помощью расписания crontab, покажу как настроить синхронизацию папки с бэкапами с облаком Yandex Disk.

TL;DR

  1. Ссылка на репозиторий с python скриптом для создания бэкапов:
    https://github.com/oktend/server-backup-example

  2. Добавление расписания в crontab:

sudo apt-get update
sudo apt-get install cron
crontab -e
0 3 * * * cd /home/user/server-backup-example; /home/user/.local/bin/poetry run python src/main.py
  1. Синхронизация с Yandex Disk

echo "deb http://repo.yandex.ru/yandex-disk/deb/ stable main" | sudo tee -a /etc/apt/sources.list.d/yandex-disk.list > /dev/null && wget http://repo.yandex.ru/yandex-disk/YANDEX-DISK-KEY.GPG -O- | sudo apt-key add - && sudo apt-get update && sudo apt-get install -y yandex-disk
yandex-disk setup

Задача

Меня зовут Вадим Резвов, я начинающий системный администратор.

В мои обязанности входит: мониторинг состояния всех основных сервисов и систем проекта, создание бэкапов, поддержка ключевых компонентов сервера.

В одном из моих текущих проектов возникла задача - создавать регулярные бэкапы БД PostgreSQL за определенные отрезки времени (1, 2, 3 дня, 1 неделя, 1 месяц, 6 месяцев, каждый год - такое расписание я сформировал по запросу заказчика, чтобы была возможность откатиться на короткий, средний или длинный срок), также бэкап включает в себя папку с media и static контентом.

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

В зависимости от того насколько давно возникла ошибка, можно откатиться на 1 день, месяц, год и т.д. - расписание можно поменять в server-backup-example/src/main.py, в 35-44 строке, все числа указаны в днях:

BACKUPS_TIMETABLE = [
    0,
    1, 
    2,
    3,
    7,
    30,
    180, 
    360, 
]

Бэкап попадающий в интервал между:

0 и 1, - бэкап за 1 день,
1 и 2, - за 2 день,
2 и 3, - за 3 день,
3 и 7, за 1 неделю,
7 и 30, за 1 месяц, и т.д.

В каждом интервале всегда находится только 1 бэкап попадающий в интервал, другой бэкап либо сдвигается в следующий интервал, либо удаляется.

Бэкапы в интервале после 360 - создаются за каждый год, и не удаляются.

Сообщения об ошибках и успешных бэкапах будут приходить в “Support” Telegram топик, - это сделано для того, чтобы постоянные сообщения о бэкапах не спамили в основной топик.

Решение задачи по отправке сообщений в определенный Telegram topic я описал в прошлой статье:

https://habr.com/ru/articles/770582/

Python скрипт для создания бэкапов за определенные отрезки времени

Репозиторий с кодом для создания бэкапов:
https://github.com/oktend/server-backup-example

Рассмотрим основные моменты в коде:

Функция create_dump_postgres делает dump БД PostgreSQL:

def  create_dump_postgres(now, dump_filename):     
    command = PG_DUMP_COMMAND_TEMPLATE.format(
        password=os.getenv("DATABASE_PASSWORD"),
        user=os.getenv("DATABASE_USER"),
        database=os.getenv("DATABASE_NAME"),
        port=os.getenv("DATABASE_PORT"),
        filename=dump_filename,
    )
    result = os.system(command)
    if result != 0:
        log.error(f"pg_dump failed with code {result}, command = {command}")
        sys.exit(10)
        

Функция create_backup_file архивирует dump БД (sql файл) и папку media в один файл формата tar.gz

def create_backup_file(now):
   log.info("create_backup_file started")
   archive_filename = BACKUPS_PATH / BACKUP_FILENAME_TEMPLATE.format(now=now)
   with tempfile.TemporaryDirectory() as tmpdirname:
       dump_filename = SQLDUMP_FILENAME_TEMPLATE.format(now=now)
       dump_filepath = Path(tmpdirname) / dump_filename
       create_dump_postgres(now, dump_filepath)
       tar_command = TAR_COMMAND_TEMPLATE.format(
           archive_filename=archive_filename,
           dump_dirname=tmpdirname,
           dump_filename=dump_filename,
           media_dirpath=os.getenv("MEDIA_DIRPATH"),
           media_dirname=os.getenv("MEDIA_DIRNAME"),
       )
      

Список BACKUPS_TIMETABLE далее используется в функции rotate_backups, чтобы удалить лишние бэкапы в определенных интервалах:

def rotate_backups(now):
    def add_backup(intervals, backup):
        for interval in intervals:
            if interval["start"] <= backup["timestamp"] and backup["timestamp"] < interval["end"]:
                interval["backups"].append(backup)
                return
        log.warning(f"found a file that does not belong to any interval: {backup}")


    def clear_extra_backups(intervals):
        for interval in intervals: 
            backups = sorted(interval["backups"], key=lambda a: a["timestamp"], reverse=True)
            for i in range(len(backups) - 1):
                filename = backups[i]["filename"] 
                log.info(f"deleting extra backup: {filename}")
                os.remove(filename)
                backups[i]["status"] = "deleted" 

    log.info("rotate_backups started") 
    intervals = []
    for i in range(len(BACKUPS_TIMETABLE) - 1):
        end = BACKUPS_TIMETABLE[i]
        start = BACKUPS_TIMETABLE[i + 1]
        intervals.append({
            "start": now - timedelta(days=start), 
            "end": now - timedelta(days=end),
            "backups": [],
            "days_end": BACKUPS_TIMETABLE[i],
            "days_start": BACKUPS_TIMETABLE[i + 1],
        })

По всему коду раскинуто логирование, например:

log.error(f"pg_dump failed with code {result}, command = {command}")
log.warning(f"found a file that does not belong to any interval: {backup}")
log.info("create_backup_file started")
log.debug(f"tar_command = {tar_command}")

В файле src/log.py в 33 строке, можно установить определенный уровень логирования, чтобы получать только важные сообщения (по уровню критичности логи имеют следущий порядок: DEBUG, INFO, WARNING, ERROR, CRITICAL), начиная с установленного уровня сообщения будут прилетать в Telegram топик:

telegram_handler.setLevel(logging.WARNING)

В .env файле прописываете правильные значения переменных:

BACKUPS_DIR="/home/user/backups"
DATABASE_USER=user
DATABASE_PASSWORD=123
DATABASE_NAME=database
DATABASE_PORT=5432
MEDIA_DIRPATH=/home/user/your_project_repo/static_content/
MEDIA_DIRNAME=media 
LOGFILE=logs/server_backup.log
TELEGRAM_NOTIFICATION_BOT_TOKEN="1234567890:asjWJHdejKJWKKklkli"
TELEGRAM_NOTIFICATION_CHAT_ID="-1234567890"
DUMP_MINIMAL_FILESIZE_MB="100"
TELEGRAM_MESSAGE_THREAD_ID="1234"

В данном примере полный путь до директории (/home/user/your_project_repo/static_content/media),
я разбил на две переменные MEDIA_DIRPATH=/home/user/your_project_repo/static_content/ и
MEDIA_DIRNAME=media, это нужно для корректной работы скрипта.
Если у вас нет media контента, в MEDIA_DIRPATH пропишите путь до любой фейковой директории, не включая саму директорию, а в MEDIA_DIRNAME укажите название самой директории.

В файле src/log.py функция init_logger берет из .env файла ранее указанные значения bot_token, chat_id, message_thread_id, на их основе сообщения отправляются в определенный Telegram топик:

def init_logger(filename, telegram_bot_token, telegram_chat_id, telegram_message_thread_id):

Как узнать значения bot_token, chat_id, message_thread_id, я рассказывал в прошлой статье:

https://habr.com/ru/articles/770582/

После окончания редактирования, в папке репозитория (server-backup-example) нужно выполнить следующие команды установки python, poetry, чтобы подготовить репозиторий к работе:

curl -sSL https://install.python-poetry.org | python3 -
poetry install
poetry shell

Crontab

Чтобы бэкапы автоматически создавались по расписанию, будем использовать crontab, который будет выполнять по указанному расписанию определенную команду (запуск скрипта по созданию бэкапа).

Для начала скачиваем crontab:

sudo apt-get update
sudo apt-get install cron

Далее нужно настроить расписание crontab, для этого прописываем команду:

crontab -e

и добавляем последнюю строку, запускающую backup скрипт, весь остальной не используемый код должен быть закомментирован:

0 3 * * * cd /home/user/server-backup-example; /home/user/.local/bin/poetry run python src/main.py

В записи "0 3 * * *" 0 - минуты, 3 - часы, * - дни, * - месяцы, * - день недели(1-7), например 1 - это понедельник.

В данной записи бэкап создается каждый день в 3 часа ночи по серверному времени.

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

cd /home/user/server-backup-example; /home/user/.local/bin/poetry run python src/main.py

Синхронизация с облачным хранилищем Yandex Disk

Чтобы при возникновении каких-либо проблем с хранилищем на сервере мы не потеряли файлы бэкапов, имеет смысл привязать папку с бэкапами на сервере к папке с бэкапами в облачном хранилище, например Yandex Disk.

Для синхронизации понадобится аккаунт Yandex Disk, если его нет, нужно зарегистрировать новый:
https://passport.yandex.ru/registration?mode=register

Для начала, нужно скачать yandex disk cli:

echo "deb http://repo.yandex.ru/yandex-disk/deb/ stable main" | sudo tee -a /etc/apt/sources.list.d/yandex-disk.list > /dev/null && wget http://repo.yandex.ru/yandex-disk/YANDEX-DISK-KEY.GPG -O- | sudo apt-key add - && sudo apt-get update && sudo apt-get install -y yandex-disk

Далее запускаем настройку синхронизации:

yandex-disk setup

Когда запросят, выбираем путь к папке с бэкапами (та же папка, которую указали в .env файле: /home/user/backups), которая будет синхронизироваться с yandex диском.

Теперь бэкапы должны создаваться по расписанию и синхронизироваться с облаком Yandex Disk.

Заключение

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

Буду рад любой критике, комментариям, отзывам.

Комментарии (77)


  1. nightlord189
    09.12.2023 15:56

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


    1. vrezvov
      09.12.2023 15:56

      Хотел бы ответить на вопрос, но не совсем понимаю, что такое "гуе".


      1. t38c3j
        09.12.2023 15:56

        GUI


    1. vrezvov
      09.12.2023 15:56

      К сожалению не могу сказать есть ли такой софт, сам не искал.
      Однако в статье написано как можно изменить расписание создания бэкапов под свои нужды.
      Также в статья я объяснил где указать папку, в которую складываются бэкапы и как эту папку синхронизировать с облаком Yandex.


      1. Grrr5
        09.12.2023 15:56

        Это так мягко намекнули, что если такой софт напишете - плюсов в карму будет больше


    1. 150Rus
      09.12.2023 15:56

      Конечно есть софт, в котором всё уже продумано и сделано вместо костылей на коленке, в том числе бэкапы БД: AWS, GCP, Azure


      1. vrezvov
        09.12.2023 15:56

        Спасибо, изучу эти варианты!


    1. qferus
      09.12.2023 15:56

      Рубекап вроде допилили функционал, там и гуя и выбор куда складывать и все что хочешь


      1. vrezvov
        09.12.2023 15:56

        Спасибо, протестирую этот софт.


    1. Iselston
      09.12.2023 15:56

      Честно, не знаю многое ли поменялось - но года 2 назад использовал связку PGBarman (https://pgbarman.org/) и какой-то WEB-GUI с Github для него. Устраивало


      1. vrezvov
        09.12.2023 15:56

        Спасибо, гляну что это такое.


  1. lexore
    09.12.2023 15:56

    А вы пробовали проверять, восстанавливается ли из него база? У pg_dump много ключиков, от которых зависит, что будет забекаплено.

    Продолжая тему, рекомендую сделать скрипт по разворачиванию базы из бекапа. Чтобы, если что-то случится, не тратить время и развернуть запуском одного скрипта.


    1. navferty
      09.12.2023 15:56

      Вот тоже об этом подумал: "если у Вас настроены бэкапы, но нет регулярной проверки разворачивания БД из бэкапов - то у Вас нет бэкапов"


    1. vrezvov
      09.12.2023 15:56

      Да, восстановливать пробовал, - получается. Но автоматического восстановления не настроено, хотя действительно неплохо бы было проверять возможность восстановления из бэкапа, время от времени. Просто я старался сделать предельно простую систему, при этом справляющуюся со своей задачей, и база данных с которой делают dump, в моем случае была минимальных размеров ~ 100 Mb.


  1. osj
    09.12.2023 15:56

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

    Возможно лучше подобрать свое решение тут 10 способов сделать резервную копию в PostgreSQL.


    1. vrezvov
      09.12.2023 15:56

      В моем случае база данных умещалась в ~ 100 Mb, и с этим размером скрипт работает прекрасно, к сожалению на больших БД, скрипт не тестировал.


  1. stvoid
    09.12.2023 15:56

    Я извиняюсь что прочел не всё, но по моему для всего более чем хватает стандартного pg_dumpall + немного bash кода.


    1. Maxim_Q
      09.12.2023 15:56

      Да, все верно. вот мой код для бэкапа на 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
      


      1. vrezvov
        09.12.2023 15:56

        Спасибо, попробую протестировать!


      1. stvoid
        09.12.2023 15:56

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

        Удаление старых файлов бэкапов. В данном примере удаляет файлы старше 2х дней. В параметр -name поподставить свою маску, я просто упаковываю несколько бэкапов, т.к. в кластере несколько БД.

        find . -name "*.tar.gz" -type f -mtime +2 | xargs rm;


        1. jackcrane
          09.12.2023 15:56

          В данном примере удаляет файлы старше 2х дней.

          потом время глюкнет (или скрипт бэкапов перестанет выполняться) и останетесь без ничего. поэтому файлы удалять только по счету.


          1. stvoid
            09.12.2023 15:56

            Стало даже интересно, у вас бывали такие случаи? Из-за чего?

            Какой вариант автоматизации скрипта "по счету" вы предлагаете?


            1. jackcrane
              09.12.2023 15:56

              да, бывали.

              глюк со временем - 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


              1. stvoid
                09.12.2023 15:56

                Хм... да, кейс интересный конечно со временем у вас и вполне реален, у нас тоже периодически рубят доступ извне и т.п.

                Но я пожалуй безответственно оставлю это на сисадминов, которые что-то шатают :))))


    1. vrezvov
      09.12.2023 15:56

      Да, к сожалению я не нашёл готового решения своей задачи, поэтому решил сделать простенький велосипед.


      1. lexore
        09.12.2023 15:56

        Тут вы или лукавите, или вам стоит прокачать скилл гугления. Смотрите, делаете вот так: https://www.google.com/search?q=postgresql+backup+script
        И в первых же строчках получаете инструкцию от самого postgresql:
        https://wiki.postgresql.org/wiki/Automated_Backup_on_Linux

        От себя хочу добавить - в вашей профессии очень важно уметь сначала гуглить, а потом писать свой велосипед. Обычно куча людей уже получили такую же задачу, как вы, и выложили решение в интернет. Да еще и протестировали. А такая вещь, как бекап БД - попробуйте сами оценить, сколько людей в последние 20-30 лет с этим сталкивались)


        1. jackcrane
          09.12.2023 15:56

          в нагугленом не будет телеграм-бота хехе.

          лично я начинал от 1Сников: https://infostart.ru/1c/articles/956734/

          (внимание на отдельный дамп самой большой таблицы).

          и по ссылкам оттуда https://www.sinyawskiy.ru/relation_not_exist.html

          (полезно дамп схемы сделать отдельно).


  1. ptr128
    09.12.2023 15:56

    С точки зрения безопасности, чтобы вообще не указывать пароли доступа к БД в конфигурационных файлах, резервное копирование лучше запускать под postgres из его cron файла.


    1. vandalv
      09.12.2023 15:56

      Они там все из переменных среды берутся


      1. ptr128
        09.12.2023 15:56

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

        Если уже совсем вариантов нет (необходим доступ с удаленного компьютера), то пользуйтесь хотя бы keytab и kerberos авторизацией.


    1. vrezvov
      09.12.2023 15:56

      Все секреты указываются в .env файле, это относительно безопасно, хотя далеко не самый безопасный вариант.


      1. ptr128
        09.12.2023 15:56

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


        1. jackcrane
          09.12.2023 15:56

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

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

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


          1. ptr128
            09.12.2023 15:56

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


  1. bulnv
    09.12.2023 15:56

    Как решается удаление и очистка корзины на Яндекс диске? Раньше cli этого не умел.


    1. pistoletov
      09.12.2023 15:56

      Да умеет через curl


    1. vrezvov
      09.12.2023 15:56

      Удаление прописано в скрипте, а yandex disk лишь копирует содержание папки.


      1. Sap_ru
        09.12.2023 15:56

        YsDisk не удаляет заменённые файлы, а переносит их в корзину, и место очень быстро кончается. Речь об этом.


  1. qferus
    09.12.2023 15:56

    Помимо того что есть готовые инструменты или простые скрипты с pgdumpall или pgdump данный инструмент ничем не лучше.
    Если брать за условие слабую СХД, на которой постгря крутится или вообще кластер, где требуется снять влияние с мастера, а то и вовсе убрать фриз таблиц, то решение слабое.


  1. denisromanenko
    09.12.2023 15:56

    В таком важном деле я бы не доверял самописным скриптам. Нисколько не умаляю вашу работу.

    но лучше проверенные коммерческие решения. Там же потом и мониторинг, и удаление старых, и failback - скопировать на другое хранилище, если первое не отвечает.

    Свои небольшие но важные БД я бэкаплю через sqlbackupandftp (виндоус) и sqlbak (линукс)


    1. vrezvov
      09.12.2023 15:56

      Спасибо, обязательно исследую вопрос коммерческих решений.


  1. mixsture
    09.12.2023 15:56

    Лично я не очень люблю схемы бекапов, когда отдающая сторона имеет права записи в каталог с предыдущими бекапами. Конкретно в статье этот единый скрипт и запускает pg_dump, и ротацию делает.
    И тогда, если у вас подбирают пароль к ОС отдающей стороны, то заодно можете и все бекапы разом потерять.
    Имхо, гораздо лучше работает схема, когда забирает бекапы именно принимающая сторона, она же и делает ротацию. Либо как вариант append-only хранилища.

    И яндекс-диск в режиме синхронизации тут, мне кажется, слабо поможет. Он также отсинхронизирует и удаление ваших бекапов злоумышленником.


    1. vrezvov
      09.12.2023 15:56

      Справедливо, действительно при удалении файлов на сервере они удаляются и в Yandex disk, такая проблема с хранением бэков присутствует, вероятно мне ещё стоит поискать способы её решения.


    1. Bonov
      09.12.2023 15:56

      В качестве альтернативы может и сервер сам отдавать бекапы, а вот на принимающей стороне уже скрипт по ротации и по блокированию доступа к записанным ранее бекапам.


  1. pistoletov
    09.12.2023 15:56

    Pgbackrest отличный инструмент для бекапов. Полный или инкрементальный делаю периодически через cron


    1. vrezvov
      09.12.2023 15:56

      Спасибо, протестирую.


  1. showfear
    09.12.2023 15:56

    Хорошая статья, но к сожалению пока не наткнешься на команду sudo... Не поймёшь, на операционной системе которой всю эту процессию выполняешь...

    Хотелось бы так же для оболочек Windows, в powershell или CMD.


    1. vrezvov
      09.12.2023 15:56

      Понял, добавлю упоминание Linux в статье, может когда нибудь руки дойдут написать для Windows.


    1. ptr128
      09.12.2023 15:56

      А смысл есть? PostgreSQL под Windows, по объективным причинам, для продуктивного использования мало пригоден. А поиграться можно и под WSL2.


    1. jackcrane
      09.12.2023 15:56

      в этом питоновском доброкоде еще и sudo есть ?? прекрасно.

      для винды любые нужные шеллы вам даст cygwin итли msys2.

      cygwin с некоторыми фокусами позволит запустить скрипт от имени LocalSystem (S-1-5-18), msys2 я не проверял. а пользователя круче S-1-5-18 на винде нет.

      впрочем, есть и sudo для Windows от китайских хакеров.


    1. SAGSa
      09.12.2023 15:56

      Для 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. Bessome
    09.12.2023 15:56

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

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

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

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

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

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


    1. vrezvov
      09.12.2023 15:56

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


  1. Tzimie
    09.12.2023 15:56

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


    1. ptr128
      09.12.2023 15:56

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


    1. Bessome
      09.12.2023 15:56

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

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

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

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


      1. Tzimie
        09.12.2023 15:56

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


        1. Bessome
          09.12.2023 15:56

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

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


          1. Tzimie
            09.12.2023 15:56

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


            1. Bessome
              09.12.2023 15:56

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


      1. ptr128
        09.12.2023 15:56

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


    1. jackcrane
      09.12.2023 15:56

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

      нет. это не 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

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


      1. Tzimie
        09.12.2023 15:56

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


        1. jackcrane
          09.12.2023 15:56

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


      1. ptr128
        09.12.2023 15:56

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


        1. jackcrane
          09.12.2023 15:56

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


          1. ptr128
            09.12.2023 15:56

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


    1. Kassiy_Pontiy_Pilat
      09.12.2023 15:56

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


  1. jackcrane
    09.12.2023 15:56

    1) непонятен выбор питона как скриптового языка (больше него жрет только руби). или все приложение строилось как враппер для телеграм-бота ?

    2) непонятно, почему автор буквально понял требование заказчика: "1, 2, 3 дня, 1 неделя, 1 месяц, 6 месяцев" и бросился его реализовывать. дамп (будем откровенны сами с собой - это именно дамп а не бэкап) всех баз один раз в сутки с хранением 3 года перекрывает все потребности с тройным запасом и значительно упрощает логику.

    3) непонятен выбор работы с яндекс-диском через мутный фирменный бинарник вместо WebDAV. проблемы с WebDAV ? если яндекс-диск оплачен, то пусть уже техподдержка напряжется. есди яндекс-диск бесплатный, то тем хуже для него.

    4) не приняты меры против bit rot. это может быть как просчет контрольной суммы файла (и переименования файла), так и что-то поинтереснее , например par2 (по следам duplicity).


    1. EAL9000
      09.12.2023 15:56

      непонятен выбор работы с яндекс-диском через мутный фирменный бинарник вместо WebDAV. проблемы с WebDAV ?

      Насколько я знаю, у Яндекс-диска WebDAV только на чтение, на запись там что-то около некольких мегабайт можно


    1. vrezvov
      09.12.2023 15:56

      Я хотел сделать простое решение для небольшой БД и насчёт оптимизации не особо беспокоился, для БД ~ 100Mb скрипт проходит почти мгновенно, за 1-2 секунды.

      Кроме дампа БД psql, также сохраняется папка с media контентом, а затем 2 файла архивируются в 1 бэкап.

      Остальные замечания принял к сведению.


    1. ptr128
      09.12.2023 15:56

      Какая разница на чем писать скрипт, если издержки на сам интерпретатор этого скрипта - доли процента? Кому то удобней Bash, кому-то Perl (я иногда такой), кому-то PowerShell, автору - Python. Тут явно не та задача, где выбор языка играет роль. А вот если в перспективе переводить это на AirFlow, то Python окажется к месту.


  1. wizard_s
    09.12.2023 15:56

    Лить бэкапы в (чужое) облако без шифрования - дурной тон


  1. Solo2005
    09.12.2023 15:56

    Классная статья. Спасибо


  1. ashkraba
    09.12.2023 15:56

    Ох, да((( Сейчас бы в 23 году создавать бэкапы дампом, да ещё и запускать из крона и хранить все креды в открытом виде. Бегите из этой компании.!


    1. high_panurg
      09.12.2023 15:56

      А какая альтернатива?
      Просто предположим есть у меня сервер, как мне безопасно хранить на нём свои креды?
      Ну, я могу просто положить их в файл, а могу, например, развернуть Vault и хранить там, но я же всё равно должен положить api ключик для доступа к Vault.
      Я могу, конечно шифровать файл с кредами или api ключ, но я все равно где-то должен хранить ключ для расшифровки.
      И получается, что использование Vault не делает наши пароли более защищёнными, только косвенно, потому что мы можем быстрее их ротировать, быстрее обновлять и предоставлять в едином виде для сервисов.

      P.S. В данном конкретном случае, конечно, есть вариант получше - запускать скрипт от пользователя, который авторизуется через "peer" метод.


      1. jackcrane
        09.12.2023 15:56

        А какая альтернатива?

        industry-level enterprise-grade production-ready решения (т.е. те которые про себя на своем сайте так написали) за много денег.