PostgreSQL: импорт и экспорт резервных копий. Pg dump примеры

импорт и экспорт резервных копий

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

При установке PostgreSQL по умолчанию вместе с ним ставятся две консольные утилиты: pg_dump (для экспорта резервных копий) и pg_restore (для импорта резервных копий).

1. Создание резервных копий

Для создания резервных копий, воспользуемся консольной утилитой pg_dump

pg_dump -h <хост> -U <имя пользователя> -F <формат> -f <путь сохранения> <имя БД>

, где:

<хост> - имя хоста, на котором располагается база данных. IP-адрес, доменное имя или localhost
<имя пользователя> - имя пользователя базы данных PostgreSQL, имеющего привилегии

<формат> - формат исходного файла. Может быть следующих видов: с (архив .tar.gz), t (.tar файл) или p (просто текстовый файл, как правило, помечается как .sql)
<путь сохранения> - путь, по которому сохранится исходный файл.
<имя БД> - имя базы данных, резервную копию которой необходимо создать.

Пример:

pg_dump -h localhost -U testuser -F c -f /var/dumps/testdb.tar.gz testdb

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

2. Импорт резервных копий

Консольная утилита pg_restore умеет накатывать резервные копии только .tar файлов и архивов .tar.gz. Текстовые .sql файлы накатываются несколько иначе. рассмотрим оба способа.

2.1. Импорт .tar или .tar.gz

Для этих типов резервных копий воспользуемся консольной утилитой pg_restore:

pg_restore -h <имя хоста> -U <имя пользователя> -F <формат> -d <имя БД> <путь к дампу>

, где:

<имя хоста> - имя хоста, на котором располагается база данных. IP-адрес, доменное имя или localhost

<имя пользователя> - имя пользователя базы данных PostgreSQL, имеющего привилегии
<формат> - формат файла резервной копии. Если это .tar файл, то t, если .tar.gz архив, то c
<имя БД> - имя базы данных, на которую необходимо накатить резервную копию
<путь к дампу> - путь к файлу резервной копии

Пример:

pg_restore -h localhost -U testuser -F c -d testdb /var/dumps/testdb.tar.gz

2.2 Импорт текстового .sql файла

Текстовые .sql файлы резервной копии накатываются через cat:

cat <путь к дампу> | psql -h <имя хоста> -U <имя пользователя> <имя БД>

, где:

<путь к дампу> - путь к файлу резервной копии
<имя хоста> - имя хоста, на котором располагается база данных. IP-адрес, доменное имя или localhost
<имя пользователя> - имя пользователя базы данных PostgreSQL, имеющего привилегии
<имя БД> - имя базы данных, на которую необходимо накатить резервную копию

Пример:

cat /var/dumps/testdb.sql | psql -h localhost -U testuser testdb

karimov.info

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Важно

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

или:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

postgrespro.ru

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

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

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

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

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

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

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

Важно

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

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

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

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

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

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

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

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

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

или:

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

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

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

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

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

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

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

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

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

postgrespro.ru

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

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

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

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

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

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

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

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

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

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

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

Важно

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

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

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

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

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

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

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

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

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

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

или:

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

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

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

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

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

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

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

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

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

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

postgrespro.ru

Как сделать дамп postgresql. Для начинающих.

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

2. Использвание утилиты postgresql pg_dump Использовать эту утилитку получится только в том случае, если у вас есть доступ ssh к серверу. Многие хостеры дают такой доступ только по требованию в целях защиты. Если у вас виртуальный хостинг или еще лучше свой сервер, или вы у себя дома на компе поставили postgresql то создать дамп с помощью этой утилиты не составит проблем.Чтобы создать дамп пишем pg_dump dbname > outfileБаза db_name будет скопирована в outfileКогда я попробовал запустить эту строку у себя, получил ответ no pg_hba.conf entry for host "[local]", user "root", database "search"

Чтобы указать pg_dump к какому серверу баз данных подключатся, указываете директивы pg_dump -h host -p port По умолчанию используется localhost или переменная окружения PGHOST, порт по умолчанию PGPORT. По умолчанию pg_dump коннектится к серверу баз данных с логином под которым вы вошли в систему. Чтобы указать нужный логин используете директиву -U или задаете логин в переменной окружения PGUSERВводим строку с параметрами pg_dump -h locahost -U srch search >1.txt, где localhost - сервер баз данных, srch - пользователь. Опять не работает, пишет что не может подконнектится. После ввода вместо localhost айпиадреса 127.0.0.1 все заработало. Видно, где то у меня не прописано соотвествие localhost и ip. После ввода строки pg_dump -h 127.0.0.1 -U srch search >1.txt запросило пароль к базе и сделал дамп.

Полный перечень директив можно прочитать http://developer.postgresql.org/pgdocs/postgres/app-pgdump.html

Чтобы восстановить дамп, созданный pg_dump используйте утилиту psql dbname < infile, где infile - файл созданный pg_dumpPsql не создает базу данных, он копирует структуру в уже созданную базу, Поэтому перед использованием psql надо создать базу с нужным именем, нужной кодировкой, шаблон указываете template0 Также надо создать польззователей базы данных, с нужными разрешениями .Директивы для psql такие же как для pg_dumppsql продолжит выполнение sql команд даже если произойдет какая-то ошибка. Чтобы сделать останов на ошибке установите переменную psql --set ON_ERROR_STOP=on dbname < infile В этом случае при возникновении какой-нибудь ошибки произойдет останов.После восстановления дампа надо запустить ANALYZE.

sergey-freelancer.blogspot.com

Ускоряем восстановление бэкапов в 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 минут). Я очень доволен этими результатами. В будущем планирую заняться восстановлением только данных, а не базы целиком. Посмотрим, насколько это будет быстрее.

Ссылки:

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

habr.com

Как перенести данные из одной БД в другую. // ZetBlog

Недавно столкнулся с проблемой переноса данных в postgresql, как оказалось, сделать дамп и потом загрузку данных в PostgreSQL несколько сложнее, чем в MySQL. Рассмотрим как это сделать с помощью двух команд - pg_dump и pg_restore.

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

$ pg_dump -b -O -F c dbname -h localhost -U dbuser -p 5433 > dump

Это создаст полный дамп базы dbname с BLOB'ами и без owner'а, в специальном формате для pg_restore.

Далее, создаём базу данных на сервере, где будем делать загрузку данных:

$ sudo su postgres$ psql# CREATE DATABASE dbname OWNER test;$ psql dbnamedbname# DROP EXTENSION plpgsql;

Последняя команда нужна не во всех случаях, просто мой шаблон BD в postgresql уже содержал по дефолту объявление plpgsql и из-за этого загрузка данных вываливалась с ошибкой. Далее сама процедура загрузки:

$ pg_restore -e -O -1 -d dbname dump

Флаги в данном случае означают следующее:

Остановиться в случае ошибки.
Не выполнять восстановление прав (в моём случае на двух разных серверах просто были разные пользователи БД, что также вызывало ошибку импорта).
Выполнить всё в одной транзакции (если вдруг возникнет ошибка - будет проще начать с нуля).

Далее, поскольку пользователи на двух разных серверах различались и восстановление дампа происходило без восстановления пользователя, необходимо проставить права для пользователя test (выставляются права на таблицы, последовательности и представления aka views). В моём случае команды выполнялись от пользователя pgsql:

$ for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" dbname` ; do psql -c "alter table $tbl owner to test" dbname ; done$ for tbl in `psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';" dbname` ; do psql -c "alter table $tbl owner to test" dbname ; done$ for tbl in `psql -qAt -c "select table_name from information_schema.views where table_schema = 'public';" dbname` ; do psql -c "alter table $tbl owner to test" dbname ; done

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

P.S. Возможно, последние 3 команды можно заменить внутренними средствами PostgreSQL, но для меня такой вариант приемлем.

Update

Вышеперечисленные команды для переназначения прав можно завернуть в shell-скрипт. Например, в такой:

#!/bin/sh

for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" $1` ; do psql -c "alter table $tbl owner to $2" $1 ; donefor tbl in `psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';" $1` ; do psql -c "alter table $tbl owner to $2" $1 ; donefor tbl in `psql -qAt -c "select table_name from information_schema.views where table_schema = 'public';" $1` ; do psql -c "alter table $tbl owner to $2" $1 ; done

Первый параметр скрипта - имя БД, 2й - имя пользователя, на которого необходимо назначить права.

zetblog.ru