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
