Re: Dumping + restoring a subset of a table?

From: Joshua Tolley <eggyknap(at)gmail(dot)com>
To: Shaul Dar <shauldar(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Dumping + restoring a subset of a table?
Date: 2009-10-06 13:33:04
Message-ID: 20091006133304.GD32257@eddie
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Karl Denninger 2009-10-06 13:39:11 Re: Best suiting OS
Previous Message Nikolas Everett 2009-10-06 13:21:08 Re: Speed / Server