Linux скрипт backup postgresql

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

Что такое PostgreSQL?

PostgreSQL – это современная открытая система управления базами данных (или СУБД), которая часто используется для хранения информации, необходимой для работы сайтов и веб-приложений.

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

Для выполнения руководства необходим виртуальный выделенный сервер Ubuntu 12.04 и PostgreSQL 9.1 (руководство также действительно для более новых версий СУБД).

Резервное копирование при помощи pg_dump

PostgreSQL предоставляет утилиту pg_dump, которая позволяет сделать дамп базы PostgreSQL в целях резервного копирования.

Утилита pg_dump запускается из командной строки Linux. Ее базовый синтаксис выглядит так:

pg_dump name_of_database > name_of_backup_file

Для запуска команды нужно иметь право на чтение всей информации БД, потому ее, как правило, следует запускать как суперпользователь (англ. superuser).

Для примера можно войти как пользователь postgres (стандартный пользователь БД PostgreSQL) и выполнить команду на стандартную БД, которая тоже зовется postgres:

sudo su — postgres
pg_dump postgres > postgres_db.bak

Данная команда является клиентской программой PostgreSQL, потому ее можно запускать на удаленной системе (если она имеет соответствующие права на БД).

Чтобы создать резервную копию данных удаленной системы, внесите в команду флаг -h (который указывает на удаленный хост) и -p (указывает на удаленный порт):

pg_dump -h remote_host -p remote_port name_of_database > name_of_backup_file

Кроме того, можно указать другого пользователя при помощи опции -U. Синтаксис будет иметь такой вид:

pg_dump -U user_name -h remote_host -p remote_port name_of_database > name_of_backup_file

Помните: как и любая другая клиентская команда, pg_dump имеет определенные требования аутентификации. Это значит, что для создания резервной копии системы необходимо иметь валидные учетные данные этой системы.

Восстановление дампа PostgreSQL с помощью pg_dump

Чтобы восстановить дамп данных, созданный при помощи pg_dump, можно перенаправить этот файл в стандартный ввод psql:

psql empty_database backup_file

Примечание: эта операция перенаправления не создает требуемую базу данных. Ее нужно создать отдельно до запуска команды.

Для примера можно создать новую БД по имени restored_database, а затем перенаправить дамп по имени database.bak; для этого нужно выполнить:

createdb -T template0 restored_database
psql restored_database

При этом будет создана пустая БД на основе template0.

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

К примеру, если БД принадлежит пользователю test_user, то перед импортированием нужно создать его в восстанавливаемой системе:

createuser test_user
psql restored_database

Устранение ошибок восстановления

По умолчанию СУБД PostgreSQL продолжит восстанавливать данные, даже столкнувшись с ошибкой.

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

Чтобы система PostgreSQL прерывала восстановление в случае возникновения ошибки, наберите:

psql —set ON_ERROR_STOP=on restored_database backup_file

Эта команда остановит операцию восстановления PostgreSQL в случае ошибки.

Конечно, в системе останется поврежденная БД, которая была лишь частично восстановлена; тем не менее, такой подход позволяет устранять ошибки по мере их поступления, а не после полного восстановления БД (что может оказаться достаточно непростой задачей).

Во многих ситуациях отличным решением может оказаться использование опции -1 (цифра один) или –single-transaction:

psql -1 restored_database backup_file

Эта опция выполняет весь процесс восстановления в рамках одной транзакции.

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

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

Резервное копирование и восстановление всех БД PostgreSQL

Утилита pg_dumpall позволяет создать резервную копию абсолютно всех данных.

Синтаксис этой команды очень похож на синтаксис pg_dump; но в данном случае не нужно указывать БД, поскольку команда скопирует все доступные базы данных:

Чтобы восстановить эти баз данных, нужно передать файл psql с БД по умолчанию:

psql -f backup_file postgres

Итоги

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

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

Источник

Автоматический бэкап PostgreSQL в Linux

О том как делать автоматические резервные копии в PostgreSQL рассказано в статье Automated Backup on Linux. Скрипты из этой статьи всем хороши, кроме того, что если их прописать в cron, то ничего бэкапиться не будет. А происходит так из-за того, что нигде не указывается пароль пользователя PostgreSQL от имени которого делается бэкап.

Для того чтобы эти скрипты заработали в автоматическом режиме нужно в самый конец pg_backup.config добавить:
# Set PGUSER and PGPASSWORD
PGUSER=»postgres»
PGPASSWORD=»password»
export PGUSER PGPASSWORD
# End

Естественно, вместо пользователя postgres можно использовать любого нужного пользователя, а вместо password нужно указать пароль этого пользователя.

А в целях безопасности в самом конце pg_backup.sh нужно вставить:
# Unset PGUSER and PGPASSWORD
PGUSER=»»
PGPASSWORD=»»
export PGUSER PGPASSWORD
# End

Теперь можно сделать файлы исполняемыми:
chmod u+x pg_backup.sh pg_backup_rotated.sh

(для проверки работоспособности нужно выполнить bash pg_backup.sh)

Прописать их в cron пользователя postgres (на примере Ubuntu):
sudo crontab -u postgres -e

В файл нужно добавить строки:
SHELL=/bin/bash
15 3 * * * /path/to/script/pg_backup.sh > /dev/null 2>&1

Надо напоминать, что /path/to/script/ — это путь к директории с файлом?

Теперь ждём 03:15 утра и проверяем работоспособность.

Автоматический бэкап PostgreSQL в Linux: 6 комментариев

А почему бы не сделать по-элегантнее, без «засветки» пароля, например так:
1. Определим user map для root. Для этого добавим в файл $PGDATA/pg_ident.conf строчку :
root root postgres
2. Добавим этот user map в соответствующую запись в файле $PGDATA/pg_hba.conf (приписать опцию map=root в соответсвующую строку идентификации), например:
local all postgres peer map=root
3. Рестарт Postgres:
service postgresql-9.3 restart
После этого, все клиенты (тот же psql ), запускаемые под акком root , будут коннектиться к PostgresQL как postgres . Все должно запахать, и нигде никаких паролей указывать не надо.
Не нравится вам root – тоже самое можно проделать для любого системного акка, под которым вы хотите запускать бэкап скрипт.

Роман, спасибо за красивое решение! Постараюсь опробовать его и отписаться о том что получилось.

А не сделал я так из-за того, что я по роду деятельности не системный администратор и многого ещё не знаю — я только учусь (с) 🙂

Ребят, подскажите пожалуйста, как настроить все то же самое, только с выгрузкой backup-а на сторонний FTP сервер, а не локально на машину.

Или проще выгрузить dump локально и через отдельный скрипт загружать его на FTP?

Rolex, я вижу несколько вариантов:

1. Взять скрипты из пакета backup-manager ( apt-get install backup-manager ) и либо позаимствовать оттуда код отправки файлов на FTP, либо использовать скритпы из пакета для этой цели, только учти, что пакет уже давно не поддерживается, однако, стабильно работает.
2. Использовать вместо FTP SSH, что проще ( нужно добавить в существующий скрипт только одну строчку scp) и надёжнее.

Не думаю, что для кого-то открыл америку, но вот еще один вариант для создания бэкапов. Через утилиту barman

Источник

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

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

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

Пример скрипта дампа всех баз postgresSQL

#/bin/sh

START_TIME=`date +%s`


BACKUP_DIR=»/usr/local/backups»
TIMESTAMP=$(date «+%d-%m-%Y_%H-%M-%S»)
DAYS_TO_STORE=7




echo «Starting SQL backup script»
echo «\t PID: $$»
echo «\t Start time: $TIMESTAMP\n»




echo -n «Starting pg_dumpall. «
/usr/bin/pg_dumpall | gzip > $BACKUP_DIR/pg_dumpall_$TIMESTAMP.sql.gz
echo «ok»


echo -n «Computing MD5 hash. «
/usr/bin/md5sum $BACKUP_DIR/pg_dumpall_$TIMESTAMP.sql.gz >> $BACKUP_DIR/md5sums.txt
echo «ok\n»




echo «Deleting backup older than $DAYS_TO_STORE days. «
for i in $(find /usr/local/backups -type f -mtime +14 -name «*.sql.gz» -print); do
echo -n «\tdeleting file $i . «
rm $i
echo «ok»
done


END_TIME=`date +%s`
echo «\n\nScript ended in `expr $END_TIME — $START_TIME` sec.»




# Docs:
# https://www.postgresql.org/docs/9.1/static/app-pg-dumpall.html

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

Пример (для дампа одной бд зайдите под su postgres):

pg_dump bd > /tmp/bd.dump

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

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

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

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

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

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

pg_dump users | gzip > users.dump.gz

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

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 — имя учетной записи для подключения к БУБД.
* данный скрипт сначала удалит все резервные копии, старше 61 дня, но оставит от 15-о числа как длительный архив. После при помощи утилиты pg_dump будет выполнено подключение и резервирование базы db. Пароль экспортируется в системную переменную на момент выполнения задачи.

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

3 0 * * * /scripts/postgresql_dump.sh

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

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

Если сервер баз данных находится на другом сервере, просто добавляем опцию -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 — база данных.

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

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

pg_dump -d customers > /tmp/folder

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

Только схемы

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

pg_dump —schema-only users > /tmp/users.schema.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

pg_dump -Fd users > users.dir

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

pg_basebackup позволяет создать резервную копию для кластера PostgreSQL.

pg_basebackup -h node1 -D /backup

* в данном примере создается резервная копия для сервера node1 с сохранением в каталог /backup.

pg_dumpall

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

Утилиту удобно использовать с ключом -g (—globals-only) — выгрузка только глобальных объектов (ролей и табличных пространств).

Восстановление

Может понадобиться создать базу данных. Это можно сделать SQL-запросом:

=# CREATE DATABASE users WITH ENCODING=’UTF-8′;

* где users — имя базы; UTF-8 — используемая кодировка.

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

psql users или выполнив SQL, открыв файл, скопировав его содержимое и вставив в SQL-редактор.

No matching tables were found

Причина: Таблица, для которой создается дамп не существует. Утилита pg_dump чувствительна к лишним пробелам, порядку ключей и регистру.

Решение: проверьте, что правильно написано название таблицы и нет лишних пробелов.

Too many command-line arguments

Причина: Утилита pg_dump чувствительна к лишним пробелам.

Решение: проверьте, что нет лишних пробелов.

Aborting because of server version mismatch

Причина: несовместимая версия сервера и утилиты pg_dump. Может возникнуть после обновления или при выполнении резервного копирования с удаленной консоли.

Решение: нужная версия утилиты хранится в каталоге /usr/lib/postgresql/ /bin/. Необходимо найти нужный каталог, если их несколько и запускать нужную версию. При отсутствии последней, установить.

No password supplied

Причина: нет системной переменной PGPASSWORD или она пустая.

Источник