Как сделать SQL-дамп данных одного столбца таблицы PostgreSQL на ПК в отдельный файл?
У нас есть таблица с несколькими столбцами и несколькими записями. Мы хотим сохранить на компьютер данные только из одного столбца этой таблицы, чтобы у нас получился файл с расширением «.sql«.
Этот файл нам нужен для хранения наиболее важной части информации из таблицы. Такой файл не хранит индексы таблицы и так как он текстовый, то его можно будет легко сжать архиватором и передать куда-то ещё. Как это сделать?
Вкратце план действий такой:
- Создаём новую таблицу на основании запроса выборки (через интерфейс pgAdmin4 или через терминальный клиент psql)
- Переходим к терминал операционной системы, где установлен PostgreSQL
- Используем команду сохранения — pg_dump
- Копируем файл выгрузки в нужное место
- Удаляем созданную ранее таблицу
SQL-запрос на получение только одного столбца таблицы PostgreSQL
Такой командой мы получаем все столбцы таблицы:
SELECT * FROM filter.t_baddlevel2;
В моём случае нужно использовать уточнение для столбца «baddname» (хранит плохие мусорные домены второго уровня сети интернет):
SELECT baddname FROM filter.t_baddlevel2;
Результат вызова в веб-интерфейсе pgAdmin4:
Сперва мы должны знать, что именно мы хотим получить. Делаем запрос на выборку данных и убеждаемся, что всё правильно. У нас получилось 36188 записей имён доменов второго уровня, которые нужны для фильтрации новых адресов для базы данных.
Создание новой таблицы на основании запроса SELECT в PostgreSQL
Команда SELECT по факту возвращает нам новую таблицу, пусть даже с одним столбцом. Это значит, что мы можем из нашего запроса создать отдельную таблицу.
CREATE TABLE filter.t_baddname_temp AS SELECT baddname FROM filter.t_baddlevel2;
При помощи команды CREATE TABLE AS мы создаём новую таблицу с именем «t_baddname_temp» в схеме данных «filter«.
Проверяем, что все данные перенеслись корректно.
SELECT * FROM filter.t_baddname_temp;
Если в новой таблице будут не те данные, то эту таблицу-клон можно будет просто удалить.
DROP TABLE filter.t_baddname_temp;
Использование консольной утилиты pg_dump для сохранения данных новой таблицы из PostgreSQL на компьютер в операционную систему — в файл
К этому моменту у нас уже есть нужная таблица для сохранения. Теперь мы должны подключиться к удалённому хосту на котором обслуживается PostgreSQL. Для этого нужно знать логин и пароль для входа в ОС, а также логин и пароль пользователя, от имени которого будет выполняться сохранение данных из СУБД.
В общем нужно знать две учётные записи.
В терминале операционной системы (например, Debian) под правильным пользователем нужно ввести команду:
pg_dump -t 'filter.t_baddname_temp' sdb > filter.t_baddlevel2-2023-01-19.sql
После выполнения, файл будет сохранён в директорию «/var/lib/postgresql«.
Если вы захотите сменить путь сохранения, то имейте ввиду, что вашему пользователю PostgreSQL может быть отказано в доступе для сохранения в нужный вам путь в ОС. Болванка файла сможет появиться, но он будет пустой.
Просмотр файла для проверки сохранения
Открываем наш файл в любом текстовом редакторе и смотрим.
Начальная часть файла будет содержать важную для PostgreSQL информацию о состоянии таблицы. После этого последуют сами данные:
Теперь можно удалить временную таблицу.
Информационные ссылки
Официальный сайт PostgreSQL — https://www.postgresql.org
Графический веб-интерфейс pgAdmin для управления СУБД PostgreSQL — https://www.pgadmin.org
Команды SQL в PostgreSQL 15 — https://postgrespro.ru/docs/postgresql/15/sql-commands
Синтаксис SQL-команды CREATE TABLE AS — https://postgrespro.ru/docs/postgresql/15/sql-createtableas
Синтаксис SQL-команды SELECT на русском языке — https://postgrespro.ru/docs/postgresql/15/sql-select
Синтаксис SQL-команды COPY на русском языке — https://postgrespro.ru/docs/postgresql/15/sql-copy
Список команд терминального клиента PSQL — https://postgrespro.ru/docs/postgresql/15/app-psql
Операционная система Debian — https://www.debian.org
