Skip site navigation (1) Skip section navigation (2)

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

From: Andrew Dunstan <andrew(at)dunslane(dot)net>
To: Jim Nasby <jim(at)nasby(dot)net>
Cc: Josh Berkus <josh(at)agliodbs(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: pg_restore --no-post-data and --post-data-only
Date: 2011-08-25 22:15:41
Message-ID: 4E56C98D.8060002@dunslane.net (view raw or flat)
Thread:
Lists: pgsql-hackers

On 08/25/2011 06:05 PM, Jim Nasby wrote:
> On Aug 24, 2011, at 7:43 PM, Josh Berkus wrote:
>> On 8/23/11 1:30 PM, Andrew Dunstan wrote:
>>> Attached is an undocumented patch that allows pg_restore to omit
>>> post-data items or omit all but post-data items. This has been discussed
>>> before, and Simon sent in a patch back on 2008, which has bitrotted
>>> some. I'm not sure why it was dropped at the time, but I think it's time
>>> to do this. This patch relies on some infrastructure that was added
>>> since Simon's patch, so it works a bit differently  (and more simply).
>> If it's not clear from Andrew's description, the purpose of this patch
>> is to allow dividing your pgdump into 3 portions:
>>
>> 1. schema
>> 2. data
>> 3. constraints/indexes
>>
>> This allows users to implement a number of custom solutions for ad-hoc
>> parallel dump, conditional loading, data munging and sampled databases.
>> While doing so was possible before using the manifest from pg_restore
>> -l, the manifest approach has been complex to automate and relies on
>> obscure knowledge.
>>
>> I have immediate production use for this patch and may be backporting it.
> FWIW, I got around this by writing a perl script that calls pg_dump -s and watches for the end of table create statements (IIRC it specifically looks for the first CREATE INDEX). The advantage to that approach is that you don't have to first create a custom format dump and then run pg_restore against that.
>

Well, notwithstanding my well known love of perl, that strikes me as 
spending a pound to save a penny. And custom format dumps rock ;-) Also, 
your recipe above is buggy, BTW. A CREATE INDEX statement might well not 
be the first item in the post-data section.

But we could also add these switches to pg_dump too if people feel it's 
worthwhile. I haven't looked but the logic should not be terribly hard.

cheers

andrew

In response to

Responses

pgsql-hackers by date

Next:From: Josh BerkusDate: 2011-08-25 22:18:15
Subject: Re: PATCH: regular logging of checkpoint progress
Previous:From: Jim NasbyDate: 2011-08-25 22:05:35
Subject: Re: pg_restore --no-post-data and --post-data-only

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group