Логическое резервирование и восстановление (SQL)

pg_dumpall - выполняет резервную копию всех БД (включая системную). Представляет меньший контроль над процессом создания дампа, чем утилита pg_dump. Лучше использовать pg_dump, а утилитой pg_dumpall с ключем -g сохранить только глобальные данные сервера(пользователи, группы, табличные пространства).
pg_dump - выполняет резервирование одной базы данных
Ключи pg_dump:
-t, –table=TABLE позволяет помещать в дамп только таблицы (или представления или последовательности или внешние таблицы), соответствующие table. Несколько таблиц могут быть выбраны написанием их всех с ключем -t. Также параметр table интерпретируется как шаблон в соответствии с теми же правилами используемыми командой \d консольного клиента psql, поэтому несколько таблиц может быть выбрано написанием маски символов в шаблоне. Когда -t задана, то pg_dump не пытается сбросить в дамп любые другие объекты базы данных от которых может зависеть выбранная таблица(ы). Поэтому нет никакой гарантии, что результаты дампа конкретной таблицы могут быть успешно восстановлены сами по себе в чистую базу данных.
-T, –exclude-table=TABLE Не сбрасывать в дамп любые таблицы соответствующие шаблону table. Шаблон интерпретируется в соответствии с теме же правилами, что и для -t. -T может быть задано более одного раза, исключая схемы соответствующие любым из нескольких шаблонов. Когда заданы обе опции -t и -T в дамп сбрасываются только те таблицы, которые соответствуют хотя бы одному из заданных вхождений -n и ни одному -N. При наличии -T без заданных -t, таблицы соответствующие перечисленным в -T исключаются из дампа.
-v, –verbose Установить подробный режим. В этом случае pg_dump будет выводить детальные комментарии объекта и время пуска/завершения создания файла дампа, и сообщения хода выполнения направляются на стандартный поток ошибок.
Логическое резервирование заключается в создании текстового файла с командами SQL. Такой файл можно передать обратно на сервер и воссоздать базу данных в том же состоянии, в котором она была во время бэкапа. У PostgreSQL для этого есть специальная утилита - pg_dump. При выполнении pg_dump, таблицы блокируются минимально, только запрет на изменение структуры таблицы.

Создаем бэкап с помощью pg_dump

pg_dump -U postgres dbname > outfile

Для восстановления такого бэкапа достаточно выполнить:

psql -U postgres dbname < infile

базу данных «dbname» потребуется создать перед восстановлением и пользователя (которому принадлежит восстанавливаемая база данных)

postgres=# CREATE DATABASE dbname;

После восстановления бэкапа желательно запустить «ANALYZE», чтобы оптимизатор запросов обновил статистику.
Бекап только одной таблицы codes из БД testbd777

pg_dump -U postgres testbd777 -t codes > codes_backup`date +%d.%m.%Y-%H.%M`.sql

Ключ -s (–schema-only) позволяет создать только схему БД без данных, например

$ sudo -u postgres pg_dump -s utks > utks_schema.sql

Ключ –inserts позволяет сделать бекап в формате SQL

# pg_dump -U postgres --inserts testbd777 -t codes > codes_backup`date +%d.%m.%Y-%H.%M`.sql

Восстановление всего бекапа с остановкой на первой ошибке

psql -h localhost -U postgres --set ON_ERROR_STOP=on -f mydb.sql

Пример:

Полное логическое (SQL) резервирование и восстановление БД utks. Алгоритм:

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

sudo -u postgres pg_dumpall --globals-only > globals-only_`date +%Y-%m-%d.%H.%M`.sql

Можно сохранить определение объектов базы данных: роли, табличные пространства, схемы, индексы, триггеры и т.д.

sudo -u postgres pg_dumpall --schema-only > schema-only_`date +%d.%m.%Y-%H.%M`.sql

Копируем данные. Копируем каждую базу данных при помощи утилиты pg_dump

pg_dump -U postgres utks 
gzip  > utks_backup_`date +%Y-%m-%d.%H.%M`.sql.gz

Перед восстановлением нужно создать базу данных utks

sudo -u postgres createdb utks 

Восстановление пользовательских ролей, групп

psql -U postgres -f globals-only.sql

Восстановление данных БД utks из сжатого бекапа.

gunzip -c utks_backup.sql.gz | psql -U postgres utks

Желательно запустить ANALYZE для свежевосстановленной базы данных.

utks=# ANALYZE VERBOSE;