Thread: 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@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.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@postgresql.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@rhyme.com.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@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
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@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073