Postgresql dump restore: sql — How to restore PostgreSQL dump file into Postgres databases?

Дамп и восстановление — база данных Azure для PostgreSQL — один сервер

• 
  09/22/2020
• Чтение занимает 4 мин

В этой статье

применимо к:
База данных Azure для PostgreSQL —
база данных Azure с одним сервером для PostgreSQL-гибкого сервера

Можно извлечь базу данных PostgreSQL в файл дампа с помощью pg_dump и с помощью pg_restore восстановить базу данных PostgreSQL из файла архива, созданного pg_dump.You can use pg_dump to extract a PostgreSQL database into a dump file and pg_restore to restore the PostgreSQL database from an archive file created by pg_dump.

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

Прежде чем приступить к выполнению этого руководства, необходимы следующие компоненты:To step through this how-to guide, you need:

Выполните указанные ниже действия, чтобы создать дамп базы данных PostgreSQL и восстановить ее.Follow these steps to dump and restore your PostgreSQL database:

Создание файла дампа, содержащего загружаемые данные, с помощью pg_dumpCreate a dump file using pg_dump that contains the data to be loaded

Чтобы создать резервную копию базы данных PostgreSQL локально или на виртуальной машине, выполните следующую команду:To back up an existing PostgreSQL database on-premises or in a VM, run the following command:

pg_dump -Fc -v --host=<host> --username=<name> --dbname=<database name> -f <database>.dump

Например, если имеется локальный сервер с базой данных testdb.For example, if you have a local server and a database called testdb in it

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

Восстановите данные в целевую базу данных Azure для PostgreSQL с помощью pg_restoreRestore the data into the target Azure Database for PostgreSQL using pg_restore

После создания целевой базы данных можно воспользоваться командой pg_restore с параметром -d, —dbname, чтобы восстановить данные в целевую базу данных из файла дампа.After you’ve created the target database, you can use the pg_restore command and the -d, —dbname parameter to restore the data into the target database from the dump file.

pg_restore -v --no-owner --host=<server name> --port=<port> --username=<user-name> --dbname=<target database name> <database>.dump

Если включить параметр —no-owner, все объекты, созданные во время восстановления, будут присвоены пользователю —username.Including the —no-owner parameter causes all objects created during the restore to be owned by the user specified with —username. Дополнительные сведения см. в официальной документации PostgreSQL по pg_restore.For more information, see the official PostgreSQL documentation on pg_restore.

Примечание

Если серверу PostgreSQL требуются подключения TLS/SSL (по умолчанию в базе данных Azure для серверов PostgreSQL), задайте переменную среды, PGSSLMODE=require чтобы pg_restore средство подключается к TLS.If your PostgreSQL server requires TLS/SSL connections (on by default in Azure Database for PostgreSQL servers), set an environment variable PGSSLMODE=require so that the pg_restore tool connects with TLS. Без TLS ошибка может быть прочитана FATAL: SSL connection is required. Please specify SSL options and retry.Without TLS, the error may read FATAL: SSL connection is required. Please specify SSL options and retry.

В командной строке Windows выполните команду SET PGSSLMODE=require перед выполнением команды pg_restore.In the Windows command line, run the command SET PGSSLMODE=require before running the pg_restore command. В Linux или Bash выполните команду export PGSSLMODE=require перед выполнением команды pg_restore.In Linux or Bash run the command export PGSSLMODE=require before running the pg_restore command.

В этом примере восстановите данные из файла дампа testdb.dump в базу данных mypgsqldb на целевом сервере mydemoserver.postgres.database.azure.com.In this example, restore the data from the dump file testdb.dump into the database mypgsqldb on target server mydemoserver.postgres.database.azure.com.

Ниже приведен пример использования этого pg_restore для одного сервера:Here is an example for how to use this pg_restore for Single Server:

pg_restore -v --no-owner --host=mydemoserver.postgres.database.azure.com --port=5432 --username=mylogin@mydemoserver --dbname=mypgsqldb testdb.dump

Ниже приведен пример использования этого pg_restore для гибкого сервера:Here is an example for how to use this pg_restore for Flexible Server:

pg_restore -v --no-owner --host=mydemoserver.postgres.database.azure.com --port=5432 --username=mylogin --dbname=mypgsqldb testdb.dump

Оптимизация процесса миграцииOptimizing the migration process

Один из способов миграции существующей базы данных PostgreSQL в службу «База данных Azure для PostgreSQL» — это резервное копирование базы данных в источнике и ее восстановление в Azure.One way to migrate your existing PostgreSQL database to Azure Database for PostgreSQL service is to back up the database on the source and restore it in Azure. Чтобы свести к минимуму время, необходимое для завершения миграции, можно использовать следующие параметры с командами резервного копирования и восстановления.To minimize the time required to complete the migration, consider using the following parameters with the backup and restore commands.

Примечание

Подробные сведения о синтаксисе см. в статьях о pg_dump и pg_restore.For detailed syntax information, see the articles pg_dump and pg_restore.

Для резервного копированияFor the backup

• Создайте резервную копию с использованием параметра -Fc, чтобы вы могли выполнять восстановление параллельно, что позволит ускорить его.Take the backup with the -Fc switch so that you can perform the restore in parallel to speed it up. Пример:For example:

  pg_dump -h my-source-server-name -U source-server-username -Fc -d source-databasename -f Z:\Data\Backups\my-database-backup.dump
  

Для восстановленияFor the restore

• Мы предлагаем переместить файл резервной копии на виртуальную машину Azure в том же регионе, где находится сервер Базы данных Azure для PostgreSQL, на который перемещается база данных, и выполнить команду pg_restore с этой виртуальной машины, чтобы уменьшить задержку сети.We suggest that you move the backup file to an Azure VM in the same region as the Azure Database for PostgreSQL server you are migrating to, and do the pg_restore from that VM to reduce network latency. Мы также рекомендуем, чтобы виртуальная машина была создана с функцией ускорения работы в сети.We also recommend that the VM is created with accelerated networking enabled.

• Это должно происходить по умолчанию, но откройте файл дампа, чтобы проверить, что инструкции создания индекса находятся после вставленных данных.It should be already done by default, but open the dump file to verify that the create index statements are after the insert of the data. Если это не так, переместите инструкции создания индекса после вставленных данных.If it isn’t the case, move the create index statements after the data is inserted.

• Для параллелизации восстановления необходимо выполнить восстановление с помощью коммутаторов-FC и-j # .Restore with the switches -Fc and -j # to parallelize the restore. # число ядер на целевом сервере.# is the number of cores on the target server. Также можно попробовать # установить в два раза больше, чем количество ядер целевого сервера, чтобы увидеть влияние.You can also try with # set to twice the number of cores of the target server to see the impact. Пример:For example:

Ниже приведен пример использования этого pg_restore для одного сервера:Here is an example for how to use this pg_restore for Single Server:

 pg_restore -h my-target-server.postgres.database.azure.com -U azure-postgres-username@my-target-server -Fc -j 4 -d my-target-databasename Z:\Data\Backups\my-database-backup.dump

Ниже приведен пример использования этого pg_restore для гибкого сервера:Here is an example for how to use this pg_restore for Flexible Server:

 pg_restore -h my-target-server.postgres.database.azure.com -U azure-postgres-username@my-target-server -Fc -j 4 -d my-target-databasename Z:\Data\Backups\my-database-backup.dump

• Можно также изменить файл дампа, добавив команду set synchronous_commit = off; в начале и команду set synchronous_commit = on; в конце.You can also edit the dump file by adding the command set synchronous_commit = off; at the beginning and the command set synchronous_commit = on; at the end. Если не включить ее в конце, прежде чем приложения изменят данные, это может привести к последующей потере данных.Not turning it on at the end, before the apps change the data, may result in subsequent loss of data.

• Перед восстановлением рассмотрите возможность выполнения следующих действий на целевом сервере Базы данных Azure для PostgreSQL.On the target Azure Database for PostgreSQL server, consider doing the following before the restore:

  • Отключите отслеживание производительности запросов, так как эти статистические данные не нужны во время миграции.Turn off query performance tracking, since these statistics are not needed during the migration. Вы можете сделать это, задав для pg_stat_statements.track, pg_qs.query_capture_mode и pgms_wait_sampling.query_capture_mode значение NONE.You can do this by setting pg_stat_statements.track, pg_qs.query_capture_mode, and pgms_wait_sampling.query_capture_mode to NONE.

  • Используйте номер SKU с высоким объемом ресурсов вычисления и памяти, например номер с оптимизацией для операций в памяти с 32 виртуальными ядрами, чтобы ускорить миграцию.Use a high compute and high memory sku, like 32 vCore Memory Optimized, to speed up the migration. Вы можете легко вернуться к предпочитаемому номеру SKU после завершения восстановления.You can easily scale back down to your preferred sku after the restore is complete. Чем выше номер SKU, тем большего параллелизма можно достичь, увеличив значение соответствующего параметра -j в команде pg_restore.The higher the sku, the more parallelism you can achieve by increasing the corresponding -j parameter in the pg_restore command.

  • Увеличьте число операций ввода-вывода в секунду на целевом сервере. Это может улучшить производительность восстановления.More IOPS on the target server could improve the restore performance. Вы можете подготовить больше операций ввода-вывода в секунду, увеличив объем хранилища на сервере.You can provision more IOPS by increasing the server’s storage size. Этот параметр необратим, но стоит принять во внимание, будет ли большее количество операций ввода-вывода в секунду полезным для вашей рабочей нагрузки в будущем.This setting is not reversible, but consider whether a higher IOPS would benefit your actual workload in the future.

Не забудьте проверить и протестировать эти команды в тестовой среде, прежде чем использовать их в рабочей среде.Remember to test and validate these commands in a test environment before you use them in production.

Дальнейшие действияNext steps

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

24.1. Выгрузка в SQL

Идея, стоящая за этим методом, заключается в генерации текстового файла с командами 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 подключается к серверу через обычные механизмы проверки подлинности клиента (которые описываются в Главе 19).

Важное преимущество pg_dump в сравнении с другими методами резервного копирования, описанными далее, состоит в том, что вывод pg_dump обычно можно загрузить в более новые версии PostgreSQL, в то время как резервная копия на уровне файловой системы и непрерывное архивирование жёстко зависят от версии сервера. Также, только метод с применением 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. Другие советы по эффективной загрузке больших объёмов данных в PostgreSQL вы можете найти в Разделе 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 может разбивать выводимые данные на небольшие файлы, размер которых удовлетворяет ограничению нижележащей файловой системы. Например, чтобы получить части по 1 мегабайту:

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

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

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

Используйте специальный формат дампа 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 восстановление базы из дампа

Все админы делятся на 2 категории

— те которые уже делают бэкап

и те которые ещё не делают.

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

В этом посте я постараюсь рассказать о некоторых способах, которыми вы можете сделать резервную копию PostgreSQL. Для тестов будем использовать Ubuntu 12,04 VPS с PostgreSQL 9.1. Для большинства современных дистрибутивов и последних версии PostgreSQL мои советы будут актуальны.

• Создание резервной копии PostgreSQL при помощи pg_dump

PostgreSQL включает в себя утилиту под названием «pg_dump», которая позволяет сделать дамп базы данных в файл. Утилита консольная, синтаксис достаточно простой:

pg_dump name_of_database > name_of_backup_file

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

Как вариант мы можем войти через sudo под пользователем «рostgres» и выполнить команду:

sudo su — postgres pg_dump postgres > postgres_db.bak

«pg_dump» — это «полноценный» клиент PostgreSQL, т.е. при необходимости её можно запустить с удаленной машины, если имеются соответствующие разрешения к базе данных.

Расширенный синтаксис выглядит следующим образом:

pg_dump -h remote_host -p remote_port name_of_database > name_of_backup_file pg_dump -U user_name -h remote_host -p remote_port name_of_database > name_of_backup_file

• Как восстановить дампы  pg_dump в PostgreSQL

Чтобы восстановить резервную копию, созданную pg_dump, необходимо перенаправить файл с дампом в стандартный ввод psql:

psql empty_database < backup_file

Эта операция не создает новую базу данных. Об этом необходимо позаботиться заранее.

Для примера, создадим новую базу данных под названием «restored_database», а затем развернем дамп под названием «database.bak»:

createdb -T template0 restored_database psql restored_database < database.bak

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

createuser test_user psql restored_database < database.bak

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

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

psql —set ON_ERROR_STOP=on restored_database < backup_file

С данной опцией мы получим частично восстановленную базу данных.

Можно попробовать восстановить весь дамп в одну транзацию, т.е. бекап будет или полностью восстановлен или не восстановлен совсем. Данный режим может быть задан, с помощью опций -1 или —single-transaction для psql.

psql -1 restored_database < backup_file

При этом любая ошибка приведет к откату процесса восстановления, что может потребовать достаточно продолжительного времени.

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

Чтобы сэкономить время, можно сделать резервную копию всех баз данных в вашей системе, при помощи утилиты «pg_dumpall»:

pg_dumpall > backup_file

Похожим способом можно восстановить базы данных:

psql -f backup_file postgres

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

В качестве дополнения скрипт, который создает резервную копию с меткой времени и сохраняет последние 14 резервных копий:

ls -t *.sql | sed -e ‘1,13d’ | xargs -d ‘\n’ rm echo Done at `date +\%Y-\%m-\%d_\%T` pg_dump dbname —username=dbuser > `date +\%Y-\%m-\%d_\%T`.sql

Вы можете оставить комментарий ниже.

Ускоряем восстановление бэкапов в PostgreSQL / Блог компании Southbridge / Хабр

Мои ощущения от процесса работы

Недавно я решил заняться ускорением восстановления нашей базы данных в dev-окружении. Как и во многих других проектах, база вначале была небольшой, но со временем значительно выросла. Когда мы начинали, ее размер было всего несколько мегабайт. Теперь упакованная база занимает почти 2 ГБ (несжатая — 30 ГБ ). Мы восстанавливаем dev-окружение в среднем раз в неделю. Старый способ проведения операции перестал нас устраивать, а вовремя подвернувшаяся в Slack-канале картинка “DB restore foos?” побудила меня к действию.

Ниже описано, как я ускорял операцию восстановления базы данных.

Простой способ

Ниже описывается наша первая версия процедуры резервного копирования и восстановления. Мы начали с запуска pg_dump и направления его вывода в gzip. Для восстановления базы в dev-окружении мы копировали архив с помощью scp, распаковывали его, а затем загружали командой psql.

$ pg_dump db | gzip > dump.gz
real 7m9.882s
user 5m7.383s
sys 2m56.495s

$ gunzip dump.gz
real 2m27.700s
user 1m28.146s
sys 0m41.451s

$ psql db < dump 
real 30m4.237s
user 0m21.545s
sys 0m44.331s

Общее время при простом способе: 39 минут 41 секунда (32,5 минуты на восстановление в dev-окружении).

Такой подход был прост в понимании, элементарен в настройке и отлично работал, пока размер БД не превышал несколько сотен мегабайт. Однако 32,5 минуты на восстановление базы в dev-окружении — это совершенно неприемлемо.

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

Первое, что пришло в голову, — просто направить запакованный файл напрямую в psql с помощью zcat, которую можно считать аналогом cat для сжатых файлов. Эта команда распаковывает файл и выводит его в stdout, который, в свою очередь, можно направить в psql.

$ pg_dump db | gzip > dump.gz
real 7m9.882s
user 5m7.383s
sys 2m56.495s

$ zcat dump.gz | psql db
real 26m22.356s
user 1m28.850s
sys 1m47.443s

Общее время: 33 минуты 31 секунда (26,3 минут на восстановление в dev-окружении, что на 20% быстрее).

Отлично, нам удалось ускорить процесс на 16%, выиграв 20% при восстановлении. Поскольку ввод/вывод был основным ограничивающим фактором, отказавшись от распаковки файла на диск, мы сэкономили более 6 минут. Но мне показалось, что этого недостаточно. Терять на восстановлении базы 26 минут — все равно плохо. Я должен был придумать что-то еще.

Настраиваемый формат

Углубившись в документацию по pg_dump, я обнаружил, что pg_dump создает простой текстовый SQL-файл. Затем мы сжимаем его gzip-ом, чтобы сделать меньше. У Postgres есть настраиваемый (custom) формат, который по умолчанию использует zlib для сжатия. Я подумал, что можно будет добиться выигрыша в скорости создания бэкапа, сразу упаковывая данные в Postgres вместо направления простого текстового файл в gzip.

Поскольку psql не понимает настраиваемый формат, мне пришлось перейти на pg_restore.

$ pg_dump -Fc db > dumpfc.gz
real 6m28.497s
user 5m2.275s
sys 1m16.637s

$ pg_restore -d db dumpfc.gz
real 26m26.511s
user 0m56.824s
sys 0m15.037s

Общее время 32 минуты 54 секунды (26,4 минуты на восстановление в dev-окружении).

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

Распараллеливание

Когда я начинаю разбираться с какой-либо проблемой, первым делом читаю документацию и исходный код. У Postgres отличная документация, где в том числе ясно и подробно расписаны опции командной строки. Одна из опций команды pg_restore определяет количество параллельных потоков, которые запускаются во время выполнения наиболее затратных по времени задач, загрузки данных, создания индексов или ограничений.

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

$ pg_dump -Fc db > dumpfc.gz
real 6m28.497s
user 5m2.275s
sys 1m16.637s

$ pg_restore -d db -j 2 dumpfc
real 25m39.796s
user 1m30.366s
sys 1m7.032s

Общее время 32 минуты 7 секунд (25,6 минут на восстановление в dev-окружении, что на 3% быстрее, чем однопоточный запуск pg_restore).

Хорошо, немного выиграли. Можем ли мы еще ускориться?

$ pg_dump -Fc db > dumpfc.gz
real 6m28.497s
user 5m2.275s
sys 1m16.637s

$ pg_restore -d db -j 4 dumpfc.gz
real 22m6.124s
user 0m58.852s
sys 0m34.682s

Общее время 28 минут 34 секунды (22,1 минуты на восстановление в dev-окружении, что на 14% быстрее, чем с двумя потоками).

Отлично! Четыре потока быстрее двух на 14%. Да данный момент в dev-окружении мы ускорились с 32,5 до 22,1 минуты: время улучшено на 32%!

Я решил выяснить, к чему приведет дальнейшее увеличение количества ядер.

$ pg_dump -Fc db > dumpfc.gz
real 6m28.497s
user 5m2.275s
sys 1m16.637s

$ pg_restore -d db -j 8 dumpfc.gz
real 16m49.539s
user 1m1.344s
sys 0m39.522s

Общее время 23 минуты 17 секунд (16,8 на восстановление в dev-окружении, что на 24% быстрее четырех потоков).

Итак, увеличив количество потоков до удвоенного количества ядер, нам удалось уменьшить время с 22,1 до 16,8 минут. Сейчас мы ускорились на 49%, что просто чудесно.

А еще можно что-нибудь выжать?

$ pg_dump -Fc db > dumpfc.gz
real 6m28.497s
user 5m2.275s
sys 1m16.637s

$ pg_restore -d db -j 12 dumpfc.gz
real 16m7.071s
user 0m55.323s
sys 0m36.502s

Общее время 22 минуты 35 секунд (16,1 минуты на восстановление в dev-окружении, что на 4%, чем 8 потоков).

Указав 12 потоков, мы еще немного ускорились, но CPU виртуальной машины во время восстановления был загружен настолько, что никакие другие действия в системе выполнить было невозможно. В этом вопросе я решил остановиться на 8 потоках (количество ядер * 2).

Заключение

В итоге нам удалось сократить время почти вдвое: с 30 до 16 минут. Это экономит нам 72 часа времени восстановления в год (6 разработчиков на 52 запуска процедуры восстановления на 14 минут). Я очень доволен этими результатами. В будущем планирую заняться восстановлением только данных, а не базы целиком. Посмотрим, насколько это будет быстрее.

Ссылки:

1. Оригинал: Speeding up Postgres Restores.
2. Вторая часть: Ускоряем восстановление бэкапов в Postgres.

Дамп и восстановление — База данных Azure для PostgreSQL — Один сервер

• 22.09.2020
• 4 минуты на чтение

В этой статье

ОТНОСИТСЯ К:
База данных Azure для PostgreSQL — один сервер
База данных Azure для PostgreSQL — гибкий сервер

Вы можете использовать pg_dump для извлечения базы данных PostgreSQL в файл дампа и pg_restore для восстановления базы данных PostgreSQL из архивного файла, созданного pg_dump.

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

Чтобы пройти через это руководство, вам понадобится:

Для создания дампа и восстановления базы данных PostgreSQL выполните следующие действия:

Создайте файл дампа с помощью pg_dump, который содержит данные для загрузки

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

  pg_dump -Fc -v --host =  --username =  --dbname = <имя базы данных> -f  .dump
  

Например, если у вас есть локальный сервер и база данных с именем testdb в нем

  pg_dump -Fc -v --host = localhost --username = masterlogin --dbname = testdb -f testdb.свалка
  

Восстановите данные в целевой базе данных Azure для PostgreSQL с помощью pg_restore

После создания целевой базы данных вы можете использовать команду pg_restore и параметр -d, —dbname для восстановления данных в целевой базе данных из файла дампа.

  pg_restore -v --no-owner --host = <имя сервера> --port =  --username =  --dbname = <имя целевой базы данных>  .dump
  

Включение параметра —no-owner приводит к тому, что все объекты, созданные во время восстановления, принадлежат пользователю, указанному с помощью —username.Для получения дополнительной информации см. Официальную документацию PostgreSQL на pg_restore.

Примечание

Если вашему серверу PostgreSQL требуются соединения TLS / SSL (по умолчанию включены в базе данных Azure для серверов PostgreSQL), установите переменную среды PGSSLMODE = require , чтобы инструмент pg_restore соединялся с TLS. Без TLS ошибка может читаться как FATAL: требуется SSL-соединение. Укажите параметры SSL и повторите попытку.

В командной строке Windows выполните команду SET PGSSLMODE = require перед запуском команды pg_restore.В Linux или Bash выполните команду export PGSSLMODE = require перед запуском команды pg_restore.

В этом примере восстановите данные из файла дампа testdb.dump в базу данных mypgsqldb на целевом сервере mydemoserver.postgres.database.azure.com .

Вот пример того, как использовать этот pg_restore для Single Server :

  pg_restore -v --no-owner --host = mydemoserver.postgres.database.azure.com --port = 5432 --username = mylogin @ mydemoserver --dbname = mypgsqldb testdb.dump
  

Вот пример того, как использовать этот pg_restore для Flexible Server :

  pg_restore -v --no-owner --host = mydemoserver.postgres.database.azure.com --port = 5432 --username = mylogin --dbname = mypgsqldb testdb.dump
  

Оптимизация процесса миграции

Один из способов перенести существующую базу данных PostgreSQL в базу данных Azure для службы PostgreSQL — это создать резервную копию базы данных в источнике и восстановить ее в Azure.Чтобы минимизировать время, необходимое для завершения миграции, рассмотрите возможность использования следующих параметров с командами резервного копирования и восстановления.

Для бекапа

• Сделайте резервную копию с переключателем -Fc, чтобы вы могли выполнять восстановление параллельно, чтобы ускорить его. Например:

    pg_dump -h my-source-server-name -U source-server-username -Fc -d source-databasename -f Z: \ Data \ Backups \ my-database-backup.dump.
    

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

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

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

• Восстановите с помощью переключателей -Fc и -j # , чтобы распараллелить восстановление. # — количество ядер на целевом сервере. Вы также можете попробовать установить для # удвоенное количество ядер целевого сервера, чтобы увидеть влияние.Например:

Вот пример использования этого pg_restore для одиночного сервера :

  pg_restore -h my-target-server.postgres.database.azure.com -U azure-postgres-username @ my-target-server -Fc -j 4 -d my-target-databasename Z: \ Data \ Backups \ моя база данных резервная копия. дамп
  

Вот пример того, как использовать этот pg_restore для Flexible Server :

  pg_restore -h мой-целевой-сервер.postgres.database.azure.com -U azure-postgres-username @ my-target-server -Fc -j 4 -d my-target-databasename Z: \ Data \ Backups \ my-database-backup.dump
  

• Вы также можете отредактировать файл дампа, добавив команду set synchronous_commit = off; в начале и команда устанавливает synchronous_commit = on; в конце. Если не включить его в конце до того, как приложения изменят данные, это может привести к последующей потере данных.

• На целевой базе данных Azure для сервера PostgreSQL рассмотрите возможность выполнения следующих действий перед восстановлением:

  • Отключите отслеживание производительности запросов, поскольку эта статистика не нужна во время миграции.Вы можете сделать это, установив для pg_stat_statements.track, pg_qs.query_capture_mode и pgms_wait_sampling.query_capture_mode значение NONE.

  • Используйте номер SKU с высокой вычислительной мощностью и большим объемом памяти, например 32 vCore Memory Optimized, чтобы ускорить миграцию. После завершения восстановления вы можете легко вернуться к предпочитаемому номеру SKU. Чем выше номер SKU, тем большего параллелизма можно достичь, увеличив соответствующий параметр -j в команде pg_restore.

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

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

Следующие шаги

.

PostgreSQL: Документация: 9.1: Дамп SQL

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

pg_dump имя_базы> выходной файл
 

Как видите, pg_dump пишет
его результат в стандартный вывод. Ниже мы увидим, как это можно
быть полезным.

pg_dump — обычный
Клиентское приложение PostgreSQL
(хотя и особенно умный). Это означает, что вы можете
выполните эту процедуру резервного копирования с любого удаленного хоста, на котором
доступ к базе данных. Но помните, что pg_dump не работает со специальными
разрешения. В частности, он должен иметь доступ для чтения ко всем
таблицы, резервные копии которых вы хотите создать, поэтому на практике вы почти всегда
нужно запускать его как суперпользователя базы данных.

Чтобы указать, с каким сервером базы данных должен связаться pg_dump, используйте командную строку
параметры -h host и -p
порт.Хост по умолчанию
локальный хост или любой другой ваш PGHOST
переменная окружения указывает. Точно так же порт по умолчанию
обозначается средой PGPORT
переменной или, в противном случае, по умолчанию.
(Удобно, что на сервере обычно будет такой же скомпилированный
по умолчанию.)

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

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

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

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

Текстовые файлы, созданные pg_dump, предназначены для чтения
Программа psql. Генерал
форма команды для восстановления дампа —

psql dbname <файл
 

где infile - это файл
вывод командой pg_dump.
Имя базы данных не будет
созданный этой командой, поэтому вы должны создать его самостоятельно из
template0 перед выполнением
psql (например, с createdb -T template0 dbname).psql поддерживает параметры, похожие на
pg_dump для указания
сервер базы данных для подключения и имя пользователя для использования. Увидеть
справочная страница psql для получения дополнительной информации
Информация.

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

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

psql --set ON_ERROR_STOP = на имя базы данных <файл
 

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

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

pg_dump -h host1 dbname | psql -h host2 имя базы данных
 

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

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

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

pg_dumpall> Outfile
 

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

psql -f файл postgres
 

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

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

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

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

pg_dump dbname | gzip> filename.gz
 

Перезарядка с:

gunzip -c filename.gz | psql dbname
 

или:

кошка имя файла.gz | gunzip | psql dbname
 

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

pg_dump dbname | split -b 1m - имя файла
 

Перезарядка с:

кошка имя файла * | psql dbname
 

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

pg_dump -Fc dbname> имя файла
 

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

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

См. Справочные страницы pg_dump и pg_restore для
Детали.

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

.

PostgreSQL: Документация: 8.0: Резервное копирование и восстановление

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

Есть три принципиально разных подхода к поддержке
наверх Данные PostgreSQL:

У каждого есть свои сильные и слабые стороны.

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

pg_dump имя_базы> выходной файл
 

Как видите, pg_dump пишет
его результаты в стандартный вывод. Ниже мы увидим, как это
может быть полезно.

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

Чтобы указать, с каким сервером базы данных должен связаться pg_dump, используйте команду
параметры строки -h host и -p
порт. Хост по умолчанию
локальный хост или любой другой ваш PGHOST
переменная окружения указывает. Точно так же порт по умолчанию
обозначается средой PGPORT
переменной или, в противном случае, по умолчанию.(Удобно, что на сервере обычно будет такой же
встроен по умолчанию.)

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

Дампы, созданные pg_dump
внутренне согласованы, то есть обновления базы данных
во время работы pg_dump будет
не быть на свалке. pg_dump
не блокирует другие операции с базой данных, пока она
за работой. (Исключение составляют те операции, которые необходимо выполнить
с эксклюзивным замком, например VACUUM
ПОЛНЫЙ.)

Важно: Когда ваша схема базы данных полагается на
OID (например, как внешние ключи) вы должны указать
pg_dump для сброса OID
также.Для этого используйте -o
параметр командной строки. "Большой
объекты "по умолчанию также не сбрасываются. См.
справочная страница pg_dump, если
вы используете большие предметы.

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

psql dbname <файл
 

где infile - это то, что вы
используется как снаряжение для
команда pg_dump.В
база данных dbname не будет
созданный этой командой, вы должны создать его самостоятельно из
template0 перед выполнением
psql (например, с createdb -T template0 dbname). psql поддерживает параметры, похожие на
pg_dump для управления
расположение сервера базы данных и имя пользователя. См. Psql
справочная страница для получения дополнительной информации.

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

После восстановления целесообразно запустить ANALYZE для каждой базы данных, чтобы
оптимизатор имеет полезную статистику. Самый простой способ сделать это -
запустите вакуумdb -a -z, чтобы ВАКУУМНЫЙ АНАЛИЗ всех баз данных; это
эквивалентно запуску ВАКУУМНОГО АНАЛИЗА
вручную.

Возможность pg_dump
и psql для записи или чтения
from pipe позволяет выгружать базу данных прямо из
с одного сервера на другой; например:

pg_dump -h host1 dbname | psql -h host2 имя базы данных
 

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

За советом по загрузке больших объемов данных в
PostgreSQL эффективно,
см. Раздел 13.4.

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

pg_dumpall> Outfile
 

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

psql -f infile template1
 

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

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

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

pg_dump dbname | gzip> filename.gz
 

Перезарядка с

createdb dbname
gunzip -c filename.gz | psql dbname
 

или

cat filename.gz | gunzip | psql dbname
 

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

pg_dump dbname | split -b 1m - имя файла
 

Перезарядка с

createdb dbname
кошка имя файла * | psql dbname
 

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

pg_dump -Fc dbname> имя файла
 

Дамп нестандартного формата не является сценарием для psql, его необходимо восстановить.
с помощью pg_restore. Увидеть
справочные страницы pg_dump и pg_restore для
Детали.

По причинам обратной совместимости pg_dump не выгружает большие объекты
дефолт. Сбросить большой
объекты вы должны использовать либо пользовательский, либо tar-вывод
формат и используйте параметр -b в
pg_dump.См. Справочную страницу pg_dump для подробностей. В
каталог contrib / pg_dumplo
Дерево исходных текстов PostgreSQL также
содержит программу, которая может сбрасывать большие объекты.

Пожалуйста, ознакомьтесь со справочной страницей pg_dump.

.

PostgreSQL: Документация: 8.1: Резервное копирование и восстановление

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

Есть три принципиально разных подхода к поддержке
наверх Данные PostgreSQL:

У каждого есть свои сильные и слабые стороны.

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

pg_dump имя_базы> выходной файл
 

Как видите, pg_dump пишет
его результаты в стандартный вывод. Ниже мы увидим, как это
может быть полезно.

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

Чтобы указать, с каким сервером базы данных должен связаться pg_dump, используйте команду
параметры строки -h host и -p
порт. Хост по умолчанию
локальный хост или любой другой ваш PGHOST
переменная окружения указывает. Точно так же порт по умолчанию
обозначается средой PGPORT
переменной или, в противном случае, по умолчанию.(Удобно, что на сервере обычно будет такой же
встроен по умолчанию.)

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

Дампы, созданные pg_dump
внутренне согласованы, то есть обновления базы данных
во время работы pg_dump будет
не быть на свалке. pg_dump
не блокирует другие операции с базой данных, пока она
за работой. (Исключение составляют те операции, которые необходимо выполнить
с эксклюзивным замком, например VACUUM
ПОЛНЫЙ.)

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

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

psql dbname <файл
 

где infile - это то, что вы
используется как снаряжение для
команда pg_dump. В
база данных dbname не будет
созданный этой командой, вы должны создать его самостоятельно из
template0 перед выполнением
psql (например,g., с createdb -T template0 dbname). psql поддерживает параметры, похожие на
pg_dump для управления
расположение сервера базы данных и имя пользователя. См. Psql
справочная страница для получения дополнительной информации.

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

После восстановления целесообразно запустить ANALYZE для каждой базы данных, чтобы
оптимизатор имеет полезную статистику. Самый простой способ сделать это -
запустите вакуумdb -a -z, чтобы ВАКУУМНЫЙ АНАЛИЗ всех баз данных; это
эквивалентно запуску ВАКУУМНОГО АНАЛИЗА
вручную.

Возможность pg_dump
и psql для записи или чтения
from pipe позволяет выгружать базу данных прямо из
с одного сервера на другой; например:

pg_dump -h host1 dbname | psql -h host2 имя базы данных
 

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

За советом по загрузке больших объемов данных в
PostgreSQL эффективно,
см. Раздел 13.4.

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

pg_dumpall> Outfile
 

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

psql -f файл postgres
 

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

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

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

pg_dump dbname | gzip> filename.gz
 

Перезарядка с

createdb dbname
gunzip -c filename.gz | psql dbname
 

или

cat filename.gz | gunzip | psql dbname
 

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

pg_dump dbname | split -b 1m - имя файла
 

Перезарядка с

createdb dbname
кошка имя файла * | psql dbname
 

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

pg_dump -Fc dbname> имя файла
 

Дамп нестандартного формата не является сценарием для psql, его необходимо восстановить.
с помощью pg_restore. Увидеть
справочные страницы pg_dump и pg_restore для
Детали.

.