Re: Dumping + restoring a subset of a table?

On Tue, Oct 06, 2009 at 03:16:27PM +0200, Shaul Dar wrote:
> Hi everyone,
>
> I am looking for a way to dump+restore a subset of a database (on another
> server), using both selection and projection of the source tables (for
> simplicity assume a single table).
> I understand that pg_dump will not let me do this. One way I considered is
> creating a view with the subset definition and dumping it instead of the
> original table. In that case how do I restore the target table from the
> dumped view (what does pg_dump generate for a view?)? Can I still use
> pg_dump to create SQL commands (vs the binary file option), and will these
> still use COPY instead of INSERT statements?

When pg_dump dumps a view, it simply creates a "CREATE VIEW AS..." statement;
it doesn't copy the contents of the view as though it were a table.

> Is there another way to do this? Maybe replication? I care mostly about
> the time needed to replicate the DB (subset), less so about temp space
> needed.

If you're doing this repeatedly with the same table, you might set up a
replication system to do it, but the easiest way for a one-time thing,
provided you're running something newer than 8.1, is to copy the results of a
query to a file, e.g.:

COPY (SELECT foo, bar FROM baz WHERE some_condition) TO 'some_file';

You should probably also use pg_dump to dump the schema of the table, so it's
easy to create identically on your destination database:

pg_dump -s -t baz > baz.schema

Having recreated the table on the destination database, using COPY to restore
the selected data is straightforward:

COPY baz FROM 'some_file';

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com