Bug #631: pg_dumpall does not accept -F or -f

Peter Eisentraut wrote:

The pg_dumpall script seems to ignore the file and format switches
that are used with pg_dump. pg_dumpall always writes to stdout in
text format. Also, the -b switch can not be used with pg_dumpall
since text output is being forced.

That's not a bug.

Peter, can you explain why to the user; it is not clear to me why
either, though I have my suspicions, and shouldn't we throw an error to
the user when they try it?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

The pg_dumpall script seems to ignore the file and format switches
that are used with pg_dump. pg_dumpall always writes to stdout in
text format. Also, the -b switch can not be used with pg_dumpall
since text output is being forced.

That's not a bug.

--
Peter Eisentraut peter_e@gmx.net

Peter Eisentraut wrote:

Bruce Momjian writes:

Peter Eisentraut wrote:

The pg_dumpall script seems to ignore the file and format switches
that are used with pg_dump. pg_dumpall always writes to stdout in
text format. Also, the -b switch can not be used with pg_dumpall
since text output is being forced.

That's not a bug.

Peter, can you explain why to the user; it is not clear to me why
either, though I have my suspicions, and shouldn't we throw an error to
the user when they try it?

The pg_dumpall reference page explicitly cautions against this situation.

I see that now:

Any other command line parameters are passed to the under-
lying pg_dump(1) calls. This is useful to control some
aspects of the output format, but some options such as -f,
-t, and dbname should be avoided.

However, it seems we should explicitly throw an error if someone tries
to use these flags.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian writes:

Peter Eisentraut wrote:

The pg_dumpall script seems to ignore the file and format switches
that are used with pg_dump. pg_dumpall always writes to stdout in
text format. Also, the -b switch can not be used with pg_dumpall
since text output is being forced.

That's not a bug.

Peter, can you explain why to the user; it is not clear to me why
either, though I have my suspicions, and shouldn't we throw an error to
the user when they try it?

The pg_dumpall reference page explicitly cautions against this situation.

--
Peter Eisentraut peter_e@gmx.net

Bruce Momjian <pgman@candle.pha.pa.us> writes:

However, it seems we should explicitly throw an error if someone tries
to use these flags.

I agree with Bruce that it's be friendlier if pg_dumpall did a little
more checking. Not a real high priority, perhaps.

regards, tom lane

Peter Eisentraut wrote:

Bruce Momjian writes:

Peter Eisentraut wrote:

The pg_dumpall script seems to ignore the file and format switches
that are used with pg_dump. pg_dumpall always writes to stdout in
text format. Also, the -b switch can not be used with pg_dumpall
since text output is being forced.

That's not a bug.

Peter, can you explain why to the user; it is not clear to me why
either, though I have my suspicions, and shouldn't we throw an error to
the user when they try it?

The pg_dumpall reference page explicitly cautions against this situation.

Also, Peter, CVS pg_dumpall is not working for me. It isn't dumping any
database contents. This new DATABASES line doesn't work in pg_dumpall:

DATABASES="$DATABASES $DATABASE"

The problem is that this is done inside a 'while' loop, so the value
doesn't get outside the loop. I tested with BSD/OS sh and bash
2.02.1(2).

#$ X=1
#$ echo 1 2 3 | while read X

do
X=2
done

#$ echo $X
1

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

However, it seems we should explicitly throw an error if someone tries
to use these flags.

I agree with Bruce that it's be friendlier if pg_dumpall did a little
more checking. Not a real high priority, perhaps.

OK, Donald, I have added this patch to CVS that will return a proper
error message when invalid pg_dumpall options are used. I added the
options mentioned in the pg_dumpall manual page, plus -F (which wasn't
mentioned in the manual page).

The reason -Ft can't be used is that ASCII SQL commands are output by
pg_dumpall, and these can't be merged with non-ASCII pg_dump output
formats.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thanks for the quick response. I see that pg_dumpall outputs the global
objects (-g) to stdout and that it cannot be combined with the -F or -f for
this reason. I would still like to be able to do a full database backup,
including all databases with oids and blobs, from a generic shell script
that did not know about which databases existed. Under the current set of
utilities this does not seem to be possible without coding the script to
run:

pg_dump --oids --blobs --format=c

for each database and sending each database to it's own backup file. By
modifying the logic of the pg_dumpall script a solution might be possible.
I propose the following backup logic:

1. Backup all the globals to a text file.

pg_dumpall --globals-only > globals.dump

2. Backup all the schemas to a text file, don't output globals.

pg_dumpall --schema-only > schema.dump

3. Backup all the databases to a custom file, don't output globals.

pg_dumpall --data-only --format=c --oids --blobs > data.dump

This presumes however that the custom file formats output by each pg_dump
for a database can be concatenated into a single file. Is this possible?

If so, pg_dumpall would only need to detect --globals-only. If it is used
then output global information to stdio. If it is not present then call
pg_dump for each database with the full set of parameters given and don't
output anything from pg_dumpall.

I think the bigger problem is that once you concatenate the custom
format for multiple databases into a single file, how does pg_restore
recognize where one database stops and another starts. I think the only
solution is to put the binary dumps in separate files, perhaps like
this:

psql -l | while read DB
do
pg_dump --data-only --format=c --oids --blobs $DB > $DB.out
done

I can't see another way.

---------------------------------------------------------------------------

Donald A Pellegrino wrote:

Thanks for the quick response. I see that pg_dumpall outputs the global
objects (-g) to stdout and that it cannot be combined with the -F or -f for
this reason. I would still like to be able to do a full database backup,
including all databases with oids and blobs, from a generic shell script
that did not know about which databases existed. Under the current set of
utilities this does not seem to be possible without coding the script to
run:

pg_dump --oids --blobs --format=c

for each database and sending each database to it's own backup file. By
modifying the logic of the pg_dumpall script a solution might be possible.
I propose the following backup logic:

1. Backup all the globals to a text file.

pg_dumpall --globals-only > globals.dump

2. Backup all the schemas to a text file, don't output globals.

pg_dumpall --schema-only > schema.dump

3. Backup all the databases to a custom file, don't output globals.

pg_dumpall --data-only --format=c --oids --blobs > data.dump

This presumes however that the custom file formats output by each pg_dump
for a database can be concatenated into a single file. Is this possible?

If so, pg_dumpall would only need to detect --globals-only. If it is used
then output global information to stdio. If it is not present then call
pg_dump for each database with the full set of parameters given and don't
output anything from pg_dumpall.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

"Donald A Pellegrino" <Donald.A.Pellegrino@USA.dupont.com> writes:

This presumes however that the custom file formats output by each pg_dump
for a database can be concatenated into a single file. Is this possible?

AFAIK none of the custom formats are amenable to that. We could imagine
giving pg_dumpall a target directory, into which it writes a dump file
for each database --- but there are a lot of unpleasant implications of
that approach, disk space usage not the least of 'em.

One interesting thought would be for pg_dumpall to wrap some kind of
wrapper format ("tar" comes to mind immediately, but I'm not sure if
it will work when the file sizes aren't known in advance) around the
per-database files produced by pg_dump. I am not sure that this can be
done comfortably in a shell script; it might be necessary to rewrite
pg_dumpall in C. And we'd probably need a pg_restoreall program to
disassemble the wrapper and invoke pg_restore on each chunk.

regards, tom lane