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

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

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

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

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

Синтаксис:

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

Пример:

pg_dump users > /tmp/users.dump

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

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

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

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

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

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

pg_dump users | gzip > users.dump.gz

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

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

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

mkdir /scripts

И сам скрипт: 

vi /scripts/postgresql_dump.sh

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

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

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

crontab -e

3 0 * * * /scripts/postgresql_dump.sh

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

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

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

pathB=/backup/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

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

/scripts/postgresql_dump.sh

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

su - postgres -c "/scripts/postgresql_dump.sh"

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

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

pg_dump -h 10.10.0.18 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

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) — выгрузка только глобальных объектов (ролей и табличных пространств).

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

pg_dumpall | gzip > cluster.tar.gz

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

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

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

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

Если мы получим ошибку:

ERROR:  encoding "UTF8" does not match locale "en_US"
DETAIL:  The chosen LC_CTYPE setting requires encoding "LATIN1".

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

CREATE DATABASE users WITH OWNER 'postgres' ENCODING 'UTF8' LC_COLLATE = 'ru_RU.UTF-8' LC_CTYPE = 'ru_RU.UTF-8' TEMPLATE = template0;

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

Синтаксис:

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

Пример:

psql users < /tmp/users.dump

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

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

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

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

Из файла gz

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

gunzip users.dump.gz
psql users < users.dump

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

zcat users.dump.gz | psql users

Определенную базу

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

psql users < /tmp/database.dump

Если делался полный дамп (всех баз), восстановить определенную можно при помощи утилиты pg_restore с параметром -d:

pg_restore -d users cluster.bak

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

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

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

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

pg_restore -d "postgresql://postgres_user:postgres_pass@localhost/postgres_base" -Fc users.bak

* в данном примере мы подключимся к локальной базе (localhost) с названием postgres_base от пользователя postgres_user с паролем postgres_pass.

Работа с CSV

Мы можем переносить данные с использованием файлов csv. Это нельзя назвать напрямую резервным копированием, но в рамках данной инструкции материал будет интересен.

Создание файла CSV (экспорт)

Пример запроса (выполняется в командной оболочке SQL):

> COPY (SELECT * FROM public.users WHERE name LIKE 'А%') TO '/tmp/users.csv' WITH CSV DELIMITER ';' HEADER;

* в данном примере мы выгрузим все данные для таблицы users в схеме public, где значение поля name начинается с буквы А. Результат будет сохранен в файл /tmp/users.csv. Также мы указываем, что в качестве разделителя данных нужно использовать точку с запятой и первой строкой сделать заголовок.

Также мы можем сделать выгрузку, но сделать вывод в оболочку и перенаправить его в файл:

psql -d "postgresql://pg_user:pg_pass@localhost:5432/pg_databasename" -c "COPY (SELECT * FROM public.users WHERE name LIKE 'А%') TO STDIN WITH CSV DELIMITER ';' HEADER;" > /tmp/users.csv

Импорт данных из файла CSV

Также можно выполнить запрос в оболочке SQL:

> COPY public.users FROM '/tmp/test.csv' DELIMITER ';' CSV HEADER;

Или перенаправить запрос через STDOUT из файла:

psql -d "postgresql://pg_user:pg_pass@localhost:5432/pg_databasename" -c "COPY public.users FROM STDOUT DELIMITER ';' CSV HEADER;" < /tmp/users.csv

* в нашем примере мы выполним импорт данных из ранее созданного файла /tmp/users.csv в таблицу users.

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

Рассмотрим некоторые проблемы, с которыми можно столкнуться при работе с дампами PostgreSQL.

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

No password supplied

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

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

Неверная команда \

Причина: при выполнении восстановления возникла ошибка, которую СУБД не показывает при стандартных параметрах восстановления.

Решение: запускаем восстановление с опцией -v ON_ERROR_STOP=1, например:

psql -v ON_ERROR_STOP=1 users < /tmp/users.dump

Теперь, когда возникнет ошибка, система прекратит выполнять операцию и выведет сообщение на экран.