Re: Is there a way to selective dump of records in Postgres 9.0.3?

On Mon, Apr 18, 2011 at 8:11 AM, Nikolas Everett <nik9000(at)gmail(dot)com> wrote:

> This probably isn't the right place to ask that question but you may as
> well try `pg_dump -t PATTERN`. Man pg_dump for more information on how to
> form that pattern.
>
>
> On Mon, Apr 18, 2011 at 11:05 AM, Sethu Prasad <sethuprasad(dot)in(at)gmail(dot)com>wrote:
>
>> Hi List,
>> I am using PostgreSQL 9.0.3 and I have a need to dump only the selective
>> data from partial list of tables of a database. Is there a straight way to
>> do it with pg_dump or any alternative work around to suggest here?!
>>
>
Or if you need partial data from one table - a WHERE clause - then you can
do:

COPY (select * from whatever where column=value) TO '/tmp/dump.csv' WITH CSV
HEADER

in combination with

pg_dump -f whatever.sql -s -t whatever db

to dump the DDL for the 'whatever' table into whatever.sql.

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

If it is a lot of data, you'll want to edit the whatever.sql file to remove
the CREATE INDEX statements until after you've loaded the table and then
depeneding upon how many indexes there are and how many rows you havem you
may want to parallelize the CREATE INDEX statements by running them in
parallel in multiple psql sessions (and possibly with an artificially large
maintenance_work_mem if that speeds things up)