Перенос базы данных PostgreSQL с помощью экспорта и импорта
Можно извлечь базу данных PostgreSQL в файл сценария с помощью pg_dump и импортировать данные из этого файла в целевую базу данных с помощью psql. Если вы хотите перенести все базы данных, можно использовать pg_dumpall для дампа всех баз данных в один файл скрипта.
Предварительные требования
Прежде чем приступить к выполнению этого руководства, необходимы следующие компоненты:
- сервер базы данных Azure для PostgreSQL с правилами брандмауэра, разрешающими доступ к этом серверу и его базам данных;
- установленная программа командной строки pg_dump;
- установленная программа командной строки psql.
Выполните приведенные ниже действия, чтобы экспортировать и импортировать базу данных PostgreSQL.
Создание файла сценария, содержащего загружаемые данные, с помощью pg_dump
Чтобы экспортировать имеющуюся базу данных PostgreSQL в локальную среду или на виртуальную машину в виде файла сценария SQL, выполните следующую команду:
pg_dump --host= --username= --dbname= --file=.sql Например, если имеется локальный сервер с базой данных testdb.
pg_dump --host=localhost --username=masterlogin --dbname=testdb --file=testdb.sql Импорт данных в целевую базу данных Azure для PostrgeSQL
Вы можете использовать командную строку psql с параметром —dbname (-d), чтобы импортировать данные в базу данных Azure для сервера PostrgeSQL и загрузить данные из SQL-файла.
psql --file=.sql --host= --port=5432 --username= --dbname=
В этом примере используется программа psql и файл сценария testdb.sql из предыдущего шага, чтобы импортировать данные в базу данных mypgsqldb на целевом сервере mydemoserver.postgres.database.azure.com.
Для отдельного сервера используйте следующую команду.
psql --file=testdb.sql --host=mydemoserver.database.windows.net --port=5432 --username=mylogin@mydemoserver --dbname=mypgsqldb Для гибкого сервера используйте следующую команду.
psql --file=testdb.sql --host=mydemoserver.database.windows.net --port=5432 --username=mylogin --dbname=mypgsqldb Дальнейшие действия
- Сведения о переносе базы данных PostgreSQL с помощью дампа и ее восстановлении см. в этой статье.
- Дополнительные сведения о переносе баз данных в службу «База данных Azure для PostgreSQL» см. в этой статье.
Импорт и экспорт данных в 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:
\COPY (SELECT flight_id, flight_no, departure_airport, arrival_airport FROM flights WHERE departure_airport = ‘SVO’) TO ‘C:\Users\User-N\Desktop\CSV\flights_SVO.csv’ CSV HEADER DELIMITER ‘,’;
Вот такой небольшой гайд получился.
- Импорт экспорт данных в PostgreSQL
- импорт и экспорт в csv
- psql команда copy
Импорт данных из файлов различных типов в таблицы PostgreSQL
Каким образом можно осуществить импорт информации из файлов Excel/Access/CSV/… (список можно продолжить) в базу данных PostgreSQL? Этот вопрос с завидным постоянством появляется на форумах, конференциях и в списках рассылки, посвященных данной СУБД. Ответы на вопросы, касающиеся импорта данных в PostgreSQL, чаще всего содержат рекомендации по использованию различных (зачастую не опробованных на практике) SQL-скриптов, применению технологии ODBC совместно с приложением, в котором исходный файл был создан, или же советы воспользоваться разнообразными программными инструментами для преобразования данных с последующим вызовом утилиты pgsql. Эти рекомендации могут помочь решить задачу, связанную с импортом данных в БД PostgreSQL, но только в том случае, если исходный файл имеет простую структуру, объем импортируемой информации невелик, а пользователи могут подключаться к серверу напрямую.
Но что если исходный файл с информацией имеет формат Word 2007 или HTML? Или это TXT файл, содержащий Unicode данные? Или же CSV файл, размером несколько сотен мегабайт и имеющий достаточно большое число столбцов? В этой ситуации решения, приведенные выше, нередко не могут дать нужного результата – процесс импорта данных заканчивается ошибкой, исходные данные искажены и перенесены не в полном объеме, при этом сама процедура импорта занимает значительное время.
Простое и эффективное решение задачи импорта данных в PostgreSQL
В данной статье мы рассмотрим программный продукт, специально предназначенный для решения основных задач, связанных с импортом информации в PostgreSQL — EMS Data Import for PostgreSQL. Программа позволяет быстро импортировать данные в таблицы PostgreSQL из файлов MS Excel 97-2007, MS Access, DBF, XML, TXT, CSV, RTF, MS Word 2007, ODF и HTML. Пользователю предоставляется широкий набор возможностей, таких как определение разнообразных параметров импорта для каждого исходного файла в отдельности, осуществление импорта данных в одну или несколько таблиц либо представлений (views), расположенных в одной и той же или различных БД, выбор необходимого режима импортирования. Утилита позволяет использовать специальный режим пакетной вставки для максимально быстрого импорта данных, поддерживает Unicode и все последние версии СУБД PostgreSQL, имеет дружественный и гибкий пользовательский интерфейс, оформленный в виде мастера, который проведет Вас через все шаги импорта информации, а также обладает множеством других полезных возможностей.
При использовании EMS Data Import for PostgreSQL для импорта данных, у пользователя программы существует возможность указать логическое соответствие между столбцами исходного файла и столбцами целевой таблицы, расположенной в БД PostgreSQL, при этом учитывая формат исходного файла. Более того, для большинства форматов исходных файлов программа способна определить такое соответствие автоматически, в случае если исходный файл и целевая таблица имеют сходный порядок столбцов или строк. При настройке процесса импорта пользователь может указать, если это необходимо, индивидуальный формат для каждого импортируемого поля. Это очень полезная возможность программы, когда требуется, например, определить значения для одного или некоторых исходных столбцов в виде констант или же в процессе импорта следует произвести автоматическую замену фрагмента текста в исходных данных на заданное значение. К другой полезной особенности EMS Data Import for PostgreSQL следует отнести возможность определить набор SQL команд, выполняемых непосредственно до или после процесса импорта.
Data Import for PostgreSQL позволяет полностью настроить пользовательский интерфейс под Ваши потребности, а также обладает многоязыковой поддержкой. В случае если сервер PostgreSQL расположен за сетевым брандмауэром и к нему нет возможности подключиться напрямую, утилита способна использовать для подключения SSH или HTTP туннели, при этом для SSH соединений, если это требуется по соображениям безопасности, можно указать открытый и личный криптографический ключ.
Если требуется выполнять импорт данных из файлов в БД PostgreSQL на периодической основе, то Вам достаточно настроить необходимые параметры в программе всего один раз и сохранить конфигурацию в виде специального файла-шаблона. В дистрибутив Data Import for PostgreSQL, помимо программы с графическим интерфейсом, входит консольная утилита, которую можно вызывать по расписанию, и тем самым автоматизировать процесс импорта. Имя ранее сохраненного файла с конфигурацией передается данной консольной утилите в виде параметра командной строки.
Для решения задач, связанных с импортом информации из файлов различных форматов в таблицы БД PostgreSQL, существует большое количество разнообразных программных продуктов, разработанные как на основе open source, так и коммерческие проекты с закрытым исходным кодом. Однако лишь некоторые из этих программ способны предложить пользователю полный набор функций, необходимых для успешного выполнения процесса импорта. EMS Data Import for PostgreSQL – один из немногих программных инструментов, позволяющий решить все основные вопросы, возникающие при решении задачи по импорту данных в БД PostgreSQL.
Следует заметить, что импорт данных – это малая часть из повседневных задач, с которыми сталкиваются администраторы PostgreSQL в их повседневной работе. EMS SQL Management Studio for PostgreSQL поможет Вам значительно упростить задачи, связанные с разработкой баз данных PostgreSQL, администрированием серверов этой СУБД, созданием эффективных SQL запросов, разграничением доступа к данным, сравнением и синхронизацией данных и схем БД, и многие другие.
Как перенести базу данных PostgreSQL на другой сервер с помощью pgAdmin 4
Всем привет! Сегодня мы рассмотрим процесс переноса базы данных PostgreSQL с одного сервера на другой, например, с компьютера разработчика на production-сервер, при этом мы будем использовать графический инструмент pgAdmin 4.
Исходные данные. Задача
Допустим, мы разрабатываем базу данных в PostgreSQL, при этом мы используем обычный клиентский компьютер под управлением операционной системы Windows 10, где собственно локально и установлен PostgreSQL.
В качестве инструмента разработки мы используем стандартное графическое приложение pgAdmin 4.
pgAdmin 4 – это стандартный и бесплатный графический инструмент для разработки баз данных в PostgreSQL, который можно использовать для написания SQL запросов, разработки процедур, функций, а также для выполнения базовых задач администрирования баз данных.
В итоге базу данных мы разработали, протестировали ее, внесли в нее необходимые данные, заполнили справочники, в общем, база данных готова.
Теперь у нас возникла необходимость перенести эту базу данных на реальный сервер, который и будет выступать в качестве сервера баз данных. И так как мы используем PostgreSQL, в качестве такого сервера баз данных обычно выступает сервер под управлением операционной системы Linux.
Таким образом, нам необходимо перенести базу данных PostgreSQL, разработанную в Windows, в базу данных PostgreSQL на Linux. В моем случае в качестве операционной системы Linux будет выступать дистрибутив Debian.
Создание дампа базы данных PostgreSQL в pgAdmin 4
Весь процесс переноса базы данных PostgreSQL достаточно простой, суть в следующем.
Нам необходимо создать копию нашей базы данных (дамп), затем создать пустую базу на нужном нам сервере и восстановить все данные, используя созданный ранее дамп.
Все это можно сделать с нашего клиентского компьютера, используя pgAdmin 4, если, конечно же, целевой сервер нам доступен, если недоступен, то придётся каким-то другим образом переносить дамп базы данных на нужный сервер и, используя стандартные консольные утилиты, восстановить базу данных из дампа.
Кстати, стоит отметить, что pgAdmin 4 для экспорта/импорта баз данных использует как раз эти стандартные консольные утилиты, в частности pg_dump, pg_dumpall и pg_restore, которые по умолчанию входят в состав PostgreSQL.
pg_dump – утилита для экспорта баз данных PostgreSQL
pg_dumpall – утилита для экспорта кластера баз данных PostgreSQL (всех данных на сервере)
pg_restore – утилита восстановления баз данных PostgreSQL из файла архива
Таким образом, благодаря pgAdmin 4 нам не нужно писать и выполнять команды в командной строке, за нас все это делает pgAdmin 4, мы всего лишь будем пользоваться мышкой, настраивая все параметры в графическом интерфейсе.
Создать дамп базы данных PostgreSQL можно в нескольких форматах, в частности:
Специальный (Custom) – это пользовательский формат, который использует сжатие. Данный формат по умолчанию предлагается в pgAdmin 4 и рекомендован для средних и больших баз данных. Обычно архивные файлы в таком формате создают с расширением backup, однако можно использовать и другое расширение.
Tar (tar) – база данных выгружается в формат tar. Данный формат не поддерживает сжатие.
Простой (plain) – в данном случае база данных выгружается в обычный текстовый SQL-скрипт, в котором все объекты базы данных и непосредственно сами данные будут в виде соответствующих SQL инструкций. Данный скрипт можно легко отредактировать в любом текстовом редакторе и выполнить, используя Query Tool, как обычные SQL запросы. Данный формат рекомендован для небольших баз данных, а также для тех случаев, когда требуется внести изменения в дамп базы данных перед восстановлением.
Каталог (directory) – этот формат файла создает каталог, в котором для каждой таблицы и большого объекта будут созданы отдельные файлы, а также файл оглавления в машиночитаемом формате, понятном для утилиты pg_restore. Этот формат по умолчанию использует сжатие, а также поддерживает работу в несколько потоков.
В данном материале мы рассмотрим создание дампа в специальном формате, а также в формате обычного SQL скрипта, дело в том, что процесс восстановления базы данных из этих форматов в pgAdmin 4 немного отличается.
Создание дампа базы данных в сжатом формате
Чтобы создать дамп базы данных PostgreSQL в pgAdmin 4, необходимо в обозревателе выбрать нужную базу данных, я выбираю базу данных shop, далее необходимо вызвать контекстное меню правой кнопкой мыши и нажать на пункт «Резервная копия».
Затем всего лишь нужно указать имя архивного файла и путь к каталогу, где его сохранить, для этого можно использовать кнопку с тремя точками.
Формат «Специальный», как было отмечено ранее, предлагается по умолчанию, поэтому выбирать его не требуется.
Как я уже отмечал, обычно архив в таком формате создают с расширением backup, я так и поступаю, т.е. архив назову shop.backup и сохраню его в каталоге D:\PostgreSQL_Backup\.
В случае необходимости задать определенный уровень сжатия можно с помощью параметра «Коэффициент сжатия», поддерживаются значения от 0 до 9, где 0 – вообще не использовать сжатие, а 9 самый высокий уровень сжатия, по умолчанию используется умеренное сжатие.
В нашем случае база данных небольшая, поэтому мы можем оставить все по умолчанию.
Больше никаких настроек в нашем случае делать нет необходимости, и мы можем нажать на кнопку «Резервная копия», чтобы запустить процесс создания дампа базы данных.
Когда появится сообщение «Успешно завершено», значит, процесс создания дампа базы данных PostgreSQL завершен успешно, в противном случае Вы будете получать сообщения о неуспешном завершении.
Создание дампа базы данных в простом формате SQL
В данном случае нам необходимо сделать практически все то же самое, только нужно выбрать формат «Простой» и дополнительно включить пару параметров, чтобы добавление данных осуществлялось с помощью обычных инструкций INSERT, а не с помощью команды COPY, которая используется по умолчанию.
Для этого переходим на вкладку «Параметры выгрузки» и включаем два параметра «Использовать команды INSERT» и «INSERT с указанием столбцов», хотя данный параметр можно и не указывать.
Заметка! Начинающим программистам рекомендую почитать мою книгу «SQL код», которая поможет Вам изучить язык SQL как стандарт, в ней рассматриваются все базовые конструкции языка SQL, приводится много примеров и скриншотов.
Импорт дампа базы данных PostgreSQL в pgAdmin 4
Дамп готов, теперь можно переходить к восстановлению базы данных из этого дампа. Однако перед тем как приступать к импорту, необходимо создать пустую базу данных, в которую собственно и импортировать все данные, как это делается, я подробно рассказывал в отдельном материале.
Все действия по созданию базы данных и восстановлению данных этой базы из архивной копии мы будем делать все на том же компьютере с помощью того же pgAdmin 4, только для этого необходимо подключиться к нужному нам серверу (пункт контекстного меню «Создать сервер» и ввести настройки для подключения, подробнее, как это делается, я рассказывал в той же статье, которая посвящена установке PostgreSQL на Debian).
Импорт сжатого дампа базы данных
Чтобы импортировать базу данных, дамп который был создан в «специальном» формате, необходимо на целевом сервере выбрать базу данных, которую требуется восстановить из дампа (мы ее предварительно создали), в контекстном меню выбрать пункт «Восстановить», затем в пункте «Имя файла», используя кнопку с тремя точками, указать файл дампа, который мы создали чуть ранее с расширением backup.
Больше никаких настроек вводить не требуется, нужный формат выбран по умолчанию, мы можем сразу нажимать кнопку «Восстановить».
Когда появится сообщение «Успешно завершено», процесс будет завершен.
В результате все данные будут восстановлены из дампа, и таким образом мы перенесли базу данных PostgreSQL на новый сервер.
Импорт дампа базы данных в формате SQL
В случае с простым форматом, т.е. с обычными SQL инструкциями, использовать отдельный функционал для восстановления не получится, поэтому мы можем просто выполнить SQL скрипт, который содержится в этом файле.
Для этого открываем Query Tool (запросник) в контексте нужной нам базы данных, затем используя кнопку «Открыть файл» выбираем наш дамп в формате SQL и нажимаем кнопку «Выполнить».
Если инструкция выполнится без ошибок, значит, все хорошо.
Примечание! Если Вы уже восстановили базу данных предыдущим способом, то, чтобы использовать этот способ, необходимо пересоздать базу данных, иначе возникнет конфликт и, соответственно, ошибка, так как все объекты в базе уже будут существовать.
В итоге мы перенесли базу данных PostgreSQL с одного сервера, который управляется операционной системой Windows, на другой, который управляется Linux, хотя это, как Вы понимаете, в нашем случае было не так принципиально.
Стоит отметить, что если требуется перенести базу данных, размер которой достаточно большой, например, несколько десятков или сотен гигабайт, то лучше напрямую использовать консольные утилиты pg_dump или pg_dumpall, т.е. без графического интерфейса pgAdmin 4.
Видео – Перенос базы данных PostgreSQL на другой сервер с помощью pgAdmin 4
На сегодня это все, надеюсь, материал был Вам полезен, пока!