在Postgres中将表行的子集从一个数据库复制到另一个数据库的最佳方法是什么？ [英] What's the best way to copy a subset of a table's rows from one database to another in Postgres?

问题描述

我有一个生产数据库，有1000万行。我想从过去一小时的生产中提取10,000行左右的行，然后将其复制到我的本地机器中。我该怎么做？

I've got a production DB with, say, ten million rows. I'd like to extract the 10,000 or so rows from the past hour off of production and copy them to my local box. How do I do that?

让我们说查询是：

SELECT * FROM mytable WHERE date > '2009-01-05 12:00:00';

如何获取输出，将其导出到某种转储文件中，然后导入该转储

How do I take the output, export it to some sort of dump file, and then import that dump file into my local development copy of the database -- as quickly and easily as possible?

推荐答案

源：

psql -c "COPY (SELECT * FROM mytable WHERE ...) TO STDOUT" > mytable.copy

目的地：

psql -c "COPY mytable FROM STDIN" < mytable.copy

这假定mytable在源和目标中具有相同的架构和列顺序。如果不是这种情况，可以尝试使用 STDOUT CSV标头和 STDIN CSV标头代替 STDOUT 和 STDIN ，但我还没有尝试过。

This assumes mytable has the same schema and column order in both the source and destination. If this isn't the case, you could try STDOUT CSV HEADER and STDIN CSV HEADER instead of STDOUT and STDIN, but I haven't tried it.

如果您在mytable上有任何自定义触发器，则可能需要在导入时将其禁用：

If you have any custom triggers on mytable, you may need to disable them on import:

psql -c "ALTER TABLE mytable DISABLE TRIGGER USER; \
         COPY mytable FROM STDIN; \
         ALTER TABLE mytable ENABLE TRIGGER USER" < mytable.copy

这篇关于在Postgres中将表行的子集从一个数据库复制到另一个数据库的最佳方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！