Postgresql восстановление базы из backup: Как восстановить данные PostgreSQL из резервной копии — Effector Saver

Содержание

PostgreSQL : Документация: 9.6: 25.1. Выгрузка в SQL : Компания Postgres Professional

25.1.1. Восстановление дампа
25.1.2. Использование pg_dumpall
25.1.3. Управление большими базами данных

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

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

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

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

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

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

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

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

25.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 для каждой базы данных, чтобы оптимизатор запросов получил полезную статистику; за подробностями обратитесь к Подразделу 24.1.3 и Подразделу 24.1.6. Другие советы по эффективной загрузке больших объёмов данных в PostgreSQL вы можете найти в Разделе 14.4.

25.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 выполняется для отдельных баз данных.

25.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. Если при сборке PostgreSQL была подключена библиотека 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.

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

Посвящается Владимиру Габриелю

Ушел из жизни молодым Владимир Габриель. Этот замечательный человек сделал много для развития экосистемы свободного программного обеспечения в России. Работая в Microsoft, Владимир проводил тематические конференции по свободному программному обеспечению, на которые приглашались местные российские хакеры, в число которых попадал и я. С одной стороны, конечно, на этих конференциях решались корпоративные задачи компании Microsoft, шла реклама технологий, технологических платформ и мировоззрений. С другой стороны, организаторы не боялись привезти в Россию лидеров сообществ и компаний, занимавшихся свободным ПО, — Брайана Беклендорфа, Эрика Рэймонда, Эндрю Мортона, Мигеля де Иказу, Мэтта Эсея. Образования и общения с живыми иконами не хватало, поэтому подобные конференции были для нас, как глоток свежего воздуха.

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

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

PostgreSQL предлагает несколько режимов резервного копирования и восстановления баз данных (БД). Поскольку БД располагаются в файловой системе, вполне нормальным методом является резервное копирование на уровне файлов, то есть копирование самого каталога, где размещаются файлы БД. Единственное условие для использования такого режима — полный останов сервера PostgreSQL.

Для систем высокой готовности такой режим резервного копирования не подойдет. Поэтому PostgreSQL позволяет выполнять резервное копирование при запущенном сервере, не прерывая его обычной работы. Наиболее простой режим — это получение дампа БД в текстовом виде (в форме операторов SQL). Для экономии дискового пространства можно сразу же сжимать такой дамп утилитой сжатия (например, gzip). Также существует возможность создания дампа БД в двоичной форме, а также возможность задавать специальные параметры для большего удобства в получении резервной копии и её последующего восстановления.

PostgreSQL также предоставляет возможность резервного копирования в реальном времени посредством Write-Ahead Log (WAL). За счет этого возможно восстановление содержания БД на конкретный момент времени, а также инкрементальное резервное копирование.

Дамп SQL

Идея метода дампа заключается в генерации текстового файла с командами SQL, которые при выполнении на сервере, пересоздадут базу данных в том же самом состоянии, в котором она была на момент создания дампа. PostgreSQL предоставляет для этой цели програмнную утилиту pg_dump. Базовый сценарий вызова данной команды для записи дампа базы bd_name в файл dump.sql выглядит так:

pg_dump bd_name > dump.sql

Утилита pg_dump является для PostgreSQL обычным клиентским приложением. Это означает, что можно выполнять процедуру резервного копирования с любого удалённого компьютера, который имеет доступ к нужной базе данных. Так как утилите pg_dump необходим доступ на чтение всех таблиц, резервную копию которых необходимо сделать, её почти всегда нужно запускать с правами суперпользователя СУБД.

Чтобы указать, к какому серверу должен подключаться pg_dump, необходимо использовать опцию командной строки -h server_name -p port_num. По умолчанию в качестве сервера выбирается localhost или же тот сервер, который указан в переменной окружения PGHOST. Подобным образом, в качестве порта по умолчанию используется порт, указанный в переменной окружения PGPORT или, если переменная не задана, то порт, указанный по умолчанию при компиляции, как правило, 5432.

Как и любое другое клиентское приложение PostgreSQL, pg_dump по умолчанию будет подключаться к базе данных под пользователем, имя которого совпадает с именем текущего пользователя операционной системы. Чтобы изменить это, необходимо либо указать опцию -U, либо установить переменную окружения PGUSER. Подключения, которые выполняет pg_dump, работают с учётом обычных механизмов авторизации — для доступа может потребоваться пароль, или тикет Kerberos, или иная аутентификационная информация.

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

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

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

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

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

psql bd_name < dump.sql

База данных, заданная параметром bd_name не будет создана данной командой. Необходимо создать её из базы template0 перед запуском psql (например, с помощью команды createdb -T template0 bd_name). Аналогично pg_dump, команда psql позволяет указать сервер, к которому осуществляется подключение и имя пользователя, от имени которого осуществляется подключение.

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

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

psql --set ON_ERROR_STOP=on bd_name

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

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

Например:

 pg_dump -h сервер1 bd_name | psql -h сервер2 bd_name

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

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

Использование утилиты pg_dumpall

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

pg_dumpall >  dump. sql

Результирующий дамп может быть восстановлен с помощью psql:

psql –f dump.sql  postgres

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

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

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

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

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

pg_dump bd_name | gzip > dump.sql.gz

Загружая впоследствии сжатый дамп командой:

gunzip -c dump.sql.gz | psql bd_name

Или

cat  dump.sql.gz | gunzip | psql bd_name

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

pg_dump bd_name | split -b 1m - dump.sql

Загружая впоследствии полученные файлы командой:

cat dump.sql* | psql bd_name

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

pg_dump -Fc bd_name > dump.sql

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

pg_restore –d bd_name dump.sql

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

Как сделать резервную копию и восстановить базу данных PostgreSQL

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

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

Как установить PostgreSQL и pgAdmin4 в Ubuntu 20.04
Как установить PostgreSQL и pgAdmin в CentOS 8
Как установить PostgreSQL и pgAdmin в RHEL 8

Приступим…

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

PostgreSQL предоставляет утилиту pg_dump , которая поможет вам создавать резервные копии баз данных. Он генерирует файл базы данных с SQL-командами в формате, который можно легко восстановить в будущем.

Чтобы создать резервную копию базы данных PostgreSQL , начните с входа на сервер базы данных, затем переключитесь на учетную запись пользователя Postgres и запустите pg_dump следующим образом (замените tecmintdb на имя нужной базы данных для резервного копирования). По умолчанию выходной формат представляет собой обычный текстовый файл сценария SQL.

 $ pg_dump  tecmintdb  >  tecmintdb.sql

pg_dump также поддерживает другие форматы вывода. Вы можете указать выходной формат с помощью -F вариант, где c означает файл архива пользовательского формата, d означает архив формата каталога, а t означает файл архива формата tar: все форматы подходят для ввода в pg_restore .

Например:

 $ pg_dump -F c tecmintdb > tecmintdb.dump
ИЛИ
$ pg_dump -F t tecmintdb > tecmintdb.tar

Для вывода вывода в формате вывода каталога используйте флаг -f (который используется для указания выходного файла), чтобы указать целевой каталог вместо файла. Каталог, который будет создан pg_dump не должен существовать.

 $ pg_dump -F d tecmintdb -f tecmintdumpdir

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

 $ pg_dumpall > all_pg_dbs.sql

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

 $ psql -f all_pg_dbs.sql postgres

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

Чтобы восстановить базу данных PostgreSQL , вы можете использовать psql или pg_restore утилиты. psql используется для восстановления текстовых файлов, созданных с помощью pg_dump , а pg_restore используется для восстановления базы данных PostgreSQL из архива, созданного с помощью pg_dump , в одном из форматов, отличных от обычного текста (пользовательский, tar или каталог). ).

Вот пример восстановления дампа текстового файла:

 $ psql  tecmintdb  <  tecmintdb.sql

Как упоминалось выше, дамп пользовательского формата не является скриптом для psql , поэтому его необходимо восстановить с помощью pg_restore , как показано.

 $ pg_restore -d tecmintdb tecmintdb.dump
ИЛИ
$ pg_restore -d tecmintdb tecmintdb.tar
ИЛИ
$ pg_restore -d tecmintdb tecmintdumpdir

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

Если база данных, которую вы резервируете, большая и вы хотите создать выходной файл довольно меньшего размера, вы можете запустить сжатый дамп, где вам нужно отфильтровать выходные данные pg_dump с помощью инструмента сжатия например gzip или любой из ваших любимых:

 $ pg_dump  tecmintdb  | gzip >  tecmintdb.gz

Если база данных очень велика, вы можете сделать дамп параллельно, выгрузив number_of_jobs таблиц одновременно, используя флаг -j , как показано.

 $ pg_dump -F d -j 5 -f tecmintdumpdir

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

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

pg_dump — это обычный клиентский инструмент PostgreSQL, он поддерживает операции на удаленных серверах баз данных. Чтобы указать удаленный сервер базы данных , pg_dump должен связаться, используйте параметры командной строки -h , чтобы указать удаленный хост, и -p , чтобы указать удаленный порт, который прослушивает сервер базы данных. Кроме того, используйте флаг -U , чтобы указать имя роли базы данных для подключения.

Не забудьте заменить 10.10.20.10 и 5432 и tecmintdb с IP-адресом или именем удаленного хоста, портом базы данных и именем базы данных соответственно.

 $ pg_dump -U tecmint -h 10.10.20.10 -p 5432 tecmintdb > tecmintdb.sql

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

Ошибка подключения к базе данных PostgreSQL

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

 $ pg_dump -U tecmint -h 10.10.20.10 tecmintdb | pqsl -U tecmint -h 10.10.20.30 tecmintdb

Автоматическое резервное копирование базы данных PostgreSQL с использованием задания Cron

Вы можете выполнять резервное копирование через регулярные промежутки времени, используя задания cron . Задания Cron являются широко используемым средством планирования различных видов задач для выполнения на сервере.

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

 $ mkdir -p /srv/backups/databases

Затем выполните следующую команду, чтобы отредактировать crontab и добавить новое задание cron.

 $ кронтаб -е

Скопируйте и вставьте следующую строку в конец crontab. Вы можете использовать любой из описанных выше форматов дампа.

 0 0 * * * pg_dump -U postgres tecmintdb > /srv/backups/postgres/tecmintdb.sql

Сохраните файл и выйдите.

Служба cron автоматически запустит это новое задание без перезагрузки. И эта работа cron будет запускаться каждый день в полночь, это минимальное решение задачи резервного копирования.

Для получения дополнительной информации о том, как планировать задания cron, см.: Как создавать задания Cron и управлять ими в Linux

На этом все! Рекомендуется сделать резервное копирование данных частью вашей процедуры управления базой данных. Чтобы связаться с нами по любым вопросам или комментариям, используйте форму обратной связи ниже. Дополнительные сведения см. на справочных страницах pg_dump и pg_restore.

Отзывы об учебнике…

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

TecMint — самое быстрорастущее и пользующееся наибольшим доверием сообщество сайт для любых видов статей, руководств и книг по Linux в Интернете. Миллионы людей посещают TecMint! искать или просматривать тысячи опубликованных статей, доступных всем БЕСПЛАТНО.

Если вам нравится то, что вы читаете, пожалуйста, купите нам кофе (или 2) в знак признательности.

Мы благодарны за вашу бесконечную поддержку.

postgresql — восстановить файл резервной копии postgres с помощью командной строки?

спросил
12 лет, 11 месяцев назад

Изменено
3 месяца назад

Просмотрено
1,3 млн раз

Локально я использую pgadmin3. Однако на удаленном сервере у меня нет такой роскоши.

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

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

В зависимости от того, как вы создали файл дампа, можно просмотреть два инструмента.

Вашим первым источником информации должна быть справочная страница pg_dump, так как именно она создает сам дамп. Там написано:

Дампы могут выводиться в скрипте или
форматы архивных файлов. Дампы скриптов
текстовые файлы, содержащие SQL
команды, необходимые для восстановления
базу данных в состояние, в котором она была
в то время, когда он был сохранен. К
восстановить из такого скрипта, скормить его
psql(1). Файлы скриптов можно использовать
восстановить базу данных даже
на других машинах и др.
архитектуры; с некоторыми модификациями
даже на других продуктах баз данных SQL.
Альтернативные форматы файлов архива
должен использоваться с pg_restore(1) для
восстановить базу данных. Они разрешают
pg_restore, чтобы быть избирательным в отношении того, что
восстановить или даже изменить порядок
предметы до реставрации.
Форматы архивных файлов предназначены для
быть переносимым между архитектурами.

Так что зависит от того, как он был выгружен. Если вы используете Linux/Unix, вы, вероятно, можете понять это с помощью отличной команды file(1) — если в ней упоминается текст ASCII и/или SQL, его следует восстановить с помощью psql, в противном случае вам, вероятно, следует использовать pg_restore.

Восстановить довольно просто:

 psql -U имя пользователя -d имя_базы_данных < имя_файла.sql
-- Для Postgres версии 9.0 или более ранней
psql -U имя пользователя -d имя базы данных -1 -f имя файла.sql

или

 pg_restore -U имя пользователя -d имя базы данных -1 имя файла.dump

Ознакомьтесь с их справочными страницами — там довольно много параметров, влияющих на работу восстановления. Возможно, вам придется очистить ваши «живые» базы данных или воссоздать их из template0 (как указано в комментарии) перед восстановлением, в зависимости от того, как были созданы дампы.

создать резервную копию

 pg_dump -h localhost -p 5432 -U postgres -F c -b -v -f
"/usr/local/backup/10.70.0.61.backup" old_db

-F c - это пользовательский формат (сжатый и может выполняться параллельно с -j N ) -b - это включая BLOB-объекты , -v - это 30282 , verbose — это имя файла резервной копии .

восстановление из резервной копии

 pg_restore -h localhost -p 5432 -U postgres -d old_db -v
"/usr/local/резервная копия/10.70.0.61.резервная копия"

важно установить -h localhost - опция

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

 су - постгрес
psql -l # выведет список всех баз данных в кластере Postgres

pg_dump/pg_restore

 pg_dump -U имя пользователя -f backup. dump имя_базы_данных -Fc

переключатель -F указать формат файла резервной копии:

c будет использоваться пользовательский формат PostgreSQL, который сжимается и приводит к наименьшему размеру файла резервной копии
d для каталога, где каждый файл представляет собой одну таблицу
t для архива TAR (больше пользовательского формата)
-h / --хост Указывает имя хоста машины, на которой работает сервер
-W / --password Принудительно pg_dump запрашивать пароль перед подключением к базе данных

восстановить резервную копию:

 pg_restore -d имя_базы_данных -U имя пользователя -C backup.dump

Параметр -C должен создать базу данных перед импортом данных. Если это не сработает, вы всегда можете создать базу данных, например. с помощью команды (как пользователь postgres или другая учетная запись, имеющая права на создание баз данных) createdb db_name -O owner

pg_dump/psql

с -F стр. ). Тогда вы не сможете использовать pg_restore . Вы можете импортировать данные с помощью psql .

резервная копия:

 pg_dump -U имя_пользователя -f backup.sql имя_базы_данных

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

 psql -d имя_базы_данных -f backup.sql

POSTGRESQL 9.1.12

DUMP:

 pg_dump -U пользователь имя_базы_данных > имя_архива.sql

введите пароль пользователя и нажмите ввод.

ВОССТАНОВЛЕНИЕ:

 psql -U user db_name < /directory/archive.sql

введите пароль пользователя и нажмите ввод.

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

 pg_restore -h localhost -p 5432 -U postgres -d my_new_database my_old_database.backup

или используйте psql :

 psql -h localhost -U postgres -p 5432 my_new_database < my_old_database. backup

где -h хост, -p порт, -u имя пользователя для входа, -d имя базы данных

Резервное копирование и восстановление с помощью GZIP

Для базы данных большего размера это очень хорошо

резервная копия

 pg_dump -U пользователь -d mydb | gzip > mydb.pgsql.gz

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

 gunzip -c mydb.pgsql.gz | psql имя_базы_данных -U пользователь

https://www.postgresql.org/docs/14/backup-dump.html

Это сработало для меня:

 pg_restore --verbose --clean --no-acl --no-owner --host=localhost --dbname=db_name --username=username last.dump

 Резервное копирование: $ pg_dump -U {имя пользователя} {source_db} -f {dumpfilename. sql}
Восстановление: $ psql -U {имя пользователя} -d {dumpfilename.sql}

попробуйте это:

 psql -U <имя пользователя> -d <имя базы данных> -f <имя файла>.sql

Восстановление базы данных psql из файла .sql

Резервное копирование и восстановление

Это комбинация, которую я использую для резервного копирования , удаления, создания и восстановления моей базы данных (на macOS и Linux):

 sudo -u postgres pg_dump -Fc mydb > ./mydb .sql
sudo -u postgres dropdb mydb
sudo -u postgres createdb -O db_user mydb
sudo -u postgres pg_restore -d mydb < ./mydb.sql

Разное

Если вы создаете резервную копию с помощью pg_dump, вы можете легко восстановить ее следующим образом:

Открыть окно командной строки
Перейти в папку bin Postgres. Например: cd "C:\ProgramFiles\PostgreSQL\9.5\bin"
Введите команду для восстановления базы данных. Например: psql.exe -U postgres -d YourDatabase -f D:\Backup\.sql
Введите пароль для вашего пользователя postgres
Проверьте процесс восстановления

Я не видел здесь упоминаний о расширении файла дампа (*.dump).

Это решение сработало для меня:

Я получил файл дампа и мне нужно его восстановить.

Сначала я попытался сделать это с помощью pg_restore и получил:

 pg_restore: ошибка: входной файл выглядит как дамп текстового формата. Пожалуйста, используйте psql.

Я сделал это с psql и работал хорошо:

 psql -U myUser -d myDataBase < path_to_the_file/file.dump

1. Откройте Терминал.

2. Сделайте резервную копию вашей базы данных с помощью следующей команды

ваша корзина postgres -> /opt/PostgreSQL/9. 1/bin/

ваш исходный сервер базы данных -> 192.168.1.111

расположение и имя вашего файла резервной копии -> /home/dinesh/db/mydb.backup

ваш источник имя базы данных -> mydatabase

 /opt/PostgreSQL/9.1/bin/pg_dump --host '192.168.1.111' --port 5432 --username "postgres" --no-password --format custom --blobs - -file "/home/dinesh/db/mydb.backup" "mydatabase"

3. Восстановите файл mydb.backup в место назначения.

ваш целевой сервер -> localhost

имя вашей целевой базы данных -> mydatabase

Создайте базу данных для восстановления резервной копии.

 /opt/PostgreSQL/9.1/bin/psql -h 'localhost' -p 5432 -U postgres -c "СОЗДАТЬ БАЗУ ДАННЫХ mydatabase"

Восстановить резервную копию.

 /opt/PostgreSQL/9.1/bin/pg_restore --host 'localhost' --port 5432 --username "postgres" --dbname "mydatabase" --no-password --clean "/home/dinesh/db /mydb. backup"

1) Откройте терминал psql.

2) Разархивируйте/распакуйте файл дампа.

3) Создать пустую базу данных.

4) используйте следующую команду для восстановления файла .dump

 -# \i

Для восстановления файла дампа

 psql -d [имя_базы] -U [имя_пользователя] -p 5432 < [FileLocation]

Для восстановления файла .SQL

 pg_restore -U [имя пользователя] -d [имя базы данных] -1 [FileLocation]

Если вы получаете ошибки аутентификации пользователя, перейдите к файлу pg_hba.conf, который находится в папке PSQL/data в ваших программных файлах, и измените «МЕТОД» на «Доверять».
Перезапустите сервер psql в службах Windows (Win + R --> services.msc).

попытка:

 pg_restore -h localhost -p 5432 -U <имя пользователя> -d <имя базы данных> -1 <имя файла>

Восстановление файла резервной копии postgres зависит от того, как вы сделали резервную копию.

Если вы использовали pg_dump с -F c или -F d, вам нужно использовать pg_restore, в противном случае вы можете просто использовать

psql -h localhost -p 5432 -U postgres < файл резервной копии

9 способов резервного копирования и восстановления баз данных postgres

Если вы используете докер, этот ответ может быть полезен.

Запустить контейнер
запуск докера
Доступ к bash внутри контейнера
docker exec -it bash
Скопируйте файл резервной копии .tar в контейнер Docker (в другом окне)
docker cp postgres_dump.tar :/

Восстановить резервную копию
pg_restore -c -U <пользователь-postgres> -d <пароль> -v "postgres_dump.tar" -W

Введите пароль

Как сказано ниже, вы можете использовать команду psql для восстановления файла дампа:

https://www.postgresql.org/docs/8.1/static/backup.html#BACKUP-DUMP-RESTORE

 psql dbname < вводить

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

 psql dbname < infile username

Извините за некропост, но мне эти решения не подошли. У меня postgres 10. В Linux:

Мне пришлось сменить каталог на мой pg_hba. conf.
Мне пришлось отредактировать файл, чтобы изменить метод с однорангового узла на md5, как указано здесь
Перезапустить службу: служба postgresql-10 перезапустить
Перейдите в каталог, где находился мой файл backup.sql, и выполните:
psql postgres -d имя_базы_данных -1 -f backup.sql
-database_name - это имя моей базы данных
-backup.sql — это имя моего файла резервной копии .sql.

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

 sudo su - yourdbuser
psql
\i ваш резервный файл

Если у вас есть резервная копия файла SQL, вы можете легко восстановить ее.
Просто следуйте инструкциям, приведенным ниже

 1. Сначала создайте базу данных с помощью pgAdmin или чего угодно (например, my_db — это имя нашей созданной базы данных)
2. Теперь откройте окно командной строки
3. Перейдите в папку bin Postgres. Например: cd "C:\ProgramFiles\PostgreSQL\pg10\bin"
4.  Введите следующую команду для восстановления базы данных: psql.exe -U postgres -d my_db -f D:\Backup\имя_файла_резервной_копии.sql

При необходимости введите пароль для вашего пользователя postgres и дайте Postgres выполнить свою работу. Затем вы можете проверить процесс восстановления.

 psql "postgresql://:@:/" < "backup.sql"

 psql.exe "postgresql://:@:/" < "backup.sql"

У меня были проблемы с аутентификацией при запуске pg_dump, поэтому я переместил файл дампа

 mv database_dump /tmp

во временный каталог, а затем запустил

 su -u postgres
компакт-диск /tmp
pg_restore database_dump

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

Резервное копирование ==>

Вариант 1: Сделать резервную копию вместе с паролем в cmd
1. PGPASSWORD="mypassword" pg_dump -U postgres -h localhost --inserts mydb>mydb.sql
Вариант 2: Сделать резервную копию без пароля в cmd
2. pg_dump -U postgres -h localhost --inserts mydb>mydb.sql
Вариант 3: Сделать резервную копию как gzip (если база данных большая)
3. pg_dump -U postgres -h localhost mydb --inserts | gzip > mydb.gz

Восстановить:
1. psql -h localhost -d mydb -U postgres -p 5432 < mydb.sql

Во-первых, убедитесь, что вы уже добавили папку postgres bin в «Путь» переменная среды (в моем случае это папка C:\Program Files\PostgreSQL\12\bin).

Затем откройте интерпретатор команд Windows (cmd), перейдите в папку, в которой находится файл .sql, и выполните следующую команду:

pg_restore -U имя_пользователя -d database-1 backupfile.sql

Например:

pg_restore -U sam -d SamDataBase -1 SamDataBaseBackup.sql

(Он может запросить у вас пароль пользователя, поэтому убедитесь, что вы вводите его правильно, а затем нажмите Enter)

Pura vida!

Если вы создали новую базу данных с именем mydb , Чтобы восстановить дамп . sql в эту базу данных с помощью psql,

 psql --file=dump.sql --username=postgres --host=localhost --port=5432 mydb

пароль будет запрошен psql

Варианты подключения:

 -h, --host=HOSTNAME хост сервера базы данных или каталог сокета (по умолчанию: "/var/run/postgresql")
  -p, --port=PORT порт сервера базы данных (по умолчанию: "5432")
  -U, --username=USERNAME имя пользователя базы данных (по умолчанию: "xyz")
  -w, --no-password никогда не запрашивать пароль
  -W, --password принудительно ввести пароль (должно происходить автоматически)

Сохранить и восстановить точно такое же состояние с помощью сжатого дампа

В других ответах все ключевые биты даны отдельно, но, надеюсь, это обеспечит пару команд «просто работает, сохраняет и восстанавливает точное состояние».

Дамп в файл mydb.psql :

 PGPASSWORD=mypassword pg_dump -U my_username -h localhost mydb -Fc -f mydb.psql

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

 PGPASSWORD=мой пароль pg_restore -U my_username -h localhost \
  --clean -d mydb -v mydb. psql

Некоторые флаги:

-Fc : Сжатый формат, в отличие от обычного текста.
файл tmp.psql говорит:
```
 tmp.psql: дамп пользовательской базы данных PostgreSQL — v1.14-0
 
```
--clean : уничтожить целевую БД перед ее восстановлением, тем самым вернув ее в точно такое же первозданное состояние.
Все данные, созданные после создания дампа, будут потеряны.

PGPASSWORD , -U и -h , конечно, могут быть изменены в зависимости от вашего метода входа в систему, например. без PGPASSWORD вам будет предложено ввести пароль, и ни один из них не нужен, если вы настроили одноранговую аутентификацию локально.

Проверено на Ubuntu 22.04, PostgreSQL 14.5.

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

Чтобы создать резервную копию ваших данных, перейдите в каталог postgres \bin\, например C:\programfiles\postgres \10\бин\ и введите следующую команду:

pg_dump -FC -U ngb -d ngb -p 5432 >C:\BACK_UP\ngb.