Hi. I've written a Unix shell (zsh) script to streamline the process of
duplicating a database. At the heart of this script I have the following
pipeline:
pg_dump -U $OWNER -Fc $FROM | pg_restore -U $OWNER -d $TO
As far as the shell is concerned, this pipeline fails, due to three errors
emitted by pg_restore. All these errors are vacuous, as far as I can tell.
Following are the excerpts from the dump responsible for the errors, along
with the text of the errors:
COMMENT SCHEMA public postgres;
ERROR: must be owner of schema public
CREATE PROCEDURAL LANGUAGE plpgsql;
ERROR: must be superuser to create procedural language
CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
AS '$libdir/plpgsql', 'plpgsql_call_handler'
LANGUAGE c;
ERROR: permission denied for language c
In all cases, the culprit code is generated automatically by pg_dump,
irrespective of the database being dumped. Therefore the above pipeline
will fail no matter what. (If I get rid of the "-U $OWNER" flag in the
pg_restore command, I get the same errors plus several new ones triggered by
"ALTER ... OWNER TO ..." statements.)
Granted, with the form of pg_restore I'm using above, these three errors do
not prevent the database from being restored. The errors are simply skipped
over.
But these vacuous errors make it much more difficult for the script to
determine whether the pg_restore command succeeded. This seems to me an
totally gratuitous difficulty. Is there any way around it?
(At the moment, as a stopgap, I have the kluge
pg_dump -U $OWNER -Fc $FROM | ( pg_restore -U $OWNER -d $TO || true )
...to ignore all the pg_restore errors, but this is unacceptable.)
Alternatively, is there a better way to streamline the duplication of a
database?
BTW, while on this subject: is there a way to modify the pipeline above to
minimize the number of warnings and notices generated during pg_restore?
I'm aware of "\set VERBOSITY 'terse'", but I don't see how to inject this
setting into the restore.
TIA!
Kynn
