Re: Custom format for pg_dumpall

Hi,

Why is there no custom format dump option for pg_dumpall? What if I
want to use pg_dumpall to dump all db's and blobs? Or if I want to have
a huge sql dump from which I can easily exract the sql to recreate just
one table?

Chris

Christopher Kings-Lynne wrote:
> Hi,
>
> Why is there no custom format dump option for pg_dumpall? What if I
> want to use pg_dumpall to dump all db's and blobs? Or if I want to have
> a huge sql dump from which I can easily exract the sql to recreate just
> one table?

I think it is because we have no mechanism to merge multiple custom
format files from different databases into a single file. Perhaps this
is a TODO.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Christopher Kings-Lynne wrote:
>> Why is there no custom format dump option for pg_dumpall?

> I think it is because we have no mechanism to merge multiple custom
> format files from different databases into a single file. Perhaps this
> is a TODO.

It would probably require changes in pg_dump to append custom-format
output to an existing file, but offhand I see no fundamental obstacle
now that pg_dumpall is a C program. (I think it would've been real
painful to construct an archive from a shell script...) I'm envisioning
some super-archive format in which there are individual entries
containing the pg_dump output for each database, plus an entry for the
"global" users-and-groups info.

This does seem like a nice solution to the perennial problem of dumping
blobs easily.

regards, tom lane

At 02:00 AM 16/03/2004, Tom Lane wrote:
> >> Why is there no custom format dump option for pg_dumpall?
>
> > I think it is because we have no mechanism to merge multiple custom
> > format files from different databases into a single file.

That was the main reason (and as Tom suggested, dumpall was a shell script
at the time).

> Perhaps this
> > is a TODO.
>
>but offhand I see no fundamental obstacle
>now that pg_dumpall is a C program.

I agree.

>I'm envisioning
>some super-archive format in which there are individual entries
>containing the pg_dump output for each database

Not sure about this. I'd be inclined to investigate just adding another
attribute to each TOC entry (database_name).

>This does seem like a nice solution to the perennial problem of dumping
>blobs easily.

What is this?

>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp.mit.edu:11371 |/

Philip Warner <pjw(at)rhyme(dot)com(dot)au> writes:
>> I'm envisioning
>> some super-archive format in which there are individual entries
>> containing the pg_dump output for each database

> Not sure about this. I'd be inclined to investigate just adding another
> attribute to each TOC entry (database_name).

That seems like it would complicate both pg_dump and pg_restore unduly.
I'd rather keep both of them as simple one-database programs. To take
just one objection, people are accustomed to be able to load a pg_dump
from database foo into a database with a different name; that would be
either impossible or very klugy if we do what you're thinking of.

>> This does seem like a nice solution to the perennial problem of dumping
>> blobs easily.

> What is this?

That you can't use pg_dumpall if you want to dump blobs too.

regards, tom lane

At 11:12 AM 16/03/2004, Tom Lane wrote:
>That seems like it would complicate both pg_dump and pg_restore unduly.
>I'd rather keep both of them as simple one-database programs.

Sounds fine, if that's a design objective. But it is a different problem to
definining the archive format of pg_dumpall.

>To take
>just one objection, people are accustomed to be able to load a pg_dump
>from database foo into a database with a different name; that would be
>either impossible or very klugy if we do what you're thinking of.

Again, ISTM this is confusing user-interface with storage format.

While it is certainly possible to define a meta-archive format, ISTM that
the existing archive format can handle multiple DBs with little or no
work. Whether we choose to let pg_restore understand those files is
another matter (personally I'd vote yes).

The current format has a (small) TOC, followed by the (large) data. I
suggest that the TOC would become larger, and include entries for multiple
DBs, each with some tag to indicate the DB it belonged to (db name?). In
the case where the TOC has only one DB, it is almost identical to the
current dump format except it has installation-wide settings as well. The
latter case (at least) should be readable by pg_restore, but that is not
necessary if you prefer to create a pg_restoreall which almost completely
mimics pg_restore. In any case there needs to be the ability to select just
one DB, just the user definitions etc similar to the existing pg_restore.

I don't see a lot of kludges here.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp.mit.edu:11371 |/

Added to TODO:

* Allow pg_dumpall to use non-text output formats

---------------------------------------------------------------------------

Philip Warner wrote:
> At 11:12 AM 16/03/2004, Tom Lane wrote:
> >That seems like it would complicate both pg_dump and pg_restore unduly.
> >I'd rather keep both of them as simple one-database programs.
>
> Sounds fine, if that's a design objective. But it is a different problem to
> definining the archive format of pg_dumpall.
>
> >To take
> >just one objection, people are accustomed to be able to load a pg_dump
> >from database foo into a database with a different name; that would be
> >either impossible or very klugy if we do what you're thinking of.
>
> Again, ISTM this is confusing user-interface with storage format.
>
>
> While it is certainly possible to define a meta-archive format, ISTM that
> the existing archive format can handle multiple DBs with little or no
> work. Whether we choose to let pg_restore understand those files is
> another matter (personally I'd vote yes).
>
> The current format has a (small) TOC, followed by the (large) data. I
> suggest that the TOC would become larger, and include entries for multiple
> DBs, each with some tag to indicate the DB it belonged to (db name?). In
> the case where the TOC has only one DB, it is almost identical to the
> current dump format except it has installation-wide settings as well. The
> latter case (at least) should be readable by pg_restore, but that is not
> necessary if you prefer to create a pg_restoreall which almost completely
> mimics pg_restore. In any case there needs to be the ability to select just
> one DB, just the user definitions etc similar to the existing pg_restore.
>
> I don't see a lot of kludges here.
>
>
>
> ----------------------------------------------------------------
> Philip Warner | __---_____
> Albatross Consulting Pty. Ltd. |----/ - \
> (A.B.N. 75 008 659 498) | /(@) ______---_
> Tel: (+61) 0500 83 82 81 | _________ \
> Fax: (+61) 03 5330 3172 | ___________ |
> Http://www.rhyme.com.au | / \|
> | --________--
> PGP key available upon request, | /
> and from pgp.mit.edu:11371 |/
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

At 03:13 AM 17/03/2004, Bruce Momjian wrote:
> * Allow pg_dumpall to use non-text output formats

Is anyone working on this?

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp.mit.edu:11371 |/

Philip Warner wrote:
> At 03:13 AM 17/03/2004, Bruce Momjian wrote:
> > * Allow pg_dumpall to use non-text output formats
>
> Is anyone working on this?

Nope. It was just added to the TODO list. I am working on having
pg_dump use SET with_default_oids instead of WITH OIDS, and I have
communicated that to Neil Conway.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073