Date: Wed, 4 Sep 2024 11:32:31 +1000
From: raf <postgres@raf.org>
To: pgsql-general@postgresql.org
Subject: Help with "gpg -d ... | pg_restore ..." with unimportant pg_restore
 errors
Message-ID: <Zte4r3rQ9_GbCZe-@raf.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Archived-At: <https://www.postgresql.org/message-id/Zte4r3rQ9_GbCZe-%40raf.org>
Precedence: bulk

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