Postgresql backup script linux

Резервное копирование PostgreSQL

Обновлено: 30.05.2022 Опубликовано: 25.09.2017

В данной инструкции рассмотрены варианты и примеры создания резервных копий и восстановления баз СУБД PostgreSQL.

Все команды, которые приводятся ниже, должны выполняться из командной строки. В Linux — это окно терминала, в Windows — командная строка (cmd.exe) с переходом в папку установки PostgreSQL.

Создание резервных копий

Базовая команда

pg_dump users > /tmp/users.dump

Пользователь и пароль

Если резервная копия выполняется не от учетной записи postgres, необходимо добавить опцию -U с указанием пользователя:

pg_dump -U dmosk -W users > /tmp/users.dump

* где dmosk — имя учетной записи; опция W потребует ввода пароля.

Сжатие данных

Для экономии дискового пространства или более быстрой передачи по сети можно сжать наш архив:

pg_dump users | gzip > users.dump.gz

Скрипт для автоматического резервного копирования

Рассмотрим 2 варианта написания скрипта для резервирования баз PostgreSQL. Первый вариант — запуск скрипта от пользователя root для резервирования одной базы. Второй — запуск от пользователя postgres для резервирования всех баз, созданных в СУБД.

Для начала, создадим каталог, в котором разместим скрипт, например:

Вариант 1. Запуск от пользователя root; одна база.

PGPASSWORD=password
export PGPASSWORD
pathB=/backup
dbUser=dbuser
database=db

find $pathB \( -name «*-1[^5].*» -o -name «*-[023]?.*» \) -ctime +61 -delete
pg_dump -U $dbUser $database | gzip > $pathB/pgsql_$(date «+%Y-%m-%d»).sql.gz

* где password — пароль для подключения к postgresql; /backup — каталог, в котором будут храниться резервные копии; dbuser — имя учетной записи для подключения к БУБД; pathB — путь до каталога, где будут храниться резервные копии.
* данный скрипт сначала удалит все резервные копии, старше 61 дня, но оставит от 15-о числа как длительный архив. После при помощи утилиты pg_dump будет выполнено подключение и резервирование базы db. Пароль экспортируется в системную переменную на момент выполнения задачи.

Для запуска резервного копирования по расписанию, сохраняем скрипт в файл, например, /scripts/postgresql_dump.sh и создаем задание в планировщике:

3 0 * * * /scripts/postgresql_dump.sh

* наш скрипт будет запускаться каждый день в 03:00.

Вариант 2. Запуск от пользователя postgres; все базы.

find $pathB \( -name «*-1[^5].*» -o -name «*-[023]?.*» \) -ctime +61 -delete

for dbname in `echo «SELECT datname FROM pg_database;» | psql | tail -n +3 | head -n -2 | egrep -v ‘template0|template1|postgres’`; do
pg_dump $dbname | gzip > $pathB/$dbname-$(date «+%Y-%m-%d»).sql.gz
done;

* где /backup — каталог, в котором будут храниться резервные копии; pathB — путь до каталога, где будут храниться резервные копии.
* данный скрипт сначала удалит все резервные копии, старше 61 дня, но оставит от 15-о числа как длительный архив. После найдет все созданные в СУБД базы, кроме служебных и при помощи утилиты pg_dump будет выполнено резервирование каждой найденной базы. Пароль нам не нужен, так как по умолчанию, пользователь postgres имеет возможность подключаться к базе без пароля.

Необходимо убедиться, что у пользователя postgre будет разрешение на запись в каталог назначения, в нашем примере, /backup/postgres.

Зададим в качестве владельца файла, пользователя postgres:

chown postgres:postgres /scripts/postgresql_dump.sh

Для запуска резервного копирования по расписанию, сохраняем скрипт в файл, например, /scripts/postgresql_dump.sh и создаем задание в планировщике:

* мы откроем на редактирование cron для пользователя postgres.

3 0 * * * /scripts/postgresql_dump.sh

* наш скрипт будет запускаться каждый день в 03:00.

Права и запуск

Разрешаем запуск скрипта, как исполняемого файла:

chmod +x /scripts/postgresql_dump.sh

Единоразово можно запустить задание на выполнение резервной копии:

. или от пользователя postgres:

su — postgres -c «/scripts/postgresql_dump.sh»

На удаленном сервере

Если сервер баз данных находится на другом сервере, просто добавляем опцию -h:

pg_dump -h 192.168.0.15 users > /tmp/users.dump

* необходимо убедиться, что сама СУБД разрешает удаленное подключение. Подробнее читайте инструкцию Как настроить удаленное подключение к PostgreSQL.

Дамп определенной таблицы

Запускается с опцией -t или —table= :

pg_dump -t students users > /tmp/students.dump

* где students — таблица; users — база данных.

Если наша таблица находится в определенной схеме, то она указывается вместе с ней, например:

pg_dump -t public.students users > /tmp/students.dump

* где public — схема; students — таблица; users — база данных.

Размещение каждой таблицы в отдельный файл

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

pg_dump -d customers > /tmp/folder

* где /tmp/folder — путь до каталога, в котором разместяться файлы дампа для каждой таблицы.

Для определенной схемы

В нашей базе может быть несколько схем. Если мы хотим сделать дамп только для определенной схемы, то используем опцию -n, например:

pg_dump -n public peoples > /tmp/peoples.public.sql

* в данном примере мы заархивируем схему public базы данных peoples.

Только схемы (структуры)

Для резервного копирования без данных (только таблицы и их структуры):

pg_dump —schema-only users > /tmp/users.schema.dump

Также, внутри каждой базы могут быть свои схемы с данными. Если нам нужно сделать дамп именно той схемы, которая внутри базы, используем ключ -n:

pg_dump —schema-only users -n production > /tmp/users.schema_production.dump

* в данном примере мы создадим дамп структуры базы данных users только для схемы production.

Или полный дамп с данными для схемы внутри базы данных:

pg_dump users -n production > /tmp/users.production.dump

Только данные

pg_dump —data-only users > /tmp/users.data.dump

Использование pgAdmin

Данный метод хорошо подойдет для компьютеров с Windows и для быстрого создания резервных копий из графического интерфейса.

Запускаем pgAdmin — подключаемся к серверу — кликаем правой кнопкой мыши по базе, для которой хотим сделать дамп — выбираем Резервная копия:

В открывшемся окне выбираем путь для сохранения данных и настраиваемый формат:

При желании, можно изучить дополнительные параметры для резервного копирования:

После нажимаем Резервная копия — ждем окончания процесса и кликаем по Завершено.

Не текстовые форматы дампа

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

pg_dump -Fc users > users.bak

pg_dump -Ft users > users.tar

Источник

Автоматизированные бэкапы postgresql

Всем привет! Я бы сказал что эта статья cookbok по которому можно создать простое и эффективное решение для создания бэкапов базы данных.

Казалось бы довольно очевидная задача но тем не менее когда я хотел её решить столкнулся с множеством проблем. Готовые бесплатные решения в большинстве своем направленны на управления кластерами или не поддерживаются на ARM машинах.

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

Скрипт создания бэкапов

# ~/pg_backup.sh db_name=dbname db_user=dbuser db_host=host backupfolder=~/postgresql/backups recipient_email=youremail@example.ru # Сколько дней хранить файлы keep_day=30 sqlfile=$backupfolder/database-$(date +%d-%m-%Y_%H-%M-%S).sql zipfile=$backupfolder/database-$(date +%d-%m-%Y_%H-%M-%S).zip mkdir -p $backupfolder if pg_dump -U $db_user -h $db_host $db_name > $sqlfile ; then echo 'Sql dump created' else echo 'pg_dump return non-zero code' | mailx -s 'No backup was created!' $recipient_email exit fi if gzip -c $sqlfile > $zipfile; then echo 'The backup was successfully compressed' else echo 'Error compressing backup' | mailx -s 'Backup was not created!' $recipient_email exit fi rm $sqlfile echo $zipfile | mailx -s -a $sqlfile 'Backup was successfully created' $recipient_email find $backupfolder -mtime +$keep_day -delete

Что бы pg_dump не запрашивал пароль создаем .pg_pass в домашней директории пользователя в формате

И устанавливаем корректные права

запуск по расписанию

добавляем pg_backup.bash в cron. Каждый день в 5 утра мне кажется оптимальным вариантом. crontab -e и добавляем туда строчку. Важно, в кроне нужно указать полный путь.

0 5 * * 0-6 /home/www/pg_backup.bash

Настраиваем отправку писем

Доводилось ли вам отправлять письма из CMD линукс? Мне нет, но эта возможность радует своей простотой, настолько просто что даже сложно

sudo apt-get update sudo apt-get install postfix mailutils ssmtp

Настраиваем smtp. Тут можно использовать обычную гугл почту

#/etc/ssmtp/ssmtp.conf root=your@email.com mailhub=smtp.gmail.com:587 AuthUser=your@gmail.com AuthPass=yourGmailPass UseTLS=YES UseSTARTTLS=YES

Итоги

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

P.S.

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

Цели на самом деле прагматичные, я использую бесплатное облако на оракле, они дают вполне неплохое облако в бесплатно пользование размером 4 ядра 24 гига за 0р в месяц, но ядра армовские, некоторые готовые решения просто не имеют пакетов под арм. А мне бы хотелось быть увереным что моя не нагруженная и не очень большая база делает резервные копии и сохраняется где то отдельно от бесплатного оракла. Плюс уведомления на почту, если что то пойдёт не так.

Ни коим образом не претендую enterpriseность. Это простое решение для ненагруженной базы. Что бы избежать излишних уровней абстракций в виде кластеров или реплик с WAL. Всё сделано предельно просто и в лоб. Излишни они конечно только в контексте моих задач, так база более чем справляется со своей нагрузкой и будет это делать в обозримом будущем, а ради резервного копирования разворачивать масштабные системы, овчинка выделки не стоит)

Источник