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

From: raf <[email-masked]>
Date: Wed, 4 Sep 2024 12:23:45 +1000

On Tue, Sep 03, 2024 at 06:43:22PM -0700, "David G. Johnston" <[email-masked]> wrote:

> On Tuesday, September 3, 2024, raf <[email-masked]> 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

---

From: Tom Lane <[email-masked]>
Date: Tue, 03 Sep 2024 22:28:44 -0400

> thusly in the pg_restore manual entry:

But you'd better also read the para about -1:

  -1
  --single-transaction
    Execute the restore as a single transaction (that is, wrap the
    emitted commands in BEGIN/COMMIT). This ensures that either all
    the commands complete successfully, or no changes are
    applied. This option implies --exit-on-error.

			regards, tom lane

---

From: raf <[email-masked]>
Date: Wed, 4 Sep 2024 12:39:28 +1000

>     applied. This option implies --exit-on-error.

Yes, I saw that. Many thanks. All good now.

cheer,
raf