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

On Tue, Nov 15, 2011 at 8:19 PM, Joshua Berkus <josh(at)agliodbs(dot)com> wrote:
>> > Here is a patch for that for pg_dump. The sections provided for are
>> > pre-data, data and post-data, as discussed elsewhere. I still feel that
>> > anything finer grained should be handled via pg_restore's --use-list
>> > functionality. I'll provide a patch to do the same switch for pg_restore
>> > shortly.
>> >
>> > Adding to the commitfest.
>>
>> Updated version with pg_restore included is attached.
>
> Functionality review:
>
> I have tested the backported version of this patch using a 500GB production database with over 200 objects and it worked as specified.
>
> This functionality is extremely useful for the a variety of selective copying of databases, including creating shrunken test instances, ad-hoc parallel dump, differently indexed copies, and sanitizing copies of sensitive data, and even bringing the database up for usage while the indexes are still building.
>
> Note that this feature has the odd effect that some constraints are loaded at the same time as the tables and some are loaded with the post-data.  This is consistent with how text-mode pg_dump has always worked, but will seem odd to the user.  This also raises the possibility of a future pg_dump/pg_restore optimization.

That does seem odd. Why do we do it that way?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company