Re: Does pg_dumpall do BLOBs too?

On Sat, Jan 12, 2002 at 11:28:28AM -0500, Tom Lane wrote:
> Frank Joerdens <frank(at)joerdens(dot)de> writes:
> > The wrapper script doesn't seem to pass the option -Ft to pg_dump so it
> > doesn't support output formats other than plain text which means you
> > can't do BLOBs.
>
> The trouble is that pg_dumpall wants to concatenate the output from
> several pg_dumps, intermixed with commands issued by itself. Easy to do
> with text outputs, not so easy with the non-text formats.

True.

> I could see making pg_dumpall emit a script that has the global setup
> commands plus pg_restore calls referencing separately-created data
> files, one per database. Trouble with that is that the data files
> couldn't be sent to pg_dumpall's stdout, which means that pg_dumpall
> would have to include options for deciding where to put them.

As a default, create a subdir (maybe call it pg_dumpall_files[current
system date]) in the current directory where you'd also put the script
itself?

And what
> about the case where you have more than 4GB of data and a system that
> doesn't do large files? Presently it's easy to pipe pg_dumpall to
> "split", and cat the segments together to feed to psql when reloading.
> But that method won't work under this scenario.

On starting the script, give a message saying something along the lines
of 'If you have a lot of data, you might end up with files larger than 2
GB which may not work on some systems. In that case you should use the
plaintext option and dump and restore any databases with BLOBs
separately.' Then offer to continue or to quit?

What I just did to dump everything out and reload it was to do
pg_dumpall and pg_dump for the one (out of 26) databases that contained
BLOBs separately, then reload everything from the dumpall script, drop
the BLOB database, recreate it, and then restore it from its separate
script. I suppose that didn't kill me but an integrated solution would
be nicer and cleaner. I've got a sysadmin intern here next month who I
might put to the task.

Regards, Frank