Thread: Vacuous errors in pg_dump ... | pg_restore pipeline
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
On 2/20/08, Kynn Jones <kynnjo@gmail.com> wrote: > Alternatively, is there a better way to streamline the duplication of a > database? How about: CREATE DATABASE newdb TEMPLATE olddb; (don't remember the exact syntax, but it'll be in the docs for CREATE DATABASE). I think the 'createdb' program also has an option for choosing the template.
"Kynn Jones" <kynnjo@gmail.com> writes:
> 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.
They're hardly vacuous --- they're telling you that the destination
database will be missing plpgsql, which was in the source database.
The only really good way around that pre-8.3 is to run the restore as
a database superuser. As of 8.3 it should be sufficient to be the
database's owner.
If you don't have any actual use for plpgsql in this database, maybe
you should remove it from the source DB?
> 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?
... | PGOPTIONS="--client_min_messages=warning" pg_restore ...
regards, tom lane
On Feb 20, 8:12 pm, d...@mcnaught.org ("Douglas McNaught") wrote:
> On 2/20/08, Kynn Jones <kyn...@gmail.com> wrote:
>
> > Alternatively, is there a better way to streamline the duplication of a
> > database?
>
> How about:
>
> CREATE DATABASE newdb TEMPLATE olddb;
>
Do these methods also recreate the schema path of the database copied?
B.
On Feb 20, 2008, at 2:12 PM, Douglas McNaught wrote: >> >> Alternatively, is there a better way to streamline the duplication >> of a >> database? > > How about: > > CREATE DATABASE newdb TEMPLATE olddb; The template DB has to have no open connections during the entire copy process, so it is not always possible when the DB is big and/or busy. But it is a wonderful way to snapshot development databases before potentially breaking them with new features. :-)