Как экспортировать данные таблиц из PostgreSQL в CSV / Excel

Советы и Лайфхаки
Рассмотрим, как получить данные из PostgreSQL в CSV файл, удобный для анализа в Exel, с помощью ssh строки.
  1. Заходим на сайт по ssh протоколу. Можно воспользоваться, например, PuTTY удобной бесплатной утилитой для работы с командной строкой.
    Задаем Ваши данные — хост: sitename.ru и порт: xx (обычно 22). Далее откроется командная строка. Водим логин и пароль. Теперь можно работать с командной строкой.
  2. Рекомендую сначала узнать путь к рабочей папке сервера и создать папку для хранения CSV файлов. Набираем:
    pwd
    Допустим, получили /home/user_name/. Затем создаем новый каталог:
    mkdir /home/user_name/exportcsv
  3. Подготовительная работа сделана. Подключаемся к PostgreSQL командой:
    psql
  4. Для дальнейшей работы нам должно быть известно имя Postgres базы данных. Пусть, для примера, оно будет – db_name. Подключимся к этой БД:
    \connect db_name
    Если это не сработает, возможно имя пользователя БД и пользователя SSH отличаются. Тогда воспользуйтесь командой подключения к базе db_name под тем пользователем, который настроен в Вашей системе. Пусть это будет db_user:
    -U db_user -d db_name
  5. Если Вы знаете имя таблицы, которую нужно экспортировать, можно пропустить этот шаг. Но, думаю, в общем случае, эта информация будет не известна. Поэтому выведем все таблицы на экран:
    \dt
    Скопируйте полученный список. Удобно пользоваться комбинацией клавиш ctrl + insert. Если список таблиц большой, переместитесь курсором ниже. У меня был случай, когда я не полностью скопировал список из-за того, что он не весь был на экране. Привык, что выделяя мышкой содержание окна, оно все копируется. Пришлось потом снова подключаться к серверу и загружать данные.
  6. Теперь мы знаем, какие таблицы есть в базе данных. Выходим из режима отображения таблиц, нажатием на клавишу q. Пусть имя таблицы будет table_name, тогда выгрузим ее данные в csv файл table_name.csv в папку, которую мы сделали на первом шаге:
    \copy (Select * From table_name) To '/home/user_name/exportcsv/table_name.csv' CSV DELIMITER ';' HEADER ENCODING 'UTF8';
    Обратите внимание, что праметр DELIMITER дает возможность указать разделитель ячеек. Можно, например, указать DELIMITER '#', тогда разделитель станет #, и не нужно будет беспокоиться, что где-то есть описание товара со спецсиволами использующими «;».
    Выйти из режима ввода запросов к БД можно командой ctrl + d
  7. Cсобственно, осталось пойти на ftp через FileZilla или аналогичную программу и из папки /home/user_name/exportcsv/ скачать файл table_name.csv.

Есть нюанс, кодировка файла utf-8, когда я пробую открыть в Exel, то он почему-то не опознает кодировку. Может быть у Вас будет по-другому, проверьте. Поэтому я сначала открывал файл в блокноте и в меню «формат» выибирал пункт «шрифт», а там набор символов «кириллический».

Еще материалы по теме:
Создание и импорт дампа БД PostgreSQL.
Русская документация PostgreSQL по команде Copy.
Как посмотреть размер баз и таблиц в PostgreSQL.

Оцените статью
WebVertex