RE: [HACKERS] pg_dump & blobs - editable dump?

At 15:32 12/07/00 +0100, Peter Mount wrote:
>Which is why having them on stdout is still a nice option to have. You can
>pipe the lot through your favourite compressor (gzip, bzip2 etc) and
>straight on to tape, or whatever.

Well, the custom format does that, it also does compression and can go to
stdout.

>I don't know why you would want them as separate files - just think what
>would happen to directory search times!!

I agree; the request was based on a desire to do something like pg_dump_lo,
which puts them all in a directory, I think.

>How about this as an idea:
> * Option to dump sql to stdout and blobs to a designated file
> * option to dump sql & blobs to stdout
> * option to dump just sql to stdout
> * option to dump just blobs to stdout
>

The sql is *tiny* compared to most BLOB contents. The new pg_dump currently
supports:

* schema, table data, & blobs
* schema, table data
* schema
* table data & blobs
* table data

BLOBS without table data are not recomended since the process of relinking
the BLOBs to the tables is *only* performed on tables that are restored.
This is to allow import of BLOBS & tables into existing DBs. As a result
your fourth option is not really an option. The other three are already
covered.

Any single-file format (tar would be one of those) can be sent to stdout,
and BLOBs are not supported in plain-text output (for obvious reasons).

>That way (depending on the database design), you could handle the sql &
>blobs separately but still have everything backed up.

Unfortunately the data and BLOBS need to go together.

>PS: Backups is formost on my mind at the moment - had an NT one blow up in
>my face on Monday and it wasn't nice :-(

With the current version you should be able to do:

pg_dump -Fc --blobs | /dev/myfavoritetapedrive

to backup the entire database, with compressed data, to tape.

And

cat /dev/mt | pg_restore --db=dbname

to restore the entire db into the specified database

Or,

pg_dump -Fc --blobs | pg_restore --db=dbname

to copy a database with blobs...

So, in summary, I think most of what you want is already there. It's just
the human-readable part that's a problem.

*Please* let me know if there is some issue I have not considered...

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