Re: Help with "gpg -d ... | pg_restore ..." with unimportant pg_restore errors

On Tuesday, September 3, 2024, raf <postgres(at)raf(dot)org> wrote:

> Hi,
>
> I need help!
>
> I'm upgrading an ancient (but still awesome) postgresql-9.6.24 (via
> EnterpriseDB)
> to (a no doubt even more awesome) postgresql-15.8 (via debian (stable)
> packages)
> but am unable to load database backups that were encrypted via gpg.
> Loading from
> unencrypted backups works fine (and the millions of tests all pass! Yay!).
>
> I have a convenience program for handling loading called "load"
> and the underlying commands that it executes look like this:
>
> dropdb -h payroll -p 5433 -U postgres payroll_tst
> createdb -h payroll -p 5433 -U postgres -T template0 -E utf8 -O admin
> payroll_tst

Given the following command

> gpg --decrypt 20240904-011254-payroll_tst.pgdump.gpg.aps24 | pg_restore
> -1 -h payroll -p 5433 -U postgres -d payroll_tst -Fc

And this error

>
> pg_restore: [archiver (db)] could not execute query: ERROR: could not
> find function "xml_is_well_formed" in file "/usr/lib/postgresql/15/lib/
> pgxml.so"
> Command was: CREATE FUNCTION public.xml_is_well_formed(text)
> RETURNS boolean
> LANGUAGE c IMMUTABLE STRICT
> AS '$libdir/pgxml', 'xml...

This should be expected. In particular…

> gpg: error writing to '-': Broken pipe
> gpg: error flushing '[stdout]': Broken pipe
> gpg: handle plaintext failed: Broken pipe
> pgrestore encountered errors
>
> I'm not worried about the xml_is_well_formed error (or the xml_valid error
> that
> would happen next). I think those functions are ancient and irrelevant and
> not
> in use, and I'm happy for pg_restore to continue, like it does when gpg is
> not
> involved.

You specified “-1” so I don’t get why you believe pg_restore should be
continuing to execute in the face of the SQL error.

David J.