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
Attachment:
signature.asc
Description: Digital signature
