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

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

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

Синтаксис:

pg_dump <параметры> <имя базы> > <файл, куда сохранить дамп>


Пример (для дампа одной бд зайдите под 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


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

#!/bin/sh
PATH=/etc:/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin

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

unset PGPASSWORD


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

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

crontab -e


3 0 * * * /scripts/postgresql_dump.sh


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

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

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

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


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

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

Запускается с опцией -t <table> или --table=<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

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

Настраиваем путь для резервного копирования

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

Дополнительные опции

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

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

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

Бинарный с компрессией:

pg_dump -Fc users > users.bak


Тарбол:

pg_dump -Ft users > users.tar


Directory-формат:

pg_dump -Fd users > users.dir


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

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

pg_basebackup -h node1 -D /backup


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

pg_dumpall

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

pg_dumpall > cluster.bak


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

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

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

=# CREATE DATABASE users WITH ENCODING='UTF-8';


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

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

Синтаксис:

psql <имя базы> < <файл с дампом>


Пример:

psql users < /tmp/users.dump


С авторизацией

При необходимости авторизоваться при подключении к базе вводим:

psql -U dmosk -W users < /tmp/users.dump


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

Из файла gz

Сначала распаковываем файл, затем запускаем восстановление:

gunzip users.dump.gz


psql users < users.dump


Или одной командой:

zcat users.dump.gz | psql users


Определенную таблицу

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

psql users < /tmp/students.dump


Если делался полный дамп, восстановить определенную таблицу можно при помощи утилиты pg_restore с параметром -t:

pg_restore -a -t students users.dump


С помощью pgAdmin

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

Выбираем восстановление базы данных

Выбираем наш файл с дампом:

Выбираем файл с дампом

И кликаем по Восстановить:

Восстанавливаем данные

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

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

Из бинарника:

pg_restore -Fc users.bak


Из тарбола:

pg_restore -Ft users.tar


С создание новой базы:

pg_restore -Ft -C users.tar


Возможные ошибки

Input file appears to be a text format dump. please use psql.

Причина: дамп сделан в текстовом формате, поэтому нельзя использовать утилиту pg_restore.

Решение: восстановить данные можно командой psql <имя базы> < <файл с дампом> или выполнив 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/<version>/bin/. Необходимо найти нужный каталог, если их несколько и запускать нужную версию. При отсутствии последней, установить.

No password supplied

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

Решение: либо настройте сервер для предоставление доступа без пароля в файле pg_hba.conf либо экспортируйте переменную PGPASSWORD (export PGPASSWORD или set PGPASSWORD).

< Назад к списку новостей