26.1. SQL-дамп

Работа с CSV

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

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

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

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

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

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

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

Параметры

Параметры командной строки для управления содержимым и форматом вывода.

Указывает имя базы данных, из которой будут выгружаться данные. Если имя не задано, то используется значение переменной окружения PGDATABASE. Если и переменная не задана, то в качестве имени базы будет взято имя пользователя, под которым осуществляется подключение.

Выводить только данные, но не схемы объектов (DDL). Будут копироваться данные таблиц, большие объекты, значения последовательностей.

Флаг похож на —section=data, но по историческим причинам не равнозначен ему.

Включить большие объекты в выгрузку. Это поведение по умолчанию при отсутствии ключей —schema, —table или —schema-only. Таким образом, ключ -b полезен, лишь когда нужно добавить большие объекты при выгрузке только избранной схемы или таблицы. Заметьте, что большие объекты относятся к данным, и поэтому будут выгружаться, когда используется ключ —data-only, но не ключ —schema-only.

Исключить из выгрузки большие объекты.

Когда задаётся и -b, и -B, большие объекты при выгрузке данных будут выводиться (см. описание ключа -b).

Включить в выходной файл команды удаления (DROP) объектов базы данных перед командами создания (CREATE) этих объектов. Если дополнительно не указать флаг —if-exists, то при восстановлении в базу данных, где некоторые объекты отсутствуют, попытка удаления несуществующего объекта будет приводить к ошибке, которую можно игнорировать.

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

Сформировать в начале вывода команду для создания базы данных и затем подключения к ней. В этом случае не важно, какая база указана в параметрах подключения перед выполнением скрипта. Также, если указан ключ —clean, то скрипт сначала удалит, а затем пересоздаст базу данных перед подключением к ней.

Выгрузить только расширения, соответствующие шаблону. Когда этот параметр отсутствует, выгружаются все несистемные расширения в целевой базе данных. Чтобы выгрузить несколько расширений, ключ -e можно задать несколько раз. Параметр шаблон интерпретируется по тем же правилам, что и шаблон в командах d (см. Шаблоны поиска ниже), так что несколько расширений можно выбрать и шаблоном со знаками подстановки. Используя знаки подстановки, при необходимости заключайте шаблон в кавычки, чтобы эти знаки не разворачивала оболочка системы.

Для расширения, указанного в —extension, в выгрузку включаются все его конфигурационные данные, зарегистрированные функцией pg_extension_config_dump.

Примечание

При использовании -e, не выгружает прочие объекты, от которых выгружаемые расширения могут зависеть. Таким образом не гарантируется, что выгруженные расширения будут успешно восстановлены в чистой базе данных.

Создать копию в заданной кодировке. По умолчанию копия создаётся в кодировке, используемой базой данных. Другой способ достичь того же результата — задать желаемую кодировку в переменной окружения PGCLIENTENCODING. Поддерживаемые кодировки описаны в Подразделе 24.3.1.

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

Указывает формат вывода копии. format может принимать следующие значения:

Сформировать текстовый SQL-скрипт. Это поведение по умолчанию.

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

Выгрузить данные в формате каталога. Этот формат пригоден для дальнейшего использования утилитой . При этом будет создан каталог, в котором для каждой таблицы и большого объекта будут созданы отдельные файлы, а также файл оглавления в машинно-читаемом формате, понятном для . С полученной резервной копией можно работать штатными средствами Unix, например, несжатую копию можно сжать посредством . Этот формат по умолчанию сжимается, а также поддерживает работу в несколько потоков.

Выгрузить данные в формате tar, для дальнейшего использования с утилитой . Этот формат совместим с форматом вывода в каталог: если архив распаковать, получится корректная копия в формате каталога. Однако формат tar не поддерживает сжатие. Также, применяя формат tar, при восстановлении нельзя изменить относительный порядок элементов данных.

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

откроет число_заданий + 1 соединений с базой данных. Таким образом необходимо обеспечить достаточное значение параметра max_connections.

Для выполнения параллельной выгрузки сервер баз данных должен поддерживать функциональность синхронизированных снимков, которая была введена в версии 9.2 для ведущих серверов и в 10 для ведомых. Это позволяет разным клиентам работать с одной и той же версией данных, несмотря на использование разных подключений. pg_dump -j использует множественные подключения. Первое подключение осуществляется головным процессом, а последующие — рабочими процессами. Без функциональности синхронизируемых снимков нет гарантии того, что каждое подключение увидит одни и те же данные, что может привести к несогласованности данных резервной копии.

Выгрузить только схемы, соответствующие шаблону; вместе с этими схемами будут выгружены и все содержащиеся в них объекты. Когда этот параметр отсутствует, выгружаются все несистемные схемы в целевой базе данных. Чтобы выгрузить несколько схем, ключ -n можно указать несколько раз. Параметр шаблон интерпретируется по тем же правилам, что и шаблон в командах d (см. Шаблоны поиска ниже), так что несколько схем можно выбрать и шаблоном со знаками подстановки. Используя знаки подстановки, при необходимости заключайте шаблон в кавычки, чтобы эти знаки не разворачивала оболочка системы; см. Примеры ниже.

При использовании -n, не выгружает объекты других схем, от которых выгружаемая схема может зависеть. Таким образом не гарантируется, что выгруженная схема будет успешно восстановлена в чистой базе данных.

Не принадлежащие схемам объекты (например, большие бинарные объекты), не выгружаются с параметром -n. Однако можно указать —blobs, чтобы они попали в выгрузку.

Не выгружать схемы, соответствующие шаблону. Шаблон интерпретируется по тем же правилам, что и для параметра -n. Параметр -N можно использовать в команде несколько раз для исключения схем, соответствующих нескольким шаблонам.

При одновременном использовании параметров -n и -N будут выгружаться схемы, соответствующие шаблону параметра -n и не противоречащие шаблону параметра -N.

Не формировать команды, устанавливающие владельца объектов базы данных. По умолчанию генерирует команды ALTER OWNER или SET SESSION AUTHORIZATION для назначения владельцев объектов базы. Эти команды завершатся неудачно, если скрипт будет запущен не суперпользователем или не владельцем объектов. Чтобы создать скрипт, который можно выполнить при восстановлении от лица произвольного пользователя и назначить его в качестве владельца объектов восстанавливаемой базы, необходимо указать флаг -O.

Параметр является устаревшим, но в целях совместимости ещё работает.

Выгружать только определения объектов (схемы), без данных.

Действие параметра противоположно действию —data-only. Это похоже на указание —section=pre-data —section=post-data, но по историческим причинам не равнозначно ему.

(Не путайте этот параметр с —schema, где слово используется в другом значении.)

Чтобы не выгружать данные отдельных таблиц, используйте параметр —exclude-table-data.

Указать суперпользователя, который будет использоваться для отключения триггеров. Параметр имеет значение только вместе с —disable-triggers. Обычно его лучше не использовать, а запускать полученный скрипт от имени суперпользователя.

Выгрузить только таблицы, соответствующие шаблону. Чтобы выбрать несколько таблиц, ключ -t можно указать несколько раз. Параметр шаблон интерпретируется по тем же правилам, что и шаблон в командах d (см. Шаблоны поиска), так что несколько таблиц можно выбрать и шаблоном со знаками подстановки. Используя знаки подстановки, при необходимости заключайте шаблон в кавычки, чтобы эти знаки не разворачивала оболочка системы; см. Примеры.

Помимо обычных таблиц, с этим указанием можно выгрузить определения соответствующих шаблону последовательностей, представлений, материализованных представлений и сторонних таблиц. При этом содержимое представлений и матпредставлений выгружаться не будет, а содержимое сторонних таблиц будет выгружено, только если в аргументе —include-foreign-data указан соответствующий сторонний сервер.

Параметры -n и -N не действуют в присутствии параметра -t, так как отобранные им таблицы всё равно будут выгружены, а не табличные объекты выгружаться не будут.

При использовании -t, не выгружает прочие объекты, от которых выгружаемые таблицы могут зависеть. Таким образом не гарантируется, что выгруженные таблицы будут успешно восстановлены в чистой базе данных.

Не выгружать таблицы, соответствующие шаблону. Шаблон интерпретируется по тем же правилам, что и для параметра -t. Параметр -T можно использовать в команде несколько раз для исключения таблиц, соответствующих нескольким шаблонам.

При одновременном использовании параметров -t и -T будут выгружаться таблицы, соответствующие шаблону параметра -t и не противоречащие шаблону параметра -T.

Вывести версию .

Не выгружать права доступа (команды GRANT/REVOKE).

Установить уровень сжатия данных. Ноль означает, что сжатие выключено. Для специального формата и формата каталога будут сжиматься файлы отдельных таблиц. По умолчанию применяется умеренный уровень сжатия. Если указать отличный от нулевого уровень сжатия для простого формата, то сжиматься будет весь выходной файл, как это было бы при передаче файла команде . Однако по умолчанию для простого формата сжатие не производится. Формат tar в настоящий момент не поддерживает сжатие.

Этот параметр предназначен для утилит обновления сервера. Использование для иных целей не рекомендуется и не поддерживается. Поведение параметра может быть изменено в последующих версиях без предварительного уведомления.

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

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

В настоящее время команды, генерируемые с параметром —disable-triggers, должны исполняться от имени суперпользователя. Таким образом, необходимо также передавать флаг -S, либо при восстановлении выполнять скрипт от имени суперпользователя.

Этот параметр имеет смысл только при выгрузке содержимого таблицы, для которой включена защита строк. По умолчанию устанавливает для row_security значение off, чтобы убедиться, что выгружаются все данные из таблицы. Если пользователь не имеет достаточных прав для обхода защиты строк, выдаётся ошибка. Этот параметр указывает включить row_security, что позволит пользователю выгрузить часть содержимого таблицы, к которой он имеет доступ.

Заметьте, что в настоящее время для использования этого параметра обычно желательно, чтобы данные были выгружены в формате INSERT, так как команда COPY FROM в процессе восстановления не поддерживает защиту строк.

Не выгружать содержимое таблиц, соответствующих шаблону. Шаблон таблицы интерпретируется по тем же правилам, что и для параметра -t. Параметр —exclude-table-data можно использовать в команде несколько раз для исключения таблиц, соответствующих нескольким шаблонам. Полезно, когда нужно получить определение таблицы, без содержимого.

Чтобы не выгружать содержимое всех таблиц базы, используйте параметр —schema-only.

Выводить числа с плавающей точкой не с максимальной точностью, а с заданным значением extra_float_digits. При выгрузке данных в целях резервного копирования данный параметр использовать не следует.

При очистке целевой базы использовать условные команды (добавлять предложение IF EXISTS). Применяется только с параметром —clean.

Выгрузить данные всех сторонних таблиц со стороннего сервера, имя которого соответствует шаблону сторонний_сервер. Для выгрузки данных с нескольких сторонних серверов параметр —include-foreign-data можно использовать несколько раз. К тому же, значение сторонний_сервер интерпретируется как шаблон, согласно правилам, используемым командами d утилиты (см. Шаблоны поиска ниже). Поэтому несколько сторонних серверов можно также выбрать, используя в шаблоне символы подстановки. Когда используются символы подстановки, шаблон лучше экранировать кавычками, чтобы командная оболочка ОС не интерпретировала их по-своему (см. Примеры ниже). Единственное отличие от вышеупомянутых правил — шаблон не может быть пустым.

Когда используется параметр —include-foreign-data, не проверяет возможность записи в стороннюю таблицу. Таким образом, не гарантируется, что выгруженная сторонняя таблица может быть успешно восстановлена.

Выгружать данные таблиц в виде команд INSERT вместо COPY. Скорость восстановления при этом значительно снизится, но данный вариант оправдан, когда загружать данные нужно не в . При этом в случае каких-либо ошибок при загрузке данных будут потеряны только строки INSERT, где возникли ошибки, но не всё содержимое таблицы. Заметьте, что восстановление может провалиться полностью, если у таблицы изменён порядок столбцов. В такой ситуации можно использовать параметр —column-inserts, для которого порядок столбцов не важен, но он работает ещё медленнее.

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

Не ждать бесконечно получения разделяемых блокировок таблиц в начале процедуры выгрузки. Вместо этого выдать ошибку, если не удастся заблокировать таблицы за указанное время_ожидания. Это время можно задать в любом из форматов, принимаемых командой SET statement_timeout. ( Допустимые форматы зависят от версии сервера, выгружающего данные, но количество миллисекунд в виде целого числа принимают все версии.)

Не выгружать публикации.

Не выгружать метки безопасности.

Не выгружать подписки.

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

Не формировать команды для указания табличных методов доступа. При восстановлении все объекты будут создаваться с табличным методом доступа, выбираемым по умолчанию.

Не формировать команды для указания табличных пространств. При восстановлении все объекты будут создаваться в табличном пространстве по умолчанию.

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

Не выгружать данные нежурналируемых таблиц и последовательностей. Параметр не влияет на выгрузку определений таблиц и последовательностей, он только подавляет вывод их содержимого. С резервного сервера содержимое нежурналируемых таблиц и последовательностей не выгружается никогда.

Добавить предложения ON CONFLICT DO NOTHING в команды INSERT. Это указание допускается только при выборе режима —inserts, —column-inserts или —rows-per-insert.

Принудительно экранировать все идентификаторы. Этот параметр рекомендуется при выгрузке базы, когда основная версия сервера , с которого выгружается база, отличается от версии , или когда выгруженная копия предназначена для загрузки на сервере с другой основной версией. По умолчанию экранирует только те идентификаторы, которые являются зарезервированными словами в собственной основной версии. Иногда это приводит к проблемам совместимости с серверами других версий, в которых множество зарезервированных слов может быть несколько другим. Применение параметра —quote-all-identifiers предотвращает подобные проблемы, ценой ухудшения читаемости скрипта с выгруженными данными.

Выгружать данные таблиц в виде команд INSERT вместо COPY. В данном параметре задаётся максимальное число строк для одной команды INSERT. Указанное в нём значение должно быть больше 0. При этом в случае каких-либо ошибок при загрузке данных будут потеряны только строки INSERT, где возникли ошибки, но не всё содержимое таблицы.

Выгружать лишь указанную секцию. Имя секции может принимать значения pre-data, data или post-data. Для выгрузки нескольких секций, параметр можно использовать несколько раз в одной команде. По умолчанию резервируются все секции.

Секция data содержит непосредственно данные таблиц, больших объектов и значения последовательностей. Секция post-data содержит определения индексов, триггеров, правил и ограничений (кроме ограничений проверки, созданных без NOT VALID ). Секция pre-data включает определения остальных элементов.

Использовать при выгрузке транзакцию с уровнем изоляции serializable для получения снимка, согласованного с последующими состояниями базы. Правда для этого нужно выждать момент, когда в потоке транзакций нет аномалий, и поэтому нет риска, что выгрузка завершится неудачно, и риска отката других транзакций с ошибкой serialization_failure. Более подробно изоляция транзакций и управление одновременным доступом описывается в Главе 13.

Параметр не особо полезен в случаях, когда требуется восстановление после сбоя. Он полезен для создания копии базы данных, в которой формируются отчёты и выполняются другие операции чтения, в то время как в основной базе продолжается обычная работа. Без этого параметра выгрузка может содержать не целостное состояние базы данных. Например, если используется пакетная обработка, статус пакета может отражаться как завершённый, в то время как в выгрузке будут не все элементы пакета.

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

Использовать заданный синхронный снимок при выгрузке данных из базы (за подробностями обратитесь к Таблице 9.92).

Этот параметр полезен, когда требуется синхронизировать выгружаемые данные со слотом логической репликации (см. Главу 49) или с другим одновременным сеансом.

В случае с параллельной выгрузкой будет использоваться имя снимка, определённое этим параметром; новый снимок не будет сделан.

Требует, чтобы каждому указанию расширения (-e/—extension), схемы (-n/—schema) и таблицы (-t/—table) соответствовал минимум один объект расширение/схема/таблица в выгружаемой базе данных. Заметьте, что если не находится вообще ни одного такого объекта для заданных шаблонов, выдаёт ошибку и без ключа —strict-names.

Этот параметр не действует на ключи -N/—exclude-schema, -T/—exclude-table или —exclude-table-data. Если не находятся объекты, соответствующие шаблонам исключения, это не считается ошибкой.

Выводить команды SET SESSION AUTHORIZATION, соответствующие стандарту, вместо ALTER OWNER, для назначения владельцев объектов. В результате выгруженный скрипт будет более стандартизированным, но может не восстановиться корректно, в зависимости от истории объектов. Кроме того, для использования SET SESSION AUTHORIZATION при восстановлении нужны права суперпользователя, в то время как ALTER OWNER требует меньших привилегий.

Показать справку по аргументам командной строки и завершиться.

Далее описаны параметры управления подключением.

Указывает имя базы данных для подключения. Равнозначно указанию имя_бд в первом аргументе, не являющемся ключом, в командной строке. Вместо имени может задаваться строка подключения. В этом случае параметры в строке подключения переопределяют одноимённые параметры, заданные в командной строке.

Указывает имя компьютера, на котором работает сервер. Если значение начинается с косой черты, оно определяет каталог Unix-сокета. Значение по умолчанию берётся из переменной окружения PGHOST, если она установлена. В противном случае выполняется подключение к Unix-сокету.

Указывает TCP-порт или расширение файла локального Unix-сокета, через который сервер принимает подключения. Значение по умолчанию определяется переменной окружения PGPORT, если она установлена, либо числом, заданным при компиляции.

Имя пользователя, под которым производится подключение.

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

Принудительно запрашивать пароль перед подключением к базе данных.

Это несущественный параметр, так как запрашивает пароль автоматически, если сервер проверяет подлинность по паролю. Однако чтобы понять это, лишний раз подключается к серверу. Поэтому иногда имеет смысл ввести -W, чтобы исключить эту ненужную попытку подключения.

Задаёт имя роли, которая будет осуществлять выгрузку. Получив это имя, выполнит SET ROLE имя_роли после подключения к базе данных. Это полезно, когда проходящий проверку пользователь (указанный в -U) не имеет прав, необходимых для , но может переключиться на роль, наделённую этими правами. В некоторых окружениях правила запрещают подключаться к серверу непосредственно суперпользователю, и этот параметр позволяет выполнить выгрузку, не нарушая их.

Перенос PostgreSQL базы данных между разными мажорными версиями

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

Будем использовать утилиту pg_dump или pg_dumpall. Обе они генерируют набор SQL-команд. Первая используется для создания бекапа конкретной базы данных, а вторая — при бекапе всего кластера. В последнем случае, кроме баз данных кластера копируются еще и его глобальные объекты — например, роли, что позволяет затем после развертывания бекапа не делать дополнительных действий в виде создания недостающих ролей, раздачи привилегий и т.п.

Создание дампа

На целевом сервере выполнить (желательно, чтобы на этот момент потребители его баз данных были остановлены и/или не проявляли активность по отношению к БД):

Накатывание дампа

Будем считать, что на новый сервер по тому же пути (/tmp/backups/foo.dump.gz) скопирован сделанный на предыдущем шаге дамп.

Находясь на новом сервере:

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

Включение сервисов

После окончания накатывания дампа нужно:

После этого перенос можно считать завершенным.

Создание резервных копий и восстановление из командной строки

В этом разделе мы расскажем как сделать дамп базы данных PostgreSQL в консоли при подключении по SSH, разберем синтаксис и покажем примеры использования утилит pg_dump, pg_dumpall, pg_restore, pg_basebackup и wal-g.

Утилита pg_dump

В PostgreSQL есть встроенный инструмент для создания резервных копий — утилита pg_dump. Утилита имеет простой синтаксис:

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

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

Ключ -U определяет пользователя, а -W обязывает ввести пароль.

Чтобы сэкономить место на диске, можно сразу же сжимать дамп:

Резервное копирование обычно выполняется по расписанию, например, ежедневно в 3 часа ночи. Нижеприведенный пример скрипта не только выполняет бэкап, но и удаляет все файлы старше 61 дня (за исключением 15-го числа месяца).

Чтобы настроить регулярное выполнение, выполним следующую команду в планировщике crontab:

# crontab -e
3 0 * * * /etc/scripts/pgsql_dump.sh # postgres pg dump

Чтобы выполнить аналогичную команду на удаленном сервере, достаточно добавить ключ -h:

Ключ -t задает таблицу, для которой нужно создать резервную копию:

При помощи специальных ключей можно создавать резервные копии структуры данных или непосредственно данных:

У утилиты pg_dump также есть ключи для сохранения дампа в другие форматы. Чтобы сохранить копию в виде бинарного файла используются ключи -Fc:

Чтобы создать архив — -Ft:

Чтобы сохранить в directory-формате — -Fd:

Резервное копирование в виде каталогов позволяет выполнять процесс в многопоточном режиме.

Ниже мы перечислим возможные параметры утилиты pg_dump.

-w, —no-password — деактивация требования ввода пароля.

-W, —password — активация требования ввода пароля.

—role=имя роли — роль, от имени которой генерируется резервная копия.

-a, —data-only — вывод только данных, вместо схемы объектов (DDL).

-b, —blobs — параметр добавляет в выгрузку большие объекты.

-c, —clean — добавление команд DROP перед командами CREATE в файл резервной копии.

-C, —create — генерация реквизитов для подключения к базе данных в файле резервной копии.

-o, —oids — добавляет в выгрузку идентификаторы объектов (OIDs) вместе с данными таблиц.

-O, —no-owner — деактивация создания команд, определяющих владельцев объектов в базе данных.

-s, —schema-only —добавление в выгрузку только схемы данных, без самих данных.

-v, —verbose — режим подробного логирования.

-V, —version — вывод версии pg_dump.

-Z 0.9, —compress=0.9 — установка уровня сжатия данных. 0 — сжатие выключено.

Утилита pg_dumpall

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

Чтобы сразу сжать резервную копию экземпляра базы данных, нужно передать вывод на архиватор gzip:

Ниже приведены параметры, с которыми может вызываться утилита pg_dumpall.

-a, —data-only — создание резервной копии без схемы данных.

-c, —clean — добавление операторов DROP перед операторами CREATE.

-g, —globals-only — выгрузка глобальных объектов без баз данных.

-o, —oids — выгрузка идентификаторов объектов (OIDs) вместе с данными таблиц.

-O, —no-owner — деактивация генерации команд, устанавливающих принадлежность объектов, как в исходной базе данных.

-r, —roles-only — выгрузка только ролей без баз данных и табличных пространств.

-s, —schema-only — выгрузка только схемы без самих данных.

-t, —tablespaces-only — выгрузка табличных пространства без баз данных и ролей.

-V (—version — вывод версии утилиты pg_dumpall.

Утилита pg_restore

Утилита позволяет восстанавливать данные из резервных копий. Например, чтобы восстановить только определенную БД (в нашем примере zabbix), нужно запустить эту утилиту с параметром -d:

# pg_restore -d zabbix /tmp/zabbix.bak

Чтобы этой же утилитой восстановить определенную таблицу, нужно использовать ее с параметром -t:

# pg_restore -a -t history /tmp/zabbix.bak

Также утилитой pg_restore можно восстановить данные из бинарного или архивного файла. Соответственно:

# pg_restore -Fc zabbix.bak
# pg_restore -Ft zabbix.tar

При восстановлении можно одновременно создать новую базу:

# pg_restore -Ft -С zabbix.tar

Восстановить данные из дампа также возможно при помощи psql:

# psql zabbix < /tmp/zabbix.dump

Если для подключения нужно авторизоваться, вводим следующую команду:

# psql -U zabbix -W zabbix < /tmp/zabbix.dump

Ниже приведен синтаксис утилиты pg_restore.

—role=имя роли — роль, от имени которой выполняется восстановление резервная копия.

-a, —data-only — восстановление данных без схемы.

-C, —create — создание базы данных перед запуском процесса восстановления.

-e, —exit-on-error — завершение работы в случае возникновения ошибки при выполнении SQL-команд.

-l, —list) — активация вывода содержимого архива.

-O, —no-owner — деактивация генерации команд, устанавливающих владение объектами по образцу исходной базы данных.

-s, —schema-only — восстановление только схемы без самих данных.

-V, —version — вывод версии утилиты pg_restore.

Утилита pg_basebackup

Утилитой pg_basebackup можно выполнять резервное копирования работающего кластера баз данных PostgreSQL. Результирующий бинарный файл можно использовать для репликации или восстановления на определенный момент в прошлом. Утилита создает резервную копию всего экземпляра базы данных и не дает возможности создавать слепки данных отдельных сущностей. Подключение pg_basebackup к PostgreSQL выполняется при помощи протокола репликации с полномочиями суперпользователя или с правом REPLICATION.

Для выполнения резервного копирования локальной базы данных достаточно передать утилите pg_basebackup параметр -D, обозначающий директорию, в которой будет сохранена резервная копия:

# pg_basebackup -D /tmp

Чтобы создать сжатые файлы из табличных пространств, добавим параметры -Ft и -z:

# pg_basebackup -D /tmp -Ft -z

То же самое, но со сжатием bzip2 и для экземпляра базы с общим табличным пространством:

Ниже приведен синтаксис утилиты pg_basebackup.

-w, —no-password — отключение запроса на ввод пароля.

-W, —password — принудительный запрос пароля.

-V, —version — вывод версии утилиты pg_basebackup.

-?, —help — вывод справки по утилите pg_basebackup.

-D каталог, —pgdata=каталог — директория записи данных.

-R, —write-recovery-conf — записать минимальный файл recovery.conf в директорию вывода.

—xlogdir=каталог_xlog — директория хранения журналов транзакций.

-z, —gzip — активация gzip-сжатия результирующего tar-файла.

-P, —progress — активация в вывод отчета о прогрессе.

Утилита wal-g

Wal-g — утилита для резервного копирования и восстановления базы данных PostgreSQL. При помощи wal-g можно выполнять сохранение резервных копий на хранилищах S3 или просто на файловой системе. Ниже мы разберем установку, настройку и работу с утилитой. Покажем как выполнить резервное копирование в Объектное хранилище S3 от Selectel.

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

Теперь перейдем к установке wal-g. Скачаем готовый установочный пакет из репозитория на github.com, распакуем и скопируем папку содержающую исполняемые файлы:

# cd /tmp
# curl -L «https://github.com/wal-g/wal-g/releases/download/v0.2.19/wal-g.linux-amd64.tar.gz» -o «wal-g.linux-amd64.tar.gz
# tar -xzf wal-g.linux-amd64.tar.gz
# mv wal-g /usr/local/bin/

Заполним конфигурационный файл wal-g и изменим его владельца на учетную запись postgres:

Далее настроим автоматизированное создание резервных копий в PostgreSQL и перезагрузим процессы базы данных:

Теперь проверим корректность проведения настроек и загрузим резервную копию в хранилище:

# su — postgres -c ‘/usr/local/bin/wal-g backup-push /var/lib/pgsql/data’

После выполнения процесса резервного копирования, в созданном контейнере появится директория с резервными копиями баз данных:

Такой процесс в продакшене может выполняться при помощи планировщика заданий на регулярной основе.

Утилита pgAdmin

После скачивания утилиту нужно установить и запустить. Она работает в виде веб-приложения через браузер.

После добавления сервера с базой данных, в интерфейсе появляется возможность создания резервной копии. Аналогичным образом здесь же можно выполнить восстановление из резервной копии.

После выполнения команды Backup резервная копия сохраняется в заранее определенную директорию.

Диагностика

на низком уровне выполняет команды SELECT. Если есть проблемы с работой , убедитесь, что в базе данных можно выполнить SELECT, например из . Также следует учитывать, что при этом применяются все свойства подключения по умолчанию и переменные окружения, которые использует клиентская библиотека .