Restore postgresql backup: Backup and restore a PostgreSQL database on PgAdmin 4 using the program’s graphical interface

Backup and restore postgresql databases

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

Владимир работал на этих конференциях не с самой удобной, с протестной аудиторией. И добивался результата — пусть с ним не соглашались, но запоминали. Можно рассматривать и компанию Датавед, как попытку экспериментальной проверки острых, иногда жестких и неудобных, вопросов и тем, поднимавшихся Владимиром на этих конференциях. Причем же здесь 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 < dump. sql

В любом случае, будет только частичное восстановление базы данных. В качестве альтернативы, можно указать, что весь дамп должен быть восстановлен в одну транзацию, так что восстановление или будет полностью выполнено или полностью не выполнено. Данный режим может быть задан, с помощью опций командной строки -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.

http://www.dataved.ru/2014/11/postgresql-backup-and-restore.html

PostgreSQL: дамп и восстановление –

спросил

Изменено
1 год, 10 месяцев назад

Просмотрено
19 тысяч раз

Я использую EMS SQL Manager для PostgreSQL, и мне нужно создать дамп сложной базы данных (домены, более 300 хранимых процедур/функций, триггеры, данные и т. д.).
Этот инструмент не может этого сделать.

Посоветуйте, пожалуйста, хороший инструмент с графическим интерфейсом для postgres.

  • postgresql

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

 pg_dumpall -p 5432 > /path/to/my/dump_file.sql

Дамп одной базы данных:

 pg_dump -p 5432 mydb > /path/to/my/mydb_dump. sql

Дамп только схемы:

 pg_dump -p 5432 mydb -s > /path/to/my/mydb_dump_schema.sql

Подробнее в инструкции.

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

 УДАЛИТЬ БАЗУ ДАННЫХ, ЕСЛИ СУЩЕСТВУЕТ mydb;
СОЗДАТЬ БАЗУ ДАННЫХ mydb;

Опция --clean для pg_dump в этом случае не нужна.

2

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

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

 судосу-постгрес

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

 pg_dump db_name > db_backup.sql

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

 psql db_name < db_backup.sql

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

 pg_dumpall > cluster_backup.sql

Восстановить весь кластер базы данных postgres

 psql -f cluster_backup. sql postgres

Обратитесь к этому источнику за дополнительными командами команды резервного копирования

pgAdmin3 сделает свое дело, в программу установки включены pg_dump и pg_restore.

5

, если вы используете метод аутентификации md5 и хотите использовать определенного пользователя для получения дампа БД, вы можете сделать

 $ pg_dump -U имя пользователя -p 5432 имя БД> имя-файла-для-резервного-в.sql

Чтобы избежать проблем с учетными данными и именем пользователя при восстановлении, вы можете использовать флаг --no-owner 

 $ pg_dump --no-owner -U имя пользователя -p 5432 имя_базы > имя_файла для резервного копирования в.sql

Для восстановления резервной копии используйте следующую команду

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

pg_dump -U uguryilmaz modaltrans_dev > backup.sql

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя адрес электронной почты и пароль

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Нажимая «Опубликовать свой ответ», вы соглашаетесь с нашими условиями обслуживания и подтверждаете, что прочитали и поняли нашу политику конфиденциальности и кодекс поведения.

sed - Как восстановить одну таблицу из резервной копии .sql postgresql?

спросил

Изменено
20 дней назад

Просмотрено
67 тысяч раз

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

 psql -h localhost -d proddump -f /Users/U/Desktop/prod_db_backup/PostgreSQL/site_prod.sql

Это приводит к локальному полному восстановлению. Но что нам нужно, так это восстановить строки одной таблицы в рабочую среду. Любые советы о том, как заставить это работать с PostgreSQL 9.1?

Спасибо

  • postgresql
  • sed
  • postgresql-9. 1

Я не знаю ни одного инструмента для этого, но этот однострочный код извлекает драгоценную_таблицу из 9 0042 my_backup.sql 9\\\.$/p' my_backup.sql

4

Не делайте резервные копии SQL, если вам нужно восстановить одну таблицу и т. д. Используйте параметр pg_dump -Fc — «пользовательский» формат. Это можно восстановить с помощью pg_restore . Возможно выборочное восстановление, а также множество других полезных функций. pg_restore может позже преобразовать дамп пользовательского формата в дамп SQL, если вам это нужно.

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

  • Используйте текстовый редактор, чтобы извлечь данные целевой таблицы в отдельный файл и просто восстановить его; или

  • Восстановите дамп в одноразовой базе данных, затем используйте pg_dump для получения выборочного дампа, включающего только эту таблицу. Поскольку он одноразовый, вы можете использовать отдельный экземпляр Pg на какой-нибудь незагруженной быстрой, но небезопасной машине, где вы включаете все опции «сделай это быстро, но съешь мои данные, если хочешь», такие как fsync=off . Вы должны НИКОГДА установил это в производстве.

2

Есть простой способ.

‘pg_restore’ имеет опцию ‘—table/-t’.

 pg_restore -a -t ваша_таблица /путь/к/dump.sql

Используйте ‘-h’ для удаленного хоста.
Другие варианты смотрите здесь

1

Вы можете использовать grep + sed в roder для получения данных таблицы:

Во-первых, вам нужно определить границы: 9COPY test2/COPY new_table_name/’ > new_table_name.sql

Обратите внимание, вам нужно вычесть единицу из второго числа (т.е. исключить следующую копию stmt)

Теперь вы можете загрузить new_table_name.sql и восстановить данные, которые вам нужны.
Теперь вы можете загрузить данные в новую таблицу

.

1

Недавно я написал пошаговое руководство по восстановлению отдельных таблиц postgres.

Короче не работает с бэкапами SQL, нужно переходить на pg_dump для создания файлов резервных копий:

 pg_dump.exe --host localhost --port 5432 --username "postgres" --schema "public" --format custom --blobs --verbose --file "my_database.dump " "моя_база данных"

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

  1. Создать пустую локальную базу данных my_database_restored
  2. Создайте таблицу, которую необходимо восстановить my_table в пустой базе
  3. Запустите pg_restore для выборочного импорта данных желаемой таблицы:
 pg_restore.exe -U postgres --data-only -d "my_database_restored" -t "my_table" "my_database. dump"

У меня есть дамп pg_dumpall . И я хотел бы восстановить таблицу с именем пользователей из базы данных с именем edc , так как у вас, скорее всего, будут таблицы с одинаковыми именами в разных базах данных и даже схемах. 9\\connect/ ограничивает поиск рамками моей базы данных;

  • {…} выполнит все внутренние команды для диапазона;
  • /\susers\(\s\|$\)/ соответствует всем строкам с пользователями отдельно, включая конец строки;
  • /;\|\\\./ соответствует строкам с ; или содержащий \.
  • p принудительно выводит совпадающие строки (обратите внимание, что sed вызывается с помощью -n ).

    • В зависимости от сложности ваших данных вам может потребоваться настроить это.

    Все, что вам нужно сделать, это направить вывод sed в команду psql с правильными переключателями.

    Я написал несколько команд для восстановления данных только одной таблицы из обычного текста pg_dumpall. Вдохновленный Zouppen, спасибо.

    Восстановление данных таблицы из резервной копии простого текста SQL, например, с помощью pg_dumpall.

    1. перейти в каталог 9\\\.$/p’ pg_full_bak.dmp > my_table.sql
      лс -л

      1. проверить правильность данных
       меньше my_table.sql
      1. подключиться к базе данных db_name , содержащей пустую таблицу my_table , которую мы хотим восстановить, с учетной записью, имеющей разрешение на запись в таблицу
       psql -d имя_базы_данных
      1. проверить, что my_table пуста
       выберите count(*) nb из my_table ;
+------+
| нб |
+------+
| 0 |
+------+
      1. импорт данных
       \i my_table.sql
      1. проверить правильность работы
       выберите count(*) nb из my_table;
+-------+
| нб |
+-------+
| 9876 |
+-------+

      Это работает для меня на странице 9.