|From:||Jeremy Finzel <finzelj(at)gmail(dot)com>|
|To:||Carter Thaxton <carter(dot)thaxton(at)gmail(dot)com>|
|Subject:||Re: Add --include-table-data-where option to pg_dump, to export only a subset of table data|
|Views:||Raw Message | Whole Thread | Download mbox|
On Mon, May 21, 2018 at 6:34 AM Carter Thaxton <carter(dot)thaxton(at)gmail(dot)com>
> 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)
> I propose a new option:
> 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.
|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|