Postgres：将多个COPY TO输出合并到可导入postgres的文件 [英] Postgres: Combining multiple COPY TO outputs to a postgres-importable file

问题描述

我有我的数据库托管在heroku上，我想下载数据库的特定部分（例如，所有的行，id> x从表1 ，所有的行来自表2中的name = x 等）在一个文件中。

从一些研究和询问问题这里似乎有些修改的pg_dump可以解决我的问题。但是，我无法使用pg_dump，因为我无法访问命令行（基本上我想要能够点击我的网络应用程序中的一个按钮，它将生成+下载数据库文件）。 p>

所以我的新策略是使用postgres copy 命令。我将通过我的服务器数据库中的各种表，运行 COPY（选择* FROM ... WHERE ...）到文件名，其中文件名只是一个临时文件，我将在完成后下载。

问题是这个 filename 文件只会有行，所以我不能只是转身将其导入pgadmin。假设我有一个'空'数据库设置（模式，索引和东西都已经设置），有没有办法我可以格式化我的文件名文件，以便它可以轻松导入到postgres db？

解决方案

根据我关于/从stdout / stdin的评论，回答关于将多个表包含在一个文件中的实际问题;您可以构建输出文件，以便将stdin 中的 copy ...与实际数据进行交织，并通过 psql 加载。例如， psql 将支持如下所示的输入文件：

 从stdin复制my_table（col1，col2，col3）; 
 foo bar baz 
 fizz buzz bizz 
 \。 
  

（注意尾随的 \。分隔符应该是制表符;您还可以在复制命令中指定分隔符选项。）

psql 将把';'和'。'之间的所有内容当作stdin来处理。这实际上模拟了当您导出表数据并且没有模式（例如， pg_dump -a -t my_table ）时， pg_dump 。

生成的负载可能与 psql mydb < output.dump 。

I have my database hosted on heroku, and I want to download specific parts of the database (e.g. all the rows with id > x from table 1, all the rows with name = x from table 2, etc.) in a single file.

From some research and asking a question here it seems that some kind of modified pg_dump would solve my problem. However, I won't be able to use pg_dump because I won't have access to the command line (basically I want to be able to click a button in my web app and it will generate + download the database file).

So my new strategy is to use the postgres copy command. I'll go through the various tables in my server database, run COPY (Select * FROM ... WHERE ...) TO filename , where filename is just a temporary file that I will download when complete.

The issue is that this filename file will just have the rows, so I can't just turn around and import it into pgadmin. Assuming I have an 'empty' database set up (the schema, indices, and stuff are all already set up), is there a way I can format my filename file so that it can be easily imported into a postgres db?

解决方案

Building on my comment about to/from stdout/stdin, and answering the actual question about including multiple tables in one file; you can construct the output file to interleave copy ... from stdin with actual data and load it via psql. For example, psql will support input files that look like this:

copy my_table (col1, col2, col3) from stdin;
foo    bar    baz
fizz   buzz   bizz
\.

(Note the trailing \. and that the separators should be tabs; you could also specify the delimiter option in the copy command).

psql will treat everything between the ';' and '.' as stdin. This essentially emulates what pg_dump does when you export table data and no schema (e.g., pg_dump -a -t my_table).

The resulting load could be as simple as psql mydb < output.dump.

这篇关于Postgres：将多个COPY TO输出合并到可导入postgres的文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！