Предприниматели регулярно сталкиваются со сложностью переноса данных между разными средами. Зачастую — когда нужно защитить свои клиентские базы, настроив резервное копирование данных, или мигрировать в новую СУБД в рамках импортозамещения.
Также по мере расширения бизнеса нередки ситуации, когда филиалам требуется регулярно обновлять в локальных access-базах данные, приходящие с центрального PostgreSQL-сервера, которые требуется экспортировать в такие форматы, как CSV или XML. Еще одно применение данных, извлеченных из PostgreSQL — создание готовых HTML-страниц, электронных таблиц, презентаций или текстовых документов, которые используются компанией.
Все это делает проблему импорта и экспорта данных с этой СУБД крайне насущной для предпринимателей. Рассмотрим как правильно импортировать и экспортировать базы данных в PostgreSQL.
Импорт базы данных в PostgreSQL
Импортировать данные в PostgreSQL можно в нескольких форматах: из самой БД (диалект PL/pgSQL) и из .csv, в котором также часто хранятся датасеты. Технически работать можно и с популярным .json, однако извлечение из него значений и использование в запросах проще настраивать на Python, а не в SQL.
В формате в PostgreSQL
Импорт данных в PostgreSQL из sql-файла рекомендуется выполнять следующим образом.
Скачайте файл с базой данных demo_1 в нужную директорию, например C:\Users\User-N\Desktop\Database.
Запустите терминал Cmd.exe в Windows или консольный клиент SQL shell (psql). Выполнив команду cd C:\Users\User-N\Desktop\, перейдите в директорию с базой данной.
Загрузите базу данных из sql-файла, используя команду bash. Для этого в терминале вбейте путь до каталога с PostgreSQL, имя юзера (-U) и имя базы (-f): "C:\Program Files\PostgreSQL\10\bin\psql" -U postgres -f demo_1.sql
Обратите внимание, что чем больше база данных, тем дольше может длиться загрузка. После успешного завершения в консоли появится строчка C:\User-N\Desktop\БД>.
Теперь, если зайти в pgAdmin, графический клиент PostgreSQL, можно увидеть там импортированную базу данных со всем таблицами.
Из .csv-файла
Если файл с датасетом в формате .csv уже есть у вас на руках, зайдите в pgAdmin и либо воспользуйтесь одной из существующих баз, либо создайте новую (в нашем примере это снова будет demo_1).
В этой базе нужно создать таблицу, типы полей которой будут соответствовать «колонкам» в .csv-файле, из которого импортируются данные.
Теперь откройте консоль SQL shell (psql), которая запросит имя сервера, порта, юзера и БД, к которой вы планируете подключиться — от вас потребуется ввести только пользовательский пароль, demo-1 и нажать Enter. Соединение с БД установлено.
Для начала импорта данных из .csv файла в таблицу table_1, используй команду: \COPY table_1 FROM ‘C:\Users\User-N\Desktop\CSV\demo_1_MOW.csv’ DELIMITER ‘,’ CSV HEADER;
В этой команде:
- table_1 - название таблицы в базе данных. - FROM - путь к .csv файлу. - DELIMITER ‘,’ - разделитель в .csv файле (в данном случае запятая). - CSV HEADER - указатель, что первая строка в .csv файле содержит заголовки столбцов.
После выполнения команды, убедись, что импорт файлов в PostgreSQL прошел успешно, зайдя в pgAdmin и проверив данные в таблице.
Экспорт базы данных в PostgreSQL
Иногда возникает необходимость в экспорте данных из PostgreSQL в .csv-файл. Речь может идти, как о таблице целиком, так и о некоторых данных, соответствующих определенным условиям. Рассмотрим оба этих варианта.
Экспорт данных в .csv формат
Если таблицу table_1 из базы данных demo_1 нужно перенести целиком, то нужно подключиться к последней через SQL shell (psql), по схеме, описанной в предыдущем разделе.
Теперь правильно введите в консоль команду \COPY table_1 TO ‘C:\Users\User-N\Desktop\CSV\table.csv’ DELIMITER ‘,’ CSV HEADER Эта команда скопирует данные из таблицы table_1 в указанный .csv файл.
Также экспорт можно выполнить из pgAdmin: функция экспорта доступна при клике правой кнопкой по table_1 в списке таблиц.
Экспорт данных выборки в .csv-файл
Для экспорта только определенных полей (например, table_1_id и table_1_age), где table_1_age = '18' из таблицы table_1, можно использовать следующий SQL запрос: \COPY (SELECT table_1_id, table_1_age FROM table_1 WHERE table_1_age = '18') TO 'C:\Users\User-N\Desktop\CSV\table_1.csv' CSV HEADER DELIMITER ','; ```
В данном запросе:
table_1_id, table_1_age - выбранные поля для экспорта.
table_1 - имя таблицы, из которой выбираются данные.
table_1_age = '18' - условие для выборки данных.
TO - путь к .csv файлу, куда будут выгружены данные.
CSV HEADER DELIMITER ',' - указатель на формат .csv, наличие заголовков и использование запятой в качестве разделителя.
После выполнения данного SQL запроса в SQL shell (psql) данные будут экспортированы в указанный .csv файл. Поздравляем, экспорт PostgreSQL завершен.
Когда мы используем импорт / экспорт в PostgreSQL?
Импорт и экспорт данных играют важную роль в работе бизнеса, имеющего дело с большими объемами клиентской информации. Эти функции помогают с легкостью управлять данными, обновлять их, а также обеспечивать целостность их хранения. Кроме того, возможность автоматической замены фрагментов текста или использования констант при импорте данных в PostgreSQL дает дополнительные возможности для улучшения процессов работы с данными.
Вам, как предпринимателю, предоставляем нашу экспертную поддержку в области PostgreSQL. Мы можем помочь с:
Миграцией данных: переносом данных из различных источников в PostgreSQL. Это обеспечит безопасность и точность данных в процессе миграции.
Аудитом целостности данных: проверкой и обеспечение целостности данных. Это критически важно для ритейла, интернет-магазинов, банков и телекоммуникационных служб, работающих с конфиденциальными данными клиентов.
Эта поддержка позволит вам эффективно управлять клиентскими данными, обеспечивая их безопасное хранение и использование. Мы готовы помочь вам в любых вопросах, связанных с PostgreSQL, чтобы вы могли спокойно сосредоточиться на своем бизнесе!
Нужна поддержка или планируете изменения в инфраструктуре?
Проблемы с производительностью, переход на PostgreSQL, нестабильная БД — у нас есть опыт, чтобы это исправить. Оставьте заявку — обсудим, чем можем помочь именно вам.
Круглосуточный контроль за состоянием вашей базы данных. Уведомления в случае отклонений, отчёты и превентивные меры. Обеспечиваем стабильность и безопасность.
Как создать или удалить пользователя в PostgreSQL? Как узнать пользователя PostgreSQL — подробнее про создание, удаление, изменение пользователей на нашем сайте.
Версия PostgreSQL играет важную роль в администрировании базы данных. От нее зависит поддерживаемый функционал, совместимость с приложениями и отсутствия уязвимостей.
Опыт работы: 13 лет опыта работы с базами данных, более 6 лет опыта работы архитектором БД и DBA. Опыт построения отказоустойчивых кластеров на базе СУБД PostgreSQL и GreenPlum 6x. Постоянный докладчик на Российских и международных IT конференциях.
Иван Чувашов
Ведущий инженер в Data Driven Lab / Сертифицированный администратор PostgreSQL (PostgresPro, 10 уровень «Эксперт»)