У меня есть база данных PostgreSQL с таблицей на несколько гигабайт (которая содержит журнал определенных событий). Мне нужно передать последние события аналитику - допустим, ему нужны только события за последний месяц.
Как я могу создать дамп только тех строк, которые, скажем, created_at > '2012-05-01'?
Другой способ - использовать COPY или \copy (в psql команда), что-то вроде:
COPY (SELECT * FROM big_table WHERE created_at > '2012-05-01') TO '/path/to/a/dump/file';
psql -c "COPY (SELECT * FROM my_table WHERE created_at > '2012-05-01') TO STDOUT;" source_db | psql -c "COPY my_table FROM STDIN;" target_db
ОТКАЗ ОТ ОТВЕТСТВЕННОСТИ: дословно из https://stackoverflow.com/questions/1517635/save-pl-pgsql-output-from-postgresql-to-a-csv-file
Вы хотите, чтобы полученный файл был на сервере или на клиенте?
Если вам нужно что-то простое для повторного использования или автоматизации, вы можете использовать встроенный в Postgresql КОПИРОВАТЬ команда. например
Copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',';
Этот подход полностью работает на удаленном сервере. - он не может писать на ваш локальный компьютер. Его также необходимо запускать как «суперпользователь» Postgres (обычно называемый «root»), потому что Postgres не может остановить его выполнение неприятных вещей с локальной файловой системой этой машины.
На самом деле это не означает, что вы должны быть подключены как суперпользователь (автоматизация будет представлять собой угрозу безопасности другого типа), потому что вы можете использовать в SECURITY DEFINER возможность CREATE FUNCTION сделать функцию, которая работает так, как будто вы суперпользователь.
Важнейшей частью является то, что ваша функция предназначена для выполнения дополнительных проверок, а не просто для обхода безопасности - поэтому вы можете написать функцию, которая экспортирует точные данные, которые вам нужны, или вы можете написать что-то, что может принимать различные параметры, если они соблюдайте строгий белый список. Вам нужно проверить две вещи:
GRANTs в базе данных, но функция теперь работает как суперпользователь, поэтому таблицы, которые обычно находятся «вне границ», будут полностью доступны. Вероятно, вы не хотите, чтобы кто-то мог вызывать вашу функцию и добавлять строки в конец таблицы «users»…Я написал сообщение в блоге, расширяющее этот подход, включая несколько примеров функций, которые экспортируют (или импортируют) файлы и таблицы, удовлетворяющие строгим условиям.
Другой подход - обрабатывать файлы на стороне клиента, т.е. в вашем приложении или скрипте. Серверу Postgres не нужно знать, в какой файл вы копируете, он просто выдает данные, а клиент куда-то их кладет.
Базовый синтаксис для этого - COPY TO STDOUT команда, а графические инструменты, такие как pgAdmin, превратят ее в красивый диалог.
В psql клиент командной строки имеет специальную "мета-команду", которая называется \copy, который принимает все те же параметры, что и "настоящий" COPY, но выполняется внутри клиента:
\copy (Select * From foo) To '/tmp/test.csv' With CSV
Обратите внимание, что нет прекращения ;, потому что мета-команды заканчиваются новой строкой, в отличие от команд SQL.
Из документы:
Не путайте COPY с инструкцией psql \ copy. \ copy вызывает COPY FROM STDIN или COPY TO STDOUT, а затем извлекает / сохраняет данные в файле, доступном для клиента psql. Таким образом, доступность файла и права доступа зависят от клиента, а не от сервера, когда используется \ copy.
Ваш язык программирования приложений может также поддерживает отправку или выборку данных, но обычно вы не можете использовать COPY FROM STDIN/TO STDOUT в стандартном операторе SQL, потому что нет возможности подключить поток ввода / вывода. Обработчик PHP PostgreSQL (не PDO) включает очень простые pg_copy_from и pg_copy_to функции, которые копируют в / из массива PHP, что может быть неэффективным для больших наборов данных.
Если у пользователя PSQL нет разрешения на запись в файл, вы можете сделать что-то вроде этого.
psql -c "COPY (SELECT * FROM big_table WHERE created_at > '2012-05-01') TO STDOUT;" -h localhost -d my_database -U my_user > path/to/file