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

On Tue, Sep 03, 2024 at 06:43:22PM -0700, "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> 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.

The reason I believe pg_restore should be continuing to execute in the face of
the SQL error is because I didn't supply the -e option which is described
thusly in the pg_restore manual entry:

-e
--exit-on-error
Exit if an error is encountered while sending SQL commands to the database.
The default is to continue and to display a count of errors at the end of
the restoration.

So, since I didn't specify the -e option, pg_restore should continue to
execute, and not close stdin. As I explained, when restoring from a file on
disk, the pg_restore command does continue and work fine. It's only when
restoring from stdin that I'm seeing this problem.

Ah, I see. The manual entry also says that -1 implies -e.

And when reading from a file on disk, my load script doesn't include -1.
Instead, it uses the -j option. Now it all makes sense.

Many thanks. It's working without the -1. I'll change the load
script so that it only uses the -1 option when restoring from
new backups taken after the upgrade to 15.8 (where these vestigial
xml functions won't be present in the backup).

> David J.

cheers,
raf