Бэкап базы данных postgresql linux

Создание резервной копии данных 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

В данной инструкции рассмотрены варианты создания резервных копий и восстановления баз СУБД 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 и создаем задание в планировщике:

crontab -e -u postgres

* мы откроем на редактирование 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 — база данных.

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

Также называется резервированием в каталог. Данный способ удобен при больших размерах базы или необходимости восстанавливать отдельные таблицы. Выполняется с ипользованием ключа -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 — подключаемся к серверу — кликаем правой кнопкой мыши по базе, для которой хотим сделать дамп — выбираем Резервная копия:

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

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

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

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

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

Источник

Резервное копирование 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

Читайте также:  Windows smartphone upgrade tool

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 или она пустая.

Источник