Re: pg_restore --no-post-data and --post-data-only

On Aug 26, 2011, at 5:23 PM, Andrew Dunstan wrote:
> On 08/26/2011 04:46 PM, Jim Nasby wrote:
>> On Aug 26, 2011, at 12:15 PM, Andrew Dunstan wrote:
>>> I knew there would be some bike-shedding about how we specify these things, which is why I haven't written docs
yet.
>> While we're debating what shade of yellow to paint the shed...
>>
>> My actual use case is to be able to be able to "inject" SQL into a SQL-formatted dump either pre- or post-data (I'm
on8.3, so I don't actually dump any data; I'm *mostly* emulating the ability to dump data on just certain tables). 
>>
>> So for what I'm doing, the ideal interface would be a way to tell pg_dump "When you're done dumping all table
structuresbut before you get to any constraints, please run $COMMAND and inject it's output into the dump output." For
someof the data obfuscation we're doing it would be easiest if $COMMAND was a perl script instead of SQL, but we could
probablyconvert it. 
>>
>> Of course, many other folks actually need the ability to just spit out specific portions of the dump; I'm hoping we
cancome up with something that supports both concepts. 
>>
>
> Well, the Unix approach is to use tools that do one thing well to build up more complex tools. Making pg_dump run
someexternal command to inject things into the stream seems like the wrong thing given this philosophy. Use pg_dump to
getthe bits you want (pre-data, post-data) and sandwich them around whatever else you want. 

I agree... except for one little niggling concern: If pg_dump is injecting something, then the DDL is being grabbed
witha single, consistent snapshot. --pre and --post do not get you that (though we could probably use the new ability
toexport snapshots to fix that...) 

> As for getting data from just certain tables, I just posted a patch for pg_dump to exclude data for certain tables,
andwe could look at providing a positive as well as a negative filter if there is sufficient demand. 

Unfortunately some of the dumped data needs to be sanitized, so that won't work unless I can also dump an arbitrary
SELECT.But yes, a positive filter would definitely be welcome. 
--
Jim C. Nasby, Database Architect                   jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net