public inbox for [email protected]  

help / color / mirror / Atom feed

From: raf <[email protected]>
To: [email protected]
Subject: Help with "gpg -d ... | pg_restore ..." with unimportant pg_restore errors
Date: Wed, 4 Sep 2024 11:32:31 +1000
Message-ID: <[email protected]> (raw)

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: Help with "gpg -d ... | pg_restore ..." with unimportant pg_restore errors
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox