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

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

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

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

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

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

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

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

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

Дамп SQL

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

pg_dump bd_name > dump.sql

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

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

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

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

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

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

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

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

psql bd_name < dump.sql

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

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

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

psql --set ON_ERROR_STOP=on bd_name   
В любом случае, будет только частичное восстановление базы данных.  В качестве альтернативы, можно указать, что весь дамп должен быть восстановлен в одну транзацию, так что восстановление или будет полностью выполнено или полностью не выполнено. Данный режим может быть задан, с помощью опций командной строки -1 или single-transaction для  psql. Когда используется этот режим, необходимо проявлять осторожность, т.к. даже маленькая ошибка может привести к откату процесса восстановления, который может продолжаться несколько часов. Однако это может быть предпочтительней, чем ручная чистка сложной базы данных после частично восстановленного дампа. 
Возможность  pg_dump  и  psql  писать и читать из конвееров делают возможным создание дампа базы данных напрямую с одного сервера на другой. 
Например: 
 pg_dump -h сервер1 bd_name | psql -h сервер2 bd_name
Important: Дампы, которые делает  pg_dump, являются относительными  template0. Это означает, что любые языки, процедуры и т. д. добавленные через  template1, также попадут в дамп при выполнении  pg_dump. В итоге, при восстановлении, если Вы использовали специально изменённый  template1, Вы должны создать пустую базу данных из  template0, как показано в примере выше.
После восстановления резервной копии, следует запустить команду ANALYZE на каждую базу данных, чтобы оптимизатор запросов получил нужную статистику.
Использование утилиты pg_dumpall
pg_dump делает дамп только одной базы данных в один момент времени и не включает в дамп информацию о ролях или табличных пространствах (потому что эти данные относятся к уровню кластера, а не к самой базе данных). Дамп всего кластера баз данных создается утилитой pg_dumpall. pg_dumpall делает резервную копию каждой базы данных кластера, а также сохраняет данные уровня кластера, включая роли и определения табличных пространств. Базовая форма использования этой команды:
pg_dumpall >  dump. sql
Результирующий дамп может быть восстановлен с помощью psql:
psql –f dump.sql  postgres
Можно указать любые имена существующих баз данных, чтобы начать восстановление. Если вы производите загрузку в пустой кластер, то нужно использовать postgres. При восстановлении дампа, сделанного pg_dumpall, всегда необходимо выполнять восстановление с правами суперпользователя баз данных, потому,  что они требуются для восстановления ролей и информации о табличных пространствах. Если Вы используете табличные пространства, убедитесь, что пути к табличным пространствам в дампе соответствуют новой установке.
pg_dumpall получает и выдаёт команды для пересоздания ролей, табличных пространств и пустых баз данных, затем вызывает для каждой базы данных pg_dump. Это означает, что в то время, как каждая база данных будет внутренне целостной, снимки других баз данных могут не быть точно синхронизированы.
Управление большими базами данных
Некоторые операционные системы имеют ограничение на максимальный размер файла, что приводит к проблемам при создании больших файлов с помощью  pg_dump. К счастью,  pg_dump может писать на стандартный вывод, и можно использовать стандартные инструменты Unix  для того, чтобы избежать потенциальных проблем. Вот несколько возможных методов:
Используйте сжатые дампы. Можно использовать любимую программу сжатия, например gzip:
pg_dump bd_name | gzip > dump.sql.gz
Загружая впоследствии сжатый дамп командой:
gunzip -c dump.sql.gz | psql bd_name
Или
cat  dump.sql.gz | gunzip | psql bd_name
Используйте split. Команда  split позволяет Вам разбивать вывод на файлы меньшего размера, которые не попадают под ограничения на максимальный размер файла в файловой системе. Например, чтобы нарезать дамп на кусочки по 1 мегабайту:
pg_dump bd_name | split -b 1m - dump.sql
Загружая впоследствии полученные файлы командой:
cat dump.sql* | psql bd_name
Используйте специальный формат дампа в pg_dump. Если PostgreSQL была скомпилирована в системе с установленной библиотекой zlib, то специальный формат дампа будет сжимать данные, которые выдаются в файл вывода. Это приведёт к созданию файла дампа, который по размеру будет похож на дамп, сжатый gzip, но такой формат будет иметь преимущество, потому что позволяет выборочное восстановление таблиц. Следующая команда делает дамп базы данных, используя специальный формат дампа:
pg_dump -Fc bd_name > dump.sql
Специальный формат дампа не является скриптом для psql и должен восстанавливаться с помощью команды pg_restore, например:
pg_restore –d bd_name dump.sql
Для очень больших баз данных, нужно сочетать split с одним из двух других методов. См. подробности на страницах справочного руководства  pg_dump и pg_restore.
Восстановление базы геоданных на PostgreSQL—Справка
Для восстановления базы данных из файла архива, созданного с помощью команды pg_dump, используйте команду pg_restore. Проверьте модели архивирования и восстановления заранее на тестовых базах данных.
Когда вы восстанавливаете базу данных или геоданных с типом геометрии ST_Geometry, необходимо восстанавливать схемы в определенном порядке: сначала следует восстановить схему public, затем sde, затем остальные.
Общие инструкции по восстановлению, такие как опции синтаксиса, см. в документации к PostgreSQL. Также, если у вас установлена система PostGIS, и вы используете хранилище геометрии, обязательно прочитайте документацию PostGIS по созданию резервных копий и восстановлению баз данных. Этот процесс может выполняться по-разному в зависимости от используемой вами версии PostGIS.
В кластере PostgreSQL имена баз данных должны быть уникальны, поэтому, если вы восстанавливаете такой же кластер баз данных, необходимо удалить существующую базу данных.
dropdb –U sde mypgdb
Если вы восстанавливаете данные в новый кластер, войдите в psql, снова создайте роль учетной записи sde и предоставьте ей права суперпользователя.
CREATE ROLE sde LOGIN 
  ENCRYPTED PASSWORD '0shallpass'
  SUPERUSER INHERIT;
Если вы восстанавливаете данные в новый кластер, снова создайте роли учетных записей для всех владельцев данных. Также вы можете заново создать роли редактора и роль «только для чтения», хотя для восстановления базы данных это не обязательно.
В следующем примере скрипт создает роли и группы владельца данных, редактора и роль «только для чтения», затем добавляет роли в соответствующие группы.
--Re-create dataowners group and login roles. CREATE ROLE dataowners  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
CREATE ROLE owner1 LOGIN  ENCRYPTED PASSWORD 'pw.4.owner1'  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT dataowners TO owner1;
CREATE ROLE owner2 LOGIN  ENCRYPTED PASSWORD 'pw.4.owner2'  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT dataowners TO owner2;
--Re-create editors group and login roles. CREATE ROLE editors  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
CREATE ROLE editor1 LOGIN  ENCRYPTED PASSWORD 'pw. 4editor1'  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT editors TO owner1;
CREATE ROLE owner2 LOGIN  ENCRYPTED PASSWORD 'pw.4editor2'  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT editors TO editor2;
--Re-create readers group and login roles. CREATE ROLE readers  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
CREATE ROLE reader1 LOGIN  ENCRYPTED PASSWORD 'pw.4reader1'  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT readers TO reader1;
CREATE ROLE owner2 LOGIN  ENCRYPTED PASSWORD 'pw.4reader2'  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT readers TO reader2;
Если вы восстанавливаете данные в новый кластер базы данных, убедитесь, что библиотека st_geometry.dll помещена в папку lib PostgreSQL. Дополнительные сведения о размещении библиотеки st_geometry см. в Настройка базы геоданных в PostgreSQL на Linux или Настройка базы геоданных в PostgreSQL на Windows.
Создайте базу данных заново. Это можно сделать с помощью выражения createdb в командной строке или выражения psql.
Примечание:
Убедитесь, что новая база данных имеет те же свойства, что и та, которую вы восстанавливаете, включая имя, кодировку и владельца.
В следующем примере инструмент командной строки createdb используется для создания базы данных mypgdb с кодировкой UTF8, принадлежащей роли учетной записи sde и расположенной в табличном пространстве tblspgdb:
createdb –U sde –E UTF8 –D tblspgdb -O sde mypgdb
Снова войдите в psql и создайте схему sde, принадлежащую роли учетной записи sde в новой базе данных. Предоставьте право пользователя в схеме sde всем остальным ролям учетных записей, которым необходим доступ к базе геоданных.
CREATE SCHEMA sde
  AUTHORIZATION sde;
GRANT USAGE ON SCHEMA sde TO dataowners;
GRANT USAGE ON SCHEMA sde TO editors;
GRANT USAGE ON SCHEMA sde TO readers;
Измените путь поиска для новой базы данных, чтобы включить схему sde.
ALTER DATABASE mypgdb
  SET SEARCH_PATH="$user",sde,public;
Из командной строки восстановите схему public и данные, используя команду pg_restore. Запустите команду от имени учетной записи с правами суперпользователя, например, postgres или sde.
Внимание:
Необходимо сначала восстановить схему public и ее содержание. Если вы этого не сделаете, некоторые пространственные данные не будут восстановлены. Затем восстановите схему sde. Если этого не сделать, пространственные индексы классов пространственных объектов не будут созданы заново.
Например, для восстановления схемы public из архива базы данных mypgdb1031.dump в базу mypgdb, выполните следующее:
pg_restore -U sde -d mypgdb -n public mypgdb1031.dump
Затем восстановите содержимое схемы sde.
pg_restore -U sde -d mypgdb -n sde mypgdb1031.dump
И наконец, восстановите остальные схемы и данные.
pg_restore –U sde –d mypgdb mypgdb1031.dump
После завершения восстановления базы данных, проверьте ее, подключившись из ArcGIS Desktop и изучив данные.
 windows — Как сделать резервную копию и восстановить базу данных PostgreSQL в Windows7? 
 Я новичок в базе данных Postgres. Мне нужно получить резервную копию с производственного сервера (версия pgAdmin 9.2.4) и восстановить ее на моем локальном компьютере (у меня версия pgAdmin 9.4). Я попытался получить резервную копию, щелкнув правой кнопкой мыши базу данных -> Резервное копирование, чтобы получить файл .backup. Как показано на изображении ниже:
 Но когда я пытаюсь восстановить файл резервной копии, я получаю много ошибок.
 Я также хочу знать, могут ли разные порты в обеих системах создавать проблемы при восстановлении резервных копий. Когда я попытался восстановить резервную копию той же системы, проблем не было.
 окна
 postgresql
 pgadmin
 2
  Для резервного копирования базы данных  вы можете использовать  pg_dump.exe  :
 Open Powershell
 Перейдите в папку bin Postgres. Например:
 cd "C:\Program Files\PostgreSQL\9. 6\bin"
 
 Введите команду для создания дампа базы данных. Например:
 ./pg_dump.exe -U postgres -d my_database_name -f D:\Backup\<имя-файла-резервной копии>.sql
 
 Введите пароль для вашего пользователя postgres
  Для восстановления базы данных  можно использовать  psql.exe  .  (Обратите внимание, следующее извлечено из полезного ответа Александра Омельченко, который был удален по непонятным мне причинам.) 
 Open Powershell
 Перейдите в папку bin Postgres. Например:
 cd "C:\ProgramFiles\PostgreSQL\9.6\bin"
 
 Введите команду для восстановления базы данных. Например:
 ./psql.exe -U postgres -d my_database_name -f D:\Backup\<имя-файла-резервной копии>.sql
 
 Введите пароль для вашего пользователя postgres
 1
 Я застрял здесь при создании файла дампа базы данных из-за несоответствия версий.
 Поэтому я следую приведенной ниже команде, чтобы получить резервную копию и восстановить.
 pg_dump -h localhost -U postgres -p 5432 YourDbName > BackupFileName.dump
 
 В качестве альтернативы вы можете попробовать приведенный ниже код cmd. Если у вас возникли проблемы с восстановлением из pgAdmin4, вы можете попробовать это.
 Сначала вы должны найти путь pg_restore.exe. Обычно это
 C:\Program Files\PostgreSQL\12\bin>
 запустите приведенный ниже код после изменения имени базы данных и пути резервного копирования
 pg_restore.exe --verbose --clean -U "postgres" --dbname имя_базы_данных c:\database.backup
 
 Зарегистрируйтесь или войдите в систему 
 Зарегистрируйтесь с помощью Google
 Зарегистрироваться через Facebook
 Зарегистрируйтесь, используя адрес электронной почты и пароль
 Опубликовать как гость 
 Электронная почта
 Обязательно, но не отображается
 Опубликовать как гость 
 Электронная почта
 Требуется, но не отображается
 Восстановление базы данных PostgreSQL 
  Резюме  : в этом руководстве вы узнаете, как восстановить базу данных с помощью инструментов  восстановления PostgreSQL , включая  pg_restore  и  psql  .
 Перед восстановлением базы данных необходимо разорвать все соединения с этой базой данных и подготовить файл резервной копии. В PostgreSQL вы можете восстановить базу данных двумя способами:
 Использование  psql  для восстановления простого файла сценария SQL, созданного  Инструменты pg_dump  и  pg_dumpall .
 Использование  pg_restore  для восстановления файла tar и формата каталога, созданного инструментом  pg_dump .
 Как восстанавливать базы данных с помощью psql 
 Инструмент  psql  позволяет восстановить файл сценария SQL, сгенерированный  pg_dump  ,  pg_dumpall  или любым другим инструментом, который создает совместимые резервные копии файлов. С помощью инструмента  psql  вы можете выполнить весь сценарий в файле дампа.
 Чтобы восстановить полную резервную копию и игнорировать любые ошибки, возникшие в процессе восстановления, используйте следующую команду:
 
  psql -U имя пользователя -f backupfile. sql  
  Язык кода: CSS (css)  
 Если вы хотите остановить восстановление базы данных в случае ошибок, вы добавляете опцию  --set ON_ERROR_STOP=on  :
 
  psql -U username --set ON_ERROR_STOP=on -f backupfile  
  Язык кода: JavaScript ( джаваскрипт)  
 При резервном копировании объектов в определенной базе данных их можно восстановить с помощью следующей команды:
 
  psql -U имя_пользователя -d имя_базы_данных -f objects.sql  
  Язык кода: CSS (css)  
 Как восстанавливать базы данных с помощью pg_restore 
 Помимо инструмента  psql , вы можете использовать программу  pg_restore  для восстановления баз данных, резервные копии которых были созданы инструментами  pg_dump  или  pg_dumpall . С  pg_restore , у вас есть различные варианты восстановления баз данных, например:
 Параметр  pg_restore  позволяет выполнять параллельное восстановление с использованием параметра  -j , чтобы указать количество потоков для восстановления. Каждый поток одновременно восстанавливает отдельную таблицу, что значительно ускоряет процесс. В настоящее время  pg_restore  поддерживает этот параметр только для пользовательского формата файла.
  pg_restore  также позволяет восстанавливать определенные объекты базы данных в файле резервной копии, содержащем полную базу данных.
  pg_restore  может взять базу данных из резервной копии в старой версии и восстановить ее в новой версии.
 Давайте создадим новую базу данных с именем  newdvdrental  для практики с помощью инструмента  pg_restore .
 
  СОЗДАТЬ БАЗУ ДАННЫХ newdvdrental;  
  Язык кода: SQL (язык структурированных запросов) (sql)  
 Вы можете восстановить базу данных  dvdrental  в формате файла  tar , сгенерированном  Инструмент pg_dump  в учебнике по резервному копированию базы данных PostgreSQL с помощью следующей команды:
 
  pg_restore --dbname=newdvdrental --verbose c:\pgbackup\dvdrental. tar  
 
 Если вы восстанавливаете базу данных, то же самое в качестве резервной копии вы можете использовать следующую команду:
 
  pg_restore --dbname=dvdrental --create --verbose c:\pgbackup\dvdrental.tar  
 
 Начиная с PostgreSQL 9.2, вы можете используйте  --section  опция для восстановления только структуры таблицы. Это позволяет использовать новую базу данных в качестве шаблона для создания других баз данных.
 Сначала создайте новую базу данных с именем  dvdrental_tpl  .
 
  СОЗДАТЬ БАЗУ ДАННЫХ dvdrental_tpl;  
  Язык кода: SQL (язык структурированных запросов) (sql)  
 Во-вторых, восстановите структуру таблицы только из файла резервной копии  dvdrental.