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

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

(The ".aps24" is a label to indicate which gpg key was used)

Below is the output from the gpg | pg_restore pipeline:

  gpg: encrypted with 2048-bit RSA key, ID 1373FBE2D5B2229A, created 2024-01-15
        "Payroll <data@payroll>"
  pg_restore: [archiver (db)] Error while PROCESSING TOC:
  pg_restore: [archiver (db)] Error from TOC entry 1925; 1255 937975638 FUNCTION xml_is_well_formed(text) postgres
  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...
  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.

But can anyone explain why gpg is getting SIGPIPE? Does pg_restore behave
differently in the face of sql errors when input is stdin rather than from a
named file? Is it closing stdin when this error happens (and -e is not supplied)?
If so, is there a way to stop it closing stdin when an sql error happens?

I could just tell the relevant people (or modify the load script) to
decrypt the file to disk and then load the decrypted database backup
from disk, and delete the temporary unencrypted database backup, but
I'd rather be able to continue to pipe the gpg output into pg_restore.

I assume this problem will go away for future backups, but there are many
old backups containing these xml functions that might need to be loaded
at any time.

Any advice?

Update: The load was using the 9.6 version of pg_restore. When using the
15.8 version of pg_restore, the output changes to:

  pg_restore: error: could not execute query: ERROR:  schema "public" already exists
  Command was: CREATE SCHEMA public;


  gpg: error writing to '-': Broken pipe
  gpg: error flushing '[stdout]': Broken pipe
  gpg: handle plaintext failed: Broken pipe

And it still doesn't load.

cheers,
raf

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

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

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

  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.

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

raf <postgres@raf.org> writes:
> On Tue, Sep 03, 2024 at 06:43:22PM -0700, "David G. Johnston" <david.g.johnston@gmail.com> wrote:
>> 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:

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

On Tue, Sep 03, 2024 at 10:28:44PM -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> raf <postgres@raf.org> writes:
> > On Tue, Sep 03, 2024 at 06:43:22PM -0700, "David G. Johnston" <david.g.johnston@gmail.com> wrote:
> >> 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:
> 
> 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
> 

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

cheer,
raf