RE: 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   |/