Перенос базы данных PostgreSQL с помощью экспорта и импорта
Можно извлечь базу данных PostgreSQL в файл сценария с помощью pg_dump и импортировать данные из этого файла в целевую базу данных с помощью psql.
Предварительные требования
Прежде чем приступить к выполнению этого руководства, необходимы следующие компоненты:
-
с правилами брандмауэра, разрешающими доступ к этом серверу и его базам данных;
- установленная программа командной строки pg_dump;
- установленная программа командной строки psql.
Выполните приведенные ниже действия, чтобы экспортировать и импортировать базу данных PostgreSQL.
Создание файла сценария, содержащего загружаемые данные, с помощью pg_dump
Чтобы экспортировать имеющуюся базу данных PostgreSQL в локальную среду или на виртуальную машину в виде файла сценария SQL, выполните следующую команду:
Например, если имеется локальный сервер с базой данных testdb.
Импорт данных в целевую базу данных Azure для PostrgeSQL
Вы можете использовать командную строку psql с параметром —dbname (-d), чтобы импортировать данные в базу данных Azure для сервера PostrgeSQL и загрузить данные из SQL-файла.
Импорт и экспорт данных в PostgreSQL, гайд для начинающих
В процессе обучения аналитике данных у человека неизбежно возникает вопрос о миграции данных из одной среды в другую. Поскольку одним из необходимых навыков для аналитика данных является знание SQL, а одной из наиболее популярных СУБД является PostgreSQL, предлагаю рассмотреть импорт и экспорт данных на примере этой СУБД.
В своё время, столкнувшись с импортом и экспортом данных, обнаружилось, что какой-то более-менее структурированной инфы мало: этот момент обходят на всяких там курсах по аналитике, подразумевая, что это очень простые моменты, которым не следует уделять внимание.
В данной статье приведены примеры импорта в PostgreSQL непосредственно самой базы данных в формате sql, а также импорта и экспорта данных в наиболее простом и распространенном формате .csv, в котором в настоящее время хранятся множество существующих датасетов. Формат .json хоть и является также очень распространенным, рассмотрен не будет, поскольку, по моему скромному мнению, с ним все-таки лучше работать на Python, чем в SQL.
1. Импорт базы данных в формате в PostgreSQL
Скачиваем (получаем из внутреннего корпоративного источника) файл с базой данных в выбранную папку. В данном случае путь:
Имя файла: demo-big-20170815
Далее понадобиться командная строка windows или SQL shell (psql). Для примера воспользуемся cmd. Переходим в каталог, где находится скачанная БД, командой cd C:\Users\User-N\Desktop\БД :
Далее выполняем команду для загрузки БД из sql-файла:
«C:\Program Files\PostgreSQL\10\bin\psql» -U postgres -f demo-big-20170815.sql
Где сначала указывается путь, по которому установлен PostgreSQL на компьютере, -U – имя пользователя, -f — название файла БД.
Отметим, что в зависимости от размера базы данных загрузка может занимать до нескольких десятков минут. Конец загрузки будет отмечен следующим видом:
Заходим в pgAdmin и наблюдаем там импортированную БД:
2. Импорт данных из csv-файла
Предполагается, что у вас уже есть необходимый .csv-файл, и первое, что нужно сделать, это перейти pgAdmin и создать там новую базу данных. Ну или воспользоваться уже существующей, в зависимости от текущих нужд. В данном случае была создана БД airtickets.
В выбранной БД создается таблица с полями, типы которых должны соответствовать «колонкам» в выбранном .csv-файле.
Далее воспользуемся SQL shell (psql) для подключения к нужной БД и для подачи команд на импорт данных. При открытии SQL shell (psql) она стандартно спросит про имя сервера, имя подключаемой БД, порт и пользователя. Ввести нужно только имя БД и пароль пользователя, всё остальное проходим нажатием ентра. Создается подключение к нужной БД – airtickets.
Ну и вводим команды на импорт данных из файла:
\COPY tickets FROM ‘C:\Users\User-N\Desktop\CSV\ticket_dataset_MOW.csv’ DELIMITER ‘,’ CSV HEADER;
Где tickets – название созданной в БД таблицы, из – путь, где хранится .csv-файл, DELIMITER ‘,’ – разделитель, используемый в импортируемом .csv-файле, сам формат файла и HEADER , указывающий на заголовки «колонок».
Один интересный момент. Написание команды COPY строчными (маленькими) буквами привело к тому, что psql ругнулся, выдал ошибку и предложил написать команду прописными буквами.
Заходим в pgAdmin и удостоверяемся, что данные были загружены.
3. Экспорт данных в .csv-файл
Предположим, нам надо сохранить таблицу airports_data из уже упоминаемой выше БД demo.
Для этого подключимся к БД demo через SQL shell (psql) и наберем команду, указав уже знакомые параметры разделителя, типа файла и заголовка:
\COPY airports_data TO ‘C:\Users\User-N\Desktop\CSV\airports.csv’ DELIMITER ‘,’ CSV HEADER;
Существует и другой способ экспорта через pgAdmin: правой кнопкой мыши по нужной таблице – экспорт – указание параметров экспорта в открывшемся окне.
4. Экспорт данных выборки в .csv-файл
Иногда возникает необходимость сохранить в .csv-файл не полностью всю таблицу, а лишь некоторые данные, соответствующие некоторому условию. Например, нам нужно из БД demo таблицы flights выбрать поля flight_id, flight_no, departure_airport, arrival_airport, где departure_airport = ‘SVO’. Данный запрос можно вставить сразу в команду psql:
Как перенести базу данных postgresql на другой компьютер
Идея, стоящая за этим методом, заключается в генерации текстового файла с командами SQL, которые при выполнении на сервере пересоздадут базу данных в том же самом состоянии, в котором она была на момент выгрузки. PostgreSQL предоставляет для этой цели вспомогательную программу pg_dump . Простейшее применение этой программы выглядит так:
Как видите, pg_dump записывает результаты своей работы в устройство стандартного вывода. Далее будет рассмотрено, чем это может быть полезно. В то время как вышеупомянутая команда создаёт текстовый файл, pg_dump может создать файлы и в других форматах, которые допускают параллельную обработку и более гибкое управление восстановлением объектов.
Программа pg_dump является для PostgreSQL обычным клиентским приложением (хотя и весьма умным). Это означает, что вы можете выполнять процедуру резервного копирования с любого удалённого компьютера, если имеете доступ к нужной базе данных. Но помните, что pg_dump не использует для своей работы какие-то специальные привилегии. В частности, ей обычно требуется доступ на чтение всех таблиц, которые вы хотите выгрузить, так что для копирования всей базы данных практически всегда её нужно запускать с правами суперпользователя СУБД. (Если у вас нет достаточных прав для резервного копирования всей базы данных, вы тем не менее можете сделать резервную копию той части базы, доступ к которой у вас есть, используя такие параметры, как -n схема или -t таблица .)
Указать, к какому серверу должна подключаться программа pg_dump , можно с помощью аргументов командной строки -h сервер и -p порт . По умолчанию в качестве сервера выбирается localhost или значение, указанное в переменной окружения PGHOST . Подобным образом, по умолчанию используется порт, заданный в переменной окружения PGPORT , а если она не задана, то порт, указанный по умолчанию при компиляции. (Для удобства при компиляции сервера обычно устанавливается то же значение по умолчанию.)
Как и любое другое клиентское приложение PostgreSQL , pg_dump по умолчанию будет подключаться к базе данных с именем пользователя, совпадающим с именем текущего пользователя операционной системы. Чтобы переопределить имя, либо добавьте параметр -U , либо установите переменную окружения PGUSER . Помните, что pg_dump подключается к серверу через обычные механизмы проверки подлинности клиента (которые описываются в Главе 20).
Важное преимущество pg_dump в сравнении с другими методами резервного копирования, описанными далее, состоит в том, что вывод pg_dump обычно можно загрузить в более новые версии PostgreSQL , в то время как резервная копия на уровне файловой системы и непрерывное архивирование жёстко зависят от версии сервера. Также, только метод с применением pg_dump будет работать при переносе базы данных на другую машинную архитектуру, например, при переносе с 32-битной на 64-битную версию сервера.
Дампы, создаваемые pg_dump , являются внутренне согласованными, то есть, дамп представляет собой снимок базы данных на момент начала запуска pg_dump . pg_dump не блокирует другие операции с базой данных во время своей работы. (Исключение составляют операции, которым нужна исключительная блокировка, как например, большинство форм команды ALTER TABLE .)
25.1.1. Восстановление дампа
Текстовые файлы, созданные pg_dump , предназначаются для последующего чтения программой psql . Общий вид команды для восстановления дампа:
где файл_дампа — это файл, содержащий вывод команды pg_dump . База данных, заданная параметром имя_базы , не будет создана данной командой, так что вы должны создать её сами из базы template0 перед запуском psql (например, с помощью команды createdb -T template0 имя_базы ). Программа psql принимает параметры, указывающие сервер, к которому осуществляется подключение, и имя пользователя, подобно pg_dump . За дополнительными сведениями обратитесь к справке по psql . Дампы, выгруженные не в текстовом формате, восстанавливаются утилитой pg_restore .
Перед восстановлением SQL-дампа все пользователи, которые владели объектами или имели права на объекты в выгруженной базе данных, должны уже существовать. Если их нет, при восстановлении будут ошибки пересоздания объектов с изначальными владельцами и/или правами. (Иногда это желаемый результат, но обычно нет).
По умолчанию, если происходит ошибка SQL, программа psql продолжает выполнение. Если же запустить psql с установленной переменной ON_ERROR_STOP , это поведение поменяется и psql завершится с кодом 3 в случае возникновения ошибки SQL:
В любом случае вы получите только частично восстановленную базу данных. В качестве альтернативы можно указать, что весь дамп должен быть восстановлен в одной транзакции, так что восстановление либо полностью выполнится, либо полностью отменится. Включить данный режим можно, передав psql аргумент -1 или —single-transaction . Выбирая этот режим, учтите, что даже незначительная ошибка может привести к откату восстановления, которое могло продолжаться несколько часов. Однако это всё же может быть предпочтительней, чем вручную вычищать сложную базу данных после частично восстановленного дампа.
Благодаря способности pg_dump и psql писать и читать каналы ввода/вывода, можно скопировать базу данных непосредственно с одного сервера на другой, например:
Важно
Дампы, которые выдаёт pg_dump , содержат определения относительно template0 . Это означает, что любые языки, процедуры и т. п., добавленные в базу через template1 , pg_dump также выгрузит в дамп. Как следствие, если при восстановлении вы используете модифицированный template1 , вы должны создать пустую базу данных из template0 , как показано в примере выше.
После восстановления резервной копии имеет смысл запустить ANALYZE для каждой базы данных, чтобы оптимизатор запросов получил полезную статистику; за подробностями обратитесь к Подразделу 24.1.3 и Подразделу 24.1.6. Другие советы по эффективной загрузке больших объёмов данных в PostgreSQL вы можете найти в Разделе 14.4.
25.1.2. Использование pg_dumpall
Программа pg_dump выгружает только одну базу данных в один момент времени и не включает в дамп информацию о ролях и табличных пространствах (так как это информация уровня кластера, а не самой базы данных). Для удобства создания дампа всего содержимого кластера баз данных предоставляется программа pg_dumpall , которая делает резервную копию всех баз данных кластера, а также сохраняет данные уровня кластера, такие как роли и определения табличных пространств. Простое использование этой команды:
Полученную копию можно восстановить с помощью psql :
(В принципе, здесь в качестве начальной базы данных можно указать имя любой существующей базы, но если вы загружаете дамп в пустой кластер, обычно нужно использовать postgres ). Восстанавливать дамп, который выдала pg_dumpall , всегда необходимо с правами суперпользователя, так как они требуются для восстановления информации о ролях и табличных пространствах. Если вы используете табличные пространства, убедитесь, что пути к табличным пространствам в дампе соответствуют новой среде.
pg_dumpall выдаёт команды, которые заново создают роли, табличные пространства и пустые базы данных, а затем вызывает для каждой базы pg_dump . Таким образом, хотя каждая база данных будет внутренне согласованной, состояние разных баз не будет синхронным.
Только глобальные данные кластера можно выгрузить, передав pg_dumpall ключ —globals-only . Это необходимо, чтобы полностью скопировать кластер, когда pg_dump выполняется для отдельных баз данных.
25.1.3. Управление большими базами данных
Некоторые операционные системы накладывают ограничение на максимальный размер файла, что приводит к проблемам при создании больших файлов с помощью pg_dump . К счастью, pg_dump может писать в стандартный вывод, так что вы можете использовать стандартные инструменты Unix для того, чтобы избежать потенциальных проблем. Вот несколько возможных методов:
Используйте сжатые дампы. Вы можете использовать предпочитаемую программу сжатия, например gzip :
Затем загрузить сжатый дамп можно командой:
Используйте split . Команда split может разбивать выводимые данные на небольшие файлы, размер которых удовлетворяет ограничению нижележащей файловой системы. Например, чтобы получить части по 2 гигабайта:
Восстановить их можно так:
Использовать GNU split можно вместе с gzip :
Восстановить данные после такого разбиения можно с помощью команды zcat .
Используйте специальный формат дампа pg_dump . Если при сборке PostgreSQL была подключена библиотека zlib , дамп в специальном формате будет записываться в файл в сжатом виде. В таком формате размер файла дампа будет близок к размеру, полученному с применением gzip , но он лучше тем, что позволяет восстанавливать таблицы выборочно. Следующая команда выгружает базу данных в специальном формате:
Дамп в специальном формате не является скриптом для psql и должен восстанавливаться с помощью команды pg_restore , например:
За подробностями обратитесь к справке по командам pg_dump и pg_restore .
Для очень больших баз данных может понадобиться сочетать split с одним из двух других методов.
Используйте возможность параллельной выгрузки в pg_dump . Чтобы ускорить выгрузку большой БД, вы можете использовать режим параллельной выгрузки в pg_dump . При этом одновременно будут выгружаться несколько таблиц. Управлять числом параллельных заданий позволяет параметр -j . Параллельная выгрузка поддерживается только для формата архива в каталоге.
Вы также можете восстановить копию в параллельном режиме с помощью pg_restore -j . Это поддерживается для любого архива в формате каталога или специальном формате, даже если архив создавался не командой pg_dump -j .