Date: Wed, 4 Sep 2024 12:23:45 +1000
From: raf <postgres@raf.org>
To: pgsql-general@lists.postgresql.org
Subject: Re: Help with "gpg -d ... | pg_restore ..." with unimportant
 pg_restore errors
Message-ID: <ZtfEsfn1HaxQyy2x@raf.org>
References: <Zte4r3rQ9_GbCZe-@raf.org>
 <CAKFQuwZsJ+oGEiyxFLppF7c-JU5=00u0pwZdqjqrnV94uL6oUA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <CAKFQuwZsJ+oGEiyxFLppF7c-JU5=00u0pwZdqjqrnV94uL6oUA@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/ZtfEsfn1HaxQyy2x%40raf.org>
Precedence: bulk

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

> On Tuesday, September 3, 2024, raf <postgres@raf.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