Outra maneira é usar COPY ou \copy (o comando psql ), algo como:
COPY (SELECT * FROM big_table WHERE created_at > '2012-05-01') TO '/path/to/a/dump/file';
Eu tenho um banco de dados PostgreSQL com uma tabela multi-Gb (que contém um log de certos eventos). Eu preciso passar os últimos acontecimentos para um analista - digamos que ele só precisa de eventos do último mês.
Como posso produzir um dump apenas das linhas que têm, digamos, created_at > '2012-05-01' ?
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
AVISO LEGAL : verbatim de link
Você quer o arquivo resultante no servidor ou no cliente?
Se você deseja algo fácil de reutilizar ou automatizar, é possível usar o Postgresql incorporado em COPY comando. por exemplo,
Copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',';
Esta abordagem é executada inteiramente no servidor remoto - não pode gravar no seu PC local. Ele também precisa ser executado como um "superusuário" do Postgres (normalmente chamado de "root") porque o Postgres não pode impedi-lo de fazer coisas desagradáveis com o sistema de arquivos local da máquina.
Isso não significa que você precisa estar conectado como um superusuário (automatizar isso seria um risco de segurança de um tipo diferente), porque você pode usar a opção SECURITY DEFINER para CREATE FUNCTION para fazer uma função que é executada como se você fosse um superusuário .
A parte crucial é que sua função está lá para executar verificações adicionais, não apenas para passar a segurança - assim você pode escrever uma função que exporte os dados exatos que você precisa, ou você pode escrever algo que aceite várias opções. contanto que eles atendam a uma lista de permissões rígida. Você precisa verificar duas coisas:
GRANT s no banco de dados, mas a função agora está sendo executada como um superusuário, portanto, tabelas que normalmente estariam "fora dos limites" estarão totalmente acessíveis. Você provavelmente não quer permitir que alguém invoque sua função e adicione linhas no final da tabela "usuários"… Eu escrevi um post de blog expandindo essa abordagem , incluindo alguns exemplos de funções que exportam (ou importar) arquivos e tabelas que atendem a condições estritas.
A outra abordagem é fazer o tratamento de arquivos no lado do cliente , ou seja, em seu aplicativo ou script. O servidor do Postgres não precisa saber para qual arquivo você está copiando, apenas mostra os dados e o cliente o coloca em algum lugar.
A sintaxe básica para isso é o comando COPY TO STDOUT , e ferramentas gráficas como o pgAdmin irão envolvê-lo em um belo diálogo.
O psql cliente da linha de comando tem um "meta-comando" especial chamado \copy , que usa todas as mesmas opções que o "real" COPY , mas é executado dentro do cliente:
\copy (Select * From foo) To '/tmp/test.csv' With CSV
Note que não há finalização ; , porque os meta-comandos são terminados por nova linha, ao contrário dos comandos SQL.
De os documentos :
Do not confuse COPY with the psql instruction \copy. \copy invokes COPY FROM STDIN or COPY TO STDOUT, and then fetches/stores the data in a file accessible to the psql client. Thus, file accessibility and access rights depend on the client rather than the server when \copy is used.
Sua linguagem de programação de aplicativos may também tem suporte para enviar ou buscar os dados, mas geralmente não é possível usar COPY FROM STDIN / TO STDOUT em uma instrução SQL padrão, porque não há como conectar o fluxo de entrada / saída. O manipulador PostgreSQL do PHP ( não PDO) inclui pg_copy_from e pg_copy_to funções que copiam de / para um PHP array, que pode não ser eficiente para grandes conjuntos de dados.
Se o usuário do PSQL não tiver permissão para gravar em um arquivo, você poderá fazer algo assim.
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
Tags postgresql sql