Re: Add --include-table-data-where option to pg_dump, to export only a subset of table data

From: Jeremy Finzel <finzelj(at)gmail(dot)com>
To: Carter Thaxton <carter(dot)thaxton(at)gmail(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Add --include-table-data-where option to pg_dump, to export only a subset of table data
Date: 2018-09-06 15:40:55
Message-ID: CAMa1XUiGKd+xMO35JOtt9R=E-_Hxcyi0PJ3iZmGh8+6MBbpOAg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

On Mon, May 21, 2018 at 6:34 AM Carter Thaxton <carter(dot)thaxton(at)gmail(dot)com>
wrote:

> Many times I've wanted to export a subset of a database, using some sort
> of row filter condition on some of the large tables. E.g. copying a
> production database to a staging environment, but with some time series
> data only from the past month.
>
> We have the existing options:
> --include-table=table (and its -t synonym)
> --exclude-table=table
> --exclude-table-data=table
>
> I propose a new option:
> --include-table-data-where=table:filter_clause
>
> One would use this option as follows:
>
> pg_dump --include-table-data-where=largetable:"created_at >=
> '2018-05-01'" database_name
>
> The filter_clause is used as the contents of a WHERE clause when querying
> the data to generate the COPY statement produced by pg_dump.
>
> I've prepared a proposed patch for this, which is attached. The code
> changes are rather straightforward. I did have to add the ability to carry
> around an extra pointer-sized object to the simple_list implementation, in
> order to allow the filter clause to be associated to the matching oids of
> the table pattern. It seemed the best way to augment the existing
> simple_list implementation, but change as little as possible elsewhere in
> the codebase. (Note that SimpleOidList is actually only used by pg_dump).
>
> Feel free to review and propose any amendments.
>
>
Why not simply use \copy (select * from largetable where created_at >=
'2018-05-01') to stdout? That is what I’ve always done when I need
something like this and have not found it particularly bothersome but
rather quite powerful. And here you have tons of flexibility because you
can do joins and whatever else.

FWIW. Thanks,
Jeremy

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message David G. Johnston 2018-09-06 15:47:50 Re: Add --include-table-data-where option to pg_dump, to export only a subset of table data
Previous Message Ildus Kurbangaliev 2018-09-06 15:27:13 Re: [HACKERS] Custom compression methods