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