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

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).

So with this patch, the following three sequences should be equivalent:

pg_restore --no-post-data
pg_restore --post-data-only

pg_restore -s --no-post-data
pg_restore -a
pg_restore --post-data-only

pg_restore

This is useful and worth doing on its own, and will also add to the
usefulness of the pg_dump --exclude-table-data patch in my previous email.

As with that patch, a version that applies to version 9.0 and 8.4
sources is also attached, for the very eager.

cheers

andrew

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.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 08/24/2011 08: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.

It's already backported, at least as far as 8.4. Check your email :-)

cheers

andrew

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.
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net

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

On 08/25/2011 06:15 PM, Andrew Dunstan wrote:
>
>
> 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.
>

Something like the attached, in fact, which seems pretty simple.

cheers

andrew

On Fri, Aug 26, 2011 at 11:22 AM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>> 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.
>
> Something like the attached, in fact, which seems pretty simple.

It seems like there are three sets of things you might want here:
pre-data, data, post-data. So in the end we could end up with:

--pre-data-only
--post-data-only
--data-only
--no-pre-data
--no-post-data
--no-data

And then maybe someone will want just the create index commands and
not the constraint commands. It seems like it might be more elegant
to come up with a single switch where you can list which things you
want:

--sections='predata data'
--sections='postdata'
--sections='index'

Just thinking out loud....

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

On Fri, 2011-08-26 at 12:46 -0400, Robert Haas wrote:
> --sections='predata data'
> --sections='postdata'
> --sections='index'

Agreed. After command line options reach a certain level of complexity,
I think it's worth looking for a more general way to express them.

Regards,
Jeff Davis

On 08/26/2011 12:46 PM, Robert Haas wrote:
> On Fri, Aug 26, 2011 at 11:22 AM, Andrew Dunstan<andrew(at)dunslane(dot)net> wrote:
>>> 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.
>> Something like the attached, in fact, which seems pretty simple.
> It seems like there are three sets of things you might want here:
> pre-data, data, post-data. So in the end we could end up with:
>
> --pre-data-only
> --post-data-only
> --data-only
> --no-pre-data
> --no-post-data
> --no-data
>
> And then maybe someone will want just the create index commands and
> not the constraint commands. It seems like it might be more elegant
> to come up with a single switch where you can list which things you
> want:
>
> --sections='predata data'
> --sections='postdata'
> --sections='index'
>
> Just thinking out loud....

I knew there would be some bike-shedding about how we specify these
things, which is why I haven't written docs yet.

All the possibilities you specify except for the indexes section can be
done by using these switches in combination with -s and -a.

For anything more fine-grained, I'm inclined to say that people need to
roll their own. pg_restore's --list and --use-list give you extremely
fine-grained control. I have working scripts which use these for example
to filter out londiste and pgq objects, certain large tables, audit
objects and more. As an example of the complexity I think we should
avoid, which section would UNIQUE and PRIMARY KEY constraints belong in?
"constraints" because that's what they are, or "indexes" because that's
what they create? No matter which answer you choose someone will claim
you have violated POLA.

Chopping things into pre-data, data and post-data would get us around
99% of the cases we could reasonably provide for in my experience. That
seems enough :-)

I don't have anything in principle against your '--sections="foo bar"'
suggestion, but it would be more work to program. Simpler, and probably
more consistent with how we do other things, would be allowing multiple
--section options, if we don't want to have named options such as I have
provided.

cheers

andrew

On Fri, Aug 26, 2011 at 1:15 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> I don't have anything in principle against your '--sections="foo bar"'
> suggestion, but it would be more work to program. Simpler, and probably more
> consistent with how we do other things, would be allowing multiple --section
> options, if we don't want to have named options such as I have provided.

I wouldn't object to that, but "more work to program" probably means
about an extra 10 lines of code in this particular case.

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

Excerpts from Robert Haas's message of vie ago 26 15:36:36 -0300 2011:
> On Fri, Aug 26, 2011 at 1:15 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> > I don't have anything in principle against your '--sections="foo bar"'
> > suggestion, but it would be more work to program. Simpler, and probably more
> > consistent with how we do other things, would be allowing multiple --section
> > options, if we don't want to have named options such as I have provided.
>
> I wouldn't object to that, but "more work to program" probably means
> about an extra 10 lines of code in this particular case.

The "--section=data --section=indexes" proposal seems very reasonable to
me -- more so than "--sections='data indexes'".

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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 on 8.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 structures but before you get to any constraints, please run $COMMAND and inject it's output into the dump output." For some of the data obfuscation we're doing it would be easiest if $COMMAND was a perl script instead of SQL, but we could probably convert it.

Of course, many other folks actually need the ability to just spit out specific portions of the dump; I'm hoping we can come up with something that supports both concepts.
--
Jim C. Nasby, Database Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> The "--section=data --section=indexes" proposal seems very reasonable to
> me -- more so than "--sections='data indexes'".

+1 ... not only easier to code and less squishily defined, but more like
the existing precedent for other pg_dump switches, such as --table.

regards, tom lane

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 on 8.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 structures but before you get to any constraints, please run $COMMAND and inject it's output into the dump output." For some of the data obfuscation we're doing it would be easiest if $COMMAND was a perl script instead of SQL, but we could probably convert it.
>
> Of course, many other folks actually need the ability to just spit out specific portions of the dump; I'm hoping we can come 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 some external command to
inject things into the stream seems like the wrong thing given this
philosophy. Use pg_dump to get the bits you want (pre-data, post-data)
and sandwich them around whatever else you want. As for getting data
from just certain tables, I just posted a patch for pg_dump to exclude
data for certain tables, and we could look at providing a positive as
well as a negative filter if there is sufficient demand.

cheers

andrew

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> For anything more fine-grained, I'm inclined to say that people need to roll
> their own. pg_restore's --list and --use-list give you extremely
> fine-grained control. I have working scripts which use these for example to
> filter out londiste and pgq objects, certain large tables, audit objects and

Which is exactly the core features of pg_staging, that builds schema
whitelist and schema_nodata options on top of pg_restore listing. The
only complex thing here is to be able to filter out triggers using a
function defined in a schema you're filtering out, but pg_staging has
support for that.

http://tapoueh.org/pgsql/pgstaging.html
https://github.com/dimitri/pg_staging
http://tapoueh.org/blog/2011/03/29-towards-pg_staging-10.html

And you can also only use the pg_restore listing commands of pg_staging
without having to do the full installation of its features. Will write
some article about how to use it for only catalog listing purpose,
without its infrastructure for fetching backups and managing dev staging
environments.

> I don't have anything in principle against your '--sections="foo bar"'
> suggestion, but it would be more work to program. Simpler, and probably more
> consistent with how we do other things, would be allowing multiple --section
> options, if we don't want to have named options such as I have provided.

+1 for --section foo --section bar.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

> 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.

A big +1 to --pre-data and --post-data, but until we get there, or
if you have an existing dump file (schema *or* schema+data) that needs
parsing, there is an existing tool:

http://blog.endpoint.com/2010/01/splitting-postgres-pgdump-into-pre-and.html

Once these new flags and the ability to custom format dump pg_dumpall
is done, I'll have very little left to complain about with pg_dump :)

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201108271855
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAk5ZdfwACgkQvJuQZxSWSsipDQCgpmNtD/I/2gfAzm2b3jouD8nS
qhgAn33t5VLiF8HeslBwCqyMzQJy6VN5
=PfK7
-----END PGP SIGNATURE-----

On 08/27/2011 06:56 PM, Greg Sabino Mullane wrote:
>
> Once these new flags and the ability to custom format dump pg_dumpall
> is done, I'll have very little left to complain about with pg_dump :)
>
>

It's off topic. But I think custom format would require a major mangling
to be able to handle a complete cluster. This isn't just a simple matter
of programming, IMNSHO.

cheers

andrew

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 on 8.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 structures but before you get to any constraints, please run $COMMAND and inject it's output into the dump output." For some of the data obfuscation we're doing it would be easiest if $COMMAND was a perl script instead of SQL, but we could probably convert it.
>>
>> Of course, many other folks actually need the ability to just spit out specific portions of the dump; I'm hoping we can come 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 some external command to inject things into the stream seems like the wrong thing given this philosophy. Use pg_dump to get the 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 with a single, consistent snapshot. --pre and --post do not get you that (though we could probably use the new ability to export 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, and we 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(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net

Excerpts from Jim Nasby's message of mié ago 31 16:45:59 -0300 2011:
> 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 on 8.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 structures but before you get to any constraints, please run $COMMAND and inject it's output into the dump output." For some of the data obfuscation we're doing it would be easiest if $COMMAND was a perl script instead of SQL, but we could probably convert it.
> >>
> >> Of course, many other folks actually need the ability to just spit out specific portions of the dump; I'm hoping we can come 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 some external command to inject things into the stream seems like the wrong thing given this philosophy. Use pg_dump to get the 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 with a single, consistent snapshot. --pre and --post do not get you that (though we could probably use the new ability to export snapshots to fix that...)

Eh, --pre and --post are pg_restore flags, so you already have a
consistent snapshot.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On 08/31/2011 04:03 PM, Alvaro Herrera wrote:
>>>
>>> Well, the Unix approach is to use tools that do one thing well to build up more complex tools. Making pg_dump run some external command to inject things into the stream seems like the wrong thing given this philosophy. Use pg_dump to get the 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 with a single, consistent snapshot. --pre and --post do not get you that (though we could probably use the new ability to export snapshots to fix that...)
> Eh, --pre and --post are pg_restore flags, so you already have a
> consistent snapshot.
>

We've been talking about adding them for pg_dump too.

I take Jim's point about the snapshot, but I still don't feel it's a
good reason to allow some arbitrary code or script to be run between
them (and after all, it's not likely to run with the same snapshot anyway).

cheers

andrew

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

> It's off topic. But I think custom format would require a major mangling
> to be able to handle a complete cluster. This isn't just a simple matter
> of programming, IMNSHO.

Oh, I meant just having it create separate custom format files for each
database. As shell scripts all over the world have been doing for years,
but it would be nice if it was simply built in.

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201109012139
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAk5gM+oACgkQvJuQZxSWSsi+xgCfbr0q+Ilbw0JRsORLZN2pSz1r
JtcAoJaleZvW/wWtU83d9MVeOes4I6+0
=VqFQ
-----END PGP SIGNATURE-----

"Greg Sabino Mullane" <greg(at)turnstep(dot)com> writes:
>> It's off topic. But I think custom format would require a major mangling
>> to be able to handle a complete cluster. This isn't just a simple matter
>> of programming, IMNSHO.
>
> Oh, I meant just having it create separate custom format files for each
> database. As shell scripts all over the world have been doing for years,
> but it would be nice if it was simply built in.

+1
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

On 09/01/2011 09:40 PM, Greg Sabino Mullane wrote:
>> It's off topic. But I think custom format would require a major mangling
>> to be able to handle a complete cluster. This isn't just a simple matter
>> of programming, IMNSHO.
> Oh, I meant just having it create separate custom format files for each
> database. As shell scripts all over the world have been doing for years,
> but it would be nice if it was simply built in.
>
>

I guess it could be done, although I'm not going to do it :-) I'm more
about making somewhat hard things easier than easy things slightly
easier :-) You'd have to invent some sort of way to name files, possibly
by supplying a template to the -f parameter which would fill in some
placeholder, say a %, with the name of the database. Of course, then
you'd have to make sure the database name didn't contain any forbidden
characters.

cheers

andrew

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
>> Oh, I meant just having it create separate custom format files for each
>> database. As shell scripts all over the world have been doing for years,
>> but it would be nice if it was simply built in.
>
> I guess it could be done, although I'm not going to do it :-) I'm more about
> making somewhat hard things easier than easy things slightly easier :-)

Then what about issuing an archive (tar or ar format here) containing
one custom file per database plus the globals file, SQL, plus maybe a
database listing, and hacking pg_restore so that it knows what to do
with such an input ?

Bonus points if that supports the current -l and -L options, of course.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

On 09/03/2011 04:49 PM, Dimitri Fontaine wrote:
> Andrew Dunstan<andrew(at)dunslane(dot)net> writes:
>>> Oh, I meant just having it create separate custom format files for each
>>> database. As shell scripts all over the world have been doing for years,
>>> but it would be nice if it was simply built in.
>> I guess it could be done, although I'm not going to do it :-) I'm more about
>> making somewhat hard things easier than easy things slightly easier :-)
> Then what about issuing an archive (tar or ar format here) containing
> one custom file per database plus the globals file, SQL, plus maybe a
> database listing, and hacking pg_restore so that it knows what to do
> with such an input ?
>
> Bonus points if that supports the current -l and -L options, of course.
>
>

That's probably a lot of code for a little benefit, at least from my
POV, but others might find it useful.

cheers

andrew

On 08/26/2011 05:11 PM, Tom Lane wrote:
> Alvaro Herrera<alvherre(at)commandprompt(dot)com> writes:
>> The "--section=data --section=indexes" proposal seems very reasonable to
>> me -- more so than "--sections='data indexes'".
> +1 ... not only easier to code and less squishily defined, but more like
> the existing precedent for other pg_dump switches, such as --table.
>
>

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.

cheers

andrew

Hi Andrew,

On 13/11/2011 02:56, Andrew Dunstan 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.

FWIW, I've tested the patch as I've recently needed to build a custom
splitting script for a project and the patch seemed to be a much more
elegant solution. As far as I can tell, it works great and the output
matches the result of my script.

The only little thing I've noticed is a missing ending ")" in the --help
message.

Cheers
--
Matteo Beccati

Development & Consulting - http://www.beccati.com/

On Sat, November 12, 2011 8:56 pm, Andrew Dunstan wrote:
>
>
> On 08/26/2011 05:11 PM, Tom Lane wrote:
>> Alvaro Herrera<alvherre(at)commandprompt(dot)com> writes:
>>> The "--section=data --section=indexes" proposal seems very reasonable
>>> to
>>> me -- more so than "--sections='data indexes'".
>> +1 ... not only easier to code and less squishily defined, but more like
>> the existing precedent for other pg_dump switches, such as --table.
>>
>>
>
>
> 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.

cheers

andrew

> > 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.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
San Francisco

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

>> 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?

Beats me.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

Josh Berkus <josh(at)agliodbs(dot)com> writes:
>>> 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?

> Beats me.

Performance, mostly --- we prefer to apply checks during the original
data load if possible, but for indexes and FK constraints it's faster to
apply them later. Also, we can separate constraints from the original
table declaration if it's necessary to break a reference circularity.
This isn't something that would be wise to whack around.

regards, tom lane

On 12/07/2011 11:31 AM, Tom Lane wrote:
> Josh Berkus<josh(at)agliodbs(dot)com> writes:
>>>> 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?
>> Beats me.
> Performance, mostly --- we prefer to apply checks during the original
> data load if possible, but for indexes and FK constraints it's faster to
> apply them later. Also, we can separate constraints from the original
> table declaration if it's necessary to break a reference circularity.
> This isn't something that would be wise to whack around.
>
>

Yeah, and if we did want to change it that should be a TODO and not hold
up this feature.

cheers

andrew

On Tue, Nov 15, 2011 at 6:14 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> Updated version with pg_restore included is attached.

The patch applies with some fuzz by now but compiles without errors or warnings.

The feature just works, it is not adding a lot of new code, basically
it parses the given options and then skips over steps depending on the
selected section.

I verified the equivalence of -a and -s to the respective sections in
the different archive formats and no surprise here either, they were
equivalent except for the header (which has a timestamp).

If you ask pg_restore to restore a section out of an archive which
doesn't have this section, there is no error and the command just
succeeds. This is what I expected and I think it's the right thing to
do but maybe others think that
there should be a warning.

In pg_restore, pre-data cannot be run in parallel, it would only run
serially, data and post-data can run in parallel, though. This is also
what I had expected but it might be worth to add a note about this to
the documentation.

What I didn't like about the implementation was the two set_section()
functions, I'd prefer them to move to a file that is shared between
pg_dump and pg_restore and become one function...

Minor issues:

{"section", required_argument, NULL, 5} in pg_dump.c is not in the alphabetical
order of the options.

./pg_restore --section=foobar
pg_restore: unknown section name "foobar")

Note the trailing ')', it's coming from a _(...) confusion

Some of the lines in the patch have trailing spaces and in the
documentation part tabs and spaces are mixed.

int skip used as bool skip in dumpDumpableObject()

Joachim

On 12/8/11 9:18 PM, Joachim Wieland wrote:
> If you ask pg_restore to restore a section out of an archive which
> doesn't have this section, there is no error and the command just
> succeeds. This is what I expected and I think it's the right thing to
> do but maybe others think that
> there should be a warning.

Andrew and I discussed this previously. It's consistent with how we
treat other options in pg_restore. It may be that we should be
consistently treating all options differently, but I don't think that's
specific to this patch.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 12/08/2011 09:18 PM, Joachim Wieland wrote:
> On Tue, Nov 15, 2011 at 6:14 PM, Andrew Dunstan<andrew(at)dunslane(dot)net> wrote:
>> Updated version with pg_restore included is attached.
> The patch applies with some fuzz by now but compiles without errors or warnings.
>
> The feature just works, it is not adding a lot of new code, basically
> it parses the given options and then skips over steps depending on the
> selected section.
>
> I verified the equivalence of -a and -s to the respective sections in
> the different archive formats and no surprise here either, they were
> equivalent except for the header (which has a timestamp).
>
> If you ask pg_restore to restore a section out of an archive which
> doesn't have this section, there is no error and the command just
> succeeds. This is what I expected and I think it's the right thing to
> do but maybe others think that
> there should be a warning.
>
> In pg_restore, pre-data cannot be run in parallel, it would only run
> serially, data and post-data can run in parallel, though. This is also
> what I had expected but it might be worth to add a note about this to
> the documentation.

This is true now of parallel restore, and is by design (see debates from
the time.)

> What I didn't like about the implementation was the two set_section()
> functions, I'd prefer them to move to a file that is shared between
> pg_dump and pg_restore and become one function...

Done

>
> Minor issues:
>
> {"section", required_argument, NULL, 5} in pg_dump.c is not in the alphabetical
> order of the options.
>
> ./pg_restore --section=foobar
> pg_restore: unknown section name "foobar")
>
> Note the trailing ')', it's coming from a _(...) confusion
>
> Some of the lines in the patch have trailing spaces and in the
> documentation part tabs and spaces are mixed.
>
> int skip used as bool skip in dumpDumpableObject()
>
>

Should all be fixed. Revised patch attached.

cheers

andrew

On 12/12/2011 04:35 PM, Andrew Dunstan wrote:
> Should all be fixed. Revised patch attached.

There were two successful test results here and only minor things noted
to fix, which are all cleaned up now. This seems ready for a committer
now; I'm just now sure if you want to do it yourself or have someone
else take a last look over it instead.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us

On 12/16/2011 02:43 PM, Greg Smith wrote:
> On 12/12/2011 04:35 PM, Andrew Dunstan wrote:
>> Should all be fixed. Revised patch attached.
>
> There were two successful test results here and only minor things
> noted to fix, which are all cleaned up now. This seems ready for a
> committer now; I'm just now sure if you want to do it yourself or have
> someone else take a last look over it instead.

I'll do it myself if nobody else wants to comment.

cheers

andrew