Как сделать дамп базы данных postgresql

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

pg_dump имя_базы > файл_дампа

Как видите, pg_dump записывает результаты своей работы в устройство стандартного вывода. Далее будет рассмотрено, чем это может быть полезно. В то время как вышеупомянутая команда создаёт текстовый файл, pg_dump может создать файлы и в других форматах, которые допускают параллельную обработку и более гибкое управление восстановлением объектов.

Программа pg_dump является для Postgres Pro обычным клиентским приложением (хотя и весьма умным). Это означает, что вы можете выполнять процедуру резервного копирования с любого удалённого компьютера, если имеете доступ к нужной базе данных. Но помните, что pg_dump не использует для своей работы какие-то специальные привилегии. В частности, ей обычно требуется доступ на чтение всех таблиц, которые вы хотите выгрузить, так что для копирования всей базы данных практически всегда её нужно запускать с правами суперпользователя СУБД. (Если у вас нет достаточных прав для резервного копирования всей базы данных, вы тем не менее можете сделать резервную копию той части базы, доступ к которой у вас есть, используя такие параметры, как -n схема или -t таблица .)

Указать, к какому серверу должна подключаться программа pg_dump , можно с помощью аргументов командной строки -h сервер и -p порт . По умолчанию в качестве сервера выбирается localhost или значение, указанное в переменной окружения PGHOST . Подобным образом, по умолчанию используется порт, заданный в переменной окружения PGPORT , а если она не задана, то порт, указанный по умолчанию при компиляции. (Для удобства при компиляции сервера обычно устанавливается то же значение по умолчанию.)

Как и любое другое клиентское приложение Postgres Pro , pg_dump по умолчанию будет подключаться к базе данных с именем пользователя, совпадающим с именем текущего пользователя операционной системы. Чтобы переопределить имя, либо добавьте параметр -U , либо установите переменную окружения PGUSER . Помните, что pg_dump подключается к серверу через обычные механизмы проверки подлинности клиента (которые описываются в Главе 19).

Важное преимущество pg_dump в сравнении с другими методами резервного копирования, описанными далее, состоит в том, что вывод pg_dump обычно можно загрузить в более новые версии Postgres Pro , в то время как резервная копия на уровне файловой системы и непрерывное архивирование жёстко зависят от версии сервера. Также, только метод с применением pg_dump будет работать при переносе базы данных на другую машинную архитектуру, например, при переносе с 32-битной на 64-битную версию сервера.

Дампы, создаваемые pg_dump , являются внутренне согласованными, то есть, дамп представляет собой снимок базы данных на момент начала запуска pg_dump . pg_dump не блокирует другие операции с базой данных во время своей работы. (Исключение составляют операции, которым нужна исключительная блокировка, как например, большинство форм команды ALTER TABLE .)

24.1.1. Восстановление дампа

Текстовые файлы, созданные pg_dump , предназначаются для последующего чтения программой psql . Общий вид команды для восстановления дампа:

psql имя_базы < файл_дампа

где файл_дампа — это файл, содержащий вывод команды pg_dump . База данных, заданная параметром имя_базы , не будет создана данной командой, так что вы должны создать её сами из базы template0 перед запуском psql (например, с помощью команды createdb -T template0 имя_базы ). Программа psql принимает параметры, указывающие сервер, к которому осуществляется подключение, и имя пользователя, подобно pg_dump . За дополнительными сведениями обратитесь к справке по psql . Дампы, выгруженные не в текстовом формате, восстанавливаются утилитой pg_restore .

Перед восстановлением SQL-дампа все пользователи, которые владели объектами или имели права на объекты в выгруженной базе данных, должны уже существовать. Если их нет, при восстановлении будут ошибки пересоздания объектов с изначальными владельцами и/или правами. (Иногда это желаемый результат, но обычно нет).

По умолчанию, если происходит ошибка SQL, программа psql продолжает выполнение. Если же запустить psql с установленной переменной ON_ERROR_STOP , это поведение поменяется и psql завершится с кодом 3 в случае возникновения ошибки SQL:

psql --set ON_ERROR_STOP=on имя_базы < файл_дампа

В любом случае вы получите только частично восстановленную базу данных. В качестве альтернативы можно указать, что весь дамп должен быть восстановлен в одной транзакции, так что восстановление либо полностью выполнится, либо полностью отменится. Включить данный режим можно, передав psql аргумент -1 или —single-transaction . Выбирая этот режим, учтите, что даже незначительная ошибка может привести к откату восстановления, которое могло продолжаться несколько часов. Однако это всё же может быть предпочтительней, чем вручную вычищать сложную базу данных после частично восстановленного дампа.

Благодаря способности pg_dump и psql писать и читать каналы ввода/вывода, можно скопировать базу данных непосредственно с одного сервера на другой, например:

pg_dump -h host1 имя_базы | psql -h host2 имя_базы

Важно

Дампы, которые выдаёт pg_dump , содержат определения относительно template0 . Это означает, что любые языки, процедуры и т. п., добавленные в базу через template1 , pg_dump также выгрузит в дамп. Как следствие, если при восстановлении вы используете модифицированный template1 , вы должны создать пустую базу данных из template0 , как показано в примере выше.

После восстановления резервной копии имеет смысл запустить ANALYZE для каждой базы данных, чтобы оптимизатор запросов получил полезную статистику; за подробностями обратитесь к Подразделу 23.1.3 и Подразделу 23.1.6. Другие советы по эффективной загрузке больших объёмов данных в Postgres Pro вы можете найти в Разделе 14.4.

24.1.2. Использование pg_dumpall

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

pg_dumpall > файл_дампа

Полученную копию можно восстановить с помощью psql :

psql -f файл_дампа postgres

(В принципе, здесь в качестве начальной базы данных можно указать имя любой существующей базы, но если вы загружаете дамп в пустой кластер, обычно нужно использовать postgres ). Восстанавливать дамп, который выдала pg_dumpall , всегда необходимо с правами суперпользователя, так как они требуются для восстановления информации о ролях и табличных пространствах. Если вы используете табличные пространства, убедитесь, что пути к табличным пространствам в дампе соответствуют новой среде.

pg_dumpall выдаёт команды, которые заново создают роли, табличные пространства и пустые базы данных, а затем вызывает для каждой базы pg_dump . Таким образом, хотя каждая база данных будет внутренне согласованной, состояние разных баз не будет синхронным.

Только глобальные данные кластера можно выгрузить, передав pg_dumpall ключ —globals-only . Это необходимо, чтобы полностью скопировать кластер, когда pg_dump выполняется для отдельных баз данных.

24.1.3. Управление большими базами данных

Некоторые операционные системы накладывают ограничение на максимальный размер файла, что приводит к проблемам при создании больших файлов с помощью pg_dump . К счастью, pg_dump может писать в стандартный вывод, так что вы можете использовать стандартные инструменты Unix для того, чтобы избежать потенциальных проблем. Вот несколько возможных методов:

Используйте сжатые дампы. Вы можете использовать предпочитаемую программу сжатия, например gzip :

pg_dump имя_базы | gzip > имя_файла.gz

Затем загрузить сжатый дамп можно командой:

gunzip -c имя_файла.gz | psql имя_базы

cat имя_файла.gz | gunzip | psql имя_базы

Используйте split . Команда split может разбивать выводимые данные на небольшие файлы, размер которых удовлетворяет ограничению нижележащей файловой системы. Например, чтобы получить части по 2 гигабайта:

pg_dump имя_базы | split -b 2G - имя_файла

Восстановить их можно так:

cat имя_файла* | psql имя_базы

Использовать GNU split можно вместе с gzip :

pg_dump имя_базы | split -b 2G --filter='gzip > $FILE.gz'

Восстановить данные после такого разбиения можно с помощью команды zcat .

Используйте специальный формат дампа pg_dump . Если при сборке Postgres Pro была подключена библиотека zlib , дамп в специальном формате будет записываться в файл в сжатом виде. В таком формате размер файла дампа будет близок к размеру, полученному с применением gzip , но он лучше тем, что позволяет восстанавливать таблицы выборочно. Следующая команда выгружает базу данных в специальном формате:

pg_dump -Fc имя_базы > имя_файла

Дамп в специальном формате не является скриптом для psql и должен восстанавливаться с помощью команды pg_restore , например:

pg_restore -d имя_базы имя_файла

За подробностями обратитесь к справке по командам pg_dump и pg_restore .

Для очень больших баз данных может понадобиться сочетать split с одним из двух других методов.

Используйте возможность параллельной выгрузки в pg_dump . Чтобы ускорить выгрузку большой БД, вы можете использовать режим параллельной выгрузки в pg_dump . При этом одновременно будут выгружаться несколько таблиц. Управлять числом параллельных заданий позволяет параметр -j . Параллельная выгрузка поддерживается только для формата архива в каталоге.

pg_dump -j число -F d -f выходной_каталог имя_базы

Вы также можете восстановить копию в параллельном режиме с помощью pg_restore -j . Это поддерживается для любого архива в формате каталога или специальном формате, даже если архив создавался не командой pg_dump -j .

Пред.	Наверх	След.
Глава 24. Резервное копирование и восстановление	Начало	24.2. Резервное копирование на уровне файлов

Создание и импорт дампа БД PostgreSQL

Для создания дампа БД PostgreSQL следует использовать в консоли SSH команду следующего вида:

pg_dump -h hostname -U username -F format -f dumpfile dbname

hostname — имя сервера БД;
username — имя пользователя БД (совпадает с именем базы данных);
format — формат дампа (может быть одной из трех букв: ‘с’ (custom — архив .tar.gz), ‘t’ (tar — tar-файл), ‘p’ (plain — текстовый файл). В команде букву надо указывать без кавычек.);
dumpfile — имя создаваемого файла дампа;
dbname — имя базы данных.

Для баз созданных до 16.09.2019 имя хоста будет выглядеть так: pg.sweb.ru; для баз данных, которые были созданы после 16.09.2019 имя хоста будет таким: pg2.sweb.ru. Для баз данных созданных после 24.04.2023 имя хоста будет таким: pg3.sweb.ru

После завершения задачи файл с именем dumpfile будет размещен в директории, из которой запускалась команда.

Пример создания дампа базы vh36sup в файл архива формата postgress. где custom — архив, в формате самого postgress:

pg_dump -h pg2.sweb.ru -U vh36sup -F c -f dump.tar.gz vhsup

Импорт дампа БД PostgreSQL

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

pg_restore -h hostname -U username -F format -d dbname dumpfile

Параметры аналогичные, за исключением того, что format может быть либо ‘c’, либо ‘t’.

Пример загрузки архива дампа dump.tar.gz в базу vhsup:

pg_restore -h pg2.sweb.ru -U vhsup -F c -d vhsup dump.tar.gz

Дампы представленные в виде текстового файла можно импортировать с помощью следующей команды:

cat dumpfile | psql -h hostname -U username dbname

Перенос базы данных PostgreSQL с помощью дампа и ее восстановление

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

Предварительные требования

Перед началом работы требуется наличие:

Сервера БД для PostgreSQL. В правилах брандмауэра необходимо указать, что доступ к данному серверу разрешен.
Установить в командной строке программы pg_dump (для извлечения БД в файл дампа) и pg_restore (для восстановления БД из файла архива).

Создание дампа с нужными данными для загрузки

Можно создавать бэкап базы данных PostgreSQL локально или на виртуальной машине. В первом случае требуется использовать команду:

pg_dump -Fc -v —host=localhost —username=masterlogin —dbname= -f testdb.dump

Во втором случае:

Восстановление данных в целевую БД

Когда целевая БД будет создана, можно применить команду pg_restore – восстановление базы из дампа.

Если использовать параметр —no-owner, то все объекты, которые были восстановлены, автоматически присваиваются пользователю, который будет отмечен в параметре —username. Более подробную информацию об этом можно найти в открытых источниках или документации PostgreSQL.

Важно. Иногда сервер требует наличие соединений TLS и SSL, но они на нем отсутствуют по каким-то причинам. Тогда нужно применить переменную PGSSLMODE=require. Иногда утилита работает без данного протокола, но есть высокий риск появления ошибки FATAL. Тогда администратору сети перед выполнением pg_restore нужно выполнить команду

если у вас операционная система Windows. При использовании ОС Linux нужно будет прописать команду

Рассмотрим еще несколько команд более подробно.

Утилита pg_dumpall

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

Для сжатия бэкапа нужно передавать информацию на архиватор gzip

pg_dumpall | gzip > /tmp/instance.tar.gz

Это довольно простая и понятная в использовании утилита, поэтому многие системные администраторы применяют ее в своей работе.

Утилита pg_basebackup

Она позволяет выполнять бэкап работающего кластера БД PostgreSQL. Итоговый бинарный файл применяется для восстановления БД в конкретное состояние в прошлом. Отличительная возможность этой утилиты – создание полноценной копии. В данном случае создать резервные копии отдельных сущностей не получится. Подключение к PostgreSQL происходит с использованием протокола репликации с правами админа или с правом REPLICATION.

С параметром —D, который обозначает директорию, где будет находиться бэкап. Параметры —Ft и —z отвечают за сжатие резервной копии.

Утилита wal-g

С помощью данной утилиты удается сохранять бэкапы на файловой системе или на серверах S3.

Готовый установочный пакет можно найти на github. Далее нам потребуется скопировать папку с исполняемыми файлами и заполнить конфигурационный файл. После этого можно задать настройки для автоматического создания бэкапов по расписанию. Рекомендуется первую резервную копию сделать вручную, чтобы проверить, что все настройки заданы правильно и сохранение происходит корректно.

Утилита pgAdmin

Эта утилита позволяет создавать резервные копии с использованием графического интерфейса. Запустить данное web-приложение можно как на локальном устройстве, так и на сервере. Оно работает на различных операционных системах. Актуальную версию рекомендуется скачивать с официального сайта.

Заключение

Создавать резервные копии с помощью утилит достаточно просто. Необходимо выбрать подходящую, исходя из собственного опыта и задач проекта. Автоматизация процесса позволит освободить время системного администратора.

Создание резервных копий позволяет повысить отказоустойчивость системы, поэтому рекомендуется использовать их в проектах любого уровня. Чем чаще происходит изменение БД, тем чаще нужно создавать бэкап, чтобы в случае форс-мажора пришлось выполнять минимальную работу для восстановления потерянной информации.

Резервное копирование и восстановление баз данных PostgreSQL

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

Что такое PostgreSQL

PostgreSQL — это объектно-реляционная система управления базами данных. Она относится к категории свободно распространяемых и имеет открытый исходный код.

Какие преимущества имеет PostgreSQL относительно других СУБД:

возможность работать в реляционном и объектном подходе;
поддержка разных форматов данных: например XML, JSON или NoSQL;
нет ограничений на объем базы данных и количество записей в ней;
возможность писать функции на разных языках программирования;
поддержка составных запросов;
доступ к базе с нескольких устройств одновременно и другие.

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