What's the best way to copy a subset of a table's rows from one database to another in Postgres?

Question

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?

Let's say the query is:

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?

Answer 1

Source:

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

Destination:

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

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.

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

Answer 2

source server:

BEGIN;

CREATE TEMP TABLE mmm_your_table_here AS
    SELECT * FROM your_table_here WHERE your_condition_here;

COPY mmm_your_table_here TO 'u:\\source.copy';

ROLLBACK;

your local box:

-- your_destination_table_here must be created first on your box

COPY your_destination_table_here FROM 'u:\\source.copy';

article: http://www.postgresql.org/docs/8.1/static/sql-copy.html