Why not have it using something like tar, and the first file being stored in
ascii?

That way, you could extract easily the human readable SQL but still pipe the
blobs to stdout.

Peter

--
Peter Mount
Enterprise Support
Maidstone Borough Council
Any views stated are my own, and not those of Maidstone Borough Council


-----Original Message-----
From: Philip Warner [mailto:pjw@rhyme.com.au]
Sent: Wednesday, July 12, 2000 2:51 PM
To: pgsql-hackers@postgresql.org; pgsql-general@postgresql.org
Cc: Pavel@Janik.cz
Subject: [HACKERS] pg_dump & blobs - editable dump?



I have been speaking to Pavel about pg_dump support of blobs, and he thinks
it is important to allow for some kind of human-readable version of the
dump to be created.

My guess is that this will involve a plain text schema dump, followed by
all BLOBs in separate files, and a script to load them. To implement this
I'll obviosly need to be passed a directory/file location for the script
since I can't pipe seperate files to stdout.

I'd be interested in knowing what features people think are important in
this kind of format; what do you need to do with the blob files, what do
peple want to edit, etc etc.


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

Wed, 12 Jul 2000 14:58:50 +0100
Peter Mount <petermount@it.maidstone.gov.uk>
RE: [HACKERS] pg_dump & blobs - editable dump?
[This is a multi-reply, CCs and -general removed.]

   From: Peter Mount <petermount@it.maidstone.gov.uk>
   Date: Wed, 12 Jul 2000 14:58:50 +0100

Hi,

   > Why not have it using something like tar, and the first file being
   > stored in ascii?

some filesystems do not allow you to have files bigger then 2G :-( I do not
think that one file (even gzipped tar file) is good.


   From: Peter Mount <petermount@it.maidstone.gov.uk>
   Date: Wed, 12 Jul 2000 15:32:10 +0100

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

No problem, you can use one index file and hashes in it so files are then
stored as:

   AA/AA/AA/00
   AA/AA/AA/01

See how squid (http://www.squid-proxy.org/) does his job here. No problem,
I think. I really prefer this solution over one big file. You can easily
swap files with other databases, you can even create md5sum of md5sums of
each file so you can have a multi-md5sum of your database (you can be
really sure that your backup is OK, etc. :-).

   > That way (depending on the database design), you could handle the sql
   > & blobs separately but still have everything backed up.
   > 
   > 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 :-(

No one (I hope) is arguing about the need for backing BLOBs from the DB :-)
-- 
Pavel Janďż˝k ml.
Pavel.Janik@linux.cz

Thu, 13 Jul 2000 00:35:46 +0200
Pavel.Janik@linux.cz (Pavel =?iso-8859-2?q?Jan=EDk?= ml.)
Re: pg_dump & blobs - editable dump?